Write some SQL

1 files changed, 46 insertions, 32 deletions

diff --git a/assignments/2/run.sql b/assignments/2/run.sql
index ef50a67..d7bffcb 100644
--- a/assignments/2/run.sql
+++ b/assignments/2/run.sql
@@ -1,4 +1,4 @@
--- \echo '--- Question 4 ---'
+\echo '--- Question 4 ---'
 \set ON_ERROR_STOP on
 
 DROP TABLE IF EXISTS ProjAssigned;
@@ -26,43 +26,57 @@ INSERT INTO ProjAssigned VALUES (1, 3, 120);
 INSERT INTO ProjAssigned VALUES (2, 2, 40);
 INSERT INTO ProjAssigned VALUES (3, 1, 80);
 
-/*
-                                                        QUERY PLAN                                                    >
----------------------------------------------------------------------------------------------------------------------->
- Sort  (cost=68.22..69.62 rows=559 width=536) (actual time=0.021..0.021 rows=3 loops=1)
-   Sort Key: e.name
-   Sort Method: quicksort  Memory: 25kB
-   ->  Hash Join  (cost=11.16..42.71 rows=559 width=536) (actual time=0.015..0.016 rows=3 loops=1)
-         Hash Cond: (pa."emp-no" = e."emp-no")
-         ->  Seq Scan on projassigned pa  (cost=0.00..27.00 rows=1700 width=20) (actual time=0.003..0.003 rows=3 loops>
-         ->  Hash  (cost=10.88..10.88 rows=23 width=524) (actual time=0.010..0.010 rows=4 loops=1)
-               Buckets: 1024  Batches: 1  Memory Usage: 9kB
-               ->  Seq Scan on employee e  (cost=0.00..10.88 rows=23 width=524) (actual time=0.002..0.003 rows=4 loops>
-                     Filter: (salary > 66000.0)
- Planning time: 0.053 ms
- Execution time: 0.033 ms
-(12 rows)
-*/
-
 CREATE INDEX employees_idx ON ProjAssigned("emp-no");
 CREATE INDEX employee_salary_idx ON Employee(salary);
 CREATE INDEX employee_names_idx ON Employee(name ASC);
 -- CREATE INDEX employee_composite_idx ON Employee(salary, name ASC);
-
-/*
-*/
-
-\d Employee;
-\d ProjAssigned;
-
-SELECT e."emp-no", e.name, pa."proj-no", pa."worked-hours"
-FROM Employee e
-INNER JOIN ProjAssigned pa ON pa."emp-no" = e."emp-no"
-WHERE e.salary > 66000.0
-ORDER BY e.name ASC;
-
+-- \d Employee;
+-- \d ProjAssigned;
 EXPLAIN ANALYZE SELECT e."emp-no", e.name, pa."proj-no", pa."worked-hours"
 FROM Employee e
 INNER JOIN ProjAssigned pa ON pa."emp-no" = e."emp-no"
 WHERE e.salary > 66000.0
 ORDER BY e.name ASC;
+
+
+DROP TABLE IF EXISTS TravelAgent;
+DROP TABLE IF EXISTS Customer;
+DROP TABLE IF EXISTS Transaction;
+
+CREATE TABLE TravelAgent(name varchar(255), age integer, salary decimal);
+CREATE TABLE Customer(
+	name varchar(255),
+	departure_city varchar(255),
+	destination varchar(255),
+	journey_class varchar(255)
+);
+CREATE TABLE Transaction(
+	"number" integer,
+	cust_name varchar(255),
+	travel_agent_name varchar(255),
+	amount_paid decimal
+);
+
+INSERT INTO TravelAgent VALUES('A', 30, 100000);
+INSERT INTO TravelAgent VALUES('B', 30, 100000);
+INSERT INTO TravelAgent VALUES('C', 30, 100000);
+INSERT INTO TravelAgent VALUES('D', 30, 100000);
+INSERT INTO TravelAgent VALUES('E', 30, 100000);
+
+INSERT INTO Customer VALUES ('I', 'Calgary', 'Seattle', 'Coach');
+INSERT INTO Customer VALUES ('II', 'Calgary', 'Vancouver', 'Coach');
+INSERT INTO Customer VALUES ('III', 'Calgary', 'Toronto', 'Coach');
+INSERT INTO Customer VALUES ('IV', 'Calgary', 'Montreal', 'Coach');
+INSERT INTO Customer VALUES ('V', 'Calgary', 'Edmonton', 'Coach');
+
+INSERT INTO Transaction VALUES(1, 'I', 'A', 1000.0);
+INSERT INTO Transaction VALUES(2, 'II', 'B', 1000.0);
+INSERT INTO Transaction VALUES(3, 'III', 'A', 1000.0);
+INSERT INTO Transaction VALUES(4, 'I', 'A', 1000.0);
+
+\echo '--- Question 5 ---'
+-- Compute the number of different customers who have a transaction.
+
+SELECT COUNT(DISTINCT c.name)
+FROM Customer c
+INNER JOIN Transaction t ON t.cust_name = c.name;