STUDENT

sid name dept marks city

1 Asha CSE 85 Hyd
2 Ravi EEE 72 Hyd
3 Meena CSE 90 Blr
4 John ME 65 Blr
5 Neha ECE 78 Hyd
6 Kiran CSE 55 Blr
7 Pooja EEE 88 Hyd
8 Arjun ME 70 Hyd
9 Sneha ECE 92 Blr
10 Mohan CSE
1 Count students in each department
1️⃣
SELECT dept, COUNT(*) AS total_students
FROM Student
GROUP BY dept;

Output
dept total_students
CSE 4
EEE 2
ME 2
ECE 2
2️⃣Find average marks per department
SELECT dept, AVG(marks) AS avg_marks
FROM Student
GROUP BY dept;

3 Display departments sorted by average marks

SELECT dept, AVG(marks) AS avg_marks
FROM Student
GROUP BY dept
ORDER BY avg_marks DESC;

4 Count students in each city

SELECT city, COUNT(*) AS count
FROM Student
GROUP BY city;

5 Find maximum marks per department

SELECT dept, MAX(marks) AS max_marks
FROM Student
GROUP BY dept;

6 Show departments in alphabetical order

SELECT dept, COUNT(*) AS count
FROM Student
GROUP BY dept
ORDER BY dept;

7 Minimum marks per city

SELECT city, MIN(marks) AS min_marks
FROM Student
GROUP BY city;

8 Departments having more than 2 students

SELECT dept, COUNT(*) AS total
FROM Student
GROUP BY dept
HAVING COUNT(*) > 2;

Output
dept total
CSE 4

9 Cities where average marks > 75

SELECT city, AVG(marks) AS avg_marks
FROM Student
GROUP BY city
HAVING AVG(marks) > 75;

10 Departments where minimum marks ≥ 60

SELECT dept
FROM Student
GROUP BY dept
HAVING MIN(marks) >= 60;

11 Departments having at least one student scoring above 85

SELECT dept
FROM Student
GROUP BY dept
HAVING MAX(marks) > 85;
12Department-wise total marks
SELECT dept, SUM(marks) AS total_marks
FROM Student
GROUP BY dept;

13Cities sorted by number of students (descending)

SELECT city, COUNT(*) AS total
FROM Student
GROUP BY city
ORDER BY total DESC;

14Departments with average marks between 70 and 85

SELECT dept, AVG(marks) AS avg_marks
FROM Student
GROUP BY dept
HAVING AVG(marks) BETWEEN 70 AND 85;

15 Departments having more than one student below 70

SELECT dept
FROM Student
WHERE marks < 70
GROUP BY dept
HAVING COUNT(*) > 1;

16Cities where highest marks > 90

SELECT city
FROM Student
GROUP BY city
HAVING MAX(marks) > 90;
17 Departments sorted by maximum marks
SELECT dept, MAX(marks) AS max_marks
FROM Student
GROUP BY dept
ORDER BY max_marks DESC;

18 Departments where average marks is higher than overall

average
SELECT dept
FROM Student
GROUP BY dept
HAVING AVG(marks) > (SELECT AVG(marks) FROM Student);

19 Cities having more than 3 students

SELECT city
FROM Student
GROUP BY city
HAVING COUNT(*) > 3;

20 Departments where total marks exceed 250

SELECT dept FROM Student GROUP BY dept HAVING SUM(marks)
> 250;
Foreign Key (FK)
A foreign key is a column (or group of columns) in
one table that refers to the primary key of another
table.
It is used to maintain a relationship between tables
and ensure data integrity.

1. While creating a table

CREATE TABLE Orders ( order_id INT
PRIMARY KEY, student_id INT, FOREIGN KEY
(student_id) REFERENCES Student(student_id) );

. After creating a table

ALTER TABLE Orders
ADD FOREIGN KEY (student_id) REFERENCES
Student(student_id);
Key Point to Remember
 Foreign key values must exist in the referenced
table’s primary key.
INSERT INTO Student VALUES (1, 'Ravi');
INSERT INTO Orders VALUES (101, 1);
student_id name
1 Ravi
2 Anita
3 Kiran
4 Meena
5 Suresh
6 Priya
7 Arjun
8 Neha
9 Rahul
10 Divya

order_id student_id
101 1
102 2
103 3
order_id student_id
104 4
105 5
106 6
107 7
108 8
109 9
110 10

DELETE FROM Student WHERE student_id = 1;

❌ Cannot delete parent record if child exists

Insert VALID data into CHILD table

INSERT INTO Orders VALUES (101, 1);

STEP 5: Insert INVALID data (Foreign Key

Violation)
INSERT INTO Orders VALUES (103, 50);
❌ Error Output
ERROR: insert or update on table "Orders" violates
foreign key constraint INSERT INTO Orders
VALUES (102, 2);

Insert NULL into Foreign Key column

INSERT INTO Orders VALUES (104, NULL);

Orders Table Output

order_id student_id
104 NULL
✅ Allowed
📌 Foreign key can be NULL

STEP 7: DELETE from Parent Table (Default

Behavior)
DELETE FROM Student WHERE student_id = 1;
❌ Error Output
ERROR: update or delete on table "Student"
violates foreign key constraint
Reason: Student 1 is referenced in Orders

STEP 8: DELETE from Child Table first

(Correct Way)
DELETE FROM Orders WHERE student_id = 1;
DELETE FROM Student WHERE student_id = 1;
✅ Works

STEP 9: Foreign Key with ON DELETE

CASCADE
Drop and recreate child table
DROP TABLE Orders;
CREATE TABLE Orders ( order_id INT
PRIMARY KEY, student_id INT, FOREIGN KEY
(student_id) REFERENCES Student(student_id) ON
DELETE CASCADE );

Insert data again

INSERT INTO Orders VALUES (201, 2);
Delete parent row
DELETE FROM Student WHERE student_id = 2;
Result
 Row in Student → deleted
 Related rows in Orders → automatically
deleted
✅ CASCADE action observed
STEP 10: ON DELETE SET NULL
DROP TABLE Orders;
CREATE TABLE Orders ( order_id INT
PRIMARY KEY, student_id INT, FOREIGN KEY
(student_id) REFERENCES Student(student_id) ON
DELETE SET NULL );
Insert data
INSERT INTO Student VALUES (3, 'Kiran');
INSERT INTO Orders VALUES (301, 3);
Delete parent
DELETE FROM Student WHERE student_id = 3;
STEP 11: UPDATE Parent Key (Without
CASCADE)
UPDATE Student SET student_id = 10 WHERE
student_id = 3;

❌ Error unless ON UPDATE CASCADE is defined

STEP 12: ON UPDATE CASCADE FOREIGN

KEY (student_id) REFERENCES
Student(student_id) ON UPDATE CASCADE

✔ Parent update automatically updates child

CASE 1: Normal valid insertion ✅
INSERT INTO Orders VALUES (111, 3);

Output
1 row inserted
📌 Reason: student_id = 3 exists in Student

CASE 2: Insert using column names ✅

INSERT INTO Orders (order_id, student_id)
VALUES (112, 5);
Output
1 row inserted
Column order does not matter when column names
are specified

CASE 3: Insert multiple rows at once ✅

INSERT INTO Orders VALUES(113, 6),(114, 7);
Output
2 rows inserted
📌 All foreign key values are valid

CASE 4: Insert with foreign key = NULL

INSERT INTO Orders VALUES (115, NULL);
Output
1 row inserted
Foreign key columns can be NULL
CASE 5: Insert invalid foreign key value ❌
INSERT INTO Orders VALUES (116, 25);

❌ Error Output
ERROR: insert or update on table "Orders"violates
foreign key constraint

Reason: student_id = 25 does not exist in parent

table

CASE 6: Insert duplicate PRIMARY KEY ❌

INSERT INTO Orders VALUES (101, 4);
❌ Error Output
ERROR: duplicate key value violates primary key
constraint
order_id must be unique

CASE 7: Insert without foreign key column

(partial insert)
INSERT INTO Orders (order_id) VALUES (117);
Output
1 row inserted
student_id becomes NULL
Allowed because FK allows NULL by default

CASE 8: Insert wrong data type ❌

INSERT INTO Orders VALUES ('ABC', 2);❌ Error
Output
ERROR: invalid input syntax for integer

📌 Data type mismatch

CASE 9: Insert NULL into PRIMARY KEY ❌

INSERT INTO Orders VALUES (NULL, 3);

❌ Error Output
ERROR: null value in column "order_id"violates
not-null constraint
📌 Primary key cannot be NULL

CASE 10: Insert referencing parent row not yet

committed ❌
INSERT INTO Orders VALUES (118, 11);

❌ Error Output
ERROR: insert or update on table "Orders"violates
foreign key constraint
📌 Parent record must exist first

CASE 11: Insert before parent row exists (Order

matters) ❌
INSERT INTO Orders VALUES (119, 12);
INSERT INTO Student VALUES (12,
'NewStudent');
❌ First statement fails
Foreign key constraint violation
Parent table must be populated before child table

CASE 12: Insert after parent row exists ✅

INSERT INTO Student VALUES (12,
'NewStudent');
INSERT INTO Orders VALUES (119, 12);
Output
1 row inserted

While inserting into a child table, the foreign key

value must already exist in the parent table or be
NULL; otherwise, insertion fails.
DELETE OPERATIONS WITH FOREIGN
KEY

FOREIGN KEY (student_id) REFERENCES

Student(student_id)

(Default = RESTRICT / NO ACTION)

CASE 1: Delete a row from CHILD table ✅

(Always Allowed)
DELETE FROM Orders WHERE order_id = 101;
Output
1 row deleted
Child table rows can be deleted freely
No effect on parent table

CASE 2: Delete parent row NOT referenced in

child ✅
Step 1: Ensure no child rows exist
DELETE FROM Orders WHERE student_id = 9;

Step 2: Delete parent

DELETE FROM Student WHERE student_id = 9;
Output
1 row deleted
📌 Allowed because no child depends on it

CASE 3: Delete parent row REFERENCED by

child ❌ (DEFAULT)
DELETE FROM Student WHERE student_id = 3;
❌ Error Output
ERROR: update or delete on table "Student"
violates foreign key constraint
📌 Parent row cannot be deleted if child rows exist
CASE 4: Correct Way (Delete child first, then
parent) ✅
DELETE FROM Orders WHERE student_id = 3;
DELETE FROM Student WHERE student_id = 3;
Output
1 row deleted
Manual referential cleanup

CASE 5: ON DELETE CASCADE (Automatic

deletion)
Recreate child table
DROP TABLE Orders;

CREATE TABLE Orders (

order_id INT PRIMARY KEY,
student_id INT,
FOREIGN KEY (student_id)
REFERENCES Student(student_id)
 ON DELETE CASCADE
);

Insert sample data

INSERT INTO Orders VALUES (201, 4);
INSERT INTO Orders VALUES (202, 4);
Delete parent
DELETE FROM Student WHERE student_id = 4;
Result
Student 4 deleted
Orders 201, 202 automatically deleted

✅ CASCADE behavior observed

CASE 6: ON DELETE SET NULL

DROP TABLE Orders;
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
student_id INT,
FOREIGN KEY (student_id)
REFERENCES Student(student_id)
ON DELETE SET NULL
);

Insert data
INSERT INTO Student VALUES (11, 'Manoj');
INSERT INTO Orders VALUES (301, 11);

Delete parent
DELETE FROM Student WHERE student_id = 11;

Orders table output

order_id student_id
301 NULL

📌 Child row preserved, FK set to NULL

CASE 7: ON DELETE RESTRICT / NO ACTION

(Default)
ON DELETE RESTRICT
or

ON DELETE NO ACTION

Prevents parent deletion

CASE 8: DELETE using sub-condition (still fails)

DELETE FROM Student WHERE student_id IN (1,
2);
❌ Error
Foreign key constraint violation

SUBQUERIES

What is a Subquery?
Definition:
A subquery (inner query) is a SQL query
written inside another SQL query.
 The outer query depends on the result of
the inner query
 The inner query is executed first
General Syntax
SELECT column_name FROM
table_name WHERE column_name
operator ( SELECT column_name
FROM table_name WHERE condition );
Types of Subqueries (Important)
Based on Result Size

[Link]-row subquery
[Link]-row subquery
[Link]-column subquery

Based on Dependency
 [Link]-correlated subquery
[Link] subquery
Single Row subQuery
Definition:

A single row subquery is a subquery that returns

only one value (one row, one column).
Key Characteristics
 Returns exactly one value
 Used with single-row comparison
operators
 Inner query executes first
 Result is passed to the outer query
Operators Used

= > < >= <= <>

Sample Table (Student)
student_id name Dept marks
1 Ravi CSE 78
student_id name Dept marks
2 Anita CSE 92
3 Suresh CSE 60
4 Kiran ECE 70
5 Meena ECE 85
6 Arjun ECE 66
7 Ramesh ME 75
8 Geeta ME 88
9 Sunil ME 69
10 Pooja EEE 90
11 Ajay EEE 72
12 Neha EEE 65
13 Varun CSE 81
14 Divya ECE 91
15 Manoj ME 82
Question 1:
Find the names of students who scored more
than the average marks of all students.
SELECT name FROM Student WHERE
marks > (
SELECT AVG(marks) FROM
Student);
[Link] student(s) who scored the
highest marks.
SELECT name FROM Student WHERE
marks = (
SELECT MAX(marks) FROM
Student);

3. Find students who scored more than the

average marks of the CSE department.
SELECT name FROM Student WHERE
marks > (
SELECT AVG(marks) FROM
Student WHERE dept = 'CSE');
[Link] students who scored less than the
maximum marks of the ECE department.
SELECT name FROM Student
WHERE marks < (
 SELECT MAX(marks) FROM
Student WHERE dept = 'ECE');

[Link] whose marks are equal to the

average marks of the ME department.
SELECT name FROM Student WHERE marks = (
SELECT AVG(marks) FROM Student WHERE
dept = 'ME' );
6. Find students who scored more than the
minimum marks among EEE students.
SELECT name FROM Student WHERE marks > (
SELECT MIN(marks)
FROM Student WHERE dept = 'EEE');
7. Find students who scored exactly the same as
the highest CSE student.
SELECT name FROM Student WHERE marks = (
SELECT MAX(marks) FROM Student WHERE dept =
'CSE' );
8. Find students who scored less than the average
marks of all students.
SELECT name FROM Student WHERE marks < (
SELECT AVG(marks) FROM Student );

9. Find students who scored more than

the overall average but less than the overall
maximum.
SELECT name FROM Student WHERE marks > (
SELECT AVG(marks) FROM Student ) AND
marks < ( SELECT MAX(marks) FROM
Student );
[Link] students who scored more than the
average marks of their own department.
SELECT name FROM Student S WHERE marks
> ( SELECT AVG(marks) FROM Student
WHERE dept = [Link] );
11. Find students who scored the second
highest marks.
SELECT name FROM Student WHERE marks = (
SELECT MAX(marks) FROM Student WHERE
 marks < ( SELECT MAX(marks) FROM
Student ) );

Table: Employee
Employee(emp_id, emp_name, dept, salary,
join_year)

Q1. Find employees earning more than

the average salary of all employees.
SELECT emp_name FROM Employee WHERE
salary > ( SELECT AVG(salary) FROM
Employee);

Q2. Find employee(s) with the highest

salary.
SELECT emp_name FROM Employee WHERE
salary = (
SELECT MAX(salary) FROM Employee);
Q3. Find employee(s) with the lowest
salary.
SELECT emp_name FROM EmployeeWHERE
salary = ( SELECT MIN(salary) FROM
Employee);

Q4. Find employees earning more than

the average salary of the HR department.
SELECT emp_name FROM Employee WHERE
salary > ( SELECT AVG(salary) FROM
Employee WHERE dept = 'HR');

Q5. Find employees earning less than the

maximum salary in the IT department.
SELECT emp_name FROM Employee
WHERE salary < ( SELECT MAX(salary)
FROM Employee WHERE dept = 'IT');
Q6. Find employees who joined before the
earliest joining year of Finance
department.
SELECT emp_name FROM Employee WHERE
join_year < (
SELECT MIN(join_year) FROM Employee
WHERE dept = 'Finance');

Q7. Find employees who joined in the

same year as the most recently joined
employee.
SELECT emp_name FROM Employee WHERE
join_year = (
SELECT MAX(join_year) FROM
Employee);

Q8. Find employees earning exactly the

average salary of their department.
SELECT emp_name FROM Employee E
WHERE salary = ( SELECT AVG(salary)
FROM Employee WHERE dept = [Link]);
Q9. Find employees earning more than
the minimum salary in Sales department.
SELECT emp_name FROM Employee WHERE
salary > ( SELECT MIN(salary) FROM
Employee WHERE dept = 'Sales');

Q10. Find employees earning less than the

overall average salary.
SELECT emp_name FROM Employee WHERE
salary < ( SELECT AVG(salary) FROM
Employee);

Q11. Find employees earning the same

salary as the highest paid HR employee.
SELECT emp_name FROM Employee WHERE
salary = ( SELECT MAX(salary) FROM
Employee WHERE dept = 'HR');
Q12. Find employees who joined after the
average joining year of all employees.
SELECT emp_name FROM Employee WHERE
join_year > ( SELECT AVG(join_year)
FROM Employee);

Q13. Find employees earning more than

the second highest salary (nested single-
row).
SELECT emp_name FROM Employee WHERE
salary > ( SELECT MAX(salary) FROM
Employee WHERE salary < ( SELECT
MAX(salary) FROM Employee ));

Q14. Find employees earning the same as

the lowest paid employee in IT
department.
SELECT emp_name FROM Employee WHERE
salary = ( SELECT MIN(salary) FROM
Employee WHERE dept = 'IT');
Q15. Find employees who joined in the
earliest joining year.
SELECT emp_name FROM EmployeeWHERE
join_year = (
SELECT MIN(join_year) FROM
Employee);

Q16. Find employees earning more than

the average salary of their own
department.
SELECT emp_name FROM Employee E
WHERE salary > ( SELECT AVG(salary)
FROM Employee WHERE dept = [Link]);

Q17. Find employees earning less than the

maximum salary of their department.
SELECT emp_name FROM Employee E
WHERE salary < (
SELECT MAX(salary) FROM Employee
WHERE dept = [Link]
);
Q18. Find employees who joined before
the most senior employee in HR
department.
SELECT emp_name FROM Employee
WHERE join_year < (
SELECT MIN(join_year) FROM
Employee WHERE dept = 'HR'
);

Q19. Find employees earning exactly the

average salary of all employees.
SELECT emp_name FROM Employee
WHERE salary = ( SELECT AVG(salary)
FROM Employee);
Q20. Find employees earning more than
the highest salary in Finance department.
SELECT emp_name FROM Employee
WHERE salary > ( SELECT MAX(salary)
FROM Employee WHERE dept =
'Finance' );
 MULTI-ROW SUBQUERY
A multi-row subquery is a subquery that returns
more than one row.
Returns multiple values
Cannot use =, >, < directly
Must use special operators

Operators Used with Multi-Row

Subqueries
Operator Meaning
IN Matches any value
ANY At least one value
ALL Every value
NOT IN Excludes values
EXISTS Checks existence

Operator: IN
Meaning
Matches any one value from the subquery
result.
Question 1
Find students who belong to departments where
at least one student scored below 70.
SELECT name FROM Student WHERE dept IN
( SELECT dept FROM Student WHERE marks <
70);

roll name dept marks

1 Asha CSE 85
2 Ravi CSE 62
3 Neha EEE 90
4 Kiran EEE 55
5 Meena ME 78
6 Arjun ME 72
7 Pooja CE 68
8 Suman CE 88
 Step 1: Execute the INNER QUERY first
name dept marks marks < 70
Ravi CSE 62 ✔
Kiran EEE 55 ✔
Pooja CE 68 ✔
Inner query result set:
{ CSE, EEE, CE }
Step 2: Execute the OUTER QUERY
SELECT name, dept, marks FROM Student
WHERE dept IN ('CSE', 'EEE', 'CE');

🔹 Step 3: Row-by-row checking

Student dept IN list? Selected
Asha CSE ✔ Yes
Ravi CSE ✔ Yes
Neha EEE ✔ Yes
Kiran EEE ✔ Yes
Student dept IN list? Selected
Meena ME ❌ No
Arjun ME ❌ No
Pooja CE ✔ Yes
Suman CE ✔ Yes

Final Output
name dept marks
Asha CSE 85
Ravi CSE 62
Neha EEE 90
Kiran EEE 55

Query Using ANY

 SELECT name, dept, marks FROM Student s1
WHERE 70 > ANY ( SELECT [Link] FROM
Student s2 WHERE [Link] = [Link] );
S1 s2
roll name dept marks
1 Asha CSE 85
2 Ravi CSE 62
3 Neha EEE 90
4 Kiran EEE 55
nam de mar
5 Meena ME 78
e pt ks
6 Arjun ME 72
7 Pooja CE 68
8 Suman CE 88
ro
ll
CS
1 Asha 85
E
CS
2 Ravi 62
E
3 NehaEE 90
 E
Kira EE
4 55
n E
Mee M
5 78
na E
Arju M
6 72
n E
Pooj
7 CE 68
a
Sum
8 CE 88
an

Inner Query
Outer Condition
Result
Row Dept Marks Check (70 Result
(marks of
(s1) > ANY ?)
dept)
70>85 ❌,
Asha CSE 85 {85, 62} Selected
70>62 ✔
Ravi CSE 62 {85, 62} 70>62 ✔ Selected
70>90 ❌,
Neha EEE 90 {90, 55} Selected
70>55 ✔
Kiran EEE 55 {90, 55} 70>55 ✔ Selected
 Inner Query
Outer Condition
Result
Row Dept Marks Check (70 Result
(marks of
(s1) > ANY ?)
dept)
70>78 ❌, Not
Meena ME 78 {78, 72}
70>72 ❌ Selected
Not
Arjun ME 72 {78, 72} No TRUE
Selected
Pooja CE 68 {68, 88} 70>68 ✔ Selected
Suman CE 88 {68, 88} 70>68 ✔ Selected
Final Output
name dept marks
Asha CSE 85
Ravi CSE 62
Neha EEE 90
Kiran EEE 55
Pooja CE 68
Suman CE 88
 Inner query runs for each outer row
 ANY returns TRUE if at least one
comparison is TRUE
 Value-based comparison
 Correlated subquery
Query Using EXISTS
SELECT [Link], [Link], [Link] FROM
Student s1
WHERE EXISTS ( SELECT 1 FROM
Student s2 WHERE [Link] = [Link] AND
[Link] < 70);

Execution Table (Row by Row)

 Inner
Outer Query Match EXIST
Dep Mark Selected
Row Conditio Found S
t s ?
(s1) n ? Result
Checked
Ravi
Asha CSE 85 Yes TRUE Yes
(CSE, 62)
Ravi
Ravi CSE 62 Yes TRUE Yes
(CSE, 62)
Kiran
Neha EEE 90 Yes TRUE Yes
(EEE, 55)
Kiran
Kiran EEE 55 Yes TRUE Yes
(EEE, 55)
No ME
Meen
ME 78 marks < No FALSE No
a
70
No ME
Arjun ME 72 marks < No FALSE No
70
Pooja
Pooja CE 68 Yes TRUE Yes
(CE, 68)
Suma CE 88 Pooja Yes TRUE Yes
 Inner
Outer Query Match EXIST
Dep Mark Selected
Row Conditio Found S
t s ?
(s1) n ? Result
Checked
n (CE, 68)

Operator: ANY
Condition is true if it satisfies at least one
value.
Q)Find students who scored more than any CSE
student.
SELECT name FROM Student WHERE marks >
ANY ( SELECT marks
FROM Student WHERE dept = 'CSE');

Equivalent to:
marks > MIN(CSE marks)
Operator: ALL
 Condition must be true for all values.

Question 3
Find students who scored more than all ME
students.

SELECT name FROM Student WHERE marks

> ALL ( SELECT marks
FROM Student WHERE dept = 'ME');

Equivalent to:
marks > MAX(ME marks)
Operator: NOT IN
Excludes all values returned by subquery.
Question 4
Find students who are not from departments
where anyone scored below 70.
 SELECT name FROM Student WHERE dept
NOT IN ( SELECT dept
FROM Student WHERE marks < 70);

NOT IN fails if subquery returns NULL

Sample Student Table (Data)

roll name dept marks
1 Asha CSE 85
2 Ravi CSE 65
3 Neha ECE 78
4 Kiran ECE 82
5 MeenaME 69
6 Arjun ME 74
7 Sita CIVIL 88
8 Ramesh CIVIL 91
 Step 1: Execute the inner query
SELECT dept
FROM Student
WHERE marks < 70;

Students with marks < 70:

name dept
Ravi CSE
MeenaME

So the inner query result is:CSE, ME

These are departments to be excluded
 Step 2: Execute the outer query
SELECT name FROM Student WHERE
dept NOT IN (CSE, ME);
Departments allowed now:

✅ ECE

✅ CIVIL

✅ Final Output
name
Neha
Kiran
Sita
Ramesh
This is a multi-row subquery
 NOT IN filters out entire departments
If any student in a department scores
below 70 → whole department is rejected
Inner query runs first, outer query uses
its result

If the inner query returns NULL, NOT

IN gives no output.

Operator: EXISTS
Returns TRUE if subquery returns at
least one row.

Question 5
Find departments having at least one student
scoring above 90.
 SELECT DISTINCT [Link] FROM Student s1
WHERE EXISTS (
SELECT [Link] FROM Student s2
WHERE [Link] = [Link]
AND [Link] > 90);

S1 s2

rol mark mark

name dept roll name dept
l s s
1Asha CSE 85 1Asha CSE 85
2Ravi CSE 92 2Ravi CSE 92
3Neha ECE 78 3Neha ECE 78
4Kiran ECE 95 4Kiran ECE 95
5Meena ME 88 5Meena ME 88
6Arjun ME 91 6Arjun ME 91
CIVI CIVI
7Sita 72 7Sita 72
L L
Rames CIVI Rames CIVI
8 89 8 89
h L h L

Iteration 1
s1 row:
 name dept marks
Asha CSE 85
Inner Query Runs:
SELECT [Link] FROM Student s2 WHERE
[Link] = 'CSE' AND [Link] > 90;
Matches:
Ravi → 92
 Inner query returns a row
 EXISTS = TRUE
 CSE is selected

Iteration 2
s1 row
name dept marks
Ravi CSE 92

Inner query runs again for CSE

Ravi → 92
EXISTS = TRUE
CSE again (later removed by DISTINCT)
Iteration 3
s1 row:
name dept marks
Neha ECE 78
Inner query:
WHERE [Link] = 'ECE' AND [Link] > 90
Matches:
Kiran → 95
EXISTS = TRUE
ECE selected

Iteration 4
s1 row:
name dept marks
Kiran ECE 95

Same result
 👉 ECE selected again

Q6. Find students whose marks match any EEE

student’s marks.
SELECT name FROM Student WHERE marks
IN ( SELECT marks
FROM Student WHERE dept = 'EEE');

Q7. Find students who scored less than all ECE

students.
SELECT name FROM Student WHERE marks
< ALL ( SELECT marks
FROM Student WHERE dept = 'ECE');

Q8. Find students who scored more than at least

one ME student.
 SELECT name FROM Student WHERE marks
> ANY (
SELECT marks FROM Student WHERE
dept = 'ME');

Q9. Find departments where no student scored

below 65.
SELECT DISTINCT [Link] FROM Student s1
WHERE NOT EXISTS (
SELECT 1 FROM Student s2 WHERE
[Link] = [Link] AND [Link] < 65);

Or
SELECT DISTINCT dept FROM Student
WHERE dept NOT IN (
SELECT dept FROM Student WHERE
marks < 65);
What this does

 Inner query: finds departments where at

least one student scored below 65
 Outer query: removes those departments
 Result: departments where no student
scored below 65

NOT IN fails if the subquery returns NULL.

Example:
 If any row has dept = NULL or marks = NULL
 Then the inner query returns NULL
 Result of outer query → EMPTY SET
 NOT EXISTS is preferred
 NOT IN is unsafe with NULLs
What happens without alias (wrong)
SELECT DISTINCT dept FROM Student
WHERE NOT EXISTS ( SELECT 1 FROM
Student WHERE dept = dept AND marks <
65 );

Q10. Find students who do NOT belong to

departments having students with marks above 90.
SELECT name FROM Student
WHERE dept NOT IN (
SELECT dept
FROM Student
WHERE marks > 90
);
Correlated Subquery
A correlated subquery is a subquery that
depends on the outer query for its values and is
executed once for each row of the outer query.
  The inner query cannot run independently.

Outer query picks one row

Inner query uses values from that row
Inner query executes
Result is compared
Move to the next row
Repeated execution happens.

Basic Syntax
SELECT column FROM table1 t1 WHERE
condition OPERATOR (
SELECT aggregate_function(column)
FROM table2 t2 WHERE t2.common_column =
t1.common_column);

Example 2: Departments having at least one student

scoring above 90
Query using EXISTS
 SELECT DISTINCT [Link]
FROM Student S1
WHERE EXISTS (
SELECT *
FROM Student S2
WHERE [Link] = [Link]
AND [Link] > 90
);
Explanation

For each department, DBMS checks:“Is there

any student with marks > 90?”

If yes → department selected

Example 3: Students with highest marks in each

department
SELECT name, dept, marks FROM Student
SWHERE marks = (
 SELECT MAX(marks)
FROM Student
WHERE dept = [Link]
);
Simple database Schema

A database schema is a structure that

represents the logical storage of the data in
the database.
It represents the organization of data and
provides information about the relationships
between the tables in a database.
 A database schema is the logical
representation of a database, which shows
how the data is stored logically in the entire
database.
 It contains list of attributes and instructions
that inform the database engine how the data
is organized and how the elements are
related to each other.
Types of SQL Commands
Categor Full Form /
Examples Explanation
y Purpose
Commands to
define or modify
CREATE,
Data database objects
ALTER, DROP,
DDL Definition like tables, views,
TRUNCATE,
Language schemas. They
RENAME
change structure,
not data.
Commands to
INSERT, manipulate data
Data
UPDATE, inside tables.
DML Manipulatio
DELETE, Used for adding,
n Language
MERGE changing, or
removing rows.
Command to
retrieve data from
one or more tables.
Data Query
DQL SELECT Can include
Language
conditions, joins,
grouping,
ordering.
DCL Data Control GRANT, Commands to
Language REVOKE control
access/permission
Categor Full Form /
Examples Explanation
y Purpose
s of users on
database objects.
COMMIT, Commands to
ROLLBACK, control
Transaction
SAVEPOINT, transactions:
TCL Control
SET make permanent,
Language
TRANSACTIO undo, or set
N savepoints.

SQL functions(Date and Time, Numeric,

String conversion) Date and Time
Functions
JOINS in SQL

What is a JOIN?
A JOIN is used to combine rows from two or
more tables based on a related column.
In a relational database, data is stored in multiple
tables to avoid duplication.
The common column is usually:
 Primary key in one table
 Foreign key in another table
The main purpose of join is to retrieve the data from
multiple tables in other words Join is used to perform
multi-table queries.

Types of Join
There are many types of Joins in SQL. Depending on the
use case, you can use different types of SQL JOIN
clauses. Here are the frequently used SQL JOIN types:

1. Inner Join
Inner Join is a join operation in DBMS that combines two

or more tables based on related columns and returns only

r

rows that have matching values among tables. Inner join

has three types.
 Conditional join
 Equi Join
  Natural Join

What is NATURAL JOIN?

Definition (simple):
NATURAL JOIN automatically joins two
tables using all columns that have the same
name and compatible data types.
 No need to specify ON or WHERE
 Matching columns are used implicitly
 Duplicate join columns appear only once in
output
Example Tables
STUDENT
sid name dept MARKS
sid marks
1 Asha CSE
1 85
2 Ravi EEE
2 72
3 Meena CSE
3 90
4 John ME 5 60
 Common column: sid

SELECT * FROM Student NATURAL

JOIN Marks;

How SQL Executes It (Step-by-Step)

1. Finds common column name → sid
2. Matches rows where [Link] =
[Link]
3. Removes duplicate sid column
4. Returns only matching rows

Result Table
sid name dept marks
1 Asha CSE 85
2 Ravi EEE 72
3 Meena CSE 90
 sid = 4 → no match in MARKS
sid = 5 → no match in STUDENT
Equivalent query using INNER JOIN +
ON
SELECT * FROM Student JOIN Marks ON
[Link] = [Link];
Difference between Inner join and
Natural join
STUDENT
sid name dept MARKS
1 Asha CSE
2 Ravi EEE
3 Meena CSE
sid dept marks
1 CSE 85
2 CSE 72
3 CSE 90
Common columns: sid, dept

INNER JOIN (Explicit condition)

Query
SELECT * FROM Student INNER JOIN Marks ON
[Link] = [Link];
Result
sid name [Link] [Link] marks
1 Asha CSE CSE 85
2 Ravi EEE CSE 72
3 Meena CSE CSE 90

✔ Join based only on sid

✔ Department mismatch is still visible

NATURAL JOIN (Automatic join)

Query
SELECT *
FROM Student NATURAL JOIN Marks;
Condition applied internally
[Link] = [Link] AND [Link] = [Link]
Result
sid name dept marks
1 Asha CSE 85
3 Meena CSE 90

Row for Ravi (sid=2) is removed

(because EEE ≠ CSE)

Key Observation (Very Important)

Rows
Join Type Reason
Returned
INNER JOIN 3 rows Only sid matched
NATURAL sid and dept must
2 rows
JOIN match

Key Differences
Feature INNER JOIN NATURAL JOIN
Join condition Explicit (ON) Implicit (automatic)
Control Full control No control
Multiple common columns Safe Risky
Real-world usage Very common Rare
Readability Clear Short but unclear
Sample Tables (for all questions)
STUDENT
sid name dept
1 Asha CSE
2 Ravi EEE
3 Meena CSE
4 John ME

MARKS
sid subject marks
1 DBMS 85
1 OS 78
2 DBMS 70
3 DBMS 90
5 DBMS 60

Q1. Display student names with their DBMS marks.

SELECT name, marks
FROM Student
INNER JOIN Marks
ON [Link] = [Link]
WHERE subject = 'DBMS';
Q2. Show all students who have marks recorded.
SELECT DISTINCT name
FROM Student
INNER JOIN Marks
ON [Link] = [Link];

Q3. Display student name, subject, and marks.

SELECT name, subject, marks
FROM Student
INNER JOIN Marks
ON [Link] = [Link];

Q4. Find students who scored more than 80.

SELECT name, marks
FROM Student
INNER JOIN Marks
ON [Link] = [Link]
WHERE marks > 80;

Q5. Display CSE students and their marks.

SELECT name, subject, marks
FROM Student
INNER JOIN Marks
ON [Link] = [Link]
WHERE dept = 'CSE';

Q6. Find students who scored between 70 and 85.

SELECT name, marks
FROM Student
INNER JOIN Marks
ON [Link] = [Link]
WHERE marks BETWEEN 70 AND 85;

Q7. Find students who have taken more than one subject.
SELECT name
FROM Student
INNER JOIN Marks
ON [Link] = [Link]
GROUP BY name
HAVING COUNT(subject) > 1;

Q8. Find the highest mark scored by each student.

SELECT name, MAX(marks) AS max_marks
FROM Student
INNER JOIN Marks
ON [Link] = [Link]
GROUP BY name;

Q9. Find departments where students scored above 75.

SELECT DISTINCT dept
FROM Student
INNER JOIN Marks
ON [Link] = [Link]
WHERE marks > 75;

Q10. Count number of students per department who have

marks.
SELECT dept, COUNT(DISTINCT [Link]) AS student_count
FROM Student INNER JOIN Marks ON [Link] = [Link]
GROUP BY dept;
SQL JOIN PRACTICE – QUESTIONS, QUERIES AND OUTPUTS

Table 1: EMPLOYEE
emp_id name dept salary
1 Arjun CSE 60000
2 Bhavya EEE 55000
3 Charan CSE 58000
4 Divya CSE 72000
5 Farah CSE 68000
6 Gopal ME 52000
7 Heena ECE 61000
8 Irfan ME 54000
9 Jaya CSE 75000
10 Kiran EEE 50000
11 Latha ECE 57000
12 Mohan CSE 64000
13 Nisha ME 53000
14 Omkar ME 56000
15 Charan EEE 59000

Table 2: PROJECT
proj_id proj_name emp_id hours
101 AI 1 120
102 DBMS 1 80
103 Power 2 100
104 Web 4 150
105 ML 5 110
106 CAD 6 90
107 Auto 7 95
108 Cloud 9 140
109 Security 12 100
110 IoT 13 85
111 Design 14 75
112 Analysis 3 105
113 Network 8 90
114 Testing 10 60
115 Control 11 88

Practice Questions with Answers

1. List employee names with their project names.

Answer Query:
SELECT name, proj_name FROM Employee JOIN Project ON Employee.emp_id
= Project.emp_id;
Output:
All 15 employees are listed with their respective project names.

2. . Find employees working more than 100 hours.

Answer Query:
SELECT name, proj_name FROM Employee e JOIN Project p ON
e.emp_id=p.emp_id WHERE hours>100;

Output:
Arjun, Divya, Farah, Jaya, Charan

3. Find total project hours per employee.

Answer Query:
SELECT name, SUM(hours) FROM Employee e JOIN Project p ON
e.emp_id=p.emp_id GROUP BY name;

Output:
Arjun has highest total hours (200).

4. Find department-wise total project hours.

Answer Query:
SELECT dept, SUM(hours) FROM Employee e JOIN Project p ON
e.emp_id=p.emp_id GROUP BY dept;

Output:
CSE department has maximum total hours.

5. Find employee working on the highest-hour project.

Answer Query:
SELECT name FROM Employee e JOIN Project p ON e.emp_id=p.emp_id
WHERE hours=(SELECT MAX(hours) FROM Project);

Output:
Divya
Conditional Join
Conditional Join joins tables using a
condition other than equality, such as <,
>, BETWEEN.

EMPLOYEE
| empno | name | salary |
| ----- | ----- | ------ |
| 101 | Asha | 18000 |
| 102 | Ravi | 32000 |
| 103 | Neha | 47000 |
| 104 | Kiran | 62000 |
| 105 | Meena | 85000 |
Table 2: SALARY_GRADE

grade min_sal max_sal

G1 0 20000
G2 20001 40000
G3 40001 60000
G4 60001 100000

Q1 )Retrieve the name of each employee along with their salary

grade.

SELECT name, grade

FROM EMPLOYEE, SALARY_GRADE
WHERE salary BETWEEN min_sal
AND max_sal;
RESULT
name grade
Asha G1
Ravi G2
Neha G3
Kiran G4
Meena G4
Q2) List employees who belong to grade G3 or
higher.

SELECT name, grade

FROM EMPLOYEE, SALARY_GRADE
WHERE salary BETWEEN min_sal
AND max_sal
AND grade >= 'G3';
Q3) Find employees whose salary is above
40,000 and display their grade.
SELECT name, salary, grade
FROM EMPLOYEE, SALARY_GRADE
WHERE salary BETWEEN min_sal
AND max_sal
AND salary > 40000;
Q4) Count number of employees in each
salary grade.
SELECT grade, COUNT(*) AS
emp_count
FROM EMPLOYEE, SALARY_GRADE
WHERE salary BETWEEN min_sal
AND max_sal
GROUP BY grade;
Q5) Find the highest paid employee(s) and
their grade.
SELECT name, salary, grade
FROM EMPLOYEE, SALARY_GRADE
WHERE salary BETWEEN min_sal
AND max_sal
AND salary = (SELECT MAX(salary)
FROM EMPLOYEE);