MODUL 9

A. Purpose 
Students are able to use select statement and its options-a to find, process, and display data in the database as needed.

B. Basis Theory 
· IN 

• Example 

- SELECT * FROM accounts WHERE kode_cabang IN ('BRUM', 'BRUL');

•  SQL command above is equivalent to: 

- SELECT * FROM accounts WHERE kode_cabang = 'BRUM' OR kode_cabang = 'BRUL';
 There is no limit to the number of values ​​in the IN (....)

· NOT IN 

•  Example 

- SELECT * FROM accounts WHERE kode_cabang NOT IN ('BRUS', 'BRUL')
 NULL value will not appear in the IN and NOT IN.

· BETWEEN 

•  Example 

- SELECT * FROM accounts WHERE BETWEEN 500000 AND 1000000 balances;
 first BETWEEN value must be smaller than the second value.

· NOT BETWEEN 

•  Example 

- SELECT * FROM WHERE account balance NOT BETWEEN 500000 AND 1000000;

· AGGREGATE FUNCTION 
For aggregate functions:
 MIN () 
- Used to search for the smallest value of a set of records.

•  Example 

- SELECT MIN (balance) FROM account;

•  Can be limited with the WHERE clause so that only certain records are traced: 

- SELECT MIN (Balance) FROM accounts WHERE kode_cabang = 'BRUS';
 MAX () 
- Used to seek the greatest value of a set of records.

•  Example 

- SELECT MAX (balance) FROM account;

•  Can be limited with the WHERE clause: 

- SELECT MAX (Balance) FROM accounts WHERE kode_cabang = 'BRUS';
 COUNT () 

•  Used to calculate the number of records. 

 Example
- SELECT COUNT (*) FROM customer;
- SELECT COUNT (nama_nasabah) FROM customer;
- SELECT COUNT (alamat_nasabah) FROM customer;

•  Can be limited with the WHERE clause. 
•  If we want to count unique records (no repetition) then use DISTINCT. 

- SELECT COUNT (DISTINCT alamat_nasabah) ffrom the customer;
 SUM () 

•  Used for menjumlahakan the values ​​of a set of records. 

 Example
- SELECT SUM (balance) FROM account;

•  Can be limited with the WHERE clause. 

 AVG ()

•  to calculate the average value of a set of records. 

 Example
- SELECT AVG (balance) FROM account;

•  Can be limited with the WHERE clause. 
•  Some aggregate functions can be combined in a single SQL command: 

- SEELCT MIN (balance), MAX (balance), AVG (balance) FROM account;

•  Can use Column Alias ​​(USA) to create a more professional appearance. 

· 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 

•  It is a pair of GROUP BY, used to limit which is shown. 

 Example
- Jenis_transaksi SELECT, FROM transaction date jenis_transaksi GROUP BY, HAVING jenis_transaksi-date 'credit';

•  If using HAVING, then the restriction is done after grouped in the GROUP BY. 
•  If using WHERE, then the restriction is done before the results are grouped in the GROUP BY. 

· 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 to each group. 

- SELECT kode_cabang, MIN (balance), MAX (balance), COUNT (*), SUM (balance), AVG (balance) FROM account GROUP BY kode_cabang;

•  Can be combined with a join table and ORDER BY. 

- SELECT nama_cabang, SUM (balance) FROM account GROUP BY NATURAL JOIN cabang_bank nama_cabang ORDER BY nama_cabang;

C. Equipment and Materials 
1. Computer
2. PostgreeSQL 9.2 application program
3. Lab module database system

D. Practical Steps 
1. PostgreeSQL Running the program and reopen a database that has been created that is banking.
2. Banking Right-click on the database, click "restore", select the file database module 6 we have stored, and then click "restore".

3. Opens cmd with pins

4. Displays the date of the transaction, 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;

5. Displays the 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 transactions, customer 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.

6. Displays the type of transaction and the total number of transactions (in dollars) for each transaction type and sorted by the type of transaction with the following code:
- SELECT transaksi.jenis_transaksi U.S. "Transaction Type", SUM (amount) AS "Total (USD)" FROM transactions GROUP BY ORDER BY transaksi.jenis_transaksi transaksi.jenis_transaksi;

7. Displays the 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:
- SELECT jenis_transaksi U.S. "Transaction Type", MAX (amount) AS "Largest Transaction", MIN (amount) AS "Transaction Smallest" FROM transactions GROUP BY ORDER BY transaksi.jenis_transaksi transaksi.jenis_transaksi;

8. Displays the type of transaction, the total number of transactions (in dollars), and banayaknya transactions recorded for each type of transaction that occurred before December and sorted by the type of transaction with the following code:
- SELECT jenis_transaksi U.S. "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
In this experiment we can recognize and use more kinds of select statements along with its options, such as IN, NOT IN, BETWEEN, NOT BETWEEN, AGGREGATE FUNCTIONS (MIN, MAX. COUNT, SUM, AVG), GROUP BY, HAVING. The function of the select statement I have explained on the basis of the above theory.
GROUP BY is very suitable for the aggregate function. By using GROUP BY, we can classify the records and calculate min, max, count, sum, and avg to each group. GROUP BY and aggregate functions can also be combined with a join table and ORDER BY. All the select statement will take more easier for us to manage the database.

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?