Kingston School of Management and Science
BCA 2ND yr 4TH Semester
NOTES OVER MODULE IV (SQL : Concept of DDL, DML. Basic Structure Relational databases
and tables, Set operations, Aggregate Functions, Null Values, Domain Constraints, Referential Integrity
Constraints, assertions, views, Nested Subqueries, Stored procedures,cursors and triggers.)
DATE: 05.06.2026
Paper Name: DBMS
Paper Code: BCAC401
SQL (Structured Query Language) is the standard language used to communicate with Relational Database
Management Systems (RDBMS) such as MySQL, Oracle, SQL Server, PostgreSQL, and SQLite.
SQL is used to:
Create databases and tables
Store data
Retrieve data
Update data
Delete data
Control access to data
1. SQL Categories
SQL commands are mainly divided into:
Category Full Form Purpose
DDL Data Definition Language Defines database structure
DML Data Manipulation Language Manipulates data
DQL Data Query Language Retrieves data
DCL Data Control Language Controls permissions
Category Full Form Purpose
TCL Transaction Control Language Manages transactions
2. DDL (Data Definition Language)
DDL commands define and modify database structures.
Common DDL Commands
CREATE
Used to create database objects.
CREATE TABLE Student
(
RollNo INT,
Name VARCHAR(50),
Age INT
);
ALTER
Used to modify an existing table.
ALTER TABLE Student
ADD Address VARCHAR(100);
DROP
Deletes a table permanently.
DROP TABLE Student;
TRUNCATE
Deletes all records but keeps table structure.
TRUNCATE TABLE Student;
RENAME
Changes table name.
RENAME TABLE Student TO Students;
3. DML (Data Manipulation Language)
DML commands manipulate records inside tables.
INSERT
Adds records.
INSERT INTO Student
VALUES (101,'Rahul',20);
INSERT INTO Student
VALUES (102,'Priya',21);
Table
RollNo Name Age
101 Rahul 20
102 Priya 21
UPDATE
Modifies existing data.
UPDATE Student
SET Age=22
WHERE RollNo=101;
DELETE
Removes records.
DELETE FROM Student
WHERE RollNo=102;
4. Basic Structure of Relational Databases
A relational database stores data in tables.
Components
Relation
A table.
Example:
Student Table
RollNo Name Age
101 Rahul 20
102 Priya 21
Tuple
A row in a table.
101 Rahul 20
is a tuple.
Attribute
A column.
RollNo
Name
Age
are attributes.
Degree
Number of columns.
Student table degree = 3
Cardinality
Number of rows.
Student table cardinality = 2
5. Relational Database and Tables
Example Database: College
Student
RollNo Name
101 Rahul
102 Priya
Course
CourseID CourseName
C101 DBMS
C102 Java
Enrollment
RollNo CourseID
101 C101
102 C102
Relations among tables are established through keys.
6. Set Operations in SQL
SQL supports set operations similar to mathematical sets.
UNION
Combines results and removes duplicates.
SELECT Name FROM Science
UNION
SELECT Name FROM Arts;
Result:
Rahul
Priya
Amit
UNION ALL
Keeps duplicates.
SELECT Name FROM Science
UNION ALL
SELECT Name FROM Arts;
INTERSECT
Returns common records.
SELECT Name FROM Science
INTERSECT
SELECT Name FROM Arts;
Result:
Rahul
EXCEPT (MINUS in Oracle)
Returns records in first table but not second.
SELECT Name FROM Science
EXCEPT
SELECT Name FROM Arts;
7. Aggregate Functions
Aggregate functions perform calculations on multiple rows.
Assume Employee Table
EmpID Name Salary
1 Amit 30000
2 Raj 40000
EmpID Name Salary
3 Neha 50000
COUNT()
Counts rows.
SELECT COUNT(*) FROM Employee;
Output:
SUM()
Calculates total.
SELECT SUM(Salary)
FROM Employee;
Output:
120000
AVG()
Average value.
SELECT AVG(Salary)
FROM Employee;
Output:
40000
MAX()
Highest value.
SELECT MAX(Salary)
FROM Employee;
Output:
50000
MIN()
Lowest value.
SELECT MIN(Salary)
FROM Employee;
Output:
30000
8. Null Values
NULL means unknown or unavailable data.
Example
RollNo Name Phone
101 Rahul 9876543210
102 Priya NULL
Here phone number is unknown.
Check NULL
SELECT *
FROM Student
WHERE Phone IS NULL;
Check NOT NULL
SELECT *
FROM Student
WHERE Phone IS NOT NULL;
9. Domain Constraints
A domain specifies valid values for an attribute.
Example:
Age must be between 18 and 60.
CREATE TABLE Employee
(
EmpID INT,
Age INT CHECK(Age BETWEEN 18 AND 60)
);
Valid:
INSERT INTO Employee VALUES(1,25);
Invalid:
INSERT INTO Employee VALUES(2,70);
Error generated.
10. Referential Integrity Constraints
Ensures consistency among related tables.
Parent Table
CREATE TABLE Department
(
DeptID INT PRIMARY KEY,
DeptName VARCHAR(50)
);
Child Table
CREATE TABLE Employee
(
EmpID INT PRIMARY KEY,
EmpName VARCHAR(50),
DeptID INT,
FOREIGN KEY(DeptID)
REFERENCES Department(DeptID)
);
Valid Insertion
INSERT INTO Department
VALUES(1,'CSE');
INSERT INTO Employee
VALUES(101,'Rahul',1);
Invalid Insertion
INSERT INTO Employee
VALUES(102,'Priya',5);
Error because DeptID 5 does not exist.
11. Assertions
Assertions are conditions that must always remain true in the database.
Example:
No employee salary should exceed ₹1,00,000.
CREATE ASSERTION Salary_Check
CHECK
(
NOT EXISTS
(
SELECT *
FROM Employee
WHERE Salary > 100000
)
);
Purpose
Maintains global constraints
Ensures business rules
12. Views
A View is a virtual table created from one or more tables.
Create View
CREATE VIEW Student_View AS
SELECT RollNo,Name
FROM Student;
Use View
SELECT *
FROM Student_View;
Advantages
Security
Simplifies complex queries
Data abstraction
13. Nested Subqueries
A query inside another query.
Example 1
Find employee earning maximum salary.
SELECT Name
FROM Employee
WHERE Salary=
(
SELECT MAX(Salary)
FROM Employee
);
Example 2
Find students enrolled in DBMS.
SELECT Name
FROM Student
WHERE RollNo IN
(
SELECT RollNo
FROM Enrollment
WHERE CourseID='C101'
);
14. Stored Procedures
A stored procedure is a precompiled collection of SQL statements stored in the database.
Example
DELIMITER //
CREATE PROCEDURE GetStudents()
BEGIN
SELECT * FROM Student;
END //
DELIMITER ;
Execute Procedure
CALL GetStudents();
Advantages
Faster execution
Reusability
Better security
Reduced network traffic
15. Cursors
A cursor processes records one by one.
Normally SQL processes records as a set.
Cursor processes:
Row 1
Row 2
Row 3
...
one at a time.
Cursor Steps
Declare
DECLARE student_cursor CURSOR FOR
SELECT Name FROM Student;
Open
OPEN student_cursor;
Fetch
FETCH student_cursor INTO student_name;
Close
CLOSE student_cursor;
Uses
Row-by-row processing
Payroll calculations
Report generation
16. Triggers
A Trigger is a stored program that executes automatically when a specific event occurs.
Events:
INSERT
UPDATE
DELETE
Example: Audit Trigger
CREATE TRIGGER Student_Insert
AFTER INSERT
ON Student
FOR EACH ROW
INSERT INTO Student_Log
VALUES
(
[Link],
[Link],
NOW()
);
What Happens?
When:
INSERT INTO Student
VALUES(103,'Amit',20);
Trigger automatically stores details in Student_Log table.
Types of Triggers
BEFORE Trigger
Runs before event.
BEFORE INSERT
AFTER Trigger
Runs after event.
AFTER INSERT
Difference Between Procedure and Trigger
Procedure Trigger
Called manually Executes automatically
Uses CALL statement Event driven
User controls execution DBMS controls execution
Can return values Usually does not return values
MAKAUT Exam Important Questions
Short Questions
1. What is SQL?
2. Define DDL and DML.
3. What is a View?
4. What is a Trigger?
5. What is a Cursor?
6. What is NULL value?
7. Define Referential Integrity.
8. What is a Subquery?
Long Questions
1. Explain DDL and DML commands with examples.
2. Discuss aggregate functions with suitable examples.
3. Explain referential integrity constraints with diagram and example.
4. What are views? Explain advantages and creation process.
5. Explain nested subqueries with examples.
6. Describe stored procedures and triggers with suitable SQL programs.
7. Explain cursor architecture and working mechanism.
Viva Questions
Difference between DELETE, TRUNCATE and DROP?
Difference between UNION and UNION ALL?
Difference between View and Table?
Difference between Procedure and Trigger?
What is Foreign Key?
What is NULL?
Why are aggregate functions used?