Page 1 - Database Creation, Tables, DDL &

DML

Project
College Management System

This database manages:

 Students
 Teachers
 Departments
 Courses
 Employees
 Fees
 Marks

Step 1 Create Database

CREATE DATABASE CollegeManagementSystem;

Use the database

USE CollegeManagementSystem;

Database Structure
CollegeManagementSystem

│
├── Student
├── Teacher
├── Department
├── Course
├── Employee
├── Fees
└── Marks

We'll use these tables in every chapter.

Table Relationship
Department
|
-------------------------------
| | |
Student Teacher Employee
|
Marks
|
Course
|
Fees

Department Table
Every student, teacher and employee belongs to one department.

CREATE TABLE Department

(
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(50) NOT NULL,
Building VARCHAR(50),
HeadOfDepartment VARCHAR(50)
);

Insert Records

INSERT INTO Department

VALUES
(1,'Computer Science','Block A','Dr. Sharma'),
(2,'Mechanical','Block B','Dr. Rao'),
(3,'Electrical','Block C','Dr. Kumar'),
(4,'Civil','Block D','Dr. Singh'),
(5,'AI & Data Science','Block E','Dr. Mehta');

Department Table

DepartmentID DepartmentName Building HOD

1 Computer Science A Dr Sharma
2 Mechanical B Dr Rao
3 Electrical C Dr Kumar
DepartmentID DepartmentName Building HOD
4 Civil D Dr Singh
5 AI & Data Science E Dr Mehta

Student Table
CREATE TABLE Student
(
StudentID INT PRIMARY KEY,
StudentName VARCHAR(50),
Age INT,
Gender VARCHAR(10),
DepartmentID INT,
Year INT,
Semester INT,
City VARCHAR(30),
Phone VARCHAR(15),
FOREIGN KEY (DepartmentID)
REFERENCES Department(DepartmentID)
);

Insert Students

INSERT INTO Student

VALUES
(101,'Akash',21,'Male',5,3,5,'Bangalore','9876543210'),
(102,'Rahul',20,'Male',1,2,3,'Delhi','9876543211'),
(103,'Priya',22,'Female',2,4,7,'Chennai','9876543212'),
(104,'Neha',21,'Female',5,3,5,'Hyderabad','9876543213'),
(105,'Rohit',23,'Male',3,4,8,'Mumbai','9876543214'),
(106,'Sneha',20,'Female',4,2,4,'Pune','9876543215'),
(107,'Kiran',21,'Male',1,3,6,'Bangalore','9876543216'),
(108,'Anjali',22,'Female',5,4,7,'Delhi','9876543217');

Student Table

ID Name Dept Year Semester City

101 Akash AI&DS 3 5 Bangalore
102 Rahul CSE 2 3 Delhi
103 Priya Mechanical 4 7 Chennai
104 Neha AI&DS 3 5 Hyderabad
105 Rohit Electrical 4 8 Mumbai
106 Sneha Civil 2 4 Pune
107 Kiran CSE 3 6 Bangalore
108 Anjali AI&DS 4 7 Delhi
Teacher Table
CREATE TABLE Teacher
(
TeacherID INT PRIMARY KEY,
TeacherName VARCHAR(50),
DepartmentID INT,
Qualification VARCHAR(50),
Experience INT,
Salary DECIMAL(10,2),
Phone VARCHAR(15),
FOREIGN KEY (DepartmentID)
REFERENCES Department(DepartmentID)
);

Insert Records

INSERT INTO Teacher

VALUES
(201,'Dr Sharma',1,'PhD',15,95000,'987650001'),
(202,'Dr Rao',2,'PhD',18,98000,'987650002'),
(203,'Dr Kumar',3,'MTech',12,82000,'987650003'),
(204,'Dr Singh',4,'PhD',20,105000,'987650004'),
(205,'Dr Mehta',5,'PhD',10,90000,'987650005');

Employee Table
Non-teaching staff

CREATE TABLE Employee

(
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(50),
DepartmentID INT,
Designation VARCHAR(50),
Salary DECIMAL(10,2),
City VARCHAR(30),
FOREIGN KEY (DepartmentID)
REFERENCES Department(DepartmentID)
);

Insert Data
INSERT INTO Employee
VALUES
(301,'Mahesh',1,'Lab Assistant',35000,'Bangalore'),
(302,'Suresh',2,'Clerk',30000,'Delhi'),
(303,'Ramesh',3,'Accountant',45000,'Mumbai'),
(304,'Ganesh',5,'System Admin',60000,'Hyderabad'),
(305,'Naresh',4,'Office Assistant',28000,'Pune');

Course Table
CREATE TABLE Course
(
CourseID INT PRIMARY KEY,
CourseName VARCHAR(50),
DepartmentID INT,
Credits INT,
TeacherID INT,
FOREIGN KEY(DepartmentID)
REFERENCES Department(DepartmentID),

FOREIGN KEY(TeacherID)
REFERENCES Teacher(TeacherID)
);

Insert Courses

INSERT INTO Course

VALUES
(401,'Java Programming',1,4,201),
(402,'Thermodynamics',2,4,202),
(403,'Power Systems',3,3,203),
(404,'Surveying',4,3,204),
(405,'Machine Learning',5,4,205);

Fees Table
CREATE TABLE Fees
(
ReceiptNo INT PRIMARY KEY,
StudentID INT,
TotalFees DECIMAL(10,2),
PaidAmount DECIMAL(10,2),
PendingAmount DECIMAL(10,2),
FOREIGN KEY(StudentID)
REFERENCES Student(StudentID)
);

Insert Records

INSERT INTO Fees

VALUES
(501,101,100000,90000,10000),
(502,102,90000,90000,0),
(503,103,95000,80000,15000),
(504,104,100000,100000,0),
(505,105,90000,85000,5000),
(506,106,85000,85000,0),
(507,107,90000,60000,30000),
(508,108,100000,95000,5000);

Marks Table
CREATE TABLE Marks
(
MarkID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
InternalMarks INT,
ExternalMarks INT,
TotalMarks INT,
Grade CHAR(2),

FOREIGN KEY(StudentID)
REFERENCES Student(StudentID),

FOREIGN KEY(CourseID)
REFERENCES Course(CourseID)
);

Insert Records

INSERT INTO Marks

VALUES
(1,101,405,28,60,88,'A'),
(2,102,401,25,58,83,'A'),
(3,103,402,22,55,77,'B'),
(4,104,405,30,65,95,'O'),
(5,105,403,20,50,70,'B'),
(6,106,404,18,45,63,'C'),
(7,107,401,27,59,86,'A'),
(8,108,405,29,62,91,'O');

Verify the Data

Display all students

SELECT * FROM Student;

Display all teachers

SELECT * FROM Teacher;

Display all employees

SELECT * FROM Employee;

Display all departments

SELECT * FROM Department;

Display all courses

SELECT * FROM Course;

Display all fees

SELECT * FROM Fees;

Display all marks

SELECT * FROM Marks;

DDL Commands Practiced

CREATE DATABASE

USE DATABASE

CREATE TABLE

DML Commands Practiced

INSERT INTO

What You'll Learn Using These Tables

Topic Table(s)
SELECT Student
WHERE Student, Employee
ORDER BY Student
GROUP BY Student, Teacher
HAVING Student, Teacher
Aggregate Functions Marks, Employee
INNER JOIN Student + Department
LEFT JOIN Teacher + Department
RIGHT JOIN Student + Fees
FULL JOIN Department + Student (where supported)
SELF JOIN Employee
CROSS JOIN Student + Course
Single Row Functions (SRF) Student, Teacher
 Topic Table(s)
Multiple Row Functions (MRF) Marks, Employee
Subqueries Student, Marks
Views Student + Department
Stored Procedures Student
Functions Marks
Triggers Fees
Transactions (TCL) Fees, Employee

Next (Page 2)
[‘]

 SELECT
 WHERE
 ORDER BY
 DISTINCT
 LIMIT
 TOP (SQL Server)
 LIKE
 BETWEEN
 IN
 NOT IN
 IS NULL
 IS NOT NULL

Page 2 – SELECT, WHERE, ORDER BY,

DISTINCT, LIMIT, LIKE, IN, BETWEEN,
NULL
In this page, we'll use the same College Management System database created in Page 1.

Database Tables
CollegeManagementSystem

Department
Student
Teacher
Employee
Course
Fees
Marks
1. SELECT Statement
The SELECT statement is used to retrieve data from one or more tables.

Syntax
SELECT column_name
FROM table_name;

Retrieve all student details.

SELECT * FROM Student;

Output

StudentID StudentName Age Gender DepartmentID Year Semester City

101 Akash 21 Male 5 3 5 Bangalore
102 Rahul 20 Male 1 2 3 Delhi
103 Priya 22 Female 2 4 7 Chennai
104 Neha 21 Female 5 3 5 Hyderabad
105 Rohit 23 Male 3 4 8 Mumbai
106 Sneha 20 Female 4 2 4 Pune
107 Kiran 21 Male 1 3 6 Bangalore
108 Anjali 22 Female 5 4 7 Delhi

Display only student names.

SELECT StudentName
FROM Student;

Display student name and city.

SELECT StudentName, City

FROM Student;

Display student name, year and semester.

SELECT StudentName, Year, Semester

FROM Student;
2. WHERE Clause
Filters rows based on a condition.

Syntax
SELECT *
FROM Student
WHERE condition;

Students from Bangalore.

SELECT *
FROM Student
WHERE City='Bangalore';

Output

StudentName City
Akash Bangalore
Kiran Bangalore

Students older than 21.

SELECT *
FROM Student
WHERE Age>21;

Students from AI & DS department.

DepartmentID = 5

SELECT *
FROM Student
WHERE DepartmentID=5;

Students studying in 4th year.

SELECT *
FROM Student
WHERE Year=4;
Students in Semester 5.

SELECT *
FROM Student
WHERE Semester=5;

Teachers earning more than ₹90,000.

SELECT *
FROM Teacher
WHERE Salary>90000;

Employees working in Bangalore.

SELECT *
FROM Employee
WHERE City='Bangalore';

3. Comparison Operators
Operator Meaning
= Equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
!= Not equal
<> Not equal

Salary greater than or equal to 60000.

SELECT *
FROM Employee
WHERE Salary>=60000;

Department not equal to AI & DS.

SELECT *
FROM Student
WHERE DepartmentID<>5;

4. AND Operator
Both conditions must be true.
Students from Bangalore AND studying in Year 3.

SELECT *
FROM Student
WHERE City='Bangalore'
AND Year=3;

Employees from Bangalore earning more than ₹30,000.

SELECT *
FROM Employee
WHERE City='Bangalore'
AND Salary>30000;

5. OR Operator
Either condition can be true.

Students from Bangalore OR Delhi.

SELECT *
FROM Student
WHERE City='Bangalore'
OR City='Delhi';

Teachers with salary greater than ₹95,000 OR experience greater than 15 years.

SELECT *
FROM Teacher
WHERE Salary>95000
OR Experience>15;

6. NOT Operator
Reverse the condition.

Students NOT from Bangalore.

SELECT *
FROM Student
WHERE NOT City='Bangalore';

Employees NOT working in Department 1.

SELECT *
FROM Employee
WHERE NOT DepartmentID=1;
7. ORDER BY
Sorts the data.

Ascending order (default).

SELECT *
FROM Student
ORDER BY StudentName;

Descending order.

SELECT *
FROM Student
ORDER BY StudentName DESC;

Sort teachers by salary.

SELECT *
FROM Teacher
ORDER BY Salary DESC;

Sort employees by city.

SELECT *
FROM Employee
ORDER BY City ASC;

Sort by year first and semester second.

SELECT *
FROM Student
ORDER BY Year, Semester;

8. DISTINCT
Removes duplicate values.

Display all unique cities.

SELECT DISTINCT City

FROM Student;

Output
 City
Bangalore
Delhi
Hyderabad
Mumbai
Pune
Chennai

Display unique departments.

SELECT DISTINCT DepartmentID

FROM Student;

9. LIMIT (MySQL)
Returns only a specified number of rows.

First three students.

SELECT *
FROM Student
LIMIT 3;

Top two highest-paid teachers.

SELECT *
FROM Teacher
ORDER BY Salary DESC
LIMIT 2;

Students after skipping first two records.

SELECT *
FROM Student
LIMIT 2,3;

This means:

 Skip first 2 rows

 Display next 3 rows

SQL Server Equivalent

Instead of LIMIT, SQL Server uses TOP.

SELECT TOP 3 *
FROM Student;

10. LIKE Operator

Used for pattern matching.

Starts with A
SELECT *
FROM Student
WHERE StudentName LIKE 'A%';

Ends with h.

SELECT *
FROM Student
WHERE StudentName LIKE '%h';

Contains "ha".

SELECT *
FROM Student
WHERE StudentName LIKE '%ha%';

Second letter is k.

SELECT *
FROM Student
WHERE StudentName LIKE '_k%';

Exactly five letters.

SELECT *
FROM Student
WHERE StudentName LIKE '_____';

Wildcards
Wildcard Meaning
% Any number of characters
_ Exactly one character

11. BETWEEN
Inclusive range.

Students aged between 20 and 22.

SELECT *
FROM Student
WHERE Age BETWEEN 20 AND 22;

Teachers earning between ₹85,000 and ₹1,00,000.

SELECT *
FROM Teacher
WHERE Salary BETWEEN 85000 AND 100000;

12. IN Operator
Checks multiple values.

Students from Bangalore, Delhi or Pune.

SELECT *
FROM Student
WHERE City IN ('Bangalore','Delhi','Pune');

Employees from Department 1 or 5.

SELECT *
FROM Employee
WHERE DepartmentID IN (1,5);

13. NOT IN
Students not from Bangalore or Delhi.
SELECT *
FROM Student
WHERE City NOT IN ('Bangalore','Delhi');

14. IS NULL
Find missing values.

Suppose some phone numbers are missing.

SELECT *
FROM Student
WHERE Phone IS NULL;

15. IS NOT NULL

SELECT *
FROM Student
WHERE Phone IS NOT NULL;

16. Aliases (AS)

Rename columns temporarily.

SELECT StudentName AS Name,

City AS Location
FROM Student;

Rename table using alias.

SELECT [Link],
[Link]
FROM Student S;

17. Arithmetic Expressions

Increase employee salary by ₹5,000 (display only).

SELECT EmployeeName,
Salary,
Salary+5000 AS NewSalary
FROM Employee;

Calculate remaining fees.

SELECT StudentID,
TotalFees,
PaidAmount,
TotalFees-PaidAmount AS Remaining
FROM Fees;

Summary
Clause Purpose
SELECT Retrieve data
WHERE Filter rows
ORDER BY Sort rows
DISTINCT Remove duplicates
LIMIT Restrict rows (MySQL)
TOP Restrict rows (SQL Server)
LIKE Pattern matching
BETWEEN Range filtering
IN Multiple values
NOT IN Exclude values
IS NULL Find NULL values
IS NOT NULL Find non-NULL values
AS Alias for column/table

Practice Questions
1. Display all students.
2. Display only student names and cities.
3. Find students from Delhi.
4. Find students older than 21.
5. Find teachers earning more than ₹90,000.
6. Display employees sorted by salary (highest first).
7. Show all unique student cities.
8. Display the first five students.
9. Find students whose names start with 'A'.
10. Find students whose names end with 'a'.
11. Find students aged between 20 and 22.
12. Find students from Bangalore, Delhi, or Hyderabad.
13. Find students not from Bangalore.
14. Display students whose phone number is not NULL.
15. Show employee salaries increased by ₹5,000 using an alias.
SQL Complete Notes (College Management System)
Page 3 – Aggregate Functions (MRF), GROUP BY, HAVING
This page covers one of the most frequently asked SQL interview topics.
Almost every SQL interview includes questions on COUNT, SUM, AVG, MIN,
MAX, GROUP BY, and HAVING.

SQL Execution Order

When you write:
SELECT DepartmentID, COUNT(*)
FROM Student
WHERE Age >= 21
GROUP BY DepartmentID
HAVING COUNT(*) > 1
ORDER BY DepartmentID;
SQL executes it in this order:

Execution
Clause Purpose
Order

1 FROM Choose the table

2 WHERE Filter rows

GROUP
3 Create groups
BY

4 HAVING Filter groups

5 SELECT Display columns

ORDER
6 Sort output
BY

Return limited
7 LIMIT
rows

Remember:
FROM
↓
WHERE
↓
GROUP BY
↓
HAVING
↓
SELECT
↓
ORDER BY
↓
LIMIT

Aggregate Functions (MRF)

MRF = Multiple Row Functions
They operate on multiple rows and return one result.

Functio
Purpose
n

COUNT(
Count rows
)

SUM() Add values

AVG() Average

Smallest
MIN()
value

Largest
MAX()
value

COUNT()
Count total students.
SELECT COUNT(*)
FROM Student;
Output

COUNT(
*)

Count students whose age is greater than 20.

SELECT COUNT(*)
FROM Student
WHERE Age>20;

Count students from AI & DS.

SELECT COUNT(*)
FROM Student
WHERE DepartmentID=5;

Count employees.
SELECT COUNT(*)
FROM Employee;

Count teachers.
SELECT COUNT(*)
FROM Teacher;

Count students from Bangalore.

SELECT COUNT(*)
FROM Student
WHERE City='Bangalore';

SUM()
Total salary of all teachers.
SELECT SUM(Salary)
FROM Teacher;

Total employee salary.

SELECT SUM(Salary)
FROM Employee;

Total fees collected.

SELECT SUM(PaidAmount)
FROM Fees;

Total pending fees.

SELECT SUM(PendingAmount)
FROM Fees;

AVG()
Average teacher salary.
SELECT AVG(Salary)
FROM Teacher;

Average employee salary.

SELECT AVG(Salary)
FROM Employee;

Average marks.
SELECT AVG(TotalMarks)
FROM Marks;

Average internal marks.

SELECT AVG(InternalMarks)
FROM Marks;

MAX()
Highest teacher salary.
SELECT MAX(Salary)
FROM Teacher;

Highest employee salary.

SELECT MAX(Salary)
FROM Employee;
Highest student marks.
SELECT MAX(TotalMarks)
FROM Marks;

Highest fee paid.

SELECT MAX(PaidAmount)
FROM Fees;

MIN()
Lowest teacher salary.
SELECT MIN(Salary)
FROM Teacher;

Lowest employee salary.

SELECT MIN(Salary)
FROM Employee;

Lowest marks.
SELECT MIN(TotalMarks)
FROM Marks;

Smallest pending fee.

SELECT MIN(PendingAmount)
FROM Fees;

GROUP BY
GROUP BY groups rows having the same value.
Syntax
SELECT column_name,
aggregate_function(column_name)
FROM table_name
GROUP BY column_name;
Count students department-wise
SELECT DepartmentID,
COUNT(*) AS TotalStudents
FROM Student
GROUP BY DepartmentID;
Example Output

Departmen TotalStude
tID nts

1 2

2 1

3 1

4 1

5 3

Count students city-wise

SELECT City,
COUNT(*)
FROM Student
GROUP BY City;

Average marks department-wise

SELECT DepartmentID,
AVG(TotalMarks)
FROM Student
JOIN Marks
ON [Link]=[Link]
GROUP BY DepartmentID;

Total salary department-wise

SELECT DepartmentID,
SUM(Salary)
FROM Employee
GROUP BY DepartmentID;

Maximum salary department-wise

SELECT DepartmentID,
MAX(Salary)
FROM Teacher
GROUP BY DepartmentID;

Minimum salary department-wise

SELECT DepartmentID,
MIN(Salary)
FROM Teacher
GROUP BY DepartmentID;

Total fees paid by students

SELECT StudentID,
SUM(PaidAmount)
FROM Fees
GROUP BY StudentID;

Count teachers by qualification

SELECT Qualification,
COUNT(*)
FROM Teacher
GROUP BY Qualification;

GROUP BY with ORDER BY

SELECT DepartmentID,
COUNT(*)
FROM Student
GROUP BY DepartmentID
ORDER BY DepartmentID;

Sort by highest number of students.

SELECT DepartmentID,
COUNT(*) AS TotalStudents
FROM Student
GROUP BY DepartmentID
ORDER BY TotalStudents DESC;

HAVING Clause
HAVING filters groups, while WHERE filters rows.
Syntax
SELECT column_name,
aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING condition;

Departments having more than one student

SELECT DepartmentID,
COUNT(*)
FROM Student
GROUP BY DepartmentID
HAVING COUNT(*)>1;
Output

Departmen COUNT(
tID *)

1 2

5 3

Cities having more than one student

SELECT City,
 COUNT(*)
FROM Student
GROUP BY City
HAVING COUNT(*)>1;

Departments whose average marks are above 85

SELECT DepartmentID,
AVG(TotalMarks)
FROM Student
JOIN Marks
ON [Link]=[Link]
GROUP BY DepartmentID
HAVING AVG(TotalMarks)>85;

Departments whose employee salary is greater than ₹40,000

SELECT DepartmentID,
AVG(Salary)
FROM Employee
GROUP BY DepartmentID
HAVING AVG(Salary)>40000;

Qualifications having more than one teacher

SELECT Qualification,
COUNT(*)
FROM Teacher
GROUP BY Qualification
HAVING COUNT(*)>1;

WHERE vs HAVING
WHERE
Filters rows before grouping.
SELECT *
FROM Student
WHERE Age>20;

HAVING
Filters groups after grouping.
SELECT DepartmentID,
COUNT(*)
FROM Student
GROUP BY DepartmentID
HAVING COUNT(*)>1;

WHERE + GROUP BY + HAVING Together

Find departments where students older than 20 are more than one.
SELECT DepartmentID,
COUNT(*) AS TotalStudents
FROM Student
WHERE Age>20
GROUP BY DepartmentID
HAVING COUNT(*)>1;
Execution:
1. WHERE filters students with Age > 20.
2. GROUP BY groups remaining students by department.
3. HAVING keeps only departments with more than one student.

Aggregate Functions with Aliases

SELECT DepartmentID,
COUNT(*) AS Students,
AVG(Age) AS AverageAge,
MAX(Age) AS OldestStudent,
MIN(Age) AS YoungestStudent
FROM Student
GROUP BY DepartmentID;
Common Interview Questions
Highest teacher salary
SELECT MAX(Salary)
FROM Teacher;

Lowest marks
SELECT MIN(TotalMarks)
FROM Marks;

Total pending fees

SELECT SUM(PendingAmount)
FROM Fees;

Average employee salary

SELECT AVG(Salary)
FROM Employee;

Department with the maximum number of students

SELECT DepartmentID,
COUNT(*) AS TotalStudents
FROM Student
GROUP BY DepartmentID
ORDER BY TotalStudents DESC
LIMIT 1;

Summary

Clause /
Purpose
Function

COUNT() Count rows

Total of numeric
SUM()
values
Clause /
Purpose
Function

AVG() Average value

MIN() Smallest value

MAX() Largest value

GROUP BY Group similar rows

Filter grouped
HAVING
results

ORDER BY Sort output

Practice Questions
1. Count total students.
2. Count students in each department.
3. Count students in each city.
4. Find the average age of students in each department.
5. Find the total salary of employees in each department.
6. Find the highest-paid teacher.
7. Find the lowest-paid employee.
8. Find the average marks for each department.
9. Show departments having more than two students.
[Link] cities having more than one student.
[Link] qualifications with more than one teacher.
[Link] departments where the average employee salary is greater than
₹40,000.
[Link] the department with the highest number of students.
[Link] the total pending fees.
[Link] the maximum and minimum student marks.
SQL Complete Notes (College Management System)
Page 4 – SQL Joins (Most Important Interview Topic)
Joins are used to combine data from two or more tables using a related
column (usually a Primary Key and Foreign Key).
In our College Management System, we have these relationships:
Department (DepartmentID)
↑
│
┌──────┼────────┐
│ │ │
Student Teacher Employee
│
 │ StudentID
▼
Marks
│
│ CourseID
▼
Course

Tables We'll Use

Department

Departmen DepartmentNa
tID me

Computer
1
Science

2 Mechanical

3 Electrical

4 Civil

AI & Data
5
Science

Student

Student StudentNa Departmen

ID me tID

101 Akash 5

102 Rahul 1

103 Priya 2

104 Neha 5

105 Rohit 3

106 Sneha 4

107 Kiran 1

108 Anjali 5
What is a JOIN?
Suppose you want to display:
Student Name Department Name
The Student table only contains DepartmentID, not the department name.
Student

StudentNa Departmen
me tID

Akash 5

Department

Departmen DepartmentNa
tID me

AI & Data
5
Science

To get:

StudentNa DepartmentNa
me me

AI & Data
Akash
Science

we use a JOIN.

Types of Joins

Join Purpose

INNER JOIN Matching records only

All rows from left table + matching

LEFT JOIN
rows

All rows from right table +

RIGHT JOIN
matching rows

FULL OUTER
All rows from both tables
JOIN

CROSS JOIN Cartesian product

SELF JOIN Join a table with itself

1. INNER JOIN
Returns only matching records.
Syntax
SELECT columns
FROM Table1
INNER JOIN Table2
ON [Link] = [Link];

Student Name with Department Name

SELECT [Link],
[Link],
[Link]
FROM Student
INNER JOIN Department
ON [Link] = [Link];
Output

Student StudentNa DepartmentNa

ID me me

AI & Data
101 Akash
Science

Computer
102 Rahul
Science

103 Priya Mechanical

AI & Data
104 Neha
Science

105 Rohit Electrical

106 Sneha Civil

Computer
107 Kiran
Science

AI & Data
108 Anjali
Science

Using Aliases
SELECT [Link],
[Link]
FROM Student S
INNER JOIN Department D
ON [Link] = [Link];

Teacher with Department

SELECT [Link],
[Link]
FROM Teacher T
INNER JOIN Department D
ON [Link] = [Link];

Employee with Department

SELECT [Link],
[Link],
[Link]
FROM Employee E
INNER JOIN Department D
ON [Link] = [Link];

Course with Teacher

SELECT [Link],
[Link]
FROM Course C
INNER JOIN Teacher T
ON [Link] = [Link];

INNER JOIN Diagram

Student Department

************* *************
*************======*************
*************
Only the common (matching) rows are returned.
2. LEFT JOIN
Returns:
 All rows from the left table
 Matching rows from the right table
 If no match exists, returns NULL
Syntax
SELECT columns
FROM Table1
LEFT JOIN Table2
ON [Link] = [Link];

All Students with Department

SELECT [Link],
[Link]
FROM Student S
LEFT JOIN Department D
ON [Link] = [Link];
If a student has no matching department:

DepartmentNa
StudentName
me

AI & Data
Akash
Science

Unknown
NULL
Student

All Courses with Teacher

SELECT [Link],
[Link]
FROM Course C
LEFT JOIN Teacher T
ON [Link] = [Link];
LEFT JOIN Diagram
Student Department

*************======*************
*************
*************
Everything from the left table is returned.

3. RIGHT JOIN
Returns:
 All rows from the right table
 Matching rows from the left table
SELECT [Link],
[Link]
FROM Student S
RIGHT JOIN Department D
ON [Link] = [Link];
Suppose a department has no students:

DepartmentNa StudentNa
me me

Finance NULL

RIGHT JOIN Diagram

Student Department

*************
*************======*************
*************
Everything from the right table is returned.
Note: MySQL supports RIGHT JOIN, but many developers prefer LEFT JOIN by
reversing the table order because it is often easier to read.

4. FULL OUTER JOIN

Returns:
 All rows from the left table
 All rows from the right table
 Matching rows are merged
 Non-matching rows contain NULL
SELECT [Link],
[Link]
FROM Student S
FULL OUTER JOIN Department D
ON [Link] = [Link];

MySQL Note
MySQL does not support FULL OUTER JOIN directly.
Equivalent using UNION:
SELECT [Link],
[Link]
FROM Student S
LEFT JOIN Department D
ON [Link] = [Link]

UNION

SELECT [Link],
[Link]
FROM Student S
RIGHT JOIN Department D
ON [Link] = [Link];

FULL JOIN Diagram

*************======*************
Everything from both tables.
5. CROSS JOIN
Returns the Cartesian Product.
If:
 Student has 8 rows
 Course has 5 rows
Result:
8 × 5 = 40 rows
SELECT StudentName,
CourseName
FROM Student
CROSS JOIN Course;

Example

Stude Cours
nt e

Akash Java

Akash ML

Rahul Java

Rahul ML

Every student is paired with every course.

CROSS JOIN Diagram

Every row × Every row

6. SELF JOIN
A table joined with itself.
Suppose the Employee table has:

Employee EmployeeNa Manager

ID me ID

301 Mahesh 304

302 Suresh 304

304 Ganesh NULL

Query:
SELECT [Link] AS Employee,
[Link] AS Manager
FROM Employee E
LEFT JOIN Employee M
ON [Link] = [Link];
Output

Employ Manag
ee er

Mahesh Ganesh

Suresh Ganesh

Ganesh NULL

Multiple Table Join

Student → Department → Marks
SELECT [Link],
[Link],
[Link]
FROM Student S
INNER JOIN Department D
ON [Link] = [Link]
INNER JOIN Marks M
ON [Link] = [Link];

Four-Table Join
Student + Department + Marks + Course
SELECT [Link],
[Link],
[Link],
[Link]
FROM Student S
INNER JOIN Department D
ON [Link] = [Link]
INNER JOIN Marks M
ON [Link] = [Link]
INNER JOIN Course C
ON [Link] = [Link];

Five-Table Join
Student + Department + Marks + Course + Teacher
SELECT [Link],
[Link],
[Link],
[Link],
[Link]
FROM Student S
INNER JOIN Department D
ON [Link] = [Link]
INNER JOIN Marks M
ON [Link] = [Link]
INNER JOIN Course C
ON [Link] = [Link]
INNER JOIN Teacher T
ON [Link] = [Link];

Common Interview Queries

1. Student name with department
SELECT [Link],
[Link]
FROM Student S
JOIN Department D
ON [Link] = [Link];

2. Teacher with department

SELECT [Link],
[Link]
FROM Teacher T
JOIN Department D
ON [Link] = [Link];

3. Course with teacher

SELECT [Link],
[Link]
FROM Course C
JOIN Teacher T
ON [Link] = [Link];

4. Student with marks

SELECT [Link],
[Link]
FROM Student S
JOIN Marks M
ON [Link] = [Link];

5. Student with fee details

SELECT [Link],
[Link],
[Link],
[Link]
FROM Student S
JOIN Fees F
ON [Link] = [Link];

Summary

Join Returns

INNER JOIN Only matching rows

Join Returns

LEFT JOIN All left rows + matching right rows

RIGHT JOIN All right rows + matching left rows

FULL OUTER All rows from both tables (not directly supported
JOIN in MySQL)

CROSS JOIN Every combination of rows

SELF JOIN A table joined with itself

Practice Questions
1. Display student names with department names.
2. Display teacher names with department names.
3. Display employee names with department names.
4. Display courses with teacher names.
5. Display student names with total marks.
6. Display student names with fee details.
7. Display student, department, course, and marks using four tables.
8. Display student, department, course, teacher, and marks using five tables.
9. Write a LEFT JOIN to show all departments even if no students belong to
them.
[Link] a SELF JOIN to display employees and their managers.

SQL Complete Notes (College Management System)

Page 5 – Subqueries, SRF, MRF, CASE, UNION, Views, Indexes, TCL, DCL,
Stored Procedures & Triggers
This is the final page of the SQL course. By the end of this page, you'll have
covered most SQL concepts commonly asked in interviews.

1. Subqueries
A subquery is a query inside another query.
Outer Query
|
|---- Inner Query (Subquery)
Types of Subqueries
1. Single Row Subquery
2. Multiple Row Subquery
3. Correlated Subquery

Single Row Subquery

Returns only one row.
Example 1
Find students whose age is equal to the maximum age.
SELECT *
FROM Student
WHERE Age = (
SELECT MAX(Age)
FROM Student
);
Output

Student StudentNa Ag
ID me e

105 Rohit 23

Example 2
Find the teacher earning the highest salary.
SELECT *
FROM Teacher
WHERE Salary = (
SELECT MAX(Salary)
FROM Teacher
);

Example 3
Find employees earning more than the average salary.
SELECT *
FROM Employee
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employee
);

Multiple Row Subquery

Returns multiple rows.
Use:
 IN
 ANY
 ALL

Example
Find students belonging to departments where teachers have more than 15
years of experience.
SELECT *
FROM Student
WHERE DepartmentID IN
(
SELECT DepartmentID
FROM Teacher
WHERE Experience>15
);

Using ANY
SELECT *
FROM Employee
WHERE Salary >
ANY
(
SELECT Salary
FROM Employee
WHERE DepartmentID=1
);

Using ALL
SELECT *
FROM Employee
WHERE Salary >
ALL
(
SELECT Salary
FROM Employee
WHERE DepartmentID=1
);

Correlated Subquery
The inner query depends on the outer query.
Example
SELECT StudentName
FROM Student S
WHERE EXISTS
(
SELECT *
FROM Fees F
WHERE [Link]=[Link]
);

EXISTS
Returns TRUE if the subquery returns at least one row.
SELECT *
FROM Department D
WHERE EXISTS
(
SELECT *
FROM Student S
WHERE [Link]=[Link]
);

NOT EXISTS
SELECT *
FROM Department D
WHERE NOT EXISTS
(
SELECT *
FROM Student S
WHERE [Link]=[Link]
);

Single Row Functions (SRF)

SRFs work on one row at a time.
String Functions
UPPER
SELECT UPPER(StudentName)
FROM Student;

LOWER
SELECT LOWER(StudentName)
FROM Student;

LENGTH
SELECT StudentName,
LENGTH(StudentName)
FROM Student;
CONCAT
SELECT CONCAT(StudentName,' - ',City)
FROM Student;
Output
Akash - Bangalore
Rahul - Delhi

SUBSTRING
SELECT SUBSTRING(StudentName,1,3)
FROM Student;
Output
Aka
Rah
Pri

REPLACE
SELECT REPLACE(StudentName,'a','@')
FROM Student;

TRIM
SELECT TRIM(' SQL ');

Numeric Functions
ROUND
SELECT ROUND(98.567,2);
Output
98.57

CEIL
SELECT CEIL(98.2);
Output
99

FLOOR
SELECT FLOOR(98.9);
Output
98

ABS
SELECT ABS(-45);
Output
45

MOD
SELECT MOD(20,3);
Output
2

Date Functions
Current date
SELECT CURDATE();
Current time
SELECT CURTIME();
Current timestamp
SELECT NOW();
Year
SELECT YEAR(CURDATE());
Month
SELECT MONTH(CURDATE());

CASE Statement
Acts like an IF-ELSE.
Example
SELECT StudentName,
CASE
WHEN TotalMarks>=90 THEN 'Outstanding'
WHEN TotalMarks>=80 THEN 'Excellent'
WHEN TotalMarks>=70 THEN 'Good'
WHEN TotalMarks>=60 THEN 'Average'
ELSE 'Needs Improvement'
END AS Performance
FROM Student
JOIN Marks
ON [Link]=[Link];
Output

Stude Performan
nt ce

Akash Excellent

Outstandin
Neha
g

UNION
Combines two result sets.
Duplicate rows removed.
SELECT City
FROM Student

UNION

SELECT City
FROM Employee;

UNION ALL
Keeps duplicates.
SELECT City
FROM Student

UNION ALL

SELECT City
FROM Employee;

Views
A View is a virtual table.
Create
CREATE VIEW StudentDetails
AS
SELECT StudentName,
DepartmentID,
City
FROM Student;
Display
SELECT *
FROM StudentDetails;
Delete
DROP VIEW StudentDetails;

Index
Improves searching speed.
Create
CREATE INDEX idx_studentname
ON Student(StudentName);
Delete
DROP INDEX idx_studentname
ON Student;

Constraints Review
PRIMARY KEY

FOREIGN KEY

NOT NULL

UNIQUE

CHECK

DEFAULT
Example
CREATE TABLE Example
(
ID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Email VARCHAR(50) UNIQUE,
Age INT CHECK(Age>=18),
City VARCHAR(30) DEFAULT 'Bangalore'
);

TCL (Transaction Control Language)

Used to manage transactions.

COMMIT
Save changes permanently.
UPDATE Fees
SET PaidAmount=100000
WHERE StudentID=101;

COMMIT;
ROLLBACK
Undo changes before commit.
UPDATE Fees
SET PaidAmount=0
WHERE StudentID=101;

ROLLBACK;

SAVEPOINT
Create a rollback point.
SAVEPOINT BeforeUpdate;
Example
UPDATE Employee
SET Salary=70000
WHERE EmployeeID=301;

SAVEPOINT S1;

UPDATE Employee
SET Salary=80000
WHERE EmployeeID=302;

ROLLBACK TO S1;

DCL (Data Control Language)

GRANT
GRANT SELECT
ON Student
TO User1;

REVOKE
REVOKE SELECT
ON Student
FROM User1;

Stored Procedure
Reusable SQL block.
Create
DELIMITER //

CREATE PROCEDURE GetStudents()

BEGIN

SELECT *
FROM Student;

END //

DELIMITER ;
Execute
CALL GetStudents();

Procedure with Parameter

DELIMITER //

CREATE PROCEDURE GetDepartmentStudents(IN dept INT)

BEGIN

SELECT *
FROM Student
WHERE DepartmentID=dept;
END //

DELIMITER ;
Execute
CALL GetDepartmentStudents(5);

Trigger
Automatically executes after an event.
Suppose we maintain a salary log.
Log Table
CREATE TABLE SalaryLog
(
EmployeeID INT,
OldSalary DECIMAL(10,2),
NewSalary DECIMAL(10,2)
);
Trigger
DELIMITER //

CREATE TRIGGER SalaryTrigger

AFTER UPDATE

ON Employee

FOR EACH ROW

BEGIN

INSERT INTO SalaryLog

VALUES

(
[Link],
[Link],
[Link]
);

END //

DELIMITER ;

Interview Questions
Highest salary
SELECT MAX(Salary)
FROM Teacher;

Students above average age

SELECT *
FROM Student
WHERE Age>
(
SELECT AVG(Age)
FROM Student
);

Department with maximum students

SELECT DepartmentID,
COUNT(*)
FROM Student
GROUP BY DepartmentID
ORDER BY COUNT(*) DESC
LIMIT 1;

Student with highest marks

SELECT StudentName,
TotalMarks
FROM Student
JOIN Marks
ON [Link]=[Link]
ORDER BY TotalMarks DESC
LIMIT 1;

Students whose fees are pending

SELECT StudentName,
PendingAmount
FROM Student
JOIN Fees
ON [Link]=[Link]
WHERE PendingAmount>0;

SQL Commands Summary

Catego
Commands
ry

CREATE, ALTER, DROP, TRUNCATE,

DDL
RENAME

DML INSERT, UPDATE, DELETE

DQL SELECT

TCL COMMIT, ROLLBACK, SAVEPOINT

DCL GRANT, REVOKE

Single Row Functions (SRF)

Function Example

UPPER UPPER(Name)
Function Example

LOWER LOWER(Name)

LENGTH LENGTH(Name)

CONCAT CONCAT(A,B)

SUBSTRIN SUBSTRING(Name,1
G ,3)

REPLACE(Name,'a','
REPLACE
@')

ROUND ROUND(Number,2)

CEIL CEIL(Number)

FLOOR FLOOR(Number)

ABS ABS(Number)

Multiple Row Functions (MRF)

Functio
Purpose
n

Count
COUNT
rows

SUM Total

AVG Average

MIN Smallest

MAX Largest

Complete SQL Learning Roadmap

SQL
│
├── Database
├── Tables
├── Datatypes
├── Constraints
├── DDL
├── DML
├── DQL
├── WHERE
├── ORDER BY
├── LIKE
├── BETWEEN
├── IN
├── Aggregate Functions
├── GROUP BY
├── HAVING
├── Joins
├── Subqueries
├── CASE
├── UNION
├── Views
├── Indexes
├── Stored Procedures
├── Functions
├── Triggers
├── TCL
└── DCL
🎯 What's Next?
For placement preparation, I recommend continuing with these advanced topics:
1. Window Functions
o ROW_NUMBER()

o RANK()

o DENSE_RANK()

o LEAD()

o LAG()

o NTILE()

2. Common Table Expressions (CTEs)

o Simple CTEs

o Recursive CTEs
 3. Advanced SQL Interview Questions
o Top N per group

o Second highest salary

o Duplicate records

o Pivot/Unpivot

o Running totals

o Gap and island problems

These topics are frequently asked in companies like TCS, Infosys, Accenture,
Capgemini, Cognizant, Wipro, Deloitte, Amazon, Microsoft, and Google.