uni

commit 60ca8f39798dc55d848faaa024536a49cc3102ec
parent bd3fdb78daf038d3a1f6ac1d7c4aad09e19bb795
Author: Christos Margiolis <christos@margiolis.net>
Date:   Thu,  9 Dec 2021 19:06:37 +0200

almost done with sql assignment (boring)

Diffstat:
A
sql_databases2/ex1.sql
 | 
83
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
M
vhdl_digital_design/project/part7_mips_r_ops/mips_tb.vhd
 | 
2
+-

2 files changed, 84 insertions(+), 1 deletion(-)
diff --git a/sql_databases2/ex1.sql b/sql_databases2/ex1.sql
@@ -0,0 +1,83 @@
+-- sqlite3 --
+PRAGMA foreign_keys = ON;
+.schema
+.header on
+.mode column
+
+-- Part 1 --
+CREATE TABLE INSURANCE_COVERS (
+	COVER_NO INT(2) NOT NULL,
+	DESC VARCHAR,
+	YEARLY_COST INT(2) NOT NULL,
+	MIN_VALID_MONTHS INT(2) NOT NULL,
+	PRIMARY KEY(COVER_NO)
+);
+
+CREATE TABLE CUSTOMERS (
+	FULL_NAME VARCHAR,
+	ADDRESS VARCHAR,
+	TEL VARCHAR,
+	VAT_NO VARCHAR,
+	TAX_OFFICE VARCHAR,
+	PRIMARY KEY(VAT_NO)
+);
+
+CREATE TABLE CONTRACTS (
+	COVER_NO INT(2) NOT NULL,
+	VAT_NO VARCHAR,
+	START_DATE DATE,
+	EXPIRE_DATE DATE,
+	FOREIGN KEY(VAT_NO) REFERENCES CUSTOMERS(VAT_NO),
+	FOREIGN KEY(COVER_NO) REFERENCES INSURANCE_COVERS(COVER_NO)
+);
+
+-- Part 2 --
+INSERT INTO INSURANCE_COVERS VALUES
+	(1, "Health Insurance", 200, 12),
+	(2, "Critical Illness Cover", 100, 12),
+	(3, "Home Insurance", 150, 24),
+	(4, "Car Insurance", 120, 12);
+
+INSERT INTO CUSTOMERS VALUES
+	("Foo Bar", "Nice Street 4", "2101234567", "123456789", "Peristeri"),
+	("Bob Baz", "Bad Avenue", "2113216549", "321654987", "Acharnes");
+
+INSERT INTO CONTRACTS VALUES
+	(1, "123456789", "2021-03-02", "2022-03-02"),
+	(2, "123456789", "2020-12-01", "2021-12-01"),
+	(3, "321654987", "2020-05-10", "2022-05-10"),
+	(3, "123456789", "2020-07-11", "2022-07-11");
+
+-- Part 4 --
+CREATE VIEW info AS
+	SELECT FULL_NAME "Customer name",
+	DESC AS "Coverage",
+	VAT_NO as "VAT number",
+	START_DATE AS "Start date",
+	EXPIRE_DATE AS "Expiration date"
+	FROM CUSTOMERS
+	INNER JOIN CONTRACTS USING (VAT_NO),
+	INSURANCE_COVERS USING(COVER_NO)
+	ORDER BY FULL_NAME;
+
+CREATE VIEW updatable_info AS
+	SELECT FULL_NAME, VAT_NO, ADDRESS, TEL, TAX_OFFICE
+	FROM CUSTOMERS
+	ORDER BY FULL_NAME;
+
+-- TODO: make trigger --
+
+-- Part 5 --
+SELECT INSURANCE_COVERS.DESC AS "Contract",
+	COUNT(CONTRACTS.COVER_NO) AS "Number of contracts"
+	FROM CONTRACTS
+	INNER JOIN INSURANCE_COVERS USING(COVER_NO)
+	GROUP BY CONTRACTS.COVER_NO;
+
+SELECT FULL_NAME AS "Customer",
+	SUM(YEARLY_COST) AS "Total contract cost"
+	FROM CONTRACTS
+	INNER JOIN INSURANCE_COVERS USING (COVER_NO)
+	INNER JOIN CUSTOMERS USING (VAT_NO)
+	GROUP BY VAT_NO
+	ORDER BY SUM(YEARLY_COST) DESC;
diff --git a/vhdl_digital_design/project/part7_mips_r_ops/mips_tb.vhd b/vhdl_digital_design/project/part7_mips_r_ops/mips_tb.vhd
@@ -18,7 +18,7 @@ end component;
 begin
 	uut: mips port map (
 		m_clk => s_m_clk,
-		m_rst => s_m_rst,
+		m_rst => s_m_rst
 	);
 
 	process begin