SQL (Structured Query Language) is the standard language used to manage and manipulate relational
databases. SQL commands are broadly categorized into several types, out of which the three most
important are:
• DDL (Data Definition Language)
• DML (Data Manipulation Language)
• DCL (Data Control Language)
Each of these types of commands serves a different purpose in the database environment.
1. DDL (Data Definition Language):
DDL commands are used to define and manage database schema and objects such as tables, indexes,
and views. These commands deal with the structure of the database rather than the data it holds.
➤ Common DDL Commands:
• CREATE: Used to create a new table or database.
• ALTER: Used to modify the structure of an existing table.
• DROP: Used to delete a table or database permanently.
• TRUNCATE: Removes all records from a table but not the table structure.
• RENAME: Renames a database object.
Examples:
a. CREATE:
sql
CopyEdit
CREATE TABLE Students (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT
);
This creates a table named Students.
b. ALTER:
sql
CopyEdit
ALTER TABLE Students ADD Email VARCHAR(100);
Adds a new column Email to the Students table.
c. DROP:
sql
CopyEdit
DROP TABLE Students;
Completely removes the Students table from the database.
d. TRUNCATE:
sql
CopyEdit
TRUNCATE TABLE Students;
Removes all rows from the table, but the table structure remains intact.
2. DML (Data Manipulation Language):
DML commands are used for performing operations on the data stored in the database. These
commands allow users to insert, modify, retrieve, and delete data from tables.
➤ Common DML Commands:
• INSERT: Adds new records to a table.
• UPDATE: Modifies existing records in a table.
• DELETE: Deletes records from a table.
• SELECT: Retrieves data from one or more tables (sometimes also considered under DQL –
Data Query Language).
Examples:
a. INSERT:
sql
CopyEdit
INSERT INTO Students (ID, Name, Age) VALUES (1, 'Alice', 20);
Adds a new student record.
b. UPDATE:
sql
CopyEdit
UPDATE Students SET Age = 21 WHERE ID = 1;
Changes the age of the student whose ID is 1.
c. DELETE:
sql
CopyEdit
DELETE FROM Students WHERE ID = 1;
Deletes the record of the student with ID = 1.
d. SELECT:
sql
CopyEdit
SELECT * FROM Students;
Displays all records from the Students table.
3. DCL (Data Control Language):
DCL commands are used to control access to data in a database. They help manage permissions and
access rights of users over database objects.
➤ Common DCL Commands:
• GRANT: Gives users access privileges to the database.
• REVOKE: Removes access privileges.
Examples:
a. GRANT:
sql
CopyEdit
GRANT SELECT, INSERT ON Students TO user1;
Allows user1 to select and insert data into the Students table.
b. REVOKE:
sql
CopyEdit
REVOKE INSERT ON Students FROM user1;
Removes the INSERT privilege from user1.
Key Differences Between DDL, DML, and DCL:
Feature DDL DML DCL
Purpose Defines structure Manages data Manages access
Affects Schema (tables, objects) Table rows Users and privileges
Auto-commit Yes No Yes
Rollback possible? No Yes No
1. Overview of SQL (Structured Query Language):
SQL (Structured Query Language) is the standard language used to interact with relational
databases. It enables users to perform operations such as creating databases and tables, inserting,
updating, deleting, and retrieving data efficiently.
SQL is both declarative (what to do, not how) and non-procedural, making it accessible and easy to
learn. It is used with most RDBMSs such as MySQL, PostgreSQL, Oracle, MS SQL Server, etc.
Common SQL Categories:
• DDL (Data Definition Language) – e.g., CREATE, ALTER, DROP
• DML (Data Manipulation Language) – e.g., INSERT, UPDATE, DELETE
• DCL (Data Control Language) – e.g., GRANT, REVOKE
• TCL (Transaction Control Language) – e.g., COMMIT, ROLLBACK
• DQL (Data Query Language) – e.g., SELECT
2. Data Types in SQL:
SQL provides various data types to define the kind of data a column can hold. These can be broadly
classified into:
a) Numeric Types:
• INT, SMALLINT, FLOAT, DECIMAL, NUMERIC
sql
CopyEdit
Salary DECIMAL(10, 2);
b) String/Text Types:
• CHAR(n): Fixed-length string
• VARCHAR(n): Variable-length string
sql
CopyEdit
Name VARCHAR(50);
c) Date and Time Types:
• DATE, TIME, DATETIME, TIMESTAMP
sql
CopyEdit
DOB DATE;
d) Boolean:
• BOOLEAN: Stores TRUE or FALSE
Proper selection of data type ensures efficient storage, data integrity, and optimized performance.
3. Simple and Nested Queries:
a) Simple Query:
A query that retrieves data from a single table using SELECT.
Example:
sql
CopyEdit
SELECT Name, Age FROM Students WHERE Age > 18;
Retrieves names and ages of students older than 18.
b) Nested (or Sub) Query:
A query embedded within another query. The inner query provides input to the outer query.
Types:
• Single-row Subquery – returns one value
• Multi-row Subquery – returns multiple rows
• Correlated Subquery – depends on outer query
Example:
sql
CopyEdit
SELECT Name FROM Students
WHERE Age = (SELECT MAX(Age) FROM Students);
Returns the name(s) of the oldest student(s).
4. Basic SQL Functions:
SQL functions are built-in operations used to perform calculations or manipulate data. They are
divided into two main categories:
a) Aggregate Functions – operate on a set of rows:
• COUNT() – counts number of records
• SUM() – calculates total
• AVG() – finds average
• MAX() – maximum value
• MIN() – minimum value
Example:
sql
CopyEdit
SELECT COUNT(*) FROM Orders WHERE Status = 'Delivered';
b) Scalar Functions – operate on single values:
• UPPER(), LOWER() – convert text
• LEN() or LENGTH() – length of string
• ROUND() – round numeric value
• NOW() – current timestamp
Example:
sql
CopyEdit
SELECT UPPER(Name) FROM Customers;
1. SQL Joins:
Joins in SQL are used to combine rows from two or more tables based on a related column between
them (usually a foreign key). Joins are essential for retrieving meaningful data that is spread across
multiple tables.
Common Types of Joins:
a) INNER JOIN:
Returns records with matching values in both tables.
sql
CopyEdit
SELECT Employees.Name, Departments.DeptName
FROM Employees
INNER JOIN Departments
ON Employees.DeptID = Departments.ID;
Result: Only employees who belong to a department.
b) LEFT JOIN (or LEFT OUTER JOIN):
Returns all records from the left table and matched records from the right table. Non-matching rows
from the right will return NULLs.
sql
CopyEdit
SELECT Employees.Name, Departments.DeptName
FROM Employees
LEFT JOIN Departments
ON Employees.DeptID = Departments.ID;
Result: All employees, even if they don’t belong to any department.
c) RIGHT JOIN (or RIGHT OUTER JOIN):
Opposite of LEFT JOIN. All records from the right table and matching from the left.
d) FULL OUTER JOIN:
Returns all records when there is a match in either left or right table.
e) CROSS JOIN:
Returns the Cartesian product (every row of table A joined with every row of table B).
sql
CopyEdit
SELECT A.Name, B.Product
FROM Customers A
CROSS JOIN Products B;
Result: Every customer matched with every product.
2. Data Integrity Constraints:
Constraints ensure the accuracy and reliability of data in the database. These are rules enforced on
data columns to maintain integrity.
a) NOT NULL – Ensures a column cannot have NULL value.
sql
CopyEdit
Name VARCHAR(100) NOT NULL;
b) UNIQUE – Ensures all values in a column are unique.
sql
CopyEdit
Email VARCHAR(100) UNIQUE;
c) PRIMARY KEY – Uniquely identifies each record in a table.
sql
CopyEdit
ID INT PRIMARY KEY;
d) FOREIGN KEY – Creates a link between two tables.
sql
CopyEdit
FOREIGN KEY (DeptID) REFERENCES Departments(ID);
e) CHECK – Validates values in a column based on a condition.
sql
CopyEdit
Salary INT CHECK (Salary > 0);
f) DEFAULT – Assigns a default value if none is provided.
sql
CopyEdit
Status VARCHAR(10) DEFAULT 'Active';
g) AUTO_INCREMENT (MySQL-specific) – Automatically increases integer value (for PKs).
3. Views in SQL:
A View is a virtual table based on the result set of an SQL query. It does not store data physically but
provides a way to simplify complex queries, enhance security, and improve readability.
Syntax:
sql
CopyEdit
CREATE VIEW ActiveEmployees AS
SELECT Name, DeptID
FROM Employees
WHERE Status = 'Active';
Features of Views:
• Acts like a table.
• Can be used in SELECT, JOIN, and even UPDATE (in certain conditions).
• Simplifies complex queries for users.
• Can restrict access to specific columns (security).
Example – Using the View:
sql
CopyEdit
SELECT * FROM ActiveEmployees;
Updating Through a View:
sql
CopyEdit
UPDATE ActiveEmployees
SET DeptID = 5
WHERE Name = 'John';
Note: The update will reflect in the underlying table if the view is updatable.
