%%sql
postgresql:///oldestbusinesses
-- Select the oldest and newest founding years from the businesses table
SELECT MIN(year_founded), MAX(year_founded)
FROM businesses
1 rows affected.
%%sql
-- Get the count of rows in businesses where the founding year was before 1000
SELECT COUNT(year_founded)
FROM businesses
WHERE year_founded <= 1000
* postgresql:///oldestbusinesses
1 rows affected.
%%sql
-- Select all columns from businesses where the founding year was before 1000
-- Arrange the results from oldest to newest
SELECT * FROM businesses
WHERE year_founded <= 1000
ORDER BY year_founded ASC
* postgresql:///oldestbusinesses
6 rows affected.
%%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 b.business, b.year_founded, b.country_code, c.category
FROM businesses as b
JOIN categories as c
ON b.category_code = c.category_code
WHERE b.year_founded < 1000
ORDER BY year_founded ASC
* postgresql:///oldestbusinesses
6 rows affected.
%%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;
* postgresql:///oldestbusinesses
10 rows affected.
%%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;
* postgresql:///oldestbusinesses
6 rows affected.
%%sql
-- Select the business, founding year, category, country, and continent
...
* postgresql:///oldestbusinesses
(psycopg2.errors.SyntaxError) syntax error at or near ".."
LINE 2: ...
^
[SQL: -- Select the business, founding year, category, country, and continent
...]
(Background on this error at: http://sqlalche.me/e/13/f405)
%%sql
-- Count the number of businesses in each continent and category
...
%%sql
-- Repeat that previous query, filtering for results having a count greater than 5
...