Зміст курсу

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

NOT like this!

Often situation when we know what we don't want to find. In the case of numeric value, you should already know, that there is <> sign in SQL which means not equal. This also works for non-numeric columns: you can easily set <> 'some word' condition. But what if we want to avoid multiple non-numeric values? Or avoid some pattern?

Of course, it's already solved! There is NOT statement in SQL, which can be used with LIKE and IN statements. For example, we can filter cars in audi_table so there won't be Q-series cars.


              123
            
SELECT *
FROM audi_cars
WHERE model NOT LIKE 'Q%'

Please note, that NOT should be placed before IN or LIKE, like in spelling.

Завдання

Extract all the cars from the audi_cars table except A3, A4, and Q3 models.

Завдання

Extract all the cars from the audi_cars table except A3, A4, and Q3 models.

Перейдіть на комп'ютер для реальної практикиПродовжуйте з того місця, де ви зупинились, використовуючи один з наведених нижче варіантів

Все було зрозуміло?

Секція 2. Розділ 7

NOT like this!

Of course, it's already solved! There is NOT statement in SQL, which can be used with LIKE and IN statements. For example, we can filter cars in audi_table so there won't be Q-series cars.


              123
            
SELECT *
FROM audi_cars
WHERE model NOT LIKE 'Q%'

Please note, that NOT should be placed before IN or LIKE, like in spelling.

Завдання

Extract all the cars from the audi_cars table except A3, A4, and Q3 models.

Завдання

Extract all the cars from the audi_cars table except A3, A4, and Q3 models.

Все було зрозуміло?

Секція 2. Розділ 7

NOT like this!

Of course, it's already solved! There is NOT statement in SQL, which can be used with LIKE and IN statements. For example, we can filter cars in audi_table so there won't be Q-series cars.


              123
            
SELECT *
FROM audi_cars
WHERE model NOT LIKE 'Q%'

Please note, that NOT should be placed before IN or LIKE, like in spelling.

Завдання

Extract all the cars from the audi_cars table except A3, A4, and Q3 models.

Завдання

Extract all the cars from the audi_cars table except A3, A4, and Q3 models.

Все було зрозуміло?

Of course, it's already solved! There is NOT statement in SQL, which can be used with LIKE and IN statements. For example, we can filter cars in audi_table so there won't be Q-series cars.


              123
            
SELECT *
FROM audi_cars
WHERE model NOT LIKE 'Q%'

Please note, that NOT should be placed before IN or LIKE, like in spelling.

Завдання

Extract all the cars from the audi_cars table except A3, A4, and Q3 models.

Секція 2. Розділ 7