course content

Course Content

Intermediate SQL

1. Grouping

GROUP BYChallenge: Number of StationsChallenge: Total Ride Time for Each Subway LineChallenge: Average Ride Time for Each Subway LineChallenge: The Shortest Ride of Each Subway LineHAVINGChallengeMore Complex Filtering

2. Nested Subqueries

Nested Queries

3. Joining Tables

JOIN 2 TablesChallenge: All Flights InformationChallenge: Information about Passenger Flight DepartureChallenge: Find the Number of PassengerJOIN 3 TablesChallenge 1Challenge 2UNION clause

4. Left, Right, and Inner Joins

INNER JOINChallengeLEFT JOINRIGHT JOIN

Intermediate SQL

INNER JOININNER JOIN

INNER JOIN is a join that will join only those records from two tables that contain the same values in the column being joined. It will be easier to understand on the gif. Please, watch it.

Let's take a look at the syntax.

In this section, we are going to work with the product database. It contains the following two tables: product, and price. Let's take a look at them closer and learn their connections.

Let's look at the first table - product.

Example

Let's look at the second table - price.

Example

Note

INNER JOIN and JOIN have the same functionality in SQL and can be used interchangeably to join tables based on a specified condition.

It is time to practice!

Task

  1. You need to retrieve the names (name column from the product table);
  2. Retrieve the price (price column from price table) of the products whose price exceeds $10;
  3. For it you have to join these tables using the INNER JOIN by product_id column;
  4. Please start with the product table and then join the price table.

Everything was clear?

Section 4. Chapter 1
toggle bottom row