drop database if exists dbKompetisiBasket;
create database dbKompetisiBasket;

use dbKompetisiBasket;

create table tblTim
(nouruttim int primary key,
namatim varchar(100),
tempatlatihan varchar(100),
alamat varchar(100),
nourutlawan int,
hasil1 int,
hasil2 int,
tanggaltanding timestamp,
foreign key(nourutlawan) references tblTim(nouruttim));

insert into tblTim (nouruttim, namatim, tempatlatihan, alamat)
values
(1, 'Singa Putih', 'Samirono','Yogyakarta' ),
(2, 'Macan Terbang', 'Kepuh','Yogyakarta'),
(3, 'Buana Jaya', 'Klitren','Yogyakarta'),
(4, 'Gajah Mungkur', 'Ungaran','Semarang'),
(5, 'Abadi Semesta', 'Dr. Cipto','Semarang'),
(6, 'Patrika Jaya', 'Gondokusumo','Solo'),
(7, 'Pelita Abadi', 'Tanah Abang','Jakarta'),
(8, 'Flobamora Raya', 'Ruteng','Flores');

update tblTim set nourutlawan=3, hasil1=103, hasil2=90, tanggaltanding = '2008-10-10' where nouruttim=1;
update tblTim set nourutlawan=5, hasil1=90, hasil2=87, tanggaltanding = '2008-11-11' where nouruttim=2;
update tblTim set nourutlawan=7, hasil1=94, hasil2=98, tanggaltanding = '2008-09-12' where nouruttim=3;
update tblTim set nourutlawan=6, hasil1=83, hasil2=78, tanggaltanding = '2008-06-13' where nouruttim=4;
update tblTim set nourutlawan=8, hasil1=75, hasil2=96, tanggaltanding = '2008-02-14' where nouruttim=5;
update tblTim set nourutlawan=2, hasil1=97, hasil2=87, tanggaltanding = '2008-04-15' where nouruttim=6;
update tblTim set nourutlawan=1, hasil1=82, hasil2=99, tanggaltanding = '2008-05-16' where nouruttim=7;
update tblTim set nourutlawan=4, hasil1=71, hasil2=84, tanggaltanding = '2008-07-17' where nouruttim=8;

create table tblAnggota
(nourutanggota int primary key,
nama varchar(100),
alamat varchar(100),
tinggi int,
berat int);

insert into tblAnggota values
(1, 'Budi', 'Jl Sudirman 45', 189, 70),
(2, 'Andre', 'Jl Soeharto 35', 177, 70),
(3, 'Jony', 'Jl S Parman 67', 189, 70),
(4, 'Didin', 'Jl A Yani 23', 178, 70),
(5, 'Unang', 'Jl Diponegoro 198', 189, 70),
(6, 'Joko', 'Jl Imam Bonjol 78', 178, 70),
(7, 'Purwoko', 'Jl Mawar 4', 187, 70),
(8, 'Yoyok', 'Jl Melati 26', 185, 70),
(9, 'Edward', 'Jl Flamboyan 190', 189, 70),
(10, 'Ferdi', 'Jl Gajah 14', 170, 70),
(11, 'Filipus', 'Jl Singa 56', 186, 70),
(12, 'George', 'Jl Kamboja 187', 189, 70),
(13, 'Robi', 'Jl Menjangan 190', 189, 70),
(14, 'Franky', 'Jl Jetis 87', 183, 70),
(15, 'Yanto', 'Jl Colombo 47', 181, 70),
(16, 'Jack', 'Jl Gejayan 27', 178, 70),
(17, 'David', 'Jl Kancil 36', 179, 70),
(18, 'Bire', 'Jl Cemara 87', 184, 70),
(19, 'Anton', 'Jl Gerilya 45', 188, 70),
(20, 'Markus', 'Jl Mataram 98', 183, 70),
(21, 'Handi', 'Jl Gajah Mada 35', 184, 70),
(22, 'Hartono', 'Jl Panglima Sudirman 74', 185, 70);

update tblAnggota Set berat = tinggi  - 105;

create table tblRelasi
(nouruttim int,
nourutanggota int,
tanggaldaftar timestamp,
nopunggung int,
foreign key (nouruttim) references tblTim(nouruttim),
foreign key (nourutanggota) references tblAnggota(nourutanggota)
);

insert into tblRelasi
values
(1, 1, '2007-10-11',67),
(2, 2, '2007-11-14',45),
(1, 3, '2007-02-23',65),
(1, 4, '2007-06-25',77),
(8, 5, '2007-04-24',87),
(3, 6, '2007-03-27',56),
(1, 7, '2007-05-25',45),
(1, 8, '2007-09-29',34),
(4, 9, '2007-11-21',87),
(1, 10, '2007-02-12',94),
(7, 11, '2007-04-16',25),
(1, 12, '2007-03-18',74),
(5, 13, '2007-05-16',28),
(1, 14, '2007-07-13',39),
(6, 15, '2007-08-10',43),
(1, 15, '2007-11-18',15),
(8, 17, '2007-12-12',23);

/*Nomor1*/
select concat(tblTim.namatim," - ",tblTim.alamat) as "Nama Tim Basket - Asal", tblAnggota.nama as "Nama Anggota Tim"
from tblRelasi,tblTim,tblAnggota
where tblTim.nouruttim=tblRelasi.nouruttim and tblAnggota.nourutanggota=tblRelasi.nourutanggota;

/*Nomor2*/
select tblAnggota.nama as "Anggota",tblRelasi.tanggaldaftar as "Tanggal Daftar",tblTim.tanggaltanding as "Waktu Tanding",
DATEDIFF(tblTim.tanggaltanding,tblRelasi.tanggaldaftar) as "Selisih Waktu"
from tblRelasi
inner join tblTim on(tblTim.nouruttim=tblRelasi.nouruttim)
inner join tblAnggota on (tblAnggota.nourutanggota=tblRelasi.nourutanggota);

/*Nomor3*/
select tblAnggota.nama as "Anggota,Tinggi < 185",tblRelasi.nopunggung as "Nomor Punggung",year(tblRelasi.tanggaldaftar) as "Tahun Daftar"
from tblRelasi
inner join tblTim on(tblTim.nouruttim=tblRelasi.nouruttim)
inner join tblAnggota on (tblAnggota.nourutanggota=tblRelasi.nourutanggota)
where tblAnggota.tinggi < 185
group by tblAnggota.tinggi asc;

/*Nomor4 SESUAI SOAL*/
select concat(tblAnggota.nama,",",tblAnggota.alamat) as "Anggota-alamat, berat > 75", tblTim.alamat as "Dari Yogyakarta atau Semarang"
from tblRelasi
inner join tblTim on(tblTim.nouruttim=tblRelasi.nouruttim)
inner join tblAnggota on (tblAnggota.nourutanggota=tblRelasi.nourutanggota)
where tblAnggota.berat > 75 and (tblTim.alamat ="Semarang" or tblTim.alamat="Yogyakarta")
ORDER by tblAnggota.berat asc;

/*Nomor 5*/
select tim1.namatim as "Tim",tim2.namatim as "Lawan",tim1.hasil1 as "Hasil Tim",tim1.hasil2 as "Hasil Lawan",
if((tim1.hasil2-tim1.hasil1)<0, (tim1.hasil2-tim1.hasil1)*-1,(tim1.hasil2-tim1.hasil1)) as "Selisih",
if(tim1.hasil2>tim1.hasil1,tim2.namatim,tim1.namatim) as "Yang Menang"
from tblTim as tim1,tblTim as tim2
where tim1.nouruttim=tim2.nourutlawan;

/*Nomor6*/
select tblTim.namatim as "Nama Tim", count(tblRelasi.nouruttim) as "Jumlah pemain >1 (urut DESC)"
from tblRelasi
inner join tblTim on(tblTim.nouruttim=tblRelasi.nouruttim)
inner join tblAnggota on (tblAnggota.nourutanggota=tblRelasi.nourutanggota)
group by tblTim.namatim
having  count(tblRelasi.nouruttim) > 1
order by count(tblRelasi.nouruttim) desc;