Joins
SELECT *
FROM joins1;
SELECT *
FROM joins2;
SELECT j1.id
FROM joins1 j1
INNER JOIN joins2 j2
ON j1.id = j2.id;
SELECT j1.id
FROM joins1 j1
INTERSECT
SELECT j2.id
FROM joins2 j2;
SELECT j1.id AS j1,
j2.id AS j2
FROM joins1 j1
INNER JOIN joins2 j2
ON j1.id = j2.id;
SELECT j1.id AS j1,
j2.id AS j2
FROM joins1 j1
LEFT JOIN joins2 j2
ON j1.id = j2.id;
SELECT j1.id AS j1
j2.id AS j2
FROM joins1 j1
RIGHT JOIN joins2 j2
ON j1.id = j2.id;
SELECT j1.id AS j1,
j2.id AS j2
FROM joins1 j1 FULL
JOIN joins2 j2
ON j1.id = j2.id;
SELECT e.full_name AS employee, m.full_name AS manager
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id;
SELECT j1.id AS j1,
j2.id AS j2
FROM joins1 j1 NATURAL
JOIN joins2 j2;
SELECT j1.id
FROM joins1 j1
INTERSECT
SELECT j2.id
FROM joins2 j2;
SELECT j2.id
FROM joins2 j2
INTERSECT
SELECT j1.id
FROM joins1 j1;
SELECT id
FROM joins1
EXCEPT DISTINCT
SELECT id
FROM joins2;
SELECT id
FROM joins1
EXCEPT DISTINCT
SELECT id
FROM joins1;
SELECT id FROM joins1
UNION
SELECT id FROM joins2;
SELECT id FROM joins1
UNION ALL
SELECT id FROM joins2;
SELECT j1.id, j2.id
FROM joins1 j1
CROSS JOIN joins2 j2;
SELECT *
FROM joins1 j1
CROSS JOIN (
SELECT *
FROM joins2 j2
WHERE j2.id = 3 -- No reference to outer query
) subquery;
SELECT *
FROM joins1 j1
CROSS JOIN LATERAL (
SELECT *
FROM joins2 j2
WHERE j1.id = j2.id -- j1.id —reference is possible because of lateral
) subquery;
SELECT
e.employee_id as emp_id, h.hire_date,
extract(
YEAR FROM AGE(NOW(), h.hire_date)
) AS employed_in_years,
DATE(
h.hire_date + (
EXTRACT(YEAR FROM AGE(NOW(), h.hire_date)) + 1
) * INTERVAL '1 year'
) AS next_review,
DATE(
h.hire_date + (
EXTRACT(YEAR FROM AGE(NOW(), h.hire_date)) + 1
) * INTERVAL '1 year'
) - DATE(NOW()) AS days_to_next_review
FROM employees e
JOIN hire_dates h
ON e.employee_id = h.employee_id
ORDER BY days_to_next_review;
SELECT
e.employee_id AS employee,
h.hire_date AS hired,
employed_in_years AS years_employed,
next_review_date,
days_to_next_review
FROM employees e
JOIN hire_dates h
ON e.employee_id = h.employee_id
CROSS JOIN LATERAL (
SELECT
CAST(
EXTRACT(YEAR FROM AGE(NOW(), h.hire_date)) AS int
) AS employed_in_years
) ex
LEFT JOIN LATERAL (
SELECT next_review_date(h.hire_date)
) ey ON TRUE
LEFT JOIN LATERAL (
SELECT
next_review_date - DATE(NOW()
) AS days_to_next_review
) ez ON TRUE
ORDER BY days_to_next_review;
SELECT date, user_id, 1 AS captured_email
FROM traffic_log e1
WHERE user_id = e1.user_id
AND event LIKE 'email';
SELECT *
FROM traffic_log;
SELECT event, COUNT(*) AS visitors
FROM traffic_log
GROUP BY event;
SELECT date, user_id, 1 AS captured_email
FROM traffic_log
WHERE event LIKE 'email';
SELECT date, user_id, 1 AS demo
FROM traffic_log
WHERE event LIKE 'demo';
SELECT date, user_id, 1 AS offer
FROM traffic_log
WHERE event LIKE 'offer';
SELECT date, user_id, 1 AS purchased
FROM traffic_log
WHERE event LIKE 'purchase';
SELECT COUNT(*) AS visitors
FROM traffic_log;
SELECT *
FROM traffic_log
WHERE event LIKE ‘email’
LIMIT 10;
SELECT *
FROM traffic_log
WHERE event LIKE 'demo'
LIMIT 10;
SELECT *
FROM traffic_log
WHERE event LIKE 'offer'
LIMIT 10;
SELECT *
FROM traffic_log
WHERE event LIKE 'purchase'
LIMIT 10;
SELECT COUNT(*) AS visitors;
SELECT
SUM(visitors) AS homepage,
CONCAT(SUM(e2.captured_email),' | ',
TO_CHAR(SUM(e2.captured_email)/SUM(visitors) * 100, '99D9%')) AS email,
CONCAT(SUM(e3.watched_demo), ' | ',
TO_CHAR(SUM(e3.watched_demo)/SUM(visitors) * 100, '99D9%')) AS demo,
CONCAT(SUM(e4.received_offer), ' | ',
TO_CHAR(SUM(e4.received_offer)/SUM(visitors) * 100, '99D9%')) AS offer,
CONCAT(SUM(e5.purchased), ' | ',
TO_CHAR(SUM(e5.purchased)/SUM(visitors) * 100, '99D9%')) AS purchase
FROM (
SELECT date, user_id, 1 AS home_page
FROM traffic_log
WHERE event LIKE 'homepage'
) e1
LEFT JOIN LATERAL (
SELECT date, user_id, 1 AS captured_email
FROM traffic_log
WHERE user_id = e1.user_id
AND event LIKE 'email'
) e2 ON TRUE
LEFT JOIN LATERAL (
SELECT date, user_id, 1 AS watched_demo
FROM traffic_log
WHERE user_id = e2.user_id
AND event LIKE 'demo'
) e3 ON TRUE
LEFT JOIN LATERAL (
SELECT date, user_id, 1 AS received_offer
FROM traffic_log
WHERE user_id = e3.user_id
AND event LIKE 'offer'
) e4 ON TRUE
LEFT JOIN LATERAL (
SELECT date, user_id, 1 AS purchased
FROM traffic_log
WHERE user_id = e4.user_id
AND event LIKE 'purchase'
) e5 ON TRUE
JOIN LATERAL (
SELECT
COUNT(*) AS visitors
) e6 ON TRUE;
DROP TABLE IF EXISTS reqs;
CREATE TABLE reqs (req int);
INSERT INTO reqs
VALUES
(9),
(15),
(42);
DROP TABLE IF EXISTS applicants;
CREATE TABLE applicants (employee_id int, skill int);
INSERT INTO applicants
VALUES
(20,2),
(20,4),
(20,6),
(17,3),
(17,6),
(17,9),
(15,42),
(15,15),
(15,9),
(15,11),
(16,2),
(16,3),
(16,9);
SELECT *
FROM employees
WHERE title LIKE 'Developer'
OR title LIKE 'Engineer';
SELECT *
FROM employees;
SELECT *
FROM applicants;
SELECT *
FROM reqs;
SELECT e.*,
r.req
FROM employees e
JOIN applicants a
ON e.employee_id = a.employee_id
JOIN reqs r
ON a.skill = r.req;
SELECT e.*, q.matched
FROM employees e
JOIN (
SELECT a.employee_id, COUNT(r.req) AS matched
FROM applicants a, reqs r
WHERE MOD(a.skill, r.req) = 0
GROUP BY a.employee_id
HAVING COUNT(*) = (SELECT COUNT(*) FROM reqs)
) q
ON q.employee_id = e.employee_id;
SELECT e.*,
r.req
FROM employees e
JOIN applicants a
ON e.employee_id = a.employee_id
JOIN reqs r
ON a.skill = r.req;
SELECT s.employee_id, e.full_name,
COUNT(r.req)
FROM applicants s
JOIN employees e
ON s.employee_id = e.employee_id,
reqs r
WHERE MOD(s.skill, r.req) = 0
GROUP BY s.employee_id, e.full_name
HAVING COUNT(*) = ( SELECT COUNT(*)
FROM reqs);
SELECT s.employee_id, e.full_name,
COUNT(r.req)
FROM applicants s
JOIN employees e
ON s.employee_id = e.employee_id,
reqs r
WHERE MOD(s.skill, r.req) = 0
GROUP BY s.employee_id, e.full_name
HAVING COUNT(*) = ( SELECT COUNT(*)
FROM reqs);
SELECT q.employee_id, e.full_name
FROM (
SELECT a.employee_id
FROM applicants a
JOIN reqs r
ON a.skill = r.req
GROUP BY employee_id
HAVING COUNT(*) = (SELECT COUNT(*) FROM reqs)
) q
JOIN employees e
ON q.employee_id = e.employee_id;
SELECT a.employee_id, a.skill, r.req
FROM applicants a
CROSS JOIN reqs r;
SELECT a.employee_id, a.skill, r.req,
CASE
WHEN a.skill = r.req THEN 1
ELSE 0
END AS match
FROM applicants a
CROSS JOIN reqs r;
SELECT a.employee_id, a.skill,
SUM(
CASE
WHEN a.skill = r.req THEN 1
ELSE 0
END) AS match,
COUNT(*) AS requirements
FROM applicants a
CROSS JOIN reqs r
GROUP BY a.employee_id, a.skill;
SELECT q.employee_id, e.full_name, match, requirements,
SUM(match) AS match_count,
MIN(requirements) AS req_count
FROM (
SELECT a.employee_id, a.skill,
SUM(
CASE
WHEN a.skill = r.req THEN 1
ELSE 0
END) AS match,
COUNT(*) AS requirements
FROM applicants a
CROSS JOIN reqs r
GROUP BY a.employee_id, a.skill
) q
JOIN employees e
ON e.employee_id = q.employee_id
GROUP BY q.employee_id, e.full_name, requirements, match
HAVING SUM(match) = MIN(requirements);
SELECT DISTINCT
m.mgr,
dp.dept_id,
m.project_id,
dp.project_id
FROM projectmgr AS m
CROSS JOIN department_projects AS dp
GROUP BY m.mgr, dp.dept_id, dp.project_id, m.project_id;
SELECT DISTINCT
m.mgr,
dp.dept_id,
dp.project_id AS portf,
MAX (CASE
WHEN m.project_id = dp.project_id
THEN 2
ELSE 1
END
) AS managing
FROM projectmgr AS m
CROSS JOIN department_projects AS dp
GROUP BY m.mgr, dp.dept_id, dp.project_id, dp.project_id;
SELECT DISTINCT
m.mgr,
dp.dept_id,
dp.project_id AS dept_portfolio,
m.project_id AS mgr_portfolio,
CASE
WHEN m.project_id = dp.project_id THEN 2
ELSE 1
END) AS managing
FROM projectmgr AS m
CROSS JOIN department_projects AS dp
GROUP BY m.mgr, dp.dept_id, dp.project_id, m.project_id;
SELECT DISTINCT
m.mgr,
dp.dept_id,
MAX (CASE
WHEN m.project_id = dp.project_id THEN 2
ELSE 1
END) AS managing
FROM projectmgr AS m
CROSS JOIN department_projects AS dp
GROUP BY m.mgr, dp.dept_id, dp.project_id;
SELECT DISTINCT
m.mgr,
dp.dept_id,
m.project_id AS m_project, -- extended for clarity
dp.project_id AS d_project, -- extended for clarity
MAX (CASE
WHEN m.project_id = dp.project_id THEN 2
ELSE 1
END) AS managing
FROM projectmgr AS m
CROSS JOIN department_projects AS dp
GROUP BY m.mgr, dp.dept_id, dp.project_id,m.project_id; -- extended for clarity
SELECT t.dept_id, t.mgr,
CASE SUM(t.managing)
WHEN 1 THEN 'None'
WHEN 2 THEN 'All'
WHEN 3 THEN 'Some'
ELSE NULL
END AS "dept portfolio"
FROM (
SELECT DISTINCT
m.mgr,
dp.dept_id,
MAX (CASE
WHEN m.project_id = dp.project_id THEN 2
ELSE 1
END) AS managing
FROM projectmgr AS m
CROSS JOIN department_projects AS dp
GROUP BY m.mgr, dp.dept_id, dp.project_id
) t
GROUP BY t.dept_id, t.mgr
ORDER BY t.dept_id, t.mgr;