31 Examples to Master SQL
In today’s highly data-driven business world, SQL is a highly sought after skill, and individuals who have a strong understanding of SQL are in high demand in a variety of industries, including finance, healthcare, and technology.
Whether you’re working as a data analyst, database administrator, or software developer, having a solid understanding of SQL can help you advance your career and stay ahead of the curve in a rapidly evolving job market.
Getting Started with SQL
Relational Data
A relational database consists of multiple tables that relate to each other. The relationship between tables is formed in the sense of shared columns.
Database Management
There are many different relational database management systems (MySQL, PostgreSQL, SQL Server). The SQL syntax between them may differ slightly.
Practical Approach
1. Connect to the server
First, we connect to the Terminal’s MySQL server and create a database.
sudo mysql -u root
We will be asked to enter the password. Now we are connected to the MySQL server on our machine.
2. Create a Database
The following command creates a database called “retail.
create database retail;
use retail;
We are not yet in the retail database that does not yet contain tables.
3. Create tables
First, we will create a table called “client” using the create table command.
create table customer (
cust_id int primary key,
age int,
location varchar (20),
gender varchar (20));
We define the name of the columns and associated data types within the parenthesis. The column cust_id is specified as the primary key.
The primary key is the column that uniquely identifies each row. It’s like the index of a Pandas DataFrame.
4. Create another table
Let’s create the second table that is called “orders.
create table orders (
order_id int primary key,
date date,
amount decimal(5,2),
cust_id int,
foreign key (cust_id) references customer(cust_id)
on delete cascade);
In the beginning, we mentioned that relational tables are related to each other through shared columns. A column that lists two tables is a foreign key.
The foreign key is what concerns one table to another.
The foreign key contains the primary key of another table.
The column cust_id in the orders table is a foreign key and relates the order table to the customer table. We specify this condition when creating the table.
5. View Tables
The retail database contains two tables now. We can see the existing tables in a database using the show tables command.
show tables;
+------------------+
| Tables_in_retail |
+------------------+
| customer |
| orders |
+-----------------+
Note: SQL commands end with a semicolon (“;”).
6. Table Description — info( )
The desc or describe commands provide an overview of the table in column names, data types, and additional information.
mysql> desc orders;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| order_id | int(11) | NO | PRI | NULL | |
| date | date | YES | | NULL | |
| amount | decimal(5,2) | YES | | NULL | |
| cust_id | int(11) | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+
7. Modify Tables
We can modify existing tables. For example, the change table command can add a new column or delete an existing one. For example, let’s add a column to the order table called “is_sale.”
alter table orders add is_sale varchar(20);
We type dread the column name and data type, along with the additional keyword.
desc orders;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| order_id | int(11) | NO | PRI | NULL | |
| date | date | YES | | NULL | |
| amount | decimal(5,2) | YES | | NULL | |
| cust_id | int(11) | YES | MUL | NULL | |
| is_sale | varchar(20) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
The column is_sale has been added to the order table.
8. Delete Feature
We can also use the changing table to delete a column with a slight change in syntax.
alter table orders drop is_sale;
The drop keyword is used instead of the add. It is because we also don’t need to write the data type to drop a column.
9. Enter data
We have tables, but they don’t contain data. One way to populate tables is the insertion statement.
insert into customer values (
1000, 42, 'Austin', 'female);
The specified values are entered in the columns in the same order. So, we need to maintain consistent order.
10. Insert Multiple Lines
We can insert multiple lines at the same time by selecting each row.
insert into customer values
(1001, 34, 'Austin', 'male'),
(1002, 37, 'Houston', 'male'),
(1003, 25, 'Austin', 'female'),
(1004, 28, 'Houston', 'female'),
(1005, 22, 'Dallas', 'male');
11. Deleting Lines
The declaration deletion can be used to delete existing rows in a table. First, we need to identify the rows to be deleted by providing a condition. For example, the statement below will delete the row with an order id of 17.
delete from orders
where order_id = 17;
If we do not specify a condition, all rows in the given table are deleted.
12. Update a Line
We can also update an existing line. For example, let’s update a row in the order table.
+----------+------------+--------+---------+
| order_id | date | amount | cust_id |
+----------+------------+--------+---------+
| 1 | 2020-10-01 | 24.40 | 1001 |
+----------+------------+--------+---------+
It is the first row in the order table. We want to change the order value to 27.40.
update orders
set amount = 27.40 #alterar essa coluna
where order_id = 1;
select * from orders limit 1;
+----------+------------+--------+---------+
| order_id | date | amount | cust_id |
+----------+------------+--------+---------+
| 1 | 2020-10-01 | 27.40 | 1001 |
We write the updated values after the keyword set. The rows to be updated are identified by providing the conditions after the keyword where.
13. Replicate a Table Structure
If we want to create a table by copying the structure of an existing table, we can use the create table statement with a similar keyword.
create table orders_copy like orders;
show tables;
+------------------+
| Tables_in_retail |
+------------------+
| customer |
| orders |
| orders_copy |
+------------------+
The table orders_copy has the same structure as the order table but contains no data.
14. Replicate a whole Table
We can also create a copy of an existing table with the data using the create table and select claims together.
create table new_orders
select * from orders;
It Looks like a combination of two separate statements. The first row creates the table, and the second row fills it with the data in the order table.
15. Drop Tables
We can use the drop statement to delete tables in a database.
drop table orders_copy, new_orders;
show tables;
+------------------+
| Tables_in_retail |
+------------------+
| customer |
| orders |
We successfully dropped the tables created in the previous example.
We have two relational tables in a database. The following examples will demonstrate how we can retrieve data from these tables using selected queries.
16. View Table Features
The most straightforward query is to view all the columns in a table.
select * from orders
limit 3;
+----------+------------+--------+---------+
| order_id | date | amount | cust_id |
+----------+------------+--------+---------+
| 1 | 2020-10-01 | 27.40 | 1001 |
| 2 | 2020-10-01 | 36.20 | 1000 |
| 3 | 2020-10-01 | 65.45 | 1002 |
+----------+------------+--------+---------+
The “*” selects all columns, and the limit keyword places a constraint on the number of rows to display.
17. Select Specific Columns
We can select only some of the columns by typing the column name instead of “*
select order_id, amount
from orders
limit 3;
+----------+--------+
| order_id | amount |
+----------+--------+
| 1 | 27.40 |
| 2 | 36.20 |
| 3 | 65.45 |
+----------+--------+
18. Condition Where
We can specify a condition for rows to be selected using the where clause. For example, the following query will return all orders placed in 2020–10–01.
select * from orders
where date = '2020-10-01';
+----------+------------+--------+---------+
| order_id | date | amount | cust_id |
+----------+------------+--------+---------+
| 1 | 2020-10-01 | 27.40 | 1001 |
| 2 | 2020-10-01 | 36.20 | 1000 |
| 3 | 2020-10-01 | 65.45 | 1002 |
+----------+------------+--------+---------+
19. Multiple Where Conditions
The clause accepts multiple conditions. Let’s add another state to the query in the previous example.
select * from orders
where date = '2020-10-01' and amount > 50;
+----------+------------+--------+---------+
| order_id | date | amount | cust_id |
+----------+------------+--------+---------+
| 3 | 2020-10-01 | 65.45 | 1002 |
+----------+------------+--------+---------+
20. Sort
We may want to sort query results that can be done using order by clause. For example, the following query will return orders in 2020–10–02 and sort them based on value.
select * from orders
where date = '2020-10-02'
order by amount;
+----------+------------+--------+---------+
| order_id | date | amount | cust_id |
+----------+------------+--------+---------+
| 5 | 2020-10-02 | 18.80 | 1005 |
| 6 | 2020-10-02 | 21.15 | 1009 |
| 4 | 2020-10-02 | 34.40 | 1001 |
| 7 | 2020-10-02 | 34.40 | 1008 |
| 8 | 2020-10-02 | 41.10 | 1002 |
+----------+------------+--------+---------+
21. Ascending Sort
The order by clause sorts the lines in ascending order by default. However, we can change it to go down with the keyword desc.
select * from orders
where date = '2020-10-02'
order by amount desc;
+----------+------------+--------+---------+
| order_id | date | amount | cust_id |
+----------+------------+--------+---------+
| 8 | 2020-10-02 | 41.10 | 1002 |
| 4 | 2020-10-02 | 34.40 | 1001 |
| 7 | 2020-10-02 | 34.40 | 1008 |
| 6 | 2020-10-02 | 21.15 | 1009 |
| 5 | 2020-10-02 | 18.80 | 1005 |
+----------+------------+--------+---------+
22. Count (fundamental)
SQL is a universal language that can also be used as a data analysis tool. It provides many functions for analyzing and transforming data during the query from a database.
For example, we can count the number of unique days in the order table.
select count(distinct(date)) as day_count
from orders;
+-----------+
| day_count |
+-----------+
| 4 |
+-----------+
The order table contains orders on four different days — the keyword “as” is used to rename the column in the query result. Otherwise, the column name would be “count(data).”
23. Group By
There are four different days in the order table. We can also find out how many orders per day. Group by will help us in this task.
select date, count(order_id) as order_count
from orders
group by date;
+------------+-------------+
| date | order_count |
+------------+-------------+
| 2020-10-01 | 3 |
| 2020-10-02 | 5 |
| 2020-10-03 | 6 |
| 2020-10-04 | 2 |
+------------+-------------+
We count the orders and group them up to the dates column.
24. Average daily value
We will calculate the average order value for each day and order the results based on the average value in descending order.
select date, count(order_id) as order_count
from orders
group by date;
+------------+-------------+
| date | order_count |
+------------+-------------+
| 2020-10-01 | 3 |
| 2020-10-02 | 5 |
| 2020-10-03 | 6 |
| 2020-10-04 | 2 |
+------------+-------------+
25. Group and Filter
We want to modify the query in the previous example and include only days with an average value greater than 30.
select date, avg(amount)
from orders
group by date
having avg(amount) > 30
order by avg(amount) desc;
+------------+-------------+
| date | avg(amount) |
+------------+-------------+
| 2020-10-01 | 43.016667 |
| 2020-10-04 | 42.150000 |
| 2020-10-03 | 37.025000 |
+------------+-------------+
It is important to note that the order of the statements in the query matters. For example, it gives an error if we place the order by clause before they have a clause.
26. Maximum Daily Value
We want to find out the maximum order amount per day.
select date, max(amount)
from orders
group by date;
+------------+-------------+
| date | max(amount) |
+------------+-------------+
| 2020-10-01 | 65.45 |
| 2020-10-02 | 41.10 |
| 2020-10-03 | 80.20 |
| 2020-10-04 | 50.10 |
+------------+-------------+
27. Combine Various Functions
We want to combine multiple aggregate functions into a selected statement. To demonstrate this, let’s elaborate on the previous example.
We want to see the difference between the maximum order and the minimum order for each customer. We also want to sort the results based on the difference in the ascending order and display the first three.
select cust_id, max(amount) - min(amount) as dif
from orders
group by cust_id
order by dif desc
limit 3;
+---------+-------+
| cust_id | dif |
+---------+-------+
| 1007 | 46.00 |
| 1009 | 28.95 |
| 1002 | 24.35 |
+---------+-------+
The dif column is obtained by subtracting the minimum value from the maximum value.
28. Grouping Count
We’re moving to the client’s desk now. But, first, let’s find out how many female and male clients we have in each city.
select location, gender, count(cust_id)
from customer
group by location, gender;
+----------+--------+----------------+
| location | gender | count(cust_id) |
+----------+--------+----------------+
| Austin | female | 2 |
| Austin | male | 1 |
| Dallas | female | 2 |
| Dallas | male | 2 |
| Houston | female | 2 |
| Houston | male | 1 |
+----------+--------+----------------+
29. Relationship
Customer and order tables are related to each other based on the cust_id. Therefore, we can query data from both tables using JOINS.
We want the average order value for each city in the customer table.
select customer.location, avg(orders.amount) as avg
from customer
join orders
on customer.cust_id = orders.cust_id
group by customer.location;
+----------+-----------+
| location | avg |
+----------+-----------+
| Austin | 33.333333 |
| Dallas | 34.591667 |
| Houston | 44.450000 |
Because we select columns from two different tables, column names are specified with the associated table name. The second, third, and fourth rows of the above query join the order and customer table based on the cust_id in each table.
30. Aggregation and Filter
We want to see the average age of customers who have an order in 2020–10–03.
select avg(c.age) as avg_age
from customer c
join orders o
on c.cust_id = o.cust_id
where o.date = '2020-10-03';
+---------+
| avg_age |
+---------+
| 30.0000 |
+---------+
We can use codenames for the names on the table as well. It is useful when we need to type table names often.
31. Nested Condition
We want to see the location of the customer who has the most significant number of orders.
select c.location, o.amount
from customer c
join orders o
on c.cust_id = o.cust_id
where o.amount = (select max(amount) from orders);
+----------+--------+
| location | amount |
+----------+--------+
| Dallas | 80.20 |
+----------+--------+
We have a selected statement nested in this query.
Concluding
I believe the 30 examples in this article provide a comprehensive introduction to SQL. In addition, we cover the following topics:
It is best to move on to the more advanced operations since you are comfortable working with the basics.
Thank you for taking the time to read it.
Business Analyst |EXCEL |SQL |Tableau lData analysis
1 年Saved this post... practicing with real business problems gives more insight on how the tools are used in the business world.