Chapter 5 SQL Agenda Data Manipulation Language DML

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

• SELECT * FROM student; • SELECT stuname, major, credits FROM student WHERE stuid = ‘S 114’; • SELECT * FROM faculty WHERE dept = ‘MIS’;

SELECT - WHERE condition • • • AND OR 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 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 <= ‘ 8/31/06’) AND • (dateto >= ‘ 8/1/06’);

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. . . 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 List stuname and grade • SELECT stuname, grade FROM student, enrollment WHERE student. stuid = enrollment. stuid;

Example List stuname and grade • SELECT stuname, grade FROM student s, enrollment e WHERE s. stuid = e. stuid;

List course#, stuname and major faculty number is equal to f 114 • SELECT enrollment. course#, stuname, major FROM class, enrollment, student WHERE class. course# = enrollment. course# AND enrollment. stuid = student. stuid AND facid = ‘F 114’ 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) • Use only when the database contains proper records

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 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 City=‘Sacramento’) • 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 City~=‘Sacramento’)

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

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

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

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 <= ‘SYSTEM DATE’ AND dateto >= ‘SYSTEM DATE’) AND h. hotelname = 'Grosvenor';

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

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

Example • INSERT INTO student (stuid, stuname, major, credits) VALUES (‘S 114’, ‘Grace’, ‘MIS’, 60); • UPDATE student SET major = ‘Database’, credits = 100 WHERE stuid = ‘S 114’; • UPDATE student SET major = ‘MIS’;

• DELETE FROM student WHERE stuid = ‘S 114’; • DELETE FROM student;

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

• Type the problem before your answer for every assigned problem of chapter 5, assignment 5, chapter 6, and assignment 6

Assignment • Review chapter 5 • Read chapter 6 • Homework Assignment – Chapter 5. 7 – 5. 28 (not 5. 18) and assignment 5 – Due date: