Top 30 SQL Query Interview Questions

5 mins read41.6K Views Comment

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.

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?

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

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

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?

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

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?

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?

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

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!

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 concept is very important concept in SQL. This article explains creation, addition and deletion of foreign key. This article explains foreign key in SQL. It also explains the...read more

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

Shiksha Online

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