- You need to build a pivot table WITHOUT using CROSSTAB function. Having two tables products and details you need to select a pivot table of products with counts of details occurrences (possible details values are ['good', 'ok', 'bad'].
SELECT
distinct pro.name,
(SELECT count(*) FROM details det2 WHERE det2.detail = 'good' AND det2.product_id = det.product_id) AS good,
(SELECT count(*) FROM details det2 WHERE det2.detail = 'ok' AND det2.product_id = det.product_id) AS ok,
(SELECT count(*) FROM details det2 WHERE det2.detail = 'bad' AND det2.product_id = det.product_id) AS bad
FROM products pro
LEFT JOIN details det ON pro.id = det.product_id
- For this challenge you need to create a SELECT statement, this SELECT statement will use an IN to check whether a department has had a sale with a price over 90.00 dollars BUT the sql MUST use the WITH statement which will be used to select all columns from sales where the price is greater than 90.00, you must call this sub-query special_sales.
WITH special_sales AS (
SELECT
*
FROM sales
WHERE price > 90.00
)
SELECT
d.id, d.name
FROM departments d
WHERE
d.id IN (SELECT department_id FROM special_sales);
- How can you output a list of all members who have recommended another member? Ensure that there are no duplicates in the list, and that results are ordered by (surname, firstname).
SELECT
distinct recm.firstname, recm.surname
FROM cd.members mem
JOIN cd.members recm ON mem.recommendedby = recm.memid
ORDER BY recm.surname, recm.firstname
-
In baseball, the batting average is a simple and most common way to measure a hitter's performace. Batting average is calculated by taking all the players hits and dividing it by their number of at_bats, and it is usually displayed as a 3 digit decimal (i.e. 0.300).
Given a yankees table with the following schema,
-player_id STRING
-player_name STRING
-primary_position STRING
-games INTEGER
-at_bats INTEGER
-hits INTEGER
return a table with player_name, games, and batting_average.
We want batting_average to be rounded to the nearest thousandth, since that is how baseball fans are used to seeing it. Format it as text and make sure it has 3 digits to the right of the decimal (pad with zeroes if neccesary).
Next, order our resulting table by batting_average, with the highest average in the first row.
Finally, since batting_average is a rate statistic, a small number of at_bats can change the average dramatically. To correct for this, exclude any player who doesn't have at least 100 at bats.
SELECT *
FROM (
SELECT
player_name, games,
round(hits/at_bats::decimal, 3)::text AS batting_average
FROM yankees
WHERE at_bats >= 100
) AS bat_stats
ORDER BY batting_average DESC
- List all employess, their salaries, the department in which they work in, and the average salary per department. Also, create another column stating if they are above average or below.
SELECT
*,
CASE
WHEN salary < dept_avg THEN 'Below Avg'
ELSE 'Above Avg'
END AS salary_status
FROM (
SELECT
s1.last_name || ', ' || s1.last_name AS full_name,
s1.salary::money,
s1.department,
(SELECT avg(s2.salary)::money FROM staff s2
WHERE s1.department = s2.department) AS dept_avg
FROM staff s1
) AS department_stats
- How can you produce a list of all members who have used a tennis court? Include in your output the name of the court, and the name of the member formatted as a single column. Ensure no duplicate data, and order by the member name.
SELECT
distinct mem.firstname || ' ' || mem.surname AS member,
fac.name AS facility
FROM cd.members mem
LEFT JOIN cd.bookings boo ON mem.memid = boo.memid
LEFT JOIN cd.facilities fac ON boo.facid = fac.facid
WHERE fac.name LIKE 'Tennis%'
ORDER BY 1;
- Make a list of all employee last names and their salaries. Include the average by department using both a subquery and a function. Name their relative department.
SELECT
s1.department,
s1.last_name,
s1.salary::money,
avg(s1.salary) OVER(PARTITION BY department)::money,
(SELECT avg(s2.salary)::money FROM staff s2 WHERE s1.department = s2.department)
FROM staff s1
- For this challenge you need to create a RECURSIVE Hierarchical query. You have a table employees of employees, you must order each employee by level. You must use a WITH statement and name it employee_levels after that has been defined you must select from it.
Note: import a csv file to the PostgreSQL database and query the data using pgAdmin
CREATE TABLE employees (
id INT,
first_name VARCHAR(25),
last_name VARCHAR(25),
manager_id INT,
PRIMARY KEY(id)
);
COPY employees
FROM 'D:/Dropbox/Practice_Data/hierarchy_data.csv'
DELIMITER ','
CSV HEADER;
WITH RECURSIVE employee_levels(level, id, first_name, last_name, manager_id, path) AS (
SELECT
1,
e.*,
e.id::text
FROM employees e
WHERE e.manager_id IS NULL
UNION ALL
SELECT
el.level + 1,
e.*,
(el.path || '->' || e.id::text)
FROM employees e, employee_levels el
WHERE e.manager_id = el.id
)
SELECT *
FROM employee_levels;
- Given film_actor and film tables from the DVD Rental sample database find all movies both Sidney Crowe (actor_id = 105) and Salma Nolte (actor_id = 122) cast in together and order the result set alphabetically.
Note: Come up with 3 different queries that come up with same results
SELECT
title, film_id
FROM film fl
WHERE
film_id IN (SELECT film_id FROM film_actor WHERE actor_id = 105) AND
film_id IN (SELECT film_id FROM film_actor WHERE actor_id = 122);
SELECT
fl.title
FROM film fl
JOIN film_actor fa ON fl.film_id = fa.film_id
WHERE fa.actor_id IN (105, 122)
GROUP BY 1
HAVING COUNT(*) > 1;
SELECT
title
FROM (
SELECT
fl.title,
fa.film_id,
fa.actor_id,
ROW_NUMBER() OVER(PARTITION BY fa.film_id) AS actor_num
FROM film fl
JOIN film_actor fa ON fl.film_id = fa.film_id
WHERE fa.actor_id IN (105, 122)
) AS foo
WHERE actor_num = 2;
- How can you produce a list of bookings on the day of 2012-09-14 which will cost the member (or guest) more than $30? Remember that guests have different costs to members (the listed costs are per half-hour 'slot'), and the guest user is always ID 0. Include in your output the name of the facility, the name of the member formatted as a single column, and the cost. Order by descending cost, and do not use any subqueries.
SELECT
mem.firstname || ' ' || mem.surname AS member,
fac.name AS facility,
CASE
WHEN mem.memid = 0 THEN bok.slots*fac.guestcost
ELSE bok.slots*fac.membercost
END AS cost
FROM cd.bookings bok
JOIN cd.facilities fac ON bok.facid = fac.facid
JOIN cd.members mem ON bok.memid = mem.memid
WHERE
((mem.memid = 0 AND bok.slots*guestcost > 30) OR
(mem.memid <> 0 AND bok.slots*fac.membercost > 30))
AND bok.starttime::date = '2012-09-14'
ORDER BY 3 DESC;
- How can you output a list of all members, including the individual who recommended them (if any), using a cross join and without using any joins? Ensure that there are no duplicates in the list, and that each firstname + surname pairing is formatted as a column and ordered.
-- with cross join
SELECT
distinct m1.firstname || ' ' || m1.surname AS member,
CASE
WHEN m1.memid = m2.memid THEN ''
ELSE m2.firstname || ' ' || m2.surname
END AS recommender
FROM cd.members m1, cd.members m2
WHERE m1.recommendedby = m2.memid OR (m1.recommendedby IS NULL AND m1.memid = m2.memid)
ORDER BY 1;
-- without any join
SELECT
distinct firstname || ' ' || surname AS member,
(SELECT firstname || ' ' || surname
FROM cd.members m2
WHERE m1.recommendedby = m2.memid) AS recommender
FROM cd.members m1
ORDER BY 1;
- For this challenge you need to create a simple SELECT statement. Your task is to create a query and do a FULL TEXT SEARCH. You must search the product table on the field name for the word Awesome and return each row with the given word. Your query MUST contain to_tsvector and to_tsquery PostgreSQL functions.
SELECT
id,
name,
price
FROM product
WHERE to_tsvector('english', name) @@ to_tsquery('english', 'Awesome');