1.

DDL, DML, TCL, DCL, and DQL Statements in SQL with Examples
SQL is divided into different categories based on their functionality:
a) Data Definition Language (DDL)
DDL statements are used to define, modify, and delete database structures.
Command Description Example
Creates a new database object (table, CREATE TABLE Student (ID INT, Name
CREATE
view, index) VARCHAR(50));
ALTER TABLE Student ADD COLUMN
ALTER Modifies an existing database object
Age INT;
DROP Deletes a database object DROP TABLE Student;
Removes all records from a table but
TRUNCATE TRUNCATE TABLE Student;
keeps the structure
b) Data Manipulation Language (DML)
DML statements deal with the manipulation of data in tables.
Command Description Example
INSERT INTO Student (ID, Name, Age) VALUES
INSERT Adds new records to a table
(1, 'John', 22);
UPDATE Modifies existing records UPDATE Student SET Age = 23 WHERE ID = 1;
Removes specific records from
DELETE DELETE FROM Student WHERE ID = 1;
a table
c) Transaction Control Language (TCL)
TCL statements manage transactions.
Command Description Example
COMMIT Saves the transaction permanently COMMIT;
ROLLBACK Reverts changes since the last commit ROLLBACK;
SAVEPOINT Creates a point within a transaction SAVEPOINT Save1;
d) Data Control Language (DCL)
DCL statements control access to the database.
Command Description Example
GRANT Gives privileges to users GRANT SELECT ON Student TO user1;
REVOKE Removes privileges from users REVOKE SELECT ON Student FROM user1;
e) Data Query Language (DQL)
DQL is used to retrieve data from the database.
Command Description Example
SELECT Retrieves data from one or more tables SELECT * FROM Student;

Dr.Ciyamala Kushbu S (Assistant Professor/CMRIT)

3. Six SQL Clauses with Examples
[1] WHERE: Filters records based on a specified condition in a query.
Example: SELECT * FROM Student WHERE Age > 20;

[2] GROUP BY: Groups records with the same values in specified columns.
Example: SELECT Department, COUNT(*) FROM Student GROUP BY
Department;

[3] HAVING: Filters grouped records based on aggregate conditions.

Example: SELECT Department, COUNT(*) FROM Student GROUP BY
Department HAVING COUNT(*) > 5;

[4] ORDER BY: Sorts query results in ascending (ASC) or descending (DESC) order.
Example: SELECT * FROM Student ORDER BY Name ASC;

[5] LIMIT: Restricts the number of rows returned in a query result.

Example: SELECT * FROM Student LIMIT 5;

[6] JOIN: Combines records from multiple tables based on a related column.
Example: SELECT Student.Name, Course.CourseName FROM Student JOIN
Course ON Student.CourseID = Course.CourseID;

4. Database Triggers in SQL

A trigger is a set of SQL statements that automatically execute when a specific event (INSERT,
UPDATE, DELETE) occurs in a database. Triggers help maintain the integrity of the database
by enforcing business rules and validations.

Syntax
CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
-- Trigger logic here
END;

[Write example given in lab manual]

Advantages of Triggers

1. Automates Database Tasks – Executes automatically based on conditions.

2. Ensures Data Integrity – Prevents invalid transactions.
3. Maintains Audit Logs – Tracks changes in a database table.
4. Reduces Application Code Complexity – Logic is stored inside the database.

Dr.Ciyamala Kushbu S (Assistant Professor/CMRIT)

6. Constraints of SQl:

1. PRIMARY KEY Constraint

A PRIMARY KEY uniquely identifies each row in a table.

It does not allow NULL values and must be unique for all records.
A table can have only one PRIMARY KEY, which may consist of multiple columns.

Using CREATE TABLE

CREATE TABLE Student (

ID INT CONSTRAINT PK_Student PRIMARY KEY,
Name VARCHAR(50)
);

Using ALTER TABLE

ALTER TABLE Student ADD CONSTRAINT PK_Student PRIMARY KEY (ID);

2. NOT NULL Constraint

A NOT NULL constraint ensures that a column cannot have NULL values.
It is used when a column must always have a value.
This helps maintain data integrity by preventing missing information.

Using CREATE TABLE

CREATE TABLE Student (

ID INT,
Name VARCHAR(50) CONSTRAINT NN_Student NOT NULL
);

Using ALTER TABLE

ALTER TABLE Student ALTER COLUMN Name SET NOT NULL;

3. UNIQUE Constraint

A UNIQUE constraint ensures that all values in a column are distinct.

Unlike PRIMARY KEY, multiple UNIQUE constraints can exist in a table.
It allows NULL values but prevents duplicate non-null values.

Using CREATE TABLE

CREATE TABLE Student (

ID INT,

Dr.Ciyamala Kushbu S (Assistant Professor/CMRIT)

 Email VARCHAR(50) CONSTRAINT UQ_Student UNIQUE
);

Using ALTER TABLE

ALTER TABLE Student ADD CONSTRAINT UQ_Student UNIQUE (Email);

4. CHECK Constraint

A CHECK constraint ensures that column values satisfy a specified condition.

It is used to enforce business rules on data.
For example, it can restrict an age column to only allow values greater than 18.

Using CREATE TABLE

CREATE TABLE Student (

ID INT,
Age INT CONSTRAINT CK_Age CHECK (Age >= 18)
);

Using ALTER TABLE

ALTER TABLE Student ADD CONSTRAINT CK_Age CHECK (Age >= 18);

5. FOREIGN KEY Constraint

A FOREIGN KEY establishes a relationship between two tables. It ensures referential integrity
by linking a column to a PRIMARY KEY in another table. If a referenced value is deleted, the
database may restrict or cascade the change.

Using CREATE TABLE

CREATE TABLE Student (

ID INT,
DeptID INT CONSTRAINT FK_Dept REFERENCES Department(ID)
);

Using ALTER TABLE

ALTER TABLE Student ADD CONSTRAINT FK_Dept FOREIGN KEY (DeptID)

REFERENCES Department(ID);

6. DEFAULT Constraint

Dr.Ciyamala Kushbu S (Assistant Professor/CMRIT)

A DEFAULT constraint assigns a predefined value to a column if no value is provided. This
helps avoid NULL values and ensures a consistent default setting. For example, a country
column can default to 'India' if not specified.

Using CREATE TABLE

CREATE TABLE Student (

ID INT,
Country VARCHAR(20) CONSTRAINT DF_Country DEFAULT 'India');

Using ALTER TABLE

ALTER TABLE Student ADD CONSTRAINT DF_Country DEFAULT 'India' FOR Country;

7.Assertions:

1. Assertions in SQL are used to enforce complex constraints on the database.

2. They ensure that a specified condition holds true for the entire database, beyond
individual table constraints.
3. Assertions use the CREATE ASSERTION statement and are checked automatically
when data is modified.

Example:

Ensure that all students are at least 18 years old in the Student table.

CREATE ASSERTION Age_Check

CHECK (NOT EXISTS (SELECT * FROM Student WHERE Age < 18));

 This ensures that no student has an age below 18.

 If an INSERT or UPDATE violates this rule, the database rejects the operation.

Example with EXISTS and NOT EXISTS in Assertions

1. Using EXISTS

Scenario: Ensure every department has at least one student.

CREATE ASSERTION Dept_Exists

CHECK (EXISTS (SELECT * FROM Student WHERE DeptID IS NOT NULL));

 This prevents the deletion of all students from any department.

2. Using NOT EXISTS

Scenario: Ensure no student has duplicate email IDs.

Dr.Ciyamala Kushbu S (Assistant Professor/CMRIT)

CREATE ASSERTION Unique_Emails
CHECK (NOT EXISTS (SELECT Email FROM Student GROUP BY Email HAVING
COUNT(Email) > 1));

 This prevents duplicate emails from being inserted into the Student table.

Key Points About Assertions

✔They enforce complex constraints at the database level.

✔Checked automatically when data is modified.

8.Aggregate Functions:

Definition:

1. Aggregate functions perform operations on multiple rows and return a single

summarized value.
2. They are used for calculations such as sum, average, count, min, and max in large
datasets.
3. These functions help in data analysis, reporting, and statistical calculations.

Common Aggregate Functions with Examples

Function Description Example

SELECT SUM(Salary) FROM
SUM() Returns the total sum of a numeric column
Employee;
Returns the average value of a numeric
AVG() SELECT AVG(Age) FROM Student;
column
Returns the number of rows in a table or
COUNT() SELECT COUNT(*) FROM Orders;
group
SELECT MAX(Salary) FROM
MAX() Returns the highest value in a column
Employee;
MIN() Returns the lowest value in a column SELECT MIN(Price) FROM Product;

Example Table: Employee

EmpID Name Department Salary Age

101 Alice HR 50000 28
102 Bob IT 70000 35
103 Charlie HR 55000 30

Dr.Ciyamala Kushbu S (Assistant Professor/CMRIT)

EmpID Name Department Salary Age
104 David IT 80000 40
105 Eve Finance 60000 32

Queries Using Aggregate Functions

1. Calculate the Total Salary of All Employees

SELECT SUM(Salary) AS Total_Salary FROM Employee;

Output:

Total_Salary
315000

2. Find the Average Age of Employees

SELECT AVG(Age) AS Average_Age FROM Employee;

Output:

Average_Age
33

3. Count the Number of Employees in Each Department

SELECT Department, COUNT(*) AS Employee_Count

FROM Employee
GROUP BY Department;

Output:

Department Employee_Count
HR 2
IT 2
Finance 1

4. Find the Highest and Lowest Salary

SELECT MAX(Salary) AS Highest_Salary, MIN(Salary) AS Lowest_Salary FROM Employee;

Output:

Dr.Ciyamala Kushbu S (Assistant Professor/CMRIT)

Highest_Salary Lowest_Salary
80000 50000

Advantages of Aggregate Functions

✔Summarize Large Data: Helps in analyzing large datasets efficiently.

✔Easy Data Reporting: Used in dashboards and reports for quick insights.
✔Better Decision Making: Helps organizations make data-driven decisions.

9. Datatype in SQL:

Definition

1. Data types define the type of data a column can store in a database.
2. They ensure data integrity and optimize storage by restricting the format of values.
3. SQL provides different categories of data types, including numeric, string, date/time, and
others.

Categories of SQL Data Types

Category Data Type Description

Numeric INT, BIGINT Stores whole numbers.
DECIMAL, FLOAT Stores decimal values.
String (Text) CHAR(n), VARCHAR(n) Fixed and variable-length text.
TEXT Large text data.
Date/Time DATE, TIME Stores date and time values.
DATETIME, TIMESTAMP Stores date and time with higher precision.
Boolean BOOLEAN Stores TRUE or FALSE values.
Binary BLOB, VARBINARY Stores binary data like images and files.

Example Table Using Different Data Types

CREATE TABLE Employee (

EmpID INT PRIMARY KEY, -- Numeric Data Type
Name VARCHAR(50) NOT NULL, -- String Data Type
Salary DECIMAL(10,2), -- Numeric with Decimal
JoiningDate DATE, -- Date Data Type
IsActive BOOLEAN -- Boolean Data Type
);

Dr.Ciyamala Kushbu S (Assistant Professor/CMRIT)

Example Queries Using Data Types

1. Insert Data into Employee Table

INSERT INTO Employee (EmpID, Name, Salary, JoiningDate, IsActive)

VALUES (101, 'Alice', 75000.50, '2023-06-15', TRUE);

2. Retrieve Employees Who Joined After 2022

SELECT Name, JoiningDate FROM Employee WHERE JoiningDate > '2022-01-01';

Few SQL Queries to go through:

Query 0

Retrieve the birthdate and address of the employee(s) whose name is ‘John B Smith’

SELECT BDATE, ADDRESS

FROM EMPLOYEE

WHERE FNAME = ‘John’ AND MINIT = ‘B’ AND LNAME = ‘Smith’;

Query 1

Retrieve the name and address of all employees who work for the ‘Research’ department

SELECT FNAME, LNAME, ADDRESS

FROM EMPLOYEE, DEPARTMENT

WHERE DNAME = ‘Research’ AND DNUMBER = DNO;

Query 1A

Ambiguous attribute names

SELECT FNAME, EMPLOYEE.NAME, ADDRESS

FROM EMPLOYEE, DEPARTMENT

WHERE DEPARTMENT.NAME = ‘Research’ AND

Dr.Ciyamala Kushbu S (Assistant Professor/CMRIT)

DEPARTMENT.DNUMBER = EMPLOYEE.DNUMBER;

Query 1B

Aliasing

SELECT E.FNAME, E.NAME, E.ADDRESS

FROM EMPLOYEE E, DEPARTMENT D

WHERE D.NAME = ‘Research’ AND D.DNUMBER = E.DNUMBER;

Query 1C

Retrieve all the attribute values of EMPLOYEE tuples who work in department number 5

SELECT *

FROM EMPLOYEE

WHERE DNO = 5;

Query 1D

Retrieve all the attributes of an EMPLOYEE and the attributes of the DEPARTMENT he or she
works in for

every employee of the ‘Research’ department

SELECT *

FROM EMPLOYEE, DEPARTMENT

WHERE DNAME = ‘Research’ AND DNO = DNUMBER;

Query 2

For every project located in ‘Stafford’, list the project number, the controlling department
number

and the department manager’s last name, address and birthdate

Dr.Ciyamala Kushbu S (Assistant Professor/CMRIT)

SELECT PNUMBER, DNUM, LNAME, ADDRESS, BDATE

FROM PROJECT, DEPARTMENT, EMPLOYEE

WHERE DNUM = DNUMBER AND MGRSSN = SSN AND PLOCATION = ‘Stafford’;

Query 8

For each employee, retrieve the employee’s first and last name, and the first and last name of his
or her

immediate supervisor.

SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME

FROM EMPLOYEE AS E, EMPLOYEE AS S

WHERE E.SUPERSSN = S.SSN;

Query 8A

Reformulation of query 8 to retrieve the last name of each employee and his or her supervisor,
while

renaming the resulting attribute names as EMPLOYEE_NAME and SUPERVISOR_NAME

SELECT E.LNAME AS EMPLOYEE_NAME, S.LNAME AS SUPERVISOR_NAME

FROM EMPLOYEE AS E, EMPLOYEE AS S

WHERE E.SUPERSSN = S.SSN;

Query 9

Select all EMPLOYEE SSNs in the database

SELECT SSN

FROM EMPLOYEE;

Query 10

Select all combination of EMPLOYEE SSN and DEPARTMENT DNAME in the database

SELECT SSN, DNAME

Dr.Ciyamala Kushbu S (Assistant Professor/CMRIT)

FROM EMPLOYEE, DEPARTMENT;

Query 10A

Select the CROSS PRODUCT of the EMPLOYEE and DEPARTMENT relations

SELECT *

FROM EMPLOYEE, DEPARTMENT;

Query 11

Retrieve the salary of every employee

SELECT ALL SALARY

FROM EMPLOYEE;

Query 11A

Retrieve all distinct salary values

SELECT DISTINCT SALARY

FROM EMPLOYEE;

Query 12

Retrieve all employees whose address is in Houston, Texas

SELECT FNAME, LNAME

FROM EMPLOYEE

WHERE ADDRESS LIKE ‘%Houston,TX%’;

Query 12A

Find all employees who were born during the 1950s

SELECT FNAME, LNAME

FROM EMPLOYEE

WHERE BDATE LIKE ‘_ _ _ _ _ _ _ 70’;

Dr.Ciyamala Kushbu S (Assistant Professor/CMRIT)

Query 13

Show the resulting salaries if every employee working on the ‘ProductX’ project is given a 10
percent raise

SELECT FNAME, LNAME, 1.1*SALARY

FROM EMPLOYEE, WORKS_ON, PROJECT

WHERE SSN = ESSN AND PNO = PNUMBER AND PNAME = ‘ProductX’;

Query 14

Retrieve all employees in department 5 whose salary is between £30,000 and £40,000

SELECT *

FROM EMPLOYEE

WHERE (SALARY BETWEEN 30000 AND 40000) AND DNO = 5;

Query 15

Retrieve a list of employees and the projects they are working on, ordered by department and,
within

each department, ordered alphabetically by last name, first name

SELECT DNAME, LNAME, FNAME, PNAME

FROM DEPARTMENT, EMPLOYEE,WORKS_ON, PROJECT

WHERE DNUMBER = DNO AND SSN = ESSN AND PNO = PNUMBER

ORDER BY DNAME DESC, LNAME ASC, FNAME ASC;

Dr.Ciyamala Kushbu S (Assistant Professor/CMRIT)