Rabu, 19 Maret 2014

Tugas Praktikum 02 Basis Data

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