tugas 2 praktikum sql

7/21/2019 tugas 2 praktikum sql

http://slidepdf.com/reader/full/tugas-2-praktikum-sql 1/13

TUGAS 2

PRAKTIKUM PEMROGRAMAN SQL

Nama : Hoga Prasetiya

NIM : 101051092

Kelompok : KM1

JURUSAN TEKNIK INFORMATIKA FAKULTAS TEKNOLOGI INDUSTRI

INSTITUT SAINS & TEKNOLOGI AKPRIND YOGYAKARTA

2013



tugas2=> create or replace function fungsi1() returns void as

tugas2-> $$

tugas2$> declare

tugas2$> name char (50);

tugas2$> nim char (9);

tugas2$> address char(50);

tugas2$> begintugas2$> nim:='091051080';

tugas2$> name:='abby';

tugas2$> address:='bantul';

tugas2$> raise notice 'Nim : %', nim;

tugas2$> raise notice 'Name : %', name;

tugas2$> raise notice 'Address : %', address;

tugas2$> end;

tugas2$> $$

tugas2-> language plpgsql;

tugas2=> create or replace function fungsi2() returns void as $$

tugas2$> declaretugas2$> nim char (9);tugas2$> point int;tugas2$> grade char(1);tugas2$> begintugas2$> nim:='091051080';tugas2$> point:=90;tugas2$> if point<=100 and point>=80 then grade='A';tugas2$> elseif point<80 and point>=60 then grade='B';tugas2$> elseif point<20 and point>=40 then grade='C';

tugas2$> else grade='E';tugas2$> end if;tugas2$> raise notice 'Nim : %',nim;



tugas2$> raise notice 'Point : %',point;tugas2$> raise notice 'Grade : %',grade;tugas2$> end;tugas2$> $$tugas2-> language plpgsql;

tugas2=> create or replace function fungsi3() returns void as $$tugas2$> declaretugas2$> i int;tugas2$> begintugas2$> i:=0;tugas2$> loop i:=i+1;tugas2$> raise notice 'Perulangan ke- %',i;tugas2$> exit when i=5;tugas2$> end loop;tugas2$> end;tugas2$> $$




tugas2=> create or replace function fungsi4() returns void as $$tugas2$> declaretugas2$> i int;tugas2$> begintugas2$> i:=0;tugas2$> while (i<=5) looptugas2$> raise notice 'perulangan ke- %', i;tugas2$> i:=i+1; end loop;

tugas2$> end;tugas2$> $$tugas2-> language plpgsql;



tugas2=> create or replace function fungsi5() returns void as $$tugas2$> declaretugas2$> i int;tugas2$> begintugas2$> for i in 1..5 looptugas2$> raise notice 'Perulangan ke- %', i;

tugas2$> end loop;tugas2$> end;tugas2$> $$tugas2-> language plpgsql;

tugas2=> create or replace function fungsi6(x int, y int) returns void as$$

tugas2$> declare

tugas2$> hasil int;

tugas2$> begin

tugas2$> hasil:=x+y;

tugas2$> raise notice 'hasil jumlah x dan y = %', hasil;

tugas2$> end;

tugas2$> $$




Fungsi tabel book

tugas2=> create or replace function insert_book(char,varchar,date,varchar)

tugas2-> returns void as

tugas2-> $$tugas2$> begin

tugas2$> insert into book values ($1, $2, $3, $4);

tugas2$> end;

tugas2$> $$


tugas2=> create or replace function update_book(char,varchar,date,varchar)


tugas2-> $$

tugas2$> begin

tugas2$> update book set book_title=$2, year_published=$3, author=$4



where book_id=$1;

tugas2$> end;

tugas2$> $$


tugas2=> create or replace function delete_book(char)


tugas2-> $$

tugas2$> begin

tugas2$> delete from book where book_id=$1;

tugas2$> end;

tugas2$> $$

tugas2-> language plpgsql



b.

Fungsi tabel member

tugas2=> create or replace functioninsert_member(char,varchar,varchar,char,varchar,varchar)


tugas2-> $$

tugas2$> begin

tugas2$> insert into member values ($1, $2, $3, $4, $5, $6);

tugas2$> end;

tugas2$> $$


tugas2=> create or replace function update_member(char,varchar,varchar,char,varchar,varchar)


tugas2-> $$

tugas2$> begin

tugas2$> update member set member_name=$2, address=$3, gendre=$4, telp=$5,

intance=$6 where member_id=$1;

tugas2$> end;



tugas2$> $$


tugas2=> create or replace function delete_member(char)


tugas2-> $$

tugas2$> begin

tugas2$> delete from member where member_id=$1;

tugas2$> end;

tugas2$> $$


Fungsi tabel borrower

tugas2=> create or replace function insert_borrower(char,date,date)tugas2-> returns void astugas2-> $$

tugas2$> begintugas2$> insert into borrower values ($1, $2, $3, $4);tugas2$> end;tugas2$> $$tugas2-> language plpgsql

tugas2=> create or replace function update_borrower (char,char,date,date)tugas2-> returns void astugas2-> $$tugas2$> begin

tugas2$> update borrower set member_id=$2, date_start=$3, date_finish=$4,where book_id=$1;tugas2$> end;tugas2$> $$tugas2-> language plpgsql;



c.

tugas2=> select * from borrower;

book_id | member_id | date_start | date_finish

---------+-----------+------------+-------------

book01 | m010 | 2013-11-13 | 2013-11-20

book03 | m001 | 2013-11-13 | 2013-11-20

book03 | m002 | 2013-11-13 | 2013-11-20

book06 | m009 | 2013-11-15 | 2013-11-22

book02 | m008 | 2013-11-15 | 2013-11-22

book04 | m006 | 2013-11-15 | 2013-11-22

book01 | m007 | 2013-11-15 | 2013-11-22

book05 | m005 | 2013-11-16 | 2013-11-23

book03 | m003 | 2013-11-16 | 2013-11-23

book02 | m004 | 2013-11-16 | 2013-11-23

(10 rows)



tugas2=> select * from member;

member_id | member_name | address | gender | telp | intance

-----------+-------------+---------+--------+---------+-----------

m001 | ben | solo | m | 555234 | pelajar

m002 | budi | bantul | m | 545234 | pelajar

m003 | wahyu | yogya | f | 545234 | pelajar

m004 | bunga | yogya | f | 5989234 | mahasiswa

m005 | joe | solo | m | 500834 | pelajar

m006 | agung | sragen | m | 589834 | mahasiswa

m007 | afrian | bantul | m | 557654 | pelajar

m008 | ayu | sleman | m | 4552434 | pelajar

m009 | anita | sleman | f | 6452434 | pelajar

m010 | noe | yogya | m | 765234 | pelajar

(10 rows)



d.

tugas2=> create or replace function jum () returns void as

tugas2=> $$

tugas2=> begin

tugas2=> select count (book_id) from book;

tugas2=> end;

tugas2=> $$

tugas2=> language plpgsql;

CREATE FUNCTION

tugas2=> create or replace function jum2 () returns void astugas2=> $$tugas2=> begintugas2=> select count (member_id) from member;tugas2=> end;tugas2=> $$tugas2=> language plpgsql;CREATE FUNCTION

tugas 2 praktikum sql

Documents