1- Introduction
This document guides for beginners to learn SQL. The document is based on:
-
MySQL >= 5.6
- SQL language
- SQL syntax (Basic syntax, standard for every DB)
- Specific SQL syntax of MySQL.
2- Database to learn SQL (LearningSQL)
This Post use
LearningSQL database (
MySQL version). You need to create that database for the convenience of your studying (It takes you less than 5 minutes to create it).
Script create database. The structure of this database can be seen at:
Script create database. The structure of this database can be seen at:
3- Visualization tools to work with MySQL
In this instruction, I use
MySQL Workbench which is visual tool to work. You can see more instructions for installing
MySQL Community (After the install is finished,
MySQL Workbench is also included in) at:
4- Query
-
4.1- SQL Select
- Select statement is the most basic statement in SQL. It is used to query data in tables.
- PRODUCT_TYPE: A data table of types of products (Bank's service).
- This is Select statement, query data in the table PRODUCT_TYPE
- 12345678910111213
-- Query entire column of Product_Type table.
Select
*
From
Product_Type;
-- The above statement and the following statement are equivalent.
-- Pty is alias (a alias of Product_Type table).
Select
Pty.*
From
Product_Type Pty;
-- Query some columns.
Select
Pty.Product_Type_Cd
,Pty.
Name
From
Product_Type Pty;
- Query results:
- Query EMPLOYEE table:
- 12345678
-- Query 4 columns on the Employee table
-- Using emp as alias for Employee
Select
Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
,Emp.Dept_Id
From
Employee Emp;
- Query results:
Create Alias for column:
- 1234567891011121314151617181920
-- Using the concat (string1, string2) to concatenate two strings together.
-- We have a new column, use 'as' to define column name for this column.
Select
Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
,Emp.Dept_Id
,Concat(
'EMP'
,Emp.Emp_Id)
As
Emp_No2
-- New column
From
Employee Emp;
-- Maybe not need 'as' in the definition Alias column.
Select
Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
,Emp.Dept_Id
,Concat(
'EMP'
,Emp.Emp_Id) Emp_No2
-- New column
From
Employee Emp;
- Query results:
-
4.2- SQL Distinct
- Distinct statement is used along with Select in order to select data and skip identical data. Syntax is:
- 1
Select
distinct
, .... - View Example:
- 1234567891011
-- Query Product table.
Select
Pro.Product_Cd
,Pro.
Name
,Pro.Product_Type_Cd
From
Product Pro;
-- Query Product_Type in Product table.
Select
Pro.Product_Type_Cd
from
Product Pro;
-- Distinct need to use to remove the duplicates.
Select
Distinct
Pro.Product_Type_Cd
from
Product Pro;
- Results of running the above commands:
-
4.3- SQL Where
- Where is a statement aimed at limiting the scope of finding. For example, you want to look for service products that are "Individual an Business Loans"
- Product_Type_Cd = 'LOAN'.
- 12345
-- Querying the Product table to find the type of products:
-- "Individual and Business Loans".
-- Corresponding column: Product_Type_Cd = 'LOAN'.
Select
*
From
Product Pro
Where
Pro.Product_Type_Cd =
'LOAN'
;
- Query results:
- Another example used where and conditions
-
4.4- SQL And Or
- And and Or are operators used in where:
- For example, you want to make a list looking for employees whose first name starts with 'S' character and work in Operation department.
- 123456789101112131415161718
-- Query the employee whose first name starts with S.
Select
Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
,Emp.Dept_Id
From
Employee Emp
Where
Emp.First_Name
Like
'S%'
;
-- Query the employee whose first name starts with S.
-- and work in Operation department. Dept_Id = 1.
Select
Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
,Emp.Dept_Id
From
Employee Emp
Where
Emp.First_Name
Like
'S%'
And
Emp.Dept_Id = 1;
- Results of running the queries:
- Results of running the queries:
- 12345678910
-- Find the employees whose first name starts with S or P.
-- and work in Operation department. Dept_Id = 1.
Select
Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
,Emp.Dept_Id
From
Employee Emp
Where
(Emp.First_Name
Like
'S%'
Or
Emp.First_Name
Like
'P%'
)
And
Emp.Dept_Id = 1;
- Query Results:
-
4.5- SQL IN
- The IN operator allows testing a term of a condition by comparing it for equality with a list of fixed values. IN works with values of all data types
- 12345678910
-- This command searches the employee named
-- Susan or Paula or Helen.
Select
Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
,Emp.Dept_Id
From
Employee Emp
Where
Emp.First_Name
In
(
'Susan'
,
'Paula'
,
'Helen'
);
- Query Results:
-
4.6- SQL Between
- 12345678910111213141516
-- Find employees, with Emp_Id between 5 and 10.
Select
Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
,Emp.Start_Date
From
Employee Emp
Where
(Emp.Emp_Id
Between
5
And
10);
-- The statement above is equivalent to:
Select
Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
,Emp.Start_Date
From
Employee Emp
Where
Emp.Emp_Id >= 5
And
Emp.Emp_Id <= 10;
- Query Results:
- BETWEEN statement is also used in time limit. For example, you look for employees who begin working in the period of time from:
- 03-05-2002 ==> 09-08-2002 (dd-MM-yyyy)
- 12345678910111213141516171819202122232425
-- This statement helps find employees who have begun working for a period of time
-- specify it in where statement.
-- For example, 03-05-2002 ==> 09-08-2002 (Format: dd-MM-yyyy)
Select
Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
,Emp.Start_Date
,
-- DATE_FORMAT is a function of MySQL .
-- It may not exist in other DB.
-- DATE_FORMAT convert Date type to text
-- According to a certain format here is DD-MM-YYYY
Date_Format(Emp.Start_Date
,
'%d-%m-%Y'
) Start_Date_Vn
From
Employee Emp
Where
-- Str_To_Date is function of MySQL (It may not exist in other DB)
-- Str_To_Date convert Text to Date
-- According to a certain format here is DD-MM-YYYY
(
--
Emp.Start_Date
Between
Str_To_Date(
'03-05-2002'
,
'%d-%m-%Y'
)
And
Str_To_Date(
'09-08-2002'
,
'%d-%m-%Y'
)
--
);
- Query Results:
-
4.7- SQL Wildcard
- There are 2 special characters in SQL:
- % character
- _ character
- % describes 0, 1 or more characters
- _ describes exactly one character.
- 12345678910
-- Find Customers whose FED_ID is formatted:
-- The initial part is random, following by -, then two any characters, then -, and the last part is any.
-- Use two dashes (_) for illustrating two characters.
-- (Each dash (_) is a unique character).
Select
Cus.Cust_Id
,Cus.Fed_Id
,Cus.Address
From
Customer Cus
where
cus.fed_id
like
'%-__-%'
;
- Query Results:
-
4.8- SQL Like
- This statement we have become familiar in some of the examples above.
-
4.9- SQL Order By
- ORDER BY clause: Arranging the result of a query in order.
- 12345678910
-- Syntax:
SELECT
"column_name"
FROM
"table_name"
[
WHERE
"condition"
]
ORDER
BY
"column_name1"
[
ASC
,
DESC
],
"column_name2"
[
ASC
,
DESC
];
-- Note:
-- ASC: ascending (default)
-- DESC: descending order..
- Example:
- 12345678910111213141516171819202122232425262728
-- Arrange Product_Type_Cd in ascending order
-- Next, arrange Name in ascending order, too.
Select
Pro.Product_Cd
,Pro.Product_Type_Cd
,Pro.
Name
From
Product Pro
Order
By
Pro.Product_Type_Cd
Asc
,Pro.
Name
Asc
;
-- In Order BY, ASC is defaulted.
-- Thus, it is unnecessary to write ASC.
Select
Pro.Product_Cd
,Pro.Product_Type_Cd
,Pro.
Name
From
Product Pro
Order
By
Pro.Product_Type_Cd
,Pro.
Name
;
-- Arrange Product_Type_Cd in descending order
-- Next, arrange Name in ascending order
Select
Pro.Product_Cd
,Pro.Product_Type_Cd
,Pro.
Name
From
Product Pro
Order
By
Pro.Product_Type_Cd
Desc
,Pro.
Name
Asc
;
- Query Results:
- Order By always stands behind where.
- 123456789
-- Find the employees whose first name starts with S.
-- Sort descending by start date of work.
Select
Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
,Emp.Start_Date
From
Employee Emp
Where
Emp.First_Name
Like
'S%'
Order
By
Emp.Start_Date
Desc
;
- Query Results:
- 12345678910
-- Apply the order of column to Order by clause.
-- First_Name is the second column in Select clause.
-- Be able to use Order by 2 in lieu of Order by First_Name.
Select
Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
,Emp.Start_Date
From
Employee Emp
Order
By
2
Desc
;
-
4.10- SQL Group By
- First, we need to understand what Aggregate Functions are:
- Sum: Function returns the sum of all the values
- Avg: Function return average of values
- Count: Function Returns the number of items.
- Min: Function finds the minimum value
- Max: Function finds the maximum value
- 1234567
-- Query Account table.
Select
Acc.Account_Id
,Acc.Product_Cd
,Acc.Avail_Balance
,Acc.Pending_Balance
From
Account Acc;
- The question is that you want to see the total amount of money in account in proportion to each type of different services (Product_Cd). It means you need to group on Product_Cd.
- 123456
Select
Acc.Product_Cd
,
Count
(Acc.Product_Cd)
As
Count_Acc
,
Sum
(Acc.Avail_Balance)
As
Sum_Avail_Balance
,
Avg
(Acc.Avail_Balance)
As
Avg_Avail_Balance
From
Account Acc
Group
By
Acc.Product_Cd;
- Query Results:
- Thus, you have an evaluation:
- There are 4 accounts taking the service "Saving deposit" (SAV) with the total amount of 1855.76 and each account has an average amount of 463.94.
- ...
-
4.11- SQL Having
- The HAVING clause enables you to specify conditions that filter which group results appear in the final results.
The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause. - Suppose you want to group types of services (Product_Cd) on the Account table and display types that have more than three (>3) participants.
- 1234567
Select
Acc.Product_Cd
,
Count
(Acc.Product_Cd)
As
Count_Acc
,
Sum
(Acc.Avail_Balance)
As
Sum_Avail_Balance
-- Sum available balance
,
Avg
(Acc.Avail_Balance)
As
Avg_Avail_Balance
-- The average available balance
From
Account Acc
Group
By
Acc.Product_Cd
Having
Count
(Acc.Product_Cd) > 3;
- Query Results:
Distinguishing Where & Having
- You need to differentiate between Where and Having in the same statement.
- Where is a clause used to filter data before group
- Having is a clause used to filter data after group
- If you want to have general information of a bank branch ( BRANCH Table), you can use where to filter data before group.
- 12345678910
Select
Acc.Product_Cd
,
Count
(Acc.Product_Cd)
As
Count_Acc
,
Sum
(Acc.Avail_Balance)
As
Sum_Avail_Balance
,
Avg
(Acc.Avail_Balance)
As
Avg_Avail_Balance
From
Account Acc
-- Using WHERE to filter data before group
Where
Acc.Open_Branch_Id = 1
Group
By
Acc.Product_Cd
-- Using HAVING to filter data after group
Having
Count
(Acc.Product_Cd) > 1;
- Query Results:
5- SQL Insert
-
5.1- SQL Insert Into
- Syntax:
- 1234
-- Syntax of Insert Into:
INSERT
INTO
( "column1"
,
"column2"
, ...)
VALUES
(
"value1"
,
"value2"
, ...);
- For example, you insert a customer transaction in ACC_TRANSACTION table:
- 1234567891011121314151617181920212223
-- Insert a record in Acc_Transaction table
-- numeric value is automatically generated for Txn_ID.
-- Now() is function of MySQL, return system datetime
-- Now(): System datetime
Insert
Into
Acc_Transaction
(Txn_Id
,Amount
,Funds_Avail_Date
,Txn_Date
,Txn_Type_Cd
,Account_Id
,Execution_Branch_Id
,Teller_Emp_Id)
Values
(
Null
-- Txn_Id (automatically generated)
,100
-- Amount
,now()
-- Funds_Avail_Date
,now()
-- Txn_Date
,
'CDT'
-- Txn_Type_Cd
,2
-- Account_Id
,
Null
-- Execution_Branch_Id
,
Null
-- Teller_Emp_Id
);
-
5.2- SQL Insert Into Select
- You can use the Select statement to provide data for the Insert statement. Through I nsert Into ... Select statement.
- 12345
-- Syntax of INSERT INTO .. SELECT
INSERT
INTO
"table1"
(
"column1"
,
"column2"
, ...)
SELECT
"column3"
,
"column4"
, ...
FROM
"table2"
;
- Example:
- 123456789101112131415161718
-- Insert multi record in Acc_Transaction table
-- Use Select statement to provide data.
Insert
Into
Acc_Transaction
(Txn_Id
,Txn_Date
,Account_Id
,Txn_Type_Cd
,Amount
,Funds_Avail_Date)
Select
Null
-- Txn_Id (Tá»± sinh ra)
,Acc.Open_Date
-- Txn_Date
,Acc.Account_Id
-- Account_Id
,
'CDT'
-- Txn_Type_Cd
,200
-- Amount
,Acc.Open_Date
-- Funds_Avail_Date
From
Account Acc
Where
Acc.Product_Cd =
'CD'
;
6- SQL Update
1
2
3
4
5
| -- Syntax of Update: UPDATE "table_name" SET "column_1" = "new value 1" , "column_2" = "new value 2" WHERE "condition" ; |
Update, increase the amount of money in customers' account with
CUST_ID = 1 by 2%.
Update:
1
2
3
4
5
6
| -- Update, increase the amount of money in customers' account with CUST_ID = 1 by 2%. Update Account Acc Set Acc.Avail_Balance = Acc.Avail_Balance + 2 * Acc.Avail_Balance / 100 ,Acc.Pending_Balance = Acc.Pending_Balance + 2 * Acc.Pending_Balance / 100 Where Acc.Cust_Id = 1; |
Requery, after Update.
7- SQL Delete
Syntax delete records in the table
1
2
3
4
| -- Syntax delete records in the table. DELETE FROM "table_name" WHERE "condition" ; |
1
2
3
4
5
| -- Delete two records in the Acc_Transaction table Delete From Acc_Transaction Txn Where Txt.Txn_Id In (25 ,26); |
8- SQL Functions
8.1- SQL Count
- The COUNT() function returns the number of rows that matches a specified criteria.
- 123
-- Count the number of rows in the Account table
Select
Count
(Acc.Account_Id)
From
Account Acc;
- Count the number of accounts having transaction with the bank ( Acc_Transaction Table)
- 123
-- Count the number of accounts having transaction with the bank
Select
Count
(
distinct
txn.Account_id)
From
Acc_Transaction txn;
Using Group by:
- A customer can open many accounts. Each account is equivalent to a service product of the bank.
- You want to enumerate customers (CUST_ID) and their accounts.
- 1234567
-- Count the number of accounts opened for each customer....
Select
Acc.Cust_Id
,
Count
(Acc.Account_Id)
From
Account Acc
Group
By
Acc.Cust_Id;
-
8.2- SQL Sum
- The SUM() function returns the total sum of a numeric column.
- 1234
-- Syntax:
SELECT
SUM
(
"column_name"
)
FROM
"table_name"
;
- Example:
- 123456789
-- Find the sum of the money in customers' accounts with CUST_ID = = 1
Select
Sum
(Acc.Avail_Balance)
From
Account Acc
Where
Acc.Cust_Id = 1;
-- Use Group by.
-- Find the sum of the money in accounts owned by each customer.
Select
Acc.Cust_Id
,
Sum
(Acc.Avail_Balance)
From
Account Acc
Group
By
Acc.Cust_Id;
-
8.3- SQL AVG
- The AVG() function returns the average value of a numeric column.
- 1234
-- Syntax:
SELECT
AVG
(
"column_name"
)
FROM
"table_name"
;
- Example:
- 1234567891011121314
-- Find the average of money equivalent to each type of deposit.
Select
Avg
(Acc.Avail_Balance)
From
Account Acc
Where
Acc.Product_Cd =
'SAV'
;
-- Use Group by.
-- A customer can own one or more account.
-- Find the average of money in each account owned by each customer
-- (In the bank with Branch_ID = 1)
Select
Acc.Cust_Id
,
Avg
(Acc.Avail_Balance)
From
Account Acc
Where
Acc.Open_Branch_Id = 1
Group
By
Acc.Cust_Id;
-
8.4- SQL MIN
- The MIN() function returns the smallest value of the selected column.
- 1234
-- Syntax:
SELECT
MIN
(
"column_name"
)
FROM
"table_name"
;
- Example:
- 12345678910111213
-- Find the minimum amount of deposit.
Select
Min
(Acc.Avail_Balance)
From
Account Acc
Where
Acc.Product_Cd =
'SAV'
;
-- Use Group by.
-- A customer can own one or more account.
-- Accounts can be opened in different branches.
-- Find the amount in the account, minimum for each branch
Select
Acc.Open_Branch_Id
,
Min
(Acc.Avail_Balance)
From
Account Acc
Group
By
Acc.Open_Branch_Id;
-
8.5- SQL MAX
- The MAX() function returns the largest value of the selected column.Usage is similar to MIN. You can refer for example to MIN.
- 1234
-- Syntax:
SELECT
MAX
(
"column_name"
)
FROM
"table_name"
;
9- SQL Join
For example, you look at the information in the
EMPLOYEE table. You can find an employee if
you know his/her department's ID. However, it is just a meaningless
number.To find the department, you need to look for in the
DEPARTMENT table. The link of these tables to get the full information is known as
JOIN.
There are 4 ways to join two tables:
- INNER JOIN (JOIN)
- LEFT OUTER JOIN (LEFT JOIN)
- RIGHT OUTER JOIN (RIGHT JOIN)
- FULL OUTER JOIN (OUTER JOIN)
- CROSS JOIN
-
9.1- INNER JOIN (or JOIN)
- The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.
Syntax:
- 1234567891011121314
-- Syntax
SELECT
column_name(s)
FROM
table1
INNER
JOIN
table2
ON
table1.column_name=table2.column_name;
-- INNER JOIN can replaced by JOIN
-- Same Meaning, and result.
SELECT
column_name(s)
FROM
table1
JOIN
table2
ON
table1.column_name=table2.column_name;
- Syntax:
- 1234567891011
-- INNER JOIN 2 table: EMPLOYEE and DEPARTMENT.
Select
Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
,Emp.Dept_Id
,Dep.
Name
Dept_Name
From
Employee Emp
Inner
Join
Department Dep
On
Emp.Dept_Id = Dep.Dept_Id
Order
By
Emp.Emp_Id;
MySQL's own syntax:
- 1234567891011
-- Other MySQL syntax to join two tables:
Select
Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
,Emp.Dept_Id
,Dep.
Name
Dept_Name
From
Employee Emp
,Department Dep
Where
Emp.Dept_Id = Dep.Dept_Id
Order
By
Emp.Emp_Id;
-
9.2- LEFT OUTER JOIN (or LEFT JOIN)
- The LEFT OUTER JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.
- See the illustration below:
- Example:
- 123456789101112
-- Customer LEFT OUTER JOIN Officer
-- LEFT OUTER JOIN Can be replaced by LEFT JOIN (meaning, and the result is the same)
Select
Cus.Cust_Id
,Cus.Fed_Id
,Cus.State
,Ofc.Cust_Id
As
Ofc_Cust_Id
,Ofc.Officer_Id
,Ofc.Start_Date
,Ofc.Title
From
Customer Cus
-- Table1
Left
Outer
Join
Officer Ofc
-- Table2
On
Cus.Cust_Id = Ofc.Cust_Id;
- Result:
-
9.3- RIGHT OUTER JOIN (or RIGHT JOIN)
- RIGHT OUTER JOIN is quite similar to LEFT OUTER JOIN:
-
9.4- FULL OUTER JOIN (or OUTER JOIN)
- FULL OUTER JOIN is a combination of LEFT OUTER JOIN and RIGHT OUTER JOIN
- 1234567
-- Syntax: (FULL OUTER JOIN)
-- Or: FULL JOIN
SELECT
columns
FROM
table1
FULL
[
OUTER
]
JOIN
table2
ON
table1.
column
= table2.
column
;
10- Subquery
In
MySQL, a subquery is a query within a query. You can create subqueries within your SQL statements. These subqueries can reside in the
WHERE clause, the
FROM clause, or the
SELECT clause.
-
10.1- Subquery in the Where clause
- Most often, the subquery will be found in the WHERE clause. These subqueries are also called nested subqueries.
- 1234567
Select
Acc.Account_Id
,Acc.Open_Date
,Acc.Product_Cd
,Acc.Avail_Balance
From
Account Acc
Where
Acc.Cust_Id
In
(
Select
Cus.Cust_Id
From
Customer Cus
Where
Cus.Cust_Type_Cd =
'B'
);
-
10.2- Subquery in the From clause
- A subquery can also be found in the FROM clause. These are called inline views.
- 1234567891011
Select
Cus.Cust_Id
,Cus.Address
,Cus.Fed_Id
,Acc2.Sum_Avail_Balance
From
Customer Cus
,
-- Define a inline view.
(
Select
Acc.Cust_Id
,
Sum
(Acc.Avail_Balance)
As
Sum_Avail_Balance
From
Account Acc
Group
By
Acc.Cust_Id) Acc2
Where
Cus.Cust_Id = Acc2.Cust_Id;
-
10.3- Subquery in the Select clause
- A subquery can also be found in the SELECT clause.
- 1234567
Select
Cus.Cust_Id
,Cus.Address
,Cus.Fed_Id
,(
Select
Sum
(Acc.Avail_Balance)
From
Account Acc
Where
Acc.Cust_Id = Cus.Cust_Id)
As
Sum_Avail_Balance
From
Customer Cus;
- The trick to placing a subquery in the select clause is that the subquery must return a single value. This is why an aggregate function such as SUM function, COUNT function, MIN function, or MAX function is commonly used in the subquery.
very good post!!! Thanks for sharing with us. It is more useful for us...
ReplyDeleteMysql Tutorial
Learn Mysql