Introduction to SQL

Hasan Mushariar Ahmmed

mushariar@[Link]
 10/08/2025

Tutorial Outlines

 What is SQL  Tutorial sample db overview

 SQL History  Schema of the sample db
 Terminology By Examples  Data of the sample db
 SQL Syntax By Examples  Hands on Tutorial Setup
 SQL Category
 Small yet worth noting
points
 10/08/2025

What is SQL ( ‘Structured Query Language’ )?

 SQL stands for ‘Structured Query Language’

 SQL is domain-specific language, NOT a general programming language
 SQL is specialized to handle ‘structured data’ that follows relational
model – data that incorporates relations among entities and variables.
 Used to interact with relational databases to manage data: create,
populate, modify, or destroy data. Also, can manage data access.
 10/08/2025

SQL is a standard language

 Nevertheless, SQL is a ‘language’. It has its language specification – a set

of language elements, rules and syntax
 Rigid and structural:
 Since the underlying data model is structural, SQL is very ‘structural’
too - requiring rigid predefined schema as compared with those of
‘noSQL’
 Syntax and grammar is also strict
 SQL specific advance features – triggers, stored procedures/functions
 10/08/2025

History of SQL

 First developed in 1970s by two scientists at IBM following a theory of

‘relational algebra’ by Edgar F. Codd, who was also an IBM scientist.
 First commercial implementation of SQL-based RDMBS was Oracle’s V2.
 First adopted by ANSI in 1986, and ISO in 1987 as standard.
 The latest version of the SQL standard is from 2016. There have been
very many versions in between.
 Though standardized, this does not necessarily mean SQL code can be
migrated between different RDBMS seamlessly.
 10/08/2025

Objective: be able to perform the basic operation of the

 create, modify the layout of a table

 remove a table from the user schema

 insert data into the table

 retrieve and manipulate data from one or more tables

 update/ delete data in a table

+

 Some more advanced modifications

 10/08/2025

Terminology - Structure

 Database  Relation
 Table  Primary key
 Column  Foreign key
 Row
 Index
 10/08/2025

Terminology - SQL Language Elements

 Clause
 Statement  Predicate
 Query  Expression
 Function  Keyword
 Stored  Identifier
Procedure/Function
 10/08/2025

SQL Category

1. Data Definition Language (DDL) – used to define/modify database schema

2. Data Manipulation Language (DML) – used to create/modify/destroy data

3. Data Query Language (DQL) - used to query data

4. Data Control Language (DCL) – used for security and access control

** Transaction Control (Commit, Rollback)

 10/08/2025

Most Important SQL Statements

 CREATE DATABASE - create a new database (DDL)

 CREATE/ALTER TABLE - create/modify a table (DDL)

 CREATE/ALTER INDEX - create/modify a new table (DDL)

 DROP TABLE - deletes a table (DDL)

 UPDATE - updates data in a database (DML)

 DELETE - deletes data from a database (DML)

 INSERT - inserts new data into a database (DML)

 SELECT - extracts data from a database (DQL)

 10/08/2025

Datatypes

 Numeric Data Types - Integer, Float, Double, Decimal etc.

 String Data Types - Char, Varchar, Text

 Date and Time Types - Date, Time, Datetime, Timestamp

 Binary Data Type - Blob, Clob.

 10/08/2025

Data Definition Language (DDL) – used to define/modify database schema

CREATE TABLE `tutorials_tbl` (

`tutorial_id` int(11) NOT NULL,
`tutorial_title` varchar(100) NOT NULL,
`tutorial_author` varchar(40) NOT NULL,
`tutorial_pages` int(11) DEFAULT NULL,
`submission_date` date DEFAULT NULL
);

DROP TABLE tutorials_tbl ;

 10/08/2025

Constraints

PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table

FOREIGN KEY - Prevents actions that would destroy links between tables

NOT NULL - Ensures that a column cannot have a NULL value

UNIQUE - Ensures that all values in a column are different

CHECK - Ensures that the values in a column satisfies a specific condition

DEFAULT - Sets a default value for a column if no value is specified

ALTER TABLE tutorials_tbl

MODIFY COLUMN tutorial_id int(11) PRIMARY KEY AUTO_INCREMENT;
 10/08/2025

Data Manipulation Language (DML) – used to create/modify/destroy data

INSERT INTO creati86_etakeawaydb.tutorials_tbl

(tutorial_title,tutorial_author,tutorial_pages,submission_date) VALUES
('Learn PHP','John Poul',190,'2025-08-10');

INSERT INTO creati86_etakeawaydb.tutorials_tbl

(tutorial_title,tutorial_author,tutorial_pages,submission_date) VALUES
('Learn Javascript','John Poul',234,'2025-01-10'),
('Learn HTML','John Poul',35,'2025-03-19'),
('Learn MySQL','Mushariar Ahmmed',117,'2025-08-11'),
('Learn OraclePLSQL','Mushariar Ahmmed',350,'2025-04-12'),
('Learn T-SQL','Mushariar Ahmmed',170,'2025-01-15'),
('Learn PostgreSQL','Mushariar Ahmmed',205,'2025-08-03'),
('JAVA Tutorial','Jason S',75,'2025-08-04'),
('C# Tutorial','George',110,'2007-05-06'),
('AI Tutorial', NULL, 500, NOW());
 10/08/2025

Create Table

CREATE TABLE `authors` (

`author_id` int NOT NULL AUTO_INCREMENT,
`author_name` varchar(250) NOT NULL,
`author_email` varchar(250) DEFAULT NULL,
`author_country` varchar(250) DEFAULT NULL,
`author_dob` date,
PRIMARY KEY (`author_id`)
);

INSERT INTO creati86_etakeawaydb.authors

(author_name,author_email,author_country,author_dob) VALUES
('John Poul','[Link]@[Link]','England','1980-06-30'),
('Jason S',NULL,'USA','1998-06-15'),
('George',NULL,'England','1995-12-25'),
('Mushariar Ahmmed','mushariar@[Link]','England','1985-01-01'),
('Jenifar G',NULL,'England','2000-04-20');
 10/08/2025

Foreign Key

Alter Table tutorials_tbl Add Column `tutorial_author_id` int after

`tutorial_author` ;

ALTER TABLE tutorials_tbl

ADD FOREIGN KEY (tutorial_author) REFERENCES authors(author_id);

Update tutorials_tbl set tutorial_author_id = 2 Where tutorial_author = 'Jason S';

Update tutorials_tbl set `tutorial_author_id` = 1 Where `tutorial_author` = 'John
Poul';
Update tutorials_tbl set tutorial_author_id = 3 Where tutorial_author = 'George';
Update tutorials_tbl set tutorial_author_id = 4 Where tutorial_author = 'Mushariar
Ahmmed';

ALTER TABLE tutorials_tbl DROP COLUMN tutorial_author;

 10/08/2025

Attention Please !
1. SQL keywords and table/column names are NOT case sensitive: ‘select’ and ‘SELECT’ are the
same

2. The values stored in a table can be case-sensitive – depending on configuration

3. Semicolon ‘;’ is the standard way to separate SQL statements. It can be required in some
DBMS to end each statement with a ‘;’ even after a single statement

4. Comments can be used to make SQL more readable. Usually ‘#’ for single line comment, and
‘/*’ and ’*/’ for multiline comments. Add ‘--’ at the beginning to indicate a comment line

5. Standard is NOT STANDARD – none of SQL standard is fully implemented by all vendors. Pay
attention to the differences that each vendor’s implementation have from the SQL ‘standard’
 10/08/2025

Retrieve Query

SELECT Columns -- SELECT clause

FROM Table -- FROM clause

select * from authors;

select * from tutorials_tbl;

SELECT tutorial_id, tutorial_title, tutorial_author_id, tutorial_pages, submission_date

FROM tutorials_tbl t;

SELECT author_id, author_name, author_email, author_country, author_dob

FROM authors a;
 10/08/2025

Create Table

SELECT Columns -- SELECT clause

FROM Table -- FROM clause
WHERE Id=1 – WHERE Clause

SELECT tutorial_id, tutorial_title, tutorial_author_id,

tutorial_pages, submission_date
FROM tutorials_tbl t
where t.tutorial_id = 1;;

SELECT author_id, author_name, author_email, author_country,

author_dob
FROM authors a
WHERE a.author_name = 'Mushariar Ahmmed';
 10/08/2025

Group Functions

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

SELECT GroupFunction SELECT Columns, GroupFunction

FROM Table FROM Table -- FROM clause
WHERE Clause [Optional]
GROUP BY Columns
 10/08/2025

SQL Joins

 INNER JOIN

 LEFT [OUTER] JOIN

 RIGHT [OUTER] JOIN

 FULL JOIN (Not available in MySQL)

 10/08/2025

SELECT t.tutorial_id, t.tutorial_title, t.tutorial_pages,

t.submission_date, a.author_name , a.author_country
From tutorials_tbl t
INNER JOIN authors a ON t.tutorial_author_id = a.author_id ;

SELECT t.tutorial_id, t.tutorial_title, t.tutorial_pages,

t.submission_date, a.author_name , a.author_country
From tutorials_tbl t, authors a
Where t.tutorial_author_id = a.author_id ;
 10/08/2025

SELECT t.tutorial_id, t.tutorial_title, t.tutorial_pages, t.submission_date,

a.author_name , a.author_country
From tutorials_tbl t
LEFT JOIN authors a ON t.tutorial_author_id = a.author_id ;
 10/08/2025

SELECT t.tutorial_id, t.tutorial_title, t.tutorial_pages, t.submission_date,

a.author_name , a.author_country
From tutorials_tbl t
RIGHT JOIN authors a ON t.tutorial_author_id = a.author_id ;
 10/08/2025

SELECT t.tutorial_id, t.tutorial_title, t.tutorial_pages,

t.submission_date, a.author_name , a.author_country
From tutorials_tbl t
LEFT JOIN authors a ON t.tutorial_author_id = a.author_id
UNION
SELECT t.tutorial_id, t.tutorial_title, t.tutorial_pages,
t.submission_date, a.author_name , a.author_country
From tutorials_tbl t
RIGHT JOIN authors a ON t.tutorial_author_id = a.author_id ;
 10/08/2025

ER Diagram for a Fast Food Restaurant Database

 10/08/2025

Customer Table
 10/08/2025

Get active menus

SELECT [Link], [Link], [Link], [Link],

[Link], [Link], [Link], [Link],
[Link], [Link]
FROM ItemCategory c, Items i, ItemPrice p
WHERE [Link] = [Link]
AND [Link] = [Link]
AND [Link] = 1
And [Link] = 1
And [Link] = 1
Order BY [Link], [Link], [Link];
 10/08/2025

Get Orders within a date range

SELECT [Link], [Link], FORMAT([Link], 'dd/MM/yyyy HH:mm') AS
OrderDateTime, ROUND([Link], 2) as TotalAmount,
[Link], Count([Link]) AS ItemCount,
(Case
When [Link] = 'C' then 'Collection'
When [Link] = 'D' then 'Delivery'
Else 'Store'
End) As ServiceChannelName
#get_service_channel([Link])
From salesorder so
Left Join salesitems si On [Link] = [Link]
Where
[Link] >= STR_TO_DATE('14-12-2024', '%d-%m-%Y')
AND [Link] < DATE_ADD(STR_TO_DATE('17-12-2024', '%d-%m-%Y'),
INTERVAL 1 DAY)
Group By [Link],
[Link], [Link], [Link], [Link],
[Link], [Link], [Link], [Link], [Link]
ORDER BY
[Link] DESC;
 10/08/2025

Stored Function

FUNCTION `get_service_channel`(ChannelCode varchar(1)) RETURNS

varchar(10) CHARSET utf8mb4 COLLATE utf8mb4_general_ci
DETERMINISTIC
BEGIN
DECLARE ServiceChannelName varchar(10);
Set ServiceChannelName = (Case When ChannelCode = 'C' then
'Collection'
When ChannelCode = 'D' then 'Delivery'
Else 'Store' End);
RETURN ServiceChannelName;
END
 10/08/2025

Stored Procedure
BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
ROLLBACK;
END;

START TRANSACTION;

INSERT INTO salesorder_arch

SELECT * FROM salesorder WHERE DATE(OrderDateTime) < DATE_SUB(CURDATE(), INTERVAL 8 DAY);

INSERT INTO salesitems_arch

SELECT * FROM salesitems
WHERE EXISTS (SELECT 1 FROM salesorder
WHERE [Link] = [Link]
AND DATE(OrderDateTime) < DATE_SUB(CURDATE(), INTERVAL 8 DAY));

INSERT INTO salescustomizations_arch

SELECT * FROM salescustomizations WHERE EXISTS
(
SELECT 1 FROM salesitems
WHERE [Link] = [Link]
AND EXISTS (SELECT 1 FROM salesorder
WHERE [Link] = [Link]
AND DATE(OrderDateTime) < DATE_SUB(CURDATE(), INTERVAL 8 DAY))
);
 10/08/2025

Stored Procedure
DELETE FROM salescustomizations WHERE EXISTS
(
SELECT 1 FROM salesitems
WHERE [Link] = [Link]
AND EXISTS (SELECT 1 FROM salesorder
WHERE [Link] = [Link]
AND DATE(OrderDateTime) < DATE_SUB(CURDATE(), INTERVAL 8 DAY))
);

DELETE FROM salesitems

WHERE EXISTS (SELECT 1 FROM salesorder
WHERE [Link] = [Link]
AND DATE(OrderDateTime) < DATE_SUB(CURDATE(), INTERVAL 8 DAY));

DELETE FROM salesorder WHERE DATE(OrderDateTime) < DATE_SUB(CURDATE(), INTERVAL 8 DAY);

/*COMMIT;*/

END;
 10/08/2025

Trigger

CREATE TRIGGER `TrgOrderInsert` AFTER INSERT ON `salesorder`

FOR EACH ROW
BEGIN
INSERT INTO daily_earn_register (register_date, amount)
VALUES (CURDATE(), [Link]);
END;

CREATE TRIGGER `TrgOrderDelete` AFTER DELETE ON `salesorder`

FOR EACH ROW
BEGIN
UPDATE daily_earn_register
SET amount = amount - [Link]
Where register_date = CURDATE();
END;
 10/08/2025

Trigger
CREATE TRIGGER `TrgOrderUpdate` AFTER UPDATE ON `salesorder`
FOR EACH ROW BEGIN
declare isExists INTEGER;

If [Link] <> [Link] Then

Select Count(1) into isExists
From daily_earn_register
Where register_date = CURDATE();
If isExists > 0 Then
UPDATE daily_earn_register
SET amount = amount - [Link] + [Link]
Where register_date = CURDATE();
Else
INSERT INTO daily_earn_register (register_date, amount)
Values (CURDATE(), [Link]);
End If;
End If;
END;
 10/08/2025

Table Partitioning

ALTER TABLE salesorder_arch PARTITION BY RANGE (Month(OrderDateTime))

(
PARTITION p_Apr VALUES LESS THAN (TO_DAYS('2024-05-01')),
PARTITION p_May VALUES LESS THAN (TO_DAYS('2024-06-01')),
PARTITION p_Jun VALUES LESS THAN (TO_DAYS('2024-07-01')),
PARTITION p_Jul VALUES LESS THAN (TO_DAYS('2024-08-01')),
PARTITION p_Aug VALUES LESS THAN (TO_DAYS('2024-09-01')),
PARTITION p_Sep VALUES LESS THAN (TO_DAYS('2024-10-01')),
PARTITION p_Oct VALUES LESS THAN (TO_DAYS('2024-11-01')),
PARTITION p_Nov VALUES LESS THAN (TO_DAYS('2024-12-01')),
PARTITION p_Dec VALUES LESS THAN MAXVALUE );
 10/08/2025

GUI tool is not the only way!

A GUI tool like DBeaver is not the only way to access databases!

There could be many other ways!

 10/08/2025

Thank You
Any Questions???