Top SQL interview Questions in 2023

Lets see some most asked SQL interview questions in this post. Use this freely and comment below if you have some sql questions to share.

1. What is SQL?

• SQL stands for Structured Query Language and is used to communicate with relational database management systems (RDBMS) to create, read, update, and delete data stored in the database.

2. What are the different types of SQL commands?

• There are several types of SQL commands, including:
  • Data Definition Language (DDL): commands used to define the database schema, such as CREATE, ALTER, and DROP.
  • Data Manipulation Language (DML): commands used to manipulate data within the database, such as SELECT, INSERT, UPDATE, and DELETE.
  • Data Control Language (DCL): commands used to control access to the database, such as GRANT and REVOKE.
  • Transaction Control Language (TCL): commands used to manage transactions, such as COMMIT and ROLLBACK.

3. What is a primary key in SQL?

• A primary key is a column or set of columns in a table that uniquely identifies each row in the table. Primary keys are used to enforce uniqueness and prevent duplicate data.

4. What is a foreign key in SQL?

• A foreign key is a column or set of columns in a table that refers to the primary key of another table. Foreign keys are used to establish relationships between tables and enforce referential integrity.

5. What is a join in SQL?

• A join is a SQL operator that combines rows from two or more tables based on a related column between the tables. There are several types of joins, including inner join, outer join, and cross join.

6. What is a subquery in SQL?

• A subquery is a SELECT statement that is nested within another SELECT, INSERT, UPDATE, DELETE, or SET statement. Subqueries are used to return data to be used in the main query as a condition or to retrieve data from multiple tables.

7. What is a self join in SQL?

• A self join is a type of join that allows a table to be joined to itself. This is often used to compare values in a table to other values in the same table.

8. What is a view in SQL?

• A view is a virtual table that is created based on a SELECT statement. Views do not store data themselves, but rather display data stored in the database. Views can be used to simplify complex queries or to restrict access to certain data.

9. What is an index in SQL?

• An index is a performance-tuning method used to quickly locate data without having to search every row in a table. Indexes can be created on one or more columns of a table, and can be either unique (i.e., no two rows can have the same index value) or non-unique.

10. What is a stored procedure in SQL?

• A stored procedure is a precompiled set of SQL statements that can be called by name and executed as a single unit. Stored procedures can accept input parameters and return multiple output parameters. They are used to encapsulate logic and improve performance.

11. What is a trigger in SQL?

• A trigger is a special type of stored procedure that is executed automatically when an event occurs in the database, such as the insertion, update, or deletion of data. Triggers can be used to enforce data integrity, implement business logic, or audit data changes.

12. What is a transaction in SQL?

• A transaction is a logical unit of work that contains one or more SQL statements. Transactions are used to ensure that a database remains in a consistent state by

13. What is normalization in SQL?

• Normalization is the process of organizing a database to minimize redundancy and dependency. Normalization involves decomposing a table into smaller, more focused tables, and establishing relationships between them using foreign keys. The goal of normalization is to create a more flexible, scalable, and maintainable database design.

14. What is denormalization in SQL?

• Denormalization is the process of intentionally introducing redundancy into a database design in order to improve performance. Denormalization is often used when the cost of joining tables is too high, or when the data needs to be accessed in a way that is not easily supported by the normalized design.

15. What is a database schema in SQL?

• A database schema is the overall structure of a database, including the tables, columns, relationships, and other database objects. The schema defines how the data is organized and how it relates to other data within the database.

16. What is a database normal form in SQL?

• A database normal form is a rule or set of rules that defines the level of normalization of a database. The most common normal forms are first normal form (1NF), second normal form (2NF), and third normal form (3NF). Higher levels of normalization generally result in a more efficient and flexible database design, but may come at the cost of increased complexity and slower performance.

17. What is a database constraint in SQL?

• A database constraint is a rule that is enforced by the database management system (DBMS) to ensure the integrity and correctness of the data. Constraints can be used to specify the type of data that can be stored in a column, the allowed values for a column, or the relationships between columns in different tables.

18. What is a database trigger in SQL?

• A database trigger is a special type of stored procedure that is automatically executed by the DBMS in response to a specific event, such as the insertion, update, or deletion of data. Triggers can be used to enforce data integrity, implement business logic, or audit data changes.

19. What is a database view in SQL?

• A database view is a virtual table that is created based on a SELECT statement. Views do not store data themselves, but rather display data stored in the database. Views can be used to simplify complex queries or to restrict access to certain data.

20. What is a database cursor in SQL?

• A database cursor is a control structure that allows traversal over the rows in a result set. Cursors are used to process rows one at a time and are often used in conjunction with loops to perform repeated actions on the data.

21. What is a database index in SQL?

• A database index is a performance-tuning method used to quickly locate data without having to search every row in a table. Indexes can be created on one or more columns of a table, and can be either unique (i.e., no two rows can have the same index value) or non-unique.

22. What is a database transaction in SQL?

• A database transaction is a logical unit of work that contains one or more SQL statements. Transactions are used to ensure that a database remains in a consistent state by committing or rolling back the changes made by the SQL statements.

23. What is a database foreign key in SQL?

• A database foreign key is a column or set of columns in a table that refers to the primary key of another table. Foreign keys are used to establish relationships between tables and enforce referential integrity.

Few complex SQL interview questions with answers:

24. How can you generate a range of dates in SQL?

• One way to generate a range of dates in SQL is to use a recursive common table expression (CTE). For example:

WITH dates (date) AS (
  SELECT '2020-01-01' AS date
  UNION ALL
  SELECT date + INTERVAL 1 DAY
  FROM dates
  WHERE date < '2020-01-31'
)
SELECT * FROM dates;

This CTE generates a list of dates from January 1st to January 31st.

25. How can you pivot rows to columns in SQL?

• To pivot rows to columns in SQL, you can use the CASE statement and GROUP BY clause. For example:

SELECT month,
  SUM(CASE WHEN product = 'Product A' THEN sales ELSE 0 END) AS "Product A",
  SUM(CASE WHEN product = 'Product B' THEN sales ELSE 0 END) AS "Product B",
  SUM(CASE WHEN product = 'Product C' THEN sales ELSE 0 END) AS "Product C"
FROM sales
GROUP BY month;

This query pivots the rows in the sales table by month, with the product names as the columns and the sum of sales as the values.

26. How can you calculate running totals in SQL?

• To calculate running totals in SQL, you can use the SUM() function and a self-join. For example:

SELECT t1.date, t1.sales,
  (SELECT SUM(t2.sales) FROM sales t2 WHERE t2.date <= t1.date) AS running_total
FROM sales t1;

This query calculates the running total of sales by date, using a self-join to sum all the sales up to and including the current date.

27. How can you remove duplicates from a table in SQL?

• To remove duplicates from a table in SQL, you can use the DISTINCT keyword in a SELECT statement. For example:

SELECT DISTINCT column1, column2, column3 FROM table;

This query returns all unique rows from the table, based on the values in column1, column2, and column3.

If you want to delete the duplicates from the table permanently, you can use the DELETE statement with a self-join:

DELETE t1
FROM table t1
INNER JOIN table t2
  ON t1.column1 = t2.column1 AND t1.column2 = t2.column2 AND t1.column3 = t2.column3
WHERE t1.id > t2.id;

This query deletes all the duplicates from the table, leaving only the first occurrence of each set of duplicates.

28. How can you find the second highest value in a column in SQL?

• To find the second highest value in a column in SQL, you can use a combination of the MAX() and LIMIT functions. For example:

SELECT MAX(column) FROM table WHERE column < (SELECT MAX(column) FROM table);

This query returns the second highest value in the column of the table.

here are a few more complex SQL interview questions:

29. How can you generate a rank for rows in a table in SQL?

• To generate a rank for rows in a table in SQL, you can use the RANK() function. For example:

SELECT *, RANK() OVER (ORDER BY column1 DESC, column2 ASC) FROM table;

This query generates a rank for each row in the table based on the values in column1 (in descending order) and column2 (in ascending order).

30. How can you find the Nth highest value in a column in SQL?

• To find the Nth highest value in a column in SQL, you can use a combination of the LIMIT and ORDER BY clauses. For example:

SELECT column FROM table ORDER BY column DESC LIMIT N-1, 1;

This query returns the Nth highest value in the column of the table.

31. How can you perform a full-text search in SQL?

• To perform a full-text search in SQL, you can use the MATCH() function in conjunction with the AGAINST() function. For example:

SELECT * FROM table WHERE MATCH(column) AGAINST('search term');

This query searches for rows in the table where the column contains the search term using a full-text index.

32. How can you generate a random sample of rows from a table in SQL?

• To generate a random sample of rows from a table in SQL, you can use the RAND() function in conjunction with the LIMIT clause. For example:

SELECT * FROM table ORDER BY RAND() LIMIT N;

This query selects N random rows from the table.

33. How can you pivot multiple columns in SQL?

• To pivot multiple columns in SQL, you can use the CASE statement and GROUP BY clause. For example:

SELECT month, region,
  SUM(CASE WHEN product = 'Product A' THEN sales ELSE 0 END) AS "Product A",
  SUM(CASE WHEN product = 'Product B' THEN sales ELSE 0 END) AS "Product B",
  SUM(CASE WHEN product = 'Product C' THEN sales ELSE 0 END) AS "Product C"
FROM sales
GROUP BY month, region;

This query pivots the rows in the sales table by month and region, with the product names as the columns and the sum of sales as the values.

SQL interview questions with answers on table partitioning

34. What is table partitioning in SQL?

• Table partitioning is a method of dividing a large table into smaller, more manageable pieces called partitions. Partitions can be based on a column or set of columns, such as date or location, and can be used to improve the performance, scalability, and maintainability of a database.

35. What are the benefits of table partitioning in SQL?

• Some benefits of table partitioning in SQL include:
  • Improved query performance: Partitions can be created and accessed independently, allowing queries to run faster by accessing only the relevant data.
  • Enhanced scalability: Large tables can be partitioned into smaller pieces, making it easier to scale the database as the data grows.
  • Ease of maintenance: Partitions can be added, dropped, or truncated independently, making it easier to perform maintenance tasks such as archiving or purging data.
  • Improved availability: Partitions can be placed on different storage devices or servers, allowing the database to remain available even if one partition is offline.

36. What are the different types of table partitioning in SQL?

• There are several types of table partitioning in SQL, including:
  • Range partitioning: partitions data based on a range of values, such as date or id.
  • List partitioning: partitions data based on a list of specific values, such as country or region.
  • Hash partitioning: partitions data based on a hash value calculated from a column or set of columns, such as name or email.
  • Composite partitioning: combines two or more types of partitioning, such as range and hash partitioning.

37. How do you create a table partition in SQL?

• To create a table partition in SQL, you can use the CREATE TABLE statement with the PARTITION BY clause. For example:

CREATE TABLE sales (
  id INT NOT NULL AUTO_INCREMENT,
  date DATE NOT NULL,
  product VARCHAR(255) NOT NULL,
  sales INT NOT NULL,
  PRIMARY KEY (id),
  KEY (date)
)
PARTITION BY RANGE (YEAR(date))
(
  PARTITION p0 VALUES LESS THAN (2021),
  PARTITION p1 VALUES LESS THAN (2022),
  PARTITION p2 VALUES LESS THAN (2023)
);

This creates a table called sales with three range partitions based on the year of the date column.

38. How do you add a partition to an existing table in SQL?

• To add a partition to an existing table in SQL, you can use the ALTER TABLE statement with the ADD PARTITION clause. For example:

ALTER TABLE sales ADD PARTITION (PARTITION p3 VALUES LESS THAN (2024));

This adds a new partition called p3 to the sales table for values less than 2024.

39. How do you drop a partition from an existing table in SQL?

• To drop a partition from an existing table in SQL, you can use the ALTER TABLE statement with the DROP PARTITION clause. For example:

ALTER TABLE sales DROP PARTITION p3;

This drops the partition called p3 from the sales table.

Some SQL analytical interview questions

40. How can you calculate the moving average of a column in SQL?

• To calculate the moving average of a column in SQL, you can use the AVG() function with a self-join. For example:

SELECT t1.date, t1.value,
  (SELECT AVG(t2.value) FROM table t2 WHERE t2.date BETWEEN t1.date - INTERVAL 7 DAY AND t1.date) AS moving_avg
FROM table t1;

This query calculates the moving average of the value column by date, using a self-join to average the values over the past 7 days.

41. How can you calculate the cumulative sum of a column in SQL?

• To calculate the cumulative sum of a column in SQL, you can use the SUM() function with a self-join. For example:

SELECT t1.date, t1.value,
  (SELECT SUM(t2.value) FROM table t2 WHERE t2.date <= t1.date) AS cum_sum
FROM table t1;

This query calculates the cumulative sum of the value column by date, using a self-join to sum all the values .

SQL queries using complex aggregate functions:

• Find the average, minimum, and maximum salary of all employees:

SELECT AVG(salary) AS avg_salary, MIN(salary) AS min_salary, MAX(salary) AS max_salary
FROM employees;

• Find the number of employees in each department, along with the average salary of those employees:

SELECT department, COUNT(*) AS num_employees, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

• Find the total salary paid to all employees in each department, along with the average salary of all employees in the company:

SELECT department, SUM(salary) AS total_salary,
    (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees
GROUP BY department;

• Find the top 10 employees with the highest salary:

SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 10;

• Write a query to find the names of all employees who have a salary greater than the average salary of their department.

Answer:

Copy codeSELECT e.name
FROM employees e
JOIN (SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department) d
ON e.department = d.department
WHERE e.salary > d.avg_salary;

• Question: Write a query to find the names of all employees who have a salary greater than the average salary of all employees in the company.

Answer:

Copy codeSELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

• Question: Write a query to find the names and salaries of all employees who have a higher salary than the employee with the highest salary in their department.

Answer:

Copy codeSELECT e1.name, e1.salary
FROM employees e1
JOIN (SELECT department, MAX(salary) AS max_salary FROM employees GROUP BY department) e2
ON e1.department = e2.department
WHERE e1.salary > e2.max_salary;

• Question: Write a query to find the names of all employees who have the same salary as the employee with the highest salary in their department.

Answer:

Copy codeSELECT e1.name
FROM employees e1
JOIN (SELECT department, MAX(salary) AS max_salary FROM employees GROUP BY department) e2
ON e1.department = e2.department
WHERE e1.salary = e2.max_salary;

interview questions