Module 9

MODULE 9

ADVANCE SQL 1

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. Run the program PostgreSQL 9.3> pgAdmin III and commit command to connect to the database created in module 4 is Banking.
2. View transaction date, transaction type, and the number of transactions for all transactions made by Sutopo and Canka Lokananta and sorted by the date of the transaction with the following code:
Ø SELECT transaksi.tanggal, transaksi.jenis_transaksi, transaksi.jumlah FROM customers WHERE transaction nasabah.id_nasabah = transaksi.id_nasabahFK nasabah.nama_nasabah AND IN ('Sutopo', 'Canka Lokananta') ORDER BY transaksi.tanggal;

3. View transaction date, name of customer, type of transaction, and the transaction amount for all transactions that occur from 15 November until 20 November 2009 and sorted by the date of the transaction and the name of the customer with the following code:

Ø SELECT transaksi.tanggal, nasabah.nama_nasabah, transaksi.jenis_transaksi, transaksi.jumlah FROM customers, transactions transaksi.tanggal WHERE BETWEEN '2009-11-15 'AND '2009-11-20' ORDER AND nasabah.id_nasabah = transaksi.id_nasabahFK BY transaksi.tanggal, nasabah.nama_nasabah;

4. View the types of transactions and the total number of transactions (in dollars) for each transaction type and sorted by the type of transaction with the following code: 

Ø transaksi.jenis_transaksi AS SELECT "Transaction Type", SUM (amount) AS "Total (USD)" FROM transactions GROUP BY ORDER BY transaksi.jenis_transaksi transaksi.jenis_transaksi;

5. Show type of transaction, the largest number of transactions and the smallest for each transaction type and sorted by the type of transaction with the following code: 

Ø jenis_transaksi AS SELECT "Transaction Type", MAX (amount) AS "Largest Transaction", MIN (amount) AS "Transaction Smallest" FROM transactions GROUP BY ORDER BY transaksi.jenis_transaksi transaksi.jenis_transaksi;

6. View the types of transactions, total transaction amount (in dollars), and the number of transactions recorded for each type of transaction that occurred prior to December 2009 and sorted by the type of transaction with the following code: 

Ø jenis_transaksi AS SELECT "Transaction Type", SUM (amount) AS "Total (USD)", COUNT (amount) AS "Number of Transactions" FROM transactions WHERE date between '2009-11-1 'AND '2009-11-30' GROUP BY ORDER BY transaksi.jenis_transaksi transaksi.jenis_transaksi;

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. View the types of transactions, number of transactions and total transactions in USD to customers who named the suffix 'Kartika Padmasari' for each type of transaction!

2. How many total balance owned by Maryati?