TUGAS My SQL

My sql

Insert – insert

SOURCE CODE 

CREATE TABLE ii_brang_msk

( kode VARCHAR(10) NOT NULL,

nama VARCHAR(25),

tgl date,

PRIMARY KEY (kode))

CREATE TABLE ii_history

( kode VARCHAR(10) NOT NULL,

tgl date,

PRIMARY KEY (kode))

DELIMITER go

CREATE TRIGGER tr_ii

BEFORE INSERT ON ii_brang_msk

FOR EACH ROW BEGIN

INSERT INTO ii_history (kode,tgl)

VALUES (NEW.kode,NEW.tgl);

END;

go

DELIMITER ;

INSERT INTO II_BRANG_MSK VALUES ('AA001','RAM','2013-07-16')

INSERT INTO II_BRANG_MSK VALUES ('AA002','VGA','2013-07-18')

SELECT*FROM II_BRANG_MSK

SELECT*FROM II_HISTORY

Insert – update

SOURCE CODE

CREATE TABLE iu_suplai

( kode VARCHAR(5) NOT NULL,

nama VARCHAR(25),

jumlah int,

PRIMARY KEY (kode))

CREATE TABLE iu_stok

( kode VARCHAR(5) NOT NULL,

stok int,

PRIMARY KEY (kode))

DELIMITER go

CREATE TRIGGER tr_iu

after INSERT ON iu_suplai

FOR EACH ROW BEGIN

update iu_stok

SET STOK=stok+NEW.jumlah

WHERE Kode=NEW.kode;

END;

go

DELIMITER ;

INSERT INTO IU_STOK VALUES ('VG001','3')

INSERT INTO IU_STOK VALUES ('RM001','4')

SELECT* FROM IU_STOK

SELECT* FROM IU_SUPLAI

INSERT INTO IU_SUPLAI VALUES ('VG001','VGA','3')

Insert – delete

SOURCE CODE

CREATE TABLE id_dipinjam

( kode VARCHAR(5) NOT NULL,

nama VARCHAR(25),

PRIMARY KEY (kode))

CREATE TABLE id_kembali

( kode VARCHAR(5) NOT NULL,

nama VARCHAR(25),

PRIMARY KEY (kode))

DELIMITER go

CREATE TRIGGER tr_id

after INSERT ON id_kembali

FOR EACH ROW BEGIN

delete from id_dipinjam

WHERE kode=NEW.kode;

END;

go

DELIMITER ;

INSERT INTO ID_DIPINJAM VALUES ('GMOO1','PES 2014')

INSERT INTO ID_DIPINJAM VALUES ('GMOO2','GTA 5')

SELECT* FROM ID_DIPINJAM

SELECT* FROM ID_KEMBALI

INSERT INTO ID_KEMBALI VALUES ('GMOO2','GTA 5')

Update – insert

SOURCE CODE

CREATE TABLE ui_game

( kode VARCHAR(5) NOT NULL,

nama VARCHAR(25),

PRIMARY KEY (kode))

CREATE TABLE ui_backup

( kode VARCHAR(5) NOT NULL,

nama VARCHAR(25),

PRIMARY KEY (kode))

DELIMITER go

CREATE TRIGGER tr_ui

before update ON ui_game

FOR EACH ROW BEGIN

insert into ui_backup values (old.kode,old.nama);

END;

go

DELIMITER ;

INSERT INTO UI_GAME VALUES('GM001','FARCRY 3')

INSERT INTO UI_GAME VALUES('GM002','FIFA 2014')

SELECT * FROM UI_GAME

UPDATE UI_GAME

SET NAMA='FARCRY 4'

WHERE NAMA='FARCRY 3'

SELECT * FROM UI_BACKUP

Update – update

SOURCE CODE

CREATE TABLE uu_member

( id VARCHAR(5) NOT NULL,

nama VARCHAR(25),

alamat varchar(50),

PRIMARY KEY (id))

CREATE TABLE uu_pinjam

( id VARCHAR(5) NOT NULL,

nama VARCHAR(25),

alamat varchar(50),

nama_buku varchar(25),

PRIMARY KEY (id))

DELIMITER go

CREATE TRIGGER tr_uu

after update ON uu_member

FOR EACH ROW BEGIN

update uu_pinjam

set alamat=new.alamat

where id=new.id;

END;

go

DELIMITER ;

insert into uu_member values ('a01','hanin','graha mutiara')

insert into uu_pinjam values ('a01','hanin','graha mutiara','basis data')

update uu_member

set alamat='pondok jati'

where alamat='graha mutiara'

select* from uu_member

select* from uu_pinjam

Update – delete

SOURCE CODE

CREATE TABLE ud_game

( kode VARCHAR(5) NOT NULL,

nama VARCHAR(25),

stat varchar(6),

PRIMARY KEY (kode))

CREATE TABLE ud_keluar

( kode VARCHAR(5) NOT NULL,

id_peminjam VARCHAR(5),

PRIMARY KEY (kode))

DELIMITER go

CREATE TRIGGER tr_ud

after update ON ud_game

FOR EACH ROW BEGIN

delete from ud_keluar

where kode=new.kode;

END;

go

DELIMITER ;

insert into ud_game values ('a01','pes 2014','keluar')

insert into ud_game values ('a02','fifa 2014','keluar')

insert into ud_keluar values ('a01','001')

insert into ud_keluar values ('a02','200')

select* from ud_game

select* from ud_keluar

update ud_game

set stat='ada'

where kode='a01'

Delete – insert

SOURCE CODE

CREATE TABLE di_barang

( kode VARCHAR(5) NOT NULL,

nama VARCHAR(25),

harga int,

PRIMARY KEY (kode))

CREATE TABLE di_recycle

( kode VARCHAR(5) NOT NULL,

nama VARCHAR(25),

harga int,

PRIMARY KEY (kode))

DELIMITER go

CREATE TRIGGER tr_di

after delete ON di_barang

FOR EACH ROW BEGIN

insert into di_recycle values (old.kode, old.nama, old.harga);

END;

go

DELIMITER ;

insert into di_barang values ('a001','vga','1000000')

insert into di_barang values ('a02','ram','700000')

delete from di_barang where kode='a001'

select*from di_barang

select*from di_recycle

Delete – update

SOURCE CODE

CREATE TABLE du_daftar_brg_dijual

( kode VARCHAR(5) NOT NULL,

nama VARCHAR(25),

harga int,

PRIMARY KEY (kode))

CREATE TABLE du_status_brg

( kode VARCHAR(5) NOT NULL,

stat VARCHAR(8),

PRIMARY KEY (kode))

DELIMITER go

CREATE TRIGGER tr_du

after delete ON du_daftar_brg_dijual

FOR EACH ROW BEGIN

update du_status_brg set stat='sold out' where kode=old.kode;

END;

go

DELIMITER ;

insert into du_daftar_brg_dijual values ('a01','lemari','400000')

insert into du_daftar_brg_dijual values ('a02','meja','300000')

insert into du_status_brg values ('a01','dijual')

insert into du_status_brg values ('a02','dijual')

select*from du_daftar_brg_dijual

select*from du_status_brg

delete from du_daftar_brg_dijual where kode='a01'

Delete – delete

SOURCE CODE

CREATE TABLE dd_barang_stok

( kode VARCHAR(5) NOT NULL,

nama VARCHAR(25),

harga int,

PRIMARY KEY (kode))

CREATE TABLE dd_daftar_jual

( kode VARCHAR(5) NOT NULL,

nama VARCHAR(25),

harga int,

PRIMARY KEY (kode))

DELIMITER go

CREATE TRIGGER tr_dd

after delete ON dd_barang_stok

FOR EACH ROW BEGIN

delete from dd_daftar_jual where kode=old.kode;

END;

go

DELIMITER ;

insert into dd_barang_stok values('a01','vga','1000000')

insert into dd_barang_stok values('a02','power suply','900000')

insert into dd_daftar_jual values('a01','vga','1000000')

insert into dd_daftar_jual values('a02','power suply','900000')

delete from dd_barang_stok where kode='a01'

select* from dd_barang_stok

select* from dd_daftar_jual

Branching

SOURCE CODE

CREATE TABLE b_mhs

( npm CHAR(10) NOT NULL,

nama VARCHAR(25),

matkul varchar(25),

nilai_angka int,

PRIMARY KEY (npm))

CREATE TABLE b_nilai

( npm CHAR(10) NOT NULL,

matkul varchar(25),

nilai_abjad varchar(2),

PRIMARY KEY (npm))

DELIMITER go

CREATE TRIGGER tr_b

after insert ON b_mhs

FOR EACH ROW BEGIN

if new.nilai_angka <45 then

insert into b_nilai values (new.npm, new.matkul, 'E');

elseif new.nilai_angka between 45 and 55 then

insert into b_nilai values (new.npm, new.matkul, 'D');

elseif new.nilai_angka between 56 and 59 then

insert into b_nilai values (new.npm, new.matkul, 'C');

elseif new.nilai_angka between 60 and 63 then

insert into b_nilai values (new.npm, new.matkul, 'C+');

elseif new.nilai_angka between 64 and 67 then

insert into b_nilai values (new.npm, new.matkul, 'B-');

elseif new.nilai_angka between 68 and 71 then

insert into b_nilai values (new.npm, new.matkul, 'B');

elseif new.nilai_angka between 72 and 75 then

insert into b_nilai values (new.npm, new.matkul, 'B+');

elseif new.nilai_angka between 76 and 79 then

insert into b_nilai values (new.npm, new.matkul, 'A-');

elseif new.nilai_angka between 80 and 100 then

insert into b_nilai values (new.npm, new.matkul, 'A');

end if;

END;

go

DELIMITER ;

insert into b_mhs values ('1134010053', 'hanin', 'basis data', '80')

insert into b_mhs values ('1134010054', 'kukuh', 'basis data', '70')

select* from b_mhs

select* from b_nilai

Looping

SOURCE CODE

CREATE TABLE l_sewa

( id VARCHAR(5) NOT NULL,

nama VARCHAR(25),

no_hp varchar(14),

jam_sewa int

)

CREATE TABLE l_lapangan

( id VARCHAR(5) NOT NULL,

nama VARCHAR(25),

jam_ke int

)

DELIMITER go

CREATE TRIGGER tr_l

after insert ON l_sewa

FOR EACH ROW BEGIN

declare a int;

SET a=1;

WHILE a<=new.jam_sewa DO

insert into l_lapangan values (new.id,new.nama,a);

set a=a+1;

end while;

END;

go

DELIMITER ;

insert into l_sewa values ('a001', 'agung', '085123456789', '4')

select * from l_sewa

select * from l_lapangan