Database PetersPan

Idea : To be better to setup and request information of a database.

Background : The exercise of PetersPan see: DomainER.htm
                        See: : https://www.w3schools.com/sql/  | https://www.codecademy.com/learn/learn-sql | http://sqlzoo.net/wiki/Main_Page

Step 1: Setup the system i.e. the Database

So before you start this exercise you need to run these two scripts:

1. Create tables MSSQL   |   Create tables MySQL
2. Insert values into the tables

When you have run these two scripts you should a database like this:

Step 2 - select from one table

1. List all information from MenuItem
    
2. List all information from MenuItem for Drinking only
    
3. List all information from MenuItem
   for Drinking only in ascending order of the price (or try descending)
    
4. List Name and Price of MenuItems
    
5. List all drinks of coffee of the MenuItems
    
6. LIst all Bookings
    
7. List all Bookings of the 16/feb 2018
    
8. List all Bookings for Lunch time i.e. between 11-14 (11AM-2PM)
    
9. LIst all Bookings of today
    
10. List all the different customers who have a booking i.e. no duplicates
     

Hint: Dates

Get the actuale date from system - use getdate()

Get the month etc. from a date use DATEPART(MONTH, <<the date>>) -- 1st parameter Year, Month, Day, Hour, Minute, Second

Get the actuale date from system - use now()

Get the month etc. from a date use MONTH(<<the date>>)-- Year, Month, Day, Hour, Minute, Second

Step 3 - Aggregate-functions

1. List the number of drinking menuItems
    
2. List the minimum,maximum and average price of all Menuitems
    
3. List the minimum,maximum and average price of all Menuitems with naming columns
   
   
4. List the minimum,maximum and average price of all Menuitems with naming columns
   For each of the menuCards
    
5. List the minimum,maximum and average price of all Menuitems with naming columns
   For each of the menuCards in a descending order of MenuCard names
    
6. List the minimum and maximum price of all Menuitems with naming columns
   For each of the TypeOfItems
    
7. List the minimum and maximum price of all Menuitems with naming columns
   For each of the TypeOfItems - But only for the Drinkings
    
8. List the number of tables and the number of seats which have a window site.