Skip to content

Instantly share code, notes, and snippets.

View bartubozkurt's full-sized avatar
🎯
Focusing

Bartu Bozkurt bartubozkurt

🎯
Focusing
32 followers · 98 following
View GitHub Profile
@bartubozkurt
bartubozkurt / multiple_values.sql
Created June 11, 2021 18:26
Finds cities in countries that have a population above 20M
SELECT name FROM city
WHERE country_id IN (
SELECT country_id
FROM country
WHERE population > 2000000;
);
@bartubozkurt
bartubozkurt / correlated.sql
Created June 11, 2021 18:32
Finds cities with a population greater than the average population in the country
SELECT *
FROM city main_city
WHERE population > (
SELECT AVG(population)
FROM city average_city
WHERE average_city.country_id = main_city.country_id
);
@bartubozkurt
bartubozkurt / correlated_2.sql
Created June 11, 2021 18:34
Finds countries that have at least one city:
SELECT name FROM country
WHERE EXISTS (
SELECT *
FROM city
WHERE country_id = country.id
);
@bartubozkurt
bartubozkurt / union.sql
Last active June 11, 2021 19:12
Displays Deutsch cyclists together with Deutsch skaters
SELECT name
FROM cycling
WHERE country = 'DE'
UNION
SELECT name
FROM skating
WHERE country = 'DE';
@bartubozkurt
bartubozkurt / intersect.sql
Created June 11, 2021 19:23
Displays DE cyclists who are also DE skaters at the same time
SELECT name
FROM cycling
WHERE country = 'DE'
INTERSECT
SELECT name
FROM skating
WHERE country = 'DE'
@bartubozkurt
bartubozkurt / except.sql
Created June 11, 2021 19:28
Displays DE cyclists unless they are also DE skaters at the same time
SELECT name
FROM cycling
WHERE country = 'DE'
EXCEPT
SELECT name
FROM skating
WHERE country = 'DE';
@bartubozkurt
bartubozkurt / inner_join.sql
Created June 11, 2021 19:38
The INNER JOIN statement is used to join tables with a common value.
SELECT city.name, country.name
FROM city
INNER JOIN country
ON city.country_id = country.id;
@bartubozkurt
bartubozkurt / left_join.sql
Created June 11, 2021 19:44
It is used to combine all rows in the first selected table and matching rows in the second selected table. If the values in the first table do not match the values in the second table, it gets null.
SELECT city.name, country.name
FROM city
LEFT JOIN country
ON city.country_id = country.id;
@bartubozkurt
bartubozkurt / right_join.sql
Created June 11, 2021 19:47
It is used to merge all rows from the second selected table and the matching rows from the first selected table. Returns null if there is a value in the second table that does not match the first table
SELECT city.name, country.name
FROM city
RIGHT JOIN country
ON city.country_id = city.id;
@bartubozkurt
bartubozkurt / full_outter_join.sql
Created June 11, 2021 19:53
(or explicitly FULL OUTER JOIN) returns all rows from both tables - if there's no matching row in the second table, NULLs are returned.
SELECT city.name, country.name
FROM city
FULL JOIN country
ON city.country_id = country.id;