invoices.sql

CREATE TABLE INVOICES
(
	INV_NO char(10) not null,
	INV_DATE DATE not null,
	constraint PK_INVOICES primary key (INV_NO)
)

CREATE TABLE PRODUCTS
(
	PRODUCT_ID int not null,
	NAME varchar(30) not null,
	PRODUCER varchar(40) null,
	constraint PK_PRODUCTS primary key (PRODUCT_ID)
)

CREATE TABLE INVOICE_ITEMS
(
	INV_NO char(10) not null,
	PRODUCT_ID int not null,
	QUANTITY int not null,
	PRICE decimal(6,2) not null,
	constraint PK_INVOICE_ITEMS primary key (INV_NO, PRODUCT_ID)
)

alter table INVOICE_ITEMS
add constraint FK_INVOICE_ITEMS_PRODUCTS foreign key (PRODUCT_ID) references PRODUCTS(PRODUCT_ID);

alter table INVOICE_ITEMS
add constraint FK_INVOICE_ITEMS_INVOICES foreign key (INV_NO) references INVOICES(INV_NO);

alter table PRODUCTS
drop column PRODUCER;

alter table INVOICE_ITEMS
add DDS char(3) null default null constraint FIXED_PERCENTAGE check(DDS is null or DDS IN ('9%', '20%'));

INSERT INTO INVOICES(INV_NO, INV_DATE)
VALUES(1, '2020-02-20');

INSERT INTO PRODUCTS(PRODUCT_ID, "NAME")
VALUES(1, 'Phone'),(2, 'TV');

INSERT INTO INVOICE_ITEMS(INV_NO, PRODUCT_ID, QUANTITY, PRICE, DDS)
VALUES(1, 1, 5, 1000, '20%');

DELETE FROM INVOICE_ITEMS WHERE PRODUCT_ID = 2;
DELETE FROM PRODUCTS WHERE PRODUCT_ID = 2;

UPDATE INVOICE_ITEMS SET QUANTITY = 10 WHERE PRODUCT_ID = 1;

SELECT PRODUCTS.NAME, PRODUCTS.PRODUCT_ID FROM PRODUCTS join INVOICE_ITEMS on PRODUCTS.PRODUCT_ID = INVOICE_ITEMS.PRODUCT_ID;

CREATE VIEW PRODUCTS_VIEW  as
SELECT PRODUCTS.NAME, INVOICE_ITEMS.INV_NO FROM PRODUCTS left join INVOICE_ITEMS on PRODUCTS.PRODUCT_ID = INVOICE_ITEMS.PRODUCT_ID;

INSERT INTO PRODUCTS(PRODUCT_ID, "NAME")
VALUES(3, 'gramophone'), (4, 'walkman');

SELECT PRODUCTS.NAME, AVG(INVOICE_ITEMS.QUANTITY) 
FROM PRODUCTS join INVOICE_ITEMS on PRODUCTS.PRODUCT_ID = INVOICE_ITEMS.PRODUCT_ID
GROUP BY PRODUCTS.NAME
HAVING AVG(INVOICE_ITEMS.QUANTITY) > 2
ORDER BY PRODUCTS.NAME DESC;