MODUL 7

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: