26. What is a Subquery?
A subquery is a query inside another query.
It is used when you need the result of one query to use in another.
Example: Get employees who earn more than the average salary:
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
27. What is a Nested Query?
A nested query is another name for a subquery.
It simply means a query inside another query.
28. What is a Correlated Subquery?
A correlated subquery runs again and again, once for each row of the outer query.
Example: Find employees whose salary is above their department average:
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(salary)
FROM employees
WHERE dept_id = e.dept_id
);
29. What is Group By in SQL?
GROUP BY groups rows that have the same values and is used with aggregates.
Example: Count employees in each department:
SELECT dept_id, COUNT(*)
FROM employees
GROUP BY dept_id;
30. Difference between Group By and Order By?
| GROUP BY | ORDER BY |
|---|---|
| Groups data | Sorts data |
| Used with aggregates | Used for sorting |
| No sorting guarantee | Always sorts output |
31. What is the use of LIMIT in SQL?
LIMIT restricts the number of rows returned.
Example: Show first 5 records:
SELECT * FROM employees LIMIT 5;
32. How to find the Second Highest Salary in SQL?
Method 1: Using LIMIT
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
33. How to find Duplicate Records in a table?
SELECT email, COUNT(*)
FROM employees
GROUP BY email
HAVING COUNT(*) > 1;
34. What is CTE (Common Table Expression)?
A CTE is a temporary result created using WITH keyword.
It improves readability.
Example:
WITH high_salary AS (
SELECT name, salary FROM employees WHERE salary > 50000
)
SELECT * FROM high_salary;
35. What is Temporary Table in SQL?
A temporary table stores data temporarily for the session.
CREATE TEMPORARY TABLE temp_emp AS
SELECT * FROM employees WHERE salary > 50000;
36. What is a Window Function?
Window functions perform calculations across rows without grouping.
Example: Assign row numbers:
SELECT name, salary,
ROW_NUMBER() OVER(ORDER BY salary DESC) AS sl_no
FROM employees;
37. Difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?
| Function | Output | Duplicate handling |
|---|---|---|
| ROW_NUMBER | No duplicates | Always unique |
| RANK | Skips ranks | 1, 2, 2, 4 |
| DENSE_RANK | No skip in ranks | 1, 2, 2, 3 |
38. What is CASE Statement in SQL?
CASE works like IF-ELSE.
Example:
SELECT name, salary,
CASE
WHEN salary > 50000 THEN 'High'
WHEN salary > 30000 THEN 'Medium'
ELSE 'Low'
END AS salary_level
FROM employees;
39. What is COALESCE in SQL?
COALESCE returns the first non-NULL value.
SELECT COALESCE(email, 'No Email') AS email_id
FROM employees;
40. What is NVL Function in SQL?
NVL is Oracle-only, but MySQL uses IFNULL() instead.
MySQL Example:
SELECT IFNULL(phone, 'Not Provided') FROM employees;
41. What is Indexing in SQL?
An index improves search speed in a table, just like an index in a book.
CREATE INDEX idx_name ON employees(name);
42. What is a Clustered Index?
MySQL (InnoDB) automatically makes the Primary Key a clustered index.
It stores data physically sorted by the key.
43. What is Non-Clustered Index?
A non-clustered index creates a separate structure that points to the data.
Example:
CREATE INDEX idx_email ON employees(email);
44. Difference between Clustered and Non-Clustered Index?
| Clustered Index | Non-Clustered Index |
|---|---|
| Sorts actual data | Stores pointers |
| Only one per table | Many allowed |
| Fast for range queries | Fast for exact matches |
45. What is a View in SQL?
A view is a virtual table created from a SELECT query.
CREATE VIEW emp_view AS
SELECT name, salary FROM employees;
46. Difference between View and Table?
| View | Table |
|---|---|
| Virtual | Physical |
| Stores query | Stores data |
| Auto updates | Manual updates |
47. What is Stored Procedure?
A stored procedure is a saved SQL block that you can run anytime.
DELIMITER $$
CREATE PROCEDURE get_emps()
BEGIN
SELECT * FROM employees;
END$$
DELIMITER ;
48. Difference between Function and Stored Procedure?
| Stored Procedure | Function |
|---|---|
| Can return multiple values | Returns single value |
| Can use INSERT/UPDATE | Mostly calculations |
| Called using CALL | Used inside queries |
49. What is Trigger in SQL?
Trigger runs automatically when an event (INSERT/UPDATE/DELETE) happens.
Example:
CREATE TRIGGER before_insert_emp
BEFORE INSERT ON employees
FOR EACH ROW
SET NEW.created_at = NOW();
50. What is Cursor in SQL?
A cursor is used to process rows one-by-one (rarely used in MySQL but supported).
DECLARE done INT DEFAULT FALSE;
DECLARE emp_name VARCHAR(50);
DECLARE cur CURSOR FOR SELECT name FROM employees;