1. Using the MLC database structure write the following queries using subqueries or joins as necessary.

2. The MLC database is located on SQLDatabase and you should run it before you start the assignment.

3. You will need to submit a .sql (text file) that includes your queries below (do not include the original MLC database code). Making a Sub Query (only use a Sub Query to answer question 6 no joins)

4. /* First write a query that shows unique officecodes that are in USA */

5. /* Then write a query that shows all employees in those office codes by using a WHERE clause with the specific office codes in Question 4 */

6. /* Now substitute the query from Question 4 between the () in Question 5 to create a sub query */ Join Queries (only use a join not a subquery to answer these questions)

7. /* Show all items ordered (include product name), sort by orderDate - */

8. /* Show items ordered, for the month of January 2003 */

9. /* Show payments by customer name include payment amount and date */

10. /* Show total payments by customer name - Extra Credit lookup how to format so it look like total payments look like $ 12,456.00 */

DROP TABLE IF EXISTS ORDERDETAILS; DROP TABLE IF EXISTS PAYMENTS; DROP TABLE IF EXISTS PRODUCTS; DROP TABLE IF EXISTS PRODUCTLINES; DROP TABLE IF EXISTS ORDERS; DROP TABLE IF EXISTS CUSTOMERS; DROP TABLE IF EXISTS EMPLOYEES; DROP TABLE IF EXISTS OFFICES;

DROP TABLE IF EXISTS customers;

CREATE TABLE customers ( customerNumber INT NOT NULL, customerName varchar(50) NOT NULL, contactLastName varchar(50) NOT NULL, contactFirstName varchar(50) NOT NULL, phone varchar(50) NOT NULL, addressLine1 varchar(50) NOT NULL, addressLine2 varchar(50) DEFAULT NULL, city varchar(50) NOT NULL, state varchar(50) DEFAULT NULL, postalCode varchar(15) DEFAULT NULL, country varchar(50) NOT NULL, salesRepEmployeeNumber INT DEFAULT NULL, creditLimit decimal(10,2) DEFAULT NULL, PRIMARY KEY (customerNumber) );

/*Data for the table customers */

insert into customers(customerNumber,customerName,contactLastName,contactFirstName,phone,addressLine1,addressLine2,city,state,postalCode,country,salesRepEmployeeNumber,creditLimit) values

(103,'Atelier graphique','Schmitt','Carine ','40.32.2555','54, rue Royale',NULL,'Nantes',NULL,'44000','France',1370,'21000.00'),

(112,'Signal Gift Stores','King','Jean','7025551838','8489 Strong St.',NULL,'Las Vegas','NV','83030','USA',1166,'71800.00'),

(114,'Australian Collectors, Co.','Ferguson','Peter','03 9520 4555','636 St Kilda Road','Level 3','Melbourne','Victoria','3004','Australia',1611,'117300.00'),

(119,'La Rochelle Gifts','Labrune','Janine ','40.67.8555','67, rue des Cinquante Otages',NULL,'Nantes',NULL,'44000','France',1370,'118200.00'),

(121,'Baane Mini Imports','Bergulfsen','Jonas ','07-98 9555','Erling Skakkes gate 78',NULL,'Stavern',NULL,'4110','Norway',1504,'81700.00'),

(124,'Mini Gifts Distributors Ltd.','Nelson','Susan','4155551450','5677 Strong St.',NULL,'San Rafael','CA','97562','USA',1165,'210500.00'),

(125,'Havel & Zbyszek Co','Piestrzeniewicz','Zbyszek ','(26) 642-7555','ul. Filtrowa 68',NULL,'Warszawa',NULL,'01-012','Poland',NULL,'0.00'),

(128,'Blauer See Auto, Co.','Keitel','Roland','+49 69 66 90 2555','Lyonerstr. 34',NULL,'Frankfurt',NULL,'60528','Germany',1504,'59700.00'),

(129,'Mini Wheels Co.','Murphy','Julie','6505555787','5557 North Pendale Street',NULL,'San Francisco','CA','94217','USA',1165,'64600.00'),

(131,'Land of Toys Inc.','Lee','Kwai','2125557818','897 Long Airport Avenue',NULL,'NYC','NY','10022','USA',1323,'114900.00'),

(141,'Euro+ Shopping Channel','Freyre','Diego ','(91) 555 94 44','C/ Moralzarzal, 86',NULL,'Madrid',NULL,'28034','Spain',1370,'227600.00'),

(144,'Volvo Model Replicas, Co','Berglund','Christina ','0921-12 3555','Berguvsvgen 8',NULL,'Lule',NULL,'S-958 22','Sweden',1504,'53100.00'),

(145,'Danish Wholesale Imports','Petersen','Jytte ','31 12 3555','Vinbltet 34',NULL,'Kobenhavn',NULL,'1734','Denmark',1401,'83400.00'),

N AICriutromenataharon Niarram