PUBS Database SQL Exercise Solutions
Question a
CREATE TABLE EMPLOYEE (
emp_id INT PRIMARY KEY,
fname VARCHAR(50),
lname VARCHAR(50),
stor_id VARCHAR(10),
job_id INT,
pub_id VARCHAR(10),
hire_date DATE,
job_lvl INT,
city VARCHAR(50),
state VARCHAR(50),
zip VARCHAR(10),
country VARCHAR(50),
managerID INT,
supervisorID INT
);
Question b
UPDATE SALE
SET discount_id = (
SELECT discount_id FROM DISCOUNT WHERE discount = 0.25
)
WHERE title_id = (
SELECT title_id FROM TITLES WHERE titlename = 'Flight runner'
)
AND saledate > '2019-03-31';
Question c
SELECT
[Link] || ' ' || [Link] AS Employee_Name,
CASE
WHEN e1.pub_id IS NOT NULL THEN
(SELECT fname || ' ' || lname FROM EMPLOYEE e2 WHERE e2.emp_id =
[Link])
ELSE
(SELECT fname || ' ' || lname FROM EMPLOYEE e2 WHERE e2.emp_id =
[Link])
END AS Manager_or_Supervisor
FROM EMPLOYEE e1;
Question d
SELECT COUNT(*) AS Native_Employees
FROM EMPLOYEE
WHERE country IN (
SELECT country FROM COUNTRY WHERE [Link] = [Link]
);
Question e
SELECT [Link], COUNT(e.emp_id) AS Employee_Count
FROM EMPLOYEE e
JOIN COUNTRY c ON [Link] = [Link]
WHERE e.pub_id IS NOT NULL
GROUP BY [Link];
Question f
SELECT [Link], SUM([Link]) AS Total_Sold
FROM SALE s
JOIN TITLES t ON s.title_id = t.title_id
GROUP BY [Link]
HAVING SUM([Link]) > 20000;
Question g
SELECT DISTINCT a.au_fname || ' ' || a.au_lname AS Author_Name
FROM AUTHOR a
JOIN TITLEAUTHOR ta ON a.au_id = ta.au_id
JOIN TITLES t ON ta.title_id = t.title_id
WHERE EXTRACT(YEAR FROM t.pub_date) = 2019
AND EXISTS (
SELECT 1 FROM TITLES t2
JOIN TITLEAUTHOR ta2 ON t2.title_id = ta2.title_id
WHERE ta2.au_id = a.au_id
AND EXTRACT(YEAR FROM t2.pub_date) <= 1994
);
Question h
SELECT s.stor_name, SUM([Link]) AS Total_Sold
FROM STORE s
JOIN SALE sl ON s.stor_id = sl.stor_id
WHERE [Link] = 'India' AND EXTRACT(YEAR FROM [Link]) = 2019
GROUP BY s.stor_name;
Question i
SELECT a.au_fname || ' ' || a.au_lname AS Author_Name, MAX([Link] * [Link] *
[Link]) AS Revenue
FROM AUTHOR a
JOIN TITLEAUTHOR ta ON a.au_id = ta.au_id
JOIN TITLES t ON ta.title_id = t.title_id
JOIN SALE s ON t.title_id = s.title_id
WHERE EXTRACT(YEAR FROM [Link]) = 2019
GROUP BY a.au_fname, a.au_lname
ORDER BY Revenue DESC
LIMIT 1;
Question j
SELECT DISTINCT p.pub_name
FROM PUBLISHER p
JOIN TITLES t ON p.pub_id = t.pub_id
WHERE EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM MIN(t.pub_date)) > 30
GROUP BY p.pub_id, p.pub_name;
Question k
SELECT [Link], COUNT(DISTINCT a.au_id) AS Total_Authors
FROM AUTHOR a
JOIN TITLEAUTHOR ta ON a.au_id = ta.au_id
JOIN TITLES t ON ta.title_id = t.title_id
JOIN PUBLISHER p ON t.pub_id = p.pub_id
JOIN COUNTRY c ON [Link] = [Link]
GROUP BY [Link]
HAVING COUNT(DISTINCT ta.title_id) >= 3;
Question l
SELECT j.job_id, COUNT(e.emp_id) AS Total_Employees
FROM JOB j
LEFT JOIN EMPLOYEE e ON j.job_id = e.job_id
GROUP BY j.job_id;
Question m
SELECT s.discount_id, COUNT(DISTINCT s.order_num) AS Total_Orders
FROM SALE s
GROUP BY s.discount_id;
Question n
SELECT SUM([Link] * [Link]) AS Total_Sales
FROM SALE s
JOIN TITLES t ON s.title_id = t.title_id
JOIN DISCOUNT d ON s.discount_id = d.discount_id
WHERE [Link] > 0.05;
Question o
-- Type-wise total, average, and maximum price
SELECT
[Link],
SUM([Link]) AS Total_Price,
AVG([Link]) AS Average_Price,
MAX([Link]) AS Maximum_Price
FROM TITLES t
GROUP BY [Link];
-- Total number of orders for 'Business' type books
SELECT COUNT(s.order_num) AS Total_Business_Orders
FROM SALE s
JOIN TITLES t ON s.title_id = t.title_id
WHERE [Link] = 'business';