Top 30 SQL Query Interview Questions

Top 30 SQL Query Interview Questions

5 mins read41.5K Views Comment
clickHere
Updated on Sep 19, 2023 17:55 IST

Structured Query Language or most commonly known as SQL is used on a daily basis to handle, manipulate and analyze relational databases.

2022_03_Feature-Image-Templates-46.jpg

So, it is very important for all of us to understand how to write queries in SQL to generate meaningful insights. This article will cover the top 30 SQL query interview questions which you must practice before attending an interview. In this article, I am going to consider the following tables to explain to you the most asked SQL query interview questions.

Patients Table

Patient ID Patient Name Sex Age Address Postal Code State Country RegDate DoctorID
01 Sheela F 23 Flat no 201, Vasavi Heights, Yakutapura  500023 Telangana India 03/03/2020 142
02 Rehan M 21 Building no 2, Yelahanka 560063 Karnataka India 13/11/2020 211
03 Anay M 56 H No 1, Panipat 132140 Haryana India 12/12/2021 142
04 Mahira F 42 House no 12, Gandhinagar 382421 Gujarat India 28/01/2022 345
05 Nishant M 12 Sunflower Heights, Thane 400080 Maharashtra India 05/01/2022 131

PatientsCheckup Table

Patient ID BP Weight Consultation Fees
01 121/80 67 300
02 142/76 78 400
03 151/75 55 300
04 160/81 61 550
05 143/67 78 700

Top SQL Interview Questions(Query)

Let us get started with the top interview questions about SQL.

Q1.  Write an SQL query to fetch the current date-time from the system.

 
TO fetch the CURRENT DATE IN SQL Server
SELECT GETDATE();
TO fetch the CURRENT DATE IN MYSQL
SELECT NOW();
TO fetch the CURRENT DATE IN Oracle
SELECT SYSDATE FROM DUAL();
Copy code

Q2. Write a SQL query to fetch the PatientName in uppercase and state as lowercase. Also use the ALIAS name for the result-set as PatName and NewState.

 
TO fetch the CURRENT DATE IN SQL Server
SELECT GETDATE();
TO fetch the CURRENT DATE IN MYSQL
SELECT NOW();
TO fetch the CURRENT DATE IN Oracle
SELECT SYSDATE FROM DUAL();
Copy code

Q3. Find the Nth highest consultation fees from the PatientsCheckup table with and without using the TOP/LIMIT keywords.

Nth highest consultation fees from the PatientsCheckup table with using the TOP keywords

 
SELECT TOP 1 ConsultationFees
FROM(
SELECT TOP N ConsultationFees
FROM PatientsCheckup
ORDER BY ConsultationFees DESC) AS FEES
ORDER BY ConsultationFees ASC;
Copy code

The Nth highest consultation fees from the PatientsCheckup table using the LIMIT keywords.

 
SELECT ConsultationFees
FROM PatientsCheckup
ORDER BY ConsultationFees DESC LIMIT N-1,1;
Copy code

Nth highest consultation fees from the PatientsCheckup table without using the TOP/LIMIT keywords.

 
SELECT ConsultationFees
FROM PatientsCheckup F1
WHERE N-1 = (
SELECT COUNT( DISTINCT ( F2.ConsultationFees ) )
FROM PatientsCheckup F2
WHERE F2.ConsultationFees > F1.ConsultationFees );
Copy code

Q4. Write a query to fetch top N records using the TOP/LIMIT, ordered by ConsultationFees.

TOP Command – SQL Server

 
SELECT TOP N * FROM PatientsCheckup ORDER BY ConsultationFees DESC;
LIMIT Command - MySQL
SELECT * FROM PatientsCheckup ORDER BY ConsultationFees DESC LIMIT N;
Copy code

Q5. Write a SQL query to create a table where the structure is copied from other table.

  • Create an Empty Table
  • Create a table consisting data

To create an Empty table

 
CREATE TABLE NewPatientsTable
SELECT * FROM Patients WHERE 1=0;
Copy code

Create a table consisting of data

Using SELECT command

 
SELECT * INTO NewPatientsTable FROM Patients WHERE 1 = 0;
Copy code

Using CREATE command in MySQL

 
CREATE TABLE NewPatientsTable AS SELECT * FROM Patients;
Copy code

Q6. Write a query to fetch even and odd rows from a table.

If you have an auto-increment field like PatientID then you can use the MOD() function:

Fetch even rows using MOD() function:

 
SELECT * FROM Patients WHERE MOD(PatientID,2)=0;
Copy code

Fetch odd rows using MOD() function:

 
SELECT * FROM Patients WHERE MOD(PatientID,2)=1;
Copy code

In case there are no auto-increment fields then you can use the Row_number in SQL Server or a user-defined variable in MySQL. Then, check the remainder when divided by 2.

Fetch even rows in SQL Server

 
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER(ORDER BY PatientId) AS RowNumber
FROM Patients
) P
WHERE P.RowNumber % 2 = 0;
Copy code

Fetch even rows in SQL Server

 
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER(ORDER BY PatientId) AS RowNumber
FROM Patients
) P
WHERE P.RowNumber % 2 = 0;
Fetch even rows in MySQL
SELECT * FROM (
SELECT *, @rowNumber := @rowNumber+ 1 rn
FROM Patients
JOIN (SELECT @rowNumber:= 0) r
) p
WHERE rn % 2 = 0;
Copy code

In case you wish to find the odd rows, then the remainder when divided by 2 should be 1.

Q7. Write an SQL query to fetch duplicate records from Patients, without considering the primary key.

 
SELECT PatientName, DoctorID, RegDate, State, COUNT(*)
FROM Patients
GROUP BY PatientName, DoctorID, RegDate, State
HAVING COUNT(*) > 1;
Copy code

Q8. Write a query to fetch the number of patients whose weight is greater than 68.

 
SELECT COUNT(*) FROM PatientsCheckup WHERE Weight > '68';
Copy code

Q9. Write a query to retrieve the list of patients from the same state.

 
SELECT DISTINCT P.PatientID, P.PatientName, P.State
FROM Patients P, Patient P1
WHERE P.State = P1.State AND P.PatientID != P1.PatientID;
Copy code

Q10. Write a query to retrieve two minimum and maximum consultation fees from the PatientsCheckup Table.

 
– TWO MINIMUM CONSULTATION FEES
SELECT DISTINCT ConsultationFees FROM PatientsCheckup P1
WHERE 2 >= (SELECT COUNT(DISTINCT ConsultationFees)FROM PatientsCheckup P2
WHERE P1.ConsultationFees >= P2.ConsultationFees) ORDER BY P1.SConsultationFees DESC;
– TWO MAXIMUM CONSULTATION FEES
SELECT DISTINCT ConsultationFees FROM PatientsCheckup P1
WHERE 2 >= (SELECT COUNT(DISTINCT ConsultationFees)FROM PatientsCheckup P2
WHERE P1.ConsultationFees <= P2.ConsultationFees) ORDER BY P1.ConsultationFees DESC;
Copy code

Q11. Write a query to fetch patient details along with the weight fees, even if the details are missing.

 
SELECT P.PatientName, C.ConsultationFees
FROM Patients P
LEFT JOIN
PatientsCheckup C
ON P.PatientId = C.PatientId;
Copy code

Q12. Write a SQL query to fetch doctor wise count of patients sorted by the doctors.

 
SELECT DoctorID, COUNT(PatientID) AS DocPat
FROM Patients GROUP BY DoctorID
ORDER BY DocPat;
Copy code

Q13. Write a SQL query to fetch the first and last record of the Patients table.

 
FETCH FIRST RECORD
SELECT * FROM Patients WHERE PatientID = (SELECT MIN(PatientID) FROM Patients);
FETCH LAST RECORD
SELECT * FROM Patients WHERE PatientID = (SELECT MAX(PatientID) FROM Patients);
Copy code

Q14. Write a SQL query to fetch consultation fees – wise count and sort them in descending order.

 
SELECT ConsultationFees, COUNT(PatientId) CFCount
FROM PatientsCheckup
GROUP BY ConsultationFees
ORDER BY CFCount DESC;
Copy code

Q15. Write a SQL query to retrieve patient details from the Patients table who have a weight in the PatientsCheckup table.

 
SELECT * FROM Patients P
WHERE EXISTS
(SELECT * FROM PatientsCheckup C WHERE P.PatientID = C.PatientID);
Copy code

Q16. Write a SQL query to retrieve the last 2 records from the Patients table.

 
SELECT * FROM Patients WHERE
PatientID <=2 UNION SELECT * FROM
(SELECT * FROM Patients P ORDER BY P.PatientID DESC)
AS P1 WHERE P1.PatientID <=2;
Copy code

Q17. Write a SQL query  to find all the patients who joined in the year 2022.

 
USING BETWEEN
SELECT * FROM Patients
WHERE RegDate BETWEEN '2021/01/01' AND '2021/12/31';
USING YEAR
SELECT * FROM Patients WHERE YEAR(RegDate ) = '2021';
Copy code

Q18. Write a SQL query to fetch 50% records from the PatientsCheckup table.

 
SELECT *
FROM PatientsCheckup WHERE
PatientID <= (SELECT COUNT(PatientD)/2 FROM PatientsCheckup);
Copy code

Q19. Write a query to find those patients who have paid consultation fees between 400 to 700.

 
SELECT * FROM Patients WHERE PatientID IN
(SELECT PatientID FROM PatientsCheckup WHERE ConsultationFees BETWEEN '400' AND '700');
Copy code

Q20. Write a query to update the patient names by removing the leading and trailing spaces.

 
UPDATE Patients
SET PatientName = LTRIM(RTRIM(PatientName));
Copy code

Q21. Write a query to add email validation to your database.

 
SELECT email FROM Patients WHERE NOT REGEXP_LIKE(email,[A-Z0-9._%+-]+@[A-Z0-9.-]+.[A-Z]{2,4}’, ‘i’);
Copy code

Q22. Write a query to find all patient names whose name:

  • Begin with A
  • Ends with S and contains 3 alphabets
  • Staying in the state Telangana
 
SELECT * FROM Patients WHERE PatientName LIKE 'A%';
SELECT * FROM Patients WHERE PatientName LIKE '___S';
SELECT * FROM Patients WHERE State LIKE 'Telangana%;
Copy code

Q23. Write a SQL query to fetch details of all patients excluding patients with name  “Sheela” and “Anay”.

 
SELECT * FROM Patients WHERE PatientName NOT IN ('Sheela','Anay');
Copy code

Q24. Write a query to fetch the total count of occurrences of a particular character – ‘x’ in the PatientName.

 
SELECT PatientName, PatientID
LENGTH(PatientName) - LENGTH(REPLACE(PatientName, 'x', ''))
FROM Patients;
Copy code

Q25. Write a query to retrieve the first three characters of  PatientName from the Patients table.

 
SELECT SUBSTRING(PatientName, 1, 3) FROM Patients;
Copy code

Q26. Write a query to fetch only the Address (string before space).

 
USING the MID FUNCTION IN MySQL
SELECT MID(Address, 0, LOCATE(' ',Address)) FROM Patients;
USING SUBSTRING
SELECT SUBSTRING(Address, 1, CHARINDEX(' ',Address)) FROM Patients;
Copy code

Q27. Write a query to combine Address and state into a new column – NewAddress.

 
SELECT CONCAT(Address, ' ', State) AS 'NewAddress' FROM Patients;
Copy code

Q28. Write a query to fetch PatientIDs  which are present in: 

  • Both tables
  • One of the table. Let us say, patients present in Patients and not in the PatientsCheckup table.
 
–Present IN BOTH TABLES
SELECT PatientId FROM Patients
WHERE PatientId IN
(SELECT PatientId FROM PatientsCheckup);
– Present IN One OF the TABLE
SELECT PatientId FROM Patients
WHERE PatientId NOT IN
(SELECT PatientId FROM PatientsCheckup);
Copy code

Q29. Write a query to find the number of patients whose RegDate is between 01/04/2021 to 31/12/2022 and are grouped according to state.

 
SELECT COUNT(*), State FROM Patients WHERE RegDate BETWEEN '01/04/2021' AND '31/12/2022' GROUP BY State;
Copy code

Q30. Write a query to fetch all records from the Patients table; ordered by PatientName in ascending order, State in descending order.

 
SELECT * FROM Patients ORDER BY PatientName ASC, State DESC;
Copy code

With this, we end this article on the top 30 SQL query interview questions. We hope you found it informative. 

Related Reads

What is the Difference Between SQL and MySQL?
What is the Difference Between SQL and MySQL?
SQL is query programming language to manage RDBMS while MySQL is RDBMS that uses SQL.
In this article, we will discuss the key differences between SQL (Structured Query Language) and...read more
Difference between SQL and NoSQL
Difference between SQL and NoSQL
This article designed for beginners will teach you difference between SQL and NoSQL. The tutorial includes concepts, such as what is a SQL, what is NoSQL, and more.
Difference Between DELETE and TRUNCATE
Difference Between DELETE and TRUNCATE
The DELETE command in SQL deletes specific rows from a table based on a specific condition using the WHERE clause, whereas the TRUNCATE command deletes all the rows from the...read more
How to Use TRUNCATE Command in SQL?
How to Use TRUNCATE Command in SQL?
SQL TRUNCATE TABLE command is used to remove the record from the table. Confused, the SQL DELETE statement also removes the record from the table. So, why use another command...read more
Delete Statement in SQL
Delete Statement in SQL
Delete statement is very important statement in SQL.This article covers delete statement with condition and without condition( with proper syntax and examples)
SQL ALTER TABLE : ADD, DROP, MODIFY, RENAME
SQL ALTER TABLE : ADD, DROP, MODIFY, RENAME
ALTER TABLE in SQL is used to change the structure of the existing table. In this article, we will briefly discuss how to add, modify, drop, rename columns, constraints, and...read more
How to use GROUP BY in SQL?
How to use GROUP BY in SQL?
The GROUP BY clause in sql is used to group the rows that have identical values by one or more columns. In this article, we will briefly discuss how GROUP...read more
How to use DROP command in SQL?
How to use DROP command in SQL?
In SQL, the DROP command is used to delete a database, table, index, or view. To use the DROP command, you must be very careful because it permanently deletes the...read more
SQL RIGHT JOIN – Example and Syntax
SQL RIGHT JOIN – Example and Syntax
Right Join in the sql returns all the rows from the right table and the matching entries from both tables. In this article, we will discuss how to use right...read more
Master SQL in No Time: The Ultimate Tutorial for Beginners!
Master SQL in No Time: The Ultimate Tutorial for Beginners!
Forget years of struggle; conquer SQL in weeks! This beginner-friendly guide cuts through the jargon and outlines a crystal-clear path to SQL mastery. No prior coding experience? No problem! Dive...read more
FOREIGN KEY IN SQL 
FOREIGN KEY IN SQL 
Foreign key concept is very importamt concept in SQL. This article explains creation,addition and deletion of foreign key.
Cross Join in SQL
Cross Join in SQL
SQL Cross Join combines each row of one table with each row of another table and the number of rows in the result set is the product of number of...read more
About the Author

This is a collection of insightful articles from domain experts in the fields of Cloud Computing, DevOps, AWS, Data Science, Machine Learning, AI, and Natural Language Processing. The range of topics caters to upski... Read Full Bio

Comments