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 one of the following downloads and uploads files to and from a server? A. Client B. Internet protocol C. Server D. Worksta
earnstyle [38]
The answer is A. Client request and receive files to and from a server.
5 0
3 years ago
A merge is _____.
Aliun [14]
The process of combining information from a variety of sources
7 0
3 years ago
Read 2 more answers
Password cracking is a technique used to extract user’s password of application/files without the knowledge of the legitimate us
Vlada [557]

Answer:

Rule based Attack.

Explanation:

                    As password cracking is a technique used to extract user’s password of application/files without the knowledge of the legitimate user. The attacker can use Rule Based Attack to find the password and to intrude or compromise the network and systems.

                    This is like a programming language to generate the password. This involves functions to modify, cut, edit and extend the generally used terms by the user.

5 0
3 years ago
Universal Containers (UC) uses a custom object called Vendor. The Vendor custom object has a Master-Detail relationship with the
Rudik [331]

Answer:

The account record comprises of Invoice roll up summary fields.

Explanation:

The possible reason that this change was not permitted was that the account record comprises of Invoice roll up summary fields.

Invoice roll up summary fields: A roll-up summary field computes values from associated records, for example those in a linked list. a person or someone ca design a roll-up summary field to show a value in a master record by building  the values of fields in a particular record.

The detail record must be associated to the master through a master-detail relationship. for instance, you want to show the sum of invoice amounts for all linked custom object records in an account’s Invoices related list. you can show the whole or sum in a custom account field refereed to as Total Invoice Amount.

4 0
3 years ago
A company wants to use online methods to target more customers. It decides to conduct a market research by collecting the data o
Ronch [10]

Answer:

They could use a CRM

A CRM tool lets you store customer and prospect contact information, identify sales opportunities, record service issues, and manage marketing campaigns, all in one central location — and make information about every customer interaction available to anyone at your company who might need it.

They Could Use PDC

The term “primary data” refers to data you collect yourself, rather than data you gather after another party initially recorded it. Primary data is information obtained directly from the source. You will be the first party to use this exact set of data.

They Could Use Quantitative vs. Qualitative Data

Quantitative data comes in the form of numbers, quantities and values. It describes things in concrete and easily measurable terms. Examples include the number of customers who bought a given product, the rating a customer gave a product out of five stars and the amount of time a visitor spent on your website.

Why Should I Track Users Data:

To ensure it's going smoothly, it's important to keep track of data that directly correlates to the potential success of your business. Key performance and financial metrics can help you address any problems with your business before they occur

8 0
3 years ago
Other questions:
  • HEY DO U LIKE TRAINS!
    7·1 answer
  • * Declare a variablecalled "car" of type "Car", and initialise its value to a new instance of the "Car"class.
    9·1 answer
  • What statement best describes Konrad Zuse?
    6·2 answers
  • A network administrator receives a call from the sales department requesting ports 20 and 21 be opened on the company’s firewall
    12·1 answer
  • A word object in an excel worksheet to activate the word features
    9·1 answer
  • Solve the recurrence relation.<br> S(1)=1<br> S(n)= S(n-1)+(2n-1) for n&gt;=2
    13·1 answer
  • Que significa WWW en informática ._.​
    11·1 answer
  • The process of bringing data or a file from one program to another is called
    5·2 answers
  • Which is a concept of the CIA of Computer Security
    7·1 answer
  • What are two potential benefits of using calendar-synchronization tools like calendly
    8·1 answer
Add answer
Login
Not registered? Fast signup
Signup
Login Signup
Ask question!