Login in to your database on the database server. Refer to the How To documents on D$2$L under the content section if you forgot the process. You will need a New Query window to run your commands. Select New Query from the Menu. Verifying you have no tables in your database $*$ Right Click on your database $($your login name in the Object Explorer$)$ and click Refresh $*$ Expand your database $($your login name$)*$ Expand Tables $*$ You should not have any tables listed there $($just some yellow folders that the system uses$)$ Part $1($Setup $-15$ pts$)1.$ Using the MLC database structure write the following queries using subqueries or joins as necessary. $2.$ The MLC database is located on D$2$L in the Assignment section and you should run it before you start the questions below $=15$ Points. Part $2($Sub Query ONLY $-$ NO JOINS or multiple tables on the FROM line $-25$ pts$)4.()/()*$ First write a query that shows only the unique officecodes that are in USA$(*)/()=10$ Points $5.()/()*$ Then write a query that shows all employees in those office codes by using a WHERE clause with the results of the specific office codes from Question $4(*)/()=10$ Points $6.()/()*$ Leave queries from question $4$ and $5$ unmodified. Copy the query from question $5$ here and copy the query from question $4$ and place it between the $()$ from Question $5$ to create your first sub query$(*)/()=5$ Points Part $3(45$ pts $-$ JOINS$)7.()/()*$ Show all items ordered $($include the product name$),$ sort results by orderDate $-(*)/()=15$ Points $8.()/()*$ Show items ordered, for the month of January $2003(*)/()=10$ Points $9.()/()*$ Show payments by customer name include payment amount and date$(*)/()=10$ Points $10.()/()*$ Show total payments by customer name$(*)/()=10$ Points $11.()/()*$ Extra Credit lookup how to format money so it look like $ $12,456\mathrm{.}00$ and include that in question $10(*)/()=5$ Points Part $4(15$ pts $-$ Test & Submt$)12.()/()*$ Test your query and submit only the code you wrote. Do not forget your honor statement. Be sure to download your submission and test it$.(*)/()=15$ Points IT $240-$ ASSIGNMENT $6-$ SUBQUERY & JOINS As noted below you should be turning in one file that contains all the necessary SQL commands to create the tables and insert data in those tables, and the necessary select commands to prove your tables have data. This will be about $100$ total lines of code. The grade will be running your code and checking it is correct. You should write your code in short parts $($like one CREATE table at a time$)$ and test it$.$ After that works, write one INSERT line for the first table, test it$,$ and write the next INSERT lines, and test. Remember you may want to drop $($erase$)$ your tables to test your work. The syntax for DROP is: DROP TABLE IF EXISTS $\{$TABLE NAME$\}$ ;

MLC File has over $1,000$ characters

Empty Answer Sheet.sql $-$ Edited

#$/*$ Subquery $-$ First write a query that shows unique officecodes that are in USA $*/$

$/*$ Then write a query that shows all employees in that office code $*/$

$/*$ Now substitute the query between the $()$

show all employees that work in USA$*/$

$/*$ Show items order, sort by orderDate $*/$

$/*$ Show items order, for the month of January $2003*/$

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

$/*$ Show total payments by customer name $-$

Extra Credit to lookup format so it look like $ $12,456\mathrm{.}00*/$

MLC Customs Database Diagram

offices

$*$ officeCode city phone addressLine$1$ addressLine$2$ state country postalCode territory

productName

productLine

productScale

productVendor

productDescription quantitylnStock buyPrice MSRP