insurance.sql (4179B)
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 3: diagram.png 52 53 -- Part 4 54 CREATE VIEW info AS 55 SELECT full_name "Customer name", 56 desc AS "Coverage", 57 vat_no as "VAT number", 58 start_date AS "Start date", 59 expire_date AS "Expiration date" 60 FROM customers 61 INNER JOIN contracts USING (vat_no), 62 insurance_covers USING(cover_no) 63 ORDER BY full_name; 64 65 CREATE VIEW updatable_info AS 66 SELECT full_name, vat_no, address, tel, tax_office 67 FROM customers 68 ORDER BY full_name; 69 70 SELECT * from info; 71 72 -- Part 5 73 SELECT insurance_covers.desc AS "Contract", 74 count(contracts.cover_no) AS "Number of contracts" 75 FROM contracts 76 INNER JOIN insurance_covers USING(cover_no) 77 GROUP BY contracts.cover_no; 78 79 -- Make this a view so we can make things easier later. 80 CREATE VIEW total_cost_info AS 81 SELECT full_name AS "Customer", 82 sum(yearly_cost) AS "Total contract cost" 83 FROM contracts 84 INNER JOIN insurance_covers USING (cover_no) 85 INNER JOIN customers USING (vat_no) 86 GROUP BY vat_no 87 ORDER BY sum(yearly_cost) desc; 88 89 SELECT * from total_cost_info; 90 91 -- Part 6 92 CREATE TRIGGER cost_of_contracts AFTER INSERT ON insurance_covers 93 BEGIN 94 UPDATE customers SET total = (SELECT sum(yearly_cost) FROM insurance_covers) + new.yearly_cost 95 WHERE customers.vat_no = new.vat_no; 96 END; 97 98 INSERT INTO contracts VALUES (4, "123456789", "2022-05-10", "2023-05-10"); 99 SELECT * from total_cost_info; 100 101 -- Part 7 (SQLite doesn't have CREATE FUNCTION so this part is commented out) 102 -- DELIMETER // 103 -- CREATE FUNCTION contract_duration(start DATE, end DATE) 104 -- RETURNS INT 105 -- DETERMINISTIC 106 -- BEGIN 107 -- RETURN datediff(end, start); 108 -- END // 109 -- DELIMETER ; 110 -- 111 -- Test function. 112 -- SELECT contract_duration( 113 -- SELECT start_date FROM contracts WHERE vat_no = "123456789", 114 -- SELECT expire_date FROM contracts WHERE vat_no = "123456789" 115 -- ); 116 117 118 -- Part 8 (SQLite doesn't have procedures either) 119 -- DELIMETER // 120 -- CREATE PROCEDURE active_contracts(vat_no_arg VARCHAR, date_arg DATE) 121 -- BEGIN 122 -- DECLARE start_row INT DEFAULT 0; 123 -- DECLARE end_row INT DEFAULT 0; 124 -- DECLARE p_vat_no VARCHAR, 125 -- DECLARE p_cover_no INT, 126 -- DECLARE p_start_date DATE, 127 -- DECLARE p_end_date DATE, 128 -- DECLARE contr_count INT DEFAULT 0; 129 -- DECLARE COST INT; 130 -- DECLARE pending_cost INT DEFAULT 0; 131 -- DECLARE contr_ptr CURSOR FOR SELECT * FROM contracts; 132 -- 133 -- SET start_row := 0; 134 -- SELECT count(*) FROM contracts INTO end_row; 135 -- OPEN contr_ptr; 136 -- WHILE (start_row < end_row) DO 137 -- FETCH contr_ptr INTO p_vat_no, p_cover_no, p_start_date, p_end_date; 138 -- SELECT yearly_cost FROM insurance_covers WHERE cover_no = p_cover_no INTO cost; 139 -- 140 -- IF (p_vat_no = vat_no_arg AND date_arg >= p_start_date AND date_arg <= p_end_date) THEN 141 -- SET contr_count := contr_count + 1; 142 -- SET pending_cost := pending_cost + cost; 143 -- END IF; 144 -- SET start_row := start_row + 1; 145 -- END WHILE; 146 -- SELECT contr_count AS "Active contracts", pending_cost/12 AS "Contract cost per month"; 147 -- END // 148 -- DELIMETER ; 149 -- 150 -- Test procedure. 151 -- CALL active_contracts("123456789", "2021-10-10");