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;

CREATE DATABASE ngampus
on primary
(
Name= 'ngampus_data',
filename= 'D:\Hani IK201\ngampus.mdf',
size = 5MB,
maxsize = Unlimited,
Filegrowth = 10MB
)
log on
(
Name= 'ngampus_log',
filename= 'D:\Hani IK201\ngampus.log',
size = 5MB,
maxsize = Unlimited,
Filegrowth = 10MB
)
use ngampus;

create table matakuliah(
kd_nik varchar (5) primary key not null,
nm_mtkl varchar (30),
sks int
);


--------

create table kuliah(
kd_kl varchar (5) primary key not null,
kd_nik varchar (5),
nip int,
thnakademik varchar (20),
ruang varchar (15),
jam time,
semester varchar (10)
foreign key (kd_nik) references matakuliah(kd_nik),
foreign key (nip) references dosen(nip)
);


-----

create table peserta(
kd_kl varchar (5),
nim int,
nilai int
foreign key (kd_kl) references kuliah(kd_kl),
foreign key (nim) references mahasiswa (nim)
);


-----
create table dosen(
nip int primary key not null,
nama varchar (25)
);


create table mahasiswa(
nim int primary key not null,
nama varchar (25),
ds_pmb int
foreign key (ds_pmb)references dosen (nip)
);


select * from kuliah
select * from matakuliah
select * from peserta
select * from mahasiswa
select * from dosen

create proc add_matakuliah
@kd_Nik varchar(5),
@nm_mtkl varchar(30),
@sks int
as insert into matakuliah
values(@kd_Nik,@nm_mtkl,@sks);

exec add_matakuliah 'M01','Database',2;
exec add_matakuliah 'M02','Website Desain',4;
exec add_matakuliah 'M03','Agama',2;

select * from matakuliah;

create proc add_dosen
@nip int,
@nama varchar(25)
as insert into dosen
values(@nip,@nama);

exec add_dosen '121','Aris';
exec add_dosen '122','Firdaus';
exec add_dosen '123','Fauzan';

select * from dosen;

create proc add_kuliah
@kd_kl varchar(5),
@kd_nik varchar(5),
@nip int,
@thnakademik varchar(20),
@ruang varchar(15),
@jam time(7),
@semester varchar(10)
as insert into kuliah
values(@kd_kl,@kd_nik,@nip,@thnakademik,@ruang,@jam,@semester);

exec add_kuliah 'K0111','M01',121,'2016/2017','INTEL','08:00:01','2';
exec add_kuliah 'K0112','M02',122,'2016/2017','AMD','13:00:01','2';
exec add_kuliah 'K0113','M03',123,'2017/2018','AULA','15:00:01','2';

select * from kuliah;

create proc add_mahasiswa
@nim int,
@nama varchar(25),
@ds_pmb int
as insert into mahasiswa
values(@nim,@nama,@ds_pmb);

exec add_mahasiswa '160442','Aziz',121;
exec add_mahasiswa '160443','Hani',122;
exec add_mahasiswa '160444','Aris',123;

select * from mahasiswa;

create proc add_peserta
@kd_kl varchar(5),
@nim int,
@nilai int
as insert into peserta
values(@kd_kl,@nim,@nilai);

exec add_peserta 'K0111',160442,90;
exec add_peserta 'K0112',160443,80;
exec add_peserta 'K0113',160444,75;


-----------------------

/*soal no1 (buat procedure lihat_mahasiswa, menampilkan nama berdasarkan NIM)*/
create procedure lihat_mahasiswa
@nim int
as select * from mahasiswa where nim = @nim;

exec lihat_mahasiswa '160442';
-----------------

/*soal no2 (buat procedure lihat_dosen ,berdasarkan nip dosen)*/
create procedure lihat_dosen
@nip int
as select * from dosen where nip = @nip;

exec lihat_dosen '121';
--------------


/*soal no3 (buat procedure lihat_matkul, berdasarkan sks)*/
create procedure lihat_matkul
@sks int
as select * from matakuliah where sks = @sks;

exec lihat_matkul '4'
--------------

/*soal no4 (buat procedure lihat_ruang, berdasarkan ruang)*/
create procedure lihat_ruang
@ruang varchar(15)
as select * from kuliah where ruang = @ruang;

exec lihat_ruang 'AMD'
--------------

use ngampus;

select * from kuliah
select * from matakuliah
select * from peserta
select * from mahasiswa
select * from dosen

/* 1 inner join */
SELECT DSN.nip, DSN.nama, KLH.semester
FROM dosen DSN
INNER JOIN kuliah KLH
ON DSN.nip = KLH.nip;

/* 2 outer join */
SELECT DSN.nip, DSN.nama, KLH.semester
FROM dosen DSN, kuliah KLH
WHERE DSN.nip = KLH.nip;

/*3. left outer join */
SELECT DSN.nip, DSN.nama, KLH.semester
FROM dosen DSN
LEFT OUTER JOIN kuliah KLH
ON DSN.nip = KLH.nip;

/*4. cross join */
select DSN.nip, DSN.nama, KLH.semester
from dosen DSN, kuliah KLH;

/*5. SELECT UNIQUE */
select distinct thnakademik from kuliah;


/*6. ROUND FUNCTUION */

create table student_rating(
studentid int primary key,
first_name char(20),
rating float
)

insert into student_rating values(1,'Jenny',85.235)
insert into student_rating values(2,'Bob',95.25)
insert into student_rating values(3,'Alice',3.9)
insert into student_rating values(4,'James',120.1)

SELECT first_Name, ROUND (rating, 1) rounded_score FROM student_rating;

SELECT First_Name, ROUND(Rating, -1) Rounded_Score FROM Student_Rating;


alter table student_rating add score float;
update student_rating set score=85.235 where studentid=1;
update student_rating set score=95.25 where studentid=2;
update student_rating set score=3.9 where studentid=3;
update student_rating set score=120.1 where studentid=4;

/* 7.CAST Function */
SELECT First_Name, CAST(Score AS Integer) Int_Score FROM Student_Rating;
SELECT First_Name, CAST(Score AS float) Char_Score FROM Student_Rating;

/* 8.CONVERT Function */
SELECT First_Name, CONVERT(integer, score) Int_Score FROM Student_Rating;

/* 9.CONVERT Function */
SELECT {fn CONCAT(kd_kl, kd_nik)} as Kodekode  FROM kuliah;

/* 10.SUBSTRING */
SELECT SUBSTRING (nama, 6, 7) FROM dosen WHERE nama = 'Firdaus';

/* 11.INSTR Function */
SELECT CHARINDEX(nama,'r') FROM dosen WHERE nama = 'Firdaus';

/* 12.TRIM */
SELECT TRIM(' Sample ');

/* 13.Length Function */
SELECT DataLength (nama) FROM dosen WHERE nama = 'Fauzan';

/* 14.Replace Function */
SELECT REPLACE (nama, 'ziz', 'anjas') nama FROM mahasiswa;