Die Anleitung zum JasperReport für den Anfänger mit SQL Server

-- 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 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;

-- 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;

Select distinct <column1>, <column2>  ....

-- 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;

-- 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 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;

-- 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;

-- 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');

-- 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;

-- 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) --
);

-- 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 '%-__-%';

-- 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..

-- 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;

-- 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;

-- 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;

-- Query Account table.

Select Acc.Account_Id
     ,Acc.Product_Cd
     ,Acc.Avail_Balance
     ,Acc.Pending_Balance
From   Account Acc;

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;

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;

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;

-- Syntax of Insert Into:

INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...);

-- 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
   );

-- Syntax of INSERT INTO .. SELECT

INSERT INTO "table1" ("column1", "column2", ...)
SELECT "column3", "column4", ...
FROM "table2";

-- 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';

-- 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 Account
Set    Avail_Balance   = Avail_Balance + 2 * Avail_Balance / 100
     ,Pending_Balance = Pending_Balance + 2 * Pending_Balance / 100
Where  Cust_Id = 1;

-- 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);

-- 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

Select Count(distinct txn.Account_id) as Distinct_Acc From Acc_Transaction txn;

-- 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;

-- Syntax:

SELECT SUM("column_name")
FROM "table_name";

-- 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;

-- Syntax:

SELECT AVG("column_name")
FROM "table_name";

-- 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;

-- Syntax:

SELECT MIN ("column_name")
FROM "table_name";

-- 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;

-- Syntax:

SELECT MAX("column_name")
FROM "table_name";

-- 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;

-- 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;

-- 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;

-- 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;

-- Syntax:  (FULL OUTER JOIN)
-- Or: FULL JOIN

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

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')

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;

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;