TOP SQL Interview Questions and Answers

Q1 - What are UNION, MINUS, and INTERSECT commands?

If there are two queries Q1 and Q2, The UNION operator is used to combine the results of two tables while also removing duplicate entries. The MINUS operator is used to return rows from the first query but not from the second query. The INTERSECT operator is used to combine the common results of both queries.

Q2 - What are Tables and Fields?

A table is basically a collection of data components organized in rows and columns in a relational database. A table can also be thought of as a useful representation of relationships. The most basic form of data storage is the table. Fields are columns in the table.

Q3 - What is a Self-Join?

A self-join is one type of join that can be used to connect two tables. As a result, it is a unary relationship. Each row of the table is attached to itself and all other rows of the same table are in self-join. As a result, a self-join is mostly used to combine and compare rows from the same database table.

Q4 - How to remove duplicate rows in SQL?

DELETE FROM table WHERE ID IN (
SELECT
ID, COUNT(ID)
FROM table
GROUP BY ID
HAVING
COUNT (ID) > 1);

Q5 - What are the Constraints in SQL?

These are used to specify the limit on the data type of the table. Below are some examples of constraints that can be used

• NOT NULL
• CHECK
• DEFAULT
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY

Q6 - What are the syntax and use of the COALESCE function?

COALESCE function returns the first non-NULL value from a series of expressions, It is very useful in finding meaningful value in some queries. Syntax of function - COALESCE (exp1, exp2, ….)

Q7 - What are the different types of SQL statements?

SQL statements are generally categorized into the following types:

• DDL (Data Definition Language): CREATE, ALTER, DROP
• DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
• DCL (Data Control Language): GRANT, REVOKE
• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT

Q8 - What is a primary key?

A primary key is a unique identifier for a record in a table. It must contain unique values and cannot contain NULLs. Each table can have only one primary key, which can consist of single or multiple columns.

Q9 - What is the difference between INNER JOIN and OUTER JOIN?

INNER JOIN returns only the rows that have matching values in both tables.

OUTER JOIN can be further categorized into LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN. LEFT JOIN returns all rows from the left table and the matched rows from the right table, RIGHT JOIN returns all rows from the right table and the matched rows from the left table, and FULL OUTER JOIN returns rows when there is a match in one of the tables.

Q10 - What is a foreign key?

INNER A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. It is used to establish a link between the data in two tables, enforcing referential integrity.

Q11 - What is an index and why is it used?

An index is a database object that improves the speed of data retrieval operations on a table at the cost of additional storage space and write performance. It is used to quickly locate and access the data in a database table without having to search every row in the table.

Q12 - What is a subquery and what are its types?

A subquery is a query nested inside another query. Subqueries can be used in SELECT, INSERT, UPDATE, or DELETE statements. Types of subqueries include:

• Single-row subquery: Returns zero or one row.
• Multiple-row subquery: Returns one or more rows.
• Correlated subquery: References a column from the outer query.
• Non-correlated subquery: Does not reference columns from the outer query.

Q13 - How do you use the GROUP BY clause in SQL?

The GROUP BY clause is used to arrange identical data into groups. This clause is often used with aggregate functions like COUNT, SUM, AVG, MAX, and MIN. For example:

SELECT department, COUNT(*) FROM employees GROUP BY department;

Q14 - What is normalization? Explain its types.

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. The types of normalization (normal forms) include:

• First Normal Form (1NF): Ensures each column contains atomic (indivisible) values.
• Second Normal Form (2NF): Ensures that all non-key attributes are fully functional dependent on the primary key.
• Third Normal Form (3NF): Ensures that all the attributes are functionally dependent only on the primary key.
• Boyce-Codd Normal Form (BCNF): A stricter version of 3NF.