Please answer CLEARLY and LEGIBLE from question 5-15. Consider the database mode

Question

Please answer CLEARLY and LEGIBLE from question 5-15. Consider the database model below: The database model FIGURE 7.1 CUSTOMER INVOICE LINE PK INV PK.FK1 |INV NUM?ER PK ?LINE.NUMBER PK CUS CODE generates FK1 CUS CODE CUS LNAME CUS FNAME CUS INITIAL CUS AREACODE CUS PHONE CUS BALANCE INV DATE FK2 P CODE LINE UNITS LINE PRICE is found in VENDOR PRODUCT PK P CODE V NAME V-CONTACT V AREACODE V PHONE V STATE V ORDER P DESCRIPT -sug-es- -O P INDATE P MIN P PRICE P DISCOUNT FK1 V CODE Write the SQL code for creating the database and all the above tables. State all primary keys and foreign keys constraints. CUSTOMER: CUS_ CODE(Numeric), CUS-FNAME, CUS LNAME, CUS-INITIAL, AREACODE, CUS- PHONE(strings), CUSR BALANCE(numeric) VENDOR: VCODE (integer), V NAME, vCONTACT, V.AREACODE, V.PHONE, V STATE, V ORDER(string) PRODUCT: P.DESCRIPT (String), P INDATE (date), P.Q0H, P MIN, P PRICE, P DISCOUNT(numeric), INVOICE: INV_NUMBER (numeric), INV_DATE (date) LINE: LINE NUMBER, LINE_UNITS, LINE PRICE (numeric), P_CODE(string). Write the SQL queries to retrieve the following: 1) The description, quantity on hold(P Q0H), the minimum quantity and the price for each product whose price is below $10.00 The description, quantity on hold(P_QOH), the minimum quantity, the price and the date for each product whose inventory date (P_ INDATE) is on or after January 20, 2012 2)

Explanation / Answer

5)select P_CODE from PRODUCT where v_code = 21344 UNION select P_CODE from PRODUCT where V_CODE = 24288;

6)select P_DESCRIPT,P_INDATE,P_PRICE FROM PRODUCT WHERE P_INDATE>'2012-01-15' AND P_PRICE <50;

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;

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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