CIS336: Lab 6: Group Functions and Subqueries
I desperatly need help with this MYSQL lab. Any help is appreciated. I don't need the outputs, I just need working codes.
1.Write a query to display the description of a product and the number of orders for that product. Sort the display by the description.
2.Redo the previous problem but this time just display those product names and the number of sales that have more than 3 sales.
3. Use a SubQuery to determine which sales reps have a sale that is greater than the average sale. Display the sales rep ID. Only show the sales rep ID once.
4. Use a SubQuery to display the city of all offices whose sales are below the average target for all the offices.
5. Use a SubQuery to list all sales rep IDs that have an average sales dollar amount greater the average sales dollar amount of the sales reps. You can use a correlated sub query or an correlated subquery. You need a correlated sub query if you wish to compare the average sales dollar amount to the average sales dollar amount to all the other sales reps NOT including the the sales rep you are comparing to.
Here are the tables and fields:
TABLE NAME: OFFICES
OFFICEID | CITY | REGION | MGRID | TARGET | SALES |
22 | Denver | Western | 6 | 300000.00 | 186042.00 |
11 | New York | Eastern | 9 | 575000.00 | 692637.00 |
12 | Chicago | Eastern | NULL | 800000.00 | 735042.00 |
TABLE NAME=SALESREPS
REPID | NAME | HIREDATE | QUOTA | SALES | OFFICEID |
1 | Bill Adams | 2008-10-11 | 350000.00 | 367911.00 | 13 |
2 | Mary Jones | 2013-01-04 | 200000.00 | 234877.00 | 13 |
3 | Sue Smith | 2009-06-15 | 250000.00 | 145000.00 | 12 |
TABLE NAME=CUSTOMERS
CUSTID | COMPANY | PHONE | CREDITLIMIT | CUSTREPID |
2111 | JCP Inc | 4569483748 | 50000.00 | 8 |
2102 | First Corp | 9148425732 | 65000.00 | 8 |
2103 | Division Mfg. | 8374475757 | 50000.00 | 5 |
TABLE NAME=PRODUCTS
PRODUCTID | DESCRIPTION | PRODUCTPRICE | QUANTITY_ON_HAND |
2A45C | Deluxe Driller | 1290.99 | 12 |
4100Y | Welder | 2343.99 | 24 |
XK47 | Reducer | 1854.00 | 100 |
TABLE NAME=ORDERS
ORDID | CUSTID | ORDERDATE | PRODUCTID | REPID | QTY | TOTALAMT |
112961 | 2117 | 2017-01-02 | 2A447 | 2 | 6 | 33600.00 |
113012 | 2111 | 2017-01-03 | 41003 | 2 | 2 | 6312.00 |
112989 | 2101 | 2017-01-03 | 114 | 1 | 1 | 1567.00 |
1.SELECT DESCRIPTION,QTY
FROM PRODUCTS NATURAL JOIN ORDERS
ORDER BY DESCRIPTION
2.SELECT DESCRIPTION
FROM PRODUCTS NATURAL JOIN ORDERS
WHERE QTY > 3
3.SELECT DISTICNT REPID
FROM SALESREPS
WHERE SALES>(SELECT AVG(SALES)
FROM SALESREPS)
4.SELECT CITY FROM
OFFICES WHERE
SALES<(SELECT AVG(TARGET)
FROM SALESREPS)
5.SEELCT REPID
FROM SALESREPS S
WHERE SALES>(SELECT AVG(SALES)
FROM SALESREPS R
WHERE S.REPID<>R.REPID)
NOTE:'<>' is not equal symbol in MySQL
CIS336: Lab 6: Group Functions and Subqueries I desperatly need help with this MYSQL lab. Any...
If possible please just send the SQL statement. Thank you Each question within deliverable 3 must begin on a new page and be sure to document the question as the title of each item at the top of each page. Also, using a 12-point font, include the SQL statement and then provide a screen shot of each query. The screen shots must include both the SQL statement and the results for each item below based on the data entered in...
This code must be in SQL - Run the script provided with the assignment. This will create the tables used for this assignment. Write the necessary SQL commands to perform the required actions. Run the queries to obtain results from the database. (Make sure all columns returned have proper headings.) 3 2 In a single row show the following values: how many unique manufacturers are in the products table. What is the most expensive price per unit in the products...
Hi, I'm trying to do my homework for my SQL Server class I keep getting stuck on the questions 1-5 I found out like how the format need to be I'm just stuck on implanting the code in. Auntie B's Antic ERD CUSTOMER OWNER PEOPLE PeoplelD CustomerSince CHARITY Peopleld FirstName LastName Address City State Zip Phone CellPhone EMail PeopleType CharityID Name Address City State WORKERS PeoplelD HireDate TermDate SSN ip Phone ContactID ORDERS OrderlD OrderDate CustomerlD EmployeelD DONATION DonationID DonationDate...
Need any help I can get solving this problem. I just started coding less than a month ago. A) Woody Furniture Sales The Woody Furniture Company sells several types of styles of chairs. Write a program that will take from the keyboard input • name of a chair style, • number of chairs sold of that style, • price of each chair of that style. Do this for three chairs (do not use loops i.e. you will need three chair...
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...
I need help with certain questions. Please. 18. 4 points For each Rental, list the Rental ID, Rental date, customer ID, customer first name, customer last name, and count of disks rented; sort by Rental ID. Show the Rental date formatted as ‘mm-dd-yyyy.’ Hint: use a GROUP BY clause. 19. 4 points List the disk ID, title name, rating, format description, and fee amount for all copies rented in Rental 3; sort by disk ID. Show the fee amount formatted...
I need help with these functions please Quickstove ships worldwide and this is but a sample of shipments to the Western United States. When multiple quantities of an item are in the same order, quantity discounts on that item are offered. Customers are charged sales tax on all shipments within Utah where Quickstove is located as well as on shipments to Oregon where they have another facility Sales tax is not charged on shipments to other states. Task # Points...
Missing multiple labeled functions. Card matching game in C. Shouldn't need any more functions. I am lost on how to complete the main function (play_card_match) without the sub functions complete. The program runs fine as is, but does not actually have a working turn system. It should display question marks on unflipped cards when the player is taking a turn and should also clear the screen so the player can't scroll up to cheat. Thank you #include "cardMatch.h" //main function /*...
really need help i have been working on this for hours and barely made any progress, please complete second portion of hw problem from requirments 2-7 Requirements 1. Prepare Trenton's operating budget and cash budget for 2019 by quarter. Required schedules and budgets include: sales budget, production budget direct materials budget, direct labor budget, manufacturing overhead budget, cost of goods sold budget, selling and administrative expense budget, schedule of cash receipts, schedule of cash payments, and cash budget. Manufacturing overhead...
Requires Python to answer A client wishes to keep track of his investment in shares. Write a program to help him manage his stock portfolio. You are to record both the shares that he is holding as well as shares that he has sold. For shares be is curreatly holding, record the following data: .a 3-character share code, share name, last purchase date, volume currently held and average purchase price (refer to description under part cii) option 2) For shares...