Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

PROCTECH 4IT3/ SEP 6IT3

Term Test

This is an open book test. Examples on Avenue may prove to be useful.

There are 3 questions on this test.  Each question requires one SQL statement to complete and HTML Tables to generate. You must include:

The SQL required to successfully complete each question with the command saved as part of the Django Project files.

Each question weight is noted in the question. The total of the weights adds up to 100% of the midterms worth.

AN EXAMPLE CAN BE SEEN USING THE LINK PROVIDED ON AVENUE

Rules:

•   Your midterm MUST include your statement of authorship in each of the files you edit.

•   You must use the standard copy of the midterm template that was provided on GitLab in the

Midterm Section

•   You must upload a copy that contains answers to all the questions toGitLab

•   You must upload a copy of the files to Avenue to Learn as a ZIP File excluding:

o venv

o SQL Files

o Scratches

•   You must NOT edit the requirements.

Hints:

•   All code should be written only in the views and independent html files.

•   You should write the query and confirm it in a SQL Editor before including it in your midterm.

•   Utilize the ERD to plan out your joins and connects.

Use the Northwind Database from the git repo to answer the questions.

Question 1 – Orders (23%)

SEASONALITY:

•   Write a code and query that will list the month of the year and the number of orders placed in that month for all orders in the orders table .

•   This should be configurable and default to 2014 with a range of 2013-2015.

•   The months must be shown in Calendar order (January, February … December) with Month names.

• The two columns should be labelled “Month” and “# of Orders”

Question 2 – Products (37%)

VALUABLE INVENTORY:

•   Write a query that lists the value of the current product inventory grouped by category.

•   Display the category name and value of the inventory in the results.

•   Display the value to 2 decimal places in proper currency format.

•   The value of the inventory is calculated by multiplying the UnitPrice by the UnitsInStock in the Products table.

•   List by default categories over $10,000 of inventory with step sizes of $2,500, minimum at $2,500 and maximum at $12,500.

• Label the 3 columns as “ID”, “Category Name” and “Inventory Value”

Question 3 – Shipping (40%)

HOW LONG DOES IT TAKE:

•   List the country, count as `# of Shipments` and average wait time (defined as the difference in days between the shippeddate and orderdate) as `Average Days` for all shipped orders.

•   Shipped date cannot be null and should be for all customers in different countries around the World.

•   Display the average to 1 decimal place and only display countries.

•   The default value should be for the year 2013 and countries that had at least 3 orders.

•   Order the results by the average wait time with the longest wait time at the top of the list.

• The results should be within the range of 2013-2015 and orders between 0 and 50 with a step of 1 .