Structured

Query
Language
(SQL)

Workshop-1
 Introduce Yourself
Today’s What is SQL?

Workshop Various SQL Queries

Practice Exercises
 A database is an organized collection of structured
What is information, or data, typically stored electronically in
a computer system.

Database? A database is usually controlled by a

management system (DBMS). ([Link])
database

Data is usually stored in a table in form of rows and

columns.
DBMS is a software that allows to design, update,
retrieve, and manage databases.
DBMS is an interface between the user and the database
E.g. Oracle, MySQL,Server, MS Access, Oracle

Databases use SQL to query data

Student_ID Student_Name Course Enrollment_date
101 Alex [Link] Data Analytics 1st September 2022
102 Bobby [Link] Data Analytics 21st September 2022
 EF Codd
RDBMS Relational DBMS
 • Structured Programming language
SQL • Developed by IBM in 1970s
• Used by Relational Database Management
System (RDBMS) to define, query and
manipulate data.
• Simple
• Easy to learn
• SQL can execute queries
- Retrieve data
- Insert data
- Update data
- Delete data
CRUD Create - Insert
Operations Retrieve - Select
Update
Delete
Data Definition • DDL
• syntax for creating and modifying
Language tables
(DDL), • Create a table - CREATE

Data • Change the data type of the records -

ALTER
Manipulation • Removes a table – DROP
Language
(DML) • DML
• Adding, deleting and modifying
data
• Add records to a table - INSERT
Data Query • DQL

Language (DQL), • Query to fetch information

• Retrieve data - SELECT
Data Control
Language (DCL)
• DCL
• Control access to data in
DB
• Give privilege - Grant
• Remove privilege - Revoke
Reflection What is a database?
What is the use of DBMS?
Spot How is data stored in RDBMS?
What is SQL?
What are different category
of queries can SQL execute?
Analytical Questions Mont Year Sales Store location
that you would like to h
Jan 2022 15000 Bolton
answer using SQL 0

queries Feb 2022 13000

0
Bolton

Jan 2022 14000 Manchester

In which months of the year are there more sales in a0
Feb 2022 14500 Manchester
particular store location? 0

Which kind of job roles are most often taken by [Link].

Data Analytics students?
Name Course Company placed Job role
Alex [Link]. Data Globallogic Data Analyst
Analytics
Aline [Link]. Data Amazon Data Scientist
SQL Queries
Student_ID Student_Name Course Enrollment_date
101 Alex [Link] Data Analytics & 1st September 2022
Tech.
102 Bobby [Link] Data Analytics & 21st September 2022
Tech
2011 Alina [Link]. Software 15th September 2022
Engineering
3016 Bren [Link]. Cloud & Network 1st September 2022
Security
100 Graham [Link] Data Analytics & 1st September 2022
Tech.
81 Charlie [Link] Data Analytics & 11th September 2022
Tech.
2010 Carol [Link]. Software 15th September 2022
Engineering
2011 Chris [Link]. Software 16th September 2022
Engineering
 • CREATE DATABASE databasena
Create me;
database • create database Demo;
• use Demo;
 Create table
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....);

create table Student(Student_ID int, Student_name varchar(50),

Course varchar(50), Enrollment_date date);
 MySQL uses many different data types broken into three
categories −

Data Types • Numeric

• Date and Time

in MySQL • String Types.

Numeric
• Int
• Decimal(p,d) eg Decimal(4,2)
Date and Time
• Date
• Datetime
• Time
• Timestamp

For detailed information on data types please see:

[Link]
 Data Types
in MySQL String
• Char(M)
• Varchar(M)

For detailed information on data types please see:

[Link]
 BLOB- Binary Large Object
BLOB • Upto 4 GB of binary data

CLOB • Phots, videos, audio

CLOB- Character Large Object

• Upto 4GB of character data
• HTML Webpages, text book
 -Adding rows/records to a table
- INSERT INTO table_name (column1, column2,
INSERT INTO ...) VALUES (value1, value2, value3, ...);
Statement
insert into Student(Student_ID, Student_name, Course,
Enrollment_date) values (101, 'Alex', '[Link] Data Analytics &
Tech.', '2022-09-01’);
insert into Student(Student_ID, Student_name, Course,
Enrollment_date) values (102,
Student_ 'Bobby',
Student_Na '[Link] Data Analytics
Course &
Enrollment_dat

Tech.', '2022-09-21’);ID
101
me
Alex [Link] Data Analytics & Tech.
e
2022-09-01
102 Bobby [Link] Data Analytics & Tech 2022-09-21
2011 Alina [Link]. Software Engineering 2022-09-15
3016 Bren [Link]. Cloud & Network 2022-09-01
Security
100 Graham [Link] Data Analytics & Tech. 2022-09-01
81 Charlie [Link] Data Analytics & Tech. 2022-09-11
2010 Carol [Link]. Software Engineering 2022-09-15
Select Select command is used to retrieve
data from the table
statement select * from table_name;
select * from Student;
Student_ Student_Na Course Enrollment_dat
ID me e
101 Alex [Link] Data Analytics & Tech. 2022-09-01
102 Bobby [Link] Data Analytics & Tech 2022-09-21
2011 Alina [Link]. Software Engineering 2022-09-15
3016 Bren [Link]. Cloud & Network 2022-09-01
Security
100 Graham [Link] Data Analytics & Tech. 2022-09-01
81 Charlie [Link] Data Analytics & Tech. 2022-09-11
2010 Carol [Link]. Software Engineering 2022-09-15
 Student_Na Enrollment_
me date
Alex 2022-09-01

Select Bobby
Alina
2022-09-21
2022-09-15

Statement Bren
Graham
2022-09-01
2022-09-01
Charlie 2022-09-11
Carol 2022-09-15
Chris 2022-09-16

SELECT column1, column2,… FROM tablename;

select Student_name, Enrollment_date from
Student;
Reflection
Spot
• Steps to Create a table
• Query to add elements to a table
• Query to retrieve elements from a table
Select
Distinct
statement
Returns only different values
Select DISTINCT column1, column2,… from tablename;
select distinct course from Student;
 Select
COUNT
(DISTINCT)
Returns the number of different values
SELECT COUNT(DISTINCT) column1, column2,… from
tablename;
select count(distinct course) from Student;
#Returns the number of courses that have student enrollments
 WHERE
clause
Used to filter records
SELECT column1, column2,… from tablename WHERE condition;
select * from Student where Course='[Link] Data Analytics &
Tech.’;

select Student_name from Student where Course='[Link] Data Analytics &

Tech.';
 Operat Description
or

AND Returns only those records

where both the conditions are
WHERE clause met
with multiple OR Returns all those records where
anyone or both the conditions
conditions are met
NOT Returns records where condition
is not met
select * from Student where Course='[Link] Data Analytics & Tech.' and
Enrollment_date='2022-09-01’;
Try OR and
NOT

select Student_name from Student where Course='[Link] Data

Analytics & Tech.' and Enrollment_date='2022-09-01’;
 Operat Description
or

AND Returns only those records

where both the conditions
WHERE clause are met
with multiple OR Returns all those records
where anyone or both the
conditions conditions are met
NOT Returns records where
condition is not met

select * from Student where Course = '[Link] Data Analytics & Tech.' AND
(Student_ID=101 OR Enrollment_date='2022-09-01');
 Operator Description
s
= Equal to
WHERE Clause > Greater than
>= Greater than equal to
select * from Student where Enrollment_date
between '2022-09-16' and '2022-09-21'; < Less than
<= Less than equal to

!= Not Equal to
<>
select * from Student where Student_name
BETWEEN Between a certain
like 'A%'; range
LIKE Search for a pattern
select * from Student where Course IN ('[Link]
Data Analytics & Tech.', '[Link] Software IN To specify multiple
values in a column
Engineering’);
([Link])
 LIKE Operator Description

WHERE CustomerName Finds any values that start

Like
LIKE 'a%' with "a"
WHERE CustomerName Finds any values that end
LIKE '%a' with "a"

Operator WHERE CustomerName

LIKE '%or%'
Finds any values that have
"or" in any position
WHERE CustomerName Finds any values that have
LIKE '_r%' "r" in the second position
• The percent sign (%) represents zero,
one, or multiple characters WHERE CustomerName Finds any values that start
LIKE 'a_%' with "a" and are at least 2
• The underscore sign (_) represents one, characters in length
single character WHERE CustomerName Finds any values that start
SELECT column1, column2, ... LIKE 'a__%' with "a" and are at least 3
FROM table_name
characters in length
WHERE columnN LIKE pattern; WHERE ContactName Finds any values that start
LIKE 'a%o' with "a" and ends with
"o"

[Link]
 IN Operator
• Used with multiple OR conditions in where clause
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

SELECT * FROM Student

WHERE Course IN (‘[Link] Data Analytics & Tech’, ‘[Link] Software Engineering’);
ORDER BY
- Sorts records in ascending or descending order
- SELECT column1, column2, ... FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
- select * from Student order by Enrollment_date;
- select * from Student order by Enrollment_date DESC;
UPDATE statement
-used to modify a record/row
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
update Student set Enrollment_date='2022-09-02' where
Student_ID=101;

Error 1175? SET SQL_SAFE_UPDATES =

0;
Student_ID Student_Nam Course Enrollment_da
e te
101 Alex [Link] Data 1st September
Analytics & 2022
Tech.
102 Bobby [Link] Data 21st September
Analytics & 2022
Tech
2011 Alina [Link]. Software 15th September
Engineering 2022
3016 Bren [Link]. Cloud & 1st September
Network 2022
Security
100 Graham [Link] Data 1st September
Analytics & 2022
Tech.
81 Charlie [Link] Data 11th
Analytics & September
Tech. 2022
2010 Carol [Link]. Software 15thNULL
September Missing
Engineering 2022 Values
2011 Chris [Link]. Software 16th September
Engineering 2022
 NULL
VALUES
To check if the column has a record or not
I

INSERT into Student(Student_ID, Student_name, Course,

Enrollment_date) values (2010, 'Carol', '[Link] Software
• IS NULL Engineering', null);
select * from Student
• NOT NULL
SELECT column_names
FROM table_name select * from Student where Enrollment_date is null;

WHERE column_name IS NULL;

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
 -delete the record from the table
DELETE DELETE FROM table_name WHERE condition;

statement delete from Student where

Enrollment_date IS NULL;
select * from Student;

-to delete all records Use with

Caution: All
- DELETE FROM Students records will
be deleted
 A client asks you create a database with multiple tables. Currently, he asks
to create a table with the following columns and rows

Practice Student_ID Country Arrival_date Marks

Question A101

A102
USA

France
31st August
2022
31st August
98

87
2022
A103 India 30th August 90
2022
He then asks to retrieve the following information:
(a) the students
A104who arrivedPakistan
on 30th August
30th2022.
August 89
HINT (b) the student who arrived in August. 2022
(c ) countries from which the
A105 student belong
India to
5th September 78
(d) For the student with ID A105, change2022the country to Srilanka
select * from Student (e) Arrange the table according to Arrival date
where (f) Get the details of top three students
day(Enrollment_date)=
01;
 SELECT TOP
-returns the number of specified records
- SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;
- SELECT * FROM Student
LIMIT 3;
- SELECT * FROM Student
WHERE Enrollment_date=‘2022-09-01'
LIMIT 5;
MIN() and -returns minimum and maximum
value from the column
MAX()
SELECT MIN(column_name)
FROM table_name
WHERE condition;

SELECT MAX(column_name)
FROM table_name
WHERE condition;
COUNT(), - Count returns the number of
rows
AVG(),
SUM() - SELECT COUNT(column_name
)
FROM table_name
WHERE condition;

- SELECT AVG(column_name)
FROM table_name
WHERE condition;

- SELECT SUM(column_name)
 SQL Aliases
-give alternative name to a table or a column in the table
- Use of AS keyword
- SELECT column_name AS alias_name
FROM table_name;
- SELECT column_name(s)
FROM table_name AS alias_name;
- SELECT Student_ID AS id, Student_name AS name
FROM Student;
- SELECT name, CONCAT(Address,', ',PostalCode,',
',City,', ,’Country’) AS Address FROM Employees;
Workshop-2
ALTER table -used to add, delete, or modify columns in an
existing table
-used to add and drop various constraints on an
ADD existing table.

<syntax>
-ALTER TABLE table_name
ADD column_name datatype;

<Example>
- alter table Student add Grade int;

- explain Student;
- Select * from Student;
 To delete a column in a table, use the
ALTER table following syntax

ALTER TABLE table_name

DROP COLUMN column_name;
DROP
alter table Student drop Grade;
Select * from Student;
 - Change the data type of a
ALTER table column

ALTER TABLE table_name

MODIFY COLUMN column_name
MODIFY datatype;

COLUMN
ALTER TABLE Student
MODIFY COLUMN Student_Id
VARCHAR(12);

EXPLAIN Student;
DROP database
DROP table
TRUNCATE table

DROP Table will delete the table

TRUNCATE table will delete records only not table
Difference between DELETE table and DROP table
Difference between DELETE table and TRUNCATE table
 • groups rows that have the same values into summary

Group By •
rows
Used with aggregate functions (count(), max(), avg(),
min(), sum()) to group the result by one or more columns

<Syntax>
- SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

<Example>
- SELECT Enrollment_date, COUNT(Student_ID) FROM
Student GROUP BY Enrollment_date;
 • used with GROUP BY
Having • Similar to where clause

Clause
<Syntax>
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

SELECT Enrollment_date,
COUNT(Student_ID) FROM Student
GROUP BY Enrollment_date HAVING
Enrollment_date > '2022-09-15';
 IN Operator
SUBQUERIES:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

SELECT Student_name, Course FROM Student WHERE Student_ID

IN (SELECT Student_ID FROM sports WHERE
age<=25);
CREATE TABLE sports(Student_ID int, Age int, Weight decimal(4,2));
INSERT INTO sports(Student_ID, Age, Weight) VALUES (101, 25, 76.2);
INSERT INTO sports(Student_ID, Age, Weight) VALUES (102, 25, 56.2);
INSERT INTO sports(Student_ID, Age, Weight) VALUES (2013, 27, 58);
INSERT INTO sports(Student_ID, Age, Weight) VALUES (2010, 35, 70);

SELECT * FROM sports;

 - Connects rows of two or more
Joins tables based the common columns
- Different types of Joins
Student - Inner Join
- Left Join
Student_I Student_na Course Enrollment_da
D me te - Right Join
… … … … - Full Join
sports - Self Join
Student_ID Age Weight
… … …

Student
Student_I Student_na Cours Enrollment_da Age Weig
D me e te ht

… … … … … …
INNER JOIN

Inner Join selects records that have matching

values in both tables.
<Syntax>
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

<Example>
select Student.Student_ID, Student_name,
Age, Weight from Student inner join sports
ON Student.Student_ID=sports.Student_ID;
select * from Student inner join sports ON
Student.Student_ID=sports.Student_ID;

[Link]
Left Join

-returns all records from the left table (table1),

and the matching records from the right table
(table2). The result is 0 records from the right
side, if there is no match
<Syntax>
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_n
ame;
<Example>
select Student.Student_ID,
Student_name, Age, Weight from Student
left join sports ON
Student.Student_ID=sports.Student_ID;
[Link]
Right Join
- returns all records from the right table
(table2), and the matching records from the
left table (table1). The result is 0 records
from the left side, if there is no match.
<syntax>
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_nam
e;
<Example>
- select Student.Student_ID,
Student_name, Age, Weight from
Student right join sports ON
Student.Student_ID=sports.Student_ID;

[Link]
FULL OUTER JOIN
returns all records when there is a match in
left (table1) or right (table2) table records.
<Syntax>
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_n
ame
WHERE condition;

Not Supported by MySQL

[Link]
 regular join, but the table is joined with itself.
Self Join SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

[Link]

[Link]
 [Link]
Deleting v=w9dwX7xsBgY

Duplicate
Rows
Constraints -to uniquely identify a row

Primary Key
• E.g. NIN, SSN, student number
• not null
• unique
• no duplicates
• Primary key cannot be changed
Student_ID Primary key (Immutable)
Name NOT NULL ALTER TABLE Student MODIFY COLUMN Student_Id
varchar(12) PRIMARY KEY;
Course NOT NULL
Enrollment_d NOT NULL ALTER TABLE Student
ate ADD PRIMARY KEY (Student_ID);

Create table emp(id int PRIMARY KEY, name varchar(50), designation

varchar(50), salary int);
explain emp;
select * from emp;
 Primary key of one table is foreign for
Foreign Key another

CREATE TABLE emp_details (id int,

address varchar(200), Foreign key (id)
References emp(id));

insert into emp(id, name, designation,

salary) values (1,'eww','mgr', 50000);
insert into emp_details(id, address) values
(11,'ewgjhgw');