chapter 5 sql. agenda data manipulation language (dml) –select –union compatible operations...

Chapter 5

SQL

Agenda

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

SQL DML - SELECT

• SELECT [DISTINCT|ALL] {* | [col-expr [AS newname]][,...]

FROM table-name [alias] [,...] [WHERE condition] [GROUP BY colm [, colm] [HAVING condition]] ORDER BY colm [, colm]

SQL DML - SELECT

• SELECT attributes (or calculations: +, -, /, *)

FROM relation

• SELECT DISTINCT attributes FROM relation

Examples

• SELECT stunameFROM student;

• SELECT stuid, stuname, creditFROM student;

• SELECT stuid, stuname, credit+10FROM student;

• SELECT DISTINCT majorFROM student;

SQL DML - SELECT

• SELECT attributes (or * wild card) FROM relation WHERE condition

Examples

• SELECT *FROM student;

• SELECT stuname, major, creditFROM student

WHERE stuid = ‘S114’;

• SELECT *FROM faculty

WHERE dept = ‘MIS’;

SELECT - WHERE condition

• AND OR• NOT IN• NOT IN BETWEEN• IS NULL IS NOT NULL• SOME ALL• LIKE '%' multiple characters • LIKE ‘_’ single character

Examples• SELECT *

FROM facultyWHERE 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, productnameFROM inventory

WHERE onhand BETWEEN 50 and 100;

• SELECT companyid, companynameFROM companyWHERE companyname BETWEEN

‘G’ AND ‘K’;

• SELECT productid, productnameFROM inventoryWHERE onhand NOT BETWEEN

50 and 100;

• SELECT companyid, companynameFROM companyWHERE companyname NOT

BETWEEN ‘G’ AND ‘K’;

• SELECT facnameFROM facultyWHERE dept IN (‘MIS’, ‘ACT’);

• SELECT facnameFROM facultyWHERE rank NOT IN (‘assistant’, ‘lecture’);

• SELECT customernameFROM customerWHERE emailadd IS NOT NULL;

• SELECT customernameFROM customer

WHERE creditlimit IS NULL;

SELECT - aggregate functions

• COUNT (*)• COUNT• SUM• AVG• MIN• MAX

Examples

• SELECT COUNT(*)FROM student;

• SELECT COUNT(major)FROM student;

• SELECT COUNT(DISTINCT major)FROM student;

• SELECT COUNT(stuid), SUM(credit), AVG(credit), MAX(credit),

MIN(credit)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 <= ‘8/31/04’) AND

• (dateto >= ‘8/1/04’);

SELECT - GROUP

• GROUP BY• HAVING

Examples

• SELECT major, AVG(credit)FROM student

GROUP BY major

• SELECT course#, COUNT(stuid)FROM enrollment

GROUP BY course#

Examples

• SELECT major, AVG(credit)FROM studentGROUP BY major

HAVING COUNT(*) > 2;• SELECT course#, COUNT(stuid)

FROM enrollmentGROUP BY course#

HAVING COUNT(*) > 2;

• SELECT major, AVG(credit)FROM student

WHERE major IN (‘mis’, ‘act’) GROUP BY major

HAVING COUNT(*) > 2;

SELECT - ORDER BY

• ORDER BY• ORDER BY ... DESC

Examples

• SELECT facname, rank FROM faculty

ORDER BY facname;

• SELECT facname, rank FROM faculty

ORDER BY rank DESC, facname;

SELECT - JOIN Tables

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

Examples

• SELECT stuname, gradeFROM student, enrollmentWHERE student.stuid =

enrollment.stuid;

• SELECT enrollment.course#, stuname, major

FROM class, enrollment, studentWHERE class.course# =

enrollment.course#AND enrollment.stuid = student.stuidAND facid = ‘F114’

ORDER BY enrollment.course#;

OUTER JOINS

• RIGHT JOIN• LEFT JOIN• FULL JOIN• Appending (+) to the optional column

(null) in the join condition (Oracle)

Examples

• SELECT f.facid, f.facname, c.course# FROM class c, faculty fWHERE c.facid (+) = f.facid

ORDER BY f.facname; (rightouterjoin)

• SELECT s.stuname, major, gradeFROM student s, enrollment eWHERE s.stuid = e.stuid (+)

ORDER BY s.stuname; (leftouterjoin)

• SELECT f.facid, f.facname, c.course# FROM class c RIGHT JOIN faculty fON c.facid = f.facid

ORDER BY f.facname; (rightouterjoin)• SELECT s.stuname, major, grade

FROM student s LEFT JOIN enrollment e

ON s.stuid = e.stuid

ORDER BY s.stuname; (leftouterjoin)

• SELECT f.facid, f.facname, c.course#, c.room

FROM class c, faculty fWHERE c.facid (+) =

f.facid (+) ORDER BY f.facname; (fullouterjoin)

• SELECT f.facid, f.facname, c.course#, c.room

FROM class c FULL JOIN faculty f ON c.facid = f.facid

ORDER BY f.facname; (fullouterjoin)

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)

Example

• List the number of room in each hotel in London.

• SELECT r.hotelno, COUNT(roomno)• FROM room r, hotel h• WHERE r.hotelno=h.hotelno AND• city = ‘London'• GROUP BY hotelno;

Union Compatible Operations

• UNION

• MINUS or EXCEPT

• INTERSECT

• Union compatible operator [ALL] [CORRESPONGIND][BY column,..] (ALL includes duplicated rows in the result)

• Used between SELECT commands

Examples

• SELECT stuid, stunameFROM sacstudent

UNIONSELECT stuid, stuname

FROM chicostudent;• SELECT *• FROM sacstudentUNION CORRESPONGIND BY stuid, stuname

SELECT *FROM chicostudent;

• SELECT stuid, stunameFROM sacstudent

EXCEPT

SELECT stuid, stunameFROM chicostudent;

• (SELECT stuid, stunameFROM sacstudent)

INTERSECT

(SELECT stuid, stunameFROM chicostudent)ORDER BY 2;

Column Alias

• SELECT prodid, prodname,(salesprice - goodofcost) profit

FROM productORDER BY prodid;

• SELECT prodid, prodname,(salesprice - goodofcost) AS

profit FROM productORDER BY prodid;

SUBQUERY

• SELECT stuid, stuname, credit

• FROM student

• WHERE credit > (SELECT AVG(credit)

• FROM student);

• SELECT stuid, stuname, major

• FROM student

• WHERE stuid IN (SELECT stuid

• FROM enrollment);

• SELECT stuid, stuname, major

• FROM student

• WHERE stuid NOT IN (SELECT stuid

• FROM enrollment)

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)

• 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;

EXIST

• SELECT s.stuname, majorFROM student s

WHERE EXIST(SELECT *

FROM enrollment e WHERE

s.stuid = e.stuid);

NOT EXIST

• SELECT s.stuname, majorFROM student s

WHERE NOT EXIST(SELECT *

FROM enrollment e WHERE

s.stuid = e.stuid);

SOME

• SELECT stuid, stuname, major, credit

• FROM student

• WHERE credit > SOME (SELECT credit

• FROM student

• WHERE major=‘mis’);

ANY

• SELECT stuid, stuname, major, credit

• FROM student

• WHERE credit > ANY (SELECT credit

• FROM student

• WHERE major=‘mis’);

ALL

• SELECT stuid, stuname, major, credit

• FROM student

• WHERE credit > ALL (SELECT credit

• FROM student

• WHERE major=‘mis’);

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

• 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 <= ‘SYSTEM DATE’• AND dateto >= ‘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 <= ‘SYSTEM DATE’ AND• dateto >= ‘SYSTEM DATE’ )• GROUP BY hotelno;

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]

Examples

• INSERT INTO student

(stuid, stuname, major, credit)

VALUES (‘S114’, ‘Grace’, ‘MIS’, 60);

• UPDATE student

SET major = ‘Database’, credit = 100

WHERE stuid = ‘S114’;

• UPDATE student

SET major = ‘MIS’;

• DELETE FROM student

WHERE stuid = ‘S114’;

• DELETE FROM student;

Points To Remember

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

Assignment

• Review chapter 1 – 5, appendix C

• Read chapter 6

• Homework Assignment– 5.7 – 5.28 (not 5.18)– Due date: