Understanding Subqueries in SQL

Understanding Subqueries in SQL

5 mins read2.4K Views Comment
clickHere
Updated on Nov 21, 2022 10:29 IST

This blog explains subqueries in SQL. We will explore what are nested queries and sub-queries and how they help retrieve data from complex databases.

2022_03_Subqueries-in-SQL.jpg

In my previous articles, you have understood what is MySQL and its basic functions. In this article, we will discuss nested queries and subqueries in SQL to retrieve data from complex databases.

Nested & Subqueries in SQL

Nested queries have outer queries and inner sub queries. Multiple subqueries can be nested inside another query by using the SELECT, INSERT, UPDATE, and DELETE commands.

Nested queries are most commonly used when you have to retrieve specific data from multiple tables, along with additional criteria. 

Refer to the image below:

2022_03_NESTED-QUERIES.jpg

Note:

  • Inner Query is executed first
  • You can have any number of subqueries in the nested queries
  • The performance of the query might be affected if nested way too much
  • SELECT in subqueries can only retrieve a single column
  • Use indentation while writing queries to make it easy for the users to understand
  • You can use subqueries for calculations as well

Top Interview Questions on Nested Queries

In this article, I am going to consider the following tables to explain to you the most asked 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 ConsultationFees
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
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.
Introduction to SQL
Introduction to SQL
This article will teach you basic and advanced concepts of SQL. It concepts, such as what is a Database Management System, what is SQL, SQL Data types, SQL Statements, and...read more

Read Also: Update Query in SQL

Must Read: SQL Logical Operator

Also Read: How to use UNION in SQL

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

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

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;

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

Q2. 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);

Q3. Write a query to find those patients who have paid consultation fees between 200 to 300.

SELECT * FROM Patients WHERE PatientID IN 
(SELECT PatientID FROM PatientsCheckup WHERE Salary BETWEEN '200' AND '300');

Q4. Write a SQL query to retrieve the last 4 records from the Patients table.

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

75 Most Popular MySQL Commands
75 Most Popular MySQL Commands
This article covers the most popular MySQL commands with examples to help you work more efficiently with MySQL databases.
How to Install MySQL on Windows 10?
How to Install MySQL on Windows 10?
To install MySQL on Windows 10, download the MySQL Installer from the official website. Run the installer and follow the on-screen instructions, selecting the appropriate setup type for your needs....read more
Top 30 SQL Query Interview Questions
Top 30 SQL Query Interview Questions
Structured Query Language or most commonly known as SQL is used on a daily basis to handle, manipulate and analyze relational databases.

Q5. Write a query to fetch PatientIDs:

a. Which are present in both tables

b. Which are present in 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);

Q6. Write a query to fetch even and odd rows from a table by diving 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;

Fetch even rows in MySQL

SELECT * FROM (
      SELECT *, @rowNumber := @rowNumber+ 1 rn
      FROM Patients
      JOIN (SELECT @rowNumber:= 0) r
     ) p 
WHERE rn % 2 = 0;

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

Fetch odd rows in SQL Server

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

Fetch odd rows in MySQL

SELECT * FROM (
      SELECT *, @rowNumber := @rowNumber+ 1 rn
      FROM Patients
      JOIN (SELECT @rowNumber:= 0) r
     ) p 
WHERE rn % 2 = 1;

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

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

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

– TWO MINIMUM ConsultationFees
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;
 
– TWO MAXIMUM ConsultationFees
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;

Q9. Write a query to remove duplicate rows in SQL.

DELETE FROM Patients WHERE PatientID IN (
SELECT 
ID, COUNT(PatientID) 
FROM   Patients
GROUP BY  PatientID
HAVING 
COUNT (PatientID) > 1); 

Q10. Write a SQL query to retrieve patient details from 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);

With this, we end this article on the subqueries in SQL. We hope you found it informative. 

Top Trending Tech Articles:
Career Opportunities after BTech | Online Python Compiler | What is Coding | Queue Data Structure | Top Programming Language | Trending DevOps Tools | Highest Paid IT Jobs | Most In Demand IT Skills | Networking Interview Questions | Features of Java | Basic Linux Commands | Amazon Interview Questions

Recently completed any professional course/certification from the market? Tell us what liked or disliked in the course for more curated content.

Click here to submit its review with Shiksha Online.

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