CS 3630 Database Design and Implementation

1. CS 3630 Database Design and Implementation

2. Aggregate Functions:Produce a single value from a set Count Max Min Sum Avg …

3. SQL Aggregate Functions -- Max, Min, Avg, Sum Select Max(price), Min(price), Avg(price) From room;

4. Formatting: May not working -- Formatting Col "Max Price" format $99.00 heading "Max Price" Select Max(price) "Max Price", Min(price) MinPrice, Avg(price) AvgPrice From room;

5. Formatting: Should Work -- Formatting Col MaxPrice format $99.99 heading "Max Price" Select Max(price) MaxPrice, Min(price) MinPrice, Avg(price) AvgPrice From room;

6. Formatting: Will Work! -- Formatting Col MaxPrice format $999.99 heading "Max Price" Col MinPrice format $999.99 heading "Min Price" Col AvgPrice format $999.99 heading "Avg Price" Select Max(price) MaxPrice, Min(price) MinPrice, Avg(price) AvgPrice From room;

7. SQL Aggregate Functions -- Room_No char(4) Select Max(room_no), Min(room_no) From room;

8. SQL Aggregate Functions // Room_No char(4) // No average or sum on strings! Select Avg(room_no), Sum(room_no) From room;

9. Max/Min Dates Col Min(Date_From) Format a20 Heading 'Earlist Date_From' Col Max(Date_To) Format a20 Heading 'Latest Date_To' Select Min(Date_From), Max(Date_To) From Booking;

10. Max/Min Dates: May not work col "Earliest Date from" format a20 col "Latest Date To" format a20 Select To_Char(Min(Date_From), 'Mon dd yyyy') "Earliest Date from", To_Char(Max(Date_To), 'Mon dd yyyy') "Latest Date To" From Booking;

11. Working! col EarliestDatefrom format a20 heading "Earlist Date From" col LatestDateTo format a20 heading "Latest Date To" Select To_Char(Min(Date_From), 'Mon dd yyyy') EarliestDatefrom, To_Char(Max(Date_To), 'Mon dd yyyy') LatestDateTo From Booking;

12. Count -- Count(*): number of all records -- count(hotel_no): number of Hotel_No -- Count(unique Hotel_No): -- number of distinct Hotel_No Select count(*), count(hotel_no), count(unique hotel_no) From room; -- Checking results Select * From room Order by hotel_no;

13. Count -- Does not count null values select count(*), count(address) From guest; Select * From guest;

14. Aggregate Functions withDistinct/Unique Select Avg(Price) From room; -- With Distinct Select Avg(Distinct Price) From room; -- With Distinct Select Avg(Unique Price) From room; Select * From room Order by price;

15. Aggregate Functions with Where -- With where clause -- Avg is applied after Where clause col "Average Price" format $999.00 Select Avg(Price) "Average Price" From room Where rtype = 'Family'; Select Avg(Price) "Average Price" From room Where rtype = 'Single'; Select Avg(Price) "Average Price" From room Where rtype = 'Double';

16. Aggregate Functions with Where -- With where clause -- Avg is applied after Where clause Select 'Average Price of Family rooms: ', Avg(Price) From room Where rtype = 'Family'; Pause Prompt Run time error! Select rtype, Avg(Price) From room Where rtype = 'Family';

17. Group By Clause Count, Avg Select Group By Count, Avg Count, Avg One record for each group.

18. Group By Select Avg(Price) From room Group by rtype; Select rtype, Avg(Price) From room Group by rtype;

19. Group By Clause For each hotel, display Hotel_No with the number of rooms. Select Hotel_No, Count(*) From Room Group By Hotel_No;

20. What can be selected with Group By Clause Can select Hotel_No, the group by field Can select aggregate functions on any other fields such as Max(Room_No) or Avg(Price)

21. What CANNOT be selected with Group By Clause Cannot select other fields (without aggregate function) not in the group by clause ORA-00979: not a GROUP BY expression

22. Group By Clause ? Select Group By ? ? One record for each group! What can be selected? Aggregate functions Group by fields

23. One Record for Each Group Select Hotel_no, Count(*), Max(Price) From Room Group by Hotel_No; Select Hotel_no, Room_No, rtype, Price From Room Group by Hotel_No; -- not a Group By expression

24. Group by More Than One Field Select Hotel_No, rtype, Max(Price) From Room Group By Hotel_no, rtype Order By Hotel_no, rtype

25. Order After Group by Select Hotel_no, Count(*), Max(Price) From Room Group by Hotel_No Order by Count(*);

26. Group By Clause ? Select Group By ? ? What if we don’t want all groups? Having on groups

27. Having to select groups Select Hotel_no, Count(*), Max(Price) From Room Group by Hotel_No; Select Hotel_no, Count(*), Max(Price) From Room Group by Hotel_No Having Count(*) >= 5;

28. Group By • Where before Group by to select records • Having after Group by to select groups Select Hotel_no, Count(*), Max(Price) From Room Where rtype = 'Single' Group by Hotel_No Having Max(Price) < 100;

29. Where before Group by to select records Having after Group by to select groups Let’s do it in 4 steps: From Where Group By Having Select * From Room Order by rtype; pause Select * From Room Where rtype = 'Single'; pause Select Hotel_no, Count(*), Max(Price) From Room Where rtype = 'Single' Group by Hotel_No; Pause Select Hotel_no, Count(*), Max(Price) From Room Where rtype = 'Single' Group by Hotel_No Having Max(Price) < 100;

30. Where before Group by • Having after Group by • Order by last Select Hotel_no, Count(*), Max(Price) From Room Where rtype in ('Double', 'Single') Group by Hotel_No Order by Count(*); Select Hotel_no, Count(*), Max(Price) From Room Where rtype in ('Double', 'Single') Group by Hotel_No Having Count(*) > 3 Order by Count(*) desc;

31. Group By Clause Execution Order Where (condition on records) Group by Having (condition on groups) Order by (Select)

32. Script File Style Prompt Prompt 9. Prompt For each hotel that has more than Prompt one Single room, list hotel number Prompt with number of Single rooms the hotel Prompt has and the highest price of Prompt such rooms of the hotel. Prompt Col Count(*) Format 99 heading "Single Rooms" Col Max(Price) format $999.00 heading "Max Price" Select Hotel_no, Count(*), Max(Price) From Room Where rtype = 'Single' Group by Hotel_No Having Count(*) > 1 Order by Count(*); pause

33. Assignment 8 Due Friday, April 5 by 11 pm Should work for any table instance Run against my tables Should insert more records to test (Don’t include insert commands!)

34. Phase II Due Monday, April 8

35. Quiz 3 • Wednesday, April 10 • Run Script file to Drop/Create tables, insert records • Create Script file to query database • Hands-on: in Lab 206 • Open Computer • Canvas