Learning SQL – Lessons for Beginners
Learning SQL – Day one – Lesson One:
As a beginner to SQL, the
first thing you should learn is the basic syntax of SQL queries. SQL, or
Structured Query Language, is a programming language used to manage and
manipulate data in relational database systems. SQL queries are used to
retrieve, insert, update, and delete data in a database.
The basic structure of a SQL query includes the
following clauses:
SELECT: specifies the columns
to retrieve
FROM: specifies the table(s)
to retrieve data from
WHERE: filters the rows to
retrieve based on a condition
GROUP BY: groups the
retrieved data by one or more columns
HAVING: filters the grouped
data based on a condition
ORDER BY: sorts the retrieved
data by one or more columns
Not all SQL queries require
all of these clauses. The SELECT and FROM clauses are mandatory, while the
other clauses are optional.
Here is an example of a basic SQL query:
Sql query code:
SELECT column1, column2, ... FROM table_name
WHERE condition;
In this query, the SELECT
clause specifies the columns to retrieve, the FROM clause specifies the
table(s) to retrieve data from, and the WHERE clause filters the rows to
retrieve based on a condition.
For example, to retrieve all
customers whose city is 'London', you can use the following query:
Sql query code:
SELECT * FROM customers WHERE city = 'London';
This query will retrieve all
columns from the customers table where the city column is equal to 'London'.
As a beginner, it's important
to understand the basic syntax of SQL queries and practice writing simple
queries like the one above. You can also learn about other clauses such as
GROUP BY and ORDER BY, which can help you manipulate and analyze data in more
complex ways.
Lesson 2 of learning SQL
Lesson 2: Data Filtering with WHERE Clause
One of the most common
operations in SQL is filtering data based on certain conditions. In order to do
this, we use the WHERE clause in our SQL statements. The WHERE clause allows us
to specify one or more conditions that must be true for a row to be returned in
our query results.
Syntax:
Sql Query code:
SELECT column1, column2, ... FROM table_name
WHERE condition;
In this syntax, the SELECT
statement is followed by a list of columns that we want to retrieve from the
table. The FROM statement specifies the table from which we want to retrieve
the data. The WHERE clause is used to specify the conditions that must be met
for a row to be returned in our query results.
Example:
Let's say we have a table
named "employees" with columns id, name, age, and salary. We can use
the following query to retrieve all employees who are older than 30 years:
Sql Query code:
SELECT * FROM employees WHERE age > 30;
In this query, we are using
the WHERE clause to specify the condition age > 30. This means that only
those rows will be returned where the value in the age column is greater than
30.
We can also use multiple
conditions in our WHERE clause by using logical operators such as AND and OR.
For example, let's say we want to retrieve all employees who are older than 30
years and have a salary greater than $50,000. We can use the following query:
Sql Query code:
SELECT * FROM employees WHERE age > 30 AND
salary > 50000;
In this query, we are using
the logical operator AND to combine two conditions. This means that only those rows
will be returned where both conditions are true.
We can also use the logical
operator OR to specify that either of two conditions must be true for a row to
be returned in our query results. For example, let's say we want to retrieve
all employees who are either older than 30 years or have a salary greater than
$50,000. We can use the following query:
Sql Query code:
SELECT * FROM employees WHERE age > 30 OR
salary > 50000;
In this query, we are using
the logical operator OR to combine two conditions. This means that any row
where either of the two conditions is true will be returned in our query
results.
Other operators that can be
used in the WHERE clause include =, <, >, <=, >=, and <> (not
equal to).
It is important to note that
the WHERE clause must be used after the FROM statement in our SQL query. This
is because we first need to specify the table from which we want to retrieve
the data before we can apply any conditions to it.
Next, we will move on to Lesson 3 and learn
about sorting data using the ORDER BY clause.
Lesson Three: Filtering Data
with WHERE Clause
Let's move on to lesson three of learning SQL.
Lesson Three: Filtering Data with WHERE Clause
In the previous lesson, we
learned how to retrieve data from a table using the SELECT statement. However,
sometimes we need to filter the data we retrieve based on certain conditions.
That's where the WHERE clause comes in.
The WHERE clause is used to
filter data based on one or more conditions. It is added to the SELECT
statement and specifies the conditions that the retrieved data must meet.
Here's the basic syntax of
the SELECT statement with a WHERE clause:
Sql Query code:
SELECT column1, column2, ... FROM table_name WHERE
condition;
The condition can be any
valid expression that evaluates to true, false, or unknown. It can contain
operators such as =, <, >, <=, >=, <>, BETWEEN, IN, LIKE, and
IS NULL.
Let's take a look at some
examples to see how the WHERE clause is used in practice:
Example 1: Retrieving data
based on a single condition
Suppose we have a table named
"employees" with columns "id", "name",
"age", and "salary". We want to retrieve the name and
salary of all employees who earn more than $50,000 per year. We can do this
using the following SQL statement:
Sql Query code:
SELECT name, salary FROM employees WHERE salary
> 50000;
This statement will retrieve
the name and salary of all employees whose salary is greater than 50,000.
Example 2: Retrieving data based on multiple conditions
Suppose we have a table named
"students" with columns "id", "name",
"age", "gender", and "grade". We want to retrieve
the name and grade of all male students who are older than 18 and have a grade
of A. We can do this using the following SQL statement:
Sql Query code:
SELECT name, grade FROM students WHERE gender =
'M' AND age > 18 AND grade = 'A';
This statement will retrieve
the name and grade of all male students who are older than 18 and have a grade
of A.
Example 3: Retrieving data based on a pattern match
Suppose we have a table named
"products" with columns "id", "name", and
"description". We want to retrieve the name of all products whose
name contains the word "phone". We can do this using the LIKE
operator in the WHERE clause:
Sql Query code:
SELECT name FROM products WHERE name LIKE '%phone%';
This statement will retrieve
the name of all products whose name contains the word "phone".
Example 4: Retrieving data based on null values
Suppose we have a table named
"customers" with columns "id", "name",
"email", and "phone". We want to retrieve the name of all
customers who have not provided their email address. We can do this using the
IS NULL operator in the WHERE clause:
Sql Query code:
SELECT name FROM customers WHERE email IS NULL;
This statement will retrieve
the name of all customers who have not provided their email address.
Summary:
In this lesson, we learned
how to use the WHERE clause to filter data based on one or more conditions. We
saw some examples of how to retrieve data based on a single condition, multiple
conditions, pattern matching, and null values. The WHERE clause is a powerful
feature of SQL that allows us to retrieve only the data we need from a table,
and it is essential for any SQL developer or analyst to understand how to use
it effectively.
Lesson 4: Filtering and
Sorting Data
In the previous lessons, we
learned how to retrieve data from a table using the SELECT statement and how to
filter data using the WHERE clause. In this lesson, we will dive deeper into
filtering data and sorting the retrieved data using the ORDER BY clause.
Filtering Data
Filtering data is an
essential aspect of working with SQL. In SQL, the WHERE clause is used to
filter the rows retrieved from a table based on a condition. The WHERE clause
uses comparison operators to compare a column value with a given value or
expression.
For example, let's say we
have a table named 'employees' with columns 'id', 'name', 'age', 'department',
and 'salary'. To retrieve all employees who work in the 'IT' department, we can
use the following SQL query:
Sql Query code:
SELECT * FROM employees WHERE department = 'IT';
This query will retrieve all
columns from the 'employees' table where the 'department' column is equal to
'IT'.
In addition to using
comparison operators, we can also use logical operators such as AND and OR to
create more complex conditions. For example, let's say we want to retrieve all
employees who work in the 'IT' department and earn a salary greater than 50000.
We can use the following SQL query:
Sql Query code:
SELECT * FROM employees WHERE department = 'IT'
AND salary > 50000;
This query will retrieve all
columns from the 'employees' table where the 'department' column is equal to
'IT' and the 'salary' column is greater than 50000.
Sorting Data
Once we have retrieved the
desired data from a table, we may want to sort the data in a specific order. In
SQL, we can use the ORDER BY clause to sort the retrieved data.
The ORDER BY clause takes one
or more columns as arguments and sorts the data based on the values in those
columns. By default, the data is sorted in ascending order. We can specify the
sorting order using the ASC (ascending) or DESC (descending) keyword.
For example, let's say we
want to retrieve all employees from the 'employees' table and sort them by
their age in descending order. We can use the following SQL query:
Sql Query code:
SELECT * FROM employees ORDER BY age DESC;
This query will retrieve all
columns from the 'employees' table and sort the data based on the 'age' column
in descending order.
We can also specify multiple
columns in the ORDER BY clause to sort the data based on multiple columns. For
example, let's say we want to retrieve all employees from the 'employees' table
and sort them by their department and then by their salary in descending order.
We can use the following SQL query:
Sql Query code:
SELECT * FROM employees ORDER BY department,
salary DESC;
This query will retrieve all
columns from the 'employees' table and sort the data based on the 'department'
column first and then by the 'salary' column in descending order.
Summary:
In this lesson, we learned
how to filter data using the WHERE clause and how to sort data using the ORDER
BY clause. These are fundamental aspects of SQL that are essential for working
with databases. By mastering these concepts, we can retrieve the desired data
from a database efficiently and effectively.
Lesson 5: Using SQL Functions
In addition to the basic SQL
commands covered in the previous lessons, SQL also includes a variety of
functions that can be used to manipulate data. Functions allow you to perform
calculations, format data, and manipulate strings, dates, and times.
There are several categories of functions in
SQL, including:
Aggregate functions - used to calculate a summary value from a set
of values. Examples of aggregate functions include SUM, AVG, MIN, MAX, and
COUNT.
String functions - used to manipulate string values. Examples
of string functions include CONCAT, SUBSTRING, UPPER, LOWER, and TRIM.
Date and time functions - used to manipulate date and time values.
Examples of date and time functions include DATE, YEAR, MONTH, DAY, HOUR,
MINUTE, SECOND, and NOW.
Mathematical functions - used to perform mathematical calculations.
Examples of mathematical functions include ABS, ROUND, CEILING, FLOOR, and
POWER.
Let's take a closer look at some of these
functions.
Aggregate Functions
Aggregate functions are used
to perform calculations on sets of values. The most common aggregate functions
are:
SUM:
calculates the sum of a set of values.
AVG:
calculates the average value of a set of values.
MIN:
returns the minimum value from a set of values.
MAX:
returns the maximum value from a set of values.
COUNT: counts the number of values in a set.
Here is an example of using the SUM function to
calculate the total sales for a particular product:
SELECT SUM(sales) FROM
products WHERE product_name = 'Widget';
String Functions
String functions are used to
manipulate string values. The most common string functions are:
CONCAT: combines two or more strings together.
SUBSTRING: extracts a portion of a string.
UPPER: converts a string to uppercase.
LOWER: converts a string to lowercase.
TRIM:
removes whitespace from the beginning and end of a string.
Here is an example of using the CONCAT function
to combine two strings:
SELECT CONCAT(first_name, '
', last_name) FROM employees;
Date and Time Functions
Date and time functions are
used to manipulate date and time values. The most common date and time
functions are:
DATE:
converts a string to a date value.
YEAR:
extracts the year from a date value.
MONTH: extracts the month from a date value.
DAY:
extracts the day from a date value.
HOUR:
extracts the hour from a time value.
MINUTE: extracts the minute from a time value.
SECOND: extracts the second from a time value.
NOW:
returns the current date and time.
Here is an example of using the YEAR function
to extract the year from a date:
SELECT YEAR(order_date) FROM
orders;
Mathematical Functions
Mathematical functions are
used to perform mathematical calculations. The most common mathematical
functions are:
ABS:
returns the absolute value of a number.
ROUND: rounds a number to a specified number of
decimal places.
CEILING: rounds a number up to the nearest integer.
FLOOR: rounds a number down to the nearest integer.
POWER: raises a number to a specified power.
Here is an example of using the ROUND function
to round a number to two decimal places:
SELECT ROUND(price, 2) FROM
products;
As you can see, SQL functions
provide a powerful way to manipulate data in a variety of ways. By
understanding how to use functions, you can unlock the full potential of SQL
and take your data analysis to the next level.
Lesson 6: Updating and
Deleting Data in SQL
In SQL, we not only retrieve
data, but we can also modify it. We can add new records, update existing
records, or delete records. In this lesson, we will learn how to update and
delete data in SQL.
Updating Data
The UPDATE statement is used
to modify existing records in a table. Here is the basic syntax:
Sql Query code:
UPDATE table_name SET column1 = value1, column2
= value2, ... WHERE condition;
The SET clause specifies the
columns to be updated along with their new values. The WHERE clause specifies
which records to update based on a condition.
For example, to update the
email of a customer whose customer_id is 1, we can use the following query:
Sql Query code:
UPDATE customers SET email = 'newemail@example.com'
WHERE customer_id = 1;
This will update the email of
the customer with customer_id = 1 to 'newemail@example.com'.
Deleting Data
The DELETE statement is used
to remove records from a table. Here is the basic syntax:
Sql Query code:
DELETE FROM table_name WHERE condition;
The WHERE clause specifies
which records to delete based on a condition. If the WHERE clause is omitted,
all records in the table will be deleted.
For example, to delete all
records from the customers table whose city is 'London', we can use the
following query:
Sql Query code:
DELETE FROM customers WHERE city = 'London';
This will delete all records
from the customers table whose city is 'London'.
It is important to be careful
when using the DELETE statement because it can permanently remove data from the
table. It is a good practice to always backup the data before performing any
delete operations.
Summary:
In this lesson, we learned
how to update and delete data in SQL using the UPDATE and DELETE statements.
With these statements, we can modify the data in our database to keep it up to
date and accurate. However, we must always be careful when performing these
operations to avoid accidentally deleting or modifying important data.
Lesson 7: Advanced SQL Concepts
In the previous lessons, we covered the basics of SQL,
including querying data from tables and using basic SQL functions. In this
lesson, we will cover some more advanced concepts that will help you take your
SQL skills to the next level.
Joins Joins are used
to combine data from two or more tables based on a related column between them.
There are different types of joins:
INNER JOIN:
returns only the matching rows between the two tables
LEFT JOIN:
returns all the rows from the left table and matching rows from the right table
RIGHT JOIN:
returns all the rows from the right table and matching rows from the left table
FULL OUTER JOIN:
returns all the rows from both tables, including the non-matching rows
Here is an example of an INNER JOIN between two tables,
where we want to combine data from the orders table and the customers table
based on the customer_id column:
Sql Query code
SELECT * FROM orders INNER
JOIN customers ON orders.customer_id = customers.customer_id;
Subqueries A subquery is a query inside another query. It
can be used to retrieve data from another table or to perform calculations on
the data. Here is an example of a subquery that retrieves the average price of
products in a category:
Sql Query code
SELECT category_name,
AVG(product_price) FROM products WHERE category_id IN ( SELECT category_id FROM
categories WHERE category_name = 'Electronics' ) GROUP BY category_name;
Aggregation Functions Aggregation functions are used to
perform calculations on groups of rows. Some of the most common aggregation
functions are:
COUNT(): counts
the number of rows in a group
SUM(): calculates
the sum of a column in a group
AVG(): calculates
the average of a column in a group
MIN(): returns
the minimum value of a column in a group
MAX(): returns
the maximum value of a column in a group
Here is an example of a query that calculates the total
revenue for each customer:
Sql Query code
SELECT customers.customer_id,
customers.first_name, customers.last_name, SUM(order_details.quantity *
products.product_price) as total_revenue FROM orders INNER JOIN customers ON
orders.customer_id = customers.customer_id INNER JOIN order_details ON orders.order_id
= order_details.order_id INNER JOIN products ON order_details.product_id =
products.product_id GROUP BY customers.customer_id, customers.first_name,
customers.last_name ORDER BY total_revenue DESC;
Window Functions Window functions are used to perform
calculations on a window or subset of rows. They are similar to aggregation
functions, but they do not group the data. Some of the most common window
functions are:
ROW_NUMBER():
assigns a unique number to each row within a window
RANK(): assigns a
rank to each row within a window
DENSE_RANK():
assigns a dense rank to each row within a window
LAG(): retrieves
the value of a column from the previous row within a window
LEAD(): retrieves
the value of a column from the next row within a window
Here is an example of a query that calculates the running
total of sales for each customer:
Sql Query code
SELECT
orders.order_date, customers.customer_id, customers.first_name,
customers.last_name, SUM(order_details.quantity * products.product_price) OVER
(PARTITION BY customers.customer_id ORDER BY orders.order_date) as
running_total FROM orders INNER JOIN customers ON orders.customer_id =
customers.customer_id INNER JOIN order_details ON orders.order_id =
order_details.order_id INNER JOIN products ON order_details.product_id =
products.product_id ORDER BY customers.customer_id, orders.order_date;
Lesson 8: Joining Tables
When working with relational
databases, it is often necessary to combine data from multiple tables. This is
where the JOIN statement comes in. A JOIN statement allows you to combine rows
from two or more tables based on a related column between them.
There are several types of
JOINs, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN. Here,
we will focus on the INNER JOIN, which is the most commonly used type of JOIN.
The syntax for an INNER JOIN
is as follows:
Sql Query code
SELECT column_name(s) FROM table1 INNER JOIN
table2 ON table1.column_name = table2.column_name;
In this syntax, column_name(s)
is the name(s) of the column(s) you want to retrieve, table1 and table2 are the
names of the tables you want to join, and column_name is the name of the
column(s) that the tables have in common. The ON clause specifies the condition
that must be met for the rows to be included in the result set.
Let's say we have two tables, employees and departments,
with the following data:
employees table:
|
emp_id |
first_name |
last_name |
department_id |
|
1 |
John |
Smith |
1 |
|
2 |
Jane |
Doe |
2 |
|
3 |
Bob |
Johnson |
1 |
|
4 |
Alice |
Brown |
3 |
departments table:
|
department_id |
department_name |
|
1 |
Sales |
|
2 |
Marketing |
|
3 |
Finance |
|
4 |
IT |
To retrieve the employee's first name, last
name, and department name, we can use the following INNER JOIN query:
Sql Query code
SELECT first_name, last_name, department_name FROM
employees INNER JOIN departments ON employees.department_id =
departments.department_id;
The result set would look
like this:
|
first_name |
last_name |
department_name |
|
John |
Smith |
Sales |
|
Jane |
Doe |
Marketing |
|
Bob |
Johnson |
Sales |
|
Alice |
Brown |
Finance |
Notice that the result set includes only the
rows where the department_id column of the employees table matches the department_id
column of the departments table.
In addition to the INNER
JOIN, there are other types of JOINs that you can use depending on your
specific needs. For example, the LEFT JOIN and RIGHT JOIN return all the rows
from one table and the matching rows from the other table, even if there are no
matches. The FULL OUTER JOIN returns all the rows from both tables, including
those that have no matches in the other table.
It's important to understand
how to use JOINs effectively when working with relational databases, as they
allow you to combine data from multiple tables to gain insights and perform
more complex queries.
