Basilus 10

PT KAII merupakan perusahaan kereta api yang berenacana untuk mengubah sistem nya menjadi sistem database. Akan disimpan data kereta dan Stasiun. Data kereta meliputi nama dan kelas kereta. Data stasiun akan disimpan nama stasiun, kota dan provinsi.
Terdapat Jadwal kereta yang akan disimpan yaitu idJadwal tanggal, id_kereta, stasiunBerangkat, stasiunTiba, jamBerangkat dan jamTiba.
Untuk setiap jadwal, terdapat HargaTiket yang bervariasi. Data yang disimpan yaitu idHargaTiket, idJadwal, jenisPenumpang(Dewasa/Anak) dan hargaTiket

Jawabannya adalah

create database PT_KAII
use PT_KAII;
create table KERETA(
id_kereta char(5) not null primary key constraint ck_id check(id_kereta like 'k%'),
nama varchar(50) not null,
kelas_kereta varchar(20)
);
create table STASIUN(
id_stasiun char(5) not null primary key constraint ck_st_id check(id_stasiun like 's%'),
nama varchar(50) not null,
kota varchar(50) not null,
provinsi varchar(50) not null
);
create table JADWAL(
idJadwal char(5) not null primary key constraint ck_id_jdwal check(idJadwal like 'j%'),
idKereta char(5) not null,
tanggal smalldatetime,
idStasiunBerangkat char(5) not null,
idStasiunTiba char(5) not null,
jamBerangkat varchar(15),
jamTiba varchar(15)
);
create table HARGATIKET(
idHargaTiket char(5) not null primary key constraint ck_idHargaTiket check(idHargaTiket like 'H%'),
idJadwal char(5),
jenisPenumpang varchar(50) not null,
hargaTiket int not null
);
create table PEMESANAN(
idHargaTiket char(5) not null,
noBangku varchar(5),
gerbong char(1),
namaPenumpang varchar(50),
primary key(idHargaTiket,noBangku,gerbong)
);





alter table JADWAL add foreign key(idKereta) references KERETA(id_kereta);
alter table JADWAL add foreign key(idStasiunBerangkat) references STASIUN(id_stasiun);
alter table JADWAL add foreign key(idStasiunTiba) references STASIUN(id_stasiun);

alter table HARGATIKET add foreign key(idJadwal) references JADWAL(idJadwal);

alter table PEMESANAN add foreign key(idHargaTiket) references HARGATIKET(idHargaTiket);
============================================================================================

insert into KERETA values ('k001','Argo Bromo','Ekonomi')
insert into KERETA values ('k002','Argo Bromo','Bisnis')
insert into KERETA values ('k003','Taksaka Pagi','Eksekutif')
select * from KERETA

insert into STASIUN values ('s001','Gambir','Jakarta','DKI Jakarta')
insert into STASIUN values ('s002','Pasar Senen','Jakarta','DKI Jakarta')
insert into STASIUN values ('s003','Tugu','Yogyakarta','DIY')
insert into STASIUN values ('s004','Merah','Bandung','Jawa Barat')
select * from STASIUN

insert into JADWAL values ('j001','k001','2015/02/9','s001','s002','08:15','08:45')
insert into JADWAL values ('j002','k003','2015/02/10','s001','s004','20:15','23:45')
insert into JADWAL values ('j003','k002','2015/02/10','s004','s003','15:00','07:00')
insert into JADWAL values ('j004','k001','2015/02/8','s002','s003','06:30','15:15')
select * from JADWAL

insert into HARGATIKET values ('h001','j002','Dewasa','2500')
insert into HARGATIKET values ('h002','j003','Dewasa','145000')
insert into HARGATIKET values ('h003','j004','Anak','100000')
insert into HARGATIKET values ('h004','j001','Dewasa','250000')
select * from HARGATIKET

insert into PEMESANAN values ('h001','8A','A','Koko')
insert into PEMESANAN values ('h002','1D','D','Novianto')
insert into PEMESANAN values ('h003','3B','B','Pratama')
insert into PEMESANAN values ('h004','2C','C','ratama')
insert into PEMESANAN values ('h001','8B','B','Koko')
insert into PEMESANAN values ('h004','6H','H','Koko')
insert into PEMESANAN values ('h002','3A','A','Koko')
select * from PEMESANAN

=======================================================================================================
// no 2
create view view_total_pendapatan
AS select B.tanggal,SUM(hargaTiket) AS pendapatan from KERETA A,
JADWAL B, HARGATIKET C,PEMESANAN D where
A.id_kereta = B.idKereta AND B.idJadwal = C.idJadwal AND
C.idHargaTiket = D.idHargaTiket
group by B.tanggal having sum(hargaTiket) > 100000
select * from view_total_pendapatan

// no 3
select A.namaPenumpang from PEMESANAN A,HARGATIKET B
where A.idHargaTiket = B.idHargaTiket AND
B.hargaTiket IN (select max(hargaTIket) from HARGATIKET)

// no 4
select TOP 1 A.namaPenumpang from PEMESANAN A,HARGATIKET B
where A.idHargaTiket = B.idHargaTiket AND
B.hargaTiket IN (select min(hargaTIket) from HARGATIKET)

// no 5
select A.nama from KERETA A, JADWAL B, STASIUN C
where A.id_kereta = B.idKereta AND
B.idStasiunTiba = C.id_stasiun AND C.kota = 'Bandung'

// no 6
select A.nama, B.tanggal, B.jamBerangkat from KERETA A, JADWAL B
where A.id_kereta = B.idKereta AND
B.idJadwal = 'j001'

// no 7
select namaPenumpang, count(*) AS PEMESANAN FROM PEMESANAN
group by namaPenumpang having count(*) > 3

// no 8
select A.nama, count(hargaTiket) AS JumlahTiketTerjual, SUM(hargaTiket) AS Pendapatan from KERETA A,
JADWAL B, HARGATIKET C,PEMESANAN D where
A.id_kereta = B.idKereta AND B.idJadwal = C.idJadwal AND
C.idHargaTiket = D.idHargaTiket
group by A.nama

// no 9
select TOP 1 A.nama, count(*) AS PEMESANAN from KERETA A,
JADWAL B, HARGATIKET C,PEMESANAN D where
A.id_kereta = B.idKereta AND B.idJadwal = C.idJadwal AND
C.idHargaTiket = D.idHargaTiket
group by nama

// no 10
select TOP 1 namaPenumpang, count(*) AS PEMESANAN FROM PEMESANAN
group by namaPenumpang

=====================================================================================================

// no 1
select namaPenumpang, count(*) AS PEMESANAN FROM PEMESANAN
group by namaPenumpang having count(*) = 1

// no 2

// no 5
select A.nama from KERETA A, JADWAL B, HARGATIKET C
where A.id_kereta = B.idKereta AND
B.idJadwal = C.idJadwal AND
C.jenisPenumpang = 'Dewasa'