Introduction to SQL

(Structured Query
Language)
1. What is MySQL?
MySQL is an open-source Relational Database Management System
(RDBMS) that uses Structured Query Language (SQL) to manage and
manipulate data. It is primarily developed by MySQL AB (later acquired by Oracle
Corporation).

2. Why Use MySQL?

Feature Description
Open Source Free to use and modify
Cross-Platform Runs on Windows, macOS, Linux
Reliable Handles thousands of transactions per
Performance second
Secure Role-based access control and encryption
features
Widely Used with PHP, Java, Python, WordPress,
Supported etc.

3. Where is MySQL Used?

Organization Use of MySQL
bKash Stores customer transaction data
Daraz Stores product, customer, and order information
Bangladesh
IUB SRMS Student information system (courses, grades,
registration)
Chaldal Inventory and logistics tracking
[Link] Real-time bus ticketing and seat reservation

4. What is SQL?
Structured Query Language (SQL) is used to create, read, update, and
delete (CRUD) data in MySQL.

5. Types of SQL Commands

SQL commands are classified into five major categories based on their
functionality. These categories are:

I. DDL – Data Definition Language

DDL is a subset of SQL used to define and manage the structure
of database objects like tables, views, indexes, and schemas.

Think of DDL as "designing the blueprint" of a database system.

Common DDL Commands

Comman Purpose
d
CREATE To create new database objects (e.g.,
tables)
ALTER To modify existing table structure
DROP To permanently remove database
objects
TRUNCA To delete all data from a table quickly
TE

CREATE TABLE – Creating a Table

This command defines a table and its columns, along with data types and
constraints.

Example: Create a Customer table (Daraz)

CREATE TABLE Customer (

CustomerID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE,
Phone VARCHAR(15),
Address TEXT
);

Explanation:

 INT: For numeric ID

 VARCHAR(n): Text data up to 'n' characters
 TEXT: Long text
 PRIMARY KEY: Uniquely identifies a record
 NOT NULL: Cannot be empty
 UNIQUE: No duplicates allowed

ALTER TABLE – Modifying Table Structure

Used to add, modify, or delete columns from an existing table.

Example: Add a new column DateOfBirth to Customer

ALTER TABLE Customer
ADD DateOfBirth DATE;

Example: Change column type

ALTER TABLE Customer

MODIFY Phone VARCHAR(20);

Example: Rename a column

ALTER TABLE Customer

CHANGE Address FullAddress TEXT;

DROP TABLE – Delete a Table Permanently

Removes a table and all its data and structure. This action is irreversible.

Example:

DROP TABLE Customer;

⚠️Use with caution – this will remove the entire table and its data.

TRUNCATE TABLE – Delete All Records Quickly

Deletes all rows from a table, but retains the structure (i.e., table remains
for reuse).

Example:

TRUNCATE TABLE Customer;

Faster than DELETE FROM Customer, and it cannot be rolled back in most
systems.

Real-Life Use Case: Chaldal Inventory System

Imagine a Product table:

CREATE TABLE Product (

ProductID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Price DECIMAL(10,2) CHECK (Price > 0),
Stock INT DEFAULT 0,
CategoryID INT,
FOREIGN KEY (CategoryID) REFERENCES Category(CategoryID)
);

This uses multiple DDL features:

 Data types
 Constraints
  Foreign keys for linking to categories

Class Activity

Design a Student table for IUB that includes:

 StudentID, Name, Email, Department, CGPA

 Use suitable data types
 Add constraints: PRIMARY KEY, NOT NULL, CHECK

Bonus: Write an ALTER statement to add Mobile later

Reflection Questions

1. What is the difference between DROP and TRUNCATE?

2. Why are constraints important when creating tables?
3. Which command would you use to rename a column?

II. DML – Data Manipulation Language

DML is a subset of SQL used to modify the actual data in a database.

If DDL is about creating the structure, DML is about working with the data
itself.

Common DML Commands

Comman Purpose
d
INSERT Add new rows of data
UPDATE Modify existing data
DELETE Remove data from tables
SELECT Retrieve data (covered under
DQL)

Note: SELECT is technically DQL (Data Query Language), but is often introduced
with DML due to its frequent use.

INSERT Statement

Syntax:

INSERT INTO table_name (column1, column2, ...)

VALUES (value1, value2, ...);

Example – Add Customers in Daraz:

INSERT INTO Customer (CustomerID, Name, Email, Phone)

VALUES (101, 'Farhana Ahmed', 'farhana@[Link]', '01711223344');

You can also insert multiple records:

INSERT INTO Customer (CustomerID, Name, Email, Phone)
VALUES
(102, 'Rafiq Islam', 'rafiq@[Link]', '01844556677'),
(103, 'Nasrin Jahan', 'nasrin@[Link]', '01678912345');

UPDATE Statement

Used to modify existing records in a table.

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example – Update Customer Phone:

UPDATE Customer
SET Phone = '01999887766'
WHERE CustomerID = 101;

Always use a WHERE clause — otherwise, all records may be changed!

DELETE Statement

Used to remove rows from a table.

Syntax:

DELETE FROM table_name

WHERE condition;

Example – Delete a customer:

DELETE FROM Customer

WHERE CustomerID = 103;

Like UPDATE, if you omit the WHERE clause, all records will be deleted.

Example: IUB Library Borrowing System

Suppose we have:

Book Table:

BookI Title Stoc

D k
B001 SQL for 5
Beginners
B002 Data Science 2
Intro
Student Table:

StudentI Name
D
S001 Nafisa
Akter
S002 Arafat
Karim

Insert Example:

INSERT INTO Book (BookID, Title, Stock)

VALUES ('B003', 'AI Basics', 3);

Update Example:

UPDATE Book
SET Stock = Stock - 1
WHERE BookID = 'B001';

Delete Example:

DELETE FROM Book

WHERE BookID = 'B003';

Real-Life DML Examples in Bangladesh

Organization Use of DML Commands

bKash Update balance after transaction
(UPDATE)
Grameenpho Delete expired promotional offers
ne (DELETE)
Unimart Insert new product stock (INSERT)
Shohoz Update seat availability in real-time
(UPDATE)

Class Activity

Task:

1. Create a Student table with fields: StudentID, Name, Major

2. Insert 2 sample students
3. Update one student’s Major
4. Delete one student

Write the SQL for each step.

Response

1. Create the Student Table

CREATE TABLE Student (
StudentID INT PRIMARY KEY,
Name VARCHAR(100),
Major VARCHAR(50)
);

2. Insert 2 Sample Students

INSERT INTO Student (StudentID, Name, Major) VALUES
(20211401, 'Nafi Ahmed', 'MIS'),
(20211402, 'Rumi Akter', 'FIN');

3. Update One Student’s Major

Change Rumi’s Major from FIN to MKT:

UPDATE Student
SET Major = 'MKT'
WHERE StudentID = 20211402;

4. Delete One Student

Delete student with ID 20211401 (Nafi):

DELETE FROM Student

WHERE StudentID = 20211401;

a. DCL – Data Control Language

DCL (Data Control Language) is a subset of SQL used to control access to
data and database operations by managing user privileges.

Think of DCL as the security gatekeeper for the database.

Why DCL Matters

Without DCL… With DCL…

Anyone can change or delete Access is limited to authorized users
data
Risk of data theft or corruption Confidentiality and integrity are
preserved
No control over operations Permissions can be assigned role-
(read/write) wise

DCL Commands Overview

Comman Description
 d
GRANT Gives user(s) specific privileges (e.g., SELECT,
INSERT)
REVOKE Removes privileges previously granted to a
user

GRANT Command

Gives one or more users the permission to perform operations.

Syntax:

GRANT privileges
ON database_name.table_name
TO 'username'@'host';

Example:

GRANT SELECT, INSERT

ON [Link]
TO 'report_user'@'localhost';

This allows user report_user to view and add customers, but not delete or
update.

REVOKE Command

Removes previously granted privileges from a user.

Syntax:

REVOKE privileges
ON database_name.table_name
FROM 'username'@'host';

Example:

REVOKE INSERT
ON [Link]
FROM 'report_user'@'localhost';

This removes the ability to insert data but keeps other permissions (e.g.,
SELECT).

Types of Privileges in MySQL

Privilege Meaning
SELECT View data in tables or views
INSERT Add new data
UPDATE Modify existing records
DELETE Remove records
ALL PRIVILEGES Full access to the database or
 table
CREATE, DROP, Modify structure (DDL
ALTER privileges)

Real-Life Examples

Organizati Scenario DCL Application

on
bKash Only Finance Team can delete Grant DELETE only to finance
transactions department accounts
Daraz Warehouse staff can only update Grant UPDATE on Product
inventory, not customer data table only
IUB SRMS Faculty can read student data but Grant SELECT on
cannot change grades StudentGrades, deny UPDATE
[Link] Ticket agents can issue tickets Grant INSERT on Booking,
m but not cancel them revoke DELETE

Difference Between DCL and Other SQL Subsets

SQL Purpose Examples

Type
DDL Define structure CREATE, ALTER,
DROP
DML Manipulate data INSERT, UPDATE,
DELETE
DCL Control user access GRANT, REVOKE
DQL Retrieve data SELECT
(queries)

Combining DCL with User Management

Create a new user:

CREATE USER 'store_user'@'localhost' IDENTIFIED BY 'password123';

Grant privileges:

GRANT SELECT, INSERT ON [Link] TO 'store_user'@'localhost';

Revoke privileges:

REVOKE INSERT ON [Link] FROM 'store_user'@'localhost';

Best Practices for Using DCL

Practice Why It Matters

Grant minimum necessary Follows the principle of least
access privilege
Use roles for groups Easier to manage access by
department
Revoke unused Reduce risk of accidental data
permissions exposure
 Document access policies Helps with audits and compliance

Class Activity

Scenario: You are a database administrator for a library system.

 Create a user called librarian_user

 Grant permission to SELECT and INSERT books
 Revoke DELETE privilege from the same user

Write the corresponding SQL commands.

Response

1. Create the User

CREATE USER 'librarian_user'@'localhost' IDENTIFIED BY 'password123';

2. Grant SELECT and INSERT on Book Table

GRANT SELECT, INSERT
ON [Link]
TO 'librarian_user'@'localhost';

Replace Library with your actual database name.

3. Revoke DELETE Privilege (if it was previously granted)

REVOKE DELETE
ON [Link]
FROM 'librarian_user'@'localhost';

This ensures the user cannot delete book records, even if they had permission earlier.
IV. TCL – Transaction Control Language
What is a Transaction?

A transaction is a group of one or more SQL operations that are executed as a

single unit of work.

Either all operations succeed, or none take effect.

Example Scenario:
In bKash, when you send money:

 Deduct from sender

 Add to receiver

Both must succeed — or neither happens!

ACID Properties of Transactions

Example (Bangladesh
Property Meaning
Context)
bKash transfer either deducts
Atomicity All steps succeed or fail together
and credits or neither
Consisten Database moves from one valid Total balance in system remains
cy state to another correct after Tx
Each transaction is executed as if it Two Shohoz users booking the
Isolation
were alone same seat won't collide
Once committed, data remains Daraz order saved even if
Durability
even after crash or shutdown system fails right after

TCL Commands Overview

Command Purpose
BEGIN / START
Starts a transaction block
TRANSACTION
Saves all changes made in the current
COMMIT
transaction
Cancels all changes made in the current
ROLLBACK
transaction
SAVEPOINT Marks a specific point in a transaction
RELEASE SAVEPOINT Deletes a savepoint

MySQL Syntax Examples

Start a Transaction

START TRANSACTION;
-- or
BEGIN;

COMMIT

COMMIT;

Makes all changes permanent.

ROLLBACK

ROLLBACK;

Undoes all changes since BEGIN.

SAVEPOINT

SAVEPOINT point1;

ROLLBACK TO SAVEPOINT

ROLLBACK TO point1;

Example: E-commerce Transaction (Daraz)

START TRANSACTION;

UPDATE Product SET Stock = Stock - 1 WHERE ProductID = 101;

INSERT INTO Order (OrderID, CustomerID, ProductID, Qty)
VALUES (501, 1001, 101, 1);

COMMIT;

✅ If both queries succeed → data saved.

❌ If any fails → ROLLBACK to cancel.

Example: bKash Transaction

START TRANSACTION;

UPDATE UserAccount SET Balance = Balance - 500 WHERE AccountID = 201;

UPDATE UserAccount SET Balance = Balance + 500 WHERE AccountID = 202;

COMMIT;

If power fails before COMMIT, the entire transaction is undone — protecting

both users.

Using SAVEPOINT

START TRANSACTION;
DELETE FROM Product WHERE Category = 'Expired';
SAVEPOINT before_critical;

DELETE FROM Product WHERE Price < 10;

-- Oops! Wrong delete

ROLLBACK TO before_critical;

COMMIT;

Only the second delete is undone, not the first.

Key Differences: COMMIT vs. ROLLBACK

COMMIT ROLLBACK
Saves the changes Cancels all changes since last
permanently BEGIN
Cannot be undone Can undo unsafe changes

Complete syntax of START, SAVEPOINT, ROLLBACK

START TRANSACTION;

UPDATE Product SET Stock = Stock - 1 WHERE ProductID = 101;

SAVEPOINT after_stock_update;

UPDATE Product SET Price = Price - 100 WHERE ProductID = 101;

-- Oops! Wrong price update

ROLLBACK TO after_stock_update;

COMMIT;

Real-Life TCL Use Cases in Bangladesh

Organizati Use Case TCL Command Used

on
bKash Transfer money securely START, COMMIT,
ROLLBACK
Shohoz Book bus ticket — update seat & START, SAVEPOINT,
payment ROLLBACK
Aarong Update inventory and sales record START, COMMIT
together
Chaldal Process order: reduce stock, add START, COMMIT,
invoice ROLLBACK

Class Discussion Prompt

Imagine you’re a developer for Pathao.

What could go wrong if you don’t use transactions when:
  Deducting fare from rider's wallet
 Paying the driver

When would you use ROLLBACK?

Response

What Could Go Wrong Without Transactions in Pathao?

Scenario:

When a ride is completed, two critical financial operations occur:

1. Deduct fare from the rider’s wallet

2. Credit the fare to the driver’s account

These operations must happen together as one atomic unit.

If Transactions Are Not Used

Step Operation Risk Without Transaction

1 Rider is charged 300 BDT ✅ Money deducted
2 Driver is credited 300 BDT ❌ System crashes or loses connection

Result:

 Rider loses money

 Driver doesn’t get paid
 No record of what happened
 Creates loss of trust, manual errors, and customer complaints

When Would You Use ROLLBACK?

You would use ROLLBACK if any part of the transaction fails.
Example Scenario:
START TRANSACTION;

UPDATE Wallet
SET Balance = Balance - 300
WHERE UserID = 'RIDER123';

-- Attempt to pay driver

UPDATE Wallet
SET Balance = Balance + 300
WHERE UserID = 'DRIVER456';

-- Something goes wrong here (e.g., database error or driver account

doesn't exist)
ROLLBACK;

Why?

 To undo the rider's deduction

 Ensure the system returns to a consistent state
 Prevent partial processing

V. DATA Query Language (DQL)

DQL (Data Query Language) is a subset of SQL used to retrieve and view data from a
database. DQL helps users ask questions about data, such as:

 “Which students are in the MIS department?”

 “What are the top-selling products on Daraz?”

Main DQL Command

Comman Purpose
d
SELECT Retrieve data from a table

Basic Syntax of SELECT

SELECT column1, column2, ...
FROM table_name;

Example:
SELECT Name, Email FROM Customer;

Using WHERE Clause (Filtering)

Used to get specific rows based on a condition.
Syntax:
SELECT * FROM Product
WHERE Price > 500;

Shows only products with price over 500 taka.

Using DISTINCT (Remove Duplicates)

Example:
SELECT DISTINCT Department FROM Student;

Returns each department only once.

Using ORDER BY (Sorting Results)

Used to sort results in ascending (ASC) or descending (DESC) order.

Example:
SELECT * FROM Product
ORDER BY Price DESC;

Lists products from most expensive to least expensive.

LIMIT Clause (Top N Results)

Restricts how many rows are shown.

Example:
SELECT * FROM Orders
ORDER BY OrderDate DESC
LIMIT 5;

Shows the 5 most recent orders.

Real-Life Query Examples (Bangladesh)

Daraz – View all products over 1000 Taka:
SELECT Name, Price FROM Product
WHERE Price > 1000;

bKash – Get last 3 transactions for a customer:

SELECT TxID, Amount, TxDate FROM Transaction
WHERE SenderID = 201
ORDER BY TxDate DESC
LIMIT 3;

IUB SRMS – List all students from MIS department:

SELECT StudentID, Name FROM Student
WHERE Department = 'MIS';

Combining Conditions with AND/OR

Example:
SELECT * FROM Product
WHERE Category = 'Electronics' AND Price < 2000;

Practical Use Cases

Organization Use Case Sample Query Feature
[Link] Show available tickets for a date WHERE, ORDER BY
Chaldal List all items in grocery category SELECT, WHERE
BRAC Bank Display top 10 largest transactions this week ORDER BY, LIMIT
Unimart POS Find duplicate product categories SELECT DISTINCT

Class Activity
Given the following Student table:

StudentID Name Department CGPA

20211401 Nafi MIS 3.80
20211402 Rumi FIN 3.55
20211403 Sifat MIS 3.95

Write queries to:

1. List all students in MIS

2. Show names of students with CGPA > 3.6
3. List students ordered by CGPA (descending)
1. List all students in MIS
SELECT * FROM Student
WHERE Department = 'MIS';

Output:

StudentID Name Department CGPA

20211401 Nafi MIS 3.80
20211403 Sifat MIS 3.95

2. Show names of students with CGPA > 3.6

SELECT Name FROM Student
WHERE CGPA > 3.6;

Output:

Name
Nafi
Sifat

3. List students ordered by CGPA (descending)

SELECT * FROM Student
ORDER BY CGPA DESC;

Output:

StudentID Name Department CGPA

20211403 Sifat MIS 3.95
20211401 Nafi MIS 3.80
20211402 Rumi FIN 3.55