This SQL question involves writing SELECT and DML statements involving a single table.

The SQL should be written using the standard coding style:

all keywords are to be in upper case

all user-defined names are to be in lower case (example: table and field names)

there should be a carriage return before each major part of the SQL statement (i.e. before SELECT, FROM, WHERE and ORDER BY)

Example Answer:

SELECT columns

FROM tables

WHERE conditions

ORDER BY column1, column2;

____________________________________________________________________________________

Display the data for all offices. Display office code, city, state, country and phone for all offices.

____________________________________________________________________________________

Display employee number, first name, last name, and extension for all employees whose office code is 1. Sort the result based on the employee number.

____________________________________________________________________________________

Display customer number, customer name, contact first name, contact last name and phone for all customers in Paris. (hint: be wary of case sensitivity) Sort the result based on the customer number.

____________________________________________________________________________________

Repeat the previous Query with a couple of small changes:

- The contact's first and last name should be in a single column in the below format

lastname, firstname

- Show customers who are in Canada

- Sort the result based on the customer name.

OFFICECODE CITY STATE COUNTRY PHONE 11 San Francisco CA USA +1 650 219 4782 Boston MA USA +1 215 837 0825 33 NYC NY USA +1 212 555 3000 44 Paris (null) France +33 14 723 4404 Tokyo Chiyoda-Ku Japan +81 33 224 5000 66 Sydney (null) Australia +61 2 9264 2451 77 London (null) UK +44 20 7877 2041 55 1 2 3 EMPLOYEENUMBER FIRSTNAME LASTNAME EXTENSION 1002 Diane Murphy x5800 1056 Mary Patterson x4611 1076 Jeff Firrelli x9273 1143 Anthony Bow x5428 1165 Leslie Jennings x3291 1166 Leslie Thompson X4065 4 5 6 1 CUSTOMER NUMBER CUSTOMERNAME CONTACTFIRSTNAME CONTACTLASTNAME PHONE 172 La Corne 'abondance, Co. Marie Bertrand (1) 42.34.2555 250 Lyon Souveniers Daniel Da Silva +33 1 46 62 7555 406 Auto Canal+ Petit Dominique Perrier (1) 47.55.6555 2 3 1 2 3 Customer Number Customer Name Contact Name Phone 202 Canadian Gift Exchange Network Tamuri, Yoshi (604) 555-3392 233 Qubec Home Shopping Network Fresnire, Jean (514) 555-8054 260 Royal Canadian Collectables, Ltd. Lincoln, Elizabeth (604) 555-4555 OFFICECODE CITY STATE COUNTRY PHONE 11 San Francisco CA USA +1 650 219 4782 Boston MA USA +1 215 837 0825 33 NYC NY USA +1 212 555 3000 44 Paris (null) France +33 14 723 4404 Tokyo Chiyoda-Ku Japan +81 33 224 5000 66 Sydney (null) Australia +61 2 9264 2451 77 London (null) UK +44 20 7877 2041 55 1 2 3 EMPLOYEENUMBER FIRSTNAME LASTNAME EXTENSION 1002 Diane Murphy x5800 1056 Mary Patterson x4611 1076 Jeff Firrelli x9273 1143 Anthony Bow x5428 1165 Leslie Jennings x3291 1166 Leslie Thompson X4065 4 5 6 1 CUSTOMER NUMBER CUSTOMERNAME CONTACTFIRSTNAME CONTACTLASTNAME PHONE 172 La Corne 'abondance, Co. Marie Bertrand (1) 42.34.2555 250 Lyon Souveniers Daniel Da Silva +33 1 46 62 7555 406 Auto Canal+ Petit Dominique Perrier (1) 47.55.6555 2 3 1 2 3 Customer Number Customer Name Contact Name Phone 202 Canadian Gift Exchange Network Tamuri, Yoshi (604) 555-3392 233 Qubec Home Shopping Network Fresnire, Jean (514) 555-8054 260 Royal Canadian Collectables, Ltd. Lincoln, Elizabeth (604) 555-4555