1. What is SQL?

Answer:
SQL (Structured Query Language) is used to communicate with databases. It allows us to insert,
retrieve, update, and delete data, and also manage database structures.

2. What is the difference between SQL, MySQL, and SQL Server?

Answer:

 SQL → Language for managing databases.

 MySQL / SQL Server / Oracle DB → Database Management Systems (DBMS) that use SQL as
their query language.

3. What are the types of SQL commands?

Answer:

 DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE

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

 DQL (Data Query Language): SELECT

 DCL (Data Control Language): GRANT, REVOKE

 TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT

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

Answer:

 DELETE → Removes rows from a table (with WHERE condition). Rollback possible.

 TRUNCATE → Removes all rows from a table. Rollback not possible (DDL).

 DROP → Deletes the entire table structure + data permanently.

5. What is a Primary Key and Foreign Key?

Answer:

 Primary Key: Uniquely identifies each record in a table (cannot be NULL, must be unique).

 Foreign Key: A column in one table that refers to the primary key of another table (maintains
relationship).

6. What are Constraints in SQL?

Answer:
Constraints are rules applied to columns to maintain data integrity. Common constraints:

 NOT NULL

 UNIQUE

 PRIMARY KEY

 FOREIGN KEY

 CHECK

 DEFAULT

7. What is the difference between WHERE and HAVING?

Answer:

 WHERE → Used to filter rows before grouping (cannot use aggregate functions).

 HAVING → Used to filter groups after applying aggregate functions.

8. What are Joins? Name types of Joins.

Answer:
Joins combine data from multiple tables based on related columns.
Types:

 INNER JOIN → Returns matching rows from both tables

 LEFT JOIN → Returns all rows from left + matching from right

 RIGHT JOIN → Returns all rows from right + matching from left

 FULL JOIN → Returns all rows when there is a match in either table

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

Answer:

 UNION → Combines results of two queries, removes duplicates.

 UNION ALL → Combines results, keeps duplicates.

10. What is Normalization?

Answer:
Normalization is the process of organizing data to reduce redundancy and improve integrity.
Common forms:

 1NF: Remove repeating groups, ensure atomic values.

  2NF: No partial dependency (every non-key depends on the whole key).

 3NF: No transitive dependency (non-key depends only on key).

11. What is a View?

Answer:
A View is a virtual table created using a SQL query. It does not store data itself but fetches from base
tables.

12. What is the difference between Clustered and Non-Clustered Index?

Answer:

 Clustered Index: Sorts and stores rows physically (only one per table, usually primary key).

 Non-Clustered Index: Stores a separate index with pointers to actual data.

13. What is a Subquery?

Answer:
A Subquery is a query inside another query.

 Single-row subquery → Returns one value.

 Multi-row subquery → Returns multiple values.

14. What is the difference between IN and EXISTS?

Answer:

 IN → Checks if a value is present in a given list.

 EXISTS → Returns TRUE if subquery returns at least one row.

15. What are Aggregate Functions in SQL?

Answer:
Functions that return a single value from multiple rows:

 COUNT()

 SUM()

 AVG()

 MAX()

 MIN()
16. What is the difference between CHAR and VARCHAR?

Answer:

 CHAR(n): Fixed-length storage (always uses n bytes).

 VARCHAR(n): Variable-length storage (uses only needed space).

17. What is a Stored Procedure?

Answer:
A Stored Procedure is a precompiled collection of SQL statements that can be executed as a single
unit. Improves performance and reusability.

18. What is a Trigger?

Answer:
A Trigger is a special kind of stored procedure that automatically executes when an event (INSERT,
UPDATE, DELETE) occurs on a table.

19. What is the difference between SQL and NoSQL databases?

Answer:

 SQL: Relational, structured data, uses tables (MySQL, Oracle, SQL Server).

 NoSQL: Non-relational, unstructured data, uses collections/documents (MongoDB,

Cassandra).

20. Write a query to find the second highest salary.

Answer:

SELECT MAX(salary)

FROM employees

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

🗂 Sample Table: Employees

CREATE TABLE Employees (

EmpID INT PRIMARY KEY,

EmpName VARCHAR(50),

Dept VARCHAR(50),

Salary INT,

JoinDate DATE

);

INSERT INTO Employees (EmpID, EmpName, Dept, Salary, JoinDate) VALUES

(1, 'Amit', 'HR', 35000, '2020-01-10'),

(2, 'Sneha', 'IT', 60000, '2019-03-15'),

(3, 'Rahul', 'Finance', 45000, '2021-07-22'),

(4, 'Priya', 'IT', 75000, '2020-11-05'),

(5, 'Arjun', 'Finance', 50000, '2018-06-01'),

(6, 'Neha', 'HR', 30000, '2022-02-12');

🔹 Practice Questions & Answers

1. Retrieve all employees from IT department.

SELECT * FROM Employees WHERE Dept = 'IT';

2. Find the highest salary.

SELECT MAX(Salary) AS HighestSalary FROM Employees;

3. Find the second highest salary.

SELECT MAX(Salary)

FROM Employees

WHERE Salary < (SELECT MAX(Salary) FROM Employees);

4. Count number of employees in each department.

SELECT Dept, COUNT(*) AS EmpCount

FROM Employees

GROUP BY Dept;

5. Find employees who joined after 2020.

SELECT *

FROM Employees

WHERE JoinDate > '2020-12-31';

6. Find average salary of Finance department.

SELECT AVG(Salary) AS AvgSalary

FROM Employees

WHERE Dept = 'Finance';

7. Display employees with salary greater than 50,000.

SELECT EmpName, Salary

FROM Employees

WHERE Salary > 50000;

8. Find department-wise maximum salary.

SELECT Dept, MAX(Salary) AS MaxSalary

FROM Employees

GROUP BY Dept;

9. Find employees whose name starts with ‘A’.

SELECT *

FROM Employees

WHERE EmpName LIKE 'A%';

10. Find total salary given by each department.

SELECT Dept, SUM(Salary) AS TotalSalary

FROM Employees
GROUP BY Dept;

11. Write a query to display top 3 highest paid employees.

SELECT *

FROM Employees

ORDER BY Salary DESC

LIMIT 3;

(In Oracle / SQL Server use FETCH FIRST 3 ROWS ONLY instead of LIMIT.)

12. Find employees who do not belong to IT department.

SELECT *

FROM Employees

WHERE Dept <> 'IT';

13. Find employees with duplicate salaries (if any).

SELECT Salary, COUNT(*)

FROM Employees

GROUP BY Salary

HAVING COUNT(*) > 1;

14. Find youngest employee by JoinDate.

SELECT *

FROM Employees

WHERE JoinDate = (SELECT MAX(JoinDate) FROM Employees);

15. Increase all HR employees’ salary by 10%.

UPDATE Employees

SET Salary = Salary * 1.10

WHERE Dept = 'HR';

1. Which SQL command is used to remove all rows from a table but keeps the table structure?
a) DELETE
b) DROP
c) TRUNCATE
d) REMOVE

Answer: c) TRUNCATE

2. Which of the following is a correct way to select all columns from a table named Employees?
a) SELECT * FROM Employees;
b) SELECT all FROM Employees;
c) GET * FROM Employees;
d) SHOW * Employees;

Answer: a) SELECT * FROM Employees;

3. In SQL, which keyword is used to remove duplicate rows from a result set?
a) UNIQUE
b) DISTINCT
c) REMOVE DUPLICATE
d) CLEAN

Answer: b) DISTINCT

4. Which SQL clause is used to filter records based on a condition?

a) ORDER BY
b) WHERE
c) GROUP BY
d) HAVING

Answer: b) WHERE

5. What will be the output of the following SQL statement?

SELECT COUNT(*) FROM Students WHERE Marks > 90;

a) Number of students with marks > 90

b) List of all students with marks > 90
c) Maximum marks from the table
d) Error

Answer: a) Number of students with marks > 90

6. Which SQL command is used to add a new column to an existing table?

a) ADD COLUMN
b) ALTER TABLE … ADD COLUMN
c) UPDATE TABLE … ADD
d) INSERT COLUMN

Answer: b) ALTER TABLE … ADD COLUMN

7. What does the following query return?

SELECT AVG(Salary) FROM Employees;

a) Highest salary of employees

b) Lowest salary of employees
c) Average salary of employees
d) Total salary of employees

Answer: c) Average salary of employees

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

 DELETE → Removes rows (can use WHERE), logs each row, slower.

 TRUNCATE → Removes all rows, resets identity, faster, no WHERE.

 DROP → Deletes entire table structure.

2. What is the difference between WHERE and HAVING?

 WHERE → Filters rows before grouping.

 HAVING → Filters groups after GROUP BY.

3. What are the different types of SQL JOINs?

 INNER JOIN → Returns matching rows from both tables.

 LEFT JOIN → Returns all rows from left + matching rows from right.

 RIGHT JOIN → Returns all rows from right + matching rows from left.

 FULL JOIN → Returns all rows from both tables.

4. Difference between PRIMARY KEY, UNIQUE, and FOREIGN KEY?

 PRIMARY KEY → Unique + Not Null (only one per table).

 UNIQUE → Unique values, but allows one NULL.

 FOREIGN KEY → References primary key of another table.

5. What is a subquery?
A query inside another SQL query.

 Single-row subquery → returns 1 value.

 Multi-row subquery → returns multiple values (use IN, ANY, ALL).

6. Difference between UNION and UNION ALL?

 UNION → Removes duplicates.

 UNION ALL → Keeps duplicates.

7. What is the difference between CHAR and VARCHAR?

 CHAR(n) → Fixed length (pads with spaces).

 VARCHAR(n) → Variable length (saves only actual size).

8. What is Normalization?
Process of organizing data to reduce redundancy.

 1NF → Atomic values (no repeating groups).

 2NF → 1NF + no partial dependency.

 3NF → 2NF + no transitive dependency.

9. What is an Index in SQL?

 A data structure (B-tree/Hash) to speed up queries.

 Clustered Index → Sorts data physically (only 1 per table).

 Non-Clustered Index → Creates a separate structure (many allowed).

10. What are SQL Aggregate Functions?

 COUNT() → Number of rows

 SUM() → Total value

 AVG() → Average

 MAX() → Largest value

 MIN() → Smallest value

11. What is a View in SQL?

  A virtual table based on the result of a query.

 Does not store data itself, only query definition.

 Can simplify complex queries and improve security.

12. Difference between OLTP and OLAP databases?

 OLTP → Online Transaction Processing (real-time insert/update/delete).

 OLAP → Online Analytical Processing (used for reporting, aggregation).

13. What is a Stored Procedure?

 A precompiled set of SQL statements stored in the database.

 Improves performance, reusability, and security.

14. What are Triggers in SQL?

 A block of SQL that runs automatically in response to events (INSERT, UPDATE, DELETE).

 Example: Audit logs, automatic calculations.

15. What are Transactions in SQL?

 A unit of work executed as a single logical operation.

 Properties → ACID: Atomicity, Consistency, Isolation, Durability.

16. What is the difference between EXISTS and IN?

 IN → Compares with a list of values (subquery executed once).

 EXISTS → Checks if subquery returns rows (subquery executed for each row).

17. Difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?

 RANK() → Skips numbers if ties.

 DENSE_RANK() → No gaps in ranking.

 ROW_NUMBER() → Unique sequence number, no ties.

18. What is a Cursor in SQL?

 A database object used to fetch rows one by one from a result set.
  Used in procedural operations when row-by-row processing is needed.

19. Difference between Scalar and Aggregate Functions?

 Scalar functions → return single value (LEN(), UCASE(), GETDATE()).

 Aggregate functions → return computed value for multiple rows (SUM, COUNT).

20. What is the difference between Clustered and Non-Clustered Index?

 Clustered Index → Sorts physical data, only one allowed.

 Non-Clustered Index → Logical pointer, many allowed.

21. What is the difference between Correlated Subquery and Simple Subquery?

 Simple Subquery → Executed once, result used by outer query.

 Correlated Subquery → Executed repeatedly for each row in outer query.

22. What is the difference between DELETE with WHERE and TRUNCATE?

 DELETE WHERE → Removes specific rows.

 TRUNCATE → Removes all rows quickly.

23. What are SQL Constraints?

 Rules enforced on data.

 Types: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT.

24. What is the difference between SQL and NoSQL databases?

 SQL → Structured, relational, predefined schema.

 NoSQL → Unstructured/semi-structured, flexible schema (MongoDB, Cassandra).

25. What is Denormalization?

 Adding redundancy to improve query performance.

 Used in data warehouses.

26. Difference between INNER JOIN and OUTER JOIN?

  INNER JOIN → Returns only matching rows.

 OUTER JOIN → Returns matching + non-matching rows (LEFT/RIGHT/FULL).

27. What is Self-Join?

 A join of a table with itself.

 Example: finding employees and their managers from the same table.

28. What is a Cross Join?

 Produces the Cartesian product of two tables.

 If table A has 5 rows and table B has 4 rows → result has 20 rows.

29. Difference between NVL, ISNULL, and COALESCE?

 NVL (Oracle) → Replace NULL with given value.

 ISNULL (SQL Server) → Replace NULL with given value.

 COALESCE → Returns first non-NULL value from a list.

30. What are Window Functions in SQL?

 Functions that perform calculations across a set of rows related to the current row.

 Examples: ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG().

31. What is the difference between UNION and JOIN?

 UNION → Combines result sets vertically (rows).

 JOIN → Combines tables horizontally (columns).

32. How do you find the 2nd highest salary in a table?

SELECT MAX(Salary)

FROM Employees

WHERE Salary < (SELECT MAX(Salary) FROM Employees);

Or using RANK():

SELECT Salary

FROM (
 SELECT Salary, RANK() OVER(ORDER BY Salary DESC) AS rnk

FROM Employees

)t

WHERE rnk = 2;

33. How to find duplicate rows in a table?

SELECT column1, COUNT(*)

FROM table

GROUP BY column1

HAVING COUNT(*) > 1;

34. What is a Composite Key?

 A key formed by combining two or more columns to uniquely identify a row.

35. Difference between OLAP Cube and Star Schema?

 OLAP Cube → Multidimensional representation for analysis.

 Star Schema → A central fact table with dimension tables (data warehouse design).

36. What is Query Optimization?

 Process of making SQL queries run faster.

 Methods: Indexing, avoiding SELECT *, using EXISTS over IN, partitioning.

37. What is Materialized View?

 Like a view, but it stores data physically.

 Used in data warehouses for faster access.

38. Difference between DELETE CASCADE and ON DELETE SET NULL?

 CASCADE → Deletes child rows automatically.

 SET NULL → Sets foreign key to NULL when parent is deleted.

39. How do you find the Nth highest salary in SQL?
Using LIMIT / OFFSET (MySQL/Postgres):

SELECT Salary

FROM Employees

ORDER BY Salary DESC

LIMIT 1 OFFSET N-1;

40. What is a Deadlock in SQL?

 A situation where two transactions block each other by holding resources the other needs.

SQL Quick Revision Cheat Sheet

 DDL: CREATE, ALTER, DROP, TRUNCATE

 DML: INSERT, UPDATE, DELETE
 DQL: SELECT
 DCL: GRANT, REVOKE
 TCL: COMMIT, ROLLBACK, SAVEPOINT
 Clauses: WHERE, GROUP BY, HAVING, ORDER BY
 Aggregate Functions: COUNT(), SUM(), AVG(), MAX(), MIN()
 Joins: INNER, LEFT, RIGHT, FULL
 Keys: PRIMARY KEY (unique), FOREIGN KEY (relationship)
 Operators: IN, EXISTS, LIKE, BETWEEN
 Normalization: 1NF, 2NF, 3NF
 Second Highest Salary: SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT
 MAX(Salary) FROM Employees);
 Top N Records: SELECT * FROM Employees ORDER BY Salary DESC LIMIT N;
 Find Duplicates: SELECT col, COUNT(*) FROM table GROUP BY col HAVING COUNT(*)>1;