Top 25 SQL Queries Interview Questions

If you are preparing for the database administrator (DBA) job you are at the perfect place. This post contains some mostly asked SQL queries interview questions. This post starts from some basic queries to advanced SQL queries.

Table – StudentDetails

StudIdNameEnrollmentNoDateOfJoining
11Nick Panchal123456701/02/2019
21Yash Panchal246810115/03/2017
31Gyan Rathod368924527/05/2018

Table – StudentStipend

StudIdProjectStipend
11P180000
21P210000
31P1120000

Index

1) Write a SQL query to insert a new student detail in StudentDetails table.

Ans. Using Insert-

INSERT INTO StudentDetails(StudId,Name,EnrollmentNo,DateOfJoining) Values('51','Ashish Patel','2468653','09/03/2019');

2) Write a SQL query to Select a specific student detail in StudentDetails table.

Ans. Using Select-

SELECT * FROM StudentDetails WHERE Studid = '31';

3) Write a SQL query to update a project detail in StudentStipend table.

Ans. Using Update-

UPDATE StudentStipend SET Project = 'P3' WHERE Studid = '31';

4) Write a SQL query to drop a StudentStipend table with its structure.

Ans. Using Drop-

DROP TABLE StudentStipend;

5) Write a SQL query to delete only StudentDetails table data.

Ans. Using Truncate-

TRUNCATE TABLE StudentDetails;

6) Write a SQL query to fetch student names having stipend greater than or equal to 50000 and less than or equal 100000.

Ans. Here, we can use BETWEEN in the ‘where’ clause to return the StudId of the student with stipend satisfying the required criteria and then use it as subquery to find the Name of the student form StudentDetails table.

SELECT Name 
 FROM StudentDetails 
 WHERE StudId IN 
 (SELECT StudId FROM StudentStipend 
 WHERE Stipain BETWEEN 50000 AND 100000);

7) Write a query to fetch student names and stipend records. Return student details even if the stipend record is not present for the student.

Ans. Here, we can use left join with StudentDetail table on the left side.

SELECT St.Name, S.Stipend  
 FROM StudentDetails St LEFT JOIN StudentStipend S
 ON St.StudId = S.StudId;

8) Write a SQL query to fetch all student records from StudentDetails table who have a stipend record in StudentStipend table.

Ans. Using ‘Exists’-

SELECT * FROM StudentDetails St 
 WHERE EXISTS 
 (SELECT * FROM StudentStipend S WHERE  St.StudId = S.StudId);

9) Write a SQL query to fetch the number of students working in project ‘P1’.

Ans. Here, we can use aggregate function count() with the SQL where clause-

SELECT COUNT(*) FROM StudentStipend WHERE Project = 'P1';

10) Write a SQL query for fetching duplicate records from a table.

Ans. To find duplicate records from the table, we can use GROUP BY function on all the fields and then we have to use HAVING clause to return only those fields whose count is greater than one, i.e. the rows having duplicate records.

SELECT StudId, Project, Stipend, COUNT() FROM StudentStipend GROUP BY StudId, Project, Stipend HAVING COUNT() > 1;

11) Write a SQL query for removing duplicates from a table without using a temporary table.

Method 1: Using Group By and Having clause-

DELETE FROM StudentStipend  
 WHERE StudId IN (
 SELECT StudId 
 FROM  StudentStipend       
 GROUP BY Project, Stipend 
 HAVING COUNT(*) > 1));

Method 2: Using rowId in Oracle-

DELETE FROM StudentStipend 
 WHERE rowid NOT IN
 (SELECT MAX(rowid) FROM StudentStipend  GROUP BY StudId);

12) Write a SQL query for fetching all the Students who also have enrollment No from StudentDetails table.

Ans. Here, we can use Self-Join as the requirement wants us to analyze the StudentDetails table as two different tables, each for Student and enrollment records.

SELECT DISTINCT S.Name
 FROM StudentDetails S
 INNER JOIN StudentDetails E
 ON S.StudId = E.EnrollmentNo;

13) Write a SQL query for creating a new table with data and structure copied from another table.

Ans. Using SELECT INTO command-

SELECT * INTO newTable FROM StudentDetails;

14) Write a SQL query to fetch a joint record between two tables using intersect.

Ans.

SELECT * FROM StudentStipend
 INTERSECT
 SELECT * FROM EnrollmentDetails

15) Write a SQL query for fetching records that are present in one table but not in another table using Minus.

Ans.

SELECT * FROM StudentStipend
 MINUS
 SELECT * FROM EnrollmentDetail

16) Write a SQL query to fetch count of students project-wise sorted by project’s count in descending order.

Ans. This query, we have to first to fetch the project-wise count and then to sort the result by that count. For project-wise count, we will be using GROUP BY clause, and then we will use ORDER BY clause for sorting, on the alias of the project-count.

SELECT Project, count(StudId) StudProjectCount 
 FROM StudentStipend
 GROUP BY Project 
 ORDER BY StudProjectCount DESC;

17) Write a SQL query for creating an empty table with the same structure as some other table.

Ans. We can use MySQL ‘Like’ command with CREATE statement-

CREATE TABLE newTable LIKE StudentDetails; 

18) Write a SQL query for finding current date-time.

In Oracle:

 SELECT SYSDATE FROM DUAL;

In SQL Server:

SELECT getdate();

In MySQL:

SELECT NOW();

19) Write a SQL query for fetching only even rows from the table.

Ans. We can achieve this using Row_number in SQL server-

SELECT St.StudId, St.Project, St.Stipend
 FROM (
     SELECT *, Row_Number() OVER(ORDER BY StudId) AS RowNumber
     FROM StudentStipend
 ) St
 WHERE St.RowNumber % 2 = 0

20) Write a SQL query for fetching all the Students details from StudentDetails table who joined in the Year 2018.

Ans. Using BETWEEN for the date range ’01-01-2018′ AND ’31-12-2018′-

SELECT * FROM StudentDetails
 WHERE DateOfJoining BETWEEN '01-01-2018' AND date '31-12-2018';

Also, we can extract the year part from the joining date (using YEAR in MySQL)-

SELECT * FROM StudentDetails
 WHERE YEAR(DateOfJoining) = '2018';

21) Write the SQL query to find the nth highest stipend from the table.

Using Top keyword (SQL Server)-

SELECT TOP 1 Stipend
 FROM (
       SELECT DISTINCT TOP N Stipend
       FROM StudentStipend
       ORDER BY Stipend DESC
       )
 ORDER BY Stipend ASC

Using limit clause(mySQL)-

SELECT Stipend FROM StudentStipend ORDER BY Stipend DESC LIMIT N-1,1;

22) Write a SQL query for fetching top n records using LIMIT?

In MySQL:

SELECT * FROM StudentStipend ORDER BY Stipend DESC LIMIT N;

In SQL server using the TOP command:

SELECT TOP N * FROM StudentStipend ORDER BY Stipend DESC

In Oracle using ROWNUM:

SELECT * FROM (SELECT * FROM StudentStipend ORDER BY Stipend DESC)
 WHERE ROWNUM <= 3;

23) Write a query for fetching only the first name(string before space) from the Name column of StudentDetails table.

Ans. Here, we have to first fetch the location of the space character in the Name field and then we can extract the first name out of the Name field using LOCATE method in MySQL, CHARINDEX in SQL SERVER, and for fetching the string before space, we will use SUBSTRING OR MID method.

MySQL- Using MID

SELECT MID(Name, 0, LOCATE(' ',Name)) FROM StudentDetails;

SQL Server-Using SUBSTRING

SELECT SUBSTRING(Name, 0, CHARINDEX(' ',Name)) FROM StudentDetails;

We can also use LEFT which returns the left part of a string till specified number of characters.

SELECT LEFT(Name, CHARINDEX(' ',Name) - 1) FROM StudentDetails;

24) Write a SQL query for fetching only odd rows from the table.

Ans. We can achieve this using Row_number in SQL server-

SELECT St.StudId, St.Project, St.Stipend
 FROM (
     SELECT *, Row_Number() OVER(ORDER BY StudId) AS RowNumber
     FROM StudentStipend
 ) St
 WHERE St.RowNumber % 2 = 1

25) Write SQL query for finding the 3rd highest Stipend from the table without using TOP/limit keyword.

Ans. We have to use of correlated subquery for finding the 3rd highest Stipend the inner query will return us the count of till we find that there are two rows that Stipend higher than other distinct stipends.

SELECT Stipend
 FROM StudentStipend Stud1
 WHERE 2 = (
                 SELECT COUNT( DISTINCT ( Stud2.Stipend ) )
                 FROM StudentStipend Stud2
                 WHERE Stud2.Stipend > Stud1.Stipend
             )

For nth highest stipend-

SELECT Stipend
 FROM StudentStipend Stud1
 WHERE N-1 = (
                 SELECT COUNT( DISTINCT ( Stud2.Stipend ) )
                 FROM StudentStipend Stud2
                 WHERE Stud2.Stipend > Stud1.Stipend
             )

Leave a Reply

Your email address will not be published. Required fields are marked *

Read previous post:
top 25 html interview questions
Top 25 HTML Interview Questions

1) What is HTML? [HTML], Hypertext Markup Language is used to design and display pages on the Web. It makes...

Close