Skip to content

Instantly share code, notes, and snippets.

View bartubozkurt's full-sized avatar
🎯
Focusing

Bartu Bozkurt bartubozkurt

🎯
Focusing
32 followers · 97 following
View GitHub Profile
@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 / 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 / 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 / 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 / single_value.sql
Created June 11, 2021 18:23
Finds cities with the same rating as Paris
SELECT name
FROM city
WHERE rating = (SELECT rating
FROM city
WHERE name = 'Paris');
@bartubozkurt
bartubozkurt / avg.sql
Created June 11, 2021 18:19
Find out the average rating for cities in respective countries if the average is above 3.0
SELECT country_id , AVG(rating)
FROM city
GROUP BY country_id
HAVING AVG(rating) > 3.0;
@bartubozkurt
bartubozkurt / sum.sql
Created June 11, 2021 18:11
Find out the total population of cities in respective countries
SELECT country_id, SUM(population) FROM country;
@bartubozkurt
bartubozkurt / min_max.sql
Created June 11, 2021 18:09
Find out the smallest and the greatest counrt populations
SELECT MIN(population), MAX(population) FROM country;
@bartubozkurt
bartubozkurt / distinct.sql
Created June 11, 2021 18:04
Find out the number of distinctive country values
SELECT COUNT(DISTINCT country_id)
FROM city;
@bartubozkurt
bartubozkurt / is_not_null.sql
Created June 11, 2021 18:02
Find out the number of cities with non-null ratings
SELECT COUNT(rating)
FROM city;