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
True or false? LearnSmart (the "smart flash card assignments") really gets your competitive spirit in gear by allowing you to se
vfiekz [6]
<h2>Answer:</h2>

The following statement is TRUE.

LearnSmart (the "smart flash card assignments") really gets your competitive spirit in gear by allowing you to see where you stand as compared to your classmates in terms of your mastery of grammar and vocabulary concepts.

<h2>Explanation:</h2>

LearnSmart is an adaptive technology that is helping a lot of students by letting them judge which parts/topics of the book are not clearly learnt by them and which of them are under their grip.

LearnSmart provides cards with short term questions that make i possible for students to complete their preparation in short time. In addition to this smart flash card assignments are a to compare students preparation relatively.

<h3>I hope it will help you!</h3>
4 0
3 years ago
A user reports that a PC gets a BSOD on startup. A technician has researched the error message and found a driver to replace. Wh
7nadin3 [17]

Answer:

The explanation of this question is given below in the explanation section. however, the correct option of this question is "Boot the machine into safe mode"

Explanation:

BSOD error on startup

Blue screen of death error arises mostly when dirver produce fault into system or did not install properly. however, the correct option for this question, if PC gets BSOD error on startup then it is the best option for technician to remove the hardware that is recently installed and/or try to boot the machine into safe mode, then uninstall the driver that causing error on startup.

BSOD error occur during running the program on PC

However, if PC get BSOD during running program, then the best option for technician is to restart the machine and then install the driver properly. During this operation, as error occured, you may loss the data becuase you have no other option to save the data of program that is running.

6 0
3 years ago
Stock ____ firms trade financial securities between a buyer and a seller.
diamong [38]
<span>Stock market firms, also known as brokerage firms, assist in the act of buying and selling financial securities. These trades are financed through financial institutions for security.</span>
3 0
3 years ago
Synchronization barriers are a common paradigm in many parallel applications.
Bas_tet [7]

Answer:

I am not sure on this one I am guessing it is True

Explanation:

3 0
3 years ago
PLEASE HELP!!
Anika [276]

Answer:

the answer is true

hope it helps

7 0
3 years ago
Read 2 more answers
Other questions:
  • 1. If you purchase the renters insurance policy described above, you bring a suitcase of clothing and other general
    12·1 answer
  • Add is a function that accepts two int parameters and returns their sum.
    8·1 answer
  • Obtain a file name from the user, which will contain data pertaining to a 2D array Create a file for each of the following: aver
    5·1 answer
  • If you were going to construct a table that only included shape names with number prefixes, which shape would you include
    11·1 answer
  • The incident results in huge losses of revenue as a result their mobile app service is withdrawn. Investigators discovered a vul
    5·1 answer
  • the easiest and cheapest time to alter the information system is in the ________ phase of the sdlc. a. requirements analysis b.
    8·1 answer
  • Alison discovers that a system under her control has been infected with malware, which is using a key logger to report user keys
    12·1 answer
  • In 1-2 sentences, describe how to change the font.
    8·2 answers
  • Which branch of science helps avoid or minimize stress-related injuries at workplace?
    14·2 answers
  • What do macOS and Windows use to prevent us from accidentally deleting files?
    15·1 answer
Add answer
Login
Not registered? Fast signup
Signup
Login Signup
Ask question!