1answer.
Ask question
Login Signup
Ask question
All categories
  • English
  • Mathematics
  • Social Studies
  • Business
  • History
  • Health
  • Geography
  • Biology
  • Physics
  • Chemistry
  • Computers and Technology
  • Arts
  • World Languages
  • Spanish
  • French
  • German
  • Advanced Placement (AP)
  • SAT
  • Medicine
  • Law
  • Engineering
Vesna [10]
3 years ago
6

Create the following SQL Server queries that access the Northwind database. Place them in a zip file and place the zip file in t

he "drop box" for Assignment 5. Name the queries Query 1-MP5, Query2-MP5, etc. Note: These problems all require aggregate processing with GROUP BY
1. For each employee, list the employee ID, employee first name, employee last name, and count of orders taken in 1996. Place the list in descending order by the number of orders.
2. For all orders shipped in July or August, 1996, list the order ID, company name, order date, and total cost of all items ordered. Place the list in descending order by the total cost
3. For all customers from the USA, list the customer ID, company name, state, count of orders, and total order amount. Do not consider the discount. Place the list in order by state and then customer ID.
4. Same as problem 3 but limit the list to all customers who placed 3 or more orders.
5. For all items in the Grains/Cereals category, list the product ID, product name, supplier name, and last date that the product was ordered. Order the list by supplier name and product name (Hint: Use MAX and be sure to include all necessary tables)
6. The product ID, product name, and count of distinct customers who ordered that product in 1996. Place the list in descending order by the count of customers.
Computers and Technology
1 answer:
neonofarm [45]3 years ago
8 0

Answer:

1. SELECT e.EmployeeID, e.FirstName, e.LastName, COUNT(*) as OrderByEmployee FROM Employees e

JOIN Orders o

ON e.EmployeeID = o.EmployeeID

WHERE YEAR(o.OrderDate) = '1996'

GROUP BY e.EmployeeID,e.FirstName,e.LastName

ORDER BY OrderByEmployee DESC

2. SELECT o.OrderID,c.CustomerID, o.OrderDate,SUM((od.Quantity)*

od.UnitPrice - od.Discount)) as TotalCost FROM Orders o

JOIN [Order Details] od

ON od.OrderID = o.OrderID

JOIN Customers c

ON o.CustomerID = c.CustomerID

WHERE (MONTH(OrderDate)= 7 OR MONTH(OrderDate) = 8) and

YEAR(OrderDate) = 1996

GROUP BY o.OrderID,c.CustomerID, o.OrderDate

ORDER BY TotalCost DESC

3. SELECT c.CustomerID, c.CompanyName, c.City, COUNT(o.OrderID) as TotalOrder, SUM(od.Quantity* od.UnitPrice) as TotalOrderAmount FROM Customers c

JOIN Orders o

ON o.CustomerID = c.CustomerID

JOIN [Order Details] od

ON od.OrderID = o.OrderID

WHERE c.Country = 'USA'

GROUP BY c.CustomerID, c.CompanyName, c.City

ORDER BY c.City, c.CustomerID

4. SELECT c.CustomerID, c.CompanyName, c.City, COUNT(o.OrderID) as TotalOrder, SUM(od.Quantity* od.UnitPrice) as TotalOrderAmount FROM Customers c

JOIN Orders o

ON o.CustomerID = c.CustomerID

JOIN [Order Details] od

ON od.OrderID = o.OrderID

WHERE c.Country = 'USA'

GROUP BY c.CustomerID, c.CompanyName, c.City

HAVING COUNT(o.OrderID) > 3

ORDER BY c.City, c.CustomerID

5. SELECT p.ProductID, p.ProductName, s.ContactName as SupplierName, MAX(o.OrderDate) as LastOrderDateOfProduct FROM Products p

JOIN Categories c

ON c.CategoryID = p.CategoryID

JOIN Suppliers s

ON s.SupplierID = p.SupplierID

JOIN [Order Details] od

ON od.ProductID = p.ProductID

JOIN Orders o

ON o.OrderID = od.OrderID

where c.CategoryName = 'Grains/Cereals'

GROUP BY p.ProductID, p.ProductName, s.ContactName

ORDER BY SupplierName, p.ProductName

6. SELECT p.ProductID, p.ProductName, Count(DISTINCT c.CustomerID ) as OrderByThisManyDistinctCustomer

FROM Products p

JOIN [Order Details] od

ON od.ProductID = p.ProductID

JOIN Orders o

ON o.OrderID = od.OrderID

JOIN Customers c

ON c.CustomerID = o.CustomerID

where YEAR(o.OrderDate) = 1996

GROUP BY p.ProductID, p.ProductName

Explanation:

The six query statements returns data from the SQL server Northwind database. Note that all the return data are grouped together, this is done with the 'GROUP BY' Sql clause.

You might be interested in
Which of the following will you select as X in the following series of clicks to lay the title over a chart: Chart Title box &gt
guapka [62]

Answer:

A

Explanation:

We are to lay a title over a chart, which means the position of the title should be above the chart.

When doing this, it will centre the title over the chart without duly interfering with the original size of the chart.

The option 'over chart' is incorrect as it is not a valid selection in the pane. Below chart as well is incorrect as we are to lay the title above the chart.

3 0
3 years ago
Question 1
likoan [24]

Answer:

No Answer by by a third jsjshs

7 0
1 year ago
It is a SQL keyword that creates an autoincrementing field.
Serjik [45]

Answer:

c. AUTO_INCREMENT

Explanation:

this should help

6 0
3 years ago
Ayúdenme porfa se los agradecería :c
babymother [125]

Answer:

Me podrían ayudar se lo agradecería un montón

5 0
3 years ago
Sarah has to add a picture from her computer file and add a caption to it. Arrange the steps in a correct sequence.
MatroZZZ [7]

Answer:

-Click insert

-Click picture

-Click from file

-Right click the picture

-Select the desired picture and again click Insert

-Click insert caption

-Write the caption and add it

Explanation:

6 0
3 years ago
Other questions:
  • If you are viewing a webpage with customized or regenerated content, such as updated stock quotes, what type of webpage are you
    14·1 answer
  • What are the main types of computer software?
    5·2 answers
  • Write a recursive function, replace, that accepts a parameter containing a string value and returns another string value, the sa
    5·1 answer
  • A network technician is tasked with designing a firewall to improve security for an existing FTP server that is on the company n
    9·1 answer
  • What is the difference between a key escrow and a recovery agent? (Choose all that apply.)
    12·1 answer
  • 1.Customer service is assistance provided by a company to the people who buy or use its products or services.
    13·2 answers
  • Florida Highway Safety and Motor Vehicles reported blank of traffic fatalities were alcohol-related in Florida in 2009.​
    8·1 answer
  • ou use productivity apps on your iPad tablet device while traveling between client sites. You're concerned that you may lose you
    11·1 answer
  • Excerpt from "How Prepared Are Students for College Level Reading? Applying a Lexile-Based Approach."
    6·1 answer
  • A brick has a weight of 23N. The brick is flat and smooth on the side in contact with the floor having measurements ( height= 5c
    13·1 answer
Add answer
Login
Not registered? Fast signup
Signup
Login Signup
Ask question!