MODUL 10

Module 10

MODULE 10

ADVANCE SQL 2

A. Purpose
Students are able to use select statements along with its options to search, process, and display data in the database according to his needs.
B. Basic Theory
v IN
Example:
· SELECT * FROM accounts WHERE kode_cabang IN ('BRUM', 'BRUL');
v NOT IN
Example:
· SELECT * FROM accounts WHERE kode_cabang NOT IN ('BRUS', 'BRUM');
v BETWEEN
Example:
· SELECT * FROM accounts WHERE BETWEEN 500000 AND 1000000 balances;
v NOT BETWEEN
Example:
· SELECT * FROM WHERE account balance NOT BETWEEN 500000 AND 1000000;
v AGGREGATE FUNCTIONS
For aggregate functions:
· MIN ()
· Used to search for the smallest value of a set of records.
Example:
Ø SELECT MIN (balance) FROM account;
· MAX ()
· Used to seek the greatest value of a set of records.
Example:
Ø SELECT MAX (balance) FROM account;
· COUNT ()
· Used to calculate the number of records.
Example:
Ø SELECT COUNT (nama_nasabah) FROM customer;
· SUM ()
· Used to add up the values ​​of a set of records.
Example:
Ø SELECT SUM (balance) FROM account;
· AVG ()
· Used to calculate the average value of a set of records.
Example:
Ø SELECT AVG (balance) FROM account;
v GROUP BY
· Used to group a set of records based on (the columns) specific.
Example:
- SELECT FROM transactions GROUP BY jenis_transaksi jenis_transaksi;
HAVING v
· It is a pair of GROUP BY, used to restrict groups displayed:
- SELECT jenis_transaksi, date FROM jenis_transaksi transactions, GROUP BY, HAVING jenis_transaksi date = 'credit';
v GROUP BY and AGGREGATE
· GROUP BY is suitable for aggregate functions. By using GROUP BY, we can classify the records and calculate min, max, count, sum and avg for each group.
Example:
- SELECT kode_cabang, MIN (balance), MAX (balance), COUNT (*), SUM (balance), AVG (balance) FROM account GROUP BY kode_cabang;

C. Equipment and Materials
1. Computer with Windows 7 operating system.
2. PostgreSQL 9.3 application program.
3. Modules Practical Database Systems.
D. Step Work
1. Show the customer's name, type of transaction, and the average transaction amount (in dollars) for each customer who has conducted transactions and sorted by the average number of transactions with the following code:
Ø nasabah.nama_nasabah AS SELECT "Customer Name", U.S. transaksi.jenis_transaksi "Transaction Type", AVG (transaksi.jumlah) AS "Average (IDR) 'FROM transactions, customer WHERE GROUP BY nasabah.id_nasabah = transaksi.id_nasabahFK customers . nama_nasabah, transaksi.jenis_transaksi ORDER BY "Average (IDR)";

2. Show the customer's name, type of transaction, amount of transaction, and the total number of transactions (in dollars) for each customer who has made a debit transaction in December 2009 and sorted by the total number of transactions from largest to smallest with the following code: 

Ø nasabah.nama_nasabah AS SELECT "Customer Name", U.S. transaksi.jenis_transaksi "Transaction Type", COUNT (transaksi.jumlah) AS "Number of Transactions", SUM (transaksi.jumlah) AS "Total (USD)" FROM transactions, customer WHERE nasabah.id_nasabah = transaksi.id_nasabahFK nasabah.nama_nasabah GROUP BY, ORDER BY transaksi.jenis_transaksi "Number of Transactions" DESC;

3. View bank branch name, type of transaction, the total number of transactions (in dollars), and the number of transactions that have been served by each branch of the bank and the bank branch sorted by name and type of transaction with the following code: 

Ø cabang_bank.nama_cabang AS SELECT "Branch Name", U.S. transaksi.jenis_transaksi "Transaction Type", SUM (transaksi.jumlah) AS "Total (USD)", COUNT (transaksi.jumlah) AS "Number of Transactions" FROM transactions, cabang_bank, cabang_bank.kode_cabang WHERE account = rekening.kode_cabangFK AND rekening.no_rekening = transaksi.no_rekeningFK cabang_bank.nama_cabang GROUP BY, ORDER BY transaksi.jenis_transaksi cabang_bank.nama_cabang, transaksi.jenis_transaksi;

4. View bank branch name, type of transaction, and the average transaction amount (in dollars) for all transactions which have an average number of transactions over USD 100,000 and sorted by branch name tires with the following code: 

Ø cabang_bank.nama_cabang AS SELECT "Branch Name", U.S. transaksi.jenis_transaksi "Transaction Type", AVG (transaksi.jumlah) AS "Average (IDR) 'FROM transactions, cabang_bank, GROUP BY cabang_bank.nama_cabang accounts, transaksi.jenis_transaksi , rekening.kode_cabangFK, cabang_bank.kode_cabang, transaksi.no_rekeningFK, rekening.no_rekening AND HAVING rekening.kode_cabangFK = cabang_bank.kode_cabang transaksi.no_rekeningFK = rekening.no_rekening AND AVG (transaksi.jumlah)> 100000 ORDER BY cabang_bank.nama_cabang;

5. Show customer name and number of transactions which have been serviced by Bank of Ruth unit Surakarta from 15 November 2009 until December 1, 2009 and sorted by customer name with the following code: 

Ø nasabah.nama_nasabah AS SELECT "Customer Name", COUNT (transaksi.jumlah) AS "Number of Transactions" FROM transactions, cabang_bank, accounts, customer WHERE AND rekening.kode_cabangFK = cabang_bank.kode_cabang transaksi.no_rekeningFK = rekening.no_rekening AND transaksi.id_nasabahFK = nasabah.id_nasabah transaksi.tanggal AND BETWEEN '2009-11-15 'AND '2009-12-1' AND cabang_bank.nama_cabang = 'Bank of Ruth unit Surakarta' GROUP BY ORDER BY nasabah.nama_nasabah nasabah.nama_nasabah;

E. Analysis 

From the above practice, I can pull the conclusion that for certain records show there are several alternatives command used. So, we can choose the command which will be used depending on the needs and effectiveness. 

F. Duties 

1. Display number of transactions handled by each bank branch!

2. Show customer name and balance amount has a balance between Rp 500,000 to Rp 2,000,000!

3. Show customer name, transaction date, and the number of transactions in USD where the number of transactions over USD 100,000 and sort by the number of transactions from large to small!