Last active
February 22, 2020 13:48
-
-
Save Cvetomird91/cb926b9e1eceb1045c25e680cc44b52d to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment