Top-50 Most Frequently asked SQL Interview Questions

103222 Explore and Read Our Blogs Written By Our Insutry Experts Learn From KSR Data Vizon

Basic-Level SQL Interview Questions

SQL Interview Questions


Basic Level Questions (15 Questions)

1. What is SQL?

Answer: SQL (Structured Query Language) is a standard programming language used to manage and manipulate relational databases.

2. What is a Primary Key?

Answer: A primary key is a unique identifier for a record in a table, ensuring uniqueness and preventing NULL values.

3. What is a Foreign Key?

Answer: A foreign key is a field in one table that uniquely identifies a row of another table, creating a relationship between the tables.

4. Explain the SELECT statement in SQL.

Answer: The SELECT statement is used to query data from a database. It allows you to retrieve specific columns from a table.

5. What is the difference between WHERE and HAVING clauses?

Answer: WHERE filters records before aggregation, while HAVING filters groups after aggregation.

6. What is the use of the GROUP BY clause?

Answer: The GROUP BY clause groups rows that have the same values in specified columns into summary rows.

7. What is the ORDER BY clause?

Answer: The ORDER BY clause sorts the result set of a query by one or more columns.

8. Explain the difference between INNER JOIN and OUTER JOIN.

Answer: INNER JOIN returns matching rows from both tables, while OUTER JOIN returns all rows from one table and matching rows from the other.

9. What is a UNION in SQL?

Answer: The UNION operator is used to combine the result sets of two or more SELECT statements, removing duplicates.

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

Answer: UNION removes duplicates, while UNION ALL includes all duplicates.

11. What are SQL constraints?

Answer: SQL constraints are rules applied to columns to ensure data integrity, such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT.

12. What is normalization in SQL?

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

13. Explain the INSERT INTO statement.

Answer: The INSERT INTO statement is used to add new rows of data to a table.

14. What is the difference between DELETE and TRUNCATE?

Answer: DELETE removes rows one by one and can be rolled back, while TRUNCATE removes all rows at once and cannot be rolled back.

15. What is a VIEW in SQL?

Answer: A VIEW is a virtual table created based on the result set of a SELECT query.

Intermediate Level Questions (15 Questions)

1. Explain the ALTER TABLE statement?

Answer: The ALTER TABLE statement is used to modify an existing table structure, such as adding, deleting, or modifying columns.

2. What is a subquery?

Answer: A subquery is a query nested within another query, used to retrieve data that will be used in the main query.

3. How do you handle NULL values in SQL?

Answer: NULL values can be handled using functions like IS NULL, IS NOT NULL, COALESCE, or NULLIF.

4. What is a CASE statement in SQL?

Answer: The CASE statement allows you to implement conditional logic in SQL queries.

5. What are stored procedures?

Answer: Stored procedures are precompiled collections of SQL statements stored in the database, allowing for code reuse and performance optimization.

6. What are triggers in SQL?

Answer: Triggers are automatic actions executed in response to specific events on a table or view, such as INSERT, UPDATE, or DELETE.

7. Explain the difference between CHAR and VARCHAR.

Answer: CHAR is a fixed-length data type, while VARCHAR is a variable-length data type.

8. What is indexing in SQL?

Answer: Indexing improves the speed of data retrieval operations on a database table by creating a data structure that allows for fast searching.

9. What is a JOIN? Explain its types?

Answer: A JOIN is used to combine rows from two or more tables based on a related column. Types include INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, and CROSS JOIN.

10. How do you optimize SQL queries?

Answer: Query optimization can be achieved by using indexing, avoiding SELECT *, simplifying joins, and analyzing query execution plans.

11. Explain the difference between RANK(), DENSE_RANK(), and ROW_NUMBER() in SQL.

Answer:

ROW_NUMBER() assigns a unique rank to each row.

RANK() assigns the same rank to duplicate values but skips subsequent ranks.

DENSE_RANK() assigns the same rank to duplicate values without skipping ranks.

12. What is a correlated subquery?

Answer: A correlated subquery is a subquery that references columns from the outer query, meaning it is executed for each row of the outer query.

13. What is a transaction in SQL?

Answer: A transaction is a sequence of operations performed as a single logical unit of work, ensuring data integrity through properties like ACID (Atomicity, Consistency, Isolation, Durability).

14. Explain the MERGE statement.

Answer: The MERGE statement allows you to perform INSERT, UPDATE, or DELETE operations on a target table based on the results of a JOIN with a source table.

15. What are window functions in SQL?

Answer: Window functions perform calculations across a set of table rows related to the current row without collapsing them into a single result, such as ROW_NUMBER(), RANK(), and SUM() over a partition.

Advanced Level Questions (10 Questions)

1. What is a materialized view, and how does it differ from a regular view?

Answer: A materialized view stores the result of a query physically, whereas a regular view is a virtual table that is dynamically generated.

2. Explain the concept of database partitioning.

Answer: Database partitioning involves dividing a large table into smaller, more manageable pieces called partitions, improving performance and maintainability.

3. What is the difference between OLTP and OLAP?

Answer: OLTP (Online Transaction Processing) systems manage transactional data, focusing on insert, update, and delete operations. OLAP (Online Analytical Processing) systems handle complex queries and reporting, focusing on data analysis.

4. What is the purpose of the EXPLAIN command in SQL?

Answer: The EXPLAIN command provides insight into how a SQL query will be executed by the database, helping in query optimization.

5. What is sharding in databases?

Answer: Sharding is a database partitioning technique that distributes data across multiple machines to improve performance and scalability.

6. Explain the WITH RECURSIVE clause in SQL.

Answer: The WITH RECURSIVE clause allows for the creation of recursive queries, which are useful for hierarchical data processing like organizational charts or tree structures.

7. What are ACID properties in SQL transactions?

Answer: ACID stands for Atomicity, Consistency, Isolation, and Durability, ensuring reliable processing of database transactions.

8. What is a CROSS APPLY in SQL?

Answer: CROSS APPLY is used to join two tables where the right table function depends on the left table, allowing for complex queries where each row in the left table is processed independently.

9. How do you implement a queue in SQL using a table?

Answer: A queue can be implemented using a table with columns for data and a status flag, with operations to enqueue (INSERT) and dequeue (SELECT with DELETE).

10. Explain database deadlock and how to prevent it.

Answer: A deadlock occurs when two or more transactions are waiting for each other to release resources, causing them to be stuck indefinitely. Deadlocks can be prevented by using locking mechanisms, setting timeouts, and ensuring consistent ordering of resource requests.

Practical/Scenario-Based Questions (10 Questions)

1. Write a SQL query to find the nth highest salary from the employees table.

Answer:

sql

Copy code

SELECT salary

FROM (

SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rank

FROM employees

) AS ranked_salaries

WHERE rank = n;

2. How would you design a database schema for a library management system?

Answer: A library management system schema would include tables like Books, Authors, Members, Loans, and Reservations, with appropriate relationships such as foreign keys between Books and Authors.

3. Write a SQL query to retrieve all employees who have the same job title as their manager.

Answer:

sql

Copy code

SELECT e.employee_name

FROM employees e

JOIN employees m ON e.manager_id = m.employee_id

WHERE e.job_title = m.job_title; 

4. How do you handle duplicate records in a table?

Answer: Duplicates can be managed by identifying and deleting them using ROW_NUMBER() with PARTITION BY in a CTE or using DISTINCT to filter them out.

5. Write a SQL query to find the second highest salary in each department.

Answer:

sql

Copy code

SELECT department_id, MAX(salary)

FROM employees

WHERE salary < (SELECT MAX(salary) FROM employees e2 WHERE e2.department_id = employees.department_id)

GROUP BY department_id;

6. How would you implement auditing in a SQL database?

Answer: Auditing can be implemented using triggers to log changes to tables into an audit table with details like OLD and NEW values, user information, and timestamps.

7. Write a SQL query to calculate a running total of sales for each day.

Answer:

sql

Copy code

SELECT sales_date, sales_amount,

SUM(sales_amount) OVER (ORDER BY sales_date) AS running_total

FROM sales; 

8. How would you optimize a query that is taking too long to execute?

Answer: Optimization techniques include adding indexes, rewriting the query for better performance, avoiding unnecessary columns in SELECT, and reviewing execution plans.

9. Write a SQL query to pivot data from rows to columns.

Answer:

sql

Copy code

SELECT *

FROM (

SELECT category, item, value

FROM sales

) AS source_table

PIVOT (

SUM(value)

FOR item IN ([Item1], [Item2], [Item3])

) AS pivot_table;

10. Explain how you would design an ETL (Extract, Transform, Load) process for a data warehouse.

Answer: The ETL process involves extracting data from source systems, transforming it to fit operational needs (including cleaning, aggregating, and summarizing), and loading it into the data warehouse.

Data Analytics with Power Bi and Fabric
Could Data Engineer
Data Analytics With Power Bi Fabic
AWS Data Engineering with Snowflake
Azure Data Engineering
Azure & Fabric for Power bi
Full Stack Power Bi

Social Media channels

► KSR Datavizon Website :- https://www.datavizon.com
► KSR Datavizon LinkedIn :- https://www.linkedin.com/company/datavizon/
► KSR Datavizon You tube :- https://www.youtube.com/c/KSRDatavizon
► KSR Datavizon Twitter :- https://twitter.com/ksrdatavizon
► KSR Datavizon Instagram :- https://www.instagram.com/ksr_datavision
► KSR Datavizon Face book :- https://www.facebook.com/KSRConsultingServices
► KSR Datavizon Playstore :- https://play.google.com/store/apps/details?id=com.datavizon.courses&hl=en-IN
► KSR Datavizon Appstore :- https://apps.apple.com/in/app/ksr-datavizon/id1611034268

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *