SQL Code Snippers – Stefan Jaspers

SQL – PostgreSQL

Aggregation – Basic

SELECT
  DISTINCT naam
FROM 
  steden
WHERE
  LEFT(stadsnaam, 1) IN ('A','B','C','D','E')
ORDER BY
  stadsnaam

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(*)

FROM

batsman_scored

WHERE

batsman_scored.runs_scored = 6;

SELECT 
  COUNT(DISTINCT(nationality))
FROM
  athletes ;

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,

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;

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

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

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

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

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

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

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

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

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

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;

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;

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;

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

movie.movie_name,

country.name

FROM

movies

LEFT JOIN

country

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

SELECT

season.season_year

player.player_name

FROM

season

INNER JOIN

player

season.orange_cap = player.player_id

ORDER BY

season.season_year

Specific Data Types

SELECT
  CONCAT('Hello','World') AS new_word;

1 2	SELECT CONCAT('Hello','World') AS new_word;

SELECT
  *
FROM
  favorite
WHERE LOWER(title) = 'you';

SELECT

FROM

favorite

WHERE LOWER(title) = 'you';