%%sql
postgresql:///oldestbusinesses
-- Select the oldest and newest founding years from the businesses table
SELECT MIN(year_founded), MAX(year_founded)
FROM businesses;
%%sql
-- Get the count of rows in businesses where the founding year was before 1000
SELECT COUNT(*)
FROM businesses
WHERE businesses.year_founded < 1000;
%%sql
-- Select all columns from businesses where the founding year was before 1000
-- Arrange the results from oldest to newest
SELECT *
FROM businesses
WHERE businesses.year_founded < 1000
ORDER BY year_founded;
%%sql
-- Select business name, founding year, and country code from businesses; and category from categories
-- where the founding year was before 1000, arranged from oldest to newest
SELECT business, year_founded, country_code, category
FROM businesses
JOIN categories
USING(category_code)
WHERE year_founded <1000
ORDER BY year_founded;
%%sql
-- Select the category and count of category (as "n")
-- arranged by descending count, limited to 10 most common categories
SELECT category, COUNT(category) AS n
FROM categories
JOIN businesses
USING(category_code)
GROUP BY category
ORDER BY n DESC
LIMIT 10;
%%sql
-- Select the oldest founding year (as "oldest") from businesses,
-- and continent from countries
-- for each continent, ordered from oldest to newest
SELECT MIN(year_founded) AS oldest, continent
FROM businesses
JOIN countries
USING(country_code)
GROUP BY continent
ORDER BY oldest;
%%sql
-- Select the business, founding year, category, country, and continent
SELECT business, year_founded, category, country, continent
FROM businesses
JOIN categories
USING(category_code)
JOIN countries
USING(country_code);
%%sql
-- Count the number of businesses in each continent and category
SELECT continent, category, COUNT(businesses) AS n
FROM businesses
JOIN categories
USING(category_code)
JOIN countries
USING(country_code)
GROUP BY continent, category;
%%sql
-- Repeat that previous query, filtering for results having a count greater than 5
SELECT continent, category, COUNT(*) AS n
FROM businesses
INNER JOIN categories
USING(category_code)
INNER JOIN countries
USING(country_code)
GROUP BY continent, category
HAVING COUNT(*) > 5
ORDER BY n DESC;