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
Giving 5 stars, a Thanks, 80 points, and Branliest to whoever answers them correctly.
Mrac [35]

Answer:

Explanation:

The answer is ludonarrative.

Please mark me brainliest.

4 0
2 years ago
How many comparisons will be done to find 8 in this list using a linear search?
Margaret [11]

Answer:

I think 4 comparisons

8 0
3 years ago
Any idea how to make my simple python projects such as guess the number, mad libs, etc. even better?
-Dominant- [34]

Answer: Person's name.

Explanation:

3 0
2 years ago
Read 2 more answers
You are a desktop administrator for nutex corporation. Your organization uses ethernet cable to connect network resources. A use
anygoal [31]

Answer:

A. replace the network cable

Explanation:

Based on the information provided within the question it can be said that in this scenario the best option would be to replace the network cable. The network cable refers to the Ethernet cable that is connected to the individuals personal computer. This cable being faulty is most likely the reason as too why the individual is not able to connect. Since it is displaying that signals return too early it means that there is most likely information being lost in transit.

4 0
3 years ago
When using Microsoft word, what would happen to the text of a paragraph if the "Justify" option is used?
Bond [772]
Hello, and thank you for asking your question!

When you click on the 'Justify' option on any type of word pad, it'll make all lines of words perfectly aligned. Here is a quick few-sentence example I made. 
(Photo)

Hope this helps! ☺♥

3 0
3 years ago
Other questions:
  • Discuss the differences between dimensionality reduction based on aggregation and dimensionality reduction based on techniques su
    13·1 answer
  • true or false manual handling of materials accounts for the primary source of energy in the workplace
    13·2 answers
  • Tony is in charge of all presentations for a Fortune 500 pharmaceutical company. In addition to creating powerful and persuasive
    9·1 answer
  • How can i turn on my prinfer without getting up
    12·2 answers
  • ___ is a family of data formats that helps people automatically receive feeds anytime there are new postings to their favorite b
    14·1 answer
  • FOR DIGITAL DESIGN/ PHOTOGRAPHY
    5·2 answers
  • To reduce the potential for repetitive stress injury you should use proper?
    6·1 answer
  • Which of the following is an example of group dynamics?
    8·1 answer
  • Why isn't image display working on wacom tablet.
    8·1 answer
  • What does the following code print? time_of_day = ["morning", "afternoon", "evening"] for word in time_of_day: print "Good " + w
    15·1 answer
Add answer
Login
Not registered? Fast signup
Signup
Login Signup
Ask question!