\echo '--- Question 4 ---'
\set ON_ERROR_STOP on

DROP TABLE IF EXISTS ProjAssigned;
DROP TABLE IF EXISTS Employee;

CREATE TABLE Employee(
  "emp-no" bigint primary key,
  name varchar(255),
  department varchar(255),
  salary decimal
);

CREATE TABLE ProjAssigned(
  "proj-no" bigint primary key,
  "emp-no" bigint references Employee("emp-no"),
  "worked-hours" integer
);

INSERT INTO Employee VALUES (1, 'Tsuyoshi Garrett', 'X', 200000);
INSERT INTO Employee VALUES (2, 'Peter Parker', 'X', 300000);
INSERT INTO Employee VALUES (3, 'Diana Prince', 'X', 400000);
INSERT INTO Employee VALUES (4, 'Tony Stark', 'X', 500000);

INSERT INTO ProjAssigned VALUES (1, 3, 120);
INSERT INTO ProjAssigned VALUES (2, 2, 40);
INSERT INTO ProjAssigned VALUES (3, 1, 80);

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;
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', 31, 100000);
INSERT INTO TravelAgent VALUES('B', 32, 100000);
INSERT INTO TravelAgent VALUES('C', 33, 100000);
INSERT INTO TravelAgent VALUES('D', 34, 100000);
INSERT INTO TravelAgent VALUES('E', 34, 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', 'Ottawa', 'Coach');

INSERT INTO Transaction VALUES(1, 'I', 'A', 10000.0);
INSERT INTO Transaction VALUES(2, 'II', 'B', 10000.0);
INSERT INTO Transaction VALUES(3, 'III', 'A', 10000.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;

-- Display the name of the oldest travel agent.

SELECT t.name
FROM TravelAgent t
WHERE t.age IN (
  SELECT MAX(age) from TravelAgent
);

-- List the total number of transactions for each travel agent.
-- Consider only those transactions where the amount paid exceeds 1 000.

SELECT travel_agent_name, COUNT(travel_agent_name)
FROM Transaction
WHERE amount_paid > 1000.0
GROUP BY travel_agent_name
ORDER BY travel_agent_name;

-- Display the names and ages of the travel agents who have arranged journeys for customer "John Smith", in descending order of age (use a subquery).

SELECT name, age
FROM TravelAgent
WHERE name IN (
  SELECT DISTINCT(travel_agent_name)
  FROM Transaction
  WHERE cust_name = 'John Smith'
)
ORDER BY age DESC;

-- Display the names and ages of travel agents who have arranged journeys for customer “John Smith”, in descending order of age (do not use a subquery).

SELECT DISTINCT(ta.name), ta.age
FROM TravelAgent ta
INNER JOIN Transaction t ON ta.name = t.travel_agent_name
WHERE t.cust_name = 'John Smith'
ORDER BY ta.age DESC;

-- Display the age of travel agents who have arranged journeys for customer “John Smith” to “Ottawa” (use a subquery).

SELECT age
FROM TravelAgent
WHERE name IN (
  SELECT DISTINCT(travel_agent_name)
  FROM Transaction
  WHERE cust_name IN (
    SELECT DISTINCT(name)
    FROM Customer
    WHERE name = 'John Smith'
    AND destination = 'Ottawa'
  )
);

-- Display the age of travel agents who have arranged journeys for customer “John Smith” to “Ottawa” (do not use a subquery).

SELECT DISTINCT(ta.age)
FROM TravelAgent ta
INNER JOIN Transaction t ON t.travel_agent_name = ta.name
INNER JOIN Customer c ON c.name = t.cust_name
WHERE c.destination = 'Ottawa'
AND c.name = 'John Smith';

-- Display the names and salaries of all travel agents who did not arrange journeys for customer “John Smith”, in ascending order of salary.

SELECT Distinct(ta.name), ta.salary
FROM TravelAgent ta
WHERE ta.name NOT IN (
  SELECT travel_agent_name
  FROM Transaction
  WHERE cust_name = 'John Smith'
)
ORDER BY ta.salary ASC;

-- Display the names of travel agents who have five or more transactions.

SELECT DISTINCT(t.travel_agent_name)
FROM Transaction t
GROUP BY t.travel_agent_name
HAVING COUNT(t.travel_agent_name) > 5;

-- Display the names of all travel agents who have arranged at least ten journeys to “Ottawa”.

SELECT DISTINCT(t.travel_agent_name)
FROM Transaction t
INNER JOIN Customer c on t.cust_name = c.name
WHERE c.destination = 'Ottawa'
GROUP BY t.travel_agent_name
HAVING COUNT(t.travel_agent_name) > 10;