700 likes | 839 Views
Chapter 5. SQL. Agenda. Data Manipulation Language (DML) SELECT Union compatible operations Update database. SQL DML - SELECT. SELECT [DISTINCT|ALL] {* | [column expression [AS newname]] [,...]} FROM table-name [alias] [,...] [WHERE condition] [GROUP BY column list]
E N D
Chapter 5 SQL
Agenda • Data Manipulation Language (DML) • SELECT • Union compatible operations • Update database
SQL DML - SELECT • SELECT [DISTINCT|ALL] {* | [column expression [AS newname]] [,...]} FROM table-name [alias] [,...] [WHERE condition] [GROUP BY column list] [HAVING condition] [ORDER BY column list]
Simple SELECT • SELECT attributes (or calculations: +, -, /, *) FROM relation • SELECT DISTINCT attributes FROM relation • SELECT attributes (or * wild card) FROM relation WHERE condition
Example • SELECT stuname FROM student; • SELECT stuid, stuname, credits FROM student; • SELECT stuid, stuname, credits+10 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’;
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
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;
SELECT - aggregate functions • COUNT • SUM • AVG • MIN • MAX
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 <= ‘8/31/04’) AND • (dateto >= ‘8/1/04’);
SELECT - GROUP • GROUP BY • HAVING
Example • SELECT major, AVG(credits) FROM student GROUP BY major • SELECT course#, COUNT(stuid) FROM enrollment GROUP BY course#
Example • SELECT major, AVG(credits) FROM student GROUP BY major HAVING COUNT(*) > 2; • SELECT course#, COUNT(stuid) FROM enrollment GROUP BY course# HAVING COUNT(*) > 2;
SELECT major, AVG(credits) FROM student WHERE major IN (‘mis’, ‘act’) GROUP BY major HAVING COUNT(*) > 2;
SELECT - ORDER BY • ORDER BY • ORDER BY ... DESC
Example • 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!!!
Example • SELECT stuname, grade FROM student, enrollment WHERE student.stuid = enrollment.stuid;
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#;
OUTER JOINS • RIGHT JOIN • LEFT JOIN • FULL JOIN • Appending (+) to the optional column (null) in the join condition (Oracle)
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)
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)
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)
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)
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)
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)
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] [CORRESPONDING][BY column,..] (ALL includes duplicated rows in the result) • Used between SELECT commands
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;
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;
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 > (SELECT AVG(credits) FROM student);
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);
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)
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)
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;
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);
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);
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 > SOME (SELECT credits FROM student WHERE major=‘mis’);
ANY • Find stuid, stuname, major, and credits of the student whose credits are greater than any mis student’s credits • SELECT stuid, stuname, major, credits FROM student WHERE credits > ANY (SELECT credits FROM student WHERE major=‘mis’);
ALL • Find stuid, stuname, major, and credits of the student whose credits are greater than every mis student’s credits • SELECT stuid, stuname, major, credits FROM student WHERE credits > ALL (SELECT credits FROM student WHERE major=‘mis’);