8. What is a join? What are the types of join?
Answer:
Join is used in SQL queries for joining and fetching data from two or more table.
Types of JOIN that we can use in SQL:
The
INNER JOIN returns records that have matching values in both tables.
The
LEFT JOIN returns all records from the left table, and the matched records from the right table.
The
RIGHT JOIN returns all records from the right table, and the matched records from the left table.
A
SELF JOIN is a join in which a table is joined with itself. Aliases should be used for the same table comparison.
Full Join returns all the rows from the left hand side table and all the rows from the right hand side table.
9. What is Normalization? Explain the Advantages of normalizing database.
Answer:
Normalization is a process of organizing database by splitting larger tables into smaller ones that are easier to maintain and linking them using relationships. It reduces redundant data and optimize data dependencies.
Advantages:
- Eliminates duplicate entries
- Saves storage space
- Improves the query performances
10. What are the different type of normalization?
Answer:
1. First Normal Form (1NF)
A relation is in the first normal form if it contains no repeating columns.
2. Second Normal Form (2NF)
A relation is said to be in 2NF if and only if it is in 1NF and every non-key attribute is fully dependent on the primary key.
3. Third Normal Form (3NF)
A relation is said to be in 3NF, if and only if it is in 2NF and every non-key attribute is non-transitively dependent on the primary key.
11. What is Denormalization?
Answer:
Denormalization is the process of attempting to optimize data retrieval by adding redundancy. It is basically the process of undoing the normalization done in a database design to optimize the performance of a database.
12. What are the different types of SQL statements?
Answer:
1. DDL - Data Definition Language
DDL defines the structure that holds the data. It creates and modifies the structure of database objects in database. For example, Create, Alter, Drop and Truncate table.
2. DML - Data Manipulation Language
It is used to retrieve, modify, delete, insert data in database. Typical operations are Insert, Delete, Update and retrieving the data from the table.
3. DCL - Data Control Language
DCL controls the visibility of data and enforces database security in a multiple user database environment. It includes operations like granting database access, setting privileges to create tables etc. Two types of DCL commands are GRANT and REVOKE.
4. Transaction Control Language (TCL)
TCL commands are used to manage transactions in database. e.g. COMMIT and ROLLBACK.
13. Define Union and Union All.
Answer:
Union is a distinct recording of two tables.
UNION ALL operator is a combine result set of 2 or more SELECT statements.
