o7planning

SQL Tutorial for Beginners with SQL Server

  1. Introduction
  2. Visualization tools to work with SQL Server
  3. Database to learn SQL (LearningSQL)
  4. Query
  5. Insert
  6. SQL Update
  7. SQL Delete
  8. SQL Functions
  9. SQL Join
  10. Subquery
  11. SQL Server Transact-SQL Programming Tutorial

1. Introduction

This document guides for beginners to learn SQL. The document is based on:
  • SQL Server (2008 - 2020)
Its content includes:
  • SQL language
  • SQL syntax (Basic syntax, standard for every DB)
  • Specific SQL syntax of SQL Server.
See more:

2. Visualization tools to work with SQL Server

In this instruction, I use SQL Server Management Studio which is visual tool to work. You can see more instructions for installing SQL Server Express (After the install is finished, SQL Server Management Studio is also included in) at:

3. Database to learn SQL (LearningSQL)

This document use LearningSQL database (SQL Server 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:

4. Query

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
-- 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:
-- 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:
-- Using Convert(Varchar, <intvalue>) to convert int to Varchar (Function of SQL Server)
-- Using + operation 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
    ,'EMP' +  Convert(Varchar,Emp.Emp_Id) As Emp_No2  -- Cột mới
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
    ,'EMP' + Convert(varchar,Emp.Emp_Id ) Emp_No2  -- New column
From   Employee Emp;
Query results:
SQL Distinct
Distinct statement is used along with Select, in order to select data and skip identical data. Syntax is:
Select distinct <column1>, <column2>  ....
View Example:
-- 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:
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'.
You need to query in PRODUCT table, where Product_Type_Cd= 'LOAN'.
-- 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
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. 
-- 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:
Example:
-- 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:
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 type
-- 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:
SQL Between
-- 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:
  • 03-05-2002 ==> 09-08-2002 (dd-MM-yyyy)
-- 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
     , -- Convert(Varchar, <Date>, 105) Function convert Date to varchar Varchar with DD-MM-YYYY format
      -- This is function of SQL Server (It may not exist in other DB).
      Convert(Varchar, Emp.Start_Date
             ,105) Start_Date_Vn
From   Employee Emp
Where
-- Convert(Datetime, <Varchar>, 105)  Function convert text with DD-MM-YYYY format to Datetime
-- (This is function of  SQLServer, (It may not exist in other DB))
( --
 Emp.Start_Date Between Convert(Datetime, '03-05-2002'
                               ,105) And
 Convert(Datetime,'09-08-2002'
        ,105) --
);
Query Result:
SQL Wildcard
There are 2 special characters in SQL:
  1. % character
  2. _ character
Meaning:
  • % describes 0, 1 or more characters
  • _ describes exactly one character.
Two characters are usually used in the LIKE condition.
-- 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:
SQL Like
This statement we have become familiar in some of the examples above.
SQL Order By
ORDER BY clause: Arranging the result of a query in order.
-- 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:
-- 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.
-- 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:
OR:
-- 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;
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
Those are some common aggregate functions. They can participate in Group by statement.
-- 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.
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;
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.
  • ...
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.
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
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.
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. Insert

SQL Insert Into
Syntax:
-- Syntax of Insert Into:

INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...);
For example, you insert a customer transaction in ACC_TRANSACTION table:
-- Insert a record in Acc_Transaction table
-- numeric value is automatically generated for Txn_ID.
-- Current_Timestamp is function of SQL Server, return system datetime
-- Current_Timestamp : System datetime

Insert Into Acc_Transaction
  (Amount
  ,Funds_Avail_Date
  ,Txn_Date
  ,Txn_Type_Cd
  ,Account_Id
  ,Execution_Branch_Id
  ,Teller_Emp_Id)
Values
  (100 -- Amount
  ,Current_Timestamp -- Funds_Avail_Date
  ,Current_Timestamp -- Txn_Date
  ,'CDT' -- Txn_Type_Cd
  ,2 -- Account_Id
  ,Null -- Execution_Branch_Id
  ,Null -- Teller_Emp_Id
   );
SQL Insert Into Select
You can use the Select statement to provide data for the Insert statement. Through Insert Into ... Select statement.
-- Syntax of INSERT INTO .. SELECT

INSERT INTO "table1" ("column1", "column2", ...)
SELECT "column3", "column4", ...
FROM "table2";
Example:
-- Insert multi record in Acc_Transaction table
-- Use Select statement to provide data.
-- numeric value is automatically generated for Txn_ID.

Insert Into Acc_Transaction
( Txn_Date
,Account_Id
,Txn_Type_Cd
,Amount
,Funds_Avail_Date)
Select 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

-- 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 statement:
-- Update, increase the amount of money in customers' account with CUST_ID = 1 by 2%.

Update Account
Set    Avail_Balance   = Avail_Balance + 2 * Avail_Balance / 100
     ,Pending_Balance = Pending_Balance + 2 * Pending_Balance / 100
Where  Cust_Id = 1;
Requery, after update.

7. SQL Delete

Syntax delete records in the table
-- Delete two records in the Acc_Transaction table

DELETE FROM "table_name"
WHERE "condition";
-- Delete two records in the Acc_Transaction table

Delete From Acc_Transaction
Where Txn_Id In (25
                   ,26);

8. SQL Functions

SQL Count
The COUNT() function returns the number of rows that matches a specified criteria.
-- Count the number of rows in the Account table

Select Count(Acc.Account_Id) As Count_Acc From Account Acc;
Count the number of accounts having transaction with the bank (Acc_Transaction Table)
-- Count the number of accounts having transaction with the bank

Select Count(distinct txn.Account_id) as Distinct_Acc From Acc_Transaction txn;
Group by
A customer can open many accounts. Each account is equivalent to a product (service) of the bank.
You want to enumerate customers (CUST_ID) and their accounts.
-- Count the number of accounts opened for each customer....

Select Acc.Cust_Id
     ,Count(Acc.Account_Id) As Count_Acc
From   Account Acc
Group  By Acc.Cust_Id;
SQL Sum
The SUM() function returns the total sum of a numeric column.
-- Syntax:

SELECT SUM("column_name")
FROM "table_name";
Example:
-- Find the sum of the money in customers' accounts with CUST_ID = = 1
Select Sum(Acc.Avail_Balance)  As Sum_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) As Sum_Avail_Balance
From   Account Acc
Group  By Acc.Cust_Id;
SQL AVG
The AVG() function returns the average value of a numeric column.
-- Syntax:

SELECT AVG("column_name")
FROM "table_name";
Example
-- Find the average of money equivalent to each type of deposit.

Select Avg(Acc.Avail_Balance)  As Avg_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) As Avg_Avail_Balance
From   Account Acc
Where  Acc.Open_Branch_Id = 1
Group  By Acc.Cust_Id;
SQL MIN
The MIN() function returns the smallest value of the selected column.
-- Syntax:

SELECT MIN ("column_name")
FROM "table_name";
Example:
-- Find the minimum amount of deposit.
Select Min(Acc.Avail_Balance) As Min_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) As Min_Avail_Balance
From   Account Acc
Group  By Acc.Open_Branch_Id;
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.
-- 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
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:
-- 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;
Example:
-- 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;
SQL Server's own syntax:
-- Other SQL Server 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;
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:
-- 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;
Query results:
RIGHT OUTER JOIN (or RIGHT JOIN)
RIGHT OUTER JOIN is quite similar to LEFT OUTER JOIN:
FULL OUTER JOIN (or OUTER JOIN)
FULL OUTER JOIN is a combination of LEFT OUTER JOIN and RIGHT OUTER JOIN
-- Syntax:  (FULL OUTER JOIN)
-- Or: FULL JOIN

SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;

10. Subquery

In SQLServer, 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.
Subquery in the Where clause
Most often, the subquery will be found in the WHERE clause. These subqueries are also called nested subqueries.
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')
Subquery in the From clause
A subquery can also be found in the FROM clause. These are called inline views.
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;
Subquery in the Select clause
A subquery can also be found in the SELECT clause.
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.

11. SQL Server Transact-SQL Programming Tutorial

Next, you can see SQL ServerTransact-SQL Tutorial at:

Newest Articles

Show More