11/03 Week 4, Day 2: More SQL

[ajlee12]

Morning Exercise Review

Creating Tables

Courses Table

CREATE TABLE IF NOT EXISTS courses(
  _id INTEGER NOT NULL,
  name VARCHAR NOT NULL
);

Students Table

A column is by default optional if you don't specify it to be NOT NULL.

• We're making dob optional for the students table.

  CREATE TABLE IF NOT EXISTS students(
  _id INTEGER NOT NULL,
  name VARCHAR NOT NULL,
  dob DATE
  );

Enrollments Table

CREATE TABLE IF NOT EXISTS enrollments(
  _id INTEGER NOT NULL,
  student_id INTEGER NOT NULL,
  course_id INTEGER NOT NULL
);

Add constraints to enrollments:

1. The type of constraint is 'foreign key' that references students table -- specifically the _id column on students table.
2. Another foreign key constraint that references courses table -- specifically the _id column.

The ADD CONSTRAINT command is used to create a constraint after a table is already created.

• Read more about constraints in SQL: https://www.w3schools.com/sql/sql_constraints.asp
• Read more about adding constraints: https://www.w3schools.com/sql/sql_ref_add_constraint.asp

ALTER TABLE enrollments
  ADD CONSTRAINT enrollments_fk0 FOREIGN KEY (student_id) REFERENCES students(_id);

ALTER TABLE enrollments
  ADD CONSTRAINT enrollments_fk1 FOREIGN KEY (course_id) REFERENCES courses(_id);

---

Sequential IDs

When inserting a new row, you either let Postgres create the sequential ID for you or insert your own unique ID.

You can specify a column type to be SERIAL.

• Read more about that here: https://www.postgresqltutorial.com/postgresql-serial/

• Note that this does NOT make the column the the primary key column.

Example of inserting a row to courses:

INSERT INTO courses
('Intro to JavaScript');

Example of inserting a row to students:

INSERT INTO students
('Sally Smith', '1990-11-21');

Example of inserting a row to enrollments with nested queries:

INSERT INTO enrollments()
VALUES(
  (SELECT student_id FROM students WHERE name = 'Sally Smith';),
  (SELECT course_id FROM courses WHERE name = 'Intro to JavaScript';)
);

Get all students who are enrolled in 'Intro to JavaScript':

SELECT students.name AS student_name
FROM enrollments
JOIN students ON enrollments.student_id = students._id
JOIN courses ON enrollments.course_id = courses._id
WHERE courses.name = 'Intro to JavaScript';

Code-Along: Using Postgres in JavaScript

We're using a library called pg in order to interact with our Postgres database with JavaScript code.

Let's write a function that sends a query to the DB:

• Querying the DB is an asynchronous operation.

  const exampleQuery = async () => {
  try {
  const queryText = `
    SELECT * FROM people LIMIT 10;
  `;
  
  const result = await db.query(queryText);
  
  return result.rows;
  }
  catch (error) {
  console.error(error);
  }
  };

• Note that the data we want are nested in the result.rows array.

  • result itself is a big object containing lots of metadata that we don't need at the moment.

---

Try-Catch Syntax

We attempt the operation in the try block.

If anything goes wrong, we catch and process the error in the catch block.

---

Write a function that takes in a planetName and finds that planet's residents.

• We'll need to inject a parameter value into the query string.

  const findPlanetResidents = async (planetName) => {
  try {
  const queryText = `
    SELECT 
      people.name AS person_name,
      people.mass,
      people.height,
      people.birth_year,
      people.url,
      people.homeworld
    FROM people
    JOIN planets
      ON people.homeworld = planets._id
    WHERE planets.name = ${planetName};
  `;
  
  const result = await db.query(queryText);
  
  // This `if` conditional alerts us when there's no data from the query.
  if (!results.rows.length) throw new Error('Expected results from query; received none.');
  
  return results.rows;
  }
  catch (error) {
  console.error(error);
  }
  };

If you see an error that says something like column "some_column" does not exist, it may be because you didn't wrap the injected value within single quotes.

`WHERE planets.name = '${planetName}';`

Write a function findXWhere

This is a highly reusable function that takes in tableName and whereClause and returns all the records from tableName that match the whereClause.

const findXWhere = (tableName, whereClause) => {
  try {
    const queryText = `
      SELECT *
      FROM ${tableName}
      ${whereClause}
    `;

    const result = await db.query(queryText);

    if (!results.rows.length) throw new Error('Expected results from query; received none.');

    return result.rows;
  }
  catch (error) {
    console.error(error);
  }
};

Example usage of the above function:

// Find all planets with arid climate
findXWhere('planets', `WHERE climate LIKE '%arid%'`);

// Find all info on episode 4 film 
findXWhere('films', `WHERE episode_id = 4`);

Capture the Bandit!

• Find the only resident on Dathomir:

  try {
  const allResidentsOfDathomir = await findPlanetsResidents('Dathomir');
  
  const residentsOfDathomirName = allResidentsOfDathomir[0].person_name;
  const residentsOfDathomirMass = allResidentsOfDathomir[0].mass;
  }

• Find the person who matches the above target's mass and with the height of 180:

  try {
  /* Previous code... */ 
  
  const resultsOfFindPersonWithMassAndHeight = await findXWhere('people', `WHERE mass = ${residentsOfDathomirMass} AND height = 180`);
  
  const personWithMassAndHeightName = resultsOfFindPersonWithMassAndHeight[0].name;
  const personWithMassAndHeightId = resultsOfFindPersonWithMassAndHeight[0]._id;
  }

• Find the ships piloted by the person we found above:

  try {
  /* Previous code... */ 
  
  const allShipsPilotedByHanSolo = await findXWhere('people_starships', `WHERE person_id = ${personWithMassAndHeightId}`);
  
  const allIdsForShipsPilotedByHanSolo = allShipsPilotedByHanSolo.map((shipPiloted) => shipPiloted.starship_id);
  }

• Find the only other person who piloted the same ships:

try {
  /* Previous code... */ 

  const copilotQuery = `
    SELECT
      people.name AS person_name,
      people.homeworld AS homeworld._id
    FROM people_starships
    JOIN people
    ON people_starships.person_id = people._id
    WHERE starship_id IN (5, 12)
    ORDER BY person_id 
  `;

  const copilotResults = await db.query(copilotQuery);

  if (!copilotResults.rows.length) throw new Error('Expected results from query; received none.');

  // Hardcode the results:
  const twoTimeCopilotName = 'Chewbacca';
  const twoTimeCopilotHomeworldId = 14;
}

• Find the planet of the person we found above:
  • Also find the only other resident on that planet.

try {
  const getPlanetNameQuery = `
    SELECT
      name
    FROM planets
    WHERE _id = ${twoTimeCopilotHomeworldId};
  `;

  // First find the planet name
  const planetNameById = await db.query(getPlanetNameQuery);
  const banditPlanetName = planetNameById[0].name;

  // Second, find all residents of that planet
  const allResidentsOfPlanet14 = await findPlanetResidents(banditPlanetName);
  const banditName = allResidentsOfPlanet14.filter((resident) => {
    return resident.person_name !== twoTimeCopilotName;
  })[0].person_name;
}

Afternoon Group Exercise (Tell Me More)

General Pattern of the Async Functions

const asyncFuncToGetResultsFromDb = async () => {
  try {
    // Build your query text

    // await the db.query with the query text and save the results in a constant

    // Optional: print results to console to see the shape/contents of the results

    // Return the results stored in .rows
  }

  catch (error) {
    console.error(error);
  }
};

// Call the function

---

Get each restaurant's review count:

SELECT restaurant_id, COUNT(*)
FROM reviews
GROUP BY restaurant_id
ORDER BY COUNT(*) desc;

Find the user who wrote the most reviews:

SELECT user_id, COUNT(*)
FROM reviews
-- Exclude users with invalid usernames
WHERE user_id != '#NAME?'
GROUP BY user_id
ORDER BY COUNT(*) desc
LIMIT 1;

Find all restaurants matching an ID:

SELECT * FROM restaurants
WHERE id = '${restaurantId}';

Get all reviews for a restaurant matchingn an ID:

SELECT * FROM reviews
WHERE restaurant_id = '${restaurantId}';

Insert a new review record into the reviews table and return the inserted record:

INSERT INTO reviews(
  id,
  restaurant_id,
  user_id,
  details,
  stars,
  useful,
  funny,
  cool
)
VALUES(
  '${id}',
  '${restaurant_id}',
  '${user_id}',
  '${details}',
  ${stars},
  ${useful},
  ${funny},
  ${cool}
)
RETURNING *;

Find all reviews for restaurants matching a brand name:

SELECT * FROM reviews
JOIN restaurants
  ON reviews.restaurant_id = restaurants.id
WHERE restaurants.name = '${brandName}';

Get most reviewed restaurant

Use the getRestaurantsByReviewCount function and get the top result.

Then use restaurantWithReviews to find the reviews of this particular restaurant.