Зміст курсу

SQL Tutorial for Beginners

1. Database & Introduction to Syntax

Meet the SQL Select the Songs!SQL Select Column SQL Distinct SQL Where Challenge Where SQL And, Or, Not

2. SQL Aggregate Functions

SQL Min and Max Challenge Min and Max SQL Count, Sum, Avg Challenge Count

3. Filtering Statements

SQL Null SQL Like Challenge Like SQL Between SQL Order By Challenge Order By SQL Group By Challenge Group By SQL Top and Limit SQL Aliases SQL Having Challenge Having SQL Comments

SQL Having

HAVING is a filter keyword that applies to the already grouped records. Unlike WHERE, which is used for conditions to filter the records, HAVING is used with a condition presented as an aggregate function and applied to the result obtained with the GROUP BY. Thus, the HAVING operator is only used with GROUP BY and only after this operator.

The syntax is next:


              1234567
            
--do not run this query
SELECT col1, col2, ...
FROM table
WHERE conditions
GROUP BY col1, col2, ...
HAVING agg_conditions
ORDER BY col1, col2, ...;

For example, you want to get info about singers and their songs: singers' names and the price of their most expensive song. To do that, you can use GROUP BY the singer and display the price using the function MAX(). But let it be the limit: you need only singers such that the maximum price is less than 1000. The next query solves this problem:


              1234
            
SELECT singer, MAX(price)
FROM songs
GROUP BY singer
HAVING MAX(price) < 1000

This way, you can add a condition to the aggregate function MAX(). You cannot do that in WHERE statement, that's why we need to use HAVING.

Завдання

Retrieve info about the singers and their number of songs. Select only singers with at least 2 songs. Order records by the number of songs in groups starting with the biggest one.

Завдання

Retrieve info about the singers and their number of songs. Select only singers with at least 2 songs. Order records by the number of songs in groups starting with the biggest one.

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

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

Секція 3. Розділ 11

SQL Having

The syntax is next:


              1234567
            
--do not run this query
SELECT col1, col2, ...
FROM table
WHERE conditions
GROUP BY col1, col2, ...
HAVING agg_conditions
ORDER BY col1, col2, ...;


              1234
            
SELECT singer, MAX(price)
FROM songs
GROUP BY singer
HAVING MAX(price) < 1000

This way, you can add a condition to the aggregate function MAX(). You cannot do that in WHERE statement, that's why we need to use HAVING.

Завдання

Retrieve info about the singers and their number of songs. Select only singers with at least 2 songs. Order records by the number of songs in groups starting with the biggest one.

Завдання

Retrieve info about the singers and their number of songs. Select only singers with at least 2 songs. Order records by the number of songs in groups starting with the biggest one.

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

Секція 3. Розділ 11

SQL Having

The syntax is next:


              1234567
            
--do not run this query
SELECT col1, col2, ...
FROM table
WHERE conditions
GROUP BY col1, col2, ...
HAVING agg_conditions
ORDER BY col1, col2, ...;


              1234
            
SELECT singer, MAX(price)
FROM songs
GROUP BY singer
HAVING MAX(price) < 1000

This way, you can add a condition to the aggregate function MAX(). You cannot do that in WHERE statement, that's why we need to use HAVING.

Завдання

Retrieve info about the singers and their number of songs. Select only singers with at least 2 songs. Order records by the number of songs in groups starting with the biggest one.

Завдання

Retrieve info about the singers and their number of songs. Select only singers with at least 2 songs. Order records by the number of songs in groups starting with the biggest one.

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

The syntax is next:


              1234567
            
--do not run this query
SELECT col1, col2, ...
FROM table
WHERE conditions
GROUP BY col1, col2, ...
HAVING agg_conditions
ORDER BY col1, col2, ...;


              1234
            
SELECT singer, MAX(price)
FROM songs
GROUP BY singer
HAVING MAX(price) < 1000

This way, you can add a condition to the aggregate function MAX(). You cannot do that in WHERE statement, that's why we need to use HAVING.

Завдання

Retrieve info about the singers and their number of songs. Select only singers with at least 2 songs. Order records by the number of songs in groups starting with the biggest one.

Секція 3. Розділ 11