Using the database provided, please answer the following questions. 2. List the
ID: 3694766 • Letter: U
Question
Using the database provided, please answer the following questions.
2. List the unique names of Bob's suppliers.
3. List the names of the suppliers and the total amount owed to any unpaid suppliers. Then write a separate query to display the days past due for each supplier.
4. Write an insert statement or series of insert statements if necessary, to insert data into the required tables when a bid is entered.
5. Write a delete statement(s) to delete a particular customer from the customer table (make sure to delete any corresponding rows in other tables).
CREATE TABLE CUSTOMER (
CUST_ID int,
CUST_NAME varchar(60),
CUST_ADDRESS varchar(60),
CUST_PHONE varchar(11),
CUST_EMAIL varchar(60),
CUS_BALANCE float(8),
AMT_PAID float(8)
TOTAL_DUE float(8)
CREATE TABLE INVOICE (
INVOICE_ID int,
CUST_ID int,
INV_DUEDATE datetime,
CUS_BALANCE float(8),
AMT_PAID float(8),
TOTAL_DUE float(8),
CREATE TABLE SUPPLY (
SUPPLY_ID int,
SUPPLY_NAME varchar(15),
SUPPLY_CONTACT varchar(50),
SUPPLY_PHONE varchar(11),
PENDING_ORDER varchar(1),
ORDER_PAID varchar(1),
QUANTITY float(8),
ITEM_COST float(8),
AMT_PAID float(8),
TOTAL_DUE float(8),
CREATE TABLE ORDER (
ORDER_ID int,
SUPPLY_ID int,
VENDOR_ID int,
ORDER_DATE datetime,
ITEM_NAME varchar(60),
QUANTITY float(8),
ORDER_PAID varchar(1),
TOTAL_DUE float(8),
CREATE TABLE PROJECT (
PROJECT_ID int,
PROJECT_NAME varchar(60),
TOTAL_DUE float(8),
CUST_ID int,
PROJECT_LOCATION varchar(60),
CREATE TABLE VENDOR (
VENDOR_ID int,
TOTAL_DUE float(8),
CREATE TABLE BILL (
BILL_ID int,
VENDOR_ID int,
ORDER_ID int,
BILL_DATE datetime,
BILL_PAID varchar(1),
TOTAL_DUE float (8),
Explanation / Answer
2. List the unique names of Bob's suppliers.
SE1ECT DISTINCT SUPP_NAME FROM SUPPLIER;
3. List the names of the suppliers and the total amount owed to any unpaid suppliers. Then write a separate query to display the days past due for each supplier.
SE1ECT SUPPLIER.SUPP_NAME, SUPPLIER_BILL.SUPP_BALANCE
FROM SUPPLIER
FU11 OUTER JOIN SUPPLIER_BI11
ON SUPPLIER.SUPP_NUM = SUPPLIER_BILL.SUPP_NUM
ORDER BY SUPPLIER.SUPP_NAME;
SELECT SUPP_NAME,SUPP_DUE,(SE1ECT F1OOR(SYSDATE-SUPP_DUE)FROM
DUAL)AS "PAST DUE"
FROM SUPPLIER
FULL OUTER JOIN SUPPLIER_BILL
ON SUPPLIER.SUPP_NUM = SUPPLIER_BILL.SUPP_NUM
ORDER BY SUPPLIER.SUPP_NAME;
4. Write an insert statement or series of insert statements if necessary, to insert data into the required tables when a bid is entered.
INSERT INTO BID VALUES('00004','12-APR-2015','60.00','40.00','10-MAY-2015','1003','4');
INSERT INTO CUSTOMER_BILL VALUES('201500005','00004','130.00','12-MAY-2015','530.00','400.00','14- MAY-2015','6');
5. Write a delete statement(s) to delete a particular customer from the customer table (make sure to delete any corresponding rows in other tables
DELETE FROM BID WHERE CUST_NUM ='1001';
DELETE FROM CUSTOMERWHERE CUST_NUM = '1001';
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.