create database Perpustakaan
Query OK, 1 row affected (0.02 sec)
use perpustakaan
Database changed
--> soal 1
create table Petugas_Perpus(
Id_petugas varchar (7) primary key not null,
Nama_petugas varchar (50) not null
);
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| Id_petugas | varchar(7) | NO | PRI | NULL | |
| Nama_petugas | varchar(50) | NO | | NULL | |
+--------------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
create table peminjam (
id_peminjam varchar (7) primary key not null,
nama_peminjam varchar (50) not null,
jurusan varchar (30) not null
);
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| id_peminjam | varchar(7) | NO | PRI | NULL | |
| nama_peminjam | varchar(50) | NO | | NULL | |
| jurusan | varchar(30) | NO | | NULL | |
+---------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
create table Buku(
Kd_buku varchar (7) primary key not null,
Judul varchar (40) not null,
Pengarang varchar (20) not null,
Tahun_terbit int (11) not null
);
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| Kd_buku | varchar(7) | NO | PRI | NULL | |
| Judul | varchar(40) | NO | | NULL | |
| Pengarang | varchar(20) | NO | | NULL | |
| Tahun_terbit | int(11) | NO | | NULL | |
+--------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
create table peminjaman (
id_peminjaman int (11) auto_increment primary key not null,
id_peminjam varchar (7) not null,
id_petugas varchar (7) not null,
kd_buku varchar (7) not null,
tgl_pinjam timestamp not null,
foreign key(id_peminjam) references peminjam(id_peminjam),
foreign key(id_petugas) references petugas_perpus(id_petugas),
foreign key(kd_buku) references buku(kd_buku)
);
+---------------+------------+------+-----+-------------------+-----------------
------------+
| Field | Type | Null | Key | Default | Extra
|
+---------------+------------+------+-----+-------------------+-----------------
------------+
| id_peminjaman | int(11) | NO | PRI | NULL | auto_increment
|
| id_peminjam | varchar(7) | NO | MUL | NULL |
|
| id_petugas | varchar(7) | NO | MUL | NULL |
|
| kd_buku | varchar(7) | NO | MUL | NULL |
|
| tgl_pinjam | timestamp | NO | | CURRENT_TIMESTAMP | on update CURREN
T_TIMESTAMP |
+---------------+------------+------+-----+-------------------+-----------------
------------+
5 rows in set (0.01 sec)
-->soal 2
insert into petugas_perpus
(id_petugas, nama_petugas) values
('KP001','Jujun'),
('KP002','Jejen'),
('KP003','Jajang');
+------------+--------------+
| Id_petugas | Nama_petugas |
+------------+--------------+
| KP001 | Jujun |
| KP002 | Jejen |
| KP003 | Jajang |
+------------+--------------+
3 rows in set (0.00 sec)
insert into peminjam
(id_pemijam, nama_peminjam, jurusan)values
('MP001','Sasra','Ilmu Komputer'),
('MP002','Sisri','Pendidikan Ilmu Komputer'),
('MP003','Sosro','Ilmu Komputer');
mysql> select *from peminjam;
+-------------+---------------+--------------------------+
| id_peminjam | nama_peminjam | jurusan |
+-------------+---------------+--------------------------+
| MP001 | Sasra | Ilmu Komputer |
| MP002 | Sisri | Pendidikan Ilmu Komputer |
| MP003 | Sosro | Ilmu Komputer |
+-------------+---------------+--------------------------+
3 rows in set (0.00 sec)
insert into Buku
(kd_buku,judul,pengarang,tahun_terbit) values
('BK011','Saya suka Basis Data','Candra','2004'),
('BK012','Aku suka Kamu, tapi bohong','Candra','2004'),
('BK013','SAku dan Dia memilihmu','Cokro','2005'),
('BK014','Belajar Ngoding','Kaula','2011'),
('BK015','Who am I','Jackie','2008'),
('BK016','I have no live','Kaula','2012');
+---------+----------------------------+-----------+--------------+
| Kd_buku | Judul | Pengarang | Tahun_terbit |
+---------+----------------------------+-----------+--------------+
| BK011 | Saya suka Basis Data | Candra | 2004 |
| BK012 | Aku suka Kamu, tapi bohong | Candra | 2004 |
| BK013 | SAku dan Dia memilihmu | Cokro | 2005 |
| BK014 | Belajar Ngoding | Kaula | 2011 |
| BK015 | Who am I | Jackie | 2008 |
| BK016 | I have no live | Kaula | 2012 |
+---------+----------------------------+-----------+--------------+
6 rows in set (0.00 sec)
insert into peminjaman
(id_peminjaman,id_peminjam,id_petugas,kd_buku,tgl_pinjam)values
(' ','MP001','KP002','BK012',NULL),
(' ','MP002','KP003','BK015',NULL),
(' ','MP001','KP003','BK011',NULL),
(' ','MP003','KP001','BK014',NULL),
(' ','MP002','KP001','BK013',NULL),
(' ','MP003','KP002','BK011',NULL);
+---------------+-------------+------------+---------+---------------------+
| id_peminjaman | id_peminjam | id_petugas | kd_buku | tgl_pinjam |
+---------------+-------------+------------+---------+---------------------+
| 1 | MP001 | KP002 | BK012 | 2014-03-11 18:40:20 |
| 2 | MP002 | KP003 | BK015 | 2014-03-11 18:40:20 |
| 3 | MP001 | KP003 | BK011 | 2014-03-11 18:40:20 |
| 4 | MP003 | KP001 | BK014 | 2014-03-11 18:40:20 |
| 5 | MP002 | KP001 | BK013 | 2014-03-11 18:40:20 |
| 6 | MP003 | KP002 | BK011 | 2014-03-11 18:40:20 |
+---------------+-------------+------------+---------+---------------------+
6 rows in set (0.01 sec)
-->soal 4
select a.nama_petugas, b.judul, c.nama_peminjam from petugas_perpus a, buku b, peminjam c, peminjaman d where d.id_peminjam=c.id_peminjam and d.id_petugas=a.id_petugas and
b.tahun_terbit between 2008 and 2013;
+--------------+-----------------+---------------+
| nama_petugas | judul | nama_peminjam |
+--------------+-----------------+---------------+
| Jejen | Belajar Ngoding | Sasra |
| Jajang | Belajar Ngoding | Sasra |
| Jujun | Belajar Ngoding | Sisri |
| Jajang | Belajar Ngoding | Sisri |
| Jujun | Belajar Ngoding | Sosro |
| Jejen | Belajar Ngoding | Sosro |
| Jejen | Who am I | Sasra |
| Jajang | Who am I | Sasra |
| Jujun | Who am I | Sisri |
| Jajang | Who am I | Sisri |
| Jujun | Who am I | Sosro |
| Jejen | Who am I | Sosro |
| Jejen | I have no live | Sasra |
| Jajang | I have no live | Sasra |
| Jujun | I have no live | Sisri |
| Jajang | I have no live | Sisri |
| Jujun | I have no live | Sosro |
| Jejen | I have no live | Sosro |
+--------------+-----------------+---------------+
18 rows in set (0.00 sec)
-->soal 5
select nama_petugas from petugas_perpus a, peminjam b, peminjaman c where c.id_petugas=a.id_petugas and c.id_peminjam=b.id_peminjam and nama_peminjam='sosro';
+--------------+
| nama_petugas |
+--------------+
| Jujun |
| Jejen |
+--------------+
2 rows in set (0.00 sec)
-->soal 6
select nama_peminjam from peminjam a, buku b, peminjaman c where c.id_peminjam=a.id_peminjam and c.kd_buku=b.kd_buku and b.judul like '%ta%';
+---------------+
| nama_peminjam |
+---------------+
| Sasra |
| Sasra |
| Sosro |
+---------------+
3 rows in set (0.00 sec)
-->soal 7
select nama_peminjam, jurusan from peminjam a, petugas_perpus b, peminjaman c where c.id_peminjam=a.id_peminjam and
c.id_petugas=b.id_petugas and nama_petugas='jejen';
+---------------+---------------+
| nama_peminjam | jurusan |
+---------------+---------------+
| Sasra | Ilmu Komputer |
| Sosro | Ilmu Komputer |
+---------------+---------------+
2 rows in set (0.01 sec)
-->soal 8
select nama_peminjam, judul from peminjam a, buku b, peminjaman c where c.id_peminjam=a.id_peminjam and c.kd_buku=b.kd_buku and judul like '%suka%';
+---------------+----------------------------+
| nama_peminjam | judul |
+---------------+----------------------------+
| Sasra | Aku suka Kamu, tapi bohong |
| Sasra | Saya suka Basis Data |
| Sosro | Saya suka Basis Data |
+---------------+----------------------------+
3 rows in set (0.00 sec)
-->soal 9
update buku set pengarang='I am Programmer' where pengarang='kaula';
+---------+----------------------------+-----------------+--------------+
| Kd_buku | Judul | Pengarang | Tahun_terbit |
+---------+----------------------------+-----------------+--------------+
| BK011 | Saya suka Basis Data | Candra | 2004 |
| BK012 | Aku suka Kamu, tapi bohong | Candra | 2004 |
| BK013 | SAku dan Dia memilihmu | Cokro | 2005 |
| BK014 | Belajar Ngoding | I am Programmer | 2011 |
| BK015 | Who am I | Jackie | 2008 |
| BK016 | I have no live | I am Programmer | 2012 |
+---------+----------------------------+-----------------+--------------+
6 rows in set (0.00 sec)
-->soal 10
Tidak ada komentar:
Posting Komentar