nt 6 Fall 2017 100 points SQL on multiple tables For this assignment you will be

Question

nt 6 Fall 2017 100 points SQL on multiple tables For this assignment you will be again using the classi database on our unix system. Note that there are 9 last one must be done at least two different ways, so each sql answer is worth 10 points. Again, write and test your them into a file, run that file and turn it and the output in on Blackboard. Read the questions carefully! cmodels queries and then put 1.How many employees work in each city? List the city name. 2.List each employee first name and last name and the number of customers for each one. 3.List each employee first name and last name and the first and last name of the person that employee reports to 4. For the first 25 customers, list the contact person (first name and last name) and the total amount of payments. 5.How many customers live in the same city as their sales rep works? 6.How many customers live in the same city as their sales rep works, list the name of the city and the number of customers. 7·Which customer (just the customer name) has ordered the most expensive product (based on the buyPrice)? 8.Which customer has made the largest payment? list just the customer name. 9.List all of the product descriptions for products from Min Lin Diecast and Exoto Designs do this in at least two different ways (Don't use wild cards) Extra Credit In order to receive extra credit points, you must have the basic assignment done completely and correctly. These are meant to be more difficult, so you may not ask the Instructors or TAs (or each other) for help. 1. For the first 10 orders, list the order number, the customer name and all of the product names on that order in ascending order of customer name 2.What is the average dollar amount for each order?

Explanation / Answer

1.

Select city ,count(employeeNumber) from Offices inner join Employees on Offices.officeCode = Employees.OfficeCode group by city;

2.

Select firstname,lastname,count(customerNumber) from Employees inner join Customers on Employees.employeeNumber = Customers.salesRepEmployeeNumber group by Employees.empNumber;

3.

Select e1.firstname,e1.lastname,e2.firstname,e2.lastname from Employees e1 inner join Employees e2 on e1.reportsTo = e2.employeeNumber;

4.

Select TOP 25 contactFirstName,contactLastName ,SUM(amount) from Customers inner join Payments on Customers.cutomerNumber = Payments.customerNumber group by Customers.customerNumber ;

5.

Select count(customerNumber) from Customers inner join Employees on Customers.salesRepEmployeeNumber = Employees.employeeNumber inner join Offices on Employees.officeCode = Offices.officeCode where Customers.city = Offices.city;

6

Select Customers.city,count(Customers.customerNumber) from Customers inner join Employees on Customers.salesRepEmployeeNumber = Employees.employeeNumber inner join Offices on Employees.officeCode = Offices.officeCode where Customers.city = Offices.city;

7.

Select customerName from Customers inner join Orders on Customers.customerNumber = Orders.customerNumber inner join OrderDetails on Orders.orderNumber = OrderDetails.orderNumber inner join products on Products.productCode = OrderDetails.prductCode where Products.buyPrice = (Select max(buyPrice) from Products);

8.

Select customerName from Customers inner join Payments on Customers.customerNumber = Payments.customerNumber where Payments.amount = (Select max(amount) from Payments);

9

Select productDescription from Products inner join ProductLines on Products.productLine = ProductLines.productLine where ProductLines.textDescription = 'Min Lin Diecast ' or productLines.textDescriptions = 'Exoto Designs';

Select productDescription from Products inner join ProductLines on Products.productLine = ProductLines.productLine where ProductLines.textDescription IN( 'Min Lin Diecast ' ,'Exoto Designs');

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.