Lab 4

I will assess you in 2 rounds.

Round 1

• I will visit each group for 5 minutes
  
• Pitch to me your DB design
• Unique design features
• Strong selling points
• Why should I buy your DB?

Round 2

• Each group should start working on the following queries as soon as possible
  

• save the query in a text file
• try out the query to make sure it works
• when ALL queries have been tested, email me askychang@ntu.edu.sg and print out the query & console text message results (no need to do screen capture, just text copy and paste) and let me know, I will come over to have a look at your queries and results on your screen in the order of your submitted results.
• A part of your grade will depend on how quickly your group can come up with the queries, and also if you can come up with correct results.
• Your SQL may
• include views
• NOT explicity reference primary keys, e.g., you may not delete a record by directly specifying the primary key of that record after you browse the table. E.g. if I ask you to delete the top driver, and after browsing the table, you figure out the top driver is Sim Ah Beng with NRIC 12345, you may not execute DELETE Sim Ah Beng with NRIC 12345 directly. Instead, you must use the result of your previous queries to arrive at the deletion of SIM Ah Beng
• Copy and paste the results of each query at the end of the queries, e.g. your printout should be in the format:

  Group [number=?]
  
  Query 1:
  
  CREATE VIEW FROM ....;
  SELECT * FROM ....;
  ...
  
  Console text output including error messages
  ....
  
  Query 2:
  
  CREATE VIEW FROM ....;
  SELECT * FROM ....;
  ...
  
  Console text output including error messages
  
  ....
  
  
  

Queries

1. List in descending order of #trips, all customers [Name, phone, #trips]
   
2. List in descending order of #trips, all drivers [Name, NRIC, status (master or slave driver), #trips]
   
3. List in descending order of #street pick-ups all drivers [Name, NRIC, status (master or slave driver), #street pick-ups, #ordered trips] who have more street pick-ups than ordered trips.
4. Change the status of your top driver in result of query 2 from master to slave to master, or slave to master to slave, depending on whether he is currently a master or slave. List down all staff records of that driver.
5. Set the status of your top driver in query 2 to master (if already master, skip) and add a slave driver (if none exist, skip, otherwise)
6. Delete this top driver in query 5.
   
7. List in descending order of # complaints all drivers with complaints [Name, NRIC, status (master or slave driver), #complaints]
8. Remove the most recent 2 complaints from your Top complained driver (i.e. with 20 complaints). After removal, he should now have 18 complaints.
9. Add 3 complaints to your Top complained driver (could be different from driver in query 8)
   
10. Let the top complained driver (with 20 complaints) rent a car, i.e., add a rental record for the top complained driver. (first convert his status to master driver if he is not a master driver)