INFO$-3233$ Practice$$

Instructions

$1.$Rename the Atlanta HomePro database with your last name.

$2.$Create a Microsoft Word file, name it with your last name.

$3.$The only software you need to complete this exam are MS Access and MS Word. Both are available in Apporto if you do not have them installed in your personal computer.

$4.$Create SQL queries that follow these guidelines:

$-$Save each query with the name at the beginning of the question, e$.$g$.,$ Problem$\_5.$

$-$Capitalize all SQL words or at least the first letter $($you may use the mixed case for column names$)$

$-$Begin each SQL clause on a new line and indent it properly

$-$Use appropriate aliases for the headings of computed or summary columns

$-$Avoid the use of unnecessary parentheses

$-$Return only the required data

$5.$Submission:

$-$If you are not able to save your query in Access or if it does not run properly, save your query or SQL statement in the Word file created in number $2$ above.

$-$If all the queries can be run without any error, submit only Access file.

$-$Make sure you submit the correct file. Grade will be based on the latest file you uploaded before the assignment is closed.

$-$Note: to save a query in MS Access, right click on a default name $($e$.$g$.,$ Query $1)$ and select Save

Problem$\_1.$ Return employee name and aggregate sales amount with total sales amount to date greater than $25,000.(20$ pts$)$

Problem$\_2.$ List the product id and description of all the products $($no duplicates$)$ that have been sold. Consider only brands ID $30,31$ and $32.(20$ pts$)$

Problem$\_3.$ Return the number of different products $($not how many sales$)$ sold by LE MODE brand. Include in the query output brand id$,$ brand name, count number of distinct products sold. $(20$ pts$)$

Problem$\_4.$ Determine the department name, department manager, and department phone number of HELEN TRUJILLO, employee number $83465.$ Include in the query output Helen$$s last name and number and her manager$$s last name, department, and phone. $(20$ pts$)$

Problem$\_5.$ Create a new table named TRAINING with the following structure. $(20$ pts$)$

TRAINING$\_$ID AutoNumber Must have a unique value, Primary Key

TRAINING$\_$DESC Text Size $100,$ must have a value

TRAINING$\_$COST Currency

TRAINING$\_$PROVIDER Text Size $50$

Problem$\_6.$ Insert into the TRAINING table created above a record with TRAINING$\_$ID equal to $1($even though it is AutoNumber you set its value$)$ for an $$Microsoft Power BI$$ that costs $$50$ provided by Charlotte Data Academy. $(20$ pts$)$

Problem$\_7.$ With a single query, create a new table named NCCUSTOMER containing the CUST$\_$FNAME, CUST$\_$LNAME, CUST$\_$STREET, CUST$\_$CITY, CUST$\_$STATE, CUST$\_$ZIP from CUSTOMER table for those who live in NC$.(20$ pts$)$

Problem$\_8.$ List the vendor who have supplied at least $30$ different products of type $$Interior$.$ Return the vendor number, vendor name, and number of unique $$Interior$$ products supplied. $(30$ pts$)$

Problem$\_9.$ List department name, manager name and email, and number of employees of departments with more than $45$ employees. $(25$ pts$)$

Problem$\_10.$ List Primer category products name supplied by only one vendor. $(30$ pts$)$

Bonus: Also list the supplier who delivered them $(5$ points$)$

can you please give me exact steps on how to do this on MS access. Thank you