.mode column
.header ON

CREATE TABLE Payroll (
  UserID INT PRIMARY KEY,
  Name VARCHAR(256),
  Job VARCHAR(256),
  Salary INT);



INSERT INTO Payroll
VALUES (123, 'Jack', 'TA', 50000),
       (345, 'Allison', 'TA', 60000),
       (567, 'Magda', 'Prof', 90000),
       (789, 'Dan', 'Prof', 100000);


CREATE TABLE Regist (
  UserID INT REFERENCES Payroll,
  Car VARCHAR(256));

INSERT INTO Regist
VALUES (123, 'Charger'),
       (567, 'Civic'),
       (567, 'Pinto');

--------------------------
-- Recap
SELECT count(*) as c
FROM payroll
WHERE job = 'TA';

SELECT count(*) as c, avg(Salary) as a
FROM payroll
WHERE job = 'TA';

SELECT Job, count(*)
FROM Payroll
GROUP BY Job;

SELECT Job, count(*)
FROM Payroll
WHERE Salary > 55000
GROUP BY Job;

SELECT Job, count(*)
FROM Payroll
WHERE Salary > 75000
GROUP BY Job;

--------------------------
-- Empty groups
SELECT P.Name, count(*)
FROM Payroll P, Regist R
WHERE P.UserID = R.UserID
GROUP by P.UserID, P.Name;

SELECT P.Name, count(*)
FROM Payroll P LEFT OUTER JOIN Regist R
  ON P.UserID = R.UserID
GROUP by P.UserID, P.Name;

SELECT P.Name, count(R.Car)
FROM Payroll P LEFT OUTER JOIN Regist R
  ON P.UserID = R.UserID
GROUP by P.UserID, P.Name;

SELECT P1.Job, count(DISTINCT P2.UserID)
FROM Payroll P1 LEFT OUTER JOIN Payroll P2
  ON P1.Job = P2.Job and P2.Salary > 75000
GROUP BY P1.Job;

-- we must use  P2.Salary > 75000!
-- using P1.Salary > 75000 is incorrect
-- to see why, let's add a 3rd job, with one salary <75000 and one >75000:
INSERT INTO Payroll
VALUES (222, 'Joe', 'Lec', 50000),
       (333, 'Jim', 'Lec', 90000);

SELECT *
FROM Payroll;

-- for the group 'Lec' we expect count = 1
-- but using P1.Salary>75000, we get count = 2:
SELECT P1.Job, count(DISTINCT P2.UserID)
FROM Payroll P1 LEFT OUTER JOIN Payroll P2
  ON P1.Job = P2.Job and P1.Salary > 75000
GROUP BY P1.Job;

-- to understand why it is incorrect, look at output to the full query
-- make sure you understand how the LEFT OUT JOIN worked:
SELECT *
FROM Payroll P1 LEFT OUTER JOIN Payroll P2
  ON P1.Job = P2.Job and P1.Salary > 75000;

-- the correct condition is P2.Salary > 75000:
SELECT P1.Job, count(DISTINCT P2.UserID)
FROM Payroll P1 LEFT OUTER JOIN Payroll P2
  ON P1.Job = P2.Job and P2.Salary > 75000
GROUP BY P1.Job;

-- also look at the full query:
SELECT *
FROM Payroll P1 LEFT OUTER JOIN Payroll P2
  ON P1.Job = P2.Job and P2.Salary > 75000;


--------------------------
SELECT P1.Job, P2.Name, P2.Salary
FROM Payroll AS P1, Payroll AS P2
WHERE P1.Job = P2.Job
GROUP BY P1.Job, P2.Name, P2.Salary
HAVING MAX(P1.Salary) = P2.Salary;