connect system
password 1234
select*from tab;
create table siswa
( nis char(4) not null primary key,
nama varchar(15),
alamat varchar(20));
drop table siswa;
alter table siswa add no_telepon varchar(15);
insert into siswa values('001','Amin','Bekasi','123');
insert into siswa values('002','Udin','Jakarta','777');
insert into siswa values('003','Susi','Depok','');
insert into siswa(nis,nama,alamat) values('004','Fajar','Tangerang');
insert into siswa values('005','Iman','Depok2','999');
select*from siswa;
create table nilai
( no char(3) not null,
nis char(4),
matpel varchar(20),
nilai integer,
primary key(no),
foreign key(nis) references siswa(nis));
update siswa set no_telepon='234' where nis='003';
insert into nilai values('1','001','Kimia','80');
insert into nilai values('2','001','Biologi','70');
insert into nilai values('3','003','Fisika','65');
insert into nilai values('4','004','Kimia','90');
insert into nilai values('5','002','Fisika','50');
insert into nilai values('6','005','Agama','30');
Tanggal 1-Maret-2014
create table sales
( kode char(4) not null primary key,
nama varchar(10),
alamat varchar(10));
create table penjualan
( No char(4) not null primary key,
Kdsales char(4),
Bulan char(3),
Jumlah number,
foreign key(Kdsales) references sales(Kode));
insert into sales values('0001','Firman','Jakarta');
insert into sales values('0002','Endang','Bekasi');
insert into sales values('0003','Gatot','Jakarta');
insert into penjualan values('0001','0001','Jan','5000000');
insert into penjualan values('0002','0001','Feb','4000000');
insert into penjualan values('0003','0002','Jan','7500000');
insert into penjualan values('0004','0003','Jan','1500000');
insert into penjualan values('0005','0002','Mar','1000000');
insert into penjualan values('0006','0001','Mar','8250000');
insert into penjualan values('0007','0002','Apr','4500000');
insert into penjualan values('0008','0003','Apr','6300000');
insert into penjualan values('0009','0001','Apr','4200000');
update sales
set alamat='Bogor'
where kode='0002';
select nama, alamat from sales;
select*from penjualan
2 where Bulan <> 'Jan';
2
2* where Bulan <> 'Jan'
c/<>/!=;
2* where Bulan != 'Jan'
/
select*from penjualan
where Jumlah between 2000000 and 5000000;
select*from penjualan
where Bulan in ('Jan','Feb');
select*from sales
where alamat like 'J%';
select*from sales
where nama like '_____';
Operator pada klausa WHERE:
- Aritmatika : <>, !=, =, <, >,>=
- Logika : and, or, not
- Rentang : between
- Lingkup : in
- Substitusi : like (% --> all char, _--> 1 char)
mis : select*from buku
where judul like %basis data%;
select*from sales where nama like '_____';
- Null
mis : select*from MHS where Notelp is Not Null;
Perintah C --> mengganti teks
mis : c/aku/aqiu
Perintah / --> menjalankan yg dibuffer
Perintah 2 --> pointer pada baris ke 2
select*from sales where alamat <> 'Bekasi';
select*from penjualan where Bulan between 'Jan' and 'Apr';
select*from penjualan where Bulan in ('Jan','Feb','Mar','Apr');
update sales set alamat='Banten' where nama='Gatot';
alter table sales add notelepon varchar(12);
select nama from sales where notelepon is Null;
create table barang1
(kode char(3) not null primary key,
nama varchar(15),
harga number,
jumlah number);
insert into barang1 values('001','Kemeja',100000,10);
insert into barang1 values('002','T-Shirt',125000,10);
insert into barang1 values('003','Dasi',75000,100);
insert into barang1 values('004','Topi',300000,25);
alter table penjualan add KodeBrg char(3);
alter table penjualan add constraint fk_KodeBrg foreign key(KodeBrg) references barang1(Kode);
describe penjualan;
select*from penjualan;
update penjualan set KodeBrg ='001' where No ='0001';
update penjualan set KodeBrg ='002' where No ='0002';
update penjualan set KodeBrg ='001' where No ='0003';
update penjualan set KodeBrg ='003' where No ='0004';
update penjualan set KodeBrg ='001' where No ='0005';
update penjualan set KodeBrg ='001' where No ='0006';
update penjualan set KodeBrg ='001' where No ='0007';
update penjualan set KodeBrg ='004' where No ='0008';
update penjualan set KodeBrg ='001' where No ='0009';
1. Tampilkan Data penjualan kemeja
#dengan Join
select P.* from penjualan P, barang1 B where P.KodeBrg = B.Kode and B.Nama = 'Kemeja';
#dengan sub queryc
select*from penjualan
where kodebrg in
( select kode from barang1
where nama='Kemeja');
connect system/1234;
create user eddy identified by eddy;
create user firman identified by firman default tablespace users;
drop user firman;
connect Yono/12345;
create table dataku
(kode char(2) not null primary key,
nama varchar(20));
insert into dataku values('01','Celana');
insert into dataku values('02','Dasi');
insert into dataku values('03','Sepatu');
select * from dataku;
MANAJEMEN USER PADA ORACLE
Memberikan hak kepada user :
GRANT select|insert|update|delete ON [nama tabel] TO user;
Contoh :
GRANT select ON dataku TO Tuti;
Mengambil hak dari user :
REVOKE all|select| ... ON [nama tabel] FROM users;
Contoh :
REVOKE all ON dataku from Tuti;
MENGELOLA VIEW :
CREATE VIEW [nama view]
AS [perintah select]
Contoh : Membuat view yang menampilkan judul film dan biaya sewa
CREATE VIEW ViewFilm1
AS select judul,biaya from film;
KP :
VIEW --> objek yang menyimpan query = tabel virtual
sintaks : create view nama_view as perintah_select;
Contoh :
create view v.buku as
select A.Buku_isbn;
A.Buku_Judul;
A.Penerbit_Nama;
A.Buku_Tglterbit;
A.Buku_JmlhHalaman;
from
Buku A;
Penerbit B;
where A.Penerbit_id = B.Penerbit_id
order by A.Buku_Judul;
menampilkan view :
select * from nama_view;
contoh :
select * from v_buku;
melihat struktur view :
desc nama_view;
contoh : desc v_buku;
Mengubah view
sintaks : alter view nama_view as perintah_select_yg_diubah;
atau gunakan create or replace view
contoh :
Menghilangkan kolom Buku_tglterbit dan Buku_Jmlhhalaman dari view v.buku.
create or replace view v_buku as select
A.Buku_isbn;
A.Buku_Judul;
A.Penerbit_Nama;
from
Buku A;
Penerbit B;
where
A.Penerbit_id = B.Penerbit_id;
order by
A.Buku_Judul;
Menghapus view
drop view nama_view;
contoh : drop view v_buku;
set serveroutput on;
declare
a number := 10;
b number := 15;
c number;
maks number;
begin
c := a + b;
dbms_output.put_line('Hasil Jumlah = ' || c);
dbms_output.put_line('Selisih = ' || (b - a));
dbms_output.put_line('Hasil Kali = ' || (a * b));
dbms_output.put_line('Hasil Bagi = ' || (b / a));
dbms_output.put_line('Hasil pangkat = ' || (b ** a));
if ( a > b) then
maks := a;
else
maks := b;
end if;
dbms_output.put_line('Maksimum = ' || maks);
end;
/
set serveroutput on;
declare
a number := 7;
b number := 4;
c number := 10;
besar number; kecil number;
begin
dbms_output.put_line(' A = ' || a);
dbms_output.put_line(' B = ' || b);
dbms_output.put_line(' C = ' || c);
besar := a; kecil := b;
if ( b > besar ) then
besar := b;
end if;
if ( c > besar ) then
besar := c;
end if;
if ( b < kecil ) then
kecil := b;
end if;
if ( c < kecil ) then
kecil := c;
end if;
dbms_output.put_line(' Terbesar = ' || besar);
dbms_output.put_line(' Terkecil = ' || kecil);
end;
/
set serveroutput on;
declare
bil number := 7;
begin
dbms_output.put_line(' Bilangan = ' || bil);
if ( bil > 0 ) then
dbms_output.put_line(' Positif ... ');
elsif ( bil = 0 ) then
dbms_output.put_line(' Nol ... ');
else
dbms_output.put_line(' Negatif ... ');
end if;
end;
/
declare
bil number := 99;
begin
dbms_output.put_line(' Nilai = ' || bil);
if ( bil >= 80 ) then
dbms_output.put_line(' Baik ');
elsif ( bil >= 70 ) then
dbms_output.put_line(' Cukup ');
elsif ( bil >= 60 ) then
dbms_output.put_line(' Kurang ');
elsif ( bil >= 0 ) then
dbms_output.put_line(' Tidak Lulus ');
end if;
end;
/
declare
i number := 100;
begin
while ( i >= 20 ) loop
dbms_output.put_line( i );
i := i - 10;
end loop;
end;
/
declare
i number;
begin
for i in 1 .. 10 loop
dbms_output.put_line(i*i);
end loop;
end;
/
EXEC
create or replace procedure HitBujurSangkar(s in number) as
luas number;
keliling number;
begin
luas := s*s;
keliling := 4*s;
dbms_output.put_line('Luas = ' || luas);
dbms_output.put_line('Keliling = ' || keliling);
dbms_output.put_line('Terima Kasih....');
end;
/
exec HitBujurSangkar(...);
declare
a integer;
begin
a := 7;
if a mod 2 = 0 then
dbms_output.put_line(a || ' adalah genap');
else
dbms_output.put_line(a || ' adalah ganjil');
end if;
end;
/
create or replace procedure CekBil(a in integer) as
begin
if a mod 2 = 0 then
dbms_output.put_line('Bilangan ' || a || 'adalah genap');
else
dbms_output.put_line('Bilangan ' || a || 'adalah ganjil');
end if;
end;
/
create or replace procedure Barisan(bb in integer, ba in integer) as
i integer;
begin
for i in bb .. ba loop
dbms_output.put_line('Bil : ' || i || ',nilai kuadrat : ' || (i*i));
end loop;
end;
/
create or replace function Kuadrat(bil in integer)
return integer as
x integer;
begin
x := bil * bil;
return x;
end;
/
begin
dbms_output.put_line('Kuadrat dari 5 = ' || Kuadrat(5));
end;
/
create or replace function Maks(a in integer, b in integer, c in integer)
return integer as
x integer;
begin
x := a;
if b > x then
X := b;
end if;
if c > x then
x := c;
end if;
return x;
end;
/
create or replace procedure LihatMaks(a in integer, b in integer, c in integer) as
bil integer;
begin
bil := Maks(a,b,c);
dbms_output.put_line('Yang terbesar diantara ' || a || ',' || b || ', dan ' || c || ' adalah ' || bil);
end;
/
================ TUGAS =====================
1. Buatlah procedure untuk membaca nilai a,b, dan c dari sebuah persamaan kuadrat dan menampilkan akar2 persamaan kuadrat tersebut.
x1 dan x2
x
bil Integer
2. Buatlah sebuah fungsi yang membaca 3 sisi segitiga dan menentukan apakah segitiga tersebut adalah sama kali atau bukan
---> yang ditambahkan adalah keterangan >> Sama Kali dan Bukan
3. Buatlah prosedur untuk menampilkan deret genap dan batas bawah sampai batas atas (Gunakan WHILE!)
create or replace function Luas(a in number, b in number)
return number as
c number;
begin
c := a*b/2;
return c;
end;
/
create or replace procedure HitLuas(a in number, b in number) as
bil number;
begin
bil := Luas(a,b);
dbms_output.put_line('Luas Segitiga adalah ' || bil);
end;
/
Tidak ada komentar:
Posting Komentar