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
What aspect should you consider before adding pictures to a document?
Luden [163]

Answer:

information?

Explanation:

picture are there to give a visual or make the document more appealing. if your wording and information isnt up to date or reasonable people wont lean to what you are saying as much.

8 0
3 years ago
Read 2 more answers
Why did it take fewer 1-inch tiles than 1-centimeter tiles to measure the length of the cookie cutter?​
Romashka-Z-Leto [24]

The inch tiles are larger, meaning they use less space.

6 0
3 years ago
"The principle of ______________ states that programs tend to access data and instructions nearby recently used data and instruc
maria [59]

Answer:

<em>Locality of reference.</em>

Explanation:

In computing, The principle of locality of reference is the probability of a processor to repeatedly access the same set of memory locations within a small time interval. This reference of locality can be spatially based (repetitive usage of data within the same location on the computer memory), or temporally based (repetitive usage of a particular data or resources within a shot time interval). The ability of some computing system to perform this action increases their predictability, and efficiency of memory hierarchy use, among other abilities.

4 0
3 years ago
Conceptos importantes de red de computadoras
Bumek [7]

Answer:

-Consiste en conectar varias redes de computadoras basadas en diferentes protocolos -Requiere la definición de un protocolo de interconexión común sobre los protocolos locales. -El Protocolo de Internet (IP) desempeña este papel, definiendo direcciones únicas para una red y una máquina host.

3 0
3 years ago
Which wireless technology connects with most mobile devices?
LenaWriter [7]

Answer:

¡) Internet

¡¡) Alexa

¡¡¡) Electronic Devices

8 0
2 years ago
Other questions:
  • What is a valence orbit?
    13·2 answers
  • What is the chief ethical concern concerning all the new sources of data available through new technological means like internet
    9·1 answer
  • Multiple users report that the network printer, which is connected through the print server, is not printing. Which of the follo
    12·1 answer
  • Which of the following can potentially be changed when implementing an interface?
    13·1 answer
  • You are sending an email to a coworker requesting that the coworker perform a task. Where should you put your coworker's email a
    8·1 answer
  • Dit View
    5·2 answers
  • I really want to know the best way to deal as much heart as possible plz tell me
    7·2 answers
  • What is the output of the following code:
    12·1 answer
  • Ncomputing and thin client<br>what it is <br>why is done , how it works and how it's <br> done ​
    7·1 answer
  • Can someone help me debug this please
    6·1 answer
Add answer
Login
Not registered? Fast signup
Signup
Login Signup
Ask question!