Cacher is the code snippet organizer for pro developers

create table profile_sam(
  id_profile number primary key not null,
  nama_lengkap varchar2(50),
  nama_panggilan varchar2(10),
  alamat varchar2(50),
  tmpt_tgl_lahir  date default sysdate,
  nama_universitas_asal varchar2(50),
  jurusan_kuliah varchar2(50)
  );

create user sam identified by oracle;

//harus di grant
grant connect, resource, dba to sam;

=============================================


create table user_bdp (
  id_user       number primary key not null,
  nama          varchar2(50),
  alamat        varchar2(50),
  tgl_lahir     date default sysdate,
  created_by    varchar2(50),
  created_date  date,
  updated_by    varchar2(50),
  updated_date  date
  );

1. TAMPILKAN SEMUA
2. TAMPILKAN BEBERAPA
3. PILIH YANG BERDASARKAN HARI
4. PILIH YANG BERDASARKAN NAMA DOSEN 
5. PILIH YANG BERDASARKAN JAM MULAI
6. PILIH YANG BERDASARKAN HARI & NAMA DOSEN
7. JOIN TABLE

-------------------------------------------------------------


1. SELECT * FROM MATAKULIAH, PROFILE_SAM;

2. SELECT ID_MATAKULIAH,NAMA_PELAJARAAN,JUMLAH_SKS,HARI FROM MATAKULIAH;

3. SELECT * FROM MATAKULIAH WHERE HARI = 'SENIN';

4. SELECT * FROM MATAKULIAH WHERE NAMA_DOSEN = 'SAM';

5. SELECT * FROM MATAKULIAH WHERE JAM_MASUK = '13.00';

6. SELECT hari, nama_dosen FROM MATAKULIAH WHERE hari ='senin';

7.a SELECT M.NAMA_PELAJARAN, P.JURUSAN_KULIAH
    FROM MATAKULIAH AS M, PROFILE_SAM AS P
    WHERE M.ID_MATAKULIAH = P.ID_MATAKULIAH
   
   
7.b SELECT * FROM matakuliah FULL JOIN profile_sam
    ON matakuliah.id_matakuliah = profile_sam.id_matakuliah

//

INSERT INTO matakuliah (ID_MATAKULIAH,NAMA_PELAJARAAN,NAMA_DOSEN,JUMLAH_SKS,JAM_MASUK,JAM_SELESAI,HARI)
VALUES ('101','Pemrograman','Pak Sam','5','17.00','15.00','rabu');


INSERT INTO profile_sam (ID_PROFILE,NAMA_LENGKAP,NAMA_PANGGILAN,ALAMAT,TGL_LAHIR,NAMA_UNIVERSITAS_ASAL,JURUSAN_KULIAH,TEMPAT_LAHIR,ID_JADWAL,KODE_MAHASISWA,ID_MATAKULIAH)
VALUES ('201','Rafsanjani','Sammy','Ciputat','30-10-1986','UIN Jakarta','Sistem Informasi','Jakarta','301','402','102');

//

//Execute by line bro not to all!!


ALTER TABLE user_bdp RENAME TO bdp_04;
------------------

ALTER TABLE bdp_04 ADD (kampus varchar2(50));
------------------

ALTER TABLE bdp_04 MODIFY nama varchar2(50) NOT NULL;
------------------

ALTER TABLE bdp_04 DROP COLUMN nama;
------------------

ALTER TABLE bdp_04 ADD (nama varchar2(100));
------------------

ALTER TABLE profile_sam RENAME COLUMN tmpt_tgl_lahir to tmpt_lahir;
------------------

// di dalam oracle tidak ada auto increment jadi harus di bikin sequence

CREATE SEQUENCE user_bdp_seq START WITH 1 INCREMENT BY 1;
------------------

//How to make foreign key in sql

ALTER TABLE matakuliah
ADD CONSTRAINT fk_mtkuliah
  FORIGN KEY (id_matakuliah)
  REFERENCES matakuliah(id_matakuliah);
----------------------

//Update syntax
update user_ok set
  nama = 'tes_update',
  updated_by = 'sam',
  updated_date = to_date('25-01-2014')
where id_user = 7
------------------