1 / 70

Chapter 5

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]

belita
Download Presentation

Chapter 5

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 5 SQL

  2. Agenda • Data Manipulation Language (DML) • SELECT • Union compatible operations • Update database

  3. 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]

  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, credits+10 FROM student; • SELECT DISTINCT major FROM student;

  6. 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’;

  9. 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 ‘#’;

  10. 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’;

  11. 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;

  12. 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;

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

  16. 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)

  17. 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’);

  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(*) > 2; • SELECT course#, COUNT(stuid) FROM enrollment GROUP BY course# HAVING COUNT(*) > 2;

  21. SELECT major, AVG(credits) FROM student WHERE major IN (‘mis’, ‘act’) GROUP BY major HAVING COUNT(*) > 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.hotelno=h.hotelno AND • city = ‘London' • GROUP BY hotelno;

  36. 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

  37. Example • SELECT stuid, stuname FROM sacstudent UNION SELECT stuid, stuname FROM chicostudent; • SELECT * • FROM sacstudent UNION CORRESPONDING BY stuid, stuname SELECT * FROM chicostudent;

  38. 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 > (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

  45. 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 > SOME (SELECT credits FROM student WHERE major=‘mis’);

  49. 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’);

  50. 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’);

More Related