Top SQL Interview Questions and Answers

Explore this curated list of Top SQL interview questions and answers to enhance your readiness for SQL-related job interviews. From fundamental concepts to advanced, these questions cover key topics like database, SQL structure and syntax, joins, subqueries,views, stored procedure and more. Use this resource to sharpen your SQL skills and excel in your next interview

Table of Contents

Top SQL Interview Questions and Answers

What is SQL ?

SQL stands for Structured Query Language. It is a Database programming language used for managing and manipulating relational databases.

What is a database?

A database is an organized collection of data stored and accessed electronically. It provides a way to store, organize, and retrieve large amounts of data efficiently.

What is a primary key ?

A primary key is a column that uniquely identifies each row in a table. It enforces the entity integrity rule in a relational database

What is a foreign key?

A foreign key is a column or combination of columns that establishes a link between data in two tables. It ensures referential integrity by enforcing relationships between tables

What is the difference between a primary key and a unique key?

A primary key is used to uniquely identify a row in a table and must have a unique value. On the other hand, a unique key ensures that a column or combination of columns has a unique value but does not necessarily identify the row.

What is normalization?

Normalization is the process of organizing data in a database to minimize redundancy and dependency. In normalization, a large table is broken down into smaller tables, and relationships are established between them.

What are the different types of normalization?

The different types of normalization are:

First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Fourth Normal Form (4NF)
Fifth Normal Form (5NF)
Boyce-Codd Normal Form (BCNF)

What is a join in SQL?

A join is an operation used to combine rows from two or more tables based on related columns. It allows you to retrieve data from multiple tables simultaneously.

What is the difference between DELETE and TRUNCATE in SQL?

The DELETE statement is used to remove specific rows from a table based on a condition. It can be rolled back and generates individual delete operations for each row.

On the other hand, TRUNCATE is used to remove all rows from a table.This operation cannot be undone and is more efficient than DELETE because it deallocates data pages instead of logging each individual row deletion.

What is the difference between UNION and UNION ALL?

UNION and UNION ALL are used to combine the result sets of two or more SELECT statements.

UNION removes duplicate rows from the combined result set. Whereas UNION ALL includes all rows, including duplicates.

What is the difference between the HAVING clause and the WHERE clause?

The WHERE clause is used to filter rows based on a condition before the data is grouped or aggregated. It operates on individual rows.

The HAVING is used to filter grouped rows based on a condition after the data is grouped or aggregated using the GROUP BY clause.

What is a transaction in SQL?

A transaction is a sequence of SQL statements that are executed as a single logical unit of work.
It maintains data consistency and integrity by either applying all changes (committing) or reverting them (rolling back) in case of errors or failures.

What is the difference between a clustered and a non-clustered index?

A clustered index determines the physical order of data in a table. It changes the way the data is stored on disk and can be created on only one column. A table can have only one clustered index.

A non-clustered index does not affect the physical order of data in a table. It is stored separately and contains a pointer to the actual data. A table can have multiple non-clustered indexes.

What is ACID properties in the context of database transactions?

ACID stands for Atomicity, Consistency, Isolation, and Durability. It’s a set of properties that ensure reliable processing of database transactions.

Atomicity guarantees that a transaction is treated as a single unit of work, ensuring that either all changes within the transaction are applied successfully or none of them are applied, preserving data integrity.

Consistency ensures that a transaction brings the database from one valid state to another.

Isolation ensures that concurrent transactions do not interfere with each other.

Durability ensures that once a transaction is committed, its changes are permanent and survive system failures.

What is a deadlock?

A deadlock happens when multiple transactions are each waiting for resources held by the others, leading to a circular dependency where none of the transactions can proceed. This situation causes a system deadlock, potentially resulting in unresponsiveness or performance issues.

What is the difference between a database and a schema?

A database is a container that holds multiple objects, such as tables, views, indexes, and procedures. It represents a logical grouping of related data.

A schema is a container within a database that holds objects and defines their ownership. It provides a way to organize and manage database objects

What is the difference between a temporary table and a table variable?

A temporary table is a table that is created and exists only for the duration of a session or a transaction. It can be explicitly dropped or is automatically dropped when the session or transaction ends.

A table variable is a variable that can store a table-like structure in memory and is limited to a specific scope within a batch, stored procedure, or function. It is automatically freed or deallocated when its scope ends, making it convenient for temporary data storage without creating a physical database table.

What is the purpose of the GROUP BY clause?

The GROUP BY clause is used to group rows of data based on specific columns in a table. It allows you to organize and summarize data by common values in those columns. This clause is often used with aggregate functions (like SUM, AVG, COUNT) to perform calculations on each group of data defined by the grouped columns.

What is the difference between CHAR and VARCHAR data types?

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

What is a SQL stored procedure?

A stored procedure is a set of SQL statements that are stored in the database and can be executed repeatedly. It provides code reusability and better performance

What is a subquery?

A subquery is a query nested inside another query. It is used to retrieve data based on the result of an inner query.

What is a view?

A view is a virtual table based on the result of an SQL statement. It allows users to retrieve and manipulate data.

What is the difference between a cross-join and an inner join?

A cross join (Cartesian product) returns the combination of all rows from two or more tables.

An inner join returns only the matching rows based on a join condition.

What is the purpose of the COMMIT statement?

The COMMIT statement is used to save changes made in a transaction permanently. It ends the transaction and makes the changes visible to other users

What is the purpose of the ROLLBACK statement?

The ROLLBACK statement is used to undo changes made in a transaction. It reverts the database to its previous state before the transaction started

What is the purpose of the NULL value in SQL?

NULL represents the absence of a value or unknown value. It is different from zero or an empty string and requires special handling in SQL queries

What is the difference between a view and a materialized view?

A materialized view is a physical copy of the view’s result set stored in the database, which is updated periodically. It improves query performance at the cost of data freshness.

What is a correlated subquery?

A correlated subquery is a subquery that refers to a column from the outer query. It executes once for each row processed by the outer query.

What is the purpose of the DISTINCT keyword?

The DISTINCT keyword is used to retrieve unique values from a column or combination of columns in a SELECT statement.

What is the difference between the CHAR and VARCHAR data types?

CHAR stores fixed-length character strings, while VARCHAR stores variable-length character strings. The storage size of CHAR is constant, while VARCHAR adjusts dynamically.

What is the difference between the IN and EXISTS operators?

The IN operator checks for a value within a set of values or the result of a subquery. The EXISTS operator checks for the existence of rows returned by a subquery.

What is the purpose of the TRIGGER statement?

A TRIGGER statement is used to link a set of SQL statements with a specific event in the database. This set of statements is automatically executed whenever the associated event occurs, such as an INSERT, UPDATE, or DELETE operation on a table.

What is the difference between a unique constraint and a unique index?

A unique constraint ensures the uniqueness of values in one or more columns, while a unique index enforces the uniqueness and also improves query performance.

What is the purpose of the TOP or LIMIT clause?

The TOP clause in SQL Server or the LIMIT clause in MySQL is used to restrict the number of rows returned by a query. This allows you to specify how many rows should be returned from the beginning of the result set.

What is the difference between the UNION and JOIN operators?

UNION combines the result sets of two or more SELECT statements vertically, while JOIN combines columns from two or more tables horizontally based on a join condition

What is a data warehouse?

A data warehouse is a large, centralized storage system that collects and organizes data from different sources. It’s specifically built to support reporting, analysis, and business intelligence tasks effectively.

What is the difference between a primary key and a candidate key?

A primary key is a chosen candidate key that uniquely identifies a row in a table.

A candidate key is a group of one or more columns that can uniquely identify a record in a table and could potentially serve as the primary key.

What is the purpose of the GRANT statement?

The GRANT statement is used to grant specific permissions or privileges to users or roles in a database.

What is the purpose of the CASE statement?

The CASE statement is used to perform conditional logic in SQL queries. It allows you to return different values based on specified conditions.

What is the purpose of the ROW_NUMBER() function?

The ROW_NUMBER() function assigns a unique incremental number to each row in the result set. It is commonly used for pagination or ranking purposes

What is the difference between a natural join and an inner join?

A natural join is an inner join that matches rows based on columns with the same name in the joined tables. It is automatically determined by the database.

What is the purpose of the CASCADE DELETE constraint?

The CASCADE DELETE constraint is used to automatically delete related rows in child tables when a row in the parent table is deleted

What is a self-join?

A self-join is a join operation where a table is joined with itself. It is useful when you want to compare rows within the same table based on related columns

What is an ALIAS command?

An alias in SQL is a temporary name assigned to a table or a column. It allows you to refer to the table or column using a different name within the context of a query. Aliases are often used in the WHERE clause to specify conditions involving specific tables or columns.