SQL Tutorial for DBMS Unit III: Commands, DDL, DML, Joins and Subqueries

Posted on Oct 14, 2026 in Computer Engineering

DBMS Unit III

1. SQL

Full Form

SQL = Structured Query Language

SQL is used to communicate with a relational database.

It is used to:

Create tables
Insert data
Retrieve data
Update data
Delete data
Create views
Define constraints
Control access to data

The uploaded notes also describe SQL as supporting data definition, data manipulation, view definition, transaction control and authorization.

2. Features of SQL ⭐⭐⭐⭐

1. Simple

SQL commands are relatively easy to learn.

2. Data Definition

SQL can create and modify database structures.

3. Data Manipulation

SQL can insert, update and delete records.

4. Data Retrieval

SQL can retrieve required information using SELECT.

5. Security

SQL supports authorization and access control.

6. Integrity

SQL supports constraints such as primary key, foreign key and NOT NULL.

7. Views

SQL allows creation of views.

8. Query Processing

SQL allows users to formulate database queries.

3. SQL Command Categories ⭐⭐⭐⭐⭐

For exam, remember:

SQL
│
├── DDL → Structure
├── DML → Data
├── DQL → Retrieval
├── DCL → Permission
└── TCL → Transaction

4. DDL — Data Definition Language ⭐⭐⭐⭐⭐

Definition

DDL is used to define and modify the structure/schema of database objects such as tables.

Important commands:

CREATE
ALTER
DROP
TRUNCATE

The uploaded material specifically describes DDL as defining relation schemas, attribute types, integrity constraints, indexes, security information and storage structure.

5. CREATE

Used to create a database object such as a table.

Syntax

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

Example

CREATE TABLE Student (
    Roll_No INT PRIMARY KEY,
    Name VARCHAR(30),
    Branch VARCHAR(20),
    Marks INT
);

This creates:

Student
-------------------------
Roll_No
Name
Branch
Marks

6. ALTER

Used to modify the structure of an existing table.

Add a column

ALTER TABLE Student
ADD Phone VARCHAR(15);

Now the table has:

Roll_No
Name
Branch
Marks
Phone

Exam example

ALTER TABLE Doctor
ADD Experience INT;

This is exactly the type of DDL operation useful for your 6-mark practical SQL questions.

7. DROP

Used to completely remove a database object.

DROP TABLE Student;

It removes the table and its data.

Remember

DROP = Remove complete table

8. TRUNCATE

Used to remove all rows from a table while keeping the table structure.

TRUNCATE TABLE Student;

After truncation:

Table exists ✓
Columns exist ✓
Rows deleted ✓

9. DELETE vs TRUNCATE vs DROP ⭐⭐⭐⭐⭐

DELETE	TRUNCATE	DROP
Removes rows	Removes all rows	Removes table
DML	DDL	DDL
`WHERE` can be used	`WHERE` not used	No `WHERE`
Table remains	Table remains	Table removed

Examples:

DELETE FROM Student
WHERE Roll_No = 101;

TRUNCATE TABLE Student;

DROP TABLE Student;

10. DML — Data Manipulation Language ⭐⭐⭐⭐⭐

Definition

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

Important commands:

INSERT
UPDATE
DELETE

The uploaded SQL material explicitly describes DML as providing the ability to insert tuples, delete tuples and modify tuples in the database.

11. INSERT

Used to add new records.

Syntax

INSERT INTO table_name
VALUES (value1, value2, ...);

Example

INSERT INTO Student
VALUES (101, 'Rahul', 'CSE', 80);

Specific columns

INSERT INTO Student
(Roll_No, Name, Branch, Marks)
VALUES
(102, 'Amit', 'CSE', 75);

12. UPDATE ⭐⭐⭐⭐⭐

Used to modify existing records.

Syntax

UPDATE table_name
SET column = value
WHERE condition;

Example

UPDATE Student
SET Marks = 85
WHERE Roll_No = 101;

Your question example

Update City of customer whose ID is 102:

UPDATE Customer
SET City = 'Delhi'
WHERE Customer_ID = 102;

Important

If you forget WHERE:

UPDATE Student
SET Marks = 85;

then all records may be updated.

13. DELETE ⭐⭐⭐⭐⭐

Used to delete records.

Syntax

DELETE FROM table_name
WHERE condition;

Example

DELETE FROM Student
WHERE Roll_No = 101;

Your question example

DELETE FROM Room
WHERE Room_ID = 203;

14. DML vs DDL ⭐⭐⭐⭐⭐

DDL	DML
Data Definition Language	Data Manipulation Language
Deals with structure	Deals with data
CREATE	INSERT
ALTER	UPDATE
DROP	DELETE
TRUNCATE
Changes schema/table structure	Changes table records

Easy trick:

DDL = Design/Structure

DML = Data

15. DQL — Data Query Language

Used to retrieve data.

Main command:

SELECT

Example:

SELECT * FROM Student;

16. SELECT Statement ⭐⭐⭐⭐⭐

Used to retrieve data from one or more tables.

Select all columns

SELECT * FROM Student;

Select specific columns

SELECT Name, Marks
FROM Student;

Select with condition

SELECT Name
FROM Student
WHERE Marks > 70;

17. Basic SQL Query Structure ⭐⭐⭐⭐⭐

SELECT column_name
FROM table_name
WHERE condition;

Example:

SELECT Name, Salary
FROM Employee
WHERE Salary > 50000;

Meaning:

SELECT → What to display
FROM   → From which table
WHERE  → Which records

18. WHERE Clause

Used to specify conditions.

Example:

SELECT *
FROM Employee
WHERE Salary > 50000;

19. SQL Operators ⭐⭐⭐⭐⭐

The uploaded material covers arithmetic, relational and logical operators.

Arithmetic Operators

+
-
*
/

Example:

SELECT Salary * 12
FROM Employee;

Relational Operators

=
<>
!=
<
>
<=
>=

Example:

SELECT *
FROM Employee
WHERE Salary >= 50000;

20. Logical Operators

AND

Both conditions must be true.

SELECT *
FROM Employee
WHERE Salary > 50000
AND Age > 30;

OR

At least one condition must be true.

SELECT *
FROM Employee
WHERE Department = 'CSE'
OR Department = 'IT';

NOT

Reverses a condition.

SELECT *
FROM Employee
WHERE NOT Department = 'CSE';

21. BETWEEN

Used to check a range.

SELECT *
FROM Employee
WHERE Salary BETWEEN 40000 AND 70000;

22. IN

Used to match multiple values.

SELECT *
FROM Employee
WHERE Department IN ('CSE', 'IT');

23. LIKE

Used for pattern matching.

Starts with A

SELECT *
FROM Employee
WHERE Name LIKE 'A%';

Ends with A

SELECT *
FROM Employee
WHERE Name LIKE '%a';

Contains A

SELECT *
FROM Employee
WHERE Name LIKE '%a%';

24. NULL

NULL means the value is missing or unknown.

To check NULL:

SELECT *
FROM Employee
WHERE Salary IS NULL;

To check non-NULL:

SELECT *
FROM Employee
WHERE Salary IS NOT NULL;

Do not use:

Salary = NULL

Use:

Salary IS NULL

25. Aggregate Functions ⭐⭐⭐⭐⭐

Aggregate functions operate on a group of values and produce a single result. The uploaded material includes examples such as SUM() and AVG().

Important functions:

COUNT()
SUM()
AVG()
MAX()
MIN()

COUNT()

Counts records.

SELECT COUNT(*)
FROM Employee;

SUM()

Calculates total.

SELECT SUM(Salary)
FROM Employee;

AVG()

Calculates average.

SELECT AVG(Salary)
FROM Employee;

MAX()

Finds maximum value.

SELECT MAX(Salary)
FROM Employee;

MIN()

Finds minimum value.

SELECT MIN(Salary)
FROM Employee;

26. GROUP BY ⭐⭐⭐⭐⭐

Used to group rows having the same value.

Example:

SELECT Dept_ID, AVG(Salary)
FROM Employee
GROUP BY Dept_ID;

Meaning:

Find average salary for each department.

27. HAVING

Used to apply a condition to groups.

Example:

SELECT Dept_ID, COUNT(*)
FROM Employee
GROUP BY Dept_ID
HAVING COUNT(*) > 2;

Difference

WHERE  → condition on rows
HAVING → condition on groups

28. ORDER BY

Used to sort results.

Ascending

SELECT *
FROM Employee
ORDER BY Salary ASC;

Descending

SELECT *
FROM Employee
ORDER BY Salary DESC;

29. DISTINCT

Used to remove duplicate values from output.

SELECT DISTINCT Department
FROM Employee;

30. SQL JOIN ⭐⭐⭐⭐⭐

Used to retrieve related data from multiple tables.

Suppose:

Employee

Emp_ID	Name	Dept_ID
101	Rahul	1
102	Amit	2

Department

Dept_ID	Dept_Name
1	CSE
2	IT

Query:

SELECT E.Name, D.Dept_Name
FROM Employee E
JOIN Department D
ON E.Dept_ID = D.Dept_ID;

Result:

Name	Dept_Name
Rahul	CSE
Amit	IT

31. Subquery ⭐⭐⭐⭐⭐

A subquery is a query written inside another SQL query.

Example

Find employees earning more than average salary:

SELECT Name, Salary
FROM Employee
WHERE Salary >
      (SELECT AVG(Salary)
       FROM Employee);

Here:

Outer query
    ↓
SELECT Name, Salary...

Inner query
    ↓
SELECT AVG(Salary)...

The uploaded material includes nested subqueries and examples involving average salary.

32. Subquery with IN

Find employees working in CSE or IT:

SELECT Name
FROM Employee
WHERE Dept_ID IN
(
    SELECT Dept_ID
    FROM Department
    WHERE Dept_Name IN ('CSE','IT')
);

33. View ⭐⭐⭐⭐⭐

A view is a virtual table based on the result of a SQL query.

Create View

CREATE VIEW CSE_Employees AS
SELECT Emp_ID, Name, Salary
FROM Employee
WHERE Department = 'CSE';

Now:

SELECT *
FROM CSE_Employees;

Advantages of Views

Security
Simplicity
Data abstraction
Restricts access to selected columns/rows
Provides customized views for users

34. UPDATE Statements — Important

Update one employee

UPDATE Employee
SET Salary = 60000
WHERE Emp_ID = 101;

Increase all salaries by 5%

UPDATE Employee
SET Salary = Salary * 1.05;

Increase salary of employees earning less than 70000

UPDATE Employee
SET Salary = Salary * 1.05
WHERE Salary < 70000;

These update examples are directly reflected in your uploaded lecture material.

35. SQL Practical — Your 6-Mark Questions

Question 1: Doctor

Create table

CREATE TABLE Doctor (
    Doctor_ID INT PRIMARY KEY,
    Doctor_Name VARCHAR(50),
    Specialization VARCHAR(50),
    Salary INT
);

Insert record

INSERT INTO Doctor
VALUES (101, 'Rahul', 'Cardiology', 70000);

Add Experience

ALTER TABLE Doctor
ADD Experience INT;

36. Customer

Create

CREATE TABLE Customer (
    Customer_ID INT PRIMARY KEY,
    Customer_Name VARCHAR(50),
    City VARCHAR(30),
    Phone VARCHAR(15)
);

Insert

INSERT INTO Customer
VALUES (101, 'Amit', 'Bhopal', '9876543210');

Update

UPDATE Customer
SET City = 'Delhi'
WHERE Customer_ID = 102;

37. Room

Create

CREATE TABLE Room (
    Room_ID INT PRIMARY KEY,
    Room_Type VARCHAR(30),
    Floor INT,
    Rent INT
);

Insert

INSERT INTO Room
VALUES (201, 'Deluxe', 2, 2500);

Delete

DELETE FROM Room
WHERE Room_ID = 203;

38. Account

CREATE TABLE Account (
    Account_No INT PRIMARY KEY,
    Customer_Name VARCHAR(50),
    Account_Type VARCHAR(30),
    Balance DECIMAL(10,2)
);

Insert:

INSERT INTO Account
VALUES (1001, 'Rahul', 'Savings', 25000);

Add branch:

ALTER TABLE Account
ADD Branch_Name VARCHAR(50);

39. Registration

CREATE TABLE Registration (
    Registration_ID INT PRIMARY KEY,
    Student_Name VARCHAR(50),
    Course_Name VARCHAR(50),
    Semester INT
);

Insert:

INSERT INTO Registration
VALUES (201, 'Vikas', 'CSE', 5);

Update:

UPDATE Registration
SET Semester = 6
WHERE Registration_ID = 202;

40. Bus

CREATE TABLE Bus (
    Bus_ID INT PRIMARY KEY,
    Bus_Number VARCHAR(20),
    Route VARCHAR(50),
    Capacity INT
);

Insert:

INSERT INTO Bus
VALUES (301, 'MP07AB1234', 'Gwalior-Bhopal', 50);

Delete:

DELETE FROM Bus
WHERE Bus_ID = 303;