Asked SQL query interview questions

1. Retrieve all records from a table:

Query:

SELECT * FROM table_name;

2. Retrieve unique values from a column:

Query:

SELECT DISTINCT column_name FROM table_name;

3. Count the number of records in a table:

Query:

SELECT COUNT(*) FROM table_name;

4. Sort records in descending order:

Query:

SELECT column_name FROM table_name

ORDER BY column_name DESC;

5. Find the maximum value in a column:

Query:

SELECT MAX(column_name) FROM table_name;

6. Retrieve records based on a condition:

Query:

SELECT column1, column2 FROM table_name

WHERE condition;

7. Calculate the average value in a column:

Query:

SELECT AVG(column_name) FROM table_name;

8. Join two tables:

Query:

SELECT t1.column_name, t2.column_name

FROM table1 AS t1

JOIN table2 AS t2

ON t1.common_column = t2.common_column;

9. Group records and apply an aggregate function:

Query:

SELECT department, AVG(salary)

FROM employees

GROUP BY department;

10. Find records with NULL values:

Query:

SELECT column_name FROM table_name

WHERE column_name IS NULL;

11. Insert a new record into a table:

Query:

INSERT INTO table_name (column1, column2, column3)

VALUES (value1, value2, value3);

12. Update records based on a condition:

Query:

UPDATE table_name

SET column_name = new_value

WHERE condition;

13. Delete records based on a condition:

Query:

DELETE FROM table_name

WHERE condition;

14. Find records with a specific pattern in a column:

Query:

SELECT column_name FROM table_name

WHERE column_name LIKE 'pattern%';

15. Get the N highest or lowest values from a column:

Query (N highest):

SELECT column_name

FROM table_name

ORDER BY column_name DESC

LIMIT N;

Query (N lowest):

SELECT column_name

FROM table_name

ORDER BY column_name ASC

LIMIT N;

16. Find the second-highest (or Nth highest)

value in a column:

Query (Second-highest):

SELECT DISTINCT column_name

FROM table_name

ORDER BY column_name DESC

LIMIT 1 OFFSET 1;

This query retrieves the second-highest value

in the specified column.

17. Calculate the total of a specific column

based on a condition:

Query:

SELECT SUM(column_name) FROM table_name

WHERE condition;

This query calculates the sum of values in a

specific column that meet the specified condition.

18. Retrieve records with values falling within a

range: Query:

SELECT column_name FROM table_name

WHERE column_name BETWEEN value1 AND value2;

This query retrieves records where the values

in the specified column fall within a specified

range.

19. Find records that match a specific value

in multiple columns: Query:

SELECT column1, column2 FROM table_name

WHERE column1 = 'value1' AND column2 = 'value2';

This query retrieves records where both

column1 and column2

match specific values.

20. Retrieve records based on multiple conditions

using logical operators:

Query:

SELECT column_name FROM table_name

WHERE condition1 AND (condition2 OR condition3);

This query retrieves records that meet

condition1

and either

condition2

or

condition3

.

21. Find the records with the top N values for

each category:

Query (Top N values per category):

SELECT category, column_name

FROM (

SELECT category, column_name,

ROW_NUMBER() OVER (PARTITION BY category

ORDER BY column_name DESC) AS rnum

FROM table_name

) ranked

WHERE rnum <= N;

This query retrieves the top N values for

each category in the specified table.

22. Calculate the difference between two columns

in a result set:

Query:

SELECT column1, column2, (column1 - column2)

AS difference

FROM table_name;

This query calculates the difference between

column1

and

column2

and labels it as "difference" in the result set.

23. Retrieve distinct values from multiple columns:

Query:

SELECT DISTINCT column1, column2 FROM table_name;

This query retrieves unique combinations of

values from both

column1

and

column2

.

24. Find records that exist in one table but

not in another (Set Difference):

Query:

SELECT column_name FROM table1

EXCEPT

SELECT column_name FROM table2;

This query returns records from

table1

that do not exist in

table2

.

25. Calculate the median of a column's values:

Query:

SELECT AVG(column_name) AS median

FROM (

SELECT column_name

FROM table_name

ORDER BY column_name

LIMIT 2 - (SELECT COUNT(*) FROM table_name) % 2

OFFSET (SELECT (COUNT(*) - 1) / 2 FROM table_name)

);

This query calculates the median of values

in the specified column.

26. Retrieve the last N records in a table:

Query (Last N records):

SELECT * FROM table_name

ORDER BY column_name DESC

LIMIT N;

This query retrieves the last N records from the table, ordered by a specific column in descending order.

27. Use a self-join to find related records

within the same table:

Query (Self-join):

SELECT e1.employee_name, e2.supervisor_name

FROM employees AS e1

JOIN employees AS e2

ON e1.supervisor_id = e2.employee_id;

This query performs a self-join to find employees and their respective supervisors.

28. Retrieve records that match any

value in a list:

Query:

SELECT column_name FROM table_name

WHERE column_name IN ('value1', 'value2', 'value3');

This query retrieves records where the column_name

matches any of the specified values in the list.

29. Calculate the running total

(cumulative sum) of a column's values:

Query (Running Total):

SELECT column_name, SUM(column_name)

OVER (ORDER BY some_column) AS running_total

FROM table_name;

This query calculates the running total of values in the specified column, ordered by another column.

30. Find the records with the highest

value in each group:

  Query (Top record per group):

SELECT column1, MAX(column2) AS max_column2

FROM table_name

GROUP BY column1;

This query retrieves the record with the highest

column2 value for each group of column1

.

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'