How many different origin airports exist? What are their IATA codes?
SELECT DISTINCT origin FROM datasets.us_flights
SELECT COUNT(DISTINCT origin) FROM datasets.us_flights
Give me a list of 5 origin, destination airport pairs which are furthest apart from each other (Hint: Use the distance column)
SELECT DISTINCT origin, dest, distance FROM datasets.us_flights ORDER BY distance DESC LIMIT 5
Select all US flights which had no delay. (Hint: Use the arr_delay column)
SELECT * FROM datasets.us_flights WHERE arr_delay = 0.0 OR arr_delay IS NULL
What is the average distance an airplane travels from each origin airport.
SELECT origin, AVG(distance) AS avg_distance FROM datasets.us_flights GROUP BY origin
How many flights did American Airlines (AA) cancel? (Hint: cancelled column has 1 for canceled flights)
SELECT count(*) FROM datasets.us_flights WHERE cancelled = 1 AND unique_carrier = 'AA'
Which companies are present in the financial sector in Eurasia.
Solution: Eurasia is same as Europe or Asia
SELECT company FROM datasets.forbes_global_2010_2014 WHERE (continent = 'Asia' OR continent = 'Europe') AND (sector = 'Financials')
What is the profit to sales ratio (profit / sales) for Royal Dutch Shell?
Solution: We can write queries which return only one row.
SELECT company, profits / sales AS profit_to_sales_ratio FROM datasets.forbes_global_2010_2014 WHERE company = 'Royal Dutch Shell'
What are the 3 most profitable companies in the entire world? (Hint: order by profit)
Solution: When we order by DESC we go from maximal profit to minimal profit
SELECT * FROM datasets.forbes_global_2010_2014 ORDER BY profits DESC LIMIT 3
Find the biggest market value for each sector.
SELECT sector, MAX(marketvalue) FROM datasets.forbes_global_2010_2014 GROUP BY sector
Which industry has the lowest sales while still making an average profit higher than 0. (Hint: Use a HAVING clause)
SELECT industry, -- These two columns are here for explanation purposes so you can see the numbers -- They are not required for the output. MIN(sales) AS min_sales, AVG(profits) AS avg_profit FROM datasets.forbes_global_2010_2014 GROUP BY industry -- These two lines matter most for this query HAVING AVG(profits) > 0 ORDER BY MIN(sales) ASC
Show me the breakdown of languages spoken? (Hint: use count)
SELECT language, COUNT(*) FROM datasets.playbook_users GROUP BY language ORDER BY count DESC
Find a list of users who speak English, French, German or Spanish (Hint: Use IN)
Solution: Using IN we can remove a lot of ORs
SELECT * FROM datasets.playbook_users WHERE language IN ('english', 'german', 'french', 'spanish')
What are the companies that have at least 10 Chinese speaking users?
Keep in mind that all filters which are based on aggregations (like COUNT(*) here) must go in the HAVING clause.
SELECT company_id FROM datasets.playbook_users WHERE language='chinese' GROUP BY company_id HAVING COUNT(*) >= 10
In how many movies did Abigail Breslin star?
SELECT count(*) FROM datasets.oscar_nominees WHERE nominee = 'Abigail Breslin'
Show me the Oscar winners between 2001 and 2009.
SELECT * FROM datasets.oscar_nominees WHERE winner = true AND year BETWEEN 2001 AND 2009