SQL Basics

SQL Basics

0%

1. Selecting

In this section, you will be introduced to the very basics of SQL: extracting one or multiple columns from a database.

The very first meet with the database

SELECTing certain columns

LIMITing results

Unique values from column

Unique values from multiple columns

How many unique?

2. Filtering

In this section, you will learn how to filter observations so only necessary rows will be extracted.

Number filtering

Combining conditions

Filtering by numeric interval

Multiple filtering on one column

3. Aggregating

In this section, you will learn about the usage of different aggregating functions for your table, such as minimum, maximum, average...

MINimal and MAXimal values

4. Sorting

In this section, you will learn how to sort your table, even having filters and aggregate functions applied to a table.

Sorting by one column

Sorting in DESCending order

The cheapest car

Sorting by multiple columns

5. Grouping

In this section, you will learn about grouping observations and usage of this principle.

What is grouping?

More aggregate statistics!

Grouping by multiple columns

The cheapest combination

HAVING some conditions

Least consuming cars

Filtering group size

6. Practicing

This chapter is designed for users who have completed the previous sections and want to consolidate knowledge.

Challenge: Unique combinations

Challenge: Average Q-series cars price

Challenge: The cheapest new A-series cars

Challenge: Relation between engine size and fuel consumption

Challenge: Mileage and manufacture year - is there relation?

Challenge: Average price for each model and year

Challenge: The most expensive transmission + engine size combinations

Course Content

SQL Basics

SQL Basics

1. Selecting

The very first meet with the database SELECTing certain columns LIMITing results Unique values from column Unique values from multiple columns How many?How many unique?Aliases Tables aliases

2. Filtering

Word filtering Number filtering Combining conditions Filtering by numeric interval Multiple filtering on one column LIKE filtering NOT like this!

3. Aggregating

MINimal and MAXimal values SUMming values Mean values ROUNDing

4. Sorting

Sorting by one column Sorting in DESCending order The cheapest car Sorting by multiple columns

5. Grouping

What is grouping?More aggregate statistics!Grouping by multiple columns The cheapest combination HAVING some conditions Least consuming cars Filtering group size

6. Practicing

Challenge: Unique combinations Challenge: Average Q-series cars price Challenge: The cheapest new A-series cars Challenge: Relation between engine size and fuel consumption Challenge: Mileage and manufacture year - is there relation?Challenge: Average price for each model and year Challenge: The most expensive transmission + engine size combinations

Challenge: The most expensive transmission + engine size combinations

Task

From the audi_cars table calculate the average price for each combination of transmission and enginesize. Round average price to 0 decimals (convert to integer). Filter to only those groups having at least 10 cars within. Sort the results in descending order by average price and extract only the first 5 rows.

Task

From the audi_cars table calculate the average price for each combination of transmission and enginesize. Round average price to 0 decimals (convert to integer). Filter to only those groups having at least 10 cars within. Sort the results in descending order by average price and extract only the first 5 rows.

Switch to desktop for real-world practiceContinue from where you are using one of the options below

Everything was clear?

Section 6. Chapter 7

toggle bottom row

Challenge: The most expensive transmission + engine size combinations

Task

From the audi_cars table calculate the average price for each combination of transmission and enginesize. Round average price to 0 decimals (convert to integer). Filter to only those groups having at least 10 cars within. Sort the results in descending order by average price and extract only the first 5 rows.

Task

From the audi_cars table calculate the average price for each combination of transmission and enginesize. Round average price to 0 decimals (convert to integer). Filter to only those groups having at least 10 cars within. Sort the results in descending order by average price and extract only the first 5 rows.

Switch to desktop for real-world practiceContinue from where you are using one of the options below

Everything was clear?

Section 6. Chapter 7

toggle bottom row

Challenge: The most expensive transmission + engine size combinations

Task

From the audi_cars table calculate the average price for each combination of transmission and enginesize. Round average price to 0 decimals (convert to integer). Filter to only those groups having at least 10 cars within. Sort the results in descending order by average price and extract only the first 5 rows.

Task

From the audi_cars table calculate the average price for each combination of transmission and enginesize. Round average price to 0 decimals (convert to integer). Filter to only those groups having at least 10 cars within. Sort the results in descending order by average price and extract only the first 5 rows.

Switch to desktop for real-world practiceContinue from where you are using one of the options below

Everything was clear?

Task

From the audi_cars table calculate the average price for each combination of transmission and enginesize. Round average price to 0 decimals (convert to integer). Filter to only those groups having at least 10 cars within. Sort the results in descending order by average price and extract only the first 5 rows.

Switch to desktop for real-world practiceContinue from where you are using one of the options below

Section 6. Chapter 7

Switch to desktop for real-world practiceContinue from where you are using one of the options below

some-alt