/***********************************************
*DDL(Data Definision Language)
**************************************************/


//----------------------koneksi--------
CONN

CONN sys as sysdba


//----------------------------Membuat user baru------------
CREATE USER selasa_diki IDENTIFIED BY stikomcki; 


//----------------KASIH PERMISION----------------
grant dba to selasa_diki;


//---------------------MELIHAT tabel-------------------------
SELECT * FROM tab;


//------------------------- melihat field dari tabel----------
desc barang;


//------------------menampilkan isi data dari tabel--------------
select * from pegawai;



//-------------------------------membuat table------------------
CREATE TABLE barang (kode_barang char(6), nama_barang varchar2(30), satuan_barang varchar2(10));

CREATE TABLE suplier (kode_suplier char(6), nama_suplier varchar2(30), alamat_suplier varchar2(50));

CREATE TABLE cutomer (kode_customer char(6), nama_customer varchar2(30), alamat_customer varchar2(50));

CREATE TABLE pasok (kode_pasok char(6), kode_barang char(6), kode_suplier char(6), jumlah_pasok number(38));

CREATE TABLE pembelian (kode_pembelian char(6), kode_barang char(6), kode_customer char(6), jumlah_pembelian number(38));



//--------menambahkan primary key---------
ALTER TABLE barang ADD CONSTRAINT pk PRIMARY KEY (kode_barang);

ALTER TABLE suplier ADD CONSTRAINT pk_suplier PRIMARY KEY (kode_suplier);

ALTER TABLE customer ADD CONSTRAINT pk_customer PRIMARY KEY (kode_customer);

ALTER TABLE pasok ADD CONSTRAINT pk_pasok PRIMARY KEY (kode_pasok);



//-----------------menambahkan foreign key--------------
ALTER TABLE pembelian ADD CONSTRAINT fk_barang FOREIGN KEY (kode_barang) REFERENCES barang ON DELETE CASCADE;

ALTER TABLE pembelian ADD CONSTRAINT fk_customer FOREIGN KEY (kode_customer) REFERENCES customer ON DELETE CASCADE;

ALTER TABLE pasok ADD CONSTRAINT fk_brg FOREIGN KEY (kode_barang) REFERENCES barang ON DELETE CASCADE;

ALTER TABLE pasok ADD CONSTRAINT fk_suplier FOREIGN KEY (kode_suplier) REFERENCES suplier ON DELETE CASCADE;




//-----------------mengubah tipe data---------------------
ALTER TABLE suplier MODIFY (nama_suplier char(40));



//---------------menghapus table-----------
drop table pasok;



//--------------penambahan field baru------------
ALTER TABLE barang ADD (stok_barang int);







/************************************************
*DML(Data Manipulation Language)
*************************************************/

//-----------------Insert Data-----------
insert into barang(kode_barang,nama_barang,satuan_barang,stok_barang) values('B001','Lemari Es','unit',11);

insert into barang(kode_barang,nama_barang,satuan_barang,stok_barang) values('B002','Rice Cooker','buah',23);

insert into pasok values('P001','B002','S001',5);

 insert into pasok values('P002','B003','S002',20);



//-------------------UPDATE------------------------------------
UPDATE pegawai SET nama = 'BU ATIK' WHERE nama = 'ATIK';

update barang set satuan_barang = 'unit' where kode_barang = 'B003';

update pasok set kode_barang = 'B002' where kode_pasok = 'P001';
update pasok set kode_barang = 'B003' where kode_pasok = 'P002';











/******************************************************************
*Pengelompokan, pengurutan dan kriteria data
*******************************************************************



//----------GROUP BY----------------------------------------------

SELECT TUNJANGAN_ISTRI, TUNJANGAN_ANAK1, TUNJANGAN_ANAK2, MIN(TUNJANGAN_ISTRI) FROM pegawai GROUP BY TUNJANGAN_ISTRI, TUNJANGAN_ANAK1, TUNJANGAN_ANAK2;

SELECT TUNJANGAN_ISTRI, TUNJANGAN_ANAK1, TUNJANGAN_ANAK2, MAX(TUNJANGAN_ISTRI) FROM pegawai GROUP BY TUNJANGAN_ISTRI, TUNJANGAN_ANAK1, TUNJANGAN_ANAK2;

SELECT TUNJANGAN_ISTRI, TUNJANGAN_ANAK1, TUNJANGAN_ANAK2, AVG(TUNJANGAN_ISTRI) FROM pegawai GROUP BY TUNJANGAN_ISTRI, TUNJANGAN_ANAK1, TUNJANGAN_ANAK2;

SELECT TUNJANGAN_ISTRI, TUNJANGAN_ANAK1, TUNJANGAN_ANAK2, SUM(TUNJANGAN_ISTRI) FROM pegawai GROUP BY TUNJANGAN_ISTRI, TUNJANGAN_ANAK1, TUNJANGAN_ANAK2;

SELECT TUNJANGAN_ISTRI, TUNJANGAN_ANAK1, TUNJANGAN_ANAK2, COUNT(TUNJANGAN_ISTRI) FROM pegawai GROUP BY TUNJANGAN_ISTRI, TUNJANGAN_ANAK1, TUNJANGAN_ANAK2;

//------------LATIHAN---------------------------------
SELECT ID_PEGAWAI as id_peg, MIN(ID_JABATAN) as min_pasok FROM pegawai GROUP BY ID_PEGAWAI, ID-JABATAN;

SELECT ID_PEGAWAI as id_peg, MAX(ID_JABATAN) as max_pasok FROM pegawai GROUP BY ID_PEGAWAI, ID-JABATAN;

SELECT ID_PEGAWAI as id_peg, AVG(ID_JABATAN) as avg_pasok FROM pegawai GROUP BY ID_PEGAWAI, ID-JABATAN;

SELECT ID_PEGAWAI as id_peg, SUM(ID_JABATAN) as sum_pasok FROM pegawai GROUP BY ID_PEGAWAI, ID-JABATAN;

SELECT ID_PEGAWAI as id_peg, COUNT(ID_JABATAN) as count_pasok FROM pegawai GROUP BY ID_PEGAWAI, ID-JABATAN;

select kode_barang as kd_brg, min(jumlah_pasok) as minimum_pasok from pasok group by kode_barang, jumlah_pasok;

select kode_barang as kd_bg, min(jumlah_pembelian) as minimal_pembelian from pembelian group by kode_barang;

select kode_barang as kd_bg, max(jumlah_pembelian) as maximal_pembelian from pembelian group by kode_barang;

select kode_barang as kd_bg, avg(jumlah_pembelian) as rata_rata_pembelian from pembelian group by kode_barang;

select kode_barang as kd_bg, sum(jumlah_pembelian) as total_pembelian from pembelian group by kode_barang;

select kode_barang as kd_bg, count(jumlah_pembelian) as jumlah_pembelian from pembelian group by kode_barang;

//------------cacah pembelian per customer---------------
select kode_customer as kd_cust, count(jumlah_pembelian) as jumlah_pembelian from pembelian group by kode_customer;




//-------------------ORDER BY----------------------------
SELECT * FROM pegawai ORDER BY NAMA;