Top Most SQL Query Interview Questions With Answers

Certainly! Here are 50 SQL interview questions along with their answers:

1. What is SQL?

• SQL stands for Structured Query Language, which is a domain-specific language used for managing and manipulating relational databases.

2. What is a Relational Database Management System (RDBMS)?

• An RDBMS is a software system used to manage relational databases, such as MySQL, PostgreSQL, Oracle, and SQL Server.

3. What is a table in SQL?

• A table is a database object that stores data in rows and columns.

4. What is a primary key?

• A primary key is a unique identifier for each row in a table. It ensures that each row is unique.

5. What is a foreign key?

• A foreign key is a field in a table that is a primary key in another table. It establishes a link between two tables.

6. What is SQL injection?

• SQL injection is a security vulnerability that occurs when an attacker can manipulate SQL queries through input fields, potentially gaining unauthorized access to a database.

7. Explain the SELECT statement.

• The SELECT statement is used to retrieve data from one or more tables in a database.

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

• The WHERE clause filters rows before the data is grouped and aggregated, while the HAVING clause filters rows after the data is grouped and aggregated.

9. What is a JOIN clause in SQL?

• A JOIN clause is used to combine rows from two or more tables based on a related column between them.

10. What are the different types of SQL JOINs?

• INNER JOIN, LEFT JOIN (or LEFT OUTER JOIN), RIGHT JOIN (or RIGHT OUTER JOIN), and FULL JOIN (or FULL OUTER JOIN).

11. Explain the difference between UNION and UNION ALL.

• UNION removes duplicate rows from the result set, while UNION ALL includes all rows, including duplicates.

12. What is a subquery?

• A subquery is a query embedded within another query. It is often used to retrieve data that will be used by the main query.

13. What is an index in SQL?

• An index is a database structure that improves the speed of data retrieval operations on a table.

14. What is normalization?

• Normalization is the process of organizing data in a database to reduce data redundancy and improve data integrity.

15. What is a stored procedure?

• A stored procedure is a precompiled collection of one or more SQL statements that can be executed as a single unit.

16. What is a trigger?

• A trigger is a database object that automatically executes a specified action when a certain event (e.g., INSERT, UPDATE, DELETE) occurs in a table.

17. What is ACID in the context of a database?

• ACID stands for Atomicity, Consistency, Isolation, and Durability. It is a set of properties that guarantee the reliability of database transactions.

18. Explain the difference between a primary key and a unique key.

• A primary key is used to uniquely identify each row in a table and cannot contain NULL values. A unique key ensures that the values in a column or set of columns are unique but allows NULL values.

19. What is a self-join?

• A self-join is a type of join where a table is joined with itself. It is often used when data in the same table needs to be compared or related.

20. How can you retrieve the first N rows from a table in SQL?

• You can use the LIMIT clause in MySQL or PostgreSQL and the TOP clause in SQL Server to retrieve the first N rows.

21. Explain the GROUP BY clause.

• The GROUP BY clause is used to group rows from a table based on the values in one or more columns, typically for aggregation purposes using functions like SUM, COUNT, AVG, etc.

22. What is a correlated subquery?

• A correlated subquery is a subquery that depends on values from the outer query. It is executed for each row processed by the outer query.

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

• A clustered index determines the physical order of data in a table, and there can be only one per table. A non-clustered index is a separate structure that stores a copy of the data in a specified order.

24. How do you add a new record to a table?

• You can use the INSERT INTO statement to add a new record to a table.

25. How do you update data in a table?

• You can use the UPDATE statement to modify existing data in a table.

26. How do you delete data from a table?

• You can use the DELETE FROM statement to remove data from a table.

27. Explain the concept of a view in SQL.

• A view is a virtual table that is based on the result of a SELECT query. It allows you to simplify complex queries and restrict access to certain columns or rows of a table.

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

• A cross join (or Cartesian join) returns the Cartesian product of two tables, while an inner join returns only the rows that have matching values in both tables.

29. What is a self-contained subquery?

• A self-contained subquery is a subquery that does not depend on the outer query and can be executed independently.

30. What is the purpose of the SQL CASE statement?

• The CASE statement is used to perform conditional logic in SQL queries, similar to the IF-THEN-ELSE construct in other programming languages.

31. What is a database index and why is it important?

• An index is a data structure that improves the speed of data retrieval operations on a table. It is important because it helps reduce query execution time.

32. How do you retrieve unique values from a column?

• You can use the DISTINCT keyword in a SELECT statement to retrieve unique values from a column.

33. What is a composite key?

• A composite key is a key that consists of multiple columns to uniquely identify rows in a table.

34. How do you sort the result set of a query in descending order?

• You can use the ORDER BY clause with the DESC keyword to sort the result set in descending order.

35. What is a CTE (Common Table Expression)?

• A CTE is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It helps simplify complex queries.

36. Explain the concept of data normalization.

• Data normalization is the process of organizing data in a database to reduce data redundancy and improve data integrity. It involves dividing a database into two or more tables and defining relationships between them.

37. What is the purpose of the SQL WHERE clause?

• The WHERE clause is used to filter rows in a query based on a specified condition.

38. How do you perform an outer join in SQL?

Some Others Very Most Important Interview Question - My Notes 

1. GET HIGHEST SALARY OF EMPLOYEE

SELECT TOP 1 * FROM EmployessDetails WHERE EMP_SALARY IN  (SELECT TOP 1 EMP_SALARY FROM EmployessDetails ORDER BY EMP_SALARY DESC)

2. GET SECOND HIGHEST SALARY OF EMPLOYEE

SELECT TOP 1 * FROM EmployessDetails WHERE EMP_SALARY IN  (SELECT TOP 2 EMP_SALARY FROM EmployessDetails ORDER BY EMP_SALARY DESC)

3. GET LOWEST SALARY OF EMPLOYEE

SELECT TOP 1 * FROM EmployessDetails WHERE EMP_SALARY IN  (SELECT TOP 1 EMP_SALARY FROM EmployessDetails ORDER BY EMP_SALARY ASC)

ORDER BY EMP_SALARY DESC  

4. GET SECOND LOWEST SALARY OF EMPLOYEE

SELECT TOP 1 * FROM EmployessDetails WHERE EMP_SALARY IN  (SELECT TOP 2 EMP_SALARY FROM EmployessDetails ORDER BY EMP_SALARY ASC) ORDER BY EMP_SALARY DESC 

5. GET TOP ONE ROW OF EMPLOYEE

SELECT TOP 1* FROM EmployessDetails

6. GET TOP SECOND ROW OF EMPLOYEES

SELECT TOP 1* FROM EmployessDetails WHERE EMP_ID IN  (SELECT TOP 2 EMP_ID FROM EmployessDetails ORDER BY EMP_ID ASC)  ORDER BY EMP_ID DESC

7. GET LAST ROW OF EMPLOYEE

SELECT TOP 1* FROM EmployessDetails ORDER BY EMP_ID DESC

8. GET LAST SECOND OF EMPLOYEE

SELECT TOP 1* FROM EmployessDetails WHERE EMP_ID IN  (SELECT TOP 2 EMP_ID FROM EmployessDetails ORDER BY EMP_ID DESC)  ORDER BY EMP_ID ASC

9. GET AFTER  15 DAY OF DATE FROM CURRENT DATE

SELECT DATEADD(DAY,15,GETDATE())

10. GET BEFORE  15 DAY OF DATE FROM CURRENT DATE

SELECT DATEADD(DAY,-15,GETDATE())

11. GET AFTER  10 MONTH OF MONTH FROM CURRENT MONTH

SELECT DATEADD(MONTH,10,GETDATE())

12. GET DIFFERENCE BETWEEN TWO DATE

SELECT DATEDIFF(DAY,'2020/05/20','2020/05/25')

13. GET TOTAL NO OF MALE IN EACH DEP 

SELECT DEP_NAME COUNT(GENDER) FROM EmployessDetails WHERE GENDER ='MALE' GROUP BY DEP_NAME

13.1 GET TOTAL NO OF MALE IN ALL DEP 

SELECT COUNT (GENDER) FROM EMP_DETAIL

WHERE GENDER='MALE'

14. DISPLAY ALL EMPLOYEE WHO NOT WORKING IN ANY PROJECT 

SELECT EMP_ID FROM EmployessDetails WHERE IS PROJECT IS NULL

15. GET CURRENT DATA SELECT

 GETDATE()

16. GET EVEN ROW

SELECT * FROM EmployessDetails WHERE EMP_ID%2=0

17. GET ODD ROW

SELECT * FROM EmployessDetails WHERE EMP_ID%2=1

18. GET DUPLICATE ROW

SELECT EMP_ID,COUNT(*)   FROM EmployessDetails GROUP BY EMP_ID HAVING COUNT(*)>1

19. DELETE DUPLICATE ROW

WITH CTENAME 

AS(

SELECT EMP_ID ,EMP_NAME,ROW_NUMBER()  over(partition by  emp_id ,EMP_NAME order by emp_id) abc from EmployessDetails

)

delete from CTENAME where abc>1

20. GET NO OF EMPLOYEE  WHO WORKING IN HR DEPARTMENTS

SELECT dep_name ,COUNT(*) No_Of_Emp FROM DEPARTMENT where DEP_NAME='HR' group by DEP_NAME

21. GET WHO EMPLOYEE JOINING IN 1990

SELECT * FROM EmployessDetails WHERE DATEPART(YEAR,EMP_JOINING_YEAR)='1900'

22. GET WHO EMPLOYEE JOINING IN 07 MONTH

SELECT * FROM EmployessDetails WHERE DATEPART(MONTH,EMP_JOINING_YEAR)='07'

23. GET COMMON RECARD FROM BOTH TABLE

select * from EmployessDetails

INTERSECT

select * from DEPARTMENT

24. DISPLAY EMP_ID THAT PRESENT IN FIRST TABLE BUT NOT IN SECOND TABLE

SELECT * FROM EmployessDetails WHERE EMP_ID NOT IN (SELECT EMP_ID FROM DEPARTMENT)

25. CREATE  NEW TABLE TO EXISTING TABLE (ONLY  TABLE STRUCTURE)

SELECT * INTO VNCS FROM EmployessDetails WHERE 1=0

26. CREATE  NEW TABLE TO EXISTING TABLE (WITH RECORD) 

SELECT * INTO VNCSNEW FROM EmployessDetails

27. CREATE  NEW TABLE TO EXISTING TABLE (WITH SPECIFIC RECORD)

SELECT * INTO VNCSNEW2 FROM DEPARTMENT WHERE EMP_ID=1

28. EXCHANGE VALUE LIKE MALE TO FEMALE AND FEMALE TO MALE ?? 

UPDATE EmployessDetails SET Gender=CASE Gender WHEN 'MALE' THEN 'FEMALE' WHEN 'FEMALE' THEN 'MALE' END

29. GET SAME SALARY OF EMPLOYEE 

SELECT * FROM EmployessDetails  WHERE EMP_SALARY IN (SELECT EMP_SALARY FROM EmployessDetails GROUP BY EMP_SALARY HAVING COUNT(1)>1)

30. GET 50% RECORD FROM TABLE

SELECT  * FROM DEPARTMENT WHERE EMP_ID <=(SELECT COUNT(EMP_ID)/2 FROM EmployessDetails)

31. GET EMP DETAILS WHO JOINING AFTER  31 JANUARY 2018 

SELECT * FROM  EmployessDetails WHERE EMP_JOINING_YEAR>'01/31/2018'

32. GET EMP DETAILS WHO JOINING BEFORE  31 JANUARY 2018 

SELECT * FROM  EmployessDetails WHERE EMP_JOINING_YEA<01/31/2018'

33. Get Highest Salary from Two Table Using Join

select e_name,e_salary from emp_info a

inner join emp_salary b on a.emp_id=b.emp_id

where e_salary in (select max(e_salary) from emp_salary)

34. SQL Query to Find Monthly Salary of Employee If Annual Salary is Given

SELECT emp_name, round(emp_An_salary/12,2) AS 'Monthly Salary' ,

emp_An_Salary AS 'Annual Salary' FROM GFG_salary

35. SQL Query to Find the Highest Salary of Each Department

SELECT DEPT_ID, MAX(SALARY) FROM department GROUP BY DEPT_ID;