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
100 POINTS!!!!!!
Veronika [31]

Answer:

The fundamental limitation of symmetric (secret key) encryption is ... how do two parties (we may as well assume they are Alice and Bob) agree on a key? In order for Alice and Bob to communicate securely they need to agree on a secret key. In order to agree on a secret key, they need to be able to communicate securely. In terms of the pillars of IA, To provide CONFIDENTIALITY, a secret key must first be shared. But to initially share the key, you must already have CONFIDENTIALITY. It's a whole chicken-and-egg problem.

This problem is especially common in the digital age. We constantly end up at websites with whom we decide we want to communicate securely (like online stores) but with whom we there is not really an option to communicate "offline" to agree on some kind of secret key. In fact, it's usually all done automatically browser-to-server, and for the browser and server there's not even a concept of "offline" — they only exist online. We need to be able to establish secure communications over an insecure channel. Symmetric (secret key) encryption can't do this for us.

Asymmetric (Public-key) Encryption

Yet one more reason I'm barred from speaking at crypto conferences.

xkcd.com/177/In asymmetric (public key) cryptography, both communicating parties (i.e. both Alice and Bob) have two keys of their own — just to be clear, that's four keys total. Each party has their own public key, which they share with the world, and their own private key which they ... well, which they keep private, of course but, more than that, which they keep as a closely guarded secret. The magic of public key cryptography is that a message encrypted with the public key can only be decrypted with the private key. Alice will encrypt her message with Bob's public key, and even though Eve knows she used Bob's public key, and even though Eve knows Bob's public key herself, she is unable to decrypt the message. Only Bob, using his secret key, can decrypt the message ... assuming he's kept it secret, of course.

Explanation:

3 0
2 years ago
During project management, who executes tasks and produces the deliverables as stated in the project plan and as directed by the
san4es73 [151]
B. Project team members
8 0
3 years ago
Using the world_x database you installed in Module 1, list the countries and the capitals of each country. Modify your query to
Rainbow [258]

Answer:

SELECT country.Name, city.Name

FROM country

JOIN countrylanguage ON country.Code = countrylanguage.CountryCode

JOIN city ON country.Capital = city.ID

WHERE countrylanguage.Language = 'English'

AND countrylanguage.Percentage < 30;

Explanation:

SELECT is used to query the database and get back the specified fields.

City.Name is an attribute of city table.

country.Name is an attribute of country table.

FROM is used to query the database and get back the preferred information by specifying the table name.

country , countrylanguage and city are the table names.

country and countrylanguage are joined based ON country.Code = countrylanguage.CountryCode

countrylanguage and city are joined based ON country.Capital = city.ID

WHERE is used to specify a condition based on which the data is to be retrieved. The conditions are as follows:

countrylanguage.Language = 'English'

countrylanguage.Percentage < 30;

5 0
3 years ago
Alright, so I have a wireless mouse that uses a dongle (it's not Bluetooth nor wired). But recently I realized that none of the
serg [7]

Answer:Mouse is not working because you haven't changed your battery for months.

Remember that, most of wireless mouses use AA 1.5V battery for it's power on. If you've let your computer and mouse set on, then the battery can only run around 5-6 months. If you turn off your mouse everytime you're not using then the battery life might get longer. So, all you have to do is buy new batteries and replace it. Follow steps of replacing battery in this video.

(Consider buying dry battery for using longer)

No.2: Loose battery in the tray

This is quite common as well. We understand that the mouse made materials can have its own changing. Sometimes, you might drop your mouse, then after that, the loosen battery just happening around. A loosen battery might get your wireless mouse not working well. It works then stop at the middle. Follow the easy steps to fix this state of mouse is not working.

No. 3: Bad Pairing.

Due to some reasons, bad pairing can happen and get your mouse stuck on laptop screen. Bad pairing might be from overworking or overloading tasks on your PC. Easy quick fix for bad pairing is disconnect your USB receiver for a while and re-plug in later on.

No.4: On/Off Switch or Power button on the mouse just drop or loosen in

Many people might have problems with their mouse on/off switch button. Don't worry, this can be fixed as well. You can open the mouse (just look under the battery, there is a single screw). Open the mouse from this screw, and disassembly it carefully. You can see the on/off switch is just around the corner. Now, you can, reattach the button and close the mouse correctly.

No. 5 : Buy a new mouse

I've suggested a way to search for a new wireless mouse with USB Unifying Receiver. For a wireless mouse that allows Unifiying receiver, then you don't worry when your current usb receiver get lost (because, you can always buy a new usb receiver).

Explanation:

6 0
3 years ago
What changes have occurred over the last 25 years impacting how we use information systems and that increasingly require using e
Elden [556K]

Answer:

Follows are the solution to this question:

Explanation:

Throughout the presence of third-party companies that is attackers, cryptography is indeed a method of protecting information and communication.

Its cryptographic techniques are numerous, which are used for data security and encryption levels were defines, throughout all protocols.

Encryption - was its method for encoding a message and information. So, it can be viewed only by authorized parties.

They can search online for various data encryption. The changes over the last 25 years also actually occurred:

Cryptography is all around. Each time you make a phone call, buy something like that in a store or even on the Internet with a credit or debit card or receive money from an Atm is an authentication that helps secure and secures the payment.

Tausend years ago, codes or encryption were used to protect information. These encryption schemes have evolved from the past 25 years or say we are getting far better than that of the preceding one But cryptography was far more advanced with both the advent of computers than it used to be.

World War II would also see Enigma code, the perfect example of analog encryption.

Its computer design technology had also finally been enough to break its Enigma cipher, as well as Enigma texts, still are regarded as just a necessary component of the probable Affiliated successes.

Mathematical 128-bit cryptography has become a standard for several sensible computers and portable structures, far greater than every prehistoric or ancient plasterboard.

Without it.

AES - Another of our safest cryptography techniques is the Intensive Encryption System. The state-government uses it to protect classified information, that is used by all of our phones.

6 0
3 years ago
Other questions:
  • There are many careers within the IT industry. _____ are responsible for organizing a company's data, making sure all the data i
    9·1 answer
  • What are the two main components on the motherboard?
    12·2 answers
  • What is a Hard Drive
    13·1 answer
  • Who found the first computer bug in 1947, and invented the concept of a compiler in 1952
    6·1 answer
  • I NEED HELP ASAP!
    6·2 answers
  • Design a class named Person and its two subclasses named Student and Employee. Make Faculty and Staff subclasses of Employee. A
    13·1 answer
  • Which type of app is the best choice when it is critical to be able to use the device's features but performance is not critical
    5·1 answer
  • 3. Why is human resource plan made​
    11·1 answer
  • What accesses organizational databases that track similar issues or questions and automatically generate the details to the repr
    14·1 answer
  • * what is an electronic mail ?
    13·1 answer
Add answer
Login
Not registered? Fast signup
Signup
Login Signup
Ask question!