I need it as soon as possible please CS346 A5.5: Due Apeil 5 L Ghemei Download t
ID: 3701308 • Letter: I
Question
I need it as soon as possible please
CS346 A5.5: Due Apeil 5 L Ghemei Download the CREATE_P_v.sql file from dropbox Make sure not to delete any working query as you answer more questions Once all your queries are correct and returning results, set sal to send responses to a fäe Email your file to your instructor Write the SQL queries to retrieve the following: 2) The description, quantity on holdP Q0Hl, the minimum quantity and the price for each prodast whose price is below $10.00 2) The description, quantity an holdjP_Q0HI, the minimum quantity, the price and the date for each product whose inventory date IP INDATE) is on or after January 20, 2012 3) The description, quantity on holdlP_00Hl, the unit price and total price of the inventary, as TOTValue for each product. 4) List of all products, the date they were received and the warranty expiratian date (90 days from receiving the product) as ExpDate 5) List all the products supplied by vendor 21344 or 24288 The description, inventory date (P INDATEL, the price and the date for each product whose 6) inventory date (P INDATE) is after January 15, 2012 and price is less than $s0 7) The description, inventory date (P INDATEl, the price and the date for each product whose inventory date (P INDATE) is after January 15, 2012 and price is less than $50 OR the vendor number is 24288 8) Retrieve all inforrmation on products not supplied by vendor 21344 9) Retrieve all information on products whose price is between $5D and $100 10) List all products that do not have a vendor assigned to them 11) List the description and prices of all products ordering them from higher to lower 12) Retrieve the code, description and price of the most and least expensive product. 13) Retrieve the total customer balance from the table customer 14) Retrieve the product codes, descriptions and the names of the vendors that supply them 15) For each vendor, list all the products they supply.Explanation / Answer
Solution:
1)
SELECT description, MIN(P_QOH), unitPrice FROM Product. WHERE unitPrice<'10.00'
2)
SELECT description, MIN(P_QOH), P_INDATE FROM Product. WHERE P_INDATE >'January 20, 2012'
3)
SELECT description, P_QOH, unitPrice, SUM(unitPrice) AS TOTValue FROM Product.
4)
SELECT prdoductID, recieveDate, (recieveDate+90) AS ExpDate FROM Product.
5)
SELECT productID FROM Supply WHERE vendorNumber= '21344' OR vendorNumber= '24288'
6)
SELECT description, P_INDATE, price, date FROM Product WHERE (P_INDATE> 'January 15 2015' AND price<'50') OR vendorNumber= '24288'
7)
select P_DESCRIPT,P_INDATE,P_PRICE FROM PRODUCT WHERE (P_INDATE>'2012-01-15' AND P_PRICE <50) OR (V_CODE = 24288);
8)
SELECT * FROM PRODUCT WHERE V_CODE != 24288;
9)
SELECT * FROM PRODUCT WHERE P_PRICE>50 AND P_PRICE<100;
NOTE: 50 and 100 not including the result
10)
Vendor code is FOREIGN KEY in PRODUCT so V_CODE MUST BE PRESNT IN Vendor so this type all product s have at least one vendor.
11)
select P_DESCRIPT, P_PRICE FROM PRODUCT ORDERED BY P_PRICE DESC;
12)
SELECT P_CODE,P_DESCRIPT,P_PRICE FROM PRODUCT WHERE P_PRICE=
(SELECT MIN(P_PRICE) FROM PRODUCT) UNION
SELECT P_CODE,P_DESCRIPT,P_PRICE FROM PRODUCT WHERE P_PRICE=
(SELECT MAX(P_PRICE) FROM PRODUCT);
NOTE; First find min price and find all product with price equal to min price and find max price and find all product with price equal to max price and take union of both the result.
13)
SELECT SUM(CUS_BALANCE) FROM CUSTOMER;
14)
do cross product in VENDOR AND PRODUCT and select product code and description and vendor name
SELECT VENDOR.V_NAME,PRODUCT.P_CODE,PRODUCT.P_DESCRIPT FROM VENDOR,PRODUCT WHERE VENDOR.V_CODE=PRODUCT.V_CODE;
15)
SAME AS 14 but select only P_CODE AND V_CODE::
SELECT VENDOR.V_NAME,VENDOR.V_CODE,PRODUCT.P_CODE, FROM VENDOR,PRODUCT WHERE VENDOR.V_CODE=PRODUCT.V_CODE;
I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)