Question

Hello, I need help answering all 8 of these questions for my BIS 422 class. I need the appropriate MySQL script to use for these questions

Provide the SQL for the following data requests. Your SQL should be written as a single script that can be pasted into MySQL and run without edits. Make sure you use the proper notation for your comments (see the practice solution for an example of the format). There will be a 5% deduction for scripts that cannot be pasted into MySQL and run without edits.

Technician 1 TechnicianID {Certifications) Person Employee OK, EK Employeeld SSN DateOfBirth DateHired [YOS Region Actve YN Employee Type PersonID Salesperson FirstName LastName Street City State ZipCode PhoneNbr PersonType SalespersonID SalesGoal [SalesToDate] Shipment ShipmentID Customer OrderID ShipperID TechnicianID Shipment Date Shipper ShipperID Shipper Name ContactName ContactPhone Active YN Customer Customer D Discount 4 Customer Order PK Customer OrderID FK Customer FK SalesPersonlD Order Date Status Customer Orderltem PK, FK Customer OrderID PK, FK ItemID Order Price Quantity Vendor Item PK VendorItem PK ItemID Item Description PK VendorID Vendor Name ContactName ContactPhone Active YN PK, FK VendorID HA0 5PK, FK Itemid UnitCost SubCategory SubCategoryID SubCategoryName CategoryID Price - AH FK TH >O PK Category CategoryID CategoryName SubCategoryID FK Active YN

1. List the last names of all the people in the system, and their discount if they are a customer.

2. Provide a list of the total sales (in dollars) by category. Include the category ID, category name, and total sales amount. Sort the list by category ID is ascending order.

3. Provide the average order amount (in dollars) for each state. Sort the results by average amont in descending order.

4. Display the last name and phone number of all of the customers who purchased item 377. Sort the results by last name in ascending order.

5. List the ID, first name, and last name of each salesperson along with their sales goal and their sales to date. Sort the list by last name in ascending order.

6. List the salespeople who have not met their sales goal. Include the ID, first name, and last name of each salesperson along with their sales goal and their sales to date. Sort the list by last name in ascending order.

7. List the first name, last name, and phone number of customers with a 915 area code. Sort the list by first name in ascending order.

8. Provide the average time between when an order is made and when it is shipped

Answer 1

1.

Select LastName, Discount from Person inner join Customer on Person.PersonID = Customer.CustomerID;

2.

Select CategoryID.CategoryID, CategoryName, '$' + CONVERT(varchar(12), Sum(Price*UnitCost) )as TotalSales from Category inner join SubCategory on Category.CategoryID = SubCategory.CategoryID inner join Item on SubCategory.SubCategoryID = Item.SubCategoryID inner join VendorItem on Item.ItemId = VendorItem.ItemID group by CategoryID.CategoryID, CategoryName order by Category.CategoryID ;

3.

Select State, Avg(OrderPrice) as AverageAmount from Person inner join Customer on Person.PersonID = Customer.CustomerID inner join CustomerOrder on Customer.CustomerID = CustomerOrder.CustomerID inner join CustomerOrderItem on CustomerOrder.CustomerOrderID = CustomerOrderItem. CustomerOrderID group by State order by AverageAmount desc;

4.

Select LastName, PhoneNbr from Person inner join Customer on Person.PersonID = Customer.CustomerID inner join CustomerOrder on Customer.CustomerID = CustomerOrder.CustomerID inner join CustomerOrderItem on CustomerOrder.CustomerOrderID = CustomerOrderItem. CustomerOrderID inner join Item on CustomerOrderItem.ItemId = Item.ItemID where Item.ItemID = 377 order by LastName;

5.

Select SalespersonID, FirstName , LastName , SalesGoal, SalesToDate from Salesperson, Employee,Person where Salesperson.SalespersonID = Employee.EmployeeID and Employee.EmployeeID = Person.PersonID order by LastName ;

6.

Select SalespersonID, FirstName , LastName , SalesGoal, SalesToDate from Salesperson, Employee,Person where Salesperson.SalespersonID = Employee.EmployeeID and Employee.EmployeeID = Person.PersonID and SalesGoal = 'Y' order by LastName ;

7.

Select FirstName, LastName, PhoneNbr, ZipCode from Person , Customer where Person.PersonID = Customer.CustomerID and PhoneNbr LIKE '915%' order by FirstName;

8.

Select Avg(TimeDiff(OrderDate,ShipmentDate)) from Shipment inner join CustomerOrder on Shipment.CustomerOrderID = CustomerOrder.CustomerID;

Do ask if any doubt. Please up-vote.