SQL – PostgreSQL
Aggregation – Basic
SELECT DISTINCT naam FROM steden WHERE LEFT(stadsnaam, 1) IN ('A','B','C','D','E') ORDER BY stadsnaam
SELECT COUNT(*) FROM batsman_scored WHERE batsman_scored.runs_scored = 6;
SELECT COUNT(DISTINCT(nationality)) FROM athletes ;
SELECT item, CASE WHEN energy > 300 THEN 'high' WHEN energy > 150 THEN 'medium' ELSE 'low' END AS calorie_rating FROM food ORDER BY item
SELECT item, energy, (SELECT ROUND(AVG(energy), 2) FROM food) AS avg_energy, energy - (SELECT ROUND(AVG(energy), 2) FROM food) AS difference FROM food ORDER BY food;
Aggregation with GROUP BY
SELECT teamstats.speler_id, sum(goals) FROM teamstats GROUP BY teamstats.speler_id ORDER BY SUM(goals) DESC LIMIT 5
SELECT team_name, SUM(CASE WHEN runs_scored = 4 THEN 1 ELSE 0 END) AS fours, SUM(CASE WHEN runs_scored = 6 THEN 1 ELSE 0 END) AS sixes FROM runs_2016 GROUP BY team_name ORDER BY team_name
Aggregation with HAVING
SELECT s.land, SUM(gewicht) FROM spelers as s GROUP BY s.land HAVING SUM(gewicht) > 300;
SELECT speler_naam, COUNT(match_id) FROM basketball_teams GROUP BY speler_naam HAVING COUNT(match_id) > 20 ORDER BY speler_naam
Aggregation with PARTITION BY
SELECT soort, type, prijs, ROUND(AVG(prijs) OVER(PARTITION BY type), 2) AS type_gemiddelde FROM bier WHERE prijs IS NOT NULL ORDER BY prijs DESC
Subquery
SELECT last_name FROM employees WHERE employee_id IN ( SELECT manager_id from employees) ORDER BY last_name;
SELECT stijl, prijs FROM bier_soort WHERE id IN ( SELECT bier_id FROM pairing) ORDER BY prijs
SELECT match_id, team_1, team_2, match_date, match_winner FROM match WHERE match_id IN ( SELECT match_i from goals > 2) ORDER BY match_id ;
SELECT cat_id, cat_weight FROM cats WHERE cat_weight > ( SELECT AVG(cat_weight) FROM cats) ORDER BY cat_id LIMIT 10;
SELECT beer_id, beer_name FROM beers WHERE beer_price < ( SELECT AVG(beer_price) FROM beers) ORDER BY beer_id LIMIT 10;
Creating Subqueries
SELECT item_id, cost_price FROM ( SELECT * FROM items WHERE cost_price > 20 ) AS subquery ORDER BY item_id LIMIT 10;
Temporary Table
WITH subquery AS ( SELECT * FROM prices WHERE cost_price > 20 ) SELECT item_id, cost_price FROM subquery WHERE item_category = 'Food' ORDER BY item_id LIMIT 10;
JOINs
SELECT movie.movie_name, country.name FROM movies LEFT JOIN country ON movies.country_id = country.id ORDER BY movies.movie_name LIMIT 10;
SELECT season.season_year player.player_name FROM season INNER JOIN player ON season.orange_cap = player.player_id ORDER BY season.season_year
Specific Data Types
SELECT CONCAT('Hello','World') AS new_word;
SELECT * FROM favorite WHERE LOWER(title) = 'you';