Notes

CREATE TABLE CUSTOMER (
CUS_CODE INT PRIMARY KEY,
CUS_LNAME VARCHAR(15) NOT NULL,
CUS_FNAME VARCHAR(15) NOT NULL,
CUS_INITIAL CHAR(1) NOT NULL,
CUS_AREACODE CHAR(3) NOT NULL,
CUS_PHONE CHAR(8) NOT NULL,
CUS_BALANCE DECIMAL(9,2) NULL,
)


CREATE TABLE INVOICE (
INV_NUMBER INT PRIMARY KEY,
CUS_CODE INT NOT NULL REFERENCES CUSTOMER(CUS_CODE),
INV_DATE DATE NOT NULL);

CREATE TABLE VENDOR (
V_CODE INTEGER NOT NULL UNIQUE,
V_NAME VARCHAR(35) NOT NULL,
V_CONTACT VARCHAR(15) NOT NULL,
V_AREACODE CHAR(3) NOT NULL,
V_PHONE CHAR(8) NOT NULL,
V_STATE CHAR(2) NOT NULL,
V_ORDER CHAR(1) NOT NULL,
PRIMARY KEY (V_CODE));

CREATE TABLE PRODUCT (
P_CODE VARCHAR(10) NOT NULL UNIQUE,
P_DESCRIPT VARCHAR(35) NOT NULL,
P_INDATE DATE NOT NULL,
P_QOH SMALLINT NOT NULL,
P_MIN SMALLINT NOT NULL,
P_PRICE DECIMAL(8,2) NOT NULL,
P_DISCOUNT DECIMAL(5,2) NOT NULL,
V_CODE INTEGER,
PRIMARY KEY (P_CODE),
FOREIGN KEY (V_CODE) REFERENCES VENDOR);


CREATE TABLE LINE (
INV_NUMBER INT NOT NULL,
LINE_NUMBER DECIMAL(2,0) NOT NULL,
P_CODE VARCHAR(10) NOT NULL,
LINE_UNITS DECIMAL(9,2) NOT NULL,
LINE_PRICE DECIMAL(9,2) NOT NULL,
PRIMARY KEY (INV_NUMBER,LINE_NUMBER),
FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE(INV_NUMBER),
FOREIGN KEY (P_CODE) REFERENCES PRODUCT(P_CODE),
CONSTRAINT LINE_UI1 UNIQUE(INV_NUMBER, P_CODE));


AGREGATE FUNCTONS

SELECT COUNT (P_CODE) AS COUNTPRODUC FROM PRODUCT;

SELECT COUNT (P_CODE) FROM PRODUCT WHERE P_PRICE > 19 ;

SELECT COUNT (P_CODE) AS COUNTDISTINCT FROM PRODUCT;

SELECT MAX(P_PRICE) AS MAXPRICE, MIN (P_PRICE) AS MINPRICE FROM PRODUCT;

SELECT MAX (P_INDATE) FROM PRODUCT;

SELECT SUM (CUS_BALANCE) AS TOTALBALANCE FROM CUSTOMER;

SELECT SUM (P_QOH * P_PRICE) AS TOTALVALUE FROM PRODUCT;

SELECT AVG (P_PRICE) AS AVGPRICE FROM PRODUCT;

SELECT V_CODE, AVG (P_PRICE) AS AVGPRICE FROM PRODUCT GROUP BY V_CODE;

SELECT VENDOR.V_CODE, V_NAME, COUNT (P_CODE) AS NUMPROD, AVG (P_PRICE) AS AVGPRICE
FROM PRODUCT JOIN VENDOR
ON PRODUCT.V_CODE = VENDOR.V_CODE
GROUP BY VENDOR.V_CODE,V_NAME
ORDER BY V_NAME;

SELECT VENDOR.V_CODE,V_NAME, SUM(P_QOH * P_PRICE) AS TOTALCOST
FROM PRODUCT JOIN VENDOR ON PRODUCT.V_CODE = VENDOR.V_CODE
WHERE P_DESCRIPT>0
GROUP BY VENDOR.V_CODE,V_NAME
HAVING (SUM (P_QOH * P_PRICE) > 500)
ORDER BY SUM (P_QOH * P_PRICE) DESC;