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 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
| StudId | Name | EnrollmentNo | DateOfJoining |
|---|---|---|---|
| 11 | Nick Panchal | 1234567 | 01/02/2019 |
| 21 | Yash Panchal | 2468101 | 15/03/2017 |
| 31 | Gyan Rathod | 3689245 | 27/05/2018 |
Table – StudentStipend
| StudId | Project | Stipend |
|---|---|---|
| 11 | P1 | 80000 |
| 21 | P2 | 10000 |
| 31 | P1 | 120000 |
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');Ans. We can use the SELECT query to select data from the SQL database table.
SELECT * FROM StudentDetails WHERE Studid = '31';Ans. We can use the UPDATE query to update data into the SQL database table.
UPDATE StudentStipend SET Project = 'P3' WHERE Studid = '31';Ans. We can use the DROP query to drop the SQL database table.
DROP TABLE StudentStipend;Ans. We can use the TRUNCATE query to delete data from the SQL database table.
TRUNCATE TABLE StudentDetails;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);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;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);Ans. Here, we can use the aggregate function count() with the SQL WHERE clause.
SELECT COUNT(*) FROM StudentStipend WHERE Project = 'P1';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;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);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;Ans. We can perform the required operation using the SELECT INTO query.
SELECT * INTO newTable FROM StudentDetails;Ans. We can fetch a joint record between two tables using INTERSECT clause as mentioned below.
SELECT * FROM StudentStipend
INTERSECT
SELECT * FROM EnrollmentDetailsAns. We can add the MINUS clause to exclude some rows from the resultant rows as mentioned below.
SELECT * FROM StudentStipend
MINUS
SELECT * FROM EnrollmentDetailAns. 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;Ans. We can use MySQL LIKE clause with CREATE statement.
CREATE TABLE newTable LIKE StudentDetails; 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();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 = 0Ans. 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';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 ASCUsing limit clause(MySQL)
SELECT Stipend FROM StudentStipend ORDER BY Stipend DESC LIMIT N-1,1;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 DESCIn Oracle using ROWNUM
SELECT * FROM (SELECT * FROM StudentStipend ORDER BY Stipend DESC)
WHERE ROWNUM <= 3;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;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 = 1Ans. 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
Embed Link: https://errorsea.com/wp-content/uploads/2020/08/TOP-5-SQL-Query-Interview-Questions.png
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 🙂
There is a reason big-name companies like CNN use WordPress. WordPress is a popular content…
In this tutorial, I'm going to show you how to install MySQL on your computer.…
Download Turbo C++ for windows 10 in just 7 Mb and run your first C++…
We can redirect any webpage to any other or redirect the whole domain or website…
There are lots of methods to redirect pages, like refresh-redirect from META tag, redirect from…
Include files in PHP are used in appending various global or config files. We can…