· 6 years ago · Oct 31, 2019, 07:26 AM
1drop database if exists dbKompetisi;
2create database dbKompetisi;
3use dbKompetisi;
4
5Create table tblAnggota
6 (
7 noanggota int primary key not null,
8 namaanggota varchar(100),
9 posisi int
10 );
11
12Create table tblTim
13 (
14 notim int primary key,
15 namatim varchar(100),
16 noanggota int,
17 foreign key (noanggota) references tblAnggota(noanggota)
18 );
19
20Create table tblKompetisi
21 (
22 notim int,
23 hasiltim int,
24 hasillawan int,
25 nourutlawan int,
26 foreign key (notim) references tblTim(notim)
27 );
28
29INSERT INTO tblAnggota VALUES
30(1,"Steven",0),
31(2,"Beni",1),
32(3,"Vania",1),
33(4,"Roswari",0),
34(5,"Vina",4),
35(6,"Sandy",4),
36(7,"Teddy",0),
37(8,"Gianto",7),
38(9,"Stefanus",0),
39(10,"Gregorius",9),
40(11,"Fanny",9),
41(12,"Filipus",0),
42(13,"Kevin ",12),
43(14,"Kristino",0),
44(15,"Kristiana ",14);
45
46INSERT INTO tblTim VALUES
47 (1,"Tim A",1),
48 (2,"Tim B",4),
49 (3,"Tim C",7),
50 (4,"Tim D",9),
51 (5,"Tim E",12),
52 (6,"Tim F",14);
53
54INSERT INTO tblKompetisi VALUES
55(2,1,1,8),
56(3,1,7,2),
57(4,1,9,8),
58(5,1,5,9),
59(6,1,9,8),
60(1,2,2,7),
61(3,2,9,3),
62(4,2,9,6),
63(5,2,3,7),
64(6,2,4,1),
65(1,3,2,8),
66(2,3,3,2),
67(4,3,9,10),
68(5,3,3,4),
69(6,3,0,9),
70(1,4,6,3),
71(2,4,7,4),
72(3,4,2,8),
73(5,4,2,8),
74(6,4,5,0),
75(1,5,6,8),
76(2,5,1,3),
77(3,5,0,4),
78(4,5,9,4),
79(6,5,3,0),
80(1,6,4,9),
81(2,6,4,0),
82(3,6,1,5),
83(4,6,9,3),
84(5,6,8,10);
85
86--Nomor 2
87SELECT * FROM tblTim;
88SELECT * FROM tblAnggota;
89SELECT * FROM tblKompetisi;
90
91--Nomor 3
92 select a1.namaanggota,
93 (select a2.namaanggota from tblAnggota a2 where a1.noanggota = a2.posisi limit 1) as Anggota1,
94 (select a2.namaanggota from tblAnggota a2 where a1.noanggota = a2.posisi and a2.noanggota !=
95 (select a3.noanggota from tblAnggota a3 where a3.posisi = a1.noanggota limit 1) limit 1) as Anggota2
96 from tblAnggota a1 where a1.posisi = 0;
97
98--Nomor 4
99 select t1.namatim,
100 (select count(a2.namaanggota)+1 from tblAnggota a2 where a2.posisi = a1.noanggota limit 1) as Jumlah,
101 a1.namaanggota as Ketua ,
102(select a2.namaanggota from tblAnggota a2 where a1.noanggota = a2.posisi limit 1) as Anggota1,
103 (select a2.namaanggota from tblAnggota a2 where a1.noanggota = a2.posisi and a2.noanggota !=
104 (select a3.noanggota from tblAnggota a3 where a3.posisi = a1.noanggota limit 1) limit 1) as Anggota2
105 from tblTim t1, tblAnggota a1 where t1.noanggota = a1.noanggota;
106
107--Nomor 5