SQL Interview questions and answers

SQL Interview Questions & Answers:

1. What is SQL?

--> SQL (Structured Query Language) is used to store, retrieve, manage, and manipulate data in relational databases like MySQL, PostgreSQL, SQL Server, and Oracle.

2. What are the different types of SQL commands?

SQL commands are categorized into five types:

• DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE (Defines database structure).
• DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE (Manipulates data).
• DCL (Data Control Language): GRANT, REVOKE (Controls user permissions).
• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT (Manages transactions).
• DQL (Data Query Language): SELECT (Retrieves data).

3. What is the difference between DELETE, TRUNCATE, and DROP?

• DELETE removes specific rows from a table and can be rolled back using ROLLBACK.
• TRUNCATE removes all rows from a table but keeps its structure. It cannot be rolled back.
• DROP removes the entire table, including its structure. It also cannot be rolled back.

4. What is the difference between WHERE and HAVING?

WHERE is used to filter rows before grouping.
HAVING is used to filter grouped data after GROUP BY.

Example:

SELECT department, COUNT(*) 
FROM employees 
WHERE salary > 30000  
GROUP BY department
HAVING COUNT(*) > 5;  

5. What is a Primary Key and Foreign Key?

--> Primary Key uniquely identifies each record in a table. It cannot have duplicate or NULL values.
--> Foreign Key references the primary key of another table, maintaining relationships between tables.

Example:

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

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

• UNION combines results from multiple queries and removes duplicates.
• UNION ALL combines results without removing duplicates.

Example:

SELECT name FROM customers
UNION  
SELECT name FROM employees;

SELECT name FROM customers
UNION ALL 
SELECT name FROM employees;

7. What are Joins in SQL? Explain types.

Joins combine records from multiple tables based on a related column.

• INNER JOIN: Returns matching records from both tables.
• LEFT JOIN: Returns all records from the left table + matching records from the right table.
• RIGHT JOIN: Returns all records from the right table + matching records from the left table.
• FULL JOIN: Returns all records when there is a match in either table.
• SELF JOIN: Joins a table with itself.
• CROSS JOIN: Returns the Cartesian product of both tables.

Example of INNER JOIN:

SELECT customers.name, orders.order_id  
FROM customers  
INNER JOIN orders ON customers.customer_id = orders.customer_id;

8. What is an Index in SQL?

--> An index improves search performance by creating a lookup table for faster access.
--> However, it can slow down INSERT, UPDATE, and DELETE operations because the index must be updated.

Example:

CREATE INDEX idx_customer_name ON customers(name);

9. What are Aggregate Functions?

Aggregate functions perform calculations on multiple rows and return a single value.

COUNT() → Returns the number of rows.
SUM() → Returns the total sum.
AVG() → Returns the average value.
MIN() → Returns the smallest value.
MAX() → Returns the largest value.

Example:

SELECT department, COUNT(*) AS total_employees, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

10. What is the difference between GROUP BY and ORDER BY?

GROUP BY groups rows with the same values and is used with aggregate functions.
ORDER BY sorts the result set in ascending (ASC) or descending (DESC) order.

Example:

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

SELECT name, salary 
FROM employees 
ORDER BY salary DESC;

11. What is a Subquery?

A subquery is a query inside another query, typically used in WHERE, FROM, or SELECT clauses.

Example:

SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

• Executes the inner query first (AVG(salary)).
• The outer query filters employees with a salary above the average.