Sintaks SQL Database Perusahaan

Syntax :

Membuat table manager:

create table manager(id_manager char(5) not null, nama varchar (25),no_telephon varchar(12),tgl_lahir varchar (20),alamat varchar(25),constraint pk_id_manager primary key(id_manager));

menbuat table cabang:

create table cabang(kd_cabang char(5) not null, id_manager char(5) not null, nm_cabang varchar (50), alamat varchar (50), constraint pk_kd_cabang primary key (kd_cabang), constraint fk_id_manager foreign key (id_manager) references manager (id_manager));

membuat table pegawai:

create table pegawai(id_pegawai char(5) not null, kd_cabang char(5) not null, nm_pegawai varchar (25),No_tlp varchar (12),alamat varchar (25),constraint pk_id_pegawai primary key(id_pegawai), constraint fk_kd_cabang foreign key(kd_cabang) references cabang (kd_cabang));

membuat table barang

create table barang (kd_barang char (5) not null, nm_barang varchar (30), jenis varchar(10), harga number (15), discount number(5), constraint pk_kd_barang primary key(kd_barang));

membuat table menjual:

create table menjual(id_pegawai char(5) not null, kd_barang char (5) not null, tgl_jual varchar (20), total_bayar number (15), jmlh_barang number (4), constraint fk_id_pegawai foreign key(id_pegawai) references pegawai(id_pegawai), constraint fk_kd_barang foreign key(kd_barang) references barang (kd_barang));

memasukan data ke table manager:

insert into manager values('20010','Ricardho max','0967643245','12 mei 1986','farm city 4 Australia');

insert into manager values ('20011','Michel Dornh','0797543245','14 febuari 1987','germ city 5 Singapura');

insert into manager values ('20020','Sari Fitriana','08986543467','23 desember 1987', 'jl. cempaka 4 bandung');

insert into manager values ('20021','Keny ardhi','0856123486','12 september 1986','jl. menoreh 4 tebet');

insert into manager values ('20022','Anissa putri','08912345654','23 maret 1987','jl. merdeka 4 makasar');

memasukan data ke table cabang:

insert into cabang values('10020','20010','Easyriders','unit1/220Naturaliste ice Western Australia');

insert into cabang values ('10021','20011','unkl347','Victoria street golden Singapura');

insert into cabang values ('10030','20020','unkl347 dealer','Trunojoyo no 4 Bandung');

insert into cabang values ('10031','20021','Nanonine House','Tebet utara dalam no 4 Jakarta selatan');

insert into cabang values ('10032','20022','Chamber','Boulevardrubby13MAkasar');

memasukan data ke table pegawai:

insert into pegawai values('30024','10020','Mery','0987654345','raflescia 4 australia');

insert into pegawai values ('30025','10020','Thomas','0923456543','raflecia 5 australia');

insert into pegawai values ('30042','10021','rachel','0734565432','hermania 4 singapura');

insert into pegawai values ('30045','10021','Ronald','073456543345','hermina 5 singapura');

insert into pegawai values ('30055','10030','Tony','0856234565','jl juanda 2 bandung');

insert into pegawai values ('30056','10030','Budi','08567654567','jl. solo 5 bandung');

insert into pegawai values ('30065','10031','Rahmat','0856876587','jl. jakarta 5 tebet');

insert into pegawai values ('30066','10031','Agil','08562343234','jl. merdeka 4 tebet');

insert into pegawai values ('30077','10032','Galih','08634567654','jln sumatra 5 makasar');

insert into pegawai values ('30078','10032','Rahma','08623456543','jln melati 4makasar ');

memasukan data ke table barang:

insert into barang values('005','sepatu','retro',360000,0);

insert into barang values ('006','Sanadal hotel','hotel',25000,0);

insert into barang values ('0011','kaos Hijau 234','catoon',70000,0);

insert into barang values ('0012','kaos ce panjang','jins',185000,0);

insert into barang values ('0023','tas hijau dots','slempang',125000,0);

insert into barang values ('0034','sabuk hitam','kain',65000,0);

insert into barang values ('0059','topi347','bulat34',45000,0);

memasukan data ke table menjual:

insert into menjual values('30045','0011','1 juni 2009',70000,1);

insert into menjual values ('30045','006','1 juni 2009',50000,2);

insert into menjual values ('30042','0023','1 juni 2009',125000,1);

insert into menjual values ('30042','0012','1 juni 2009',170000,2);

procedure delete

create or replace procedure delete_barang(v_kd_barang barang.kd_barang%type)

is

begin

delete from barang where kd_barang=v_kd_barang;

end;

procedure update

create or replace procedure update_barang(v_kd_barang barang.kd_barang%type,v_nm_barang barang.nm_barang%type)

is

begin

update barang set nm_barang= v_nm_barang where kd_barang= v_kd_barang;

end;

search barang;

create or replace procedure search_barang(v_kd_barang barang.kd_barang%type)

is

v_nm_barang barang.nm_barang%type;

v_jenis barang.jenis%type;

v_harga barang.harga%type;

a_kd_barang barang.kd_barang%type ;

begin

select kd_barang,nm_barang,jenis,harga into a_kd_barang,v_nm_barang,v_jenis,v_harga from barang where kd_barang =v_kd_barang;

dbms_output.put_line('kode barang:'||a_kd_barang);

dbms_output.put_line('nama barang:'||v_nm_barang);

dbms_output.put_line('jenis :'||v_jenis);

dbms_output.put_line('harga :'||v_harga);

end;

insert barang

create or replace procedure insert_barang(v_kd_barang barang.kd_barang%type)

is

v_nm_barang barang.nm_barang%type;

v_jenis barang.jenis%type;

v_harga barang.harga%type;

v_discount barang.discount%type;

a_kd_barang barang.kd_barang%type;

begin

insert into barang values(‘ ’||v_kd_barang,’ ’||v_nm_barang,’ ’||v_jenis,’ ’||v_harga,’ ’||V_discount);

end;