TUGAS 3 MANAJEMEN DAN ADMINISTRASI BASIS DATA
Dosen Pengampu: Bp. Eko Purwanto
Dosen Pengampu: Bp. Eko Purwanto
NAMA : Eko Pranoto Saputro
NIM : 160101237
Smt : 4 (empat)
STMIK DUTA BANGSA SURAKARTA
2018
========================================================================
Hasil Koding:
mysql> use ekopranotosaputro; Database changed mysql> create table karyawan(nopeg int(10) primary key auto_increment,nama varchar(25), Jeniskelamin char(2),kota varchar(25),kodepos char(5),tgllahir date); Query OK, 0 rows affected mysql> alter table karyawan change nopeg noid int(10) auto_increment; Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table karyawan change jeniskelamin jenkel char(1); Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> desc karyawan; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | noid | int(10) | NO | PRI | NULL | auto_increment | | nama | varchar(25) | YES | | NULL | | | jenkel | char(1) | YES | | NULL | | | kota | varchar(25) | YES | | NULL | | | kodepos | char(5) | YES | | NULL | | | tgllahir | date | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 6 rows in set mysql> alter table karyawan rename to tabelpegawai; Query OK, 0 rows affected mysql> desc tabelpegawai; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | noid | int(10) | NO | PRI | NULL | auto_increment | | nama | varchar(25) | YES | | NULL | | | jenkel | char(1) | YES | | NULL | | | kota | varchar(25) | YES | | NULL | | | kodepos | char(5) | YES | | NULL | | | tgllahir | date | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 6 rows in set
mysql> insert into tabelpegawai(nama,jenkel,kota,kodepos,tgllahir) values ('Ahmad Sobari','L','Bandung','41011','1977-10-02'), ('Sundariwati','P','Bandung','40123','1978-11-12'), ('Ryan Hendrawan','L','Jakarta','12111','1981-03-21'), ('Zulkarman','L','Bekasi','17211','1978-08-10'), ('Yuliawati','P','Bogor','00000','1982-06-09'), ('Mawar','P','Bogor','12345','1985-07-07'); Query OK, 6 rows affected Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from tabelpegawai; +------+----------------+--------+---------+---------+------------+ | noid | nama | jenkel | kota | kodepos | tgllahir | +------+----------------+--------+---------+---------+------------+ | 1 | Ahmad Sobari | L | Bandung | 41011 | 1977-10-02 | | 2 | Sundariwati | P | Bandung | 40123 | 1978-11-12 | | 3 | Ryan Hendrawan | L | Jakarta | 12111 | 1981-03-21 | | 4 | Zulkarman | L | Bekasi | 17211 | 1978-08-10 | | 5 | Yuliawati | P | Bogor | 00000 | 1982-06-09 | | 6 | Mawar | P | Bogor | 12345 | 1985-07-07 | +------+----------------+--------+---------+---------+------------+ 6 rows in set mysql> select nama,jenkel from tabelpegawai; +----------------+--------+ | nama | jenkel | +----------------+--------+ | Ahmad Sobari | L | | Sundariwati | P | | Ryan Hendrawan | L | | Zulkarman | L | | Yuliawati | P | | Mawar | P | +----------------+--------+ 6 rows in set mysql> select nama,jenkel from tabelpegawai where jenkel='p'; +-------------+--------+ | nama | jenkel | +-------------+--------+ | Sundariwati | P | | Yuliawati | P | | Mawar | P | +-------------+--------+ 3 rows in set mysql> select * from tabelpegawai order by nama asc; +------+----------------+--------+---------+---------+------------+ | noid | nama | jenkel | kota | kodepos | tgllahir | +------+----------------+--------+---------+---------+------------+ | 1 | Ahmad Sobari | L | Bandung | 41011 | 1977-10-02 | | 6 | Mawar | P | Bogor | 12345 | 1985-07-07 | | 3 | Ryan Hendrawan | L | Jakarta | 12111 | 1981-03-21 | | 2 | Sundariwati | P | Bandung | 40123 | 1978-11-12 | | 5 | Yuliawati | P | Bogor | 00000 | 1982-06-09 | | 4 | Zulkarman | L | Bekasi | 17211 | 1978-08-10 | +------+----------------+--------+---------+---------+------------+ 6 rows in set mysql> select * from tabelpegawai order by kota asc; +------+----------------+--------+---------+---------+------------+ | noid | nama | jenkel | kota | kodepos | tgllahir | +------+----------------+--------+---------+---------+------------+ | 1 | Ahmad Sobari | L | Bandung | 41011 | 1977-10-02 | | 2 | Sundariwati | P | Bandung | 40123 | 1978-11-12 | | 4 | Zulkarman | L | Bekasi | 17211 | 1978-08-10 | | 5 | Yuliawati | P | Bogor | 00000 | 1982-06-09 | | 6 | Mawar | P | Bogor | 12345 | 1985-07-07 | | 3 | Ryan Hendrawan | L | Jakarta | 12111 | 1981-03-21 | +------+----------------+--------+---------+---------+------------+ 6 rows in set mysql> select * from tabelpegawai order by tgllahir desc; +------+----------------+--------+---------+---------+------------+ | noid | nama | jenkel | kota | kodepos | tgllahir | +------+----------------+--------+---------+---------+------------+ | 6 | Mawar | P | Bogor | 12345 | 1985-07-07 | | 5 | Yuliawati | P | Bogor | 00000 | 1982-06-09 | | 3 | Ryan Hendrawan | L | Jakarta | 12111 | 1981-03-21 | | 2 | Sundariwati | P | Bandung | 40123 | 1978-11-12 | | 4 | Zulkarman | L | Bekasi | 17211 | 1978-08-10 | | 1 | Ahmad Sobari | L | Bandung | 41011 | 1977-10-02 | +------+----------------+--------+---------+---------+------------+ 6 rows in set mysql> select * from tabelpegawai order by nama desc; +------+----------------+--------+---------+---------+------------+ | noid | nama | jenkel | kota | kodepos | tgllahir | +------+----------------+--------+---------+---------+------------+ | 4 | Zulkarman | L | Bekasi | 17211 | 1978-08-10 | | 5 | Yuliawati | P | Bogor | 00000 | 1982-06-09 | | 2 | Sundariwati | P | Bandung | 40123 | 1978-11-12 | | 3 | Ryan Hendrawan | L | Jakarta | 12111 | 1981-03-21 | | 6 | Mawar | P | Bogor | 12345 | 1985-07-07 | | 1 | Ahmad Sobari | L | Bandung | 41011 | 1977-10-02 | +------+----------------+--------+---------+---------+------------+ 6 rows in set mysql> alter table tabelpegawai add gaji int(12) after tgllahir; Query OK, 6 rows affected Records: 6 Duplicates: 0 Warnings: 0 mysql> desc tabelpegawai; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | noid | int(10) | NO | PRI | NULL | auto_increment | | nama | varchar(25) | YES | | NULL | | | jenkel | char(1) | YES | | NULL | | | kota | varchar(25) | YES | | NULL | | | kodepos | char(5) | YES | | NULL | | | tgllahir | date | YES | | NULL | | | gaji | int(12) | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 7 rows in set mysql> select * from tabelpegawai; +------+----------------+--------+---------+---------+------------+------+ | noid | nama | jenkel | kota | kodepos | tgllahir | gaji | +------+----------------+--------+---------+---------+------------+------+ | 1 | Ahmad Sobari | L | Bandung | 41011 | 1977-10-02 | NULL | | 2 | Sundariwati | P | Bandung | 40123 | 1978-11-12 | NULL | | 3 | Ryan Hendrawan | L | Jakarta | 12111 | 1981-03-21 | NULL | | 4 | Zulkarman | L | Bekasi | 17211 | 1978-08-10 | NULL | | 5 | Yuliawati | P | Bogor | 00000 | 1982-06-09 | NULL | | 6 | Mawar | P | Bogor | 12345 | 1985-07-07 | NULL | +------+----------------+--------+---------+---------+------------+------+ 6 rows in set mysql> update tabelpegawai set gaji='1000000' where noid='1'; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from tabelpegawai where noid='1'; +------+--------------+--------+---------+---------+------------+---------+ | noid | nama | jenkel | kota | kodepos | tgllahir | gaji | +------+--------------+--------+---------+---------+------------+---------+ | 1 | Ahmad Sobari | L | Bandung | 41011 | 1977-10-02 | 1000000 | +------+--------------+--------+---------+---------+------------+---------+ 1 row in set mysql> update tabelpegawai set gaji='1250000' where noid='2'; update tabelpegawai set gaji='1500000' where noid='3'; update tabelpegawai set gaji='1750000' where noid='4'; update tabelpegawai set gaji='2000000' where noid='5'; update tabelpegawai set gaji='2250000' where noid='6'; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from tabelpegawai; +------+----------------+--------+---------+---------+------------+---------+ | noid | nama | jenkel | kota | kodepos | tgllahir | gaji | +------+----------------+--------+---------+---------+------------+---------+ | 1 | Ahmad Sobari | L | Bandung | 41011 | 1977-10-02 | 1000000 | | 2 | Sundariwati | P | Bandung | 40123 | 1978-11-12 | 1250000 | | 3 | Ryan Hendrawan | L | Jakarta | 12111 | 1981-03-21 | 1500000 | | 4 | Zulkarman | L | Bekasi | 17211 | 1978-08-10 | 1750000 | | 5 | Yuliawati | P | Bogor | 00000 | 1982-06-09 | 2000000 | | 6 | Mawar | P | Bogor | 12345 | 1985-07-07 | 2250000 | +------+----------------+--------+---------+---------+------------+---------+ 6 rows in set mysql> insert into tabelpegawai(nama,jenkel,kota,kodepos,tgllahir,gaji) values ('Sobari','L','Jakarta','41011','1976-10-02','1100000'), ('Melia','P','Bandung','40123','1979-11-12','1200000'), ('Zanda Cute','L','Jakarta','12111','1980-03-21','1300000'), ('Maman','L','Bekasi','17211','1977-08-10','1400000'), ('Yenny','P','Bogor','00000','1985-06-09','1150000'), ('Rossa','P','Bogor','12345','1987-07-07','1350000'), ('Dadan','L','Bandung','41011','1975-10-02','1450000'), ('Wawan','L','Semarang','40123','1971-11-12','1600000'), ('The Cute','L','Jakarta','12111','1977-03-21','1700000'); Query OK, 9 rows affected Records: 9 Duplicates: 0 Warnings: 0 mysql> select nama,jenkel,tgllahir from tabelpegawai where tgllahir < '1980-01-01' order by nama; +--------------+--------+------------+ | nama | jenkel | tgllahir | +--------------+--------+------------+ | Ahmad Sobari | L | 1977-10-02 | | Dadan | L | 1975-10-02 | | Maman | L | 1977-08-10 | | Melia | P | 1979-11-12 | | Sobari | L | 1976-10-02 | | Sundariwati | P | 1978-11-12 | | The Cute | L | 1977-03-21 | | Wawan | L | 1971-11-12 | | Zulkarman | L | 1978-08-10 | +--------------+--------+------------+ 9 rows in set mysql> select nama,jenkel,tgllahir from tabelpegawai where tgllahir < '1980-01-01' and jenkel='L' order by nama; +--------------+--------+------------+ | nama | jenkel | tgllahir | +--------------+--------+------------+ | Ahmad Sobari | L | 1977-10-02 | | Dadan | L | 1975-10-02 | | Maman | L | 1977-08-10 | | Sobari | L | 1976-10-02 | | The Cute | L | 1977-03-21 | | Wawan | L | 1971-11-12 | | Zulkarman | L | 1978-08-10 | +--------------+--------+------------+ 7 rows in set mysql> select nama,jenkel,tgllahir from tabelpegawai where tgllahir between '1980-01-01' and '1985-12-31' order by nama; +----------------+--------+------------+ | nama | jenkel | tgllahir | +----------------+--------+------------+ | Mawar | P | 1985-07-07 | | Ryan Hendrawan | L | 1981-03-21 | | Yenny | P | 1985-06-09 | | Yuliawati | P | 1982-06-09 | | Zanda Cute | L | 1980-03-21 | +----------------+--------+------------+ 5 rows in set mysql> select nama,jenkel,tgllahir from tabelpegawai where tgllahir between '1980-01-01' and '1985-12-31' and jenkel='L' order by nama; +----------------+--------+------------+ | nama | jenkel | tgllahir | +----------------+--------+------------+ | Ryan Hendrawan | L | 1981-03-21 | | Zanda Cute | L | 1980-03-21 | +----------------+--------+------------+ 2 rows in set mysql> select *,year(curdate())-year(tgllahir) as usia from tabelpegawai; +------+----------------+--------+----------+---------+------------+---------+------+ | noid | nama | jenkel | kota | kodepos | tgllahir | gaji | usia | +------+----------------+--------+----------+---------+------------+---------+------+ | 1 | Ahmad Sobari | L | Bandung | 41011 | 1977-10-02 | 1000000 | 41 | | 2 | Sundariwati | P | Bandung | 40123 | 1978-11-12 | 1250000 | 40 | | 3 | Ryan Hendrawan | L | Jakarta | 12111 | 1981-03-21 | 1500000 | 37 | | 4 | Zulkarman | L | Bekasi | 17211 | 1978-08-10 | 1750000 | 40 | | 5 | Yuliawati | P | Bogor | 00000 | 1982-06-09 | 2000000 | 36 | | 6 | Mawar | P | Bogor | 12345 | 1985-07-07 | 2250000 | 33 | | 7 | Sobari | L | Jakarta | 41011 | 1976-10-02 | 1100000 | 42 | | 8 | Melia | P | Bandung | 40123 | 1979-11-12 | 1200000 | 39 | | 9 | Zanda Cute | L | Jakarta | 12111 | 1980-03-21 | 1300000 | 38 | | 10 | Maman | L | Bekasi | 17211 | 1977-08-10 | 1400000 | 41 | | 11 | Yenny | P | Bogor | 00000 | 1985-06-09 | 1150000 | 33 | | 12 | Rossa | P | Bogor | 12345 | 1987-07-07 | 1350000 | 31 | | 13 | Dadan | L | Bandung | 41011 | 1975-10-02 | 1450000 | 43 | | 14 | Wawan | L | Semarang | 40123 | 1971-11-12 | 1600000 | 47 | | 15 | The Cute | L | Jakarta | 12111 | 1977-03-21 | 1700000 | 41 | +------+----------------+--------+----------+---------+------------+---------+------+ 15 rows in set mysql> select *,year(curdate())-year(tgllahir) as usia from tabelpegawai where tgllahir > '1992-01-01'; Empty set mysql> select * from tabelpegawai where kota='Bandung'; +------+--------------+--------+---------+---------+------------+---------+ | noid | nama | jenkel | kota | kodepos | tgllahir | gaji | +------+--------------+--------+---------+---------+------------+---------+ | 1 | Ahmad Sobari | L | Bandung | 41011 | 1977-10-02 | 1000000 | | 2 | Sundariwati | P | Bandung | 40123 | 1978-11-12 | 1250000 | | 8 | Melia | P | Bandung | 40123 | 1979-11-12 | 1200000 | | 13 | Dadan | L | Bandung | 41011 | 1975-10-02 | 1450000 | +------+--------------+--------+---------+---------+------------+---------+ 4 rows in set mysql> select * from tabelpegawai where not kota in('Bandung','Jakarta','Bekasi') order by kota; +------+-----------+--------+----------+---------+------------+---------+ | noid | nama | jenkel | kota | kodepos | tgllahir | gaji | +------+-----------+--------+----------+---------+------------+---------+ | 5 | Yuliawati | P | Bogor | 00000 | 1982-06-09 | 2000000 | | 6 | Mawar | P | Bogor | 12345 | 1985-07-07 | 2250000 | | 11 | Yenny | P | Bogor | 00000 | 1985-06-09 | 1150000 | | 12 | Rossa | P | Bogor | 12345 | 1987-07-07 | 1350000 | | 14 | Wawan | L | Semarang | 40123 | 1971-11-12 | 1600000 | +------+-----------+--------+----------+---------+------------+---------+ 5 rows in set mysql> select * from tabelpegawai where not kota in('Bandung','Jakarta','Bekasi') order by kota,nama; +------+-----------+--------+----------+---------+------------+---------+ | noid | nama | jenkel | kota | kodepos | tgllahir | gaji | +------+-----------+--------+----------+---------+------------+---------+ | 6 | Mawar | P | Bogor | 12345 | 1985-07-07 | 2250000 | | 12 | Rossa | P | Bogor | 12345 | 1987-07-07 | 1350000 | | 11 | Yenny | P | Bogor | 00000 | 1985-06-09 | 1150000 | | 5 | Yuliawati | P | Bogor | 00000 | 1982-06-09 | 2000000 | | 14 | Wawan | L | Semarang | 40123 | 1971-11-12 | 1600000 | +------+-----------+--------+----------+---------+------------+---------+ 5 rows in set mysql> select * from tabelpegawai where gaji between 1500000 and 2500000 order by gaji,nama; +------+----------------+--------+----------+---------+------------+---------+ | noid | nama | jenkel | kota | kodepos | tgllahir | gaji | +------+----------------+--------+----------+---------+------------+---------+ | 3 | Ryan Hendrawan | L | Jakarta | 12111 | 1981-03-21 | 1500000 | | 14 | Wawan | L | Semarang | 40123 | 1971-11-12 | 1600000 | | 15 | The Cute | L | Jakarta | 12111 | 1977-03-21 | 1700000 | | 4 | Zulkarman | L | Bekasi | 17211 | 1978-08-10 | 1750000 | | 5 | Yuliawati | P | Bogor | 00000 | 1982-06-09 | 2000000 | | 6 | Mawar | P | Bogor | 12345 | 1985-07-07 | 2250000 | +------+----------------+--------+----------+---------+------------+---------+ 6 rows in set mysql> select count(gaji) from tabelpegawai where gaji < '2000000'; +-------------+ | count(gaji) | +-------------+ | 13 | +-------------+ 1 row in set mysql> select avg(gaji) from tabelpegawai; +--------------+ | avg(gaji) | +--------------+ | 1466666.6667 | +--------------+ 1 row in set mysql> select max(gaji) from tabelpegawai; +-----------+ | max(gaji) | +-----------+ | 2250000 | +-----------+ 1 row in set mysql> select min(gaji) from tabelpegawai; +-----------+ | min(gaji) | +-----------+ | 1000000 | +-----------+ 1 row in set mysql> select sum(gaji) from tabelpegawai; +-----------+ | sum(gaji) | +-----------+ | 22000000 | +-----------+ 1 row in set mysql> select * from tabelpegawai where nama like 'a%'; +------+--------------+--------+---------+---------+------------+---------+ | noid | nama | jenkel | kota | kodepos | tgllahir | gaji | +------+--------------+--------+---------+---------+------------+---------+ | 1 | Ahmad Sobari | L | Bandung | 41011 | 1977-10-02 | 1000000 | +------+--------------+--------+---------+---------+------------+---------+ 1 row in set mysql> select noid,nama from tabelpegawai where nama like 'd%'; +------+-------+ | noid | nama | +------+-------+ | 13 | Dadan | +------+-------+ 1 row in set mysql> select noid,nama from tabelpegawai where nama like '%i'; +------+--------------+ | noid | nama | +------+--------------+ | 1 | Ahmad Sobari | | 2 | Sundariwati | | 5 | Yuliawati | | 7 | Sobari | +------+--------------+ 4 rows in set mysql> select noid,nama from tabelpegawai where nama like '%wati'; +------+-------------+ | noid | nama | +------+-------------+ | 2 | Sundariwati | | 5 | Yuliawati | +------+-------------+ 2 rows in set mysql> select noid,nama from tabelpegawai where nama like '%lia%'; +------+-----------+ | noid | nama | +------+-----------+ | 5 | Yuliawati | | 8 | Melia | +------+-----------+ 2 rows in set
========================================================================
Tidak ada komentar:
Posting Komentar