Use the SELECT commands to implement the following queries. You need to print ou
ID: 3770051 • Letter: U
Question
Use the SELECT commands to implement the following queries. You need to print out the command you used for each query and the result (table) you received.
1. Which products have a standard price of less than $275?
2. Which products have a standard price of between $275 and $800?
3. What is the address of the customer named Home Furnishings?
4. List the unit price, product name and product ID for all products in the product table.
5. What is the maximum, minimum, and average standard price for each product in inventory?
6. List the difference between current standard price and average standard price for each product in inventory.
7. List each order number and how many different items were ordered on that order number.
8. Which order have been placed since 10/24/2010?
9. List all ordered date in the order table. Note that the date should be presented as "mm/dd/yyyy" format.
10. List product name, finish, and unite price for all desks and tables in the product table that cost more than $300.
11. List product name, finish, and unite price in the product table excluding all desks and tables.
12. List customer, city, and state for all customers in the CUSTOMER table whose address is Florida, Texas, California, or Hawaii. List the customers alphabetically by state, and alphabetically by customer within each state.
13. Find only states with more than one customer.
14. Find the state that is Florida, or California or Hawaii and that has two customers.
15. List the customers who live in California or Washington. Order them by zip code, from high to low.
16. List the number of customer in each state.
17. Which product is ordered most frequently?
18. Which product has the highest quantity ordered?
In SQL
Explanation / Answer
CAN HELP U WITH THIS ANY ISSUES PLEASE COMMENT
1)
SELECT PRODUCT_NAME, STANDARD_PRICE FROM PRODUCT_V
WHERE STANDARD_PRICE < 275;
3)
SELECT CUST.CUSTMER_NAME AS NAME
CUST.CUSTOER_ADDRESS
FROM JACK.CUSTOMER_V AS CUST
WHERE NAME='Home furnishing';
4)
SELECT STANDARD_PRICE,PRODUCT_DESCRIPTION,PRODUCT_ID FROM PRODUCT_T;
6)
Current Cost = The last cost that was used to record an increase in inventory quantity. This could be through an adjustment, receipt or variance, but not a return. If you are using the Average Perpetual inventory costing method, then the current cost is calculated as the average cost
Standard Cost = The standard cost for this item. Standard cost is generally fixed for a period of time (usually a year). Differences between standard and actual cost collect in an inventory variance account in the GL. This is generally used with Periodic inventory methods.
7)
SELECT ORDER_ID, ORDERED_QUANTITY FROM ORDER_LINE_T GROUP BY ORDER_ID;
8)
SELECT OrderID,OrderDate
From Order_T
Where OrderDate>'24-OCT-2010';
10)
SELECT PRODUCT_DESCRIPTION, PRODUCT_FINISH, STANDARD_PRICE FROM PRODUCT_T WHERE STANDARD_PRICE > 300;
12)
SELECT CUSTOMER_NAME, CITY, STATE FROM CUSTOMER_T WHERE STATE IN ('FL','TX', 'CA', 'HI') ORDER BY STATE, CUSTOMER_NAME;
13)
SELECT STATE, COUNT(STATE) FROM CUSTOMER_T GROUP BY STATE HAVING COUNT(STATE)>1 ;
15)
SELECT CUSTOMER_NAME, STATE, POSTAL_CODE FROM CUSTOMER_T WHERE STATE IN ('WA','CA') ORDER BY POSTAL_CODE DESC;
16)
SELECT STATE, COUNT(STATE) FROM CUSTOMER_T GROUP BY STATE ;
17)
SELECT PRODUCT_ID, COUNT(PRODUCT_ID) FROM ORDER_LINE_T GROUP BY PRODUCT_ID HAVING COUNT(PRODUCT_ID)>= ALL(SELECT COUNT(PRODUCT_ID) FROM ORDER_LINE_T GROUP BY PRODUCT_ID);
18)
SELECT PRODUCT_ID, SUM(ORDERED_QUANTITY) AS "TOTAL ORDER" FROM ORDER_LINE_T GROUP BY PRODUCT_ID ORDER BY SUM(ORDERED_QUANTITY) DESC;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.