UNIT 2

DDL, DML, and SELECT Queries

1. DDL (Data Definition Language)
DDL is used to define and modify the structure of database objects such as tables, views, and
indexes.

Common DDL Commands

A) CREATE

Used to create a new table.

CREATE TABLE Student (

Roll_No INT PRIMARY KEY,
Name VARCHAR(50),
Age INT
);

B) ALTER

Used to modify an existing table.

ALTER TABLE Student

ADD Address VARCHAR(100);

C) DROP

Used to delete a table permanently.

DROP TABLE Student;

D) TRUNCATE

Removes all records from a table but keeps the table structure.

TRUNCATE TABLE Student;

2. DML (Data Manipulation Language)

DML is used to insert, update, and delete data in tables.

A) INSERT

Adds new records.

INSERT INTO Student
VALUES (101, 'Rahul', 20);

B) UPDATE

Modifies existing records.

UPDATE Student
SET Age = 21
WHERE Roll_No = 101;

C) DELETE

Removes records from a table.

DELETE FROM Student

WHERE Roll_No = 101;

3. SELECT Queries
The SELECT statement is used to retrieve data from one or more tables.

Basic Syntax
SELECT column_name
FROM table_name;

Examples

A) Display All Records

SELECT * FROM Student;

B) Display Specific Columns

SELECT Name, Age
FROM Student;

C) Using WHERE Clause

SELECT *
FROM Student
WHERE Age > 18;

D) Using ORDER BY
SELECT *
FROM Student
ORDER BY Name;
E) Using DISTINCT

Displays unique values.

SELECT DISTINCT Age

FROM Student;

F) Using AND Operator

SELECT *
FROM Student
WHERE Age > 18 AND Age < 25;

G) Using OR Operator
SELECT *
FROM Student
WHERE Age = 18 OR Age = 20;

H) Using LIKE
SELECT *
FROM Student
WHERE Name LIKE 'R%';

(Names starting with "R")

I) Using BETWEEN
SELECT *
FROM Student
WHERE Age BETWEEN 18 AND 25;

J) Using IN
SELECT *
FROM Student
WHERE Age IN (18, 20, 22);

Example Student Table

Roll_No Name Age
101 Rahul 20
102 Priya 19
103 Amit 21

Query
SELECT Name
FROM Student
WHERE Age > 19;

Output

Name
Rahul
Amit

Difference Between DDL and DML

DDL DML
Defines database structure Manipulates data
Affects schema Affects records
CREATE, ALTER, DROP, TRUNCATE INSERT, UPDATE, DELETE
Auto-commit in many DBMSs Requires COMMIT/ROLLBACK support

Exam Answer (5 Marks)

DDL (Data Definition Language)

DDL is used to define and modify database structures. Commands include CREATE,
ALTER, DROP, and TRUNCATE.

DML (Data Manipulation Language)

DML is used to manipulate data stored in tables. Commands include INSERT, UPDATE,
and DELETE.

SELECT Queries

The SELECT statement is used to retrieve data from tables.

Example:

SELECT * FROM Student;

It can be used with clauses such as WHERE, ORDER BY, DISTINCT, LIKE,
BETWEEN, and IN to filter and display data as required.
String, Date and Numerical Functions
SQL provides various built-in functions to manipulate strings, dates, and numeric values.

1. String Functions
String functions are used to perform operations on character data.

Function Description Example

UPPER() Converts text to uppercase UPPER('rahul')

LOWER() Converts text to lowercase LOWER('RAHUL')

LENGTH() Returns length of string LENGTH('Rahul')

CONCAT() Joins two or more strings CONCAT('Deepali',' Jundre')

SUBSTRING() Extracts part of a string SUBSTRING('Database',1,4)

TRIM() Removes leading and trailing spaces TRIM(' Hello ')

Examples

UPPER()
SELECT UPPER('rahul');

Output: RAHUL

LOWER()
SELECT LOWER('RAHUL');

Output: rahul

CONCAT()
SELECT CONCAT('Hello',' World');

Output: Hello World

LENGTH()
SELECT LENGTH('Database');
Output: 8

2. Date Functions
Date functions are used to work with date and time values.

Function Description

CURRENT_DATE Displays current date

CURRENT_TIME Displays current time

NOW() Displays current date and time

YEAR() Extracts year

MONTH() Extracts month

DAY() Extracts day

Examples

Current Date
SELECT CURRENT_DATE;

Output: 2026-06-07 (example)

Current Date and Time

SELECT NOW();

Extract Year
SELECT YEAR('2026-06-07');

Output: 2026

Extract Month
SELECT MONTH('2026-06-07');

Output: 6

Extract Day
SELECT DAY('2026-06-07');
Output: 7

3. Numerical Functions
Numerical functions are used to perform mathematical calculations.

Function Description Example

ABS() Absolute value ABS(-25)

ROUND() Rounds number ROUND(12.56)

CEIL() / CEILING() Rounds up CEIL(12.1)

FLOOR() Rounds down FLOOR(12.9)

MOD() Returns remainder MOD(10,3)

SQRT() Square root SQRT(25)

POWER() Raises number to a power POWER(2,3)

Examples

ABS()
SELECT ABS(-25);

Output: 25

ROUND()
SELECT ROUND(12.56);

Output: 13

CEIL()
SELECT CEIL(12.1);

Output: 13

FLOOR()
SELECT FLOOR(12.9);

Output: 12
MOD()
SELECT MOD(10,3);

Output: 1

SQRT()
SELECT SQRT(25);

Output: 5

POWER()
SELECT POWER(2,3);

Output: 8

Exam Answer (5 Marks)

String Functions

String functions manipulate character data. Common functions are:

• UPPER()
• LOWER()
• LENGTH()
• CONCAT()
• SUBSTRING()
• TRIM()

Date Functions

Date functions work with date and time values. Common functions are:

• CURRENT_DATE
• NOW()
• YEAR()
• MONTH()
• DAY()

Numerical Functions

Numerical functions perform mathematical operations. Common functions are:

• ABS()
• ROUND()
 • CEIL()
• FLOOR()
• MOD()
• SQRT()
• POWER()

These functions help in efficient data processing and manipulation in SQL databases.

Aggregate Functions, Views, and Indexes

1. Aggregate Functions
Aggregate Functions perform calculations on a group of values and return a single result.

They are commonly used with the SELECT statement and GROUP BY clause.

Types of Aggregate Functions

Function Purpose
COUNT() Counts rows
SUM() Calculates total
AVG() Calculates average
MAX() Finds highest value
MIN() Finds lowest value

Example Table: Student

Roll_No Name Marks
101 Rahul 80
102 Priya 90
103 Amit 70

A) COUNT()

Counts the number of records.

SELECT COUNT(*) FROM Student;

Output: 3

B) SUM()
Calculates the total.

SELECT SUM(Marks) FROM Student;

Output: 240

C) AVG()

Calculates the average.

SELECT AVG(Marks) FROM Student;

Output: 80

D) MAX()

Returns the highest value.

SELECT MAX(Marks) FROM Student;

Output: 90

E) MIN()

Returns the lowest value.

SELECT MIN(Marks) FROM Student;

Output: 70

GROUP BY Example
SELECT Department, AVG(Marks)
FROM Student
GROUP BY Department;

Used to calculate aggregate values for each group.

2. View
A View is a virtual table created from one or more existing tables. It does not store data
physically; it stores only the SQL query.

Advantages of Views
• Improves security.
• Simplifies complex queries.
• Provides data independence.
• Restricts access to specific data.

Creating a View
CREATE VIEW Student_View AS
SELECT Roll_No, Name
FROM Student;

Using a View
SELECT * FROM Student_View;

Deleting a View
DROP VIEW Student_View;

Example
Student Table

Roll_No Name Marks

101 Rahul 80
102 Priya 90

View
CREATE VIEW StudentMarks AS
SELECT Name, Marks
FROM Student;

Output

Name Marks
Rahul 80
Name Marks
Priya 90

3. Indexes
An Index is a database object that improves the speed of data retrieval operations.

It works similarly to an index in a book, allowing the DBMS to locate records quickly
without scanning the entire table.

Advantages of Indexes
• Faster searching of records.
• Improves query performance.
• Reduces data access time.

Disadvantages of Indexes
• Requires additional storage space.
• Slows down INSERT, UPDATE, and DELETE operations because indexes must also
be updated.

Creating an Index
CREATE INDEX idx_name
ON Student(Name);

Creating a Unique Index

CREATE UNIQUE INDEX idx_rollno
ON Student(Roll_No);

Deleting an Index
DROP INDEX idx_name;
Types of Indexes
1. Primary Index

• Created automatically on the primary key.

• Values are unique.

2. Secondary Index

• Created on non-primary key attributes.

3. Unique Index

• Ensures no duplicate values exist.

Difference Between View and Index

View Index
Virtual table Database object for fast searching
Stores query only Stores index structure
Used for security and simplicity Used for performance improvement
Does not store data separately Requires extra storage

Exam Answer (5 Marks)

Aggregate Functions

Aggregate functions perform calculations on multiple rows and return a single value.

Common aggregate functions:

• COUNT() – Counts records.

• SUM() – Calculates total.
• AVG() – Calculates average.
• MAX() – Finds maximum value.
• MIN() – Finds minimum value.

View

A view is a virtual table created from one or more tables. It stores only the query and helps
improve security and simplify data access.
Index

An index is a database object used to improve the speed of data retrieval. It helps the DBMS
locate records quickly and enhances query performance.

GROUP BY and HAVING Clause

1. GROUP BY Clause
The GROUP BY clause is used to group rows that have the same values in specified
columns. It is commonly used with aggregate functions such as COUNT(), SUM(), AVG(),
MAX(), and MIN().

Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name;

Example Table: Student

Roll_No Name Department Marks

101 Rahul CSE 80

102 Priya CSE 90

103 Amit IT 70

104 Neha IT 85

Example 1: Average Marks Department-wise

SELECT Department, AVG(Marks)
FROM Student
GROUP BY Department;

Output

Department AVG(Marks)

CSE 85
Department AVG(Marks)

IT 77.5

Example 2: Count Students in Each Department

SELECT Department, COUNT(*)
FROM Student
GROUP BY Department;

Output

Department COUNT(*)

CSE 2

IT 2

2. HAVING Clause
The HAVING clause is used to filter groups created by the GROUP BY clause.

WHERE filters rows before grouping, while HAVING filters groups after grouping.

Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING condition;

Example 1: Departments with Average Marks Greater Than 80

SELECT Department, AVG(Marks)
FROM Student
GROUP BY Department
HAVING AVG(Marks) > 80;

Output

Department AVG(Marks)

CSE 85
Example 2: Departments Having More Than One Student
SELECT Department, COUNT(*)
FROM Student
GROUP BY Department
HAVING COUNT(*) > 1;

Difference Between WHERE and HAVING

WHERE HAVING

Filters individual rows Filters groups

Used before GROUP BY Used after GROUP BY

Cannot use aggregate functions directly Can use aggregate functions

Applies to records Applies to grouped results

Example Using WHERE

SELECT *
FROM Student
WHERE Marks > 75;

Example Using HAVING

SELECT Department, AVG(Marks)
FROM Student
GROUP BY Department
HAVING AVG(Marks) > 75;

Execution Order
FROM
↓
WHERE
↓
GROUP BY
↓
HAVING
↓
SELECT
↓
ORDER BY

Exam Answer (5 Marks)

GROUP BY Clause

The GROUP BY clause is used to group rows having the same values in specified columns.
It is generally used with aggregate functions like COUNT(), SUM(), AVG(), MAX(), and
MIN().

Example:

SELECT Department, AVG(Marks)

FROM Student
GROUP BY Department;

HAVING Clause

The HAVING clause is used to filter groups formed by the GROUP BY clause. It is similar
to WHERE but works on grouped data.

Example:

SELECT Department, AVG(Marks)

FROM Student
GROUP BY Department
HAVING AVG(Marks) > 80;

Thus, GROUP BY creates groups of records, while HAVING applies conditions to those
groups.

Join Queries in SQL

A JOIN is used to combine data from two or more tables based on a related column between
them.

Joins are useful when data is stored in multiple tables and needs to be retrieved together.

Example Tables
Student Table
Student_ID Name Dept_ID
101 Rahul 1
102 Priya 2
103 Amit 1
Department Table
Dept_ID Dept_Name
1 Computer
2 IT
3 Mechanical

Types of Joins
1. INNER JOIN
Returns only the matching records from both tables.

Syntax
SELECT columns
FROM table1
INNER JOIN table2
ON [Link] = [Link];

Example
SELECT [Link], Department.Dept_Name
FROM Student
INNER JOIN Department
ON Student.Dept_ID = Department.Dept_ID;

Output

Name Dept_Name
Rahul Computer
Priya IT
Amit Computer

2. LEFT JOIN (LEFT OUTER JOIN)

Returns all records from the left table and matching records from the right table.

Syntax
SELECT columns
FROM table1
LEFT JOIN table2
ON [Link] = [Link];
Example
SELECT [Link], Department.Dept_Name
FROM Student
LEFT JOIN Department
ON Student.Dept_ID = Department.Dept_ID;

3. RIGHT JOIN (RIGHT OUTER JOIN)

Returns all records from the right table and matching records from the left table.

Example
SELECT [Link], Department.Dept_Name
FROM Student
RIGHT JOIN Department
ON Student.Dept_ID = Department.Dept_ID;

Output

Name Dept_Name
Rahul Computer
Amit Computer
Priya IT
NULL Mechanical

4. FULL OUTER JOIN

Returns all records from both tables whether matching or not.

Example
SELECT [Link], Department.Dept_Name
FROM Student
FULL OUTER JOIN Department
ON Student.Dept_ID = Department.Dept_ID;

5. CROSS JOIN
Returns the Cartesian Product of both tables.

Syntax
SELECT *
FROM Student
CROSS JOIN Department;
If Student has 3 rows and Department has 3 rows:

Result = 3 × 3 = 9 rows

6. SELF JOIN
A table is joined with itself.

Example

Employee Table

Emp_ID Name Manager_ID

1 Amit NULL
2 Rahul 1
3 Priya 1
SELECT [Link] AS Employee,
[Link] AS Manager
FROM Employee E1
JOIN Employee E2
ON E1.Manager_ID = E2.Emp_ID;

Output

Employee Manager
Rahul Amit
Priya Amit

Join Diagram
INNER JOIN
Common records only

LEFT JOIN
All Left + Matching Right

RIGHT JOIN
All Right + Matching Left

FULL JOIN
All records from both tables

CROSS JOIN
Cartesian Product

SELF JOIN
Table joined with itself
Difference Between INNER JOIN and
OUTER JOIN
INNER JOIN OUTER JOIN
Returns only matching rows Returns matching and non-matching rows
Excludes NULL matches Includes NULL matches
Faster Slightly slower

Exam Answer (5 Marks)

Join Queries

A JOIN is used to combine rows from two or more tables based on a related column.

Types of Joins

1. INNER JOIN – Returns matching records from both tables.

2. LEFT JOIN – Returns all records from the left table and matching records from the
right table.
3. RIGHT JOIN – Returns all records from the right table and matching records from
the left table.
4. FULL OUTER JOIN – Returns all records from both tables.
5. CROSS JOIN – Returns the Cartesian product of tables.
6. SELF JOIN – Joins a table with itself.

Example
SELECT [Link], Department.Dept_Name
FROM Student
INNER JOIN Department
ON Student.Dept_ID = Department.Dept_ID;

Joins are used to retrieve related data from multiple tables efficiently.

Set, Set Operations, and Set Membership in

DBMS
1. Set
A Set is a collection of distinct objects or elements.

In DBMS and relational algebra, a relation (table) is considered a set of tuples (rows).

Example

Let:

A = {1, 2, 3, 4}
B = {3, 4, 5, 6}

Here, A and B are sets containing unique elements.

Properties of a Set

• Elements are unique.

• Order of elements does not matter.
• Duplicate values are not allowed.

2. Set Operations
Set operations are used to combine or compare the results of two queries.

For set operations:

• Both queries must have the same number of columns.

• Corresponding columns must have compatible data types.

A) UNION
Combines results of two queries and removes duplicates.

Syntax
SELECT column_name FROM Table1
UNION
SELECT column_name FROM Table2;

Example

Table A:

Name
Rahul
Priya
Table B:

Name
Priya
Amit
SELECT Name FROM A
UNION
SELECT Name FROM B;

Output

Name
Rahul
Priya
Amit

B) UNION ALL
Combines results and keeps duplicates.

SELECT Name FROM A

UNION ALL
SELECT Name FROM B;

Output

Name
Rahul
Priya
Priya
Amit

C) INTERSECT
Returns only common records from both queries.

SELECT Name FROM A

INTERSECT
SELECT Name FROM B;

Output

Name
Priya
D) EXCEPT (or MINUS in some DBMSs)
Returns records present in the first query but not in the second.

SELECT Name FROM A

EXCEPT
SELECT Name FROM B;

Output

Name
Rahul

Summary of Set Operations

Operation Purpose
UNION Combines and removes duplicates
UNION ALL Combines and keeps duplicates
INTERSECT Finds common records
EXCEPT / MINUS Finds records in first set only

3. Set Membership
Set Membership checks whether a value belongs to a set of values.

In SQL, it is commonly implemented using the IN operator.

Syntax
SELECT *
FROM Student
WHERE column_name IN (value1, value2, value3);

Example

Student Table:

Roll_No Name
101 Rahul
102 Priya
103 Amit
SELECT *
FROM Student
WHERE Roll_No IN (101, 103);
Output

Roll_No Name
101 Rahul
103 Amit

NOT IN Operator
Used to select values that do not belong to a set.

SELECT *
FROM Student
WHERE Roll_No NOT IN (101, 103);

Output

Roll_No Name
102 Priya

Exam Answer (5 Marks)

Set

A set is a collection of distinct elements. In DBMS, relations are treated as sets of tuples.

Set Operations

Set operations are used to combine or compare query results.

1. UNION – Combines results and removes duplicates.

2. UNION ALL – Combines results and keeps duplicates.
3. INTERSECT – Returns common records.
4. EXCEPT (MINUS) – Returns records present in the first query but not in the second.

Set Membership

Set membership checks whether a value belongs to a set. It is implemented using the IN
operator.

Example:

SELECT *
FROM Student
WHERE Roll_No IN (101, 103);
These concepts help perform powerful data retrieval and comparison operations in SQL.

Nested Queries (Subqueries)

A Nested Query or Subquery is a query written inside another SQL query. The inner query
executes first, and its result is used by the outer query.

Definition
A subquery is a SELECT statement nested inside another SQL statement such as
SELECT, INSERT, UPDATE, or DELETE.

Syntax
SELECT column_name
FROM table_name
WHERE column_name OPERATOR
(SELECT column_name
FROM table_name
WHERE condition);

Example Table: Student

Roll_No Name Marks
101 Rahul 80
102 Priya 90
103 Amit 70

Types of Nested Queries

1. Single-Row Subquery
Returns only one row.

Example

Find students having marks greater than the average marks.

SELECT Name, Marks

FROM Student
WHERE Marks >
 (SELECT AVG(Marks)
FROM Student);

Calculation

Average Marks = (80 + 90 + 70)/3 = 80

Output

Name Marks
Priya 90

2. Multiple-Row Subquery
Returns multiple rows.

Example

Find students whose marks are equal to any marks greater than 75.

SELECT Name
FROM Student
WHERE Marks IN
(SELECT Marks
FROM Student
WHERE Marks > 75);

Output

Name
Rahul
Priya

3. Nested Query with IN

Checks membership in a set of values.

Example
SELECT Name
FROM Student
WHERE Roll_No IN
(SELECT Roll_No
FROM Student
WHERE Marks > 80);

Output
Name
Priya

4. Nested Query with EXISTS

Checks whether the subquery returns any rows.

Example
SELECT Name
FROM Student S
WHERE EXISTS
(SELECT *
FROM Student
WHERE Marks > 85);

Since a student with marks greater than 85 exists, the condition becomes true.

5. Nested Query with ANY

Condition is true if it matches at least one value returned by the subquery.

Example
SELECT Name
FROM Student
WHERE Marks > ANY
(SELECT Marks
FROM Student
WHERE Marks < 80);

6. Nested Query with ALL

Condition must be true for all values returned by the subquery.

Example
SELECT Name
FROM Student
WHERE Marks > ALL
(SELECT Marks
FROM Student
WHERE Marks < 80);

Output
Name
Rahul
Priya

Advantages of Nested Queries

• Simplifies complex queries.
• Improves readability.
• Allows dynamic data retrieval.
• Useful for comparisons and filtering.

Example: Employee and Department

Employee Table

Emp_ID Name Dept_ID

1 Amit 10
2 Priya 20

Department Table

Dept_ID Dept_Name
10 HR
20 IT

Query

Find employees working in the IT department.

SELECT Name
FROM Employee
WHERE Dept_ID =
(SELECT Dept_ID
FROM Department
WHERE Dept_Name = 'IT');

Output

Name
Priya
Exam Answer (5 Marks)
Nested Queries (Subqueries)

A Nested Query or Subquery is a query placed inside another SQL query. The inner query
executes first, and its result is passed to the outer query.

Types of Nested Queries

1. Single-row subquery
2. Multiple-row subquery
3. Subquery with IN
4. Subquery with EXISTS
5. Subquery with ANY
6. Subquery with ALL

Example
SELECT Name
FROM Student
WHERE Marks >
(SELECT AVG(Marks)
FROM Student);

Nested queries are used to perform complex data retrieval operations and make SQL
statements more powerful and flexible.

DCL (Data Control Language) and TCL

(Transaction Control Language)
1. DCL (Data Control Language)
DCL is used to control access to data in a database. It provides security by granting or
revoking permissions from users.

DCL Commands

A) GRANT

Used to give privileges to users.

Syntax:

GRANT privilege_name
ON table_name
TO user_name;
Example:

GRANT SELECT, INSERT

ON Student
TO User1;

Meaning: User1 can view and insert records into the Student table.

B) REVOKE

Used to remove privileges from users.

Syntax:

REVOKE privilege_name
ON table_name
FROM user_name;

Example:

REVOKE INSERT
ON Student
FROM User1;

Meaning: User1 can no longer insert records into the Student table.

Advantages of DCL
• Provides database security.
• Controls user access.
• Protects sensitive data.
• Prevents unauthorized operations.

2. TCL (Transaction Control Language)

TCL is used to manage transactions in a database.

A transaction is a sequence of SQL operations performed as a single unit of work.

TCL Commands

A) COMMIT

Permanently saves all changes made during a transaction.

Example:

INSERT INTO Student

VALUES (101, 'Rahul', 20);

COMMIT;

After COMMIT, changes cannot be undone.

B) ROLLBACK

Undoes changes made since the last COMMIT.

Example:

DELETE FROM Student

WHERE Roll_No = 101;

ROLLBACK;

The deleted record is restored.

C) SAVEPOINT

Creates a point within a transaction to which you can later roll back.

Example:

SAVEPOINT sp1;

D) ROLLBACK TO SAVEPOINT

Returns the transaction to a specified savepoint.

Example:

ROLLBACK TO sp1;

Example of TCL Commands

INSERT INTO Student VALUES (101, 'Rahul', 20);

SAVEPOINT sp1;

UPDATE Student
SET Age = 21
WHERE Roll_No = 101;

ROLLBACK TO sp1;

COMMIT;

Explanation

• Record is inserted.
• Savepoint sp1 is created.
• Age is updated.
• Rollback returns to sp1, canceling the update.
• COMMIT saves the remaining changes.

Difference Between DCL and TCL

DCL TCL
Controls user permissions Controls transactions
Related to database security Related to data consistency
Commands: GRANT, REVOKE Commands: COMMIT, ROLLBACK, SAVEPOINT
Manages access rights Manages changes to data

Transaction States
Active
↓
Partially Committed
↓
Committed

If an error occurs:

Active
↓
Failed
↓
Rolled Back

Exam Answer (5 Marks)

DCL (Data Control Language)
DCL is used to control access to data in a database.
Commands:

• GRANT – Gives privileges to users.

• REVOKE – Removes privileges from users.

Example:

GRANT SELECT ON Student TO User1;

TCL (Transaction Control Language)

TCL is used to manage database transactions.

Commands:

• COMMIT – Saves changes permanently.

• ROLLBACK – Undoes changes.
• SAVEPOINT – Creates a point within a transaction.

Example:

SAVEPOINT sp1;
ROLLBACK TO sp1;
COMMIT;

Thus, DCL provides security by controlling user permissions, while TCL ensures data
consistency by managing transactions.