1

SDF II(15B11CI211)
EVEN Semester 2021

2nd Semester , First Year

Jaypee Institute Of Information Technology (JIIT), Noida
 2

Structured Query Language

 3

Contents
• Data Definition Language (DDL)
• SQL Commands: Alter, Drop
• Data Manipulation Language (DML)
• SQL Commands : Insert, Update, Delete, Select
• Comparison Operators: IN, BETWEEN, LIKE
• Aggregate Functions: Max, Min, Sum, Count.
• SQL Clauses: GROUP BY, ORDER BY
• Practice Exercise
 4

Data Definition Language

• A data definition language (DDL) is a language used to create and

modify the structure of database objects in a database.

• SQL commands that can be used to define the database schema are
Create, Alter and Drop.
 5

Alter Command
• Alter Command is used to add or delete columns from the existing table
• Syntax:
To add a column in the existing table:
ALTER TABLE table_name
Add <Column_name> <type>(width);

To remove a column in the existing table:

ALTER TABLE table_name
Drop Column <Column_name>;

• .
 6

• Alter Command can also used to modify the existing columns

Syntax:
ALTER TABLE table_name
MODIFY COLUMN column_name <type> (width);
Example --Table: Persons
ID LastName FirstName Address City
7
1 Hansen Ola Time 20 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

ALTER TABLE Persons ADD DateOfBirth

date;
ID LastName FirstName Address City DateOfBirth
1 Hansen Ola Time 20 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

ALTER TABLE Persons

DROP COLUMN DateOfBirth;
ID LastName FirstName Address City
1 Hansen Ola Time 20 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
 8

Drop Command

• The Drop Command is used to delete the entire table along with
its contents .
• Syntax:
• DROP TABLE table_name;
• Example
• Drop Table Amazoncart;
 9

Data Manipulation Language (DML)

• Data manipulation language (DML) is a language used for

inserting, deleting, and modifying data in a database.
• SQL commands that deals with the manipulation of data are
• Insert,
• Update,
• Delete,
• Select
 10

The INSERT INTO command

• The INSERT INTO statement is used to insert new rows into a table.
Syntax:
INSERT INTO table_name
VALUES (value1, value2,……)
it can also be written as
INSERT INTO table_name(column1, column2…
VALUES (value1, value2,……)
Inserting into a table
• Note : While Insertion of String type data must be enclosed in a
single quotes and Numbers type date are not quoted.
Insert into emp (empName, Deptno, gender, salary) values
(‘johns’, 22, ‘M’, 40000);
• The above given statement can also be written as
Insert into emp Values (‘Suzy’, 11, ‘F’, 12000);
Here we have omitted the column names.
 The INSERT INTO command
• To insert a record into a table, it is necessary to have a value for each
attribute, and order matters.
Example:
INSERT into AmazonCart
VALUES (’02/27/01', ‘Apparels', 300 );
AmazonCart
date Items sold
date Items Qtysold
02/25/01 books 200
02/25/01 books 200
02/26/01 electronics 400
02/26/01 electronics 400
02/27/01 Apparels 300
 UPDATE Statement
Update Command is used to change or modify the contents of the table:
Syntax:
UPDATE <table name> SET <attr> = <value>
WHERE <selection condition>;
Example:
UPDATE Amazoncart SET Qtysold = 349
WHERE date = ’02/27/01’ AND food = ‘Apparels’;
Amazoncart
date food sold date food sold

02/25/01 books 200 02/25/01 books 200

02/26/01 Apparels 400 02/26/01 Apparels 400

02/27/01 Apparels 300 02/27/01 Apparels 349

 DELETE Statement
Delete Command is used to remove/delete records from the table:
Syntax:
DELETE FROM <table name>
WHERE <condition>;
Example:
DELETE FROM Amazoncart
WHERE Items = ‘books’;
Amazoncart
date Items sold
date Items Qtysold
02/25/01 books 200
02/26/01 Apparels 400
02/26/01 Apparels 400
02/27/01 Apparels 300
02/27/01 Apparels 300
 15

If the WHERE clause is omitted all the records are deleted from the
table.
• Example:
• DELETE FROM Amazoncart;
 SELECT Statement
Select Command is used to retrieve the required set of records from the table.
A basic SELECT statement includes 3 clauses

SELECT <attribute name> FROM <tables> WHERE <condition>

SELECT: Specifies the attributes that are part of the resulting relation.
FROM: Specifies the tables that serve as the input to the statement.
WHERE: Specifies the condition based on which records are retrieved.
SELECT Statement (cont.)
❖Using asterisk “ * “ in a select statement indicates that every
attribute of the input table is to be selected.
Example: SELECT * FROM table_name WHERE
condition;

❖To get unique records, DISTINCT keyword is used.

Example: SELECT DISTINCT column_name FROM

table_name WHERE condition;
Example:
Chocolates
Name Price qty
Chocomini 120 20
Dairy Milk 100 55
Cadbury 550 70
Perk 210 50
Kitkat 99 10

Q1: Find the chocolates having quantity more than 30

SELECT * FROM Chocolates WHERE qty> 30;

Name Price qty

Dairy Milk 100 55
Cadbury 550 70
Perk 210 50
 Example:
Q2: Find unique quantity values from the table Chocolates
SELECT distinct qty Name Price qty
FROM Chocolates; Chocomini 120 20
Qty Dairy Milk 100 50
20 Cadbury 550 70
50 Perk 210 50
70 Kitkat 99 80
80

Q3: Display the chocolate names having price more than 100
SELECT Name FROM Chocolates WHERE price > 100;

Name
Chocomini
Cadbury
Perk
Comparison Operators

• expr IN ( value1, value2, value3)

• expr BETWEEN value1 AND value2
• expr LIKE "%_"
 21

IN Operator

• IN operator is used to check whether an attribute contain one or

more specified values
• Syntax:
column_name IN ( value1, value2, value3)
 22
• Table: Chocolates Name Price qty
Chocomini 120 20
Dairy Milk 100 50
Cadbury 550 70
Perk 210 50
Kitkat 99 80

Query 1: SELECT * FROM chocolates WHERE Name IN (‘Perk',

Name Price Qty
‘Kitkat’);
Perk 210 50
Kitkat 99 80

Query 2: SELECT * FROM chocolates

Name Price WHERE
Qty Name NOT IN
(‘Perk', ‘Kitkat’);
Chocomini 120 20
Dairy Milk 100 50
Cadbury 150 70
 23

Between Operator

• It is used to fetch the data lying within specified range

• Syntax:
column_name BETWEEN value1 AND value2
 24
• Table: Chocolates
Name Price qty
Chocomini 120 20
Dairy Milk 100 50
Cadbury 550 70
Perk 210 50
Kitkat 99 80

Query 1: SELECT * FROM chocolates WHERE price BETWEEN

400 AND 600;
Name Price Qty
Cadbury 550 70
 25

LIKE Operator

• Like Operator is used for string comparison.

• Like Operator uses patterns that are described using two special
characters:
• Percent(%): Matches any substring.
• Underscore(_): Matches any Character.
• Syntax: columnname LIKE "%_"
 26
• Table: Chocolates
Name Price qty
Chocomini 120 20
Dairy Milk 100 50
Cadbury 550 70
Perk 210 50
Kitkat 99 80

Query 1: SELECT * FROM chocolates WHERE Name LIKE ‘C

%”
Name Price Qty
Chocomini 120 20
Cadbury 550 70
 27
• Table: Chocolates
Name Price qty
Chocomini 120 20
Dairy Milk 100 50
Cadbury 550 70
Perk 210 50
Kitkat 99 80

Query 2: SELECT * FROM chocolates WHERE Name LIKE ‘_er

%’;
Name Price Qty
Perk 210 50
 Aggregate Functions

Aggregate functions are used to provide summarization of information which

return a single value.
• COUNT(columnname)
• SUM(columnname)
• MAX(columnname)
• MIN(columnname)
• AVG(columnname)

Note: when using aggregate functions, NULL values are not considered,
except in COUNT(*) .
 Aggregate Functions (cont.)
Chocolates
Name Price Qty
Chocomini 120 20
Dairy Milk 100 50
Cadbury 150 70
Perk 210 50
Kitkat 99 80

• COUNT(Colname) -> return # of rows that are not null

Example: Select COUNT(distinct Qty) from Chocolates; (4)

• SUM(Colname) -> return the sum of values

Example: Select SUM(Qty) from Chocolates; (270)

•
 Aggregate Functions (cont.)
Chocolates Name Price Qty
Chocomini 120 20
Dairy Milk 100 50
Cadbury 150 70
Perk 210 50
Kitkat 99 80
MAX(Colname) -> return the highest value from the selected column
Example: Select MAX(price) from Chocolates; (550)

•MIN(Colname) -> return the lowest value from the selected column
Example: Select MIN(Qty) from Chocolates; (20)

•AVG(Colname) -> return the average value from the selected column
Example: Select AVG(price) from Chocolate;
SQL Clauses

• Group by
• Order by
The GROUP BY Clause

• Sometimes, rather than retrieving individual records, you want to

know something about a group of records.
• GROUP BY Clause is used to collect data from multiple records
and group the result by one or more column.
• We use aggregate functions like COUNT, SUM, MIN, MAX, AVG in
this clause.
The GROUP BY Clause
 The GROUP BY Clause
• The function to divide the records into groups and returns an aggregate for
each group.

Date Name Qtyrcd

Chocstock
01/10/02 Kitkat 20
• Usually, it is an aggregate function’s companion 01/15/02 Dairy Milk 50
SELECT Name, sum(Qtyrcd) as availableqty 01/20/02 Dairy Milk 70

FROM Chocstock 01/20/02 Perk 50

Name availableqty
group by Name; 100 01/25/02 Kitkat 80
Kitkat
Dairy Milk 120
Perk 50
 The GROUP BY with HAVING Clause
• We can analyze the grouped data further by using the HAVING
clause.
• The HAVING clause is a filter that acts similar to a WHERE clause,
but on groups of rows rather than on individual rows.
 The GROUP BY with HAVING Clause
• The function to divide the records into groups and returns an aggregate for
each group based on given condition .

Date Name Qtyrcd

Chocstock
01/10/02 Kitkat 20
• Usually, it is an aggregate function’s companion 01/15/02 Dairy Milk 50
SELECT Name, sum(Qtyrcd) as availableqty 01/20/02 Dairy Milk 70

FROM Chocstock 01/20/02 Perk 50

group by Name 01/25/02 Kitkat 80

havingName
sum(Qtyrcd) > 100;
availableqty
Dairy Milk 120
 The ORDER BY Clause
• The ORDER BY Clause is used to sort the result-set in ascending or
descending order.
• The ORDER BY keyword sorts the records in ascending order by default.
• To sort the records in descending order, use the DESC keyword.
For Example:
Chocstock Date Name Qtyrcd

SELECT *Date
FROM Name
Chocstock
Qtyrcdorder by Qtyrcd; 01/10/02 Kitkat 20

Result: 01/10/02 Kitkat 20 01/15/02 Dairy Milk 50

01/15/02 Dairy Milk 50 01/20/02 Dairy Milk 70

01/20/02 Perk 50 01/20/02 Perk 50

01/20/02 Dairy Milk 70 01/25/02 Kitkat 80

01/25/02 Kitkat 80

SELECT * FROM Chocstock order by Qtyrcd Desc; ( In descending

order)
 38

References

• https://www.w3schools.com/sql/sql_alter.asp
• https://www.w3resource.com/sql-exercises/sql-retrieve-exercise-1
2.php
• https://www.mysqltutorial.org/mysql-group-by.aspx/