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