Introduction to SQL
This course is perfect for beginners ready to explore the world of SQL. Whether
you're just starting out in database management or aiming to use SQL for your
application development projects, this course covers the essentials. You'll quickly
learn how to leverage the full potential of SQL, from querying and managing
data to seamlessly integrating it into real-world applications. By the end of the
course, you'll have the confidence and skills to solve practical problems with SQL
and enhance your development process.
©2026 Codefinity. All Terms Reserved.
Retrieving Data
©2026 Codefinity. All Terms Reserved.
Retrieving Individual Columns
What is SQL?
Definition
SQL is a programming language for user interaction with databases, used to
query, update, and manage databases.
Database
Definition
A database is an organized structure designed to store, modify, and process large
amounts of data.
Why SQL Matters
SQL is essential for managing and querying databases efficiently. It is widely used due
to its standardization, scalability, and ability to ensure data integrity. SQL's integration
with other technologies makes it a versatile tool in various applications, supporting
real-time data processing and decision-making.
Let's get started! In this course, we will work with a country database containing a
country table. You can see it under the code editor on the right bottom corner.
SQL Syntax Explanation
To retrieve data from a database, SQL uses the SELECT statement. Here's the basic
syntax:
©2026 Codefinity. All Terms Reserved.
SELECT: specifies the columns to retrieve;
FROM: specifies the table from which to retrieve the data.
For example, to get the sample column from the sample_table table, the syntax is:
SELECT sample FROM sample_table;
The following query will return all entries in the continent column from the country
table:
SELECT continent FROM country;
In our previous query, we utilized the SELECT to retrieve a single continent column
from the country table. It's important to note that you must specify the column name
immediately after the SELECT keyword, and the FROM keyword specifies the name of
the table from which we wish to retrieve data.
©2026 Codefinity. All Terms Reserved.
Retrieving Multiple Columns
Inspecting the Table
Let's delve deeper into the country database. This database contains a single table, also
named country . We'll take a closer look at the data within this table.
This table contains 15 rows, representing 15 unique records for various countries.
Now, let's discuss the columns. The table includes 7 columns: id , name , continent ,
region , SurfaceArea , capital , and population .
1. id - the unique identifier for each record;
2. name - the name of the country;
3. continent - the continent where the country is located;
4. region - the specific region within the continent;
5. SurfaceArea - the total land area of the country;
6. capital - the capital city of the country;
7. population - the number of people living in the country.
The Syntax for Selecting Multiple Columns
©2026 Codefinity. All Terms Reserved.
To retrieve multiple columns, use the SELECT statement. After SELECT , list the column
names you want to retrieve, separated by commas.
The syntax looks like this:
SELECT column1, column2, column3
FROM table_name;
Here's an example of how to select three columns from the country table:
SELECT id, name, capital
FROM country;
©2026 Codefinity. All Terms Reserved.
Retrieving All Columns
Instead of listing each column, you can use the asterisk * to retrieve all columns from
the table at once. This is especially useful when you need to view all the data in a table
without manually typing out each column name. Here’s an example:
SELECT *
FROM table_name;
©2026 Codefinity. All Terms Reserved.
Retrieving Distinct Rows
Note that the SELECT statement returns all rows in a given column. However, what if
we don't need all the values from a column, especially when they are duplicated, and
we only need unique values?
For such cases, it is convenient to use the keyword DISTINCT , placed immediately
before the column names. Let's take a look at an example:
SELECT DISTINCT region
FROM country;
©2026 Codefinity. All Terms Reserved.
Limiting Results
We know that the SELECT operator can retrieve all rows from a table of all or the
specific columns. However, what if we only need to fetch a specific number of rows?
We can do it using the LIMIT clause. It is always used at the end of the query. Here is
the syntax for it:
SELECT columns
FROM table
LIMIT number_of_rows;
In the example below, we extract the first 7 rows (in our case, the capitals of the
countries) from the column:
SELECT capital
FROM country
LIMIT 7;
©2026 Codefinity. All Terms Reserved.
Challenge: Find All Countries With Their IDs
Definition
In most databases, the id column acts as a unique identifier, often referred to as
a primary key, for each record. By selecting both the id and name columns, you
can view the unique identifier linked to each country. This is crucial for associating
records or when you need to reference specific entries in the database.
©2026 Codefinity. All Terms Reserved.
Sorting Retrieved Data
©2026 Codefinity. All Terms Reserved.
Sorting Data
We can sort the data we receive in a specific order. To do this, we first retrieve the data
using the SELECT operator and then apply the ORDER BY clause. This clause takes the
names of columns based on which the output will be sorted.
Here's the syntax to help us understand:
SELECT columns
FROM table
ORDER BY column_name
Here is the example where we ordering the result by continent column:
SELECT continent
FROM country
ORDER BY continent;
It's important to note that when you specify a column with integer values, the sorting
will be done in ascending order. For string columns, the sorting will follow alphabetical
order.
Also, the ORDER BY clause should be placed just before the LIMIT clause, if a LIMIT
clause is included in the query.
Explanation: In the example, you can observe that the ORDER BY clause sorts the data
based on the continent column.
©2026 Codefinity. All Terms Reserved.
Sorting by Multiple Columns
We can also sort by multiple columns. We need to specify the column names separated
by commas.
The first column specified in the sorting will have priority, and then the sorting will
proceed according to the second column.
The syntax will look like this:
SELECT columns
FROM table
ORDER BY column_with_priority, column2, ...
Let’s look at the example that retrieves three columns and sorts the results by two of
them: first by population and then by capital :
SELECT ID, population, capital
FROM country
ORDER BY population, capital;
©2026 Codefinity. All Terms Reserved.
Specifying Sort Direction
Sorting Columns in Descending Order
We can do more than just sort data in ascending order by default. The ORDER BY clause
can also arrange data in descending order, which requires using the DESC keyword.
SELECT continent
FROM country
ORDER BY continent DESC;
Sorting Multiple Columns in Descending Order
We can sort data by multiple columns, and in many cases, this is actually necessary. For
example, when displaying a list of students, you might want to order them first by their
last name and then by their first name. This approach is especially helpful when multiple
students share the same name.
Also, if you're sorting in descending order by more than one column, remember that
each column must include its own DESC keyword.
SELECT id, name, region
FROM country
ORDER BY region, name DESC;
You only need to apply the DESC keyword to the column you want sorted in
descending order. In our example, we used DESC for the name column but not for the
region column. As a result, the name column is sorted in descending order, while the
region column remains sorted in ascending order (the default).
©2026 Codefinity. All Terms Reserved.
Filtering Data
©2026 Codefinity. All Terms Reserved.
Using the WHERE Clause
In databases, tables typically hold substantial volumes of data. However, frequently
we're interested in retrieving specific portions of the data rather than the entirety. To
accomplish this, we need to define the conditions for data retrieval, which are referred
to as filtering criteria.
Data is filtered using a WHERE clause specifying the search criteria in a SELECT
statement. The WHERE clause appears immediately after the table name.
When specifying a string value, such as a country name, we need to enclose the text in
single quotes ( ' ).
SELECT name, continent
FROM country
WHERE continent='Europe';
The SELECT statement gets 2 columns from the country table and returns only rows
with the continent value 'Europe' .
Clause Position
When we use the ORDER BY and WHERE clauses, we ensure the ORDER BY comes after
the WHERE clause.
SELECT capital, continent
FROM country
WHERE continent='Asia'
ORDER BY continent DESC;
©2026 Codefinity. All Terms Reserved.
The WHERE Clause Operators
SQL supports a lot of conditional statements.
Operator Description
= Equality
<> Inequality
< Less than
<= Less than or equal to
> Greater than
>= Greater than or equal to
BETWEEN Between two specified values
IS NULL Is a NULL value
Less Than
Let's look at the examples:
This example lists all countries that have a population of less than 2424200:
©2026 Codefinity. All Terms Reserved.
SELECT name, population
FROM country
WHERE population < 2424200;
Less Than or Equal to
The following example retrieves all countries with a population less than or equal to
2424200:
SELECT name, population
FROM country
WHERE population <= 2424200;
Not Equal
The following example retrieves all non-Asian countries:
SELECT name, continent
FROM country
WHERE continent<>'Asia';
©2026 Codefinity. All Terms Reserved.
Checking for a Range of Values
To check for a range of values, we should use the BETWEEN operator. This operator
requires two values: the start-value and the end-value of the range. Also, the AND
keyword should be placed between these values.
Here is the syntax for using the BETWEEN operator:
SELECT columns
FROM table
WHERE column BETWEEN value_from AND value_to;
Let's see an example:
SELECT name, population
FROM country
WHERE population BETWEEN 100000 AND 3000000;
©2026 Codefinity. All Terms Reserved.
Checking for NO Value
If a column doesn't contain any data, it's considered NULL . To determine if a value is
NULL , you don’t just check for equality. Instead, you use the WHERE clause with IS NULL
in a SELECT statement to find rows where the column’s value is NULL .
The syntax for it looks like this:
SELECT columns
FROM table
WHERE column_name IS NULL;
Let's illustrate this with an example:
SELECT name
FROM country
WHERE region IS NULL;
©2026 Codefinity. All Terms Reserved.
Advanced Data Filtering
©2026 Codefinity. All Terms Reserved.
Using the AND Operator
When you want to filter results using more than one column, you can chain conditions
together with the AND operator in the WHERE clause.
SELECT name, population, region
FROM country
WHERE population >= 1000000 AND continent ='Europe';
This query returns only the rows that meet both conditions: the country's population is
at least 1000000, and it's located in Europe.
©2026 Codefinity. All Terms Reserved.
Using the OR operator
The OR operator is used to filter records based on more than one condition, like if you
want to return all countries from Asia but also those from Europe.
Here is the syntax of using the OR operator:
SELECT columns
FROM table
WHERE column = 'first_option' OR column = 'second_option';
Let's look at the example:
SELECT name, population, continent, region
FROM country
WHERE continent = 'Asia' OR continent = 'Europe';
©2026 Codefinity. All Terms Reserved.
Using the NOT Operator
In order to cancel the condition, you need to use the keyword NOT .
The NOT operator is essential when you want to exclude certain records from your
query results. It allows you to specify conditions that should not be met, effectively
filtering out unwanted data. This can be particularly useful in scenarios where you need
to focus on a subset of data that does not meet specific criteria.
First, let's look at the syntax:
SELECT columns
FROM table
WHERE NOT condition;
Now let's look at the example:
SELECT name, capital
FROM country
WHERE NOT continent='Asia';
The NOT operator rejects the condition here; hence, it matches the continent to
anything that is not Asia.
©2026 Codefinity. All Terms Reserved.
Aggregate Functions
©2026 Codefinity. All Terms Reserved.
The AVG() Function
AVG() returns the average value of a certain column. Let's see an example:
SELECT AVG(population)
FROM country;
AVG() can also be used to find the average of a selected set of columns or rows that
meet certain conditions. Here’s an example:
SELECT AVG(population)
FROM country
WHERE population < 4478500;
©2026 Codefinity. All Terms Reserved.
The COUNT() Function
COUNT() is a function that allows you to count rows in a table or count how many rows
match certain criteria.
Let's see an example:
SELECT COUNT(name)
FROM country;
To count the total number of rows in the table, we can use the following syntax:
SELECT COUNT(*)
FROM country;
©2026 Codefinity. All Terms Reserved.
The MAX() Function
MAX() - This function returns the highest value in a particular column. Let's take a look
at the following example:
SELECT MAX(population)
FROM country;
Here MAX() returns the largest population value in the country table.
©2026 Codefinity. All Terms Reserved.
The MIN() Function
MIN() - this function returns the lowest value in a particular column.
Let's look at an example:
SELECT MIN(population)
FROM country;
Here, MIN() returns the smallest population value in the country table.
©2026 Codefinity. All Terms Reserved.
The SUM() Function
SUM() returns the total of all the values in a given column.
Here's an example:
SELECT SUM(population)
FROM country
WHERE continent='Asia';
The SUM() function calculates the total population of all Asian countries found in the
country table.
©2026 Codefinity. All Terms Reserved.