Top 25 SQL Queries Interview Questions for Experienced (Free PDF Download)

If you are preparing for the database administrator (DBA) job?

If yes, you are at the perfect spot for getting the right SQL Queries Interview Questions and topics that can help you to get your dream job. These questions and answers are handpicked for freshers as well as experienced.

Index

SQL Queries Interview Questions and Answers

SQL is the most popular data management query language. In addition, it supports relational data, which helps to manage huge amount of data with required relations.

The given below SQL interview questions requires some data for explanation of SQL queries. You can refer the following data tables for examples.

Table – StudentDetails

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

Table – StudentStipend

StudIdProjectStipend
11P180000
21P210000
31P1120000

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

Ans. We can use the INSERT query to insert data into SQL database table.

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

Write an SQL query to select a specific student detail in StudentDetails table.

Ans. We can use the SELECT query to select data from the SQL database table.

SELECT * FROM StudentDetails WHERE Studid = '31';

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

Ans. We can use the UPDATE query to update data into the SQL database table.

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

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

Ans. We can use the DROP query to drop the SQL database table.

DROP TABLE StudentStipend;

Write an SQL query to delete only StudentDetails table data.

Ans. We can use the TRUNCATE query to delete data from the SQL database table.

TRUNCATE TABLE StudentDetails;

Write an 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);

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 as St LEFT JOIN StudentStipend as S
    ON St.StudId = S.StudId;

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

Ans. We can do the required operation using EXISTS clause in the SQL query.

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

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

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

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

Write an SQL query for fetching duplicate records from a table.

Ans. To find duplicate records from the table, we can use GROUP BY clause 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;

Write an 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);

Write an 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;

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

Ans. We can perform the required operation using the SELECT INTO query.

SELECT * INTO newTable FROM StudentDetails;

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

Ans. We can fetch a joint record between two tables using INTERSECT clause as mentioned below.

SELECT * FROM StudentStipend
    INTERSECT
    SELECT * FROM EnrollmentDetails

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

Ans. We can add the MINUS clause to exclude some rows from the resultant rows as mentioned below.

SELECT * FROM StudentStipend
    MINUS
    SELECT * FROM EnrollmentDetail

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

Ans. In the mentioned below query first, we fetch the project-wise count and then sort the result by count. For project-wise count, we use GROUP BY clause, and then we use ORDER BY clause for sorting operation, on the alias of the project-count.

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

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

Ans. We can use MySQL LIKE clause with CREATE statement.

CREATE TABLE newTable LIKE StudentDetails; 

Write an SQL query for finding current date-time.

Ans. SQL queries for various Databases are as described below.

SQL Query In Oracle

SELECT SYSDATE FROM DUAL;

SQL Query In SQL Server

SELECT getdate();

SQL Query In MySQL

SELECT NOW();

Write an 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

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

Ans. We can perform the required operation 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';

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

Ans. SQL queries to find the nth highest stipend form the table for various Databases are as described below.

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;

Write SQL query for fetching top n records using LIMIT?

Ans. SQL queries for fetching top n records using LIMIT for various Databases are as described below.

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;

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;

Write an 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

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
    )

Read Also: MySQL Interview Questions and Answers For Freshers

TOP 5 SQL Query Interview Questions

Embed Link: https://errorsea.com/wp-content/uploads/2020/08/TOP-5-SQL-Query-Interview-Questions.png

Conclusion

This is all about SQL Query interview questions with complete answers. I am sure it will help you in cracking the Database administrator (DBA) interview as an experienced or a fresher, and it will increase your confidence, too.

I hope you found this post fully informative and helpful.

Thank you for reading 🙂

Leave a Reply

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