DBMS Unit II – SQL and PL/SQL Notes
Unit II Topics: SQL: DDL, DML, Select Queries, Functions, Aggregate Functions, Views, Indexes, Group By and
Having Clause, Join Queries, Set Operations, Nested Queries, DCL, TCL. PL/SQL: Control Statements, Cursor,
Stored Procedure, Function, Trigger.
1. SQL Overview
SQL (Structured Query Language) is used to communicate with relational databases. It is used for creating tables,
inserting records, retrieving data, updating data, and managing security.
2. DDL Commands
DDL stands for Data Definition Language.
Commands: CREATE, ALTER, DROP, TRUNCATE.
Example:
CREATE TABLE Student(StudentID INT PRIMARY KEY, Name VARCHAR(50), Course VARCHAR(50));
3. DML Commands
DML stands for Data Manipulation Language.
Commands: INSERT, UPDATE, DELETE.
Examples:
INSERT INTO Student VALUES(1,'Rahul','Computer');
UPDATE Student SET Course='IT' WHERE StudentID=1;
DELETE FROM Student WHERE StudentID=1;
4. SELECT Queries
SELECT command is used to retrieve data from tables.
Example:
SELECT * FROM Student;
SELECT Name, Course FROM Student WHERE Course='Computer';
5. String, Date and Numerical Functions
String Functions: UPPER(), LOWER(), LENGTH().
Date Functions: SYSDATE, ADD_MONTHS().
Numerical Functions: ROUND(), MOD(), ABS().
6. Aggregate Functions
Aggregate functions perform calculations on multiple rows.
Functions: COUNT(), SUM(), AVG(), MAX(), MIN().
Example: SELECT AVG(Marks) FROM Student;
7. Views and Indexes
View: Virtual table created from query result.
CREATE VIEW CS_Students AS SELECT * FROM Student WHERE Course='Computer';
Index: Improves query performance.
CREATE INDEX idx_name ON Student(Name);
8. GROUP BY and HAVING Clause
GROUP BY groups rows with same values.
HAVING filters grouped data.
Example:
SELECT Course, COUNT(*) FROM Student GROUP BY Course HAVING COUNT(*) > 2;
9. Join Queries
Joins combine records from multiple tables.
Types: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN.
Example:
SELECT [Link], [Link] FROM Student INNER JOIN Course ON [Link] =
[Link];
10. Set Operations
Set operations combine query results.
Operations: UNION, UNION ALL, INTERSECT, MINUS.
11. Nested Queries
Nested query means a query inside another query.
Example:
SELECT Name FROM Student WHERE Marks > (SELECT AVG(Marks) FROM Student);
12. DCL and TCL
DCL: GRANT, REVOKE.
TCL: COMMIT, ROLLBACK, SAVEPOINT.
13. PL/SQL Overview
PL/SQL is Oracle’s procedural extension of SQL. It supports variables, loops, conditions, procedures, functions,
and triggers.
14. Control Statements
PL/SQL control statements include IF-ELSE, LOOP, WHILE LOOP, and FOR LOOP.
Example:
IF marks > 40 THEN DBMS_OUTPUT.PUT_LINE('Pass'); END IF;
15. Cursor
Cursor is used to process query results row by row.
Types: Implicit Cursor and Explicit Cursor.
16. Stored Procedure and Function
Stored Procedure is a saved PL/SQL block.
Function returns a value.
Example Procedure:
CREATE OR REPLACE PROCEDURE display_msg IS BEGIN DBMS_OUTPUT.PUT_LINE('Hello'); END;
17. Trigger
Trigger automatically executes when INSERT, UPDATE, or DELETE event occurs.
Example:
CREATE TRIGGER trg BEFORE INSERT ON Student FOR EACH ROW BEGIN NULL; END;
18. Case Study – Student Course Management System
The system manages Students, Courses, Faculty, and Enrollments.
Features:
• Store student records.
• Manage course details.
• Assign faculty members.
• Retrieve reports using SQL queries.
• Maintain security using DCL.
• Use triggers and procedures for automation.