We Helped With This Database Programming Homework: Have A Similar One?

CIS 487 – Database Programming                                                                              Midterm Part 2

Please use the NorthwindTablesAndData.sql file to populate an empty database with tables and data. All problems except #1 and #2 are based on Northwind. Each query is worth 12.5 points.  Not all queries are the same level of difficulty. This is intentional. I would appreciate it if you separate your answers with a comment containing the problem number.

You will receive a zero if you do not turn in a single .sql file containing each solution for this part of the midterm.

1.      Write a query to display the current date and time on the database server in ISO8601 format. The title of the result should be ISO8601 Date. Do not use a FROM clause.

2.      Given the variable definition below

DECLARE @Data VARCHAR(100) = 'Computer science is no more about computers than astronomy is about telescopes.'

Use the variable in a SELECT to display the following:

* astronomy is about telescopes.*

You may use only three literals in your query: the digit 1, '*' and 'than '. 

After @Data is declared, treat it just like a column, such as:

DECLARE @Data VARCHAR(100) ) = 'Computer science is no more about computers than astronomy is about telescopes.'

SELECT @Data

3.      Using the Northwind database provided in the sql script, display all of the products and their categories.  The display must have the category name first and the product name second. Sort the results alphabetically by category. Within a category, sort the product names alphabetically. If a product doesn’t have a category, it must not show up in the results.

4.      Using the Northwind database, show all the order ids, order dates and the company name that shipped the order. Display the results in order of order id. If an order doesn’t have a company that shipped it, do not display the order.

5.      Show all the employees with their names in ‘last, first’ format and who they report to using ‘last, first’ format. If they don’t report to anyone, show their supervisor as “No one.” Here are the required results. Hint: Alias the Employee table as Supervisor

CIS 487 – Database Programming                                                                              Midterm Part 2

6.      Show all the territory descriptions and the employees assigned to those territories. Display the territory even if there are not employees associated with that territory. For those territories without any employees, show the employee name as “No employees.”  Sort the results in ascending order by territory and by employee name within territory. Below is how the first few rows should appear. There are more rows than this in the actual result set.

 .

.

.

7.      Write a query to show the total number of employees for each territory. Show the number of employees first and then the territory description. Sort the results by the number of employees per territory with the highest number of employees first. If there are territories with the same number of employees, then sort the results by territory description within number of employees. Below is a sample of the expected output.

 .

.

.

8.      Now show the number of employees by region. Below is the expected result including sort order and column names. 

9.      Show the supervisor(s) that have 4 or more employees. It is okay to hard code the 4 this time. You must use the appropriate HAVING clause. Here is the expected result. Notice the column headers.

10.  Show only the territory with the most employees. You must use aggregate functions in the query to only show the correct territory. Your query must use a HAVING clause with a subquery.  Hint: Use your solution to #7 as a derived table for the subquery in the HAVING clause. Here is the entire expected result.