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]
2 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]2 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 code returns the date in the format friday, april 20th, 2015?
Vera_Pavlovna [14]
<span>The answer is : DATE_FORMAT('2012-04-20', '%W, %M %D, %Y')
This is the </span><span>code that returns the date in the format friday, april 20th, 2015?</span>
6 0
2 years ago
Which of the following is a reliable source of information: a book recommended from my professor, britannica, a blog, or wikiped
ratelena [41]
A book recommended from your professor is a reliable source of information.
8 0
3 years ago
Suppose there is a class AirConditioner. The class supports the following behaviors: turning the air conditioner on and off. The
Sunny_sXe [5.5K]

Answer:

The code is given in the explanation section

Explanation:

//Class Airconditioner

public class AirConditioner {

   private boolean turnOnOff;

//The Constructor

   public AirConditioner(boolean turnOnOff) {

       this.turnOnOff = turnOnOff;

   }

//method turn_on

   public void turn_on(){

       this.turnOnOff = true;

   }

//method turn_off

   public void turn_off( ){

       this.turnOnOff = false;

   }

}

// A new class to test the airconditional class

class AircondionTest{

   public static void main(String[] args) {

//Creating an object of the Aircondional class

       AirConditioner office_a_c = new AirConditioner(false);

//Using the reference varible to call method turn_on      

office_a_c.turn_on();

   }

}

8 0
3 years ago
The term ____ refers to a wide variety of different database technologies that were developed in order to overcome some of the l
ella [17]
Database management systems are used to store and to access information.
In <span>the Relational Database Management System </span>(RDBMS), all the data is in the form of simple columns and rows in a table. Structured Query Language (SQL) is is the standard query language for RDBMS. Some of the disadvantages of RDBMS are: not enough storage area to handle data such as images, digital and audio/video, do not provide good support for nested structures,not much efficient and effective integrated support.
<span>The term object oriented database management systems (OODBMS)  refers to a wide variety of different database technologies that were developed in order to overcome some of the limitations of relational databases.</span>





5 0
3 years ago
Which memory device should Peter use in his digital camera and media player?
Sladkaya [172]
A flash drive can be useful to store memory
3 0
3 years ago
Other questions:
  • All users on the network have antivirus software; however, several users report that they have what an administrator described a
    10·1 answer
  • Wireless attacks avoid the access points to limit detection. <br> a. True <br> b. False
    9·1 answer
  • A(n) ______ system is a set of programs that coordinates all the activities among computer or mobile device hardware. a. managem
    10·1 answer
  • If you are inserting an address block with the Address Block dialog box and the fields do not connect automatically, what can yo
    6·2 answers
  • Write the definition of a function named averager that receives a double parameter and returns-- as a double -- the average valu
    11·1 answer
  • I like to troll what about you
    10·1 answer
  • Is this App for real?​
    5·1 answer
  • Is the most important characteristic of a hard drive.​
    7·2 answers
  • Explain different types of networking-based attacks
    5·1 answer
  • Laura is filming a scene in which the subject is sitting with a lit fireplace behind him. The only other source of light in the
    15·1 answer
Add answer
Login
Not registered? Fast signup
Signup
Login Signup
Ask question!