project phase i phase ii due monday, april 15 groups 1
TRANSCRIPT
![Page 1: Project Phase I Phase II Due Monday, April 15 Groups 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d745503460f94a54461/html5/thumbnails/1.jpg)
Project
• Phase I
• Phase II
• Due Monday, April 15
• Groups
1
![Page 2: Project Phase I Phase II Due Monday, April 15 Groups 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d745503460f94a54461/html5/thumbnails/2.jpg)
Joins
• Retrieve data from two or more tables• Common Attributes PK and FK Other attributes• Based on Cartesian Product • Implementation Nested loops Optimization
2
![Page 3: Project Phase I Phase II Due Monday, April 15 Groups 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d745503460f94a54461/html5/thumbnails/3.jpg)
JoinsList all guests who have at least one booking.(We keep all guests even they don’t have any
bookings.)
-- Which table(s)?-- Guest (Guest_no, Guest_Name, Address)-- PK: Guest_no-- Booking (HoteL_no, Guest_no, ...)-- PK: Hotel_no, Guest_No, Date_From-- FK: Guest_no references Guest
Select Guest.* From Guest, BookingWhere Guest.Guest_no = Booking.Guest_no;
-- Run in Oracle-- Duplicate records
3
![Page 4: Project Phase I Phase II Due Monday, April 15 Groups 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d745503460f94a54461/html5/thumbnails/4.jpg)
Joins
List all guests who have at least one booking.
Select Guest.* From Guest, BookingWhere Guest.Guest_no = Booking.Guest_no;
Why duplicate records?
For every g in Guest For every b in Booking If g.Guest_no = b.Guest_no Then Select g.*
4
![Page 5: Project Phase I Phase II Due Monday, April 15 Groups 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d745503460f94a54461/html5/thumbnails/5.jpg)
Joins
List all guests who have at least one booking.Remove duplicate records!
Select Distinct Guest.* From Guest, BookingWhere Guest.Guest_no = Booking.Guest_no;
-- Could use Unique
5
![Page 6: Project Phase I Phase II Due Monday, April 15 Groups 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d745503460f94a54461/html5/thumbnails/6.jpg)
Joins: New Style
Select Distinct Guest.* From GuestJoin Booking on Guest.Guest_no = Booking.Guest_no;
Select Distinct Guest.* From Guest, BookingWhere Guest.Guest_no = Booking.Guest_no;
We should use the new style!
6
![Page 7: Project Phase I Phase II Due Monday, April 15 Groups 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d745503460f94a54461/html5/thumbnails/7.jpg)
Using Short Table Names
List all guests who have at least one booking.
Select Distinct G.* From Guest GJoin Booking B on G.Guest_no = B.Guest_no;
-- Cannot use the original table name any moreSelect Distinct Guest.* From Guest GJoin Booking B on Guest.Guest_no = B.Guest_no
ERROR at line 1:ORA-00904: "GUEST"."GUEST_NO": invalid identifier
7
![Page 8: Project Phase I Phase II Due Monday, April 15 Groups 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d745503460f94a54461/html5/thumbnails/8.jpg)
JoinsList all guests who have at least one bookingwith the details of each booking.
-- No need for Distinct-- All columns from both tables Select *From Guest GJoin Booking B on G.Guest_no = B.Guest_no;
Set linesize 100Col Guest_name Format a14Col address format a21
8
![Page 9: Project Phase I Phase II Due Monday, April 15 Groups 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d745503460f94a54461/html5/thumbnails/9.jpg)
JoinsWhat if no common attribute is specified?
Select G.* From Guest G, Booking B;-- Where G.Guest_no = B.Guest_no;-- Cartesian product!
Select Distinct G.* From Guest GJoin Booking B;-- on G.Guest_no = B.Guest_no;
ERROR at line 3:ORA-00905: missing keyword
9
![Page 10: Project Phase I Phase II Due Monday, April 15 Groups 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d745503460f94a54461/html5/thumbnails/10.jpg)
Join not on PK/FK
List all guests who are in a city where there is also a hotel.
Assuming Address is City.
Select Distinct G.* From Guest GJoin Hotel H on G.Address = H.Address;
10
![Page 11: Project Phase I Phase II Due Monday, April 15 Groups 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d745503460f94a54461/html5/thumbnails/11.jpg)
Joins: Three Tables List Hotel name, guest name and date_fromfor all bookings, sorted by hotel_no in ascending order and then by guest_no in descending order.Which tables? Hotel Name: Hotel Guest Name: Guest Date_From : Booking
Select H.name, G.Guest_name, Date_from From Hotel HJoin Booking B on H.Hotel_no = B.Hotel_no Join Guest G on G.Guest_no = B.Guest_noOrder By H.Hotel_No, G.Guest_no desc;-- H.Hotel_No, G.Guest_no not selected
11
![Page 12: Project Phase I Phase II Due Monday, April 15 Groups 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d745503460f94a54461/html5/thumbnails/12.jpg)
Joins: Three Tables -- List Hotel name, guest name and date_from-- for all bookings.-- Sort the result
Select H.name, G.Guest_name, Date_from From Hotel HJoin Booking B on H.Hotel_no = B.Hotel_no Join Guest G on G.Guest_no = B.Guest_noOrder By H.name, G.Guest_name, Date_from;-- Sort by name
Select H.name, G.Guest_name, Date_from From Hotel HJoin Booking B on H.Hotel_no = B.Hotel_no Join Guest G on G.Guest_no = B.Guest_noOrder By H.Hotel_No, G.Guest_no;-- Sort by Hotel_No 12
![Page 13: Project Phase I Phase II Due Monday, April 15 Groups 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d745503460f94a54461/html5/thumbnails/13.jpg)
Joins with Group By
-- For each hotel, display Hotel name and number of -- bookings of the hotel.-- Tables: -- Hotel, Booking
Select name, count(*) From Hotel HJoin Booking B on H.Hotel_no = B.Hotel_no Group by H.Hotel_No;
-- Will it work?
13
![Page 14: Project Phase I Phase II Due Monday, April 15 Groups 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d745503460f94a54461/html5/thumbnails/14.jpg)
Joins with Group By
-- For each hotel, display Hotel name and number of -- bookings of the hotel.-- Tables: -- Hotel, Booking
-- Must also Group by nameSelect name, count(*) From Hotel HJoin Booking B on H.Hotel_no = B.Hotel_no Group by H.Hotel_No, name;
-- What if group by name?
14
![Page 15: Project Phase I Phase II Due Monday, April 15 Groups 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d745503460f94a54461/html5/thumbnails/15.jpg)
Joins with Group By -- For each hotel, display Hotel name and -- number of bookings of the hotel.
Select name, count(*) From Hotel HJoin Booking B on H.Hotel_no = B.Hotel_no Group by H.Hotel_No, name;
// Missing hotels without bookings// How to display a zero for such hotels?
15
![Page 16: Project Phase I Phase II Due Monday, April 15 Groups 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d745503460f94a54461/html5/thumbnails/16.jpg)
Current Year with Assumption-- For each hotel, display Hotel name and -- number of bookings of the hotel for this year.-- Assume no booking is longer than 1 year.
Select name, count(*) From Hotel HJoin Booking B on H.Hotel_no = B.Hotel_no and ((to_char(Date_From, 'yyyy') = to_char(SysDate, 'yyyy')) or (to_char(Date_To, 'yyyy') = to_char(SysDate, 'yyyy')))Group by H.Hotel_No, name;
-- could use where after join condition-- they are the same
16
![Page 17: Project Phase I Phase II Due Monday, April 15 Groups 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d745503460f94a54461/html5/thumbnails/17.jpg)
Current Year without Assumption-- For each hotel, display Hotel name and -- number of bookings of the hotel for this year.-- No assumptions.
Select name, count(*) From Hotel HJoin Booking B on H.Hotel_no = B.Hotel_no and ((to_char(Date_From, 'yyyy') = to_char(SysDate, 'yyyy')) or (to_char(Date_To, 'yyyy') = to_char(SysDate, 'yyyy')) or ((to_char(Date_From, 'yyyy') < to_char(SysDate, 'yyyy')) and (to_char(Date_To, 'yyyy') > to_char(SysDate, 'yyyy'))))Group by H.Hotel_No, name;
17
![Page 18: Project Phase I Phase II Due Monday, April 15 Groups 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d745503460f94a54461/html5/thumbnails/18.jpg)
Current Year without Assumption-- For each hotel, display Hotel name and -- number of bookings of the hotel for this year.-- No assumptions.
Select name, count(*)From Hotel HJoin Booking B on H.Hotel_no = B.Hotel_no and to_char(Date_From, 'yyyy') <= to_char(SysDate, 'yyyy') and to_char(Date_To, 'yyyy') >= to_char(SysDate, 'yyyy')Group by H.Hotel_No, name;
-- How to make it work for the current month?-- use ‘yyyy mm’ instead of ‘yyyy’
18
![Page 19: Project Phase I Phase II Due Monday, April 15 Groups 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d745503460f94a54461/html5/thumbnails/19.jpg)
Group By and Having -- For each hotel with at least 3 bookings this -- year, display Hotel name and the number of -- bookings of the hotel this year.-- With assumption
Select name, count(*)From Hotel HJoin Booking B on H.Hotel_no = B.Hotel_no and (to_char(sysDate, 'yyyy') = to_char(date_to, 'yyyy') or to_char(sysDate, 'yyyy') = to_char(date_from,'yyyy'))Group by H.Hotel_No, nameHaving Count(*) >= 3;
19
![Page 20: Project Phase I Phase II Due Monday, April 15 Groups 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d745503460f94a54461/html5/thumbnails/20.jpg)
Group By and Having
-- For each hotel with more than 5 bookings, -- display Hotel name and the number of -- bookings of the hotel.
Select name, count(*)From Hotel HJoin Booking B on H.Hotel_no = B.Hotel_no Group by H.Hotel_No, nameHaving Count(*) > 5;
20
![Page 21: Project Phase I Phase II Due Monday, April 15 Groups 1](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d745503460f94a54461/html5/thumbnails/21.jpg)
Schedule
• Assignment 9: Join
Due Wednesday, April 24
• Quiz 3
Friday, April 19
No Join
Assignment 7 & 8
21