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
After a group sets a project schedule, members should be prepared to
Roman55 [17]

After a group sets a project schedule, members should be prepared to complete a study-time survey to prove they can do the work.

<h3>What is a group project?</h3>

A group project is a project in which more than one person work together. Group projects are given to improve the work, as various minds can bring out various implements for the project.

Thus, the correct option is D. complete a study-time survey to prove they can do the work.

Learn more about group project

brainly.com/question/14488746

#SPJ1

3 0
2 years ago
Sally needs to copy data from the first worksheet to the fifth worksheet in her workbook. Which combination of keys will she use
Marta_Voda [28]

To make things faster, instead of using your mouse to navigate multiple Microsoft Excel sheets that are in the same document, you can use a shortcut instead. This is what Sally will be doing to copy data from her first worksheet to the fifth.

The shortcut function that Sally needs to use to achieve this is (A) Ctrl and Page Up or Page Down Keys.

7 0
3 years ago
Read 2 more answers
Identify the most appropriate way to display the following research data.
VMariaS [17]

Answer:

1. Artwork of the child participants can be displayed as Picture.

Explanation:

The Art work is painting and filling different colors in different images and objects. Child make different pictures of homes, objects, fruits and vegetables and then colored them accordingly. This type of art work is displayed as picture. This work could not be displayed in the form of tables and lists and also could not shown as graphics.

Answer:

2. The changing image of public transportation is displayed as Graphics.

Explanation:

In Graphics, different images has been designed on different tools by graphic designers to display some problem and its solution in visuals. Different tools are there to make visuals. Changing image of bus transportation is also a transition from old transport system to new transportation system. So, it should displayed in the form of graphics.

Answer:

Illustrative comparison of data from the year 2010-2013 can be displayed in the form of word tables and lists.

Explanation:

In tables and list different data has been placed in specific order for the purpose of analysis of data. These tables are also designed to compare different values and things. So, the data from the year 2010-2013 can be displayed in the form of tables and list so that it can be compared and analyze from different aspects.

7 0
3 years ago
Durante 10s, la velocidad de rotación y el momento de giro de las ruedas de un coche eléctrico son 100 rpm y 1405,92 Nm, respect
Akimi4 [234]

Answer:

c

Explanation:

hope this helps

6 0
3 years ago
IF U LOVE CODING CLICK HERE!!! : )
erik [133]

Answer:

I think it is the last one. Or first. Try first though.

Explanation:

Have a Great Day.

8 0
3 years ago
Other questions:
  • Why does the PC send out a broadcast ARP prior to sending the first ping request
    12·1 answer
  • Consider an environment in which there is a one-to-one mapping between user-level threads and kernel-level threads that allows o
    7·1 answer
  • Charles would like to visit a specific page on the World Wide Web. How should he do it? Enter the address in a search engine cli
    15·2 answers
  • Adam is evaluating the security of a web server before it goes live. He believes that an issue in the code allows an SQL injecti
    5·1 answer
  • Which of the following is the correct ordering of operating systems, oldest to newest?
    5·2 answers
  • I nees the meaning of these words even just one of them would help out ( pixel megapixel colour depth bit and byte)
    15·2 answers
  • How to turn off windows 10 without loosing sound?
    11·1 answer
  • Why is it difficult to convince top management to commit funds to develop and implement a Strategic Information System
    13·1 answer
  • Which of the following is not the disadvantage of closed
    7·2 answers
  • A eclipse occurs at a full moon when the moon passes between earth and the sun
    8·1 answer
Add answer
Login
Not registered? Fast signup
Signup
Login Signup
Ask question!