SQL

DBMS Integrity Constraints

Integrity constraints are a set of rules used in DBMS to ensure that the data in a database is
accurate, consistent and reliable. These rules helps in maintaining the quality of data by
ensuring that the processes like adding, updating or deleting information do not harm the
integrity of the database. Integrity constraints also define how different parts of the
database are connected and ensure that these relationships remain valid. They play an
essential role in making sure the data is meaningful and follows the logical structure of the
database.

What are Integrity Constraints ?

Integrity constraints in a Database Management System are rules that help keep the data in
a database accurate, consistent and reliable. They act like a set of guidelines that ensure all
the information stored in the database follows specific standards.

Integrity Constraints

Example: Making sure every customer has a valid email address & ensuring that an order in
the database is always linked to an existing customer.

Note: These rules prevent mistakes, such as adding incomplete or incorrect data, and make
sure the database is secure and well-organized.

Types of Integrity Constraints

There are Different types of Integrity Constraints used in DBMS, these are:

1. Domain Constraints

2. Entity Integrity Constraints

3. Key Constraints

4. Referential integrity constraints

5. Assertion

6. Triggers

1. Domain Constraints

Domain constraints are a type of integrity constraint that ensure the values stored in a
column (or attribute) of a database are valid and within a specific range or domain. In simple
terms, they define what type of data is allowed in a column and restrict invalid data entry.
The data type of domain include string, char, time, integer, date, currency etc. The value of
the attribute must be available in comparable domains.
Example: Below table demonstrates domain constraints in action by enforcing rules for each
column

Student_Id Name Semester Age

21CSE100 Aniket Kumar 6th 20

21CSE101 Shashwat Dubey 7th 21

21CSE102 Manvendra Sharma 8th 22

21CSE103 Ashmit Dubey 5th 20

1. Student_Id: Must be unique and follow a specific format like 21CSE###. No

duplicates or invalid formats allowed.

2. Name: Accepts only valid text (no numbers) and cannot be left empty (NOT NULL
constraint).

3. Semester: Allows specific values like 5th, 6th, etc., and ensures valid input (e.g., no
10th if not permitted).

4. Age: Must be an integer within a reasonable range (e.g., 18-30) and cannot contain
invalid data like negative numbers or text.

Types of Domain Constraints:

 NOT NULL Constraint: Ensures No records can have NULL value.

 CHECK Constraint: This Constraint Checks for any specified condition over any
attribute.

Why Domain Constraints Are Important :

 They prevent invalid or inconsistent data from entering the database.

 They ensure the database is reliable and follows predefined business rules.

 They make the database easier to manage and maintain by reducing errors.

Example: Let, the not-null constraint be specified on the "Semester" attribute in the
relation/table given below, then the data entry of 4th tuple will violate this integrity
constraint, because the "Semester" attribute in this tuple contains null value. To make this
database instance a legal instance, its entry must not be allowed by database management
system.

Student_id Name Semester Age

21CSE100
Sonali Rao 5th 20
1

21CSE101
Anjali Gupta 5th 21
2

21CSE102
Aastha Singh 5th 22
3

21CSE103
Ayushi Singh NULL 20
4

Read more about Domain Constraints and its types, Here.

2. Entity Integrity Constraints

Entity integrity constraints state that primary key can never contain null value because
primary key is used to determine individual rows in a relation uniquely, if primary key
contains null value then we cannot identify those rows. A table can contain null value in it
except primary key field.

Key Features of Entity Integrity Constraints:

 Uniqueness: The primary key value must be unique for each row in the table. No
duplicate entries are allowed in the primary key column.

 NOT NULL: The primary key column cannot contain NULL values, as every row must
have a valid identifier.

 Essential for Table Design: Ensures that every record in the table can be uniquely
identified, preventing ambiguity.

Example: It is not allowed because it is containing primary key (Student_id) as NULL value.
Student_id Name Semester Age

21CSE101 Ramesh 5th 20

21CSE102 Kamlesh 5th 21

21CSE103 Aakash 5th 22

NULL Mukesh 5th 20

3. Key Constraints

Key constraints ensure that certain columns or combinations of columns in a table uniquely
identify each row. These rules are essential for maintaining data integrity and preventing
duplicate or ambiguous records.

Why Key Constraints Are Important ?

 Prevent Duplicates: Ensure unique identification of rows.

 Maintain Relationships: Enable proper linking between tables (via foreign keys).

 Enforce Data Integrity: Prevent invalid or inconsistent data.

Example: It is now acceptable because all rows must be unique.

Student_id Name Semester Age

21CSE101 Ramesh 5th 20

21CSE102 Kamlesh 5th 21

21CSE103 Aakash 5th 22

21CSE102 Mukesh 5th 20

3.1 Primary Key Constraints

It states that the primary key attributes are required to be unique and not null. That is,
primary key attributes of a relation must not have null values and primary key attributes of
two tuples must never be same. This constraint is specified on database schema to the
primary key attributes to ensure that no two tuples are same.

Example: Here, in the below example the Student_id is the primary key attribute. The data
entry of 4th tuple violates the primary key constraint that is specifies on the database
schema and therefore this instance of database is not a legal instance.

Student_id Name Semester Age

101 Ramesh 5th 20

Kamles
102 5th 21
h

103 Akash 5th 22

 Unique Values: Each student_id must be unique. 101, 102, 103 are valid. Inserting
101 again would result in an error.

 Not NULL: student_id cannot be NULL.

 Invalid: A row with NULL for student_id will be rejected.

3.2 Unique Key Constraints

The Unique key constraint in DBMS ensures that all values in a specified column (or group of
columns) are distinct across the table. It prevents duplicate entries, maintaining data
integrity, but unlike the primary key, it allows one NULL value.

Example: Here, in the below example the Email column has NULL value in 2nd record.

Employee_ID Email Name

1 aniket@[Link] Aniket Kumar

2 NULL Shashwat Dubey

3 shashwat@[Link] Manvendra Sharma

Employee_ID Email Name

 Unique Values: The email column must contain unique values. aniket@[Link]
and shashwat@[Link] are valid. Adding another row with
aniket@[Link] would result in an error.

 Allows One NULL: The email column can contain one NULL value.

 Valid: NULL in the second row.

 Invalid: Adding another row with NULL in email will be rejected.

4. Referential integrity constraints

Referential integrity constraints are rules that ensure relationships between tables remain
consistent. They enforce that a foreign key in one table must either match a value in the
referenced primary key of another table or be NULL. This guarantees the logical connection
between related tables in a relational database.

Why Referential Integrity Constraints Are Important ?

 Maintains Consistency: Ensures relationships between tables are valid.

 Prevents Orphan Records: Avoids cases where a record in a child table references a
non-existent parent record.

 Enforces Logical Relationships: Strengthens the logical structure of a relational

database.

Example: Here, in below example Block_No 22 entry is not allowed because it is not present
in 2nd table.

Student_id Name Semester Block_No

22CSE101 Ramesh 5th 20

21CSE105 Kamlesh 6th 21

22CSE102 Aakash 5th 20

Student_id Name Semester Block_No

23CSE106 Mukesh 2nd 22

Block_No Block Location

20 Chandigarh

21 Punjab

25 Delhi

SQL | String functions

Last Updated : 18 Apr, 2026

SQL string functions help manipulate and format text data efficiently. They are widely used
for cleaning, comparing, and extracting meaningful information from textual fields.

 Useful for handling names, addresses, and other text-based data.

 Essential for organizing, analyzing, and improving data quality.

SQL String Functions

String functions are used to perform an operation on input string and return an output
string. Below are some of the most commonly used SQL string functions:

1. CONCAT()

The CONCAT() function is used to concatenate (combine) two or more strings into one string.
It is useful when we want to merge fields like first and last names into a full name.

ADVERTISING

Query:
SELECT CONCAT('John', ' ', 'Doe') AS FullName;

Output:

John Doe

2. CHAR_LENGTH() / CHARACTER_LENGTH()

The CHAR_LENGTH() or LENGTH() function returns the length of a string in characters. It’s
essential for validating or manipulating text data, especially when you need to know how
many characters a string contains.

Query:

SELECT CHAR_LENGTH('Hello') AS StringLength;

Output:

3. UPPER() and LOWER()

These UPPER() and LOWER() functions convert the text to uppercase or lowercase,
respectively. They are useful for normalizing the case of text in a database.

Query:

SELECT UPPER('hello') AS UpperCase;

SELECT LOWER('HELLO') AS LowerCase;

Output:

HELLO

hello

4. LENGTH()

LENGTH() returns the length of a string in bytes. This can be useful for working with multi-
byte character sets.

Query:

SELECT LENGTH('Hello') AS LengthInBytes;

Output:

5. REPLACE()
The REPLACE() function replaces occurrences of a substring within a string with another
substring. This is useful for cleaning up data, such as replacing invalid characters or
formatting errors.

Query:

SELECT REPLACE('Hello World', 'World', 'SQL') AS UpdatedString;

Output:

Hello SQL

6. SUBSTRING() / SUBSTR()

The SUBSTRING() (or SUBSTR()) function is used to extract a substring from a string, starting
from a specified position. It is especially useful when we need to extract a specific part of a
string, like extracting the domain from an email address.

Query:

SELECT SUBSTRING('Hello World', 1, 5) AS SubStringExample;

Output:

Hello

7. LEFT() and RIGHT()

The LEFT() and RIGHT() functions allow you to extract a specified number of characters from
the left or right side of a string, respectively. It is used for truncating strings for display.

Query:

SELECT LEFT('Hello World', 5) AS LeftString;

SELECT RIGHT('Hello World', 5) AS RightString;

Output:

Hello

World

8. INSTR()

The INSTR() function is used to find the position of the first occurrence of a substring within
a string. It returns the position (1-based index) of the substring. If the substring is not found,
it returns 0. This function is particularly useful for locating specific characters or substrings in
text data.

Query:

SELECT INSTR('Hello World', 'World') AS SubstringPosition;

Output:

9. TRIM()

The TRIM() function removes leading and trailing spaces (or other specified characters) from
a string. By default, it trims spaces but can also remove specific characters using
TRIM(character FROM string). This is helpful for cleaning text data, such as user inputs or
database records.

Query:

SELECT TRIM(' ' FROM ' Hello World ') AS TrimmedString;

Output:

Hello World

10. REVERSE()

The REVERSE() function reverses the characters in a string. It’s useful in situations where we
need to process data backward, such as for password validation or certain pattern matching.

Query:

SELECT REVERSE('Hello') AS ReversedString;

Output:

olleH

SQL Aggregate functions

Last Updated : 19 Nov, 2025

SQL Aggregate Functions allow summarizing large sets of data into meaningful results,
making it easier to analyze patterns and trends across many records. They return a single
output value after processing multiple rows in a table.

 Perform calculations like totals, averages, minimum or maximum values on data.

 Ignore NULL values in most functions except COUNT(*), improving result accuracy.
  Work with clauses such as GROUP BY, HAVING and ORDER BY for analysis.

Example: First, we create a demo SQL database and table, on which we use the Aggregate
functions.

Employee Table

Query:

ADVERTISING

SELECT SUM(Salary) FROM Employee;

Output:

Syntax:

AGGREGATE_FUNCTION(column_name)

Aggregate Functions in SQL

Below are the most frequently used aggregate functions in SQL.

1. Count()

It is used to count the number of rows in a table. It helps summarize data by giving the total
number of entries. It can be used in different ways depending on what you want to count:

 COUNT(*): Counts all rows.

 COUNT(column_name): Counts non-NULL values in the specified column.

 COUNT(DISTINCT column_name): Counts unique non-NULL values in the column.

Query:

-- Total number of records in the table

SELECT COUNT(*) AS TotalRecords FROM Employee;

-- Count of non-NULL salaries

SELECT COUNT(Salary) AS NonNullSalaries FROM Employee;

-- Count of unique non-NULL salaries

SELECT COUNT(DISTINCT Salary) AS UniqueSalaries FROM Employee;

Output:

 COUNT(*) returns the total number of rows in the table, including rows with NULL
values.

 COUNT(Salary) counts only the rows where Salary is not NULL.

 COUNT(DISTINCT Salary) counts unique non-NULL salary values, ignoring duplicates.

2. SUM()

It is used to calculate the total of a numeric column. It adds up all non-NULL values in that
column for Example, SUM(column_name) returns sum of all non-NULL values in the
specified column.

Query:

-- Calculate the total salary

SELECT SUM(Salary) AS TotalSalary FROM Employee;

-- Calculate the sum of unique salaries

SELECT SUM(DISTINCT Salary) AS DistinctSalarySum FROM Employee;

Output:

 SUM(Salary) adds all non-NULL salary values to get the total salary amount.

 SUM(DISTINCT Salary) adds only unique non-NULL salary values, avoiding duplicates.

 NULL values are ignored in both SUM calculations.

3. AVG()

It is used to calculate average value of a numeric column. It divides sum of all non-NULL
values by the number of non-NULL rows for Example, AVG(column_name) returns average of
all non-NULL values in the specified column.

Query:

-- Calculate the average salary

SELECT AVG(Salary) AS AverageSalary FROM Employee;

-- Average of distinct salaries

SELECT AVG(DISTINCT Salary) AS DistinctAvgSalary FROM Employee;

Output:

 AVG(Salary) calculates the average of all non-NULL salary values.

 AVG(DISTINCT Salary) computes the average only from unique non-NULL salary
values.

 Both ignore NULL values when performing the calculation.

4. MIN() and MAX()

The MIN() and MAX() functions return the smallest and largest values, respectively, from a
column.

Query:

-- Find the highest salary

SELECT MAX(Salary) AS HighestSalary FROM Employee;

-- Find the lowest salary

SELECT MIN(Salary) AS LowestSalary FROM Employee;

Output:
  MAX(Salary) returns the highest non-NULL salary value from the Employee table.

 MIN(Salary) returns the lowest non-NULL salary value from the Employee table.

 Both functions ignore NULL values while determining the result.

SQL GROUP BY

Last Updated : 14 Apr, 2026

The SQL GROUP BY clause is used to arrange identical data into groups based on one or
more columns. It is commonly used with aggregate functions like COUNT(), SUM(), AVG(),
MAX() and MIN() to perform calculations on each group of data.

Example: First, we will create a demo SQL database and table, on which we will use the
GROUP BY command.

Employees Table

Query:

ADVERTISING

SELECT Department, SUM(Salary) AS TotalSalary

FROM Employees
GROUP BY Department;

Output:

 Groups all employee records based on their department.

  Calculates and returns total salary for each department.

Syntax:

SELECT column1, aggregate_function(column2)

FROM table_name
WHERE condition
GROUP BY column1, column2;

 aggregate_function: function used for aggregation, e.g., SUM(), AVG(), COUNT().

 table_name: name of the table from which data is selected.

 condition: Optional condition to filter rows before grouping (used with WHERE).

 column1, column2: Columns on which the grouping is applied.

Working with GROUP BY

Let's assume that we have a Student table. We will insert some sample data into this table
and then perform operations using GROUP BY to understand how it groups rows based on a
column and aggregates data.

Student Table

Example 1: Group By Single Column

When we group by a single column, rows with the same value in that column are combined.
For example, grouping by subject shows how many students are enrolled in each subject.

Query:

SELECT subject, COUNT(*) AS Student_Count

FROM Student
GROUP BY subject;

Output:
  Groups all student records by their subject.

 Counts total number of students in each subject.

SQL HAVING Clause

Last Updated : 9 Feb, 2026

The SQL HAVING clause filters the results of grouped data after using the GROUP BY clause.
It is used with aggregate functions such as SUM(), COUNT(), or AVG() to display only those
groups that meet specific conditions.

Example: First, we will create a demo SQL database and table, on which we will use the
HAVING Clause command.

Query:

SELECT Department, COUNT(EmpID) AS Employee_Count

FROM Employees

GROUP BY Department

HAVING COUNT(EmpID) > 1;

Output:

Syntax:

SELECT column_name, AGGREGATE_FUNCTION(column_name)

FROM table_name

GROUP BY column_name

HAVING condition;
View

A view is a virtual table that is created from one or more real tables. It does not store data
itself; instead, it stores a query. When you access a view, the database runs that query and
shows the result like a table.

Views are mainly used to simplify complex queries, improve security by restricting access to
certain columns or rows, and provide a customized representation of data. For example, if a
table contains sensitive employee information, a view can be created to show only names
and departments while hiding salaries.

Example:

CREATE VIEW Employee_View AS

SELECT name, department
FROM Employee;

Here, Employee_View behaves like a table but only shows selected data.

Trigger

A trigger is a special type of stored procedure that automatically executes (fires) when a
specific event occurs in the database, such as INSERT, UPDATE, or DELETE operations on a
table.

Triggers are used for enforcing rules, maintaining data integrity, logging changes, and
automating tasks without manual intervention.

There are different types of triggers:

 BEFORE Trigger – executes before the event

 AFTER Trigger – executes after the event

 INSTEAD OF Trigger – replaces the event (commonly used with views)

Example:

CREATE TRIGGER before_insert_emp

BEFORE INSERT ON Employee
FOR EACH ROW
SET NEW.created_at = NOW();

This trigger automatically sets the created_at field before a new record is inserted.
Difference Between View and Trigger

 A view is used to display data in a specific way, while a trigger is used to

automatically perform actions.

 Views are queried like tables, whereas triggers are not directly called—they run
automatically.

 Views help in data abstraction and security, while triggers help in enforcing business
rules and automation.

SQL Joins (Inner, Left, Right and Full Join)

Last Updated : 8 Apr, 2026

SQL Joins are used to combine data from two or more tables based on a related column.
They help in:

 Retrieving connected data stored across multiple tables.

 Matching records using common columns.

 Improving data analysis by combining related information.

 Creating meaningful result sets from separate tables.

Types of SQL Joins

SQL joins are categorized into different types based on how rows from two tables are
matched and combined.

1. INNER JOIN

INNER JOIN is used to retrieve rows where matching values exist in both [Link] helps in:

 Combining records based on a related column.

 Returning only matching rows from both tables.

 Excluding non-matching data from the result set.

 Ensuring accurate data relationships between tables.

Syntax:

SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 INNER JOIN

table2 ON table1.matching_column = table2.matching_column;
Inner join

Note: We can also write JOIN instead of INNER JOIN. JOIN is same as INNER JOIN.

Example of INNER JOIN:

Consider the two tables, Student and StudentCourse, which share a common column
ROLL_NO. Using SQL JOINS, we can combine data from these tables based on their
relationship, allowing us to retrieve meaningful information like student details along with
their enrolled courses.

Student Table:

StudentCourse Table:

Let's look at the example of INNER JOIN clause, and understand it's working. This query will
show the names and age of students enrolled in different courses.

Query:

SELECT StudentCourse.COURSE_ID, [Link], [Link]

FROM Student
INNER JOIN StudentCourse
ON Student.ROLL_NO = StudentCourse.ROLL_NO;

Output:

2. LEFT JOIN

LEFT JOIN is used to retrieve all rows from the left table and matching rows from the right
[Link] helps in:

 Returning all records from the left table.

  Showing matching data from the right table.

 Displaying NULL values where no match exists in the right table.

 Performing outer joins, also known as LEFT OUTER JOIN.

Syntax:

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;

Left Join

Note: We can also use LEFT OUTER JOIN instead of LEFT JOIN, both are the same.

Example: In this example, the LEFT JOIN retrieves all rows from the Student table and the
matching rows from the StudentCourse table based on the ROLL_NO column.

Query:

SELECT [Link],StudentCourse.COURSE_ID
FROM Student
LEFT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

Output:

3. RIGHT JOIN

RIGHT JOIN is used to retrieve all rows from the right table and the matching rows from the
left [Link] helps in:

 Returning all records from the right-side table.

 Showing matching data from the left-side table.

 Displaying NULL values where no match exists in the left table.

 Performing outer joins, also known as RIGHT OUTER JOIN.

Syntax
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;

Right Join

Note: We can also use RIGHT OUTER JOIN instead of RIGHT JOIN, both are the same

Example: In this example, the RIGHT JOIN retrieves all rows from the StudentCourse table
and the matching rows from the Student table based on the ROLL_NO column.

Query:

SELECT [Link],StudentCourse.COURSE_ID
FROM Student
RIGHT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

Output:

4. FULL JOIN

FULL JOIN is used to combine the results of both LEFT JOIN and RIGHT JOIN. It helps in:

 Returning all rows from both tables.

 Showing matching records from each table.

 Displaying NULL values where no match exists in either table.

 Providing complete data from both sides of the join.

Syntax

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;

Example: This example uses a FULL JOIN to return all rows from both tables. Matching
records appear together, while non-matching records still show up with NULL values for the
missing fields.

Query:
SELECT [Link],StudentCourse.COURSE_ID
FROM Student
FULL JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

Output :

5. Natural Join

A Natural Join is a type of INNER JOIN that automatically joins two tables based on columns
with the same name and data type. It returns only the rows where the values in the
common columns match.

 It joins tables using common columns with the same name.

 It returns only rows where values in those columns match.

 The common column appears only once in the result.

Example: Look at the two tables below:

Employee Table:

Department Table:

Example: Find all Employees and their respective departments.

SELECT
Emp_name,
Dept_name
FROM Employee
NATURAL JOIN Department;
Output:

SQL Subquery

Last Updated : 2 May, 2026

A subquery in SQL is a query nested inside another SQL query. It allows complex filtering,
aggregation and data manipulation by using the result of one query inside another. They are
an essential tool when we need to perform operations like:

 Filter rows based on results from another query.

 Apply aggregate functions like SUM, COUNT, or AVG dynamically.

 Update data using values from other tables.

 Delete rows based on conditions returned by another query.

Example: First, we create a demo SQL database and tables, on which we use the SQL
Subqueries.

Output

Query:

SELECT * FROM Students

WHERE Score > ( SELECT AVG(Score) FROM Students );

Output:
Output

 The subquery calculates the average score of all students.

 The main query returns only the students whose score is higher than that average.

Syntax:

SELECT column_name

FROM table_name

WHERE column_name operator

(SELECT column_name

FROM table_name

WHERE condition);

Note: Subqueries do not have a single fixed syntax, as they can be used in different clauses
like SELECT, WHERE, FROM and HAVING

SQL Clauses for Subqueries

Clauses that can be used with subqueries are:

 Filters rows in the outer query based on the results returned by the WHERE clause.

 Treats the subquery as a temporary (derived) table that can be queried like a normal
table using the FROM clause.

 Filters grouped or aggregated results using values produced by the HAVING clause.

Types of Subqueries

Consider the following two tables for examples:

Employees Table
Departments Table

1. Single-Row Subquery

A single-row subquery is a subquery that returns only one value.

 Returns exactly one row as the result.

 Commonly used with comparison operators such as =, >, <

Example:

SELECT * FROM Employees

WHERE Salary = (SELECT MAX(Salary) FROM Employees);

Output:

Output

 Finds the highest salary in the Employees table.

 Returns the employee(s) whose salary matches that maximum value.

2. Multi-Row Subquery

A multi-row subquery is a subquery that returns more than one value.

 Returns multiple rows as the result.

 Requires operators that can handle multiple values, such as IN, ANY or ALL

Example:

SELECT * FROM Employees

WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location =

'New York');

Output:
Output

 Identifies all departments located in New York.

 Returns the employees who belong to any of those departments.

3. Correlated Subquery

A correlated subquery is a subquery that depends on the outer query for its values.

 A dependent subquery: it references columns from the outer query.

 Executed once for each row of the outer query, making it slower for large datasets.

Example:

SELECT [Link], [Link]

FROM Employees e

WHERE [Link] > (SELECT AVG(Salary)

FROM Employees

WHERE DepartmentID = [Link]);

Output:

Output

 Calculates the average salary within each employee's department.

 Returns employees whose salary is higher than their department’s average.

Examples of Using SQL Subqueries

These examples showcase how subqueries can be used for various operations like selecting,
updating, deleting or inserting data, providing insights into their syntax and functionality.
Through these examples, we will understand flexibility and importance of subqueries in
simplifying complex database tasks.

Consider the following two tables:

Student_Info

Student_Section

Example 1: Fetching Data Using Subquery in WHERE Clause

This example demonstrates how to use a subquery inside the WHERE clause. The inner
query retrieves roll numbers of students who belong to section 'A' and the outer query
fetches their corresponding details (name, location and phone number) from the Student
table.

Query:

SELECT NAME, LOCATION, PHONE_NUMBER

FROM Student_Info

WHERE ROLL_NO IN (

SELECT ROLL_NO

FROM Student_Section

WHERE SECTION = 'A'

);

Output:

Output

 The subquery retrieves the roll numbers of students who belong to section 'A'.

 The outer query then uses these roll numbers to fetch details from the Student table.

 Thus, only Sophia and Emma are returned, since they are in section A.
Example 2: Using Subquery with DELETE

In this example, we use a subquery with DELETE to remove certain rows from the Student
table. Instead of hardcoding roll numbers, the subquery finds them based on conditions.

Query:

DELETE FROM Student_Info

WHERE ROLL_NO IN (

SELECT ROLL_NO FROM (

SELECT ROLL_NO FROM Student_Info WHERE ROLL_NO <= 101 OR ROLL_NO = 201

) AS temp

);

Output:

Output

 The subquery selects roll numbers 101 and 201.

 The outer query deletes students having those roll numbers.

Example 3: Using Subquery with UPDATE

Subqueries can also be used with UPDATE. In this example, we update student names to
"Geeks" if their location matches the result of a subquery.

Query:

UPDATE Student_Info

SET NAME = 'Geeks'

WHERE LOCATION IN (

SELECT LOCATION

FROM Student_Info

WHERE LOCATION IN ('London', 'Berlin')

);
Output:

Output

 The subquery selects locations 'London' and 'Berlin'.

 The outer query updates the NAME field for students whose location matches those
values.

Example 4: Simple Subquery in the FROM Clause

This example demonstrates using a subquery inside the FROM clause, where the subquery
acts as a temporary (derived) table.

Query:

SELECT NAME, PHONE_NUMBER

FROM (

SELECT NAME, PHONE_NUMBER, LOCATION

FROM Student_Info

WHERE LOCATION LIKE 'T%'

) AS subquery_table;

Output:

Output

 The subquery (SELECT NAME, PHONE_NUMBER, LOCATION FROM Student WHERE

LOCATION LIKE 'T%') fetches students whose location starts with "T" (Toronto).

 The outer query then selects only NAME and PHONE_NUMBER from this derived
table.

Example 5: Subquery with JOIN

We can also use subqueries along with JOIN to connect data across tables.

Query:
SELECT [Link], [Link], [Link]

FROM Student_Info s

INNER JOIN (

SELECT ROLL_NO, SECTION

FROM Student_Section

WHERE SECTION = 'A'

) ns

ON s.ROLL_NO = ns.ROLL_NO;

Output:

Output

 The subquery extracts roll numbers of students in section A.

 Joining this with the Student_Info table on ROLL_NO returns Emma and Sophia along
with their locations and section.