DDL – DML – Data Retrival pada MySQL/Oracle
DDL
//sintak untuk membuat field dengan nama “name” type varchar2, panjang 200 dan constraint PRIMARY KEY :
– Name Varchar2(200) Constraint pk_nama PRIMARY KEY
//jika field harus di isi, maka tambahkan Not Null
//Sintak untuk membuat Foreign key
– Id_foreign varcahar2(9),
– Constraint fK_lain Foreign Key(id_foreign) reference table_tujuan on delete cascade
Contoh :
Create table buku (
Employe_id number(6) constraint primary Key,
First_name varchar(20),
Last_name varchar(25),
Email varchar(25),
Phone_number varchar(20),
Hire_date date,
Salary Number(8),
Commision_pct number(2,2),
Job_id varchar(10),
Departement_id number(4),
Manaber_Id number(6),
Constraint fk_job_id foreign key(Job_id) reference Jobs on delete cascade,
Constraint fk_departemen foreign key(departement_id) reference departements on delete cascade,
Constraint fk_manager foreign key(manager_id) reference employees (employe_id) on delete cascade
);
//sintak or replace digunakan jika pembuatan view atau sesuatu , ternyata view tersebut sudah ada
//force digunakan jika membuat view, namun tabel sumber belum ada
//with check option digunakan untuk pengeceken data yang masuk atau diupdate di dalam view
//read only, hanya bisa melihat dari view tersebut
//Contoh —
Create or replace force view view calon( field) as select field from tab where bla bla, with check option;
//pembuatan squence—-
Create sequence nama
Increment by…
Start with value
Maxvalue
Order
;
//sintak untuk menghapus tabel dengan nama “nama_table”
Drop table nama_table;
//sintak untuk mengubah nama table
Rename baju to bajubaru
//sintak untuk menghapus isi dari tabel
Truncate table buku;
//sintak untuk memberi komen pada table
Comment on buku is ‘fifki’;
//sintak alter digunakan jika kita akan mengedit struktur dari sebuah tabel, misa menambah field,menghapus meupun mengedit
Contoh
– Alter table buku add field varchar(2);
– Alter table buku modify nim varchar2(89);
– Alter table buku drop colom/constraint field;
//sintak untuk membuat sebuah tabel, menggunakan isi dari tabel lain
Contoh
– Create table Buku_copy as
select * from Buku;
//sintak jika akan membuat sebuah table dengan menggukan field darii tabel lai ( hanya fieldnya saja)
Contoh
– Create table Buku_copy as
select * from Buku where 0=1;
———- DML —————-
//menginsert data ke tabel secara manual
Insert into buku values (‘001′,’buku dsd’);
//menginsertkan data ke tabel menggunakan data dari tabel lain
Insert into buku_copy
Select * from buku
Where status = “layak”;
//sintak update standar
Update buku set id=’1234′ where id=1231;
//sintak update dengan kondisional tertentu, misal mengupdate harga jika umur buku lebih dari 5thn dan masih layak
Update buku set harga=harga*1,15
– Where umur > 5 and status =”layak”; //kondisi 1 jiak ada umur
– Where months_between(sysdate,tgl_ada)/12 > 5 and …. // kondisi 2 jika ada tangal
//sintak delete data buku biasa
Delete from buku where id = 1323;
//sintak untuk mendelete buku yang umurnya lebih dari 10tahun
Delete from buku
Where months_between(sysdate,tgl_ada)/12 > 10;
//menggabungkan data dari 2 tabel
Contoh menggabungkan tabel buku_copy dengan buku, dimana jika data tidak ada maka data akan di insertkan, jika ada maka data akan di update
– Merge into buku_copy
Using buku
On buku_copy.id = buku.id
When matched then
Update set buku.harga = buku_copy.harga
When not matched then
Insert values (buku_copy.harga);
———— Data Retrival ————-
//distinct digunakan agar tidak ada data yang sama
Select distinct harga from buku b, canang c where b.harga = c.harga;
Select id_jabatan, count(id_jabatan) from petugas group by jabatan;
//menggurutkan data dengan cara
Order by asc/ desc
// contoh Sub query
– Select 8 from buku where harga = (select max(harga) from buku);
– Select * from sekolah s
Where exists
(select * from calon where id=s.id);
– With
Gaji as(select * from tabs)
Select * from buku, gaji where buku.id = gaji.id;
//Contoh PL/SQL
Declare
v_nama buku.nama%type;
Begin
Select name
Into v_nama
From buku
Output v_name
Exeption
When no_data_found the
Output(‘rore’);
End;
/
Cursor
Declare
Cursor C_name is
Select nama from buku;
Vnama buku.name%type
Begin
Open c_name
Loop
Fetch c_name into vname
Exit when c_name%notfound;
Output data;
End loop;
Close C_name;
For vnama in c_name
Loop
Dbms_output.put_line(c_name%rowcount||vname.name);
End;
/
//fungsi dan prosedure dala PL/SQL
Create or replace procedure lihat is
Namalokasi cabang.lokasi%type;
Begin
Select c.lokasi into namalokasi from member m cabang c where id=id
Output(
End;
/
Create or replace function buatbuku(id IN buku.id%type)
Return buku.nama%type is
v_nama buku.nama%type;
Begin
Select nama into v_nama from cabang where id=id;
Return v_nama;
End;
/
//memanggil fungsi yang telah dibuat
Select buatbuku(‘1234’) from dual;
———– Materi UAS 2010 ——————
//menggambil nama blakang pegawai yang telah bekerja lebih dari 10, dan nama blakang managernya
– Select emp.last_name “nama blakang pegawai”, mng.last_name “nama blakang manager”
– From employees emp, employees mng
– Where mng.id_employees = emp.id_menager
And months_between(sysdate,hire_date)/12 > 10;
//mengambil nama menager , dimana manager tersebut memiliki anak buah paling banyak, menggunakan klausa with
– With
Total_emp as (
Select m.first_name|| ‘ ‘ ||m.last_name as “manager name” , count(e.employee_id) as total
From employees e, employees m
Where m.id_emplye = e.id_manager
Group by m.first_name, m.last_name)
Select namager name from total_emp
Where total = (select max(total) from total_emp);
//mengambil jumlah kendaraan yang tidak pernah digunakan samsekali
– Select Count(*) as “total mobil”
from vehicles
Where License_plate not in (select license_plate from vehicle_used)
//mengambil identitas dari pegawai yang memiliki gaji terkecil dari setiap departemen
– withe
Min_hgaji as (Select employe_id, departement_id, min(salary)
From employees
Group by (departement_id))
Select * from employees e, min_gaji m
Where e.id_employe = m.id_employe;
Atau dengan cara :
Select d.departemen_name AS “departement”
e.first_name||’ ‘||e.last_name as “employee’s Name”
From employees e, departement d
Where e. Departemen_id = d.departement_id AND
(d.departemen_id, e.salary) IN (
select d.departemen_name, Min(e.salary)
From employees e, departement d
Where e.departement_id = d.departement_id
Group by d.departement_id)
Order by d.departemen_name;
