6) Consider the relationships describing the sales of a business

In this diagram, you will notice that:

The Exp field of the ORDERS table means Shipped. In other words, if the order has been shipped, this field will automatically be calculated as (Y) ui or (N) on. It is not physically stored in the base. The calculation is done by looking to see if a sending date is present: if so, then the sending has been made, so Exp = O and if not, Exp = N.

Each customer can have more than one phone number.

The Name field refers to the person's last name.

Remember to define the primary keys correctly.

Create the corresponding relational schema.

7) Using the relationships from question 6, create the appropriate SQL queries.

Write a SQL query to get the names of customers and their phone number whose phone number starts with 209.

8) Using the relationships from question 6, create the appropriate SQL queries.

Write a SQL query to get the customer names and order number that were not sent.

9) Using the relationships from question 6, create the appropriate SQL queries.

Write a SQL query to obtain the following information:

-Client name

-Phone number

-Order number

-Date received of the order

-Date of dispatch of the order

-Name of the seller

10) Using the relationships from question 6, create the appropriate SQL queries.

Order number 10230 was dispatched on 2009-11-15. Write a SQL query to add the date this order was sent.

11) Using the relationships from question 6, create the appropriate SQL queries.

Customer "Robert Smith" places an order with employee "Fred NcFaddeb". This order is received on 2009-12-01 and shipped on 2009-12-15. Write a SQL query to insert this new sale into the database.

noComm 10200 COMMANDES noClient noEmpl Exp dateRecu dateEnvoi 23511 P0239400 o 09-10-18 09-10-20 23511 P0239400 o 09-10-29 09-10-31 10210 10220 23512PO239401 o 09-11-10 09-11-13 10230 23513 P0239402 N 09-11-02 10240 N 09-11-16 23511 PO239400 23512PO239401 11022 o 09-11-10 09-11-13 DateEmbauche EMPLOYES noEmpl Prenom Nom PO239400 Jones Hoffer PO239401 Jeffrey Prescott PO239402 Fred NcFaddeb 00-12-12 06-01-01 TEL_CLIENTS no Client Type Numero 23511 Maison 360-636-5555 23512 Cell 312-678-5555 23513 Cell 312-883-7337 08-09-01 209-832-1221 23514 Bureau 23515 Cell 360-458-9878 23516 Maison 209-546-7299 209-638-2712 CLIENTS noClient Prenom Nom adresse Client 23511 Michelle Kuan 123 Main St. 23512 George Myer 237 Ash Ave. 23513 Richard Gold 111 Inwood St. 23514 Robert Smith 54 Gate Dr. 23515 Christopher David 777 Loto St. 23516 Adam Beethoven 234 Park Rd.. 23517 Lidwig Bach 5790 Walnut St. 23517 Bureau 23512 Maison 23513 Maison 506-555-1212 506-555-3233 23514 Maison 333-412-1232 noComm 10200 COMMANDES noClient noEmpl Exp dateRecu dateEnvoi 23511 P0239400 o 09-10-18 09-10-20 23511 P0239400 o 09-10-29 09-10-31 10210 10220 23512PO239401 o 09-11-10 09-11-13 10230 23513 P0239402 N 09-11-02 10240 N 09-11-16 23511 PO239400 23512PO239401 11022 o 09-11-10 09-11-13 DateEmbauche EMPLOYES noEmpl Prenom Nom PO239400 Jones Hoffer PO239401 Jeffrey Prescott PO239402 Fred NcFaddeb 00-12-12 06-01-01 TEL_CLIENTS no Client Type Numero 23511 Maison 360-636-5555 23512 Cell 312-678-5555 23513 Cell 312-883-7337 08-09-01 209-832-1221 23514 Bureau 23515 Cell 360-458-9878 23516 Maison 209-546-7299 209-638-2712 CLIENTS noClient Prenom Nom adresse Client 23511 Michelle Kuan 123 Main St. 23512 George Myer 237 Ash Ave. 23513 Richard Gold 111 Inwood St. 23514 Robert Smith 54 Gate Dr. 23515 Christopher David 777 Loto St. 23516 Adam Beethoven 234 Park Rd.. 23517 Lidwig Bach 5790 Walnut St. 23517 Bureau 23512 Maison 23513 Maison 506-555-1212 506-555-3233 23514 Maison 333-412-1232