Top SQL Interview Questions and Answers (2024)

In this article we have gathered the latest and most asked SQL Interview Questions and Answers for both freshers and experienced. The SQL Interview Questions cover the topics related to questions about SQL syntax and commands, data manipulation and aggregation, database design, and data analysis etc. It’s important to be prepared to answer these questions and to have a good understanding of key concepts in order to demonstrate your skills and knowledge of SQL.

SQL (Structured Query Language) is a programming language used to manage and manipulate data stored in relational databases. Whether you’re a beginner who learn SQL and looking to break into the field of data science. Or an experienced professional seeking to advance your career, having a strong command of SQL is essential. In this post, we’ll cover some of the most common SQL interview questions and provide tips on how to answer them effectively.

These SQL Interview Questions will help job seekers to prepare well for the job interview and cross the panel discussion easily.

So let’s get started :

1. What is SQL and what is it used for?

SQL (Structured Query Language) is a programming language used to manage and manipulate data stored in relational databases. It is used for a wide range of tasks, including creating and modifying tables and databases, inserting and updating data, and querying data for analysis and reporting.

2. What is the difference between a database and a table?

A database is a collection of data organized into tables, which are made up of rows and columns. A table represents a specific set of data, such as a list of employees or a list of customer orders. Tables are related to one another through keys, which allow them to be linked and queried together.

3. How do you select all columns and rows from a table?

To select all columns and rows from a table, you can use the SELECT * statement:

SELECT *
FROM table_name;

4. How do you select specific columns from a table?

To select specific columns from a table, you can specify the column names in the SELECT clause:

SELECT column1, column2, column3
FROM table_name;

5. How do you sort the results of a query in ascending or descending order?

To sort the results of a query in ascending order, you can use the ORDER BY clause with the ASC keyword:

SELECT column1, column2
FROM table_name
ORDER BY column1 ASC;

To sort the results in descending order, you can use the DESC keyword:

SELECT column1, column2
FROM table_name
ORDER BY column1 DESC;

6. How do you use the WHERE clause to filter query results?

The WHERE clause allows you to specify conditions to filter the rows returned by a query. For example:

SELECT column1, column2
FROM table_name
WHERE column1 = 'value';

This query would return all rows where column1 is equal to ‘value’. You can use a variety of operators in the WHERE clause, such as =, <, >, <=, >=, and LIKE, to specify different types of conditions.

7. How do you insert a new row into a table?

To insert a new row into a table, you can use the INSERT INTO statement:

INSERT INTO table_name (column1, column2, column3)
VALUES ('value1', 'value2', 'value3');

8. How do you update data in a table?

To update data in a table, you can use the UPDATE statement with the SET and WHERE clauses:

UPDATE table_name
SET column1 = 'new_value'
WHERE column2 = 'value';

This query would update column1 to ‘new_value’ for all rows where column2 is equal to ‘value’.

9. How do you delete data from a table?

To delete data from a table, you can use the DELETE FROM statement with the WHERE clause:

DELETE FROM table_name
WHERE column1

10. How do you use the GROUP BY clause to aggregate data?

The GROUP BY clause allows you to group rows together based on a common column value. For example:

SELECT column1, COUNT(*) as num_rows
FROM table_name
GROUP BY column1;

This query would return a count of the number of rows for each unique value in column1.

11. How do you use the HAVING clause to filter grouped results?

The HAVING clause allows you to specify conditions for the groups created by the GROUP BY clause. For example:

SELECT column1, COUNT(*) as num_rows
FROM table_name
GROUP BY column1
HAVING num_rows > 10;

This query would return a count of the number of rows for each unique value in column1, but only for groups with more than 10 rows.

12. What is a join and how many types of joins are there in SQL?

A join is a way to combine rows from two or more tables based on a common column. There are several types of joins in SQL, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

13. Can you explain the difference between a INNER JOIN and a LEFT JOIN?

An INNER JOIN returns only rows that have a match in both tables being joined. A LEFT JOIN returns all rows from the left table and any matching rows from the right table. If there is no match, NULL values are returned for the right table’s columns. For example:

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id

This query would return a list of employees and the departments they work in, but only for employees who are assigned to a department.

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id

This query would return a list of all employees and the departments they work in, including employees who are not assigned to a department (NULL values would be returned for the department_name column in these cases).

14. How do you use the UNION operator to combine data from multiple tables?

The UNION operator allows you to combine the results of two or more SELECT statements into a single result set. The SELECT statements must have the same number of columns and the columns must be of the same or compatible data types. For example:

SELECT column1, column2
FROM table1
UNION
SELECT column1, column2
FROM table2;

This query would return a combined list of rows from table1 and table2, with duplicates removed.

15. What is a NULL value in SQL and how do you test for it?

A NULL value in SQL represents a missing or unknown value. It is different from an empty string or a zero value. To test for a NULL value, you can use the IS NULL or IS NOT NULL operator in a WHERE clause:

SELECT * FROM table
WHERE col IS NULL;

SELECT * FROM table
WHERE col IS NOT NULL;

16. How do you create a new table from a SELECT query?

To create a new table from a SELECT query, you can use the CREATE TABLE AS (CTAS) syntax:

CREATE TABLE new_table AS
SELECT * FROM old_table;

You can also specify the column names and data types for the new table, as well as any constraints:

CREATE TABLE new_table (
  col1 datatype,
  col2 datatype,
  ...
  PRIMARY KEY (col1)
) AS
SELECT col1, col2, ... FROM old_table;

17. How do you use the GROUP BY clause in a SELECT statement?

The GROUP BY clause is used to group rows with similar values into sets. It is often used in conjunction with aggregate functions such as COUNT, AVG, MIN, and MAX to calculate summary statistics for each group.

For example, to find the total number of orders placed by each customer, you could use the following query:

SELECT customer_id, COUNT(*) AS num_orders
FROM orders
GROUP BY customer_id;

18. What is a correlated subquery and how is it different from a regular subquery?

A correlated subquery is a subquery that depends on the outer query for its values. It is evaluated once for each row in the outer query. In contrast, a regular subquery is evaluated independently of the outer query and its results are used by the outer query.

For example, the following query uses a correlated subquery to find the names of employees who have a higher salary than their manager:

SELECT e1.name AS employee_name, e2.name AS manager_name
FROM employees e1
JOIN employees e2

19. What is a window function and how do you use it in a SELECT statement?

A window function is a function that operates on a set of rows and returns a single result for each row. It is used to perform calculations across rows, similar to an aggregate function. However, unlike an aggregate function, a window function does not group the rows together.

To use a window function in a SELECT statement, you must specify an OVER() clause. The OVER() clause defines the rows over which the function will operate.

For example, to find the running total of orders by customer, you could use the SUM() window function and the OVER() clause:

SELECT customer_id, order_date,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
FROM orders;

20. What is a self-join and when would you use one?

A self-join is a join that involves two copies of the same table. It is used to compare rows within the same table, or to create a hierarchical structure.

For example, to find the names and manager names of all employees, you could use a self-join on the “employees” table:

SELECT e1.name AS employee_name, e2.name AS manager_name
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;

21. What is a window function and how do you use it in a SELECT statement?

To use a window function in a SELECT statement, you must specify an OVER() clause. The OVER() clause defines the rows over which the function will operate.

For example, to find the running total of orders by customer, you could use the SUM() window function and the OVER() clause:

SELECT customer_id, order_date,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
FROM orders;

22. How do you use the CASE statement in SQL?

The CASE statement is used to create conditional logic in a SELECT, UPDATE, or DELETE statement. It allows you to evaluate a series of conditions and return a different value for each condition.

For example, to assign a letter grade to each student based on their score, you could use the following CASE statement:

SELECT name, score,
       CASE
         WHEN score >= 90 THEN 'A'
         WHEN score >= 80 THEN 'B'
         WHEN score >= 70 THEN 'C'
         WHEN score >= 60 THEN 'D'
         ELSE 'F'
       END AS grade
FROM students;

23. How do you use the EXPLAIN or EXPLAIN ANALYZE command to optimize a query?

The EXPLAIN and EXPLAIN ANALYZE commands are used to analyze the execution plan of a SELECT, INSERT, UPDATE, or DELETE statement. They provide information about how the query is executed, including the order in which tables are joined, the indices used, and the number of rows processed.

To use the EXPLAIN or EXPLAIN ANALYZE command, you simply prefix the query with EXPLAIN or EXPLAIN ANALYZE:

EXPLAIN SELECT * FROM table WHERE col = 'value';

EXPLAIN ANALYZE SELECT * FROM table WHERE col = 'value';

The EXPLAIN ANALYZE command provides more detailed information and also executes the query, so it can be slower than the EXPLAIN command.

24. What is a common table expression (CTE) and how do you use it in a SELECT statement?

A common table expression (CTE) is a named temporary result set that you can use within a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. It can be used to simplify complex queries by breaking them up into smaller, more manageable pieces.

To use a CTE in a SELECT statement, you can use the WITH clause followed by the CTE definition:

WITH cte AS (
  SELECT col1, col2, ...
  FROM table
  WHERE condition
)
SELECT * FROM cte;

25. How do you use the LATERAL JOIN clause in a SELECT statement?

The LATERAL JOIN clause is used to join a table to a function that returns a set of rows. It allows the function to reference columns from the joined table in its WHERE clause.

To use the LATERAL JOIN clause, you can use the following syntax:

SELECT *
FROM table1
LATERAL JOIN function() ON condition;

26. What is a recursive CTE and how do you use it in a SELECT statement?

A recursive common table expression (CTE) is a CTE that references itself, allowing it to return a hierarchical tree-like structure. It is useful for querying data that has a recursive relationship, such as an organizational chart or a bill of materials.

To use a recursive CTE, you must define two queries within the CTE: a base query and a recursive query. The base query returns the initial set of rows, while the recursive query returns the subsequent rows by referencing the CTE itself.

For example, to find the hierarchical structure of an organizational chart, you could use the following recursive CTE:

WITH cte AS (
  -- Base query
  SELECT employee_id, name, manager_id
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  -- Recursive query
  SELECT e.employee_id, e.name, e.manager_id
  FROM employees e
  INNER JOIN cte ON e.manager_id = cte.employee_id
)
SELECT * FROM cte;

27. How do you use the INSERT INTO SELECT statement to copy data from one table to another?

The INSERT INTO SELECT statement is used to copy data from one table to another. It allows you to insert the results of a SELECT query into a table.

To use the INSERT INTO SELECT statement, you can use the following syntax:

INSERT INTO table1 (col1, col2, ...)
SELECT col1, col2, ...
FROM table2
WHERE condition;

You can also use the INSERT INTO SELECT statement to insert data from multiple tables or to insert data into a table with a different structure.

For example, to copy all the rows from the “old_table” to the “new_table”, you could use the following query:

INSERT INTO new_table (col1, col2, ...)
SELECT col1, col2, ...
FROM old_table;

28. How do you use the UPDATE FROM SELECT statement to update multiple rows in a table?

The UPDATE FROM SELECT statement is used to update multiple rows in a table based on the results of a SELECT query. It allows you to specify the source of the data and the target table in a single statement.

To use the UPDATE FROM SELECT statement, you can use the following syntax:

UPDATE table1
SET col1 = (SELECT col1 FROM table2 WHERE condition),
    col2 = (SELECT col2 FROM table2 WHERE condition),
    ...
WHERE condition;

You can also use the UPDATE FROM SELECT statement to update data from multiple tables or to update data in a table with a different structure.

For example, to update the salary of all employees based on their job title, you could use the following query:

UPDATE employees
SET salary = (SELECT salary FROM job_titles WHERE employees.title = job_titles.title)
WHERE salary IS NULL;

Thank you for reading this SQL interview questions and answers. We hope that it has provided you with some useful information and helped you prepare for your next SQL interview. These are just a few of the many SQL interview questions that you may encounter during a job interview. Whether you are a beginner, intermediate, or advanced SQL user, it is important to be familiar with the basic concepts, as well as more advanced features and techniques. By reviewing and practicing these SQL Interview questions, you can increase your knowledge of SQL and improve your chances of success in a job interview.

All The Best!

5/5 - (1 vote)