11/2 Week 4, Day 1: SQL Queries

[ajlee12]

ElephantSQL is a PostgreSQL hosting platform. The official docs are probably the best place to find answers.

Morning Exercise Review (SQL)

In what year was Dooku born?

We want the data in the birth_year column on the row that represents Dooku;.

SELECT birth_year from people WHERE name = 'Dooku';

-- Ans: 102BBY

• Note that it's ok to replace "public"."people" with just people for the table name.

How tall is Nien Nunb?

The column we want is height.

SELECT height from people WHERE name = 'Nien Nunb';

-- Ans: 160

Who is the tallest character, and how tall are they?

Use ORDER BY and desc syntax.

• So we get the tallest height on top.

Be careful with empty values in the height column.

• Handle it with a WHERE clause.

Use LIMIT to get only the top result.

SELECT name, height FROM people WHERE height IS NOT NULL
ORDER BY height desc LIMIT 1;

-- Ans: Yarael Poof, 264

Alternative solution: use MAX and nested query

SELECT name, height FROM people
WHERE height = (SELECT MAX(height) FROM people);

Weekend Project Review

Putting data onto Excel spreadsheet

You can refer to Andy's code from the demo here: https://gist.github.com/andyweiss1982/a876c0edf4a95eabdeb73f6c14a57a0b

After completing the JS code for fetching data, write a generateWorksheet func that takes in the restaurants array and generates a worksheet.

async function generateWorksheet(restaurants) {
  await Excel.run(async (context) => {
    // Generate a new sheet
    const sheetName = form.outputSheet.value.replace();
    const tableName = `Distance${}`.replace();
    const sheets = context.workbook.worksheets;
    const targetSheet = sheets.add(sheetName);

    await context.sync();

    // Put a table on the sheet
    const table = targetSheet.table.add('A1:G1', true /* table has headers */);
    table.name = tableName;

    await contex.sync();

    // Set table headers
    table.getHeaderRowRange().values = [[
      `${form.baseBrand.value} ID`,
      `${form.baseBrand.value} Address`,
      `${form.baseBrand.value} Rating`,
      `${form.competitorBrand.value} ID`,
      `${form.competitorBrand.value} Address`,
      `${form.competitorBrand.value} Rating`,
      'Miles',
    ]];

    // Add a row for every restaurant
    const tableRows = restaurants.maps((restaurant) => {
      return (
        [
          restaurant.id,
          restaurant.address,
          restaurant.rating,
          restaurant.closestCompetitor.id,
          restaurant.closestCompetitor.address,
          restaurant.closestCompetitor.rating,
          restaurant.distanceToClosestCompetitor,
        ]
      );
    });

    tables.rows.add(null /* add rows to the end of the table */, tableRows);

    await context.sync();
  })
}

Afternoon Code-Along

• Create a new instance on ElephantSQL
• This time we're adding a homeworld column to the people table, and a planets table.
• The values for homeworld in people are IDs of planets that correspond to the IDs in planets table.

Update the name of a planet

Change the one planet with name 'unknown' to 'Mystery Planet':

UPDATE planets SET name = 'Mystery Planet'
WHERE name = 'unknown'
RETURNING *;

What's the climate on Haruun Kal?

SELECT climate FROM planets
WHERE name = 'Haruun Kal';

-- Ans: temperate

How many planets have a hot climate?

SELECT COUNT(*) FROM planets
WHERE climate LIKE '%hot%';

• The %hot% allows us to look into multiple values within a column and find the matching string.

How many planets have tundra?

SELECT COUNT(*) FROM planets
WHERE terrain LIKE '%tundra%';

Names of planets with tundra that also have a frozen climate

SELECT name FROM planets
WHERE terrain LIKE '%tundra%' AND climate LIKE '%frozen%';

-- Ans: Hoth

Find the planet with highest rotational period

SELECT * FROM planets
WHERE rotation_period IS NOT NULL
ORDER BY rotation_period desc
LIMIT 1;

-- Ans: Nal Hutta (87)

Names of people who came from planet 10

SELECT name FROM people
WHERE homeworld = 10;

-- Ans: Boba Fett, Lama Su, Taun We

Each person's name, height, and homeworld name

This first query is almost correct. But notice that we're trying to have two columns with the same name, name.

SELECT people.name, people.height, planets.name FROM people
JOIN planets ON planets._id = people.homeworld

The resulting table has people's names overridden by planets' names.

We can fix the issue by casting planets' names into a new column, homeworld_name.

SELECT people.name, people.height, planets.name AS homeworld_name
FROM people
JOIN planets ON planets._id = people.homeworld

What planet is Luke Skywalker from?

SELECT planets.name AS homeworld_name
FROM people
JOIN planets ON planets._id = people.homeworld
WHERE people.name = 'Luke Skywalker';

-- Ans: Tatooine

How many people come from each planet?

SELECT planets.name, COUNT(*) FROM people
JOIN planets ON planets._id = people.homeworld
GROUP BY planets._id
ORDER BY COUNT(*) desc;

• Note that we don't really need the JOIN statement here if we don't care to see the names of the planets.

  SELECT homeworld, COUNT(*) FROM people
  -- JOIN planets ON planets._id = people.homeworld
  GROUP BY homeworld
  ORDER BY COUNT(*) desc;

Which planets are home to 5 or more people?

SELECT planets.name, COUNT(*)
FROM people
JOIN planets ON planets._id = people.homeworld
GROUP BY planets._id
HAVING COUNT(*) > 5
ORDER BY COUNT(*) desc;

• Note that in this case, we need to put the ORDER BY at the end after filtering out some results.

Which planets are home to residents with mass > 85?

We just need planet names.

SELECT planets.name
FROM people
JOIN planets ON planets._id = people.homeworld
WHERE people.mass > 85
GROUP BY planets._id;

• Alternatively, we can replace the GROUP BY clause with a DISTINCT when selecting.

  SELECT DISTINCT planets.name
  FROM people
  JOIN planets ON planets._id = people.homeworld
  WHERE people.mass > 85

Remove 'Mystery Planet'

We can delete records with DELETE FROM.

DELETE FROM planets
WHERE name = 'Mystery Planet';

If you run the above, you'll get an error saying that there are other records in other tables that are referencing this data.

So first, remove reference to 'Mystery Planet' from people table.

• We achieve this by updating the homeworld column for people where the homeworld _id matches 'Mystery Planet' _id in the planets table.

  UPDATE people SET homeworld = NULL
  WHERE homeworld = (
  SELECT _id FROM planets
  WHERE name = 'Mystery Planet'
  )
  RETURNING *;

  • Now the 5 people with previously 'Mystery Planet' as homeworld have no values in their homeworld column.

Now we can run the DELETE FROM query from above!

Group Exercise Review

Films that George Lucas directed

SELECT title FROM films
WHERE director = 'George Lucas';

Show all film titles and release dates ordered by their episode number

SELECT title, release_date FROM films
ORDER BY episode_id;

• Note that ascending is the default for ORDER BY.

All people_films records with person_name and film_title as the values

SELECT * FROM people_films
JOIN people ON people_films.person_id = people._id
JOIN films ON people_films.film_id = films._id

• This query above gives us all the columns from all 3 tables. But we don't need that much data.
• We just need the people names and films titles.

SELECT
  people.name AS person_name,
  films.title AS film_title
FROM people_films
JOIN people
  ON people_films.person_id = people._id
JOIN films
  ON people_films.film_id = films._id;

What are the names of all the chars in the film A New Hope?

SELECT people.name AS person_name
FROM people_films
JOIN people
  ON people_films._id = people._id
JOIN films
  ON people_films._id = films._id
WHERE films.title = 'A New Hope';

Which planets are homeworld to people who were in The Empire Strikes Back?

SELECT planets.name AS planet_name
FROM people_films
JOIN people
  ON people_films.person_id = people._id
JOIN films
  ON people_films.film_id = films._id
JOIN planets
  ON people.homeworld = planets._id
WHERE films.title = 'The Empire Strikes Back'
-- Use GROUP BY to ensure no duplicates
GROUP BY planet_name;

• Again, we could choose to use DISTINCT instead of GROUP BY.

Add some new records to the films table

INSERT INTO films
(title, release_date, episode_id, director, url)
VALUES
  (
    'The Force Awakens',
    '2018-12-18',
    7,
    'J.J. Abrams',
    'http://swapi.dev/api/films/7/',
  ),
  (
    'The Last Jedi',
    '2017-12-15',
    8,
    'Rian Johnson',
    'http://swapi.dev/api/films/8/',
  ),
  (
    'The Rise of Skywalker',
    '2019-12-20',
    9,
    'J.J. Abrams',
    'http://swapi.dev/api/films/9/',
  )

Create many-to-many relationships by adding rows to the people_films table

One example:

INSERT INTO people_films(person_id, film_id)
VALUES
(
  (SELECT _id FROM people WHERE name = 'Luke Skywalker'),
  (SELECT _id FROM films WHERE episode_id = 7)
);