uni

University stuff
git clone git://git.margiolis.net/uni.git
Log | Files | Refs | README | LICENSE

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");