Myanimeanalysis

create database db_perpustakaan
use db_perpustakaan



create table Buku(
Id_Buku varchar(5) primary key not null,
Judul_Buku varchar(50),
Penerbit varchar (50),
Karangan varchar(50),
Harga int,
Qty int,
Jumlah int,
Id_Kategori varchar (5)
);


insert into Buku values('P001','Komputer untuk perkantoran Modern','Rony Setiawan','Hani','55000','100','5000000','A001');
insert into Buku values('P002','Naruto Shippuden','Zein Achmad','Haerudin','40000','50','2000000','A002');
insert into Buku values('P003','Putri Salju','Masria','Asirun','30000','50','1500000','A003');

select * from Buku
select Karangan From Buku;


create table Kategori(
Id_Kategori varchar(5) primary key not null,
Nama_Kategori varchar(50)
)

insert into Kategori values('K001','Komik');
insert into Kategori values('K002','Informatika');
insert into Kategori values('K003','Dongeng');

select * from Kategori
select Nama_Kategori From Kategori;


create table Peminjaman(
Id_Pinjam varchar(5) primary key not null,
Id_Buku varchar (5),
Tgl_Pinjam datetime,
Tgl_Kembali datetime,
Id_Anggota varchar(5)
);

insert into Peminjaman values ('T001',

select Tgl_Pinjam From Peminjaman;


create table Pengembalian(
Id_Pengembalian varchar (5) primary key not null,
Tgl_Kembali datetime,
Denda int,
Id_Pinjam varchar (5),
Id_Anggota varchar(5)
);

insert into Pengembalian values ('PE001','2018-29-03','1000','T001','A001')
insert into Pengembalian values ('PE001','2018-27-03','1000','T002','A002')
insert into Pengembalian values ('PE001','2018-25-03','1000','T003','A003')

select from Pengembalian

create table Anggota(
Id_Anggota varchar(5) primary key not null,
Nama_Anggota varchar (30),
Alamat varchar (30)
);

insert into Anggota values ('AG001','Aziz','Bogor')
insert into Anggota values ('AG002','Ahmad','Bogor')
insert into Anggota values ('AG003','Fauzan','Bogor')

select * from Anggota

select Nama_Anggota,Alamat from Anggota;


select distinct Nama_Anggota from Anggota;

select * from Buku where Id_Buku='P002';

select * from Buku where Id_Buku='P002' or Judul_Buku='Naruto Shippuden';

select * from Buku where Id_Buku='P002' and Judul_Buku='Naruto Shippuden';

select * from Buku where Karangan in ('Hani','Haerudin');

select * from Buku where Harga between '55000' and '40000';

select * from Buku where Harga not between '40000' and '55000';

select * from Buku where Penerbit like '%am%';

select * from Buku order by Harga desc;

select Id_Buku,sum(Harga) as Jumlah from Buku group by Id_Buku having sum(tarif)>50000;

SELECT .Id_Buku, SUM(BK.Harga) AS "Jumlah"
FROM Buku AS BK
GROUP BY BK.Id_Buku;