Mencari nilai max dalam sebuah data pada oracle menggunakan % Type

Mencari nilai max dalam sebuah data pada oracle menggunakan %Type
Contoh: Mencari gaji player max dgn % TYPE
Langkah-langkah:
//membuat table ‘club’
SQL> create table club(
2 id_club varchar(8),
3 nama_club varchar(20),
4 constraint pk_id_club primary key(id_club));
Table created.

//membuat table pemain
SQL> create table player(
2 id_player varchar(8),
3 nama_player varchar(12),
4 salary numeric(10),
5 id_club varchar(10),
6 constraint pk_id_player primary key(id_player),
7 constraint fk_id_club foreign key (id_club) references club(id_club));
Table created.

//mengisi table club
SQL> insert into club values(‘00001′,’AC MILAN’);
1 row created.
SQL> insert into club values(‘00002′,’ARGENTINA’);
1 row created.
SQL> insert into club values(‘00003′,’ARSENAL’);
1 row created.
SQL> insert into club values(‘00004′,’LIVERPOOL’);
1 row created.

//mengisi table pemain
SQL> insert into player values(‘01001′,’Messi’,’50000000′,’00001′);
1 row created.
SQL> insert into player values(‘01002′,’David’,’4000000′,’00001′);
1 row created.
SQL> insert into player values(‘02001′,’Morinho’,’30000000′,’00002′);
1 row created.
SQL> insert into player values(‘02002′,’Archuleta’,’50000000′,’00002′);
1 row created.
SQL> insert into player values(‘03001′,’Jason’,’35000000′,’00003′);
1 row created.
SQL> insert into player values(‘03002′,’Mraz’,’15000000′,’00003′);
1 row created.
SQL> insert into player values(‘04001′,’Morrison’,’10000000′,’00004′);
1 row created.
SQL> insert into player values(‘04002′,’Zelpha’,’20000000′,’00004′);
1 row created.

//menampilkan isi tabel pemain
SQL> select * from player;

//menampilkan isi tabel club
SQL> select * from club;

//perintah untuk menampilkan hasil
SQL> SET SERVEROUTPUT ON;

//menampilkan gaji max
SQL> declare
2 salaryplayer player.salary%TYPE;
3 BEGIN
4 select max(salary) into salaryplayer from player;
5 dbms_output.enable;
6 dbms_output.put_line(‘Salary Max: ‘||salaryplayer);
7 end;
8 /
Salary Max: 50000000
PL/SQL procedure successfully completed.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: