Course Code: CP25C02
Course Title: Advanced DatabaseTechnologies
Assignment – 2
Titles:
1. SQL Join Queries
2. DML and DTL Commands
By:
Name: P. Gokulesh
Register No: 922025405003
Class: M.E – Computer Science
and Engineering
Semester: II
Subject: Advanced Database
Technologies
1. SQL Join Queries
SQL Joins are used to combine data from two or more tables based on a related
column between them. They help retrieve meaningful information spread across
multiple tables.
Types of Joins
Type Description Example
INNER JOIN Returns records that SELECT s.Student_ID, s.Student_Name,
have matching values in [Link] FROM Student s INNER JOIN
both tables. Enrollment e ON s.Student_ID =
e.Student_ID INNER JOIN Course c ON
[Link] = [Link];
LEFT JOIN Returns all records from SELECT s.Student_Name, [Link] FROM
(LEFT the left table and Student s LEFT JOIN Enrollment e ON
OUTER matched records from s.Student_ID = e.Student_ID LEFT JOIN
JOIN) the right table. Course c ON [Link] = [Link];
RIGHT JOIN Returns all records from SELECT s.Student_Name, [Link] FROM
(RIGHT the right table and Student s RIGHT JOIN Enrollment e ON
OUTER matched ones from the s.Student_ID = e.Student_ID RIGHT JOIN
JOIN) left. Course c ON [Link] = [Link];
FULL JOIN Returns all records when SELECT s.Student_Name, [Link] FROM
(FULL there is a match in either Student s FULL OUTER JOIN Enrollment e
OUTER left or right table. ON s.Student_ID = e.Student_ID FULL
JOIN) OUTER JOIN Course c ON [Link] =
[Link];
CROSS Returns the Cartesian SELECT s.Student_Name, [Link] FROM
JOIN product of both tables. Student s CROSS JOIN Course c;
SELF JOIN Joins a table to itself. SELECT A.Student_Name AS Student1,
B.Student_Name AS Student2 FROM Student
A, Student B WHERE A.Student_ID <>
B.Student_ID;
Example Dataset:
Student Table:
Student_ID Student_Name
S01 Priya
S02 Gokul
S03 Sneha
Enrollment Table:
Student_ID Course
S01 DBMS
S02 Java
Course Table:
Course Instructor
DBMS John
Java Ravi
Python Divya
Sample Query Outputs
Example 1: INNER JOIN
SELECT s.Student_Name, [Link], [Link]
FROM Student s
INNER JOIN Enrollment e ON s.Student_ID = e.Student_ID
INNER JOIN Course c ON [Link] = [Link];
Result:
Student_Name Course Instructor
Priya DBMS John
Gokul Java Ravi
Example 2: LEFT JOIN
SELECT s.Student_Name, [Link]
FROM Student s
LEFT JOIN Enrollment e ON s.Student_ID = e.Student_ID
LEFT JOIN Course c ON [Link] = [Link];
Result:
Student_Name Course
Priya DBMS
Gokul Java
Sneha NULL
Example 3: RIGHT JOIN (RIGHT OUTER JOIN)
SELECT s.Student_Name, [Link]
FROM Student s
RIGHT JOIN Enrollment e
ON s.Student_ID = e.Student_ID;
Result:
Student_Name Course
Harini Python
Kavin Java
NULL C++
Example 4: FULL JOIN (FULL OUTER JOIN)
SELECT s.Student_Name, [Link]
FROM Student s
FULL OUTER JOIN Enrollment e
ON s.Student_ID = e.Student_ID;
Result:
Student_Name Course
Rahul NULL
Harini Python
Kavin Java
NULL C++
Example 5: CROSS JOIN
SELECT s.Student_Name, [Link]
FROM Student s
CROSS JOIN Enrollment e;
Result:
Student_Name Course
Rahul Python
Rahul Java
Rahul C++
Harini Python
Harini Java
Harini C++
Kavin Python
Kavin Java
Kavin C++
Example 6: SELF JOIN
SELECT e1.Emp_Name AS Employee, e2.Emp_Name AS Manager
FROM Employee e1
LEFT JOIN Employee e2
ON e1.Manager_ID = e2.Emp_ID;
Result:
Employee Manager
Arjun NULL
Meena Arjun
Raj Arjun
2. DML and DTL Commands
2.1 DML (Data Manipulation Language)
These commands deal with the manipulation of data stored in tables.
Common DML commands are: INSERT, UPDATE, DELETE, and SELECT.
Command Description Example
INSERT Adds new records INSERT INTO Student VALUES ('S04',
to a table. 'Vikram');
UPDATE Modifies existing UPDATE Student SET Student_Name =
records. 'Priyanka' WHERE Student_ID = 'S01';
DELETE Removes specific DELETE FROM Student WHERE
records. Student_ID = 'S03';
SELECT Retrieves data SELECT * FROM Student;
from tables.
Sample Table: Student
Student_ID Student_Name
S01 Priya
S02 Kavin
S03 Sneha
Example 1: INSERT
INSERT INTO Student VALUES ('S04', 'Vikram');
Result:
Student_ID Student_Name
S01 Priya
S02 Kavin
S03 Sneha
S04 Vikram
Example 2: UPDATE
UPDATE Student
SET Student_Name = 'Priyanka'
WHERE Student_ID = 'S01';
Result:
Student_ID Student_Name
S01 Priyanka
S02 Kavin
S03 Sneha
S04 Vikram
Example 3: DELETE
DELETE FROM Student
WHERE Student_ID = 'S03';
Result:
Student_ID Student_Name
S01 Priyanka
S02 Kavin
S04 Vikram
Example 4: SELECT
SELECT * FROM Student;
Result:
Student_ID Student_Name
S01 Priyanka
S02 Kavin
S04 Vikram
2.2 DTL (Data Transaction Language)
DTL (also called Transaction Control Language – TCL) is used to manage
transactions in the database and ensure data consistency.
Command Description Example
COMMIT Saves all the changes permanently. COMMIT;
ROLLBACK Reverses the changes made before the ROLLBACK;
last commit.
SAVEPOINT Sets a point within a transaction to which SAVEPOINT
you can rollback. Save1;
Sample Table: Student
Student_ID Student_Name
S01 Priyanka
S02 Kavin
S04 Vikram
S05 Harini
Example 1: COMMIT
INSERT INTO Student VALUES ('S06', 'Dinesh');
COMMIT;
Result:
Student_ID Student_Name
S01 Priyanka
S02 Kavin
S04 Vikram
S05 Harini
S06 Dinesh
Changes are permanently saved in the database.
Example 2: SAVEPOINT
SAVEPOINT sp1;
INSERT INTO Student VALUES ('S07', 'Lakshmi');
INSERT INTO Student VALUES ('S08', 'Vimal');
SAVEPOINT sp2;
DELETE FROM Student WHERE Student_ID = 'S05';
At this point, the table looks like this:
Student_ID Student_Name
S01 Priyanka
S02 Kavin
S04 Vikram
S06 Dinesh
S07 Lakshmi
S08 Vimal
Example 3: ROLLBACK TO SAVEPOINT
ROLLBACK TO sp2;
This will undo the delete operation done after sp2, restoring all records up to
that point.
Result after rollback:
Student_ID Student_Name
S01 Priyanka
S02 Kavin
S04 Vikram
S05 Harini
S06 Dinesh
S07 Lakshmi
S08 Vimal
Example 4: Final COMMIT
COMMIT;
All changes (insertions and rollbacks) are now permanently saved in the
database.
3. Conclusion
SQL joins and data manipulation commands are essential for efficient database
management.
• Joins help in combining data from multiple tables to retrieve meaningful
results.
• DML allows users to insert, update, and delete records.
• DTL/TCL ensures that all database operations maintain integrity and can
be committed or rolled back safely.