4/7/26, 11:57 AM Advanced SQL Practicals
4. Introduction to SQL Joins and Nested Queries.
Reference Tables used throughout: Employee (EmpNo, EName, Job, DeptNo, Sal) and
Department (DeptNo, DeptName, Location)
Employee Department
EmpNo INT PK DeptNo INT PK
EName VARCHAR DeptName VARCHAR
⟵ DeptNo ⟶
Job VARCHAR Location VARCHAR
DeptNo INT FK
Sal INT
1. INNER JOIN — Returns only matching rows from both tables
inner_join.sql
SELECT [Link], [Link], [Link], [Link], [Link]
FROM Employee E
INNER JOIN Department D ON [Link] = [Link];
+-------+----------------+-------------------+-------------+----------+
| EmpNo | EName | Job | DeptName | Location |
+-------+----------------+-------------------+-------------+----------+
| 101 | Kartik D | Manager | Engineering | Pune |
| 102 | Jane Smith | Developer | Engineering | Pune |
| 103 | Shraddha | Sales Executive | Sales | Mumbai |
| 105 | Carey John | Sales Rep | Sales | Mumbai |
+-------+----------------+-------------------+-------------+----------+
4 rows in set (0.00 sec)
2. LEFT JOIN — All rows from left table, matched rows from right (NULL if no match)
left_join.sql
SELECT [Link], [Link], [Link]
FROM Employee E
LEFT JOIN Department D ON [Link] = [Link];
+-------+------------+-------------+
| EmpNo | EName | DeptName |
+-------+------------+-------------+
| 101 | Kartik D | Engineering |
| 102 | Jane Smith | Engineering |
| 103 | Shraddha | Sales |
| 104 | Adi | NULL | ← no dept assigned
+-------+------------+-------------+
3. RIGHT JOIN — All rows from right table, matched rows from left
[Link] 1/11
4/7/26, 11:57 AM Advanced SQL Practicals
right_join.sql
SELECT [Link], [Link], [Link]
FROM Employee E
RIGHT JOIN Department D ON [Link] = [Link];
+------------+-------------+----------+
| EName | DeptName | Location |
+------------+-------------+----------+
| Kartik D | Engineering | Pune |
| Jane Smith | Engineering | Pune |
| Shraddha | Sales | Mumbai |
| NULL | HR | Delhi | ← dept with no emp
+------------+-------------+----------+
4. SELF JOIN — Join a table with itself (find employee and their manager)
self_join.sql
SELECT [Link] AS Employee,
[Link] AS Manager
FROM Employee E
LEFT JOIN Employee M ON [Link] = [Link];
+------------+-----------+
| Employee | Manager |
+------------+-----------+
| Kartik D | NULL |
| Jane Smith | Kartik D |
| Shraddha | Kartik D |
| Adi | Jane Smith|
+------------+-----------+
5. Nested Query (Subquery) — Employees earning more than average salary
[Link]
SELECT EName, Sal
FROM Employee
WHERE Sal > (
SELECT AVG(Sal) FROM Employee
);
+-------------+-------+
| EName | Sal |
+-------------+-------+
| Kartik D | 60000 |
| Jane Smith | 50000 |
| Meera Joshi | 53000 |
+-------------+-------+
-- Average salary = 49571.43
[Link] 2/11
4/7/26, 11:57 AM Advanced SQL Practicals
6. Correlated Subquery — Employees whose salary equals max in their department
correlated_subquery.sql
SELECT EName, DeptNo, Sal
FROM Employee E1
WHERE Sal = (
SELECT MAX(Sal)
FROM Employee E2
WHERE [Link] = [Link]
);
+-------------+--------+-------+
| EName | DeptNo | Sal |
+-------------+--------+-------+
| Kartik D | 10 | 60000 |
| Shraddha | 20 | 45000 |
+-------------+--------+-------+
5. Introduction to SQL Views.
A VIEW is a virtual table based on a SELECT query. It stores the query definition —
not the data itself. Views simplify complex queries, enforce security, and present
tailored data to users.
1. CREATE VIEW — Simple view showing high-salary employees
create_view.sql
CREATE VIEW HighSalaryEmp AS
SELECT EmpNo, EName, Job, Sal
FROM Employee
WHERE Sal > 48000;
Query OK, 0 rows affected (0.02 sec)
2. SELECT from VIEW — Query it like a normal table
mysql> SELECT * FROM HighSalaryEmp;
+-------+-------------+-----------+-------+
| EmpNo | EName | Job | Sal |
+-------+-------------+-----------+-------+
| 101 | Kartik D | Manager | 60000 |
| 102 | Jane Smith | Developer | 50000 |
| 106 | Meera Joshi | Developer | 53000 |
[Link] 3/11
4/7/26, 11:57 AM Advanced SQL Practicals
+-------+-------------+-----------+-------+
3 rows in set (0.00 sec)
3. VIEW with JOIN — Department-wise employee summary view
join_view.sql
CREATE VIEW EmpDeptView AS
SELECT [Link], [Link], [Link],
[Link], [Link]
FROM Employee E
INNER JOIN Department D ON [Link] = [Link];
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT * FROM EmpDeptView WHERE Location = 'Pune';
+-------+-------------+-------+-------------+----------+
| EmpNo | EName | Sal | DeptName | Location |
+-------+-------------+-------+-------------+----------+
| 101 | Kartik D | 60000 | Engineering | Pune |
| 102 | Jane Smith | 50000 | Engineering | Pune |
| 106 | Meera Joshi | 53000 | Engineering | Pune |
+-------+-------------+-------+-------------+----------+
4. CREATE OR REPLACE VIEW — Update view definition
replace_view.sql
CREATE OR REPLACE VIEW HighSalaryEmp AS
SELECT EmpNo, EName, Job, Sal, Commission
FROM Employee
WHERE Sal > 48000;
Query OK, 0 rows affected (0.01 sec)
5. DROP VIEW — Remove a view
drop_view.sql
DROP VIEW IF EXISTS EmpDeptView;
Query OK, 0 rows affected (0.01 sec)
-- Verify: trying to select from dropped view
mysql> SELECT * FROM EmpDeptView;
ERROR 1146 (42S02): Table 'college_db.EmpDeptView' doesn't exist
Note: Views created without WITH CHECK OPTION allow INSERT/UPDATE to bypass the WHERE
filter condition. Use WITH CHECK OPTION to enforce view constraints on DML
operations.
[Link] 4/11
4/7/26, 11:57 AM Advanced SQL Practicals
6. Write PL/SQL Programs to Implement Stored Procedure, Functions, Triggers and
Cursor.
1. Stored Procedure — Calculate and display salary with bonus
stored_procedure.sql
DELIMITER //
CREATE PROCEDURE GetSalaryWithBonus(IN p_EmpNo INT)
BEGIN
DECLARE v_Name VARCHAR(30);
DECLARE v_Sal INT;
DECLARE v_Bonus INT;
SELECT EName, Sal INTO v_Name, v_Sal
FROM Employee WHERE EmpNo = p_EmpNo;
SET v_Bonus = v_Sal * 0.10;
SELECT v_Name AS Name,
v_Sal AS Salary,
v_Bonus AS Bonus,
v_Sal + v_Bonus AS TotalPay;
END//
DELIMITER ;
Query OK, 0 rows affected (0.03 sec)
-- Call the procedure
CALL GetSalaryWithBonus(102);
+------------+--------+-------+----------+
| Name | Salary | Bonus | TotalPay |
+------------+--------+-------+----------+
| Jane Smith | 50000 | 5000 | 55000 |
+------------+--------+-------+----------+
2. Function — Return grade based on salary
[Link]
DELIMITER //
CREATE FUNCTION GetGrade(p_Sal INT)
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
DECLARE v_Grade VARCHAR(10);
IF p_Sal >= 55000 THEN SET v_Grade = 'A';
ELSEIF p_Sal >= 45000 THEN SET v_Grade = 'B';
[Link] 5/11
4/7/26, 11:57 AM Advanced SQL Practicals
ELSEIF p_Sal >= 35000 THEN SET v_Grade = 'C';
ELSE SET v_Grade = 'D';
END IF;
RETURN v_Grade;
END//
DELIMITER ;
-- Use function in a SELECT
SELECT EName, Sal, GetGrade(Sal) AS Grade FROM Employee;
+----------------+-------+-------+
| EName | Sal | Grade |
+----------------+-------+-------+
| Kartik D | 60000 | A |
| Jane Smith | 50000 | B |
| Shraddha | 45000 | B |
| Adi | 48000 | B |
| Carey John | 42000 | C |
+----------------+-------+-------+
3. Trigger — Auto-log salary changes to AuditLog table
[Link]
-- Create audit table first
CREATE TABLE AuditLog (
LogID INT AUTO_INCREMENT PRIMARY KEY,
EmpNo INT,
OldSal INT,
NewSal INT,
ChangedAt DATETIME DEFAULT NOW()
);
DELIMITER //
CREATE TRIGGER AfterSalaryUpdate
AFTER UPDATE ON Employee
FOR EACH ROW
BEGIN
IF [Link] <> [Link] THEN
INSERT INTO AuditLog (EmpNo, OldSal, NewSal)
VALUES ([Link], [Link], [Link]);
END IF;
END//
DELIMITER ;
-- Fire the trigger
UPDATE Employee SET Sal = 55000 WHERE EmpNo = 102;
SELECT * FROM AuditLog;
+-------+-------+--------+--------+---------------------+
| LogID | EmpNo | OldSal | NewSal | ChangedAt |
+-------+-------+--------+--------+---------------------+
[Link] 6/11
4/7/26, 11:57 AM Advanced SQL Practicals
| 1 | 102 | 50000 | 55000 | 2024-11-10 09:22:31 |
+-------+-------+--------+--------+---------------------+
4. Cursor — Iterate over all employees and print salary details
[Link]
DELIMITER //
CREATE PROCEDURE DisplayAllSalaries()
BEGIN
DECLARE v_Done INT DEFAULT FALSE;
DECLARE v_Name VARCHAR(30);
DECLARE v_Sal INT;
DECLARE emp_cursor CURSOR FOR
SELECT EName, Sal FROM Employee ORDER BY Sal DESC;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET v_Done = TRUE;
OPEN emp_cursor;
read_loop: LOOP
FETCH emp_cursor INTO v_Name, v_Sal;
IF v_Done THEN LEAVE read_loop; END IF;
SELECT CONCAT('Employee: ', v_Name, ' | Salary: ', v_Sal) AS Info;
END LOOP;
CLOSE emp_cursor;
END//
DELIMITER ;
CALL DisplayAllSalaries();
+--------------------------------------+
| Info |
+--------------------------------------+
| Employee: Kartik D | Salary: 60000 |
| Employee: Meera Joshi | Salary: 53000 |
| Employee: Jane Smith | Salary: 50000 |
| Employee: Adi | Salary: 48000 |
| Employee: Shraddha | Salary: 45000 |
+--------------------------------------+
7. Normalise a Database to Eliminate Redundancy and Achieve Higher Normal Forms.
[Link] 7/11
4/7/26, 11:57 AM Advanced SQL Practicals
Normalization is the process of organizing a database to reduce redundancy and
improve data integrity. We progress through 1NF → 2NF → 3NF.
Unnormalized Form (UNF) — Raw data with repeating groups
UNNORMALIZED — Redundancy Present
StudentID SName Courses TeacherName TeacherPhone
DBMS, OS, Prof. Sharma, Prof. Rao, 9900, 9901,
S01 Priya
CN Prof. Singh 9902
S02 Rahul DBMS, OS Prof. Sharma, Prof. Rao 9900, 9901
First Normal Form (1NF) — Eliminate repeating groups; each cell holds one value
1NF — Atomic Values
StudentID SName CourseID CourseName TeacherName TeacherPhone
S01 Priya C1 DBMS Prof. Sharma 9900
S01 Priya C2 OS Prof. Rao 9901
S01 Priya C3 CN Prof. Singh 9902
S02 Rahul C1 DBMS Prof. Sharma 9900
S02 Rahul C2 OS Prof. Rao 9901
Problem: SName depends only on StudentID (partial dependency). CourseName,
TeacherName depend only on CourseID. → Violates 2NF.
Second Normal Form (2NF) — Remove partial dependencies; every non-key attribute fully
depends on the entire PK
2NF — No Partial Dependencies
Student Table Course Table Enrollment Table
StudentID (PK) SName CourseID StudentID CourseID
CourseName TeacherName TeacherPhone
(PK)
S01 Priya S01 C1
Prof.
S02 Rahul C1 DBMS 9900
SharmaS01 C2
C2 OS Prof. S01
Rao 9901C3
C3 CN Prof. S02
Singh 9902C1
S02 C2
Problem in Course table: TeacherPhone depends on TeacherName, not CourseID →
Transitive dependency. Violates 3NF.
Third Normal Form (3NF) — Remove transitive dependencies
[Link] 8/11
4/7/26, 11:57 AM Advanced SQL Practicals
3NF — No Transitive Dependencies ✓
Course Table (3NF) Teacher Table (3NF)
CourseID TeacherID
CourseName TeacherID TeacherName TeacherPhone
(PK) (PK)
C1 DBMS T1 Prof.
T1 9900
Sharma
C2 OS T2
T2 Prof. Rao 9901
C3 CN T3
T3 Prof. Singh 9902
Result: 4 clean tables — Student, Course, Teacher, Enrollment — all in 3NF. No
redundancy. No update anomalies.
8. Setting Up a NoSQL Database (MongoDB) and Performing CRUD Operations.
MongoDB is a document-oriented NoSQL database. Data is stored as JSON-like documents
(BSON). Collections ≈ Tables. Documents ≈ Rows. Fields ≈ Columns. No fixed schema
required.
1. Start MongoDB and create / select a database
mongosh
# Start MongoDB shell
$ mongosh
Current Mongosh Log ID: 653fc1a...
Connecting to: mongodb://[Link]:27017
Using MongoDB: 7.0.2
# Create / switch to database
test> use college_db
switched to db college_db
# Verify current db
college_db> db
college_db
2. CREATE — insertOne() and insertMany()
college_db — insert documents
// Insert a single document
college_db> [Link]({
[Link] 9/11
4/7/26, 11:57 AM Advanced SQL Practicals
rollNo: 1,
name: "Priya Sharma",
branch: "CSE",
marks: 88.5,
skills: ["Python", "SQL"]
})
{ acknowledged: true, insertedId: ObjectId('653fc2b...') }
// Insert multiple documents
college_db> [Link]([
{ rollNo:2, name:"Rahul Mehta", branch:"IT", marks:75.0 },
{ rollNo:3, name:"Anita Kulkarni", branch:"ECE", marks:91.0 },
{ rollNo:4, name:"Sanjay Patil", branch:"CSE", marks:60.25 }
])
{ acknowledged: true, insertedCount: 3 }
3. READ — find() with filters and projections
college_db — query documents
// Find all documents
college_db> [Link]()
[ { _id: ObjectId('...'), rollNo: 1, name: 'Priya Sharma', branch: 'CSE', marks: 88
{ _id: ObjectId('...'), rollNo: 2, name: 'Rahul Mehta', branch: 'IT', marks: 75
// Find with filter — marks > 80
college_db> [Link]({ marks: { $gt: 80 } })
[
{ rollNo: 1, name: 'Priya Sharma', branch: 'CSE', marks: 88.5 },
{ rollNo: 3, name: 'Anita Kulkarni', branch: 'ECE', marks: 91 }
]
// Projection — show only name and marks
college_db> [Link]({}, { _id:0, name:1, marks:1 })
[ { name: 'Priya Sharma', marks: 88.5 },
{ name: 'Rahul Mehta', marks: 75 },
{ name: 'Anita Kulkarni', marks: 91 },
{ name: 'Sanjay Patil', marks: 60.25 } ]
4. UPDATE — updateOne() and updateMany()
college_db — update documents
// Update single document — set new marks for rollNo 2
college_db> [Link](
{ rollNo: 2 },
{ $set: { marks: 82.0, grade: "B" } }
)
{ acknowledged: true, matchedCount: 1, modifiedCount: 1 }
[Link] 10/11
4/7/26, 11:57 AM Advanced SQL Practicals
// Update many — add status field for all CSE students
college_db> [Link](
{ branch: "CSE" },
{ $set: { status: "Active" } }
)
{ acknowledged: true, matchedCount: 2, modifiedCount: 2 }
// Increment marks by 5 for all ECE students
college_db> [Link](
{ branch: "ECE" },
{ $inc: { marks: 5 } }
)
{ acknowledged: true, matchedCount: 1, modifiedCount: 1 }
5. DELETE — deleteOne() and deleteMany()
college_db — delete documents
// Delete one document
college_db> [Link]({ rollNo: 4 })
{ acknowledged: true, deletedCount: 1 }
// Delete all documents where marks < 70
college_db> [Link]({ marks: { $lt: 70 } })
{ acknowledged: true, deletedCount: 0 }
// Verify final state
college_db> [Link]({}, { _id:0 })
[
{ rollNo: 1, name: 'Priya Sharma', branch: 'CSE', marks: 88.5, status: 'Active'
{ rollNo: 2, name: 'Rahul Mehta', branch: 'IT', marks: 82.0, grade: 'B'
{ rollNo: 3, name: 'Anita Kulkarni', branch: 'ECE', marks: 96.0
]
// Drop entire collection
college_db> [Link]()
true
[Link] 11/11