Using the database provided, please answer the following questions. 1. List the
ID: 3694649 • Letter: U
Question
Using the database provided, please answer the following questions.
1. List the names of the customers who were provided bids last month.
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
1. SELECT CUST_NAME FROM CUSTOMER WHERE EXISTS ( SELECT CUST_ID from INVOICE WHERE CUSTOMER.CUST_ID = INVOICE.CUST_ID AND DATEPART(mm,INV_DUEDATE)=03;
2. SELECT DISTINCT SUPPLY_NAME FROM SUPPLY
3. SELECT SUPPLY_NAME,TOTAL_DUE FROM SUPPLY
SELECT DATEDIFF(day,ORDER_DATE,NOW()) FROM ORDER,SUPPLY WHERE ORDER.SUPPLY_ID = SUPPLY.SUPPLY_ID
4.4. INSERT INTO CUSTOMER VALUES (21,"THOMAS","NEW YORK","655746","thom@outlook.com",4566.09,266.09,255.87)
INSERT INTO INVOICE (45,21,2016-04-27,455.9,266.09,255.87)
INSERT INTO SUPPLY(44,"MOVERS","43535","365666","2","1",45,22,20,2)
INSERT INTO ORDER(20,44,67,2016-04-24,"SHOES",4,"1",22)
INSERT INTO VENDOR(12,22)
INSERT INTO BILL(34,12,20,2016-04-24,"1",22)
5.DELETE FROM CUSTOMER WHERE CUST_ID = 12
DELETE FROM INVOICE WHERE CUST_ID = 12
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.