MY SQL

TUGAS MANAJEMEN BASIS DATA QUERY DI MY SQL

Disusun Oleh : INGGIT NADYA NASTITI 160101243

STMIK DUTA BANGSA SURAKARTA 2018 -------------------------------------------------------------------------- --------------------------------------------------------------------------

LATIHAN SOAL

mysql> create table anggota (
    -> id varchar(3) primary key,
    -> Nama varchar(50),
    -> Alamat varchar(50),
    -> gaji int(7));
Query OK, 0 rows affected

mysql> desc anggota;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | varchar(3)  | NO   | PRI | NULL    |       |
| Nama   | varchar(50) | YES  |     | NULL    |       |
| Alamat | varchar(50) | YES  |     | NULL    |       |
| gaji   | int(7)      | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set

mysql> insert into anggota (id,Nama,Alamat,gaji)
    -> values
    -> ('100','Arif','Surabaya','10000'),
    -> ('101','Andi','Jakarta','14000'),
    -> ('102','Burhan','Malang','12000'),
    -> ('103','Fikri','Madiiun','15000'),
    -> ('104','Fariz','Malang','17000'),
    -> ('105','Sigiti','Surabaya','20000'),
    -> ('106','Ifan','Kediri','16000'),
    -> ('107','Hanif','Yogyakarta','12000'),
    -> ('108','Zakiudin','Surabaya','21000');
Query OK, 9 rows affected
Records: 9  Duplicates: 0  Warnings: 0


mysql> select count(*) from anggota;
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1 row in set

mysql> select sum(gaji) from anggota;
+-----------+
| sum(gaji) |
+-----------+
| 137000    |
+-----------+
1 row in set

mysql> select max(gaji), min(gaji) from anggota;
+-----------+-----------+
| max(gaji) | min(gaji) |
+-----------+-----------+
|     21000 |     10000 |
+-----------+-----------+
1 row in set

mysql> select*from anggota where nama like '__r%';
+-----+--------+--------+-------+
| id  | Nama   | Alamat | gaji  |
+-----+--------+--------+-------+
| 102 | Burhan | Malang | 12000 |
| 104 | Fariz  | Malang | 17000 |
+-----+--------+--------+-------+
2 rows in set

mysql> select sum(gaji) from anggota where alamat='Surabaya';
+-----------+
| sum(gaji) |
+-----------+
| 51000     |
+-----------+
1 row in set

mysql> select max(gaji) from anggota where alamat='Surabaya';
+-----------+
| max(gaji) |
+-----------+
|     21000 |
+-----------+
1 row in set

mysql> select count(id) from anggota where gaji between '17000' and '25000';

+-----------+
| count(id) |
+-----------+
|         3 |
+-----------+
1 row in set

mysql> select sum(gaji) from anggota where nama like '__r%';
+-----------+
| sum(gaji) |
+-----------+
| 29000     |
+-----------+
1 row in set


mysql> select*from anggota;
+-----+----------+------------+-------+
| id  | Nama     | Alamat     | gaji  |
+-----+----------+------------+-------+
| 100 | Arif     | Surabaya   | 10000 |
| 101 | Andi     | Jakarta    | 14000 |
| 102 | Burhan   | Malang     | 12000 |
| 103 | Fikri    | Madiiun    | 15000 |
| 104 | Fariz    | Malang     | 17000 |
| 105 | Sigiti   | Surabaya   | 20000 |
| 106 | Ifan     | Kediri     | 16000 |
| 107 | Hanif    | Yogyakarta | 12000 |
| 108 | Zakiudin | Surabaya   | 21000 |
+-----+----------+------------+-------+
9 rows in set