CALIFORNIA STATE UNIVERSITY, SACRAMENTO College of Business Administration Nancy Tsai MIS 150 – Database Database Management Systems for Business Chapter 6 – SQL: SQL: Data Manipulation
Hotel (hotelno, hotelname, city) Room (roomno, hotelno , type, price) Booking (hotelno , guestno , datefrom, dateto, roomno ) Guest (guestno, guestname, guestaddress) Simple Queries
7. List full details of all hotels. The Hotel table contains all the information related to the hotel so we can use “select” without any where condition to get the answer. SELECT * FROM hotel; 8. List all all details of all hotels in London. The Hotel table contains all the information required for the report so we can use “select” with a where condition (London) to get the answer.
SELECT * FROM hotel WHERE city = 'London'; 9. List the names and addresses of all guests guests in London, alphabetically ordered by name. The Guest table contains all the information required for the report so we can use “select” with a where condition to get the answer and order the output by guest name.
SELECT guestname, guestaddress FROM guest WHERE guestaddress like ‘London’ ORDER BY guestname; 10. List all double or family rooms with with a price below $40. 00 per night, night, in ascending order of price.
1
The Room table contains all the information related to the room so we can use “select” to get the records that satisfies the two where conditions and order the ou tput by price. SELECT * FROM room WHERE price < 40 AND type IN ('Double', 'Family') ORDER BY price; 11. List the bookings for which no dateto has been specified. The Booking table contains all the information related to the booking so we can use “select” to get the records that satisfies one where condition (null).
SELECT * FROM booking WHERE dateto IS NULL; Aggregate Functions
12. How many hotels are there? We can use the “count” function on hetelno (the primary key) of the Hotel table to get the answer. We can also use the “*” to to count the number of rows in the Hotel relation since there is no duplication of hotel in the Hotel relation
SELECT COUNT(hotelno) FROM hotel; Or SELECT COUNT(*) FROM hotel; 13. What is the average price of a room? We can use the average function on price in the Room table to get the answer. SELECT AVG(price) FROM room; 14. What is the total revenue per night from all double rooms? We can use the “sum” function on price in the Room table with a where condition to get the answer.
SELECT SUM(price)
2
FROM room WHERE type = 'Double'; 15. How many different different guests have made bookings for August? We need to use the “count” function with “distinct” and where condition for the Booking table to get the answer. We use “distinct” to eliminate eliminate any duplicated guest number.
SELECT COUNT (DISTINCT guestno) FROM booking WHERE (datefrom <= ‘8/31/year’ AND dateto >= ‘8/1/year’); Subqueries and Joins
16. List the price price and type of all rooms at the Grosvenor Hotel. We need the information from Room table and Hotel table for the Grosvenor as the hotel name. Therefore, we use “select” to required column names by joining the Room table and Hotel table and the hotel name equal to Grosvenor. SELECT price, type FROM room, hotel WHERE hotel.hotelno = room.hotelno AND hotelname = 'Grosvenor'; 17. List all guests currently staying at the Grosvenor Hotel. We need Hotel table for the Grosvenor, Booking table for the currently staying, and Guest table for the guest information. information. Therefore, we join these three three tables together with date condition condition (currently staying ) and hotel condition (Grosvenor) for the answer. SELECT (guestno, guestname, guestaddress) FROM guest, booking, hotel WHERE guest.guestno =booking.guestno AND hotel.hotelno = booking.hotelno AND (datefrom <= ‘SYSTEM DATE’ AND dateto >= ‘SYSTEM DATE’) AND hotelname = ‘Grosvenor’; Or, we can use a row subquery and a scalar subquery. First, we use a simple select statement to get the hotel number of the Grosvenor from the Hotel table table in the most inner query. query. Second, we use a simple select statement to get a list of guest numbers who are currently staying in the hotel number equal to the Grosvenor from from the Booking table in the middle middle query. Third, we use another select statement to get every column names from Guest table for the guest number is in the middle query. SELECT * FROM guest WHERE guestno IN
3
(SELECT guestno FROM booking WHERE datefrom <= ‘SYSTEM DATE’ AND dateto >= ‘SYSTEM DATE’ AND hotelno = (SELECT hotelno FROM hotel WHERE hotelname = ‘Grosvenor’)); 18. List the details details of all rooms at the Grosvenor Hotel, including the name of the guest staying in the room, if the room is occupied. occupied . We get every room in the Grosvenor hotel by joining hotel and room tables. We get every room with a guest not by joining hotel, room, book and guest tables tables for Grosvenor hotel. We then left outer join the every room in the Grosvenor hotel with a guest in the room of the Grosvenor hotel to get the final answer. (SELECT r.hotelno, r.roomno, r.type, r.price FROM hotel h, room r WHERE h.hotelname ='Grosvenor’ AND h.hotelno = r.hotelno) LEFT JOIN (SELECT r.hotelno, r.roomno, r.type, r.price, g.guestname FROM hotel h, room r, booking b, guest g WHERE h.name = ‘Grosvenor’ AND (b.datefrom <= ‘SYSTEM DATE’ AND b.dateto >= ‘SYSTEM DATE’) AND h.hotelno = r.hotelno AND r.hotelno = b.hotelno AND r.roomno = b.roomno AND b.guestno = g.guestno) g.guestno) ON r.roomno = b.roomno; 19. What is the total income from bookings for the Grosvenor Hotel today? We need Hotel table for the Grosvenor, Booking table for the currently staying, and Room table for the income (price) information. Therefore, we join these three tables together with date condition (currently staying) and hotel condition (Grosvenor) for the answer. Note that room has two column names as its primary key. Therefore, we need two joins for joining Room table and Booking table. SELECT SUM(price) FROM booking b, room r, hotel h WHERE (b.datefrom <= ‘SYSTEM DATE’ AND b.dateto >= ‘SYSTEM DATE’) AND r.hotelno = h.hotelno AND r.hotelno = b.hotelno AND r.roomno r.roomno = b.roomno AND h.hotelname = ‘Grosvenor’; 20. List the rooms which are currently unoccupied at the Grosvenor Hotel. We use a select statement to get a list of the occupied room number nu mber (currently having a gust staying in the room) of the Grosvenor by joining the Hotel table and Booking table with a
4
currently booking condition and Grosvenor condition in in the inner query. Second, we use another select statement to get unoccupied room by getting every room in the Grosvenor that is not in the room number list with a guest generated gene rated from the inner query. SELECT (r.hotelno, r.roomno, r.type, r.price) FROM room r, hotel h WHERE r.hotelno = h.hotelno AND h.hotelname = 'Grosvenor’ AND roomno NOT IN (SELECT roomno FROM booking b, hotel h WHERE (datefrom <= ‘SYSTEM DATE’ AND dateto >= ‘SYSTEM DATE’) AND b.hotelno=h.hotelno AND hotelname = 'Grosvenor'); ); Or we use a select statement to get a list of the occupied room (currently having a gust staying in the room) of the Grosvenor by joining the Hotel table, Room table and Booking table with a currently booking condition and Grosvenor condition in in the inner query. Second, we use another select statement to get unoccupied room by getting every room in the Grosvenor that does not exist in the booked rooms with a guest generated from the inner query. SELECT (r.hotelno, r.roomno, r.type, r.price) FROM room r, hotel h WHERE r.hotelno = h.hotelno AND h.hotelname = 'Grosvenor’ AND NOT EXISTS (SELECT * FROM booking b WHERE (datefrom <= ‘SYSTEM DATE’ AND dateto >= ‘SYSTEM DATE’) AND r.hotelno=b.hotelno AND r.roomno=b.roomno AND r.hotelno=h.hotelno AND hotelname = 'Grosvenor'); 'Grosvenor'); 21. What is the lost income from unoccupied rooms at the the Grosvenor Hotel? We use a select statement to get a list of the occupied room number nu mber (currently having a gust staying in the room) of the Grosvenor by b y joining the Hotel table and Booking Booki ng table with a currently booking condition and Grosvenor condition in in the inner query. Second, we use another select statement to get unoccupied room by b y getting every room in the Grosvenor that is not in the room number list with a guest generated from the inner query. Finally, we get the total of the price for these unoccupied rooms. SELECT SUM(price) FROM room r, hotel h WHERE r.hotelno = h.hotelno AND h.hotelname = 'Grosvenor’ AND roomno NOT IN (SELECT roomno FROM booking b, hotel h WHERE (datefrom <= ‘SYSTEM DATE’ AND dateto >= ‘SYSTEM DATE’) AND b.hotelno = h.hotelno AND h.hotelname = 'Grosvenor'); );
5
Or we use a select statement to get a list of the occupied room (currently having a gust staying in the room) of the Grosvenor by joining the Hotel table, Room table and Booking table with a currently booking condition and Grosvenor condition in the inner query. Second, we use another select statement to get unoccupied room by getting every room in the Grosvenor that does not guest generated from the inner query. query. Finally, we get the total exist in the booked rooms with a guest of the price for these unoccupied rooms. SELECT SUM(price) FROM room r, hotel h WHERE r.hotelno = h.hotelno AND h.hotelname = 'Grosvenor’ AND NOT EXISTS (SELECT * FROM booking b WHERE (datefrom <= ‘SYSTEM DATE’AND dateto >= ‘SYSTEM DATE’) AND b.hotelno = h.hotelno AND r.hotelno=b.hotelno AND r.roomno=b.roomno AND h.hotelname = 'Grosvenor'); 'Grosvenor'); Grouping
22. List the number of rooms in each hotel. We only need the Room table since it has the every hotel with its associated room information. We use a select statement with a “count” function and group by the hotel number to get the answer. SELECT hotelno, COUNT(roomno) FROM room GROUP BY hotelno; 23. List the number of room in each hotel in in London. We need the Room table for number of rooms and Hotel table for for hotels located in Landon. We use a select statement with a count function, a join condition, a hotel location condition, and a group by the hotel number to get the answer. SELECT r.hotelno, COUNT(roomno) FROM room r, hotel h WHERE r.hotelno=h.hotelno AND city = 'London' GROUP BY r.hotelno; 24. What is the average number of bookings for each hotel in August? We use Booking table to calculate the total bookings for each hotel by using a select statement with total function, an August August staying condition, and a group by the hotel number. We then divide the total bookings by 31 days of August to get the final answer SELECT hotelno, COUNT(hotelno)/31
6
FROM booking WHERE (datefrom <= ‘8/31/year’ AND dateto >= ‘8/1/year’) GROUP BY hotelno; 25. What is the most commonly commonly booked room type for all all hotels in London? We need the Booking table for the booked room information, Room table for the type information, and Hotel table for for the London city. We use a select statement to join these three tables together with a count function, join conditions, and city of London condition c ondition in order to generate the each type with its number of booking in the two subquerys (red or blue color). We use “WHERE y = (SELECT MAX(y) FROM” to get the maximum number of booking among all the types from the blue subquery. Then, we can select more than one type having the maximum number of booking for the solution from the red subquery that has the maximum max imum number of booking from the blue subquery SELECT type, 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) type) WHERE y = (SELECT 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 AND city = 'London' GROUP BY type); type); Subquery output table Type Count(type) T 200 D 500 Q 180 K 150 F 500 S 50 Assume that red and blue subquerys subquer ys will generate the subquery output table since the SQL statements are identical in both subquerys. The ”WHERE y = (SELECT MAX(y) FROM” will generate maximum 500 without without the type. We select the type and y that has the maximum 500 from the red subquery subquer y (containing every type with its number of booked rooms as shown in the subquery output table). The final output table is show below.
7
Final output table Type y D 500 F 500 26. What is the lost income from from unoccupied rooms at each hotel today? We use a select statement to get a list of the occupied room (currently having havi ng a gust staying in the room) of every hotel by joining the Hotel table, Room table and Booking table with a currently booking condition and group by hotel number in the inner query. Second, we use another select statement to get unoccupied room by getting every room in each hotel that does guest generated from the inner query. Finally, we get the not exist in the booked rooms with a guest total of the price for these unoccupied rooms for each hotel. SELECT r.hotelno, SUM(price) FROM room r WHERE NOT EXISTS (SELECT * FROM booking b WHERE r.roomno = b.roomno AND r.hotelno = b.hotelno AND (datefrom <= ‘SYSTEM DATE’ AND dateto >= ‘SYSTEM DATE’)) GROUP BY hotelno; Populating tables 27. Insert rows into each of these tables. We use insert statement statement to input the values into the Hotel table and Room table. You can do the other two tables. INSERT INTO hotel VALUES (‘h11’, ‘hilton’, ‘sacramento’); INSERT INTO room VALUES (‘hr1111’, ‘h11’, ‘single’, 120); 28. Update the price of all room by 5%. UPDATE room SET price = price*1.05; Employee (empNo, fName, lName, address, DOB, sex, position, deptNo) Department (deptNo, deptName, mgrEmpNo) Project (projNo, projName, deptNo) WorksOn (empNo, projNo , dateWorked, hoursWorked)
8
29. List all employees in alphabetical order of surname, and then first name. We use a select statement statement with an order by for for the Employee table to get the answer. answer. Or, we can use * to replace every column name of the Emplyee table. SELECT (empNo, fName, lName, address, DOB, sex, position, deptNo) FROM employee ORDER BY lName, fName; Or SELECT * FROM employee ORDER BY lName, fName; 30. List all the details of employees who are female. We use a select statement statement with female condition for the the Employee table to get the answer. answer. Or, we can use * to replace every column name of the Emplyee table. SELECT (empNo, fName, lName, address, DOB, sex, position, deptNo) FROM employee WHERE sex = ‘female’; Or SELECT * FROM employee WHERE sex = ‘female’; 31. List the names and addresses of all employees who are Managers. We use a select statement with manger condition cond ition for the Employee table to get the required column names. SELECT (fName, lName, address) FROM employee WHERE position = ‘Manager’; 32. Produce a list of the the names and addresses of all employees employees who work for the IT IT department. We use a select statement with department name condition and join condition for the Employee table and Department table to get the required column names. SELECT (fName, lName, address) FROM employee e, department, d
9
WHERE e.depNo = d.depNo AND depName = ‘IT’;
33. Produce a complete list of all managers who are due to to retire this year, in in alphabetical order of surname. We use a select statement with a position condition, a DOB condition and ORDER BY for the Employee table to get the answers. answers. Or, we can use * to replace replace every column name of the Emplyee table. SELECT (empNo, fName, lName, address, DOB, sex, position, deptNo) FROM employee WHERE position = “manager” AND (DOB year – system system year) >= 65 ORDER BY lName; Or SELECT * FROM employee system year) >= 65 WHERE position = “manager” AND (DOB – system ORDER BY lName; 34. Find out how many many employees are managed by “James Adams”.
We use a select statement to get a list of the department number by joining the Employee table and Department table with a name condition and a position condition in the inner query. Second, we use another select statement with a logical operator IN to get total number of emplo yees working in the same department and managed by James Adams. SELECT COUNT(empNo) FROM employee e WHERE deptNo IN (SELECT deptNo FROM employee e, WHERE fName = ‘James AND lName = ‘Adams’ AND position = ‘manager’); 35. Produce a report of the total total hours worked by each employee, arranged arranged in order of department number and within department, alphabetically by employee surname. We use a select statement to join employee table, project table, and workson table with a SUM function, GROUP BY to get department number, employee name, employee number, and total hours worked by each employee, and ORDER BY department number and last name of employee. SELECT deptNo, empNo, fName, lName, SUM(hoursWorked) FROM employee e, project p, workson w WHERE e.empNo = w.empNo AND w.projNo = p.projNo
10
GROUP BY (deptNo, empNo, fName, lName) ORDER BY deptNo, lName; 36. For each project on which more than two employees worked, list the project number, project name, and the number of employees who work on that project. We use a select statement to join project table and workson table with a COUNT function, GROUP BY and HAVING condition (more than two employees worked for that project) to get project number, project name, and number of employees. SELECT porjNo, projName, COUNT(empNo) FROM project p, workson w WHERE w.projNo = p.projNo GROUP BY (deptNo, projName) HAVING COUNT(empNo) > 2; 37. List the total number of employees in each department for those departments with more than 10 employees. Create an appropriate heading for the columns of the results results table. We use a select statement to join department table and employee table with a COUNT function, GROUP BY and HAVING condition (more than 10 employees worked for that department) d epartment) to get department number, department name, and number of employee. SELECT deptNo AS Department No, deptName AS Departmtne Name, COUNT(empNo) AS No Of Employee FROM employee e, department d WHERE e.deptNo = d.deptNo GROUP BY (deptNo, deptName) HAVING COUNT(empNo) > 10;
11
