Course Content

Introduction to SQL

1. Retrieving Data

Retrieving Individual Columns Retrieving Multiple Columns Retrieving All Columns Retrieving Distinct Rows Limiting Results Challenge: Find the Population of the Countries Challenge: Find All Countries Challenge: Find All Countries With Their IDs Challenge: Find Country Capitals Challenge: Find the Regions in Which All Countries Are Located

2. Sorting Retrieved Data

Sorting Data Challenge: Population Sort Sorting by Multiple Columns Challenge: Dual-Sort Query Specifying Sort Direction Challenge: Sort the Countries by Region and Capital Challenge: Sort Capitals in Descending Order Challenge: Sort Countries in Ascending Order Challenge: Find Countries, Their IDs, and Their Populations Challenge: Find Countries, IDs, Populations, Regions, and Sort Them Challenge: Find All Continents and Sort Them in Ascending Order

3. Filtering Data

Using the WHERE Clause Challenge: North America Population Sort The WHERE Clause Operators Challenge: Population Threshold Query Checking for a Range of Values Checking for NO Value

4. Advanced Data Filtering

Using the AND Operator Challenge: Europe Region Sort Using the OR operator Challenge: America Regions Filter Using the NOT Operator Challenge: Non-Europe Capitals Sort

5. Aggregate Functions

The AVG() Function Challenge: Large Surface Area Average The COUNT() Function Challenge: Continental Count The MAX() Function The MIN() Function Challenge: Surface Area Selection The SUM() Function

The AVG() Function

AVG() returns the average value of a certain column. Let's see an example:


              12
            
SELECT AVG(population) 
FROM country;

AVG() can also determine the average of specific columns or rows (with some conditions). Let's take a look at the following example:


              123
            
SELECT AVG(population) 
FROM country
WHERE population < 4478500;

Task

Write an SQL query to retrieve the average value of a surfacearea column from the country table.

Here's a short example of the country table:

id	name	continent	region	surfacearea	capital	population
1	Japan	Asia	Eastern Asia	377829	Tokyo	126714000
2	Latvia	Europe	NULL	64589	Riga	2424200
3	Mexico	North America	Central America	1958201	Mexico City	98881000
...	...	...	...	...	...	...
15	Malta	Europe	Southern Europe	316	Valletta	380200

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

Everything was clear?

Thanks for your feedback!

Section 5. Chapter 1

The AVG() Function

AVG() returns the average value of a certain column. Let's see an example:


              12
            
SELECT AVG(population) 
FROM country;

AVG() can also determine the average of specific columns or rows (with some conditions). Let's take a look at the following example:


              123
            
SELECT AVG(population) 
FROM country
WHERE population < 4478500;

Task

Write an SQL query to retrieve the average value of a surfacearea column from the country table.

Here's a short example of the country table:

id	name	continent	region	surfacearea	capital	population
1	Japan	Asia	Eastern Asia	377829	Tokyo	126714000
2	Latvia	Europe	NULL	64589	Riga	2424200
3	Mexico	North America	Central America	1958201	Mexico City	98881000
...	...	...	...	...	...	...
15	Malta	Europe	Southern Europe	316	Valletta	380200

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

Everything was clear?

Thanks for your feedback!

Section 5. Chapter 1

The AVG() Function

AVG() returns the average value of a certain column. Let's see an example:


              12
            
SELECT AVG(population) 
FROM country;

AVG() can also determine the average of specific columns or rows (with some conditions). Let's take a look at the following example:


              123
            
SELECT AVG(population) 
FROM country
WHERE population < 4478500;

Task

Write an SQL query to retrieve the average value of a surfacearea column from the country table.

Here's a short example of the country table:

id	name	continent	region	surfacearea	capital	population
1	Japan	Asia	Eastern Asia	377829	Tokyo	126714000
2	Latvia	Europe	NULL	64589	Riga	2424200
3	Mexico	North America	Central America	1958201	Mexico City	98881000
...	...	...	...	...	...	...
15	Malta	Europe	Southern Europe	316	Valletta	380200

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

Everything was clear?

Thanks for your feedback!

AVG() returns the average value of a certain column. Let's see an example:


              12
            
SELECT AVG(population) 
FROM country;

AVG() can also determine the average of specific columns or rows (with some conditions). Let's take a look at the following example:


              123
            
SELECT AVG(population) 
FROM country
WHERE population < 4478500;

Task

Write an SQL query to retrieve the average value of a surfacearea column from the country table.

Here's a short example of the country table:

id	name	continent	region	surfacearea	capital	population
1	Japan	Asia	Eastern Asia	377829	Tokyo	126714000
2	Latvia	Europe	NULL	64589	Riga	2424200
3	Mexico	North America	Central America	1958201	Mexico City	98881000
...	...	...	...	...	...	...
15	Malta	Europe	Southern Europe	316	Valletta	380200

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

Section 5. Chapter 1

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