DATABASE SYSTEMS

Dr. Noha Nagy

Lecture 5 SQL[DML]
 SQL
2
Structured Query Language
 Data Definition Language (DDL)
 Definerelational schemata
 Create/Alter/Drop tables and their attributes

 Data Manipulation Language (DML)

 Insert/Delete/Update tuples in tables
 Query one or more table
 Insert Statment
3
Enum Ename phone Pnum
Employee
123 Ahmed 01110025878 111

124 Ali 01225929785

127 Ola 0102457896 111

Insert into Employee values (128, ‘Mahmoud’, 01113005581, 326);
Insert into Employee (Enum,Ename, Pnum)values (130, ‘Eyad’ , 327);
Enum Ename phone Pnum
Employee
123 Ahmed 01110025878 111

124 Ali 01225929785

127 Ola 0102457896 111

128 Mahmoud 01113005581 326

130 Eyad 327

 Inserting From Another Table
4

Insert into <tableName1>

Select * from <tableName2>
Where <condition>
 Inserting from Another Table
5

MyCustomers( id, name, city)

Insert all customers from the table “Customers” to table
“MyCustomers”

insert into myCustomers

Select customerNumber, customerName, city
from customers;
 Update Statment
6
Pnum Pname Price Quantity

123 Arial 200 20

Product
124 Persil 180 50

127 OXI 100 11

128 Tide 150 32

Update Product Set Price=price*2

Pnum Pname Price Quantity

123 Arial 400 20

Product
124 Persil 360 50

127 OXI 200 11

128 Tide 300 32

 Delete Statment
7
Employee Enum Ename
Ename phone
phone Pnum

123 Ahmed 01110025878 111

124 Ali 01225929785 254

127 Ola 0102457896 111

Delete From Employee

Where Pnum = 254;
Employee
Enum Ename
Ename phone
phone Pnum

123 Ahmed 01110025878 111

127 Ola 0102457896 111

 SQL SYNTAX
8

• Basic form
SELECT <attributes>
FROM <one or more relations>
WHERE <conditions>
Call this a SFW query.
SELECT <Column list>
FROM <table names>
[WHERE <Condition>]
[GROUP BY <Column list>]
[HAVING <Condition>]
[ORDER BY <Column list>]
 Retrieve Specific Columns and Rows
9

Product PName Price Category Manufacturer

Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi

SELECT PName, Price, Manufacturer

FROM Product
WHERE Price > 100
PName Price Manufacturer
“selection” and SingleTouch $149.99 Canon
“projection” MultiTouch $203.99 Hitachi
 Aggregate Functions
10

 Min
 Max
 Count
 Avg
 Sum
 Products PID Pname Price Qty SupplierI
D
11Slide 1 Apple 20 200 22
2 Banana 10 100 10
3 Orange 4 400 6

SELECT MIN(Price)
FROM Products; 4
 Products PID Pname Price Qty SupplierI
D
1 Apple 20 200 22

12 2 Banana 10 100 10
3 Orange 4 400 6

SELECT MIN(Price) AS SmallestPrice SmallestPrice

FROM Products; 4

SELECT MAX(Price) AS HighestPrice HighestPrice

FROM Products; 20

SELECT COUNT(PID) AS Count Count

FROM Products; 3

SELECT AVG(Price) AS AveragePrice AveragePrice

FROM Products; 11.33
 Using Aggregate Function
13

 Using the COUNT aggregate function to find totals

 Find number of customers who live in Rome
 Customer(ID,Name,City)

SELECT COUNT(*)
FROM Customer
WHERE City = ‘Rome’
 Categorizing Results
14

 For use with aggregate functions

 Scalar aggregate: single value returned from SQL query with aggregate
function
 Vector aggregate: multiple values returned from SQL query with aggregate
function (via GROUP BY) Customer
Fname Lname ID City
Ahmed Fahmy 111 Cairo
Ali Zidan 112 Cairo
Mark Antony 113 Cairo
Amr Moussa 114 Giza

SELECT City, Count(City) SELECT Count(City)

FROM Customer FROM Customer
GROUP BY City WHERE City=‘Cairo’

What is the Difference between them?

 Group by
15
SQL has a GROUP BY-clause for specifying the grouping
attributes, which must also appear in the SELECT-clause
For each department, retrieve the department number, the
number of employees in the department, and their average
salary.
SELECT DNO, COUNT (*), AVG (SALARY)
FROM EMPLOYEE
GROUP BY DNO
Employee
Name DNO ID Salary
Ahmed 1 111 1000
1 2 1000 Ali 1 112 1000
2 2 5500 Mark 2 113 5000
Amr 2 114 6000
 Group by
16
SQL has a GROUP BY-clause for specifying the grouping
attributes, which must also appear in the SELECT-clause
For each department, retrieve the department number, the
number of employees in the department, and their average
salary.
SELECT DNO as Department, COUNT (*)as
num_Employee, AVG (SALARY) as avg_salary
FROM EMPLOYEE
GROUP BY DNO Employee
Name DNO ID Salary
Ahmed 1 111 1000
Department Num_Employee Avg_salary
Ali 1 112 1000
1 2 1000 Mark 2 113 5000
Amr 2 114 6000
2 2 5500
 Example
17

Purchase
Product Date Price Quantity Product TotalSales
Apple 10/21 1 20
Apple 10/25 1.50 20 Apple 50
Banana 10/3 0.5 10
Banana 10/10 1 10
Banana 15

Get the total sales after 10/1/2005 for each product

SELECT product as Product, Sum(price*quantity) AS TotalSales

FROM Purchase
WHERE date > ‘10/1’
GROUP BY product
 GROUP BY
18

Grades SELECT Name,

Name Code Mark AVG(Mark) AS Average
FROM Grades
John DBS 56
John IAI 72 GROUP BY Name
Mary DBS 60
Mark PR1 43 Name Average
Mark PR2 35 John 64
Jane IAI 54 Mary 60
Mark 39
Jane 54
Calculate the average marks of each Student
 GROUP BY
19

 Find the total value of the

Sales sales for each department in
each month
Month Department Value
 Can group by Month then
March Fiction 20 Department or Department then
March Travel 30 Month
March Technical 40
April Fiction 10  Same results, but in a different
April Fiction 30 order
April Travel 25
April Fiction 20
May Fiction 20
May Technical 50
 GROUP BY
20

SELECT Month, Department,

SUM(Value) AS Total
FROM Sales
GROUP BY Month, Department
Month Department Total
April Fiction 60
April Travel 25
March Fiction 20
March Technical 40
March Travel 30
May Fiction 20
May Technical 50
 Qualifying Results by Categories Using the HAVING Clause
21
 Return all Order IDs that include more than 3 products in their
OrderLines. Orders OrderID ProductID Quantity
100 1 10
100 2 17
SELECT OrderID, Count(ProductID) 102 2 2

FROM Orders 100

103
5
3
9
3
GROUP BY OrderID 103 4 4

HAVING Count(productID) > 3; 103

103
5
6
5
6

Like a WHERE clause, but it operates on groups (categories), not on

individual rows. Here, only those groups with total numbers greater
than 3 will be included in final result. HAVING is considered a
SECOND WHERE.
 Qualifying Results by Categories Using the HAVING Clause
9

 Return all Order IDs that include more than 3 products in their
OrderLines. orders
OrderID ProductID Quantity
100 1 10
SELECT OrderID, Count(ProductID) as X 100 2 17
102 2 2
FROM Orders 100 5 9
GROUP BY OrderID 103 3 3
103 4 4
HAVING X > 3; 103 5 5
103 6 6

Order ID X

103 4
 Notes
23

 You can use group by with where in the same query

 You can group by more than one attribute separated by ,
 The group by list of columns must be listed in the select
statement.
 You should alias aggregate functions, so the column names
are meaningful
 Exercise
24

 Write a query to return number of students

(No_of_Students) whose names ends with “Smith” and
their age is Greater than 20.
 Exercise
25

 Write a query to return number of students

(No_of_Students) whose names ends with “Smith” and
their age is Greater than 20.

SELECT Count(*) as No_of_Students

FROM Student
WHERE Student_name Like ‘%Smith’ AND Age > 20