21. A pizza shop manages its customer orders in a database with three tables called Customers,...

 

Transcribed Image Text:

21. A pizza shop manages its customer orders in a database with three tables called Customers, Customer Order and Suburbs which have the following schemas: Customers (id INT, name VARCHAR (32)) Suburbs (code CHAR(4), name VARCHAR(32)) Customer Order (cid INT, pickup_code CHAR(4), cost double, order date DATETIME NOT NULL DEFAULT (GETDATE()) The field Customers. id represents a customer number, the field Suburbs.code represents a suburb's area code (such as 6009 for Nedlands), and these two fields are keys for their tables respectively. The fields cid and pickup_code in Customer Order are foreign keys to Customers. id and Suburbs.code. Assume every suburb only has one such pizza store and the table Suburbs has the full data, i.e., all records like (6009, Nedlands) existed in the tables. (a) Consider a new customer named John Smith ordered a few pizza with $30 and wanted to pick up from a suburb 6009. Write two SQL statements with the correct sequence that will add the customer and his order into the database, giving him the customer number 1000. [3 marks] (b) Write a SQL statement that will list each suburb's name and the total amount of sales the pizza shop did on the suburb. The list should be sorted in descending order using DESC 21. A pizza shop manages its customer orders in a database with three tables called Customers, Customer Order and Suburbs which have the following schemas: Customers (id INT, name VARCHAR (32)) Suburbs (code CHAR(4), name VARCHAR(32)) Customer Order (cid INT, pickup_code CHAR(4), cost double, order date DATETIME NOT NULL DEFAULT (GETDATE()) The field Customers. id represents a customer number, the field Suburbs.code represents a suburb's area code (such as 6009 for Nedlands), and these two fields are keys for their tables respectively. The fields cid and pickup_code in Customer Order are foreign keys to Customers. id and Suburbs.code. Assume every suburb only has one such pizza store and the table Suburbs has the full data, i.e., all records like (6009, Nedlands) existed in the tables. (a) Consider a new customer named John Smith ordered a few pizza with $30 and wanted to pick up from a suburb 6009. Write two SQL statements with the correct sequence that will add the customer and his order into the database, giving him the customer number 1000. [3 marks] (b) Write a SQL statement that will list each suburb's name and the total amount of sales the pizza shop did on the suburb. The list should be sorted in descending order using DESC