SQL - PowerPoint PPT Presentation

About This Presentation

Title:

SQL

Description:

... stuname, credits FROM student; SELECT stuid, stuname, credits 10 FROM ... stuname, major, credits FROM student WHERE ... major, AVG(credits) FROM student ... – PowerPoint PPT presentation

Number of Views:50

Avg rating:3.0/5.0

Slides: 71

Provided by: CSUS5

Learn more at: https://www.csus.edu

Category:

more less

Transcript and Presenter's Notes

Title: SQL

1
Chapter 5

2
Agenda

Data Manipulation Language (DML)
SELECT
Union compatible operations
Update database

3
SQL DML - SELECT

SELECT DISTINCTALL column expression AS
newname ,...
FROM table-name alias ,...
WHERE condition
GROUP BY column list
HAVING condition
ORDER BY column list

4
Simple SELECT

SELECT attributes (or calculations , -, /, )
FROM relation
SELECT DISTINCT attributes
FROM relation
SELECT attributes (or wild card)
FROM relation
WHERE condition

5
Example

SELECT stuname FROM student
SELECT stuid, stuname, credits FROM student
SELECT stuid, stuname, credits10 FROM
student
SELECT DISTINCT major FROM student

SELECT FROM student
SELECT stuname, major, credits FROM
student WHERE stuid S114
SELECT FROM faculty WHERE dept
MIS

7
SELECT - WHERE condition

AND OR
NOT IN
NOT IN BETWEEN
IS NULL IS NOT NULL
SOME ALL
NOT BETWEEN
LIKE '' multiple characters
LIKE _ single character
Evaluation rule left to right, brackets, NOT
before AND OR, AND before OR

8
Example

SELECT FROM faculty WHERE dept
MIS AND rank full professor
SELECT FROM faculty WHERE dept
MIS OR rank full professor
SELECT FROM faculty WHERE dept
MIS NOT rank full professor

SELECT FROM class WHERE room LIKE
b_s
SELECT FROM class WHERE room NOT
LIKE bus
SELECT productid, productname FROM
inventory WHERE onhand BETWEEN 50 and 100
SELECT customerid, discountrate
FROM sales
WHERE
discountrate LIKE 20 ESCAPE

SELECT companyid, companyname FROM
company WHERE companyname BETWEEN G
AND K
SELECT productid, productname FROM
inventory WHERE onhand NOT BETWEEN 50
and 100
SELECT companyid, companyname FROM
company WHERE companyname NOT BETWEEN
G AND K

SELECT facname FROM faculty WHERE
dept IN (MIS, ACT)
SELECT facname FROM faculty WHERE
rank NOT IN (assistant, lecture)
SELECT customername FROM customer WHERE
emailadd IS NOT NULL

SELECT customername FROM customer WHERE
creditlimit IS NULL

13
SELECT - aggregate functions

COUNT
SUM
AVG
MIN
MAX

14
Example

SELECT COUNT() FROM student
SELECT COUNT(major) FROM student
SELECT COUNT(DISTINCT major) FROM student

SELECT COUNT(stuid), SUM(credits),
AVG(credits), MAX(credits),
MIN(credits) FROM student

How many different guests have made bookings for
August 2004?
Hotel (hotelno, hotelname, city)
Room (roomno, hotelno, type, price)
Booking (hotelno, guestno, datefrom, dateto,
roomno)
Guest (guestno, guestname, guestaddress)

How many different guests have made bookings for
August 2004?
SELECT COUNT(DISTINCT guestno)
FROM booking
WHERE (datefrom lt 8/31/04) AND
(dateto gt 8/1/04)

18
SELECT - GROUP

GROUP BY
HAVING

19
Example

SELECT major, AVG(credits) FROM
student GROUP BY major
SELECT course, COUNT(stuid) FROM
enrollment GROUP BY course

20
Example

SELECT major, AVG(credits) FROM
student GROUP BY major HAVING COUNT()
gt 2
SELECT course, COUNT(stuid) FROM
enrollment GROUP BY course HAVING
COUNT() gt 2

SELECT major, AVG(credits) FROM
student WHERE major IN (mis, act)
GROUP BY major HAVING COUNT() gt 2

22
SELECT - ORDER BY

ORDER BY
ORDER BY ... DESC

23
Example

SELECT facname, rank FROM faculty
ORDER BY facname
SELECT facname, rank FROM faculty
ORDER BY rank DESC, facname

24
SELECT - JOIN Tables

Multiple tables in FROM clause
MUST have join conditions!!!

25
Example

SELECT stuname, grade FROM student,
enrollment
WHERE student.stuid enrollment.stuid

26
List course, stuname and major for faculty
number is equal to f114

SELECT enrollment.course, stuname, major FROM
class, enrollment, student WHERE class.course
enrollment.course AND enrollment.stuid
student.stuid AND facid F114 ORDER
BY enrollment.course

27
OUTER JOINS

RIGHT JOIN
LEFT JOIN
FULL JOIN
Appending () to the optional column (null) in
the join condition (Oracle)

28
Example

List the faculty member who does not teach any
class
SELECT f.facid, f.facname
FROM class c, faculty f WHERE c.facid ()
f.facid
AND c.course is null
ORDER BY f.facname (right outer join)

29
List the faculty member who does not teach any
class

SELECT f.facid, f.facname
FROM class c RIGHT JOIN faculty f ON c.facid
f.facid
WHERE c.course is null
ORDER BY f.facname (right outer join)

30
List the student name and major who is not
enrolled in any class

SELECT s.stuname, major FROM student s,
enrollment e WHERE s.stuid e.stuid () AND
e.stuid is null
ORDER BY s.stuname (left outer join)

31
List the student name and major who is not
enrolled in any class

SELECT s.stuname, major FROM student s LEFT
JOIN enrollment e
ON s.stuid e.stuid
WHERE e.stuid is null
ORDER BY s.stuname (left outer join)

32
List the faculty member who does not teach any
class

SELECT f.facid, f.facname
FROM class c, faculty f WHERE c.facid ()
f.facid () AND c.course is null
ORDER BY f.facname (full outer join)

33
List the faculty member who does not teach any
class

SELECT f.facid, f.facname
FROM class c FULL JOIN faculty f ON c.facid
f.facid
WHERE c.course is null
ORDER BY f.facname (full outer join)

34
Example

List the number of room in each hotel in London.
Hotel (hotelno, hotelname, city)
Room (roomno, hotelno, type, price)
Booking (hotelno, guestno, datefrom, dateto,
roomno)
Guest (guestno, guestname, guestaddress)

35
Example

List the number of room in each hotel in London.
SELECT r.hotelno, COUNT(roomno)
FROM room r, hotel h
WHERE r.hotelnoh.hotelno AND
city London'
GROUP BY hotelno

36
Union Compatible Operations

UNION
MINUS or EXCEPT
INTERSECT
Union compatible operator ALL
CORRESPONDINGBY column,.. (ALL includes
duplicated rows in the result)
Used between SELECT commands

37
Example

SELECT stuid, stuname FROM sacstudent
UNION
SELECT stuid, stuname FROM chicostudent
SELECT
FROM sacstudent
UNION CORRESPONDING BY stuid, stuname
SELECT
FROM chicostudent

SELECT stuid, stuname FROM sacstudent
EXCEPT
SELECT stuid, stuname FROM chicostudent
(SELECT stuid, stuname FROM sacstudent)
INTERSECT
(SELECT stuid, stuname FROM
chicostudent) ORDER BY 2

39
Column Alias

SELECT prodid, prodname, (salesprice -
goodofcost) profit FROM product ORDER BY
prodid
SELECT prodid, prodname, (salesprice -
goodofcost) AS profit FROM product ORDER
BY prodid

40
SUBQUERY

List stuid, stuname, and credits for the student
whose credits are larger than the average student
credits
SELECT stuid, stuname, credits
FROM student
WHERE credits gt (SELECT AVG(credits)
FROM student)

41
List stuid, stuname, and major of those student
who is enrolled in a class

SELECT stuid, stuname, major
FROM student
WHERE stuid IN (SELECT stuid
FROM enrollment)

42
List stuid, stuname, and major of those student
who is not enrolled in a class

SELECT stuid, stuname, major
FROM student
WHERE stuid NOT IN
(SELECT stuid FROM enrollment)

43
Example

What is the most commonly booked room type for
all hotels in London?
Hotel (hotelno, hotelname, city)
Room (roomno, hotelno, type, price)
Booking (hotelno, guestno, datefrom, dateto,
roomno)
Guest (guestno, guestname, guestaddress)

44
Find the type with its number of rooms of every
room in London

SELECT type, COUNT(type) AS y
FROM booking b, hotel h, room r
WHERE r.roomno b.roomno AND
r.hotelno b.hotelno AND
b.hotelno h.hotelno AND
city 'London'
GROUP BY type

What is the most commonly booked room type for
all hotels in London?
SELECT type, MAX(y)
FROM
(SELECT type, COUNT(type) AS y
FROM booking b, hotel h, room r
WHERE r.roomno b.roomno AND
r.hotelno b.hotelno AND
b.hotelno h.hotelno AND
city 'London'
GROUP BY type)
GROUP BY type

46
EXIST

Find student name and major who is enrolled in a
class
SELECT s.stuname, major FROM student
s WHERE EXIST (SELECT FROM
enrollment e WHERE
s.stuid e.stuid)

47
NOT EXIST

Find student name and major who is not enrolled
in a class
SELECT s.stuname, major FROM student
s WHERE NOT EXIST (SELECT
FROM enrollment e WHERE
s.stuid e.stuid)

48
SOME

Find stuid, stuname, major, and credits of the
student whose credits are greater than some mis
students credits
SELECT stuid, stuname, major, credits
FROM student
WHERE credits gt SOME (SELECT credits
FROM student
WHERE majormis)

49
ANY

Find stuid, stuname, major, and credits of the
student whose credits are greater than any mis
students credits
SELECT stuid, stuname, major, credits
FROM student
WHERE credits gt ANY (SELECT credits
FROM student
WHERE majormis)

50
ALL

Find stuid, stuname, major, and credits of the
student whose credits are greater than every mis
students credits
SELECT stuid, stuname, major, credits
FROM student
WHERE credits gt ALL (SELECT credits
FROM student
WHERE majormis)

What is the lost income from unoccupied rooms at
the Grosvenor Hotel today?
Hotel (hotelno, hotelname, city)
Room (roomno, hotelno, type, price)
Booking (hotelno, guestno, datefrom, dateto,
roomno)
Guest (guestno, guestname, guestaddress)

52
Find the rooms that are occupied at the Grosvenor
Hotel today

SELECT roomno FROM booking b, hotel h
WHERE b.hotelno h.hotelno AND
(datefrom lt SYSTEM DATE
AND dateto gt SYSTEM DATE) AND
h.hotelname 'Grosvenor'

53
Find the total income of all rooms at the
Grosvenor Hotel

SELECT SUM(price)
FROM room r, hotel h
WHERE r.hotelno h.hotelno AND
h.hotelname 'Grosvenor

What is the lost income from unoccupied rooms at
the Grosvenor Hotel today?
SELECT SUM(price)
FROM room r, hotel h
WHERE r.hotelno h.hotelno AND
h.hotelname 'Grosvenor AND
r.roomno NOT IN
(SELECT roomno FROM booking b, hotel h
WHERE b.hotelno h.hotelno AND
(datefrom lt SYSTEM DATE
AND dateto gt SYSTEM DATE) AND
h.hotelname 'Grosvenor')

What is the lost income from unoccupied rooms at
each hotel today?
Hotel (hotelno, hotelname, city)
Room (roomno, hotelno, type, price)
Booking (hotelno, guestno, datefrom, dateto,
roomno)
Guest (guestno, guestname, guestaddress)

What is the lost income from unoccupied rooms at
each hotel today?
SELECT h.hotelno, SUM(price)
FROM room r
WHERE roomno NOT EXIST
(SELECT
FROM booking b, hotel h, room r
WHERE b.hotelno h.hotelno AND
r.roomno b.roomno AND
r.hotelno b.hotelno AND
datefrom lt SYSTEM DATE AND
dateto gt SYSTEM DATE )
GROUP BY hotelno

57
SQL DML - UPDATE, INSERT, DELETE

INSERT INTO table-name (colm , colm) VALUES
(const , const )
UPDATE table-name SET colm expr colm expr
... WHERE condition
DELETE FROM table-name WHERE condition

58
Example

INSERT INTO student
(stuid, stuname, major, credits)
VALUES (S114, Grace, MIS, 60)
UPDATE student
SET major Database, credits 100
WHERE stuid S114
UPDATE student
SET major MIS

DELETE FROM student
WHERE stuid S114
DELETE FROM student

Branch (Bno, Area, City, Pcode, Tel, Fax)
Staff (Sno, FName, LName, Position, Sex, DOB,
Salary, Bno)
Property_for Rent (Pno, Area, City, Type, Rooms,
Rent, Ono, Sno, Bno)
Renter (Rno, FName, LName, Pref_Type, Max_Rent)
Owner ( Ono, FName, LName, Tel)
Viewing (Rno, Pno, Date, Comment)

A list (Bno, Street, Area,Tel, Fax) of branches
located in Bay Area
A list (Pno, Street, Area, City) of properties
for rent with 4 bedrooms or less than 1,000 per
month rent
A list (Sno, FName, LName) of female mangers with
salary between one to two million dollars
A list (Pno, Street, Area, City) of properties
for rent located in SF, LA, NY, or DC

A list (Pno, Street, Area, City) of properties
for rent not located in SF, LA, NY, or DC
A list (Ono, Fname, Lname) of owners without any
telephone

The total number of branches
The total number of staffs
The total salary for male managers
The minimum salary for the female manager
The maximum rent to rent a property by a renter

The average salary for male staff members
The average number of rooms in the single house
type
The number of employees in each branch
The average salary in each branch
The average salary in each branch with more than
10 staff members

The number of employees in each branch located in
SF, NY, LA, and DC
The number of employees in each branch with more
than 10 employees and located in SF, NY, LA, and
DC
A list (Pno, Street, Area, City) of properties
for rent managed by John Dow and owned by Sue Lee
A list (Sno, FName, LName) of staffs without
supervising any property for rent
A list (Rno, Fname, Lname) of renters without
looking any property for rent