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!

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?

MODULE 8

MODULE 8

USE OF SELECT STATEMENT 2

A. Purpose

1. Students are able to use the Create Table instructions and options it uses PostgreSQL to create database tables as needed.

2. Students are able to insert records into the database tables.

B. Basic Theory

SELECT STATEMENTS used to determine or select the data that will be displayed when performing queries against the database. The structure of the select statements in PostgreSQL is as follows:

SELECT [ALL] | DISTINCT [ON (expression) [, ...])]]

* | Expression [AS output_name] [, ...]

[FROM from_item [, ...]]

[WHERE condition]

[GROUP BY expression [, ...]]

[HAVING condition [, ...]]

[{UNION | INTERSECT | EXCEPT} [ALL] select]

[ORDER BY expression [ASC | DESC | USING operator] [, ...]]

[FOR UPDATE [OF table name [, ...]]]

[LIMIT {count | ALL}]

[OFFSET start]

Example SELECT:

· To see all the columns of a table:

SELECT * FROM nasabah;

· To view the column (columns) specified:

Nama_nasabah SELECT FROM nasabah;

SELECT id_nasabah, nama_nasabah FROM customer;

· In general:

SELECT FROM;

• Column Alias ​​(USA)

· The U.S. is used to change the column name in the SELECT display.

Example:

- SELECT nama_nasabah U.S. "nasabah Name" FROM nasabah;

- SELECT nama_nasabah U.S. "nasabah", U.S. alamat_nasabah "Customer Address" FROM nasabah;

• WHERE 

· Used to restrict the SELECT results are displayed based on specified conditions. 

example: 

- SELECT FROM nasabah WHERE nama_nasabah nama_nasabah = 'Ali Cyclone'; 

- SELECT nama_nasabah, alamat_nasabah FROM nasabah WHERE id_nasabah = 2; 

· Can use>, <, <> (or! =),> =, <= 

· Use AND or OR for more than one condition: 

- SELECT * FROM nasabah WHERE nama_nasabah = 'Rina Marsudi' AND alamat_nasabah = 'Jl. Kusumanegara 30 '; 

- SELECT * FROM nasabah WHERE nama_nasabah = 'Ali Topan' OR id_nasabah = 2;

• Search NULL 

· Use the IS NULL to look for NULL: 

- SELECT * FROM rekening WHERE kode_cabang IS NULL; 

· Use IS NOT NULL to look for non-NULL: 

- SELECT * FROM rekening WHERE kode_cabang IS NOT NULL;

•  Search String

· Use LIKE to search for a particular string:
- SELECT * FROM nasabah WHERE nama_nasabah LIKE 'Ali Cyclone';
· Can use%:
- SELECT * FROM nasabah WHERE alamat_nasabah LIKE '% state%';
· Can use _ to 1 letter:
- SELECT * FROM nasabah WHERE nama_nasabah LIKE 'Ali T_p_n';
· To search case insensitive (do not care about uppercase or lowercase letters), use ILIKE:
- SELECT * FROM nasabah WHERE nama_nasabah ILIKE '% Marsudi';

•  ORDER BY

· Used to sort the results of the SELECT.
· To sort from small to large:
- SELECT * FROM nasabah ORDER BY nama_nasabah;
· To sort from big to small:
- SELECT * FROM nasabah ORDER BY DESC nama_nasabah;
· Caution: if there is a WHERE, ORDER BY placed after the WHERE.
· To make sorting more than one column, separated by commas:
- SELECT * FROM ORDER BY nasabah_has_rekening no_rekening, id_nasabah;
· Can specify DESC for columns (columns), for example:
- SELECT * FROM ORDER BY nasabah_has_rekening no_rekening, id_nasabah DESC;
- SELECT * FROM nasabah_has_rekening no_rekening ORDER BY DESC, id_nasabah;

• LIMIT and OFFSET

· Used to limit the number of rows displayed in the SELECT.
Example: Only display the first three lines:
- SELECT * FROM nasabah ORDER BY id_nasabah LIMIT 3;
Showing 2 lines after passing through the first 2 lines:
- SELECT * FROM nasabah ORDER BY LIMIT 2 OFFSET id_nasabah 2;
· Caution: use LIMIT should always be used in conjunction with ORDER BY, so the sequence shown will always be consistent.
LIMIT and OFFSET · extremely useful in web-based display (via a web browser using PHP or JSP) to display the data is not too large and could be neat. Display a lot of data can be arranged and divided into multiple pages (pages).

•  TABLE JOIN

Kinds of join table:
· Cross Join
- Combining all the records from the first table with all the records in the second table.
- The number of records from a cross join = number of table records the first x number of records of the second table.
Example:
 SELECT * FROM CROSS JOIN cabang_bank account;

INNER JOIN
- Combining two (or more) tables based on attribute liaison.
- Method 1:

•  SELECT * FROM rekening cabang_bank INNER JOIN USING (kode_cabang);

- Method 2:

•  SELECT * FROM INNER JOIN rekening ON cabang_bank rekening.kode_cabang = cabang_bank.kode_cabang;

- Method 3:

•  SELECT * FROM rekening  NATURAL INNER JOIN cabang_bank;

- Method 4:

• SELECT * FROM rekening   cabang_bank WHERE rekening.kode_cabang = cabang_bank.kode_cabang;

- Caution: for INNER JOIN, we can eliminate the word 'INNER'. So, enough with the word 'JOIN' alone.
- The method of 4, if you want to display kolo in more than 2 tables, it must determine which table you want.
Example:

•  SELECT nasabah.id_nasabah, nama_nasabah, no_rekening FROM nasabah WHERE nasabah_has_rekening nasabah.id_nasabah = nasabah_has_rekening.id_nasabah;

Table Alias
- For ease of writing SQL, we can create a table alias.
Example:

•  customers SELECT * FROM A, B WHERE nasabah_has_rekening A.id_nasabah = B.id_nasabah;
•  SELECT * FROM A.id_nasabah, nama_nasabah, no_rekening nasabah FROM A, B WHERE nasabah_has_rekening A.id_nasabah = B.id_nasabah;

INNER JOIN

- Combining two (or more) tables based on attribute liaison.

- Method 1:

•  SELECT * FROM rekening cabang_bank INNER JOIN USING (kode_cabang);

- Method 2:

•  SELECT * FROM INNER JOIN accounts ON cabang_bank rekening.kode_cabang = cabang_bank.kode_cabang;

- Method 3:

•  SELECT * FROM NATURAL INNER JOIN account cabang_bank;

- Method 4:

•  SELECT * FROM accounts, cabang_bank WHERE rekening.kode_cabang = cabang_bank.kode_cabang;

- Caution: for INNER JOIN, we can eliminate the word 'INNER'. So, enough with the word 'JOIN' alone.

- The method of 4, if you want to display kolo in more than 2 tables, it must determine which table you want.

Example:

•  SELECT nasabah.id_nasabah, nama_nasabah, no_rekening FROM customers WHERE nasabah_has_rekening nasabah.id_nasabah = nasabah_has_rekening.id_nasabah;

Table Alias

- For ease of writing SQL, we can create a table alias.

Example:

•  customers SELECT * FROM A, B WHERE nasabah_has_rekening A.id_nasabah = B.id_nasabah;
•  SELECT * FROM A.id_nasabah, nama_nasabah, no_rekening customer FROM A, B WHERE nasabah_has_rekening A.id_nasabah = B.id_nasabah;

Distinct

- In a join table, sometimes there is information that is repeated. To eliminate repetition, use DISTINCT.

Example:

ü SELECT DISTINCT nama_nasabah, alamat_nasabah FROM customers NATURAL JOIN nasabah_has_rekening;

- Note the difference with the following:

ü nama_nasabah SELECT, FROM customers NATURAL JOIN alamat_nasabah nasabah_has_rekening;

· Right Outer Join

- Displays the results of the join table first (left side) with the second table (right side), as well as all the records in the second table (right side / right):

ü SELECT * FROM NATURAL LEFT OUTER JOIN account cabang_bank;

- The first three methods that have been mentioned to also apply for a INNER JOIN RIGHT OUTER JOIN, using USING, ON, or NATURAL.

· Left Outer Join

- Displays the results of the join table first (left side) with the second table (right side), as well as all the records in the first table (left side / left):

ü SELECT * FROM NATURAL LEFT OUTER JOIN account cabang_bank;

- The three methods that have been mentioned for the RIGHT OUTER JOIN is also true for LEFT OUTER JOIN, using USING, ON, or NATURAL.

· Full Outer Join

- Showing first join table with a second table, as well as all the records in the table:

ü SELECT * FROM NATURAL FULL OUTER JOIN account cabang_bank;

- The three methods that have been mentioned for the LEFT / RIGHT OUTER JOIN also applies to FULL OUTER JOIN, using USING, ON, or NATURAL.

· Outer Join

- For the LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN, can eliminate the word 'OUTER'. So, simply use the LEFT JOIN, RIGHT JOIN, or FULL JOIN alone.INNER JOIN vs. Outer Join

· In the INNER JOIN: shown is simply the result of a join table is successful, ie all records related in both tables are combined.

· In Outer Join: besides showing the results of the INNER JOIN, OUTER JOIN also displays all the records that are not related in both tables are combined.

v Multiple Joins

· For more than 2 tables, living alone Joinnya forwarded. For example:

- SELECT * FROM customers NATURAL JOIN NATURAL JOIN nasabah_has_rekening account;

· Another way:

- SELECT * FROM customers A, nasabah_has_rekening B, C accounts where A.id_nasabah = B.id_nasabah AND B.no_rekening = C.no_rekening;

· If you do join multiple (more than 2 tables), should pay attention to the order of the join. The order of the join table need to follow the relationship shown in the ER diagram.

· Therefore, we recommend using the ER diagram in order to generate a join table is correct.

C. Equipment and Materials

1. Computer with Windows 7 operating system.

2. PostgreSQL application program.

3. Modules Practical Database Systems.

D. Step Work 

1. Show customer name, account number, pin, and balances for all accounts the balance amount is more than USD 1,000,000 and sorted by account number with the following code: 

• SELECT nasabah.nama_nasabah, rekening.no_rekening, rekening.pin, rekening.saldo FROM accounts, customers, nasabah_has_rekening AND WHERE nasabah.id_nasabah = nasabah_has_rekening.id_rekeningFK nasabah_has_rekening.no_rekeningFK AND rekening.saldo rekening.no_rekening => 1000000 ORDER BY rekening.no_rekening ;
  
  2. View account number, pin and balances for all accounts at Bank Ruth Klaten unit with a maximum balance of USD 1,000,000 and sorted by account number with the following code: 
  ü SELECT rekening.no_rekening, rekening.pin, rekening.saldo account FROM, WHERE cabang_bank cabang_bank.nama_cabang = 'Bank of Ruth unit Klaten' AND rekening.kode_cabangFK = cabang_bank.kode_cabang AND rekening.saldo <= 1000000 ORDER BY rekening.no_rekening;
  
  3. Show customer name, account number, and the name of the bank branches for all customers who have opened an account at a bank branch and sorted by the name of the bank branch and customer name with the following code: 
  ü SELECT nasabah.nama_nasabah, rekening.no_rekening, cabang_bank.nama_cabang FROM customer, account, cabang_bank, nasabah_has_rekening AND WHERE cabang_bank.kode_cabang = rekening.kode_cabangFK nasabah_has_rekening.no_rekeningFK = rekening.no_rekeningFK AND ORDER BY nasabah.id_nasabah = nasabah_has_rekening.id_nasabahFK cabang_bank.nama_bank , nasabah.nama_nasabah;
  
  4. Show customer name and address of the customer for all customers who have an account at Bank of Ruth Units Yogyakarta and sorted by customer name with the following code: 
  ü nasabah.nama_nasabah SELECT, FROM nasabah.alamat_nasabah customers, accounts, cabang_bank, nasabah_has_rekening WHERE cabang_bank.nama_cabang = 'Bank Yogyakarta Ruth unit' AND cabang_bank.kode_cabang = rekening.kode_cabangFK AND nasabah_has_rekening.no_rekeningFK = rekening.no_rekening AND nasabah.id_nasabah = nasabah_has_rekening . id_nasabahFK ORDER BY nasabah.nama_nasabah;
  
  5. Show customer name, customer address, account number, and balance for all customers and all accounts that exist in the database and sorted by customer name and account number with the following code: 
  ü SELECT nasabah.nama_nasabah, nasabah.alamat_nasabah, rekening.no_rekening, rekening.saldo FROM customer, account, nasabah_has_rekening AND WHERE nasabah_has_rekening.no_rekeningFK = rekening.no_rekening nasabah.id_nasabah = nasabah.id_nasabahFK ORDER BY nasabah.nama_nasabah, rekening.no_rekening;
  
  E. Analysis 
  From the above experiment I can conclude that we can display data according to our needs by using the appropriate command. To display data from two different tables we need to see what is contained in the relation between the two tables. Thus we can determine the attributes connecting to then be set as in the experiment. 
  F. Duties 
  1. View account numbers, customer names, transaction types, and transaction amount where the transaction julah = Rp 20,000! 
  Answer:
  
  2. View account number, customer name and address of the customer where the customer's name begins with the word 'Su'! 
  Answer:
  
  3. View account number with the alias 'Account Number', the name of the customer with the alias 'Customer Name', the number of transactions with the alias 'Total Transactions' in which the type of transaction is a debit! Sort by name clients! 
  Answer:
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

MODULE 7

MODULE 7

USE OF SELECT STATEMENT 1

A. Purpose

1. Students are able to use the Create Table instructions and options it uses PostgreSQL to create database tables as needed.

2. Students are able to insert records into database tables

B. Basic Theory

SELECT STATEMENTS used to determine or select the data that will be displayed when performing queries against the database. The structure of the select statements in PostgreSQL is as follows:

SELECT [ALL] | DISTINCT [ON (expression) [, ...] ) ] ]

*| expression [ AS output_name ] [, ...]

[ FROM from_item [, ...] ]

[ WHERE condition ]

[ GROUP BY expression [, ...] ]

[ HAVING condition [, ...] ]

[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]

[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]

[ FOR UPDATE [ OF tablename [, ...] ] ]

[ LIMIT { count | ALL } ]

[ OFFSET start ]

Example SELECT:

· To see all the columns of a table:

SELECT * FROM customer;

· To view a specific column:

Nama_nasabah SELECT FROM customer;

SELECT id_nasabah, nama_nasabah FROM customer;

· In general:

SELECT FROM;

 Column Alias (AS)

• The U.S. is used to change the column name in the SELECT display.

example:

-SELECT nama_nasabah AS “Nama Nasabah” FROM nasabah;

-SELECT nama_nasabah AS “Nasabah”, alamat_nasabah AS “Alamat Nasabah” FROM nasabah;

WHERE

• Used to restrict the SELECT results are displayed based on specified conditions.

example

-  SELECT nama_nasabah FROM nasabah WHERE nama_nasabah=’Ali Topan’ ;

-          SELECT nama_nasabah, alamat_nasabah FROM nasabah WHERE id_nasabah=2;

· Can use>, <, <> (or! =),> =, <=

· Use AND or OR for more than one condition:

-          SELECT* FROM nasabah WHERE nama_nasabah = ‘Rina Marsudi’ AND alamat_nasabah = ‘Jl. Kusumanegara 30’;

-          SELECT* FROM nasabah WHERE nama_nasabah = ‘Ali Topan’ OR id_nasabah=2;

  Search NULL

Use the IS NULL to look for NULL :

- SELECT * FROM rekening WHERE kode_cabang IS NULL ;

· Use IS NOT NULL to look for non- NULL :

- SELECT * FROM rekening WHERE kode_cabang IS NOT NULL ;

v Search String

· Use LIKE to search for a particular string :

- SELECT * FROM nasabah WHERE nama_nasabah LIKE ' Ali Topan ' ;

· Can use % :

- SELECT * FROM nasabah WHERE alamat_nasabah LIKE ' % negara % ' ;

· Can use _ to 1 letter :

- SELECT * FROM nasabah WHERE nama_nasabah LIKE ' Ali T_p_n ' ;

· To search case insensitive ( do not care about uppercase or lowercase letters ) , use ILIKE :

- SELECT * FROM nasabah WHERE nama_nasabah ILIKE ' % Marsudi ' ;

v ORDER BY

· Used to sort the results of the SELECT .

· To sort from small to large :

- SELECT * FROM nasabah ORDER BY nama_nasabah ;

· To sort from big to small :

- SELECT * FROM nasabah ORDER BY DESC nama_nasabah ;

· Caution : if there is a WHERE , ORDER BY is placed after the WHERE .

· To make sorting more than one column , separated by commas :

- SELECT * FROM ORDER BY nasabah_has_rekening no_rekening , id_nasabah ;

· Can specify DESC to columns ( columns ) , for example :

- SELECT * FROM ORDER BY nasabah_has_rekening no_rekening , id_nasabah DESC ;

- SELECT * FROM nasabah_has_rekening no_rekening ORDER BY DESC , id_nasabah

v LIMIT and OFFSET

· Used to limit the number of rows displayed in the SELECT .

Example : Only display the first three lines :

- SELECT * FROM nasabah ORDER BY id_nasabah LIMIT 3 ;

Showing 2 lines after passing through the first 2 lines :

- SELECT * FROM nasabah ORDER BY LIMIT 2 OFFSET id_nasabah 2 ;

· Caution : use LIMIT should always be used in conjunction with ORDER BY , so the sequence shown will always be consistent .

LIMIT and OFFSET · extremely useful in web -based display ( via a web browser using PHP or JSP ) to display the data is not too large and could be neat . Display a lot of data can be arranged and divided into multiple pages ( pages) .

v TABLE JOIN

Kinds of join table :

· Cross Join

- Combining all the records from the first table with all the records in the second table .

- The number of records from a cross join = number of table records the first x number of records of the second table .

example :

ü SELECT * FROM CROSS JOIN cabang_bank account ;

· Inner Join

- Combining two ( or more ) tables based on attribute liaison .

- Method 1 :

ü SELECT * FROM rekening cabang_bank INNER JOIN USING ( kode_cabang ) ;

- Method 2 :

ü SELECT * FROM INNER JOIN rekening ON cabang_bank rekening.kode_cabang = cabang_bank.kode_cabang ;

- Method 3 :

ü SELECT * FROM NATURAL INNER JOIN rekening cabang_bank ;

- Method 4 :

ü SELECT * FROM rekening , cabang_bank WHERE rekening.kode_cabang = cabang_bank.kode_cabang ;

- Caution : for INNER JOIN , we can eliminate the word ' INNER ' . So , enough with the word ' JOIN ' alone .

- With Method 4 , if you want to display coloumn in more than 2 tables , it must determine which table you want .

example :

ü SELECT nasabah.id_nasabah , nama_nasabah , no_rekening FROM nasabah WHERE nasabah_has_rekening nasabah.id_nasabah = nasabah_has_rekening.id_nasabah ;

Table Alias

- For ease of writing SQL , we can make a table alias .

example :

ü  SELECT * FROM nasabah A, nasabah_has_rekening B WHERE A.id_nasabah = B.id_nasabah ;

ü SELECT * FROM A.id_nasabah , nama_nasabah , no_rekening  FROM  nasabah A ,  nasabah_has_rekening B WHERE A.id_nasabah = B.id_nasabah ;

Distinct

- In a join table , sometimes there is information that is repeated . To eliminate repetition , use DISTINCT .

example :

• SELECT DISTINCT nama_nasabah , alamat_nasabah FROM nasabah NATURAL JOIN nasabah_has_rekening ;
• Note the difference with the following :
• SELECT nama_nasabah , FROM nasabah NATURAL JOIN alamat_nasabah nasabah_has_rekening ;
• · Right Outer Join
• - Displays the results of the join table first ( left side ) with the second table ( right side ) , as well as all the records in the second table ( right side / right ) :
• ü SELECT * FROM rekening NATURAL LEFT OUTER JOIN cabang_bank ;

- The first three methods that have been mentioned to also apply for a INNER JOIN RIGHT

OUTER JOIN , using USING , ON , or NATURAL .

· Left Outer Join

- Displays the results of the join table first ( left side ) with the second table ( right side ) , as well as all the records in the first table ( left side / left ) :

ü SELECT * FROM rekening NATURAL LEFT OUTER JOIN cabang_bank ;

- The three methods that have been mentioned for the RIGHT OUTER JOIN is also true for LEFT OUTER JOIN , using USING , ON , or NATURAL .

· Full Outer Join

- Showing join the first table with a second table , as well as all the records in the table :

ü SELECT * FROM rekening NATURAL FULL OUTER JOIN  cabang_bank ;

- The three methods that have been mentioned for the LEFT / RIGHT OUTER JOIN also applies to FULL OUTER JOIN , using USING , ON , or NATURAL .

· Outer Join

- For the LEFT OUTER JOIN , RIGHT OUTER JOIN , and FULL OUTER JOIN , can eliminate the word ' OUTER ' . So , simply use the LEFT JOIN , RIGHT JOIN , or FULL JOIN alone .

v INNER JOIN vs . Outer Join

· In the INNER JOIN : shown is simply the result of a join table is successful , that is, all records relating in both tables are combined .

· In Outer Join : besides showing the results of the INNER JOIN , OUTER JOIN also displays all the records that are not related in both tables are combined .

v Multiple Joins

· For more than 2 tables , living alone Joinnya forwarded . For example :

· Another way :

ELECT* FROM nasabah A, nasabah_has_rekening B, rekening C where A.id_nasabah = B.id_nasabah AND B.no_rekening = C.no_rekening;

- SELECT * FROM nasabah A , nasabah_has_rekening B ,  rekening C where A.id_nasabah = B.id_nasabah AND B.no_rekening = C.no_rekening ;

· If you do join multiple ( more than 2 tables ) , should pay attention to the order of the join . The order of the join table need to follow the relationship shown in the ER diagram .

· Therefore , we recommend using the ER diagram in order to generate a join table is correct .

C. Equipment and Materials

1. Computer with Windows 7 operating system.

2. PostgreSQL application program.

3. Modules Practical Database Systems.

D. Step Work

1. PostgreSQL Run the program and perform commands  connect with databases created on 4 modules namely perbankan.

2. View nama_cabang and alamat_cabang of the bank for all branches of the bank and sorted by the name of the bank with the following code:

3. View account n0_rekening, pin, and saldo for all accounts and sorted by the amount of the balance of the biggest to the smallest with the following code:

4. View no_rekening, nama_nasabah, and alamat_nasabah of all customers who have an account and sorted by customer name with the following code:

5. View account number, customer name, and the balance for all accounts held by the customer and sorted by customer name with the following code:

•  SELECT rekening.no_rekening, nasabah.nama_nasabah, rekening.saldo FROM accounts, customers, nasabah_has_rekening AND WHERE nasabah.id_nasabah = nasabah_has_rekening.id_nasabahFK rekening.no_rekening = nasabah_has_rekening.no_rekeningFK ORDER BY nasabah.nama_nasabah;
• 

E. Analysis

From the above experiment I can conclude that we can display data according to our needs by using the appropriate command. To display data from two different tables we need to see what is contained in the relation between the two tables. Thus we can determine the attributes connecting to then be set as in the experiment.

F. Duties

1. Show customer name, customer address, transaction type and transaction amount where the transaction was a loan type and sorted by customer name!

2. View account numbers, customer names, transaction types and number of transactions to a transaction on November 21, 2009 and sorted by customer name!

Answer: