Tugas MySQL B2

Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.45-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
Database
+--------------------+
information_schema
mysql
padati_2007
padati_2007_plb
padati_2007_pls
padati_2007_pt
padati_2007_sd
padati_2007_sma
padati_2007_smk
padati_2007_smp
padati_2007_tk
perkuliahan
personalia
+--------------------+
13 rows in set (0.34 sec)


mysql> create table penggolongan
-> (
-> Golongan char,
-> Gaji_Pokok int,
-> Honor_per_jam int,
-> Tunjangan int,
-> primary key(Golongan)
-> );
Query OK, 0 rows affected (0.08 sec)


mysql> create table data_karyawan
-> (
-> NIK varchar(6),
-> Nama varchar(25),
-> Tgl_Lahir date,
-> Alamat varchar(30),
-> Kota varchar(10),
-> Tgl_Masuk date,
-> primary key(NIK)
-> );
Query OK, 0 rows affected (0.03 sec)

mysql> create table perhitungan
-> (
-> NIK varchar(6),
-> Golongan char,
-> Jumlah_jam_kerja int
-> );
Query OK, 0 rows affected (0.05 sec)

mysql> show tables;
+----------------------+
Tables_in_personalia
+----------------------+
data_karyawan
penggolongan
perhitungan
+----------------------+
3 rows in set (0.00 sec)

mysql> desc penggolongan;
+---------------+---------+------+-----+---------+-------+
Field Type Null Key Default Extra
+---------------+---------+------+-----+---------+-------+
Golongan char(1) NO PRI
Gaji_Pokok int(11) YES NULL
Honor_per_jam int(11) YES NULL
Tunjangan int(11) YES NULL
+---------------+---------+------+-----+---------+-------+
4 rows in set (0.03 sec)

mysql> desc data_karyawan;
+-----------+-------------+------+-----+---------+-------+
Field Type Null Key Default Extra
+-----------+-------------+------+-----+---------+-------+
NIK varchar(6) NO PRI
Nama varchar(25) YES NULL
Tgl_Lahir date YES NULL
Alamat varchar(30) YES NULL
Kota varchar(10) YES NULL
Tgl_Masuk date YES NULL
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> desc perhitungan;
+------------------+------------+------+-----+---------+-------+
Field Type Null Key Default Extra
+------------------+------------+------+-----+---------+-------+
NIK varchar(6) YES NULL
Golongan char(1) YES NULL
Jumlah_jam_kerja int(11) YES NULL
+------------------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into penggolongan values
-> ('A','1500000','10000','200000'),
-> ('B','1200000','8000','230000'),
-> ('C','1000000','7500','250000'),
-> ('D','750000','5000','280000'),
-> ('E','500000','4500','300000');
Query OK, 5 rows affected (0.03 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> insert into data_karyawan values
-> ('K-001','Nakula','1973-02-02','Jl.A.Yani 13','Bandung','1992-04-12'),
-> ('K-002','Gayatri','1966-01-16','Jl.Kelinci 21b','Jakarta','1987-06-13'),

-> ('K-003','Ramayana','1982-04-21','Jl.Gangga 05','Bandung','2006-01-01'),
-> ('K-004','Sadewa','1977-07-13','Jl.Jambu 3A','Bogor','1995-03-05'),
-> ('K-005','Dewi Sinta','1984-05-30','Jl.Petung 11B','Bogor','2008-04-21')
-> ;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> insert into perhitungan values
-> ('K-001','B','37'),
-> ('K-002','A','48'),
-> ('K-003','C','23'),
-> ('K-004','A','36'),
-> ('K-005','D','30')
-> ;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select*from penggolongan;
+----------+------------+---------------+-----------+
Golongan Gaji_Pokok Honor_per_jam Tunjangan
+----------+------------+---------------+-----------+
A 1500000 10000 200000
B 1200000 8000 230000
C 1000000 7500 250000
D 750000 5000 280000
E 500000 4500 300000
+----------+------------+---------------+-----------+
5 rows in set (0.02 sec)

mysql> select*from data_karyawan;
+-------+------------+------------+----------------+---------+------------+
NIK Nama Tgl_Lahir Alamat Kota Tgl_Masuk
+-------+------------+------------+----------------+---------+------------+
K-001 Nakula 1973-02-02 Jl.A.Yani 13 Bandung 1992-04-12
K-002 Gayatri 1966-01-16 Jl.Kelinci 21b Jakarta 1987-06-13
K-003 Ramayana 1982-04-21 Jl.Gangga 05 Bandung 2006-01-01
K-004 Sadewa 1977-07-13 Jl.Jambu 3A Bogor 1995-03-05
K-005 Dewi Sinta 1984-05-30 Jl.Petung 11B Bogor 2008-04-21
+-------+------------+------------+----------------+---------+------------+
5 rows in set (0.00 sec)

mysql> select*from perhitungan;
+-------+----------+------------------+
NIK Golongan Jumlah_jam_kerja
+-------+----------+------------------+
K-001 B 37
K-002 A 48
K-003 C 23
K-004 A 36
K-005 D 30
+-------+----------+------------------+
5 rows in set (0.00 sec)


mysql> alter table data_karyawan add status varchar(9) not null;
Query OK, 5 rows affected (0.05 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select*from data_karyawan;
+-------+------------+------------+----------------+---------+------------+-----
---+
NIK Nama Tgl_Lahir Alamat Kota Tgl_Masuk stat
us
+-------+------------+------------+----------------+---------+------------+-----
---+
K-001 Nakula 1973-02-02 Jl.A.Yani 13 Bandung 1992-04-12

K-002 Gayatri 1966-01-16 Jl.Kelinci 21b Jakarta 1987-06-13

K-003 Ramayana 1982-04-21 Jl.Gangga 05 Bandung 2006-01-01

K-004 Sadewa 1977-07-13 Jl.Jambu 3A Bogor 1995-03-05

K-005 Dewi Sinta 1984-05-30 Jl.Petung 11B Bogor 2008-04-21

+-------+------------+------------+----------------+---------+------------+-----
---+
5 rows in set (0.00 sec)

mysql> update data_karyawan
-> set status='menikah'
-> where NIK='K-001' or NIK='K-004' or NIK='K-005';
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> update data_karyawan
-> set status='single'
-> where NIK='K-002' or NIK='K-003';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> select*from data_karyawan;
+-------+------------+------------+----------------+---------+------------+-----
----+
NIK Nama Tgl_Lahir Alamat Kota Tgl_Masuk stat
us
+-------+------------+------------+----------------+---------+------------+-----
----+
K-001 Nakula 1973-02-02 Jl.A.Yani 13 Bandung 1992-04-12 meni
kah
K-002 Gayatri 1966-01-16 Jl.Kelinci 21b Jakarta 1987-06-13 sing
le
K-003 Ramayana 1982-04-21 Jl.Gangga 05 Bandung 2006-01-01 sing
le
K-004 Sadewa 1977-07-13 Jl.Jambu 3A Bogor 1995-03-05 meni
kah
K-005 Dewi Sinta 1984-05-30 Jl.Petung 11B Bogor 2008-04-21 meni
kah
+-------+------------+------------+----------------+---------+------------+-----
----+
5 rows in set (0.00 sec)

mysql> alter table penggolongan
-> change Golongan Gol_Kerja char;
Query OK, 5 rows affected (0.03 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select*from penggolongan;
+-----------+------------+---------------+-----------+
Gol_Kerja Gaji_Pokok Honor_per_jam Tunjangan
+-----------+------------+---------------+-----------+
A 1500000 10000 200000
B 1200000 8000 230000
C 1000000 7500 250000
D 750000 5000 280000
E 500000 4500 300000
+-----------+------------+---------------+-----------+
5 rows in set (0.00 sec)

mysql> select NIK, Nama, Tgl_Lahir, Alamat, Kota, Tgl_Masuk, status
-> from data_karyawan
-> where Nama like '_a%' and
-> Tgl_Lahir between '1970-01-01' and '1979-12-31' and
-> Kota not in('Bogor');
+-------+--------+------------+--------------+---------+------------+---------+
NIK Nama Tgl_Lahir Alamat Kota Tgl_Masuk status
+-------+--------+------------+--------------+---------+------------+---------+
K-001 Nakula 1973-02-02 Jl.A.Yani 13 Bandung 1992-04-12 menikah

+-------+--------+------------+--------------+---------+------------+---------+
1 row in set (0.00 sec)





mysql> select NIK, Nama, Tgl_lahir, Alamat, Kota, Tgl_Masuk, status
-> from data_karyawan
-> where status='Menikah' or not status='single'
-> order by Tgl_lahir;
+-------+------------+------------+---------------+---------+------------+------
---+
NIK Nama Tgl_lahir Alamat Kota Tgl_Masuk statu
s
+-------+------------+------------+---------------+---------+------------+------
---+
K-001 Nakula 1973-02-02 Jl.A.Yani 13 Bandung 1992-04-12 menik
ah
K-004 Sadewa 1977-07-13 Jl.Jambu 3A Bogor 1995-03-05 menik
ah
K-005 Dewi Sinta 1984-05-30 Jl.Petung 11B Bogor 2008-04-21 menik
ah
+-------+------------+------------+---------------+---------+------------+------
---+
3 rows in set (0.00 sec)



mysql> select distinct Kota
-> from data_karyawan
-> where Kota not in ('Jakarta')
-> order by Kota;
+---------+
Kota
+---------+
Bandung
Bogor
+---------+
2 rows in set (0.00 sec)

Klik di Sini tuk Baca Selengkapnya......