Top 100 SQL Interview Questions and Answers in India

Basic SQL Questions and Answers

1: What is SQL?

Relational databases are managed and altered using a domain-specific language called SQL (Structured Query Language).  In addition to creating and modifying database structures, it is used to query, insert, update, and delete data.

2: What are the different types of SQL commands?

• DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE
• DML (Data Manipulation Language): INSERT, UPDATE, DELETE
• DCL (Data Control Language): GRANT, REVOKE
• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT

3: What is the difference between DELETE and TRUNCATE?

• DELETE: Eliminates rows one at a time and allows for WHERE clause filtering. It reports every row deletion and is slower.
• TRUNCATE: Eliminates every row at once and is unable to employ a WHERE clause. It resets the identity of the table (auto-increment) and is faster.

4: What is a Primary Key?

A column, or group of columns, that uniquely identifies every table row is called a primary key. It must be unique and cannot contain NULL data.

5: What is a Foreign Key?

A column or group of columns in one table that point to the primary key of another table is called a foreign key. It creates a connection between two tables.

6: What is the difference between INNER JOIN and OUTER JOIN?

• INNER JOIN: Only gives back rows from both tables that match.
• OUTER JOIN: Includes rows that don’t match as well. LEFT JOIN, RIGHT JOIN, and FULL JOIN are its three varieties.

7: What is a Subquery?

A query nested inside another query is called a subquery. It is employed to retrieve information that will be a condition in the main query.

8: What is an Index?

A database object that speeds up data retrieval operations on a table is called an index. It functions similarly to a pointer to information in a certain column.

9: What is the difference between WHERE and HAVING?

• WHERE: Before grouping, rows are filtered.
• HAVING: After aggregation, it filters groupings

10: What is Normalization?

The practice of arranging data in a database to minimize dependencies and redundancies is called normalization.  It entails breaking up big tables into more manageable, relevant tables.

Intermediate SQL Questions

11. What is a View?

A view is a query-generated virtual table. It dynamically pulls data from underlying tables rather than storing it itself.

12: What is the difference between UNION and UNION ALL?

• UNION: Merges and eliminates duplicate results from two queries.
• UNION ALL: Incorporates duplicates and combines the results of two queries.

13: What is a Self-Join?

When a table is joined with itself, it’s called a self-join. When comparing rows inside the same database or dealing with hierarchical data, it is helpful.

14: What is the purpose of GROUP BY?

The GROUP BY clause, which is frequently used with aggregate functions like SUM, COUNT, AVG, etc., collects rows that share values in designated columns into summary rows.

15: What is a Stored Procedure?

A precompiled set of SQL statements that may be run as a single unit is called a stored procedure. It enhances reusability and performance.

16: What is a Trigger?

A trigger is a unique kind of stored procedure that starts running automatically when specific events (like INSERT, UPDATE, or DELETE) on a table occur.

17: What is the difference between CHAR and VARCHAR?

• CHAR: String with a set length. Uses blanks to fill in empty spaces.
• VARCHAR: Variable-length string. doesn’t fill in empty space.

18: What is a Cursor?

A database object called a cursor is used to access, modify, and move through the rows that a query returns.

19: What is the difference between DROP and TRUNCATE?

• DROP: Removes all of the data and the table structure.
• TRUNCATE: Eliminates every row while maintaining the table’s structure.

20: What is ACID in databases?

Atomicity, Consistency, Isolation, and Durability are the acronyms for ACID. It guarantees dependable database transaction processing.

Advanced SQL Questions

21. What is a CTE (Common Table Expression)?

A CTE is a temporary result set that is defined while a SELECT, INSERT, UPDATE, or DELETE statement is being executed. It makes complicated queries easier to understand and more readable.

22: What is Window Function?

Without collapsing the result set, a window function does computations across a group of rows associated with the current row. RANK(), DENSE_RANK(), and ROW_NUMBER() are a few examples.

23: What is the difference between RANK() and DENSE_RANK()?

• RANK(): Duplicate rows are given the same rank, but further ranks are skipped.
• DENSE_RANK(): Duplicate rows are given the same rank, but succeeding ranks are not skipped.

24: What is a Recursive Query?

Organizational charts and other hierarchical data are queried using a recursive query. Until a base case is satisfied, it repeatedly runs using a Common Table Expression (CTE).

25: What is Database Sharding?

The technique of dividing a big database into smaller, easier-to-manage sections known as shards is known as sharding. A subset of the data is contained in each shard.

26: What is the difference between OLTP and OLAP?

• OLTP (Online Transaction Processing): Concentrates on quick, regular transactions (banking systems, for example).
• OLAP (Online Analytical Processing): Prioritizes intricate reporting and analysis questions.

27: What is Denormalization?

Adding duplicate data to a normalized database in order to enhance read efficiency is known as denormalization. It decreases the requirement for joins while increasing storage.

28: What is a Deadlock?

When two or more transactions lock down resources that the other transactions require, it’s called a deadlock and causes a halt.

29: What is the difference between Clustered and Non-Clustered Index?

• Clustered Index: Establishes the data’s physical arrangement in a table. Each table can only have one.
• Non-Clustered Index: Separates the conceptual order from the actual data. Each table has several.

30: What is a Materialized View?

A database entry that physically stores a query’s result is called a materialized view. It enhances query performance and is updated on a regular basis.

Scenario-Based SQL Questions

31. How do you find duplicate rows in a table?

Using SQL, you can perform the following procedure to find the duplicate rows in a table:

• SELECT column1, column2, COUNT(*)
• FROM table_name
• GROUP BY column1, column2
• HAVING COUNT(*) > 1;

32: How do you delete duplicate rows?

Through SQL, you can execute the process of deleting duplicate rows via the following procedure:

• DELETE FROM my_table
• WHERE id NOT IN (
• SELECT MIN(id)
• FROM my_table
• GROUP BY column1, column2
• );

33: How do you calculate running totals?

Through SQL, you can implement the following process to calculate running totals:

• SELECT Date, Sales,
•          SUM(Sales) OVER (ORDER BY Date) AS Running_Total
• FROM SalesTable;

34: How do you find the second highest salary?

Via SQL, you can follow the below-mentioned process to find the second-highest salary:

• SELECT DISTINCT salary
• FROM employees
• ORDER BY salary DESC
• LIMIT 1 OFFSET 1;

35. What are DDL, DML, DCL, and TCL?

• DDL (Data Definition Language)

• Used to define and manage the structure of a database.

• Commands:

  • CREATE – Creates a new database or table.

  • ALTER – Modifies an existing database or table structure.

  • DROP – Deletes a database or table.

  • TRUNCATE – Removes all records from a table but keeps the structure.

• DML (Data Manipulation Language)

• Used to manipulate data within the database.

• Commands:

  • INSERT – Adds new records to a table.

  • UPDATE – Modifies existing records.

  • DELETE – Removes specific records from a table.

  • SELECT – Retrieves data from one or more tables.

• DCL (Data Control Language)

  • Used to manage user permissions and access control.

  • Commands:

    • GRANT – Gives specific privileges to users.

    • REVOKE – Removes specific privileges from users.

• TCL (Transaction Control Language)

• Used to manage transactions in a database.

• Commands:

  • COMMIT – Saves all changes made in the current transaction.

  • ROLLBACK – Reverts changes made in the current transaction.

  • SAVEPOINT – Sets a checkpoint in a transaction to which changes can be rolled back.

36: What is the difference between a Left Join and a Right Join?

• LEFT JOIN: Only returns matched records from the right table and all records from the left table. Columns from the right table return NULL values if there is no match.
• RIGHT JOIN: Only returns matched records from the left table and all records from the right table. Columns from the left table return NULL values if there is no match.

37: How do you handle NULL values in SQL?

• Use the IS NULL or IS NOT NULL condition to check for NULL values.
• Use COALESCE() to replace NULL with a default value.
• Use IFNULL() (MySQL) or NVL() (Oracle) for default replacements.

38: What is the purpose of the COALESCE function?

The COALESCE() function returns the first non-NULL value from a list of expressions. E.g.

• SELECT COALESCE(NULL, NULL, ‘Default Value’, ‘Another Value’);

39: How do you optimize SQL queries?

1. Use Indexes: Index columns that are frequently sought and primary keys.
2. Avoid SELECT *: Choose only the columns that are required.
3. Use EXPLAIN Plan: Examine the execution of the query.
4. Normalize Data: Cut down on redundancy.
5. Use Joins Efficiently: Steer clear of pointless joins.
6. Optimize WHERE Conditions: Start by putting limiting conditions in place.
7. Use LIMIT for Large Queries: Only retrieve the necessary rows.
8. Partition Large Tables: Aids in effective large data management.
9. Use Caching: Save the results of frequently asked questions.

40: What is the difference between a correlated and a non-correlated subquery?

• Correlated Subquery: Runs once for every row in the outer query; dependent on the outer query.
• Non-Correlated Subquery: Runs only once, regardless of the outer query.

41. What is a Cartesian Product?

A Cartesian Product occurs when a JOIN condition is missing, causing every row from one table to combine with every row from another.

42: How do you implement pagination in SQL?

A subset of records can be retrieved for display through pagination.

MySQL / PostgreSQL (Using LIMIT OFFSET)

• SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET 20;

43: What is the difference between a Unique Key and a Primary Key?

The prime difference between a Unique Key and a Primary Key is described in the following table:

Feature	Primary Key	Unique Key
Uniqueness	Ensures uniqueness	Ensures uniqueness
NULL values	Not allowed	Allowed (one NULL)
Number per Table	Only one	Multiple allowed
Index	Clustered Index	Non-clustered Index

E.g. using SQL,

• CREATE TABLE employees (
• id INT PRIMARY KEY,           — Only one per table, cannot be NULL
• email VARCHAR(100) UNIQUE — Can have multiple unique constraints
• );

44. What are magic tables in SQL?

Magic Tables in SQL are virtual tables that temporarily store data during INSERT, UPDATE, and DELETE operations. They are mainly used in triggers to hold the before and after values of the affected rows.

• Types of Magic Tables:

1. INSERTED Table – Stores the newly inserted or updated data.

2. DELETED Table – Stores the old data before an update or delete operation.

45: How do you find the Nth highest salary?

Using SQL, we can find the Nth highest salary through the following procedure:

• SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 2;

A good starting point for being ready for SQL interviews is this list. To succeed in technical rounds, practice crafting questions and comprehending the underlying ideas.

46: What is a Composite Key?

Combining two or more columns to uniquely identify each table row is known as a composite key.  It is employed when rows cannot be individually identified by a single column.

47: What is the difference between a Primary Key and a Unique Key?

• Primary Key: Enforces uniqueness and is unable to include NULL data.
• Unique Key: Maintains uniqueness even if it contains a single NULL value.

48: What is the purpose of the WITH clause in SQL?

CTEs, or common table expressions, are defined using the WITH clause. By dividing complicated searches into smaller, reusable components, it makes them simpler.

49: What is a Temporary Table?

A table that only exists momentarily on the database server is known as a temporary table. It is automatically dropped at the end of a session after being created during it.

50: How do you handle hierarchical data in SQL?

One way to manage hierarchical data is by using:

• Recursive queries with CTEs.
• Adjacency list model.
• Nested set model.

51. What is the difference between IN and EXISTS?

• IN: Finds out whether a value is present in a predefined list or subquery.
• EXISTS: Determines whether any rows are returned by a subquery. For large datasets, it is typically faster.

52: What is the purpose of the CASE statement?

Conditional logic in SQL queries is implemented using the CASE statement.  It assesses conditions and, depending on the outcome, returns various values.

53: How do you calculate the median in SQL?

By using the following SQL formula, we can sincerely calculate the median in SQ:

• SELECT PERCENTILE_CONT(0:5) WITHIN GROUP (ORDER BY column_name) AS median
• FROM table_name;

54: What is the difference between COUNT(*) and COUNT(column_name)?

• COUNT(*): Counts every row, even those that contain NULL values.
• COUNT(column_name): Only counts values in the designated column that are not NULL.

55: What is a Self-Referencing Table?

When a foreign key in the same table references the primary key, the table is said to be self-referencing.  Organizational charts and other hierarchical data frequently use it.