drop database perusahaancreate database perusahaancreate table departemen(id_dep varchar(2),nama_dep varchar(15),primary key(id_dep))insert into departemen values('AK ','Akuntansi'),('EN ','Engineering'),('PS ','Pemasaran'),('RD ','R & D'),('SD ','SDM')create table karyawan(no_karyawan varchar(10) primary key,nama varchar(20),jenis_kelamin bool,tanggal_lahir date,jenis_karyawan varchar(1),id_dep varchar(2),foreign key(id_dep) references departemen(id_dep))insert into karyawan values('20050001','Rusdianto ','1','1975-03-24','M ','PS'),('20050002','Darmaji ','1','1977-03-17','M ','AK'),('20050003','Dirham ','1','1969-12-01','M ','SD'),('20050004','Duhan ','1','1972-03-28','T ','EN'),('20060001','Dewi Nurhayati ','0','1973-04-01','S ','PS'),('20060002','Diah Ariyanti ','0','1977-10-14','* ','AK'),('20060003','Danang Danarhadi ','1','1976-01-16','M ','EN'),('20060004','Sakroni ','1','1977-06-23','* ','PS'),('20080001','Sita Amelia ','0','1980-12-11','* ','SD'),('20080002','Rita Andini ','0','1982-03-01','S ','AK'),('20080003','Rudi Hartono ','1','1981-08-08','M ','RD'),('20080004','Karmen ','1','1979-05-14','T ','EN'),('20080005','Santi Wardani ','0','1985-11-17','* ','PS'),('20080006','Savitri ','0','1988-12-02','* ','PS'),('20080007','Firanda ','0','1986-07-01','* ','PS'),('20080008','Rian Adiwarna ','1','1986-01-13','* ','PS'),('20090001','Ratna Maharani ','0','1977-08-29','* ','PS'),('20090002','Robert Sinamurna ','1','1969-11-30','T ','EN'),('20090003','Saman Alhamdi ','1','1978-02-28','* ','PS'),('20090004','Sumartini ','0','1980-07-24','S ','SD'),('20090005','Karim Sujarwadi ','1','1985-10-01','* ','RD'),('20090006','Suwandi ','1','1989-05-15','* ','PS'),('20090007','Siti Aminah ','0','1985-09-01','* ','AK'),('20090008','Victor Sumardi ','1','1976-03-03','* ','SD'),('20090009','Jaja Miharja ','1','1974-07-23','T ','EN'),('20090010','Tarmidzi ','1','1981-05-21','* ','PS'),('20090011','Novia Fitriana ','0','1982-08-19','* ','AK')create table karyawan_mobil(no_karyawan varchar(10) primary key,type varchar(10),no_polisi varchar(10),id_dep varchar(2),foreign key(id_dep) references departemen(id_dep))insert into karyawan_mobil values('20050001','AVANZA G ','AB1674GA ','PS'),('20050002','AVANZA G ','AB1675GA ','SD'),('20050003','RUSH G ','AB6776BA ','SD'),('20060003','RUSH G ','AB7374SA ','EN'),('20080003','BALENO ','AB1111TA ','RD')create table proyek(id_proyek varchar(2) primary key,deskripsi varchar(30),selesai bool)alter table proyek modify deskripsi textinsert into proyek values('1','Proyek Pengembangan Aplikasi A/R Internal ','0'),('2','Proyek Pengembangan A/P Internal ','0'),('3','Proyek LAN di CV Mayapada ','1'),('4','Proyek Instalasi Jaringan Komputer di PT Suramaya ','0'),('5','Proyek Instalasi Jaringan Komputer di PT Jayaraya ','1'),('6','Pelatihan LAN di PT Durajati ','1'),('7','Pra-tender PT Sukamaju ','1'),('8','Pengembangan Aplikasi BBN ','0')create table keahlian(no_karyawan varchar(10),keahlian_teknis varchar(20),primary key(no_karyawan,keahlian_teknis),foreign key(no_karyawan) references karyawan(no_karyawan))insert into keahlian values('20050004','Hardware'),('20050004','Jaringan Komputer'),('20080004','Jaringan Komputer'),('20080004','Linux'),('20090002','MySQL'),('20090002','Pemrograman C'),('20090002','PHP'),('20090002','SQL Server'),('20090009','ASP'),('20090009','FoxPro'),('20090009','Jaringan Komputer')create table bahasa(no_karyawan varchar(10),bahasa varchar(15),primary key(no_karyawan,bahasa),foreign key(no_karyawan) references karyawan(no_karyawan))insert into bahasa values('20060001','Inggris'),('20060001','Mandarin'),('20080002','Inggris'),('20090004','Inggris'),('20090004','Jerman')create table karyawan_proyek(no_karyawan varchar(10),id_proyek varchar(2),mulai_tanggal date,sampai_tanggal date,primary key(no_karyawan,id_proyek),foreign key(no_karyawan) references karyawan(no_karyawan),foreign key(id_proyek) references proyek(id_proyek))insert into karyawan_proyek values('20050004','3','2009-03-21','2000-04-10'),('20050004','4','2009-04-20','0000-00-00'),('20050004','5','2009-06-01','2009-07-01'),('20050004','6','2009-08-10','2009-08-25'),('20080004','4','2009-06-01','NULL'),('20080004','5','2009-06-01','2009-06-15'),('20080004','7','2009-10-10','2009-06-15'),('20080004','8','2009-11-15','NULL'),('20090002','1','2009-02-01','NULL'),('20090002','2','2009-02-01','NULL'),('20090009','1','2009-02-01','NULL'),('20090009','2','2009-02-01','NULL'),('20090009','8','2009-11-15','2009-11-25')SELECT Jenis_karyawan as Jenis,count(jenis_karyawan)as JumlahFROM KARYAWANGROUP BY Jenis_karyawanSELECT concat_ws(' ',no_karyawan,id_dep,type)as Karyawan, CONCAT(' ',type,no_polisi) as KENDARAANFROM karyawan_mobilselect * from karyawan_mobilSELECT no_karyawan, SUBSTR(nama,3,6) as NAMAFROM KARYAWANSELECT no_karyawan, nama,LENGTH(nama) FROM KARYAWANSELECT no_karyawan, right(nama,6) as NAMA, length(right(nama,6))FROM KARYAWANSELECT no_karyawan, reverse(nama) as nama FROM karyawanselect current_date()select nama,date_format(tanggal_lahir, '%d-%m-%y') as bulan from karyawanselect nama,tanggal_lahir from karyawanselect week(now())create view tgllahirkaryawan asselect nama,date_format(tanggal_lahir, '%d-%m-%y') as bulan from karyawanselect * from tgllahirkaryawanselect * from karyawancreate view pegawai asselect no_karyawan as NIP,nama,case jenis_kelaminwhen true then 'Pria'else 'Wanita'end as 'Jenis Kelamin',date_format(tanggal_lahir, '%d-%m-%Y') as 'Tanggal Lahir',case jenis_karyawanwhen 'M' then 'Manajer'when 'T' then 'Teknisi'when 'S' then 'Staff'else '-' end as 'Jenis Karyawan',id_dep as Departemenfrom karyawanselect * from pegawaiselect * from provinsiselect * from kotaselect * from kecamatanSelect a.nama_kota, b.nama_prov From kota a, provinsi bWhere a.kode_prov = b.kode_provSELECT nama_kota, nama_prov FROM provinsi JOIN kota using(kode_prov)SELECT nama_kec, nama_kota, nama_prov FROM kecamatan NATURAL JOIN kotaNATURAL JOIN provinsiSELECT nama_kec, nama_kota FROM kecamatan join kota using(kode_kota,kode_prov) WHERE kecamatan.kode_prov = provinsi.kode_provAND kecamatan.kode_kota = kota.kode_kotaAND kota.kode_prov = provinsi.kode_provSELECT nama_kota, nama_provFROM kota JOIN provinsi using(kode_prov)SELECT nama_kota,nama_prov FROM kota NATURAL left JOIN provinsiunionSELECT nama_kota,nama_prov FROM kotaNATURAL right JOIN provinsi