Question

Answer all questions on paper: Consider the database model below The database model FIGURE 7.1 CUSTOMER INVOICE LINE PK CUS CODE INV NUMBER FK1 CUS_COOE NV DATE CUS LNAME CUS FNAME CUS INITIAL CUS AREACODE CUS PHONE CUS BALANCE LINE UNITS LINEPRICE - isfound in VENDOR PRODUCT V NAME supplies P INDATE P DESCRIPT CONTACT de- V AREACODE V PHONE V STATE V ORDER ?,.QOH P MIN P PRICE 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: V-cODE (integer), V_NAME, VCONTACT, V AREACODE, V_PHONE, V_STATE, V_ORDER(string) PRODUCT: P.DESCRIPT(String), P INDATE (date), P.00H, P.MIN, P.PRICE, PDISCOUNT(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 ) The description, quantity on hold(P_Q0H), the minimum quantity, the price and the date for whose price is below $10.00 each product whose inventory date (P_INDATE) is on or after January 20, 2012

Answer 1

Solution:

The first four subparts have been answered as per Chegg guidelines, please repost others.

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'

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)