Top SQL Interview Questions And Answers (2024)

SQL is very important from an interview point of view. In this post, I will cover basic database and SQL interview questions and answers for Freshers and experienced professionals.

1) What is SQL?

Structured Query Language (SQL) is a domain-specific language that is used to manage and manipulate relational databases.

2) What is a Database?

A database is a structured collection of data stored in tables, where each table consists of rows and columns. The database helps in storing, accessing and collecting data.

3) What is a primary key?

A primary key is a unique key used to uniquely identify a record in the database. The primary key can not be null or duplicate.

CREATE TABLE Users (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

4) What is a foreign key?

A foreign key is a field that establishes a link between two tables. It references the primary key of another table.

5) What is the difference between BETWEEN and IN operators in SQL? 

BETWEEN Operator: The BETWEEN operator is used to filter rows within a specified range. It’s typically used with numerical or date values. The syntax for the BETWEEN the operator is as follows:

SELECT column_name
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

IN Operator: The IN is used to match values against a list of predefined values. It’s commonly used when you want to filter rows based on multiple possible values for a single column. The syntax for the IN operator is as follows:

SELECT column_name
FROM table_name
WHERE column_name IN (value1, value2, ...);

6) What is a surrogate key in SQL?

A surrogate key is a key that is used to uniquely identify a record in the table when the primary key is not available or the primary key can not be formed.

7) How to create a database in SQL?

To create a database in SQL use the below query.

CREATE DATABASE database_name

8) What is database Schema?

A database schema is a logical container that defines the structure, organization, relationships, and constraints of a database. It serves as a blueprint or design for how data is stored, organized, and accessed within a database management system (DBMS). For example, refer to the sample schema for an e-commerce application.

1.Users Table:
Columns: user_id (Primary Key), username, email, password_hash, registration_date
This table stores information about registered users of the platform.

2.Products Table:
Columns: product_id (Primary Key), product_name, description, price, stock_quantity
This table stores details about the products available for purchase, including their names, descriptions, prices, and stock levels.

3.Orders Table:
Columns: order_id (Primary Key), user_id (Foreign Key), order_date, total_amount
This table keeps track of orders placed by users, recording the date of the order and the total amount spent.

4.Addresses Table:
Columns: address_id (Primary Key), user_id (Foreign Key), street_address, city, state, postal_code
This table stores shipping addresses associated with users.

5.Reviews Table:
Columns: review_id (Primary Key), user_id (Foreign Key), product_id (Foreign Key), rating, review_text, review_date
Users can leave reviews and ratings for products they have purchased.

9) What are the constraints in the Database?

Constraints are the rules and the conditions which are applied to the data to maintain data integrity. For example “Not Null” Constraint, means that a particular column can not accept null values. Constraints have to be defined at the time of table creation.

10) What is the GROUP BY clause used for?

The GROUP BY clause is used to group rows based on specified columns and perform aggregate functions like SUM, COUNT, AVG, etc.

11) What is the difference between a candidate key and a composite key?

A candidate key is a single column that can uniquely identify a row, while a composite key consists of multiple columns used together as a unique identifier.

12) What is join? Explain different Joins.

Joins are used to Join two or more tables based on a related column. There are mainly 4 types of joins available.

1. Self-join: This join is used to combine a table with itself.
2. Inner-Join: This join is used to combine two or more tables based on matching columns. This results in the matching records from both tables.
3. Outer Join: There are three types of outer joins. The left outer joined fetches all the rows from the left table even if there is no match on the right table. The right outer join fetches all the rows from the right table even if there is no match on the left table. Full outer joins fetch records even if there is a match in either table.
4. Cross Join: Provides the Cartesian product of the sets of records from the two or more joined tables.

13) What is the difference between Union and Union all?

Union/Union All is used to combine the records of two or more tables. The only difference is that Union All keeps duplicate records while UNION does not.

14) How to select all records from a Table?

By using Select Query.

SELECT * FROM Table_Name

15) What is a Subquery?

A subquery is defined as a query within another query.

SELECT product_name
FROM products
WHERE price > (SELECT AVG(price) FROM products);

This is a normal subquery as it does not depend on the outer query value.

16) What is a correlated subquery?

A correlated subquery is a subquery that depends on values from the outer query, usually using aliases to establish the relationship.

SELECT order_id, user_id, total_amount
FROM orders o1
WHERE total_amount > (SELECT AVG(total_amount) FROM orders o2 WHERE o2.user_id = o1.user_id);

In the above query, the subquery depends on the outer query.

17) What is the difference between delete, drop and truncate commands?

1. Delete command removes data row by row from the table based on the specified condition. It is slower than the ‘Truncate’ command’ and retains table structure.

DELETE FROM table_name WHERE condition;

2. The DROP command is used to remove database objects, such as tables, indexes, or views, from the database and it can not be rolled back.

DROP TABLE table_name;

3. Truncate command deletes all rows from a table but retains the table structure. It is slightly faster than the ‘Delete’ command.

TRUNCATE TABLE table_name;

18) List some commonly used aggregate functions.

Sum(): This function returns the sum of all values of a numeric column.

SELECT SUM(column_ name) FROM table_name; 

AVG() function calculates the average of all the values of a particular numeric column and returns it.

SELECT AVG(column_name) FROM table_name; 

Min() & Max(): These functions return the minimum and maximum values.

SELECT MIN(column_name) FROM table_name; 
SELECT MAX(column_name) FROM table_name; 

19) How to calculate the length of the Text in SQL?

Use the Length() or len() function whichever is applicable.

SELECT LENGTH(string) FROM table_name; 

20) How will you add two columns and make a single column For example add FirstName and LastName and make a new column as Full Name.

We have a concat() function in SQL, which will join two or more strings.

Select concat(fname,lname) as FullName from StudentDetails;

21) How to extract Day from Date in SQL?

SELECT EXTRACT(Day FROM '2023-08-01') // To extract Day
SELECT EXTRACT(Month FROM '2023-08-01') // To extract Month

22) What is the difference between SQL and No SQL database?

SQL databases or relational databases use predefined schema while No SQL databases have dynamic schema or no schema. Examples of SQL databases are MySQL, PostgreSQL, Oracle Database, and Microsoft SQL Server. No SQL databases are MongoDB, Cassandra, Couchbase, Redis, and Amazon DynamoDB.