OSCIA Web Developer Candidate

SQL Skills

Instructions

Use the credentials you were provided to connect to the Azure SQL server hosted at:

osciasql.database.windows.net

You can use Microsoft SQL Server Management Studio, or an alternate tool of your choice. Your user account has access only to the database provided (and not the server master), so the database name must be provided when connecting. In SSMS it is set under:

Options >> Connection Properties >> Connect to database

You have been provided with an ERD diagram of the database’s schema for your reference. Your user’s default schema is SalesLT, so qualifying your queries’ table names with that schema should not be necessary.

Save your answers to a text, Word or PDF document and upload using the form below. Don’t forget to enter your name and email address before you submit the form.

If you find any of the assignment tasks ambiguous, just note any assumptions you made while performing them.

Questions

Name

1. Write a query to list the company’s four main product categories in alphabetical order.

2. Write a query to lst the names of all customer companies that have yet to make a purchase.

3. Write a query to list total sales by main product category.

Note: You can ignore any discount considerations, just use sales line totals to generate sales figures

4. You notice an issue with possible duplicate data in the customer table.

How would you suggest resolving this issue?
Are there any advantages or disadvantages to the method you chose?
Actually resolving the issue is optional