SQL Code Snippers

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';