SQL (Structured Query Language)
SQL is a standard language used to create, manipulate, and manage relational databases.
1. Concept of DDL and DML
1. DDL (Data Definition Language) – Used to define or modify database structures
o Commands: CREATE, ALTER, DROP
o Example:
o CREATE TABLE Student (
o StudentID INT PRIMARY KEY,
o Name VARCHAR(50),
o Age INT
o );
2. DML (Data Manipulation Language) – Used to insert, update, delete, and query
data
o Commands: INSERT, UPDATE, DELETE, SELECT
o Example:
o INSERT INTO Student VALUES (1, 'Rahul', 20);
o SELECT * FROM Student;
2. Relational Databases and Tables
● Database: Collection of related tables
● Table: Collection of rows (tuples) and columns (attributes)
Example Table: Student
StudentID Name Age
1 Rahul 20
2 Priya 21
3. Set Operations
1. UNION: Combines results of two queries (removes duplicates)
2. SELECT Name FROM StudentA
3. UNION
4. SELECT Name FROM StudentB;
5. INTERSECT: Returns common rows
6. SELECT Name FROM StudentA
7. INTERSECT
8. SELECT Name FROM StudentB;
9. EXCEPT / MINUS: Returns rows in first query but not in second
10.SELECT Name FROM StudentA
11.EXCEPT
12.SELECT Name FROM StudentB;
4. Aggregate Functions
Used to perform calculations on multiple rows:
● COUNT(), SUM(), AVG(), MIN(), MAX()
● Example:
SELECT COUNT(*) FROM Student;
SELECT AVG(Age) FROM Student;
5. Null Values
● NULL represents missing or unknown data
● Example:
INSERT INTO Student VALUES (3, 'Amit', NULL);
6. Domain Constraints
● Restrict the type and range of data allowed in a column
● Example:
Age INT CHECK (Age > 0);
7. Referential Integrity Constraints
● Ensures foreign key values match primary key values in another table
● Example:
CREATE TABLE Enrollment (
StudentID INT,
CourseID INT,
FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
);
8. Assertions
● Constraints that ensure conditions are true for the database
● Example:
CREATE ASSERTION AgeCheck
CHECK (NOT EXISTS (SELECT * FROM Student WHERE Age < 0));
9. Views
● A virtual table created from one or more tables
● Example:
CREATE VIEW YoungStudents AS
SELECT Name, Age FROM Student WHERE Age < 25;
10. Nested Subqueries
● A query inside another query
● Example:
SELECT Name FROM Student
WHERE Age = (SELECT MAX(Age) FROM Student);
11. Stored Procedures
● A predefined set of SQL statements executed as a program
● Example:
CREATE PROCEDURE GetStudentCount()
BEGIN
SELECT COUNT(*) FROM Student;
END;
12. Cursors
● Used to iterate through rows of a query one by one
● Example:
DECLARE cur CURSOR FOR SELECT Name FROM Student;
13. Triggers
● Automatically executed actions on insert, update, or delete
● Example:
CREATE TRIGGER AgeCheckTrigger
BEFORE INSERT ON Student
FOR EACH ROW
BEGIN
IF [Link] < 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid Age';
END IF;
END;
✅ Summary Table:
Concept Purpose Example
DDL Define structure CREATE TABLE Student
DML Manipulate data INSERT, UPDATE
Set Operations Combine/query results UNION, INTERSECT
Aggregate Calculations COUNT(), SUM()
Null Missing data Age=NULL
Domain Restrict values CHECK (Age>0)
Referential Integrity Foreign key FOREIGN KEY (StudentID)
Views Virtual table CREATE VIEW
Subqueries Nested queries SELECT ... (SELECT ...)
Stored Procedure Predefined queries CREATE PROCEDURE
Cursors Row by row processing DECLARE CURSOR
Triggers Auto actions CREATE TRIGGER