SQL QUESTIONS

Fundamental Concepts

1. Order of Execution in SQL Queries

SQL queries follow a specific order:

FROM clause

JOIN operations

WHERE clause

GROUP BY clause

HAVING clause

SELECT statement

ORDER BY clause

1. What is the Difference Between WHERE and HAVING?

WHERE: Filters rows before aggregation.

HAVING: Filters groups after aggregation.

Example:

SELECT department_id, COUNT()
FROM employees
WHERE salary > 50000
GROUP BY department_id
HAVING COUNT() > 10;

1. What is the Purpose of GROUP BY?

GROUP BY is used to group rows sharing a property so that aggregate functions can be applied to each group.

Example:

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;

1. Explain All Types of Joins in SQL

INNER JOIN: Returns rows with matching values in both tables.

LEFT JOIN: Returns all rows from the left table, and matching rows from the right table.

RIGHT JOIN: Returns all rows from the right table, and matching rows from the left table.

FULL OUTER JOIN: Returns all rows when there is a match in either table.

CROSS JOIN: Returns the Cartesian product of the two tables.

SELF JOIN: A table joined to itself.

Example:

SELECT e1.name, e2.name AS manager_name
FROM employees e1
INNER JOIN employees e2
ON e1.manager_id = e2.employee_id;

1. What are Triggers in SQL?

Triggers are special procedures that automatically execute in response to specific events on a table, such as INSERT, UPDATE, or DELETE.

Example:

CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO audit_log(employee_id, action)
VALUES (NEW.employee_id, 'INSERT');
END;

1. What is a Stored Procedure in SQL?

A stored procedure is a precompiled set of SQL statements that can be executed as a single unit to improve performance and maintainability.

Example:

CREATE PROCEDURE GetEmployeeById (IN emp_id INT)
BEGIN
SELECT * FROM employees WHERE employee_id = emp_id;
END;

1. Explain Types of Window Functions

RANK: Assigns a rank with gaps in case of ties.

DENSE_RANK: Assigns a rank without gaps.

ROW_NUMBER: Assigns a unique number to each row.

LEAD: Accesses data from a subsequent row.

LAG: Accesses data from a preceding row.

Example:

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

1. What is the Difference Between TRUNCATE and DELETE?

TRUNCATE: Removes all rows, faster, and cannot be rolled back.

DELETE: Deletes specific rows and can be rolled back.

Example:

DELETE FROM employees WHERE department_id = 10;
TRUNCATE TABLE employees;

1. What is the Difference Between DML, DDL, and DCL?

DML: Data Manipulation Language (INSERT, UPDATE, DELETE).

DDL: Data Definition Language (CREATE, ALTER, DROP).

DCL: Data Control Language (GRANT, REVOKE).

1. Which is Faster Between CTE and Subquery?

Common Table Expressions (CTEs) are often more readable and reusable than subqueries, but performance depends on the use case.

Example:
Using a CTE:

WITH EmployeeCTE AS (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
)
SELECT * FROM EmployeeCTE WHERE avg_salary > 50000;

1. What are Constraints and Their Types?

Constraints ensure data integrity:

Primary Key: Uniquely identifies each row.

Foreign Key: Links two tables.

Unique: Ensures all values are unique.

Check: Validates a condition.

Default: Sets default values.

Not Null: Ensures column values are not null.

Example:

CREATE TABLE employees (
employee_id INT PRIMARY KEY,
salary DECIMAL(10, 2) CHECK (salary > 0),
department_id INT DEFAULT 1
);

1. What is an Index? Explain Its Types

Indexes improve query performance:

Clustered Index: Data is stored in the order of the index.

Non-clustered Index: Separate structure for the index.

Example:

CREATE INDEX idx_employee_name ON employees(name);

1. Difference Between UNION and UNION ALL

UNION: Removes duplicates.

UNION ALL: Keeps all rows.

Example:

SELECT name FROM employees
UNION
SELECT name FROM managers;

1. How Many Types of Clauses in SQL?

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT

1. Difference Between Primary Key and Secondary Key

Primary Key: Unique and not null.

Secondary Key: May not be unique or mandatory.

1. How to Find the Second Highest Salary of an Employee?

SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

1. Difference Between a Function and a Stored Procedure

Function: Returns a value and can be used in SQL expressions.

Stored Procedure: Does not return a value and is invoked separately.

1. How to Optimize a Slow SQL Query?

Use indexes.

Avoid SELECT *.

Minimize joins and subqueries.

Use proper filtering and grouping.

1. How to Handle Duplicate Rows in SQL?

DELETE FROM employees
WHERE rowid NOT IN (
SELECT MIN(rowid)
FROM employees
GROUP BY name, department_id
);

1. How to Use Window Functions to Solve Complex Queries?

Example:

SELECT name, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;

Practical SQL Queries

1. Find the Top 3 Departments by Average Salary

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
ORDER BY avg_salary DESC
LIMIT 3;

1. Employees with the Same Name in the Same Department

SELECT name, department_id, COUNT()
FROM employees
GROUP BY name, department_id
HAVING COUNT() > 1;

1. Departments with No Employees

SELECT department_id
FROM departments
WHERE department_id NOT IN (
SELECT DISTINCT department_id FROM employees
);

1. Employees with Over 5 Years of Experience

SELECT *
FROM employees
WHERE DATEDIFF(CURDATE(), hire_date) > 5 * 365;

1. Customers Who Placed Orders but Made No Payments

SELECT customer_id
FROM orders
WHERE order_id NOT IN (
SELECT DISTINCT order_id FROM payments
);

1. Employees in the Same Department as Their Manager

SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
INNER JOIN employees m
ON e.manager_id = m.employee_id
WHERE e.department_id = m.department_id;

Key Differences at a Glance

Feature

Key Difference

RANK vs DENSE_RANK

Gaps in ranking vs no gaps in ranking.

HAVING vs WHERE

Filters groups vs filters rows.

UNION vs UNION ALL

Removes duplicates vs keeps all rows.

DELETE vs TRUNCATE

Deletes specific rows vs removes all rows.

CTE vs Subquery

Reusable temporary result vs nested query.