ex1.sql (2049B)
1 -- sqlite3 -- 2 PRAGMA foreign_keys = ON; 3 .schema 4 .header on 5 .mode column 6 7 -- Part 1 -- 8 CREATE TABLE INSURANCE_COVERS ( 9 COVER_NO INT(2) NOT NULL, 10 DESC VARCHAR, 11 YEARLY_COST INT(2) NOT NULL, 12 MIN_VALID_MONTHS INT(2) NOT NULL, 13 PRIMARY KEY(COVER_NO) 14 ); 15 16 CREATE TABLE CUSTOMERS ( 17 FULL_NAME VARCHAR, 18 ADDRESS VARCHAR, 19 TEL VARCHAR, 20 VAT_NO VARCHAR, 21 TAX_OFFICE VARCHAR, 22 PRIMARY KEY(VAT_NO) 23 ); 24 25 CREATE TABLE CONTRACTS ( 26 COVER_NO INT(2) NOT NULL, 27 VAT_NO VARCHAR, 28 START_DATE DATE, 29 EXPIRE_DATE DATE, 30 FOREIGN KEY(VAT_NO) REFERENCES CUSTOMERS(VAT_NO), 31 FOREIGN KEY(COVER_NO) REFERENCES INSURANCE_COVERS(COVER_NO) 32 ); 33 34 -- Part 2 -- 35 INSERT INTO INSURANCE_COVERS VALUES 36 (1, "Health Insurance", 200, 12), 37 (2, "Critical Illness Cover", 100, 12), 38 (3, "Home Insurance", 150, 24), 39 (4, "Car Insurance", 120, 12); 40 41 INSERT INTO CUSTOMERS VALUES 42 ("Foo Bar", "Nice Street 4", "2101234567", "123456789", "Peristeri"), 43 ("Bob Baz", "Bad Avenue", "2113216549", "321654987", "Acharnes"); 44 45 INSERT INTO CONTRACTS VALUES 46 (1, "123456789", "2021-03-02", "2022-03-02"), 47 (2, "123456789", "2020-12-01", "2021-12-01"), 48 (3, "321654987", "2020-05-10", "2022-05-10"), 49 (3, "123456789", "2020-07-11", "2022-07-11"); 50 51 -- Part 4 -- 52 CREATE VIEW info AS 53 SELECT FULL_NAME "Customer name", 54 DESC AS "Coverage", 55 VAT_NO as "VAT number", 56 START_DATE AS "Start date", 57 EXPIRE_DATE AS "Expiration date" 58 FROM CUSTOMERS 59 INNER JOIN CONTRACTS USING (VAT_NO), 60 INSURANCE_COVERS USING(COVER_NO) 61 ORDER BY FULL_NAME; 62 63 CREATE VIEW updatable_info AS 64 SELECT FULL_NAME, VAT_NO, ADDRESS, TEL, TAX_OFFICE 65 FROM CUSTOMERS 66 ORDER BY FULL_NAME; 67 68 -- TODO: make trigger -- 69 70 -- Part 5 -- 71 SELECT INSURANCE_COVERS.DESC AS "Contract", 72 COUNT(CONTRACTS.COVER_NO) AS "Number of contracts" 73 FROM CONTRACTS 74 INNER JOIN INSURANCE_COVERS USING(COVER_NO) 75 GROUP BY CONTRACTS.COVER_NO; 76 77 SELECT FULL_NAME AS "Customer", 78 SUM(YEARLY_COST) AS "Total contract cost" 79 FROM CONTRACTS 80 INNER JOIN INSURANCE_COVERS USING (COVER_NO) 81 INNER JOIN CUSTOMERS USING (VAT_NO) 82 GROUP BY VAT_NO 83 ORDER BY SUM(YEARLY_COST) DESC;