sql top 20 interview questions and answers with examples

Top 20 SQL interview questions & Answers

SQL interview questions along with example answers and explanations.

1. What is SQL?

Answer: SQL (Structured Query Language) is a standard programming language specifically designed for managing and manipulating relational databases. It is used to perform tasks such as querying, updating, and managing data.

2. What are the different types of SQL commands?

Answer: SQL commands are divided into five main categories:

• 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
• DQL (Data Query Language): SELECT

3. What is a primary key?

Answer: A primary key is a field (or combination of fields) in a table that uniquely identifies each row in that table. It must contain unique values and cannot contain NULLs.

Example:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    position VARCHAR(50)
);

4. What is a foreign key?

Answer: A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. The foreign key is used to establish and enforce a link between the data in the two tables.

Example:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

5. What is an index? Why is it used?

Answer: An index is a database object that improves the speed of data retrieval operations on a table at the cost of additional storage and slower write operations. Indexes are used to quickly locate and access the data in a database table.

Example:

CREATE INDEX idx_employee_name ON employees (name);

6. What is the difference between DELETE and TRUNCATE?

Answer:

• DELETE: Removes rows from a table based on a condition. It can be rolled back and triggers are fired.
• TRUNCATE: Removes all rows from a table without logging individual row deletions. It cannot be rolled back and does not fire triggers.

Example:

DELETE FROM employees WHERE position = 'Intern';
TRUNCATE TABLE employees;

7. What is a join? Explain the different types of joins.

Answer: A join is an SQL operation used to combine rows from two or more tables based on a related column between them.

• INNER JOIN: Returns rows when there is a match in both tables.
• LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table, and the matched rows from the right table. If no match, NULLs are returned for columns from the right table.
• RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right table, and the matched rows from the left table. If no match, NULLs are returned for columns from the left table.
• FULL JOIN (FULL OUTER JOIN): Returns rows when there is a match in one of the tables. Unmatched rows will also be shown, with NULLs in columns from the table without a match.

Example:

-- INNER JOIN--
SELECT employees.name, orders.order_date
FROM employees
INNER JOIN orders ON employees.employee_id = orders.customer_id;

-- LEFT JOIN--
SELECT employees.name, orders.order_date
FROM employees
LEFT JOIN orders ON employees.employee_id = orders.customer_id;

-- RIGHT JOIN--
SELECT employees.name, orders.order_date
FROM employees
RIGHT JOIN orders ON employees.employee_id = orders.customer_id;

-- FULL JOIN--
SELECT employees.name, orders.order_date
FROM employees
FULL JOIN orders ON employees.employee_id = orders.customer_id;

8. What is normalization? Explain different normal forms.

Answer: Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity.

• 1NF (First Normal Form): Ensures that each column contains atomic (indivisible) values, and each record is unique.
• 2NF (Second Normal Form): Meets all the requirements of 1NF, and all non-key attributes are fully functional dependent on the primary key.
• 3NF (Third Normal Form): Meets all the requirements of 2NF, and all attributes are dependent only on the primary key.
• BCNF (Boyce-Codd Normal Form): A stronger version of 3NF where every determinant is a candidate key.
• 4NF (Fourth Normal Form): Meets all the requirements of BCNF, and there are no multi-valued dependencies.

9. What is denormalization?

Answer: Denormalization is the process of combining normalized tables into larger tables to improve read performance. It involves adding redundancy by storing data in multiple places.

10. What is a view?

Answer: A view is a virtual table based on the result set of an SQL query. It can contain rows and columns from one or more tables. Views do not store data physically but provide a way to simplify complex queries and enhance security by restricting access to specific data.

Example:

CREATE VIEW employee_orders AS
SELECT employees.name, orders.order_date
FROM employees
JOIN orders ON employees.employee_id = orders.customer_id;

11. What is a stored procedure?

Answer: A stored procedure is a set of SQL statements that can be stored and executed on the database server. It helps in encapsulating complex business logic and improving performance by reducing network traffic.

Example:

CREATE PROCEDURE GetEmployeeOrders
AS
BEGIN
    SELECT employees.name, orders.order_date
    FROM employees
    JOIN orders ON employees.employee_id = orders.customer_id;
END;

12. What is a trigger?

Answer: A trigger is a special kind of stored procedure that automatically executes when an event (INSERT, UPDATE, DELETE) occurs in the database.

Example:

CREATE TRIGGER UpdateEmployeeLog
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_log (employee_id, log_time)
    VALUES (NEW.employee_id, CURRENT_TIMESTAMP);
END;

13. What is a cursor?

Answer: A cursor is a database object used to retrieve, manipulate, and navigate through a result set row by row. It is often used in stored procedures to handle complex row-based operations.

Example:

DECLARE @employee_id INT
DECLARE employee_cursor CURSOR FOR
SELECT employee_id FROM employees

OPEN employee_cursor
FETCH NEXT FROM employee_cursor INTO @employee_id

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Process each row--
    FETCH NEXT FROM employee_cursor INTO @employee_id
END

CLOSE employee_cursor
DEALLOCATE employee_cursor

14. What are aggregate functions? Name a few.

Answer: Aggregate functions perform a calculation on a set of values and return a single value. Common aggregate functions include:

• COUNT(): Returns the number of rows.
• SUM(): Returns the sum of a numeric column.
• AVG(): Returns the average value of a numeric column.
• MAX(): Returns the maximum value in a set.
• MIN(): Returns the minimum value in a set.

Example:

SELECT COUNT(*) AS total_employees FROM employees;
SELECT AVG(age) AS average_age FROM employees;
SELECT MAX(salary) AS highest_salary FROM employees;

15. Explain the difference between HAVING and WHERE clauses.

Answer:

• WHERE: Used to filter rows before any groupings are made.
• HAVING: Used to filter groups after the GROUP BY clause.

Example:

-- Using WHERE--
SELECT * FROM employees WHERE age > 30;

-- Using HAVING--
SELECT position, COUNT(*) as num_employees
FROM employees
GROUP BY position
HAVING COUNT(*) > 1;

16. What is a subquery?

Answer: A subquery is a query nested within another query. It is used to perform operations that need to be executed before the main query.

Example:

SELECT name FROM employees
WHERE employee_id IN (SELECT employee_id FROM orders WHERE order_date > '2024-01-01');

17. What is the difference between UNION and UNION ALL?

Answer:

• UNION: Combines the result sets of two queries and removes duplicates.
• UNION ALL: Combines the result sets of two queries and includes duplicates.

Example:

-- Using UNION
SELECT name FROM employees
UNION
SELECT name FROM customers;

-- Using UNION ALL
SELECT name FROM employees
UNION ALL
SELECT name FROM customers;

18. What is a self-join?

Answer: A self-join is a regular join but the table is joined with itself. It is useful for querying hierarchical data or comparing rows within the same table.

Example:

SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;

19. Explain the ACID properties.

Answer: ACID properties ensure reliable processing of database transactions.

• Atomicity: Ensures that all operations within a transaction are completed; if not, the transaction is aborted.
• Consistency: Ensures the database is in a valid state before and after the transaction.
• Isolation: Ensures transactions are executed in isolation; changes are not visible until the transaction is committed.
• Durability: Ensures that once a transaction is committed, it remains so, even in the event of a system failure.

CREATE TABLE test_char (
    fixed_char CHAR(10),
    variable_char VARCHAR(10)
);

These questions cover a wide range of SQL concepts and are commonly asked in technical interviews. Understanding and being able to explain these concepts with examples will help you in acing your SQL interviews.