Chapter 12 SQL - PowerPoint PPT Presentation

1 / 61

About This Presentation

Title:

Chapter 12 SQL

Description:

SQL: Data Manipulation Thomas Connolly, Carolyn Begg, Database System, A Practical Approach to Design Implementation and Management, 4th Edition, Addison Wesley – PowerPoint PPT presentation

Number of Views:152

Avg rating:3.0/5.0

Slides: 62

Provided by: edum101

Category:

more less

Transcript and Presenter's Notes

Title: Chapter 12 SQL

1
Chapter 5
SQL Data Manipulation Thomas Connolly, Carolyn
Begg, Database System, A Practical Approach to
Design Implementation and Management, 4th
Edition, Addison Wesley Pg 113 156
2
Learning Outcomes

Introduction to SQL
Writing SQL Commands
Data Manipulation

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 2006?
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 2006?
SELECT COUNT(DISTINCT guestno)
FROM booking
WHERE (datefrom lt 8/31/06) AND
(dateto gt 8/1/06)

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

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

26
Example

List stuname and grade
SELECT stuname, grade FROM student s,
enrollment e
WHERE s.stuid e.stuid

27
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

28
OUTER JOINS

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

29
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)

30
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)

31
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)

32
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)

33
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)
Use only when the database contains proper records

34
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)

35
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)

36
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

37
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

38
Example

List stuid and stuname who attends SAC or CHICO
SELECT stuid, stuname FROM sacstudent
UNION
SELECT stuid, stuname FROM chicostudent
SELECT
FROM sacstudent
UNION CORRESPONDING BY stuid, stuname
SELECT
FROM chicostudent

List stuid and stuname who attends SAC not CHICO
SELECT stuid, stuname FROM sacstudent
EXCEPT
SELECT stuid, stuname FROM chicostudent
List stuid and stuname who attends SAC and CHICO
(SELECT stuid, stuname FROM sacstudent)
INTERSECT
(SELECT stuid, stuname FROM
chicostudent) ORDER BY 2

A renter viewing list (Rno, Fname, Lname) of
renters who only look property_for_rent at
Sacramento
(Select
From Viewing v, Property_for_rent p, Renter r
Where v.Pno p.Pno and r.Rno v.Rno and
CitySacramento)
EXCEPT CORRESPONDING BY Rno, Fname, Lname
(Select
From Viewing v, Property_for_rent p, Renter r
Where v.Pno p.Pno and r.Rno v.Rno and
CitySacramento)

41
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

42
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)

43
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)

44
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)

45
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)

46
Find the type with its number of rooms of every
hotel 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

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

48
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)

49
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)

50
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)

51
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)

52
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)