C72EPqXG

1/*nomor 1: membuat tabel
2aturan main:
31. buat tabel dengan PK terlebih dahulu
42. tabel dengan PK dan FK
53. table dengan FK saja
6*/
7
8drop database if exists dbLatihan; /*drop untuk menghapus, lawannya DROP adalah ALTER*/
9create database dbLatihan;
10use dbLatihan;
11
12drop table if exists tblPeserta;
13create table tblPeserta
14(
15idpeserta int primary key,
16peserta varchar(20) NOT NULL
17) engine = Innodb;
18
19create table tblMataPelajaran
20(
21kodemapel varchar(6) primary key,
22mapel varchar(100) not null
23) engine = Innodb;
24
25create table tblUjian
26(
27idpeserta int,
28kodemapel varchar(6),
29keterangan varchar(3),
30nilai decimal(8,2), /*panjang karakter 8 dengan 2 angka di belakang koma*/
31foreign key(idpeserta) references tblPeserta(idpeserta) ON DELETE CASCADE ON UPDATE CASCADE, /*kalau di PK di DELETE, berarti FK OTOMATIS dihapus, demikian juga yang di UPDATE*/
32foreign key(kodemapel) references tblMataPelajaran(kodemapel) ON DELETE CASCADE ON UPDATE CASCADE
33) engine = Innodb;
34 
35/*ON DELETE dan ON UPDATE hanya berlaku untuk mesin database tipe InnoDB dan biasanya digunakan hanya untuk aplikasi lokal/desktop
36kalau IP, yang dipakai mesinnya myisam
37*/
38
39desc tblPeserta;
40desc tblMataPelajaran;
41desc tblUjian;
42
43
44/*memasukan data ke dalam tabel
45INSERT SINGLE VALUE: semua kolom harus disebutkan walaupun tidak berurutan
46SATU PERINTAH SATU DATA
47*/
48
49INSERT INTO tblMataPelajaran(kodemapel, mapel) 
50VALUES ('CS-001', 'RDBMS');
51
52INSERT INTO tblMataPelajaran VALUES
53('CS-002', 'DATABASE PROGRAMMING'),
54('CS-003', 'DATA MINING'),
55('CD-004', 'BIG DATA');
56/*
57MULTIPLE VALUES: 1 perintah bisa banyak data
58*/
59INSERT INTO tblPeserta VALUES
60(1, 'Stevanus Mulyono'),
61(2, 'Meliana Saraswati'),
62(3, 'Vinsensia Ananda'),
63(4, 'Rudi Paskalis');
64
65/*proses import
66syarat nya jika sudah punya data terlebih dahulu
67datanya format CSV (comma seperated values)
68alasan csv adalah standar penyimpanan flatfile di seluruh platform
69
70yang harus diperhatikan:
711. format csv
722. pembatas antar kolom (;)
733. pengapit values ('----')
74
751;'CS-001';'UTS';48.00 
762;'CS-001';'UTS';63.00 
773;'CS-001';'UTS';70.00 
784;'CS-001';'UTS';64.00 
791;'CS-002';'UTS';67.00 
802;'CS-002';'UTS';42.00 
813;'CS-002';'UTS';91.00 
824;'CS-002';'UTS';49.00 
83-------------------------------dst
84*/
85
86load data local infile 'sqlrdbms.csv'
87into table tblUjian
88fields terminated by ';'
89enclosed by ''''
90ignore 1 lines; /*diabaikan karena baris 1 adalah judul*/
91
92select * from tblMataPelajaran;
93select * from tblPeserta;
94select * from tblUjian;
95
96/*JOIN:
971. equal join (penempatan join nya ada di bagian WHERE
982. Inner Join/CROSS JOIN
993. NATURAL JOIN
100
101yang harus dilihat: sumber data di tempatkan di bagian FROM
102*/
103
104/*EQUAL JOIN*/
105SELECT tblMataPelajaran.mapel AS MATA_PELAJARAN,
106tblPeserta.peserta AS PESERTA,
107tblUjian.nilai AS NILAI_UTS
108FROM tblMataPelajaran, tblPeserta, tblUjian
109WHERE tblMataPelajaran.kodemapel = tblUjian.kodemapel
110AND tblPeserta.idpeserta = tblUjian.idpeserta
111AND tblUjian.keterangan = 'UTS';
112
113/*NATURAL JOIN: diberikan kepada mesin SQL untuk mencari join/pasangannya sendiri >> efeknya sangat lambat untuk data berukuran besar*/
114SELECT tblMataPelajaran.mapel AS MATA_PELAJARAN,
115tblPeserta.peserta AS PESERTA,
116tblUjian.nilai AS NILAI_UTS
117FROM tblMataPelajaran
118NATURAL JOIN tblPeserta
119NATURAL JOIN tblUjian
120WHERE tblUjian.keterangan = 'UTS';
121
122/*INNER JOIN*/
123SELECT tblMataPelajaran.mapel AS MATA_PELAJARAN,
124tblPeserta.peserta AS PESERTA,
125tblUjian.nilai AS NILAI_UTS
126FROM tblMataPelajaran
127INNER JOIN tblUjian ON(tblMataPelajaran.kodemapel = tblUjian.kodemapel)
128INNER JOIN tblPeserta ON(tblPeserta.idpeserta = tblUjian.idpeserta)
129WHERE tblUjian.keterangan = 'UTS';
130
131/*CROSS JOIN*/
132SELECT tblMataPelajaran.mapel AS MATA_PELAJARAN,
133tblPeserta.peserta AS PESERTA,
134tblUjian.nilai AS NILAI_UTS
135FROM tblMataPelajaran
136CROSS JOIN tblUjian USING(kodemapel)
137CROSS JOIN tblPeserta USING(idpeserta)
138WHERE tblUjian.keterangan = 'UTS';
139
140/*fungsi string
141huruf kecil LCASE (lower case)
142huruf besar UCASE (upper case)
143Proper Case (belum ada di MySQL)
144panjang huruf LENGTH
145huruf dari kiri LEFT
146huruf dari kanan RIGHT
147perintah untuk mencari string LOCATE posisi nya ada dimana (dimunculkan adalah indeks nya
148MID ambil string yang di tengah
149*/
150SELECT LCASE(mapel) as HURUF_KECIL,
151 LENGTH(mapel) as JUMLAH_KARAKTER
152FROM tblMataPelajaran;
153
154SELECT UCASE(peserta) as HURUF_BESAR,
155LEFT(peserta, 3) AS TIGAHURUFPERTAMANAMA,
156RIGHT(peserta, 2) AS DUAHURUFTERAKHIRNAMA
157FROM tblPeserta;
158
159SELECT peserta, 
160LOCATE(' ', peserta) as CARISPASI
161FROM tblPeserta;
162
163/*mid*/
164select peserta as NAMALENGKAP,
165MID(peserta, 1, locate(' ', peserta)) as DEPAN,
166MID(peserta, locate(' ', peserta) + 1,
167LENGTH(peserta) - locate(' ', peserta)) AS BELAKANG
168from tblPeserta;
169
170/*left dan right*/
171select peserta as NAMALENGKAP,
172LEFT(peserta, locate(' ', peserta)) as DEPAN,
173RIGHT(peserta, LENGTH(peserta) - locate(' ', peserta)) AS BELAKANG
174from tblPeserta;
175
176/*menggabungkan string menggunakan perintah CONCAT
177concatenate
178
179select CONCAT(tblPeserta.peserta, '#',
180tblMataPelajaran.mapel, '#', tblUjian.nilai)
181AS GABUNG_NAMA_MAPEL,NILAIUAS
182from tblMataPelajaran CROSS JOIN tblUjian USING(kodemapel)
183CROSS JOIN tblPeserta USING(idpeserta)
184where keterangan = 'UAS';
185*/
186/*fungsi logika
187IIF jika pilihan nya hanya 2
188IF(pengujian, TRUE, FALSE)
189CASE WHEN jika pilihan nya lebih dari 2
190*/
191SELECT tblMataPelajaran.mapel as MATAPELAJARAN,
192tblPeserta.peserta as PESERTA,
193tblUjian.nilai as NILAIUAS,
194IF(tblUjian.nilai<60, 'LULUS', 'TIDAKLULUS') as KETERANGAN
195FROM tblMataPelajaran, tblUjian, tblPeserta
196WHERE tblMataPelajaran.kodemapel = tblUjian.kodemapel AND tblPeserta.idpeserta = tblUjian.idpeserta AND tblUjian.keterangan = 'UAS';
197
198
199SELECT tblMataPelajaran.mapel as MATAPELAJARAN,
200tblPeserta.peserta as PESERTA,
201tblUjian.nilai as NILAIUAS,
202    case
203        when tblUjian.nilai BETWEEN 80 AND 100 THEN 
204        'A'
205        when tblUjian.nilai BETWEEN 50 AND 79 THEN
206        'B'
207        when tblUjian.nilai BETWEEN 30 AND 49 THEN
208        'C'
209        when tblUjian.nilai <30 THEN
210        'E'
211    end as NILAIHURUF
212    
213FROM tblMataPelajaran, tblUjian, tblPeserta
214WHERE tblMataPelajaran.kodemapel = tblUjian.kodemapel AND tblPeserta.idpeserta = tblUjian.idpeserta AND tblUjian.keterangan = 'UAS';
215
216ALTER TABLE tblPeserta ADD alamat VARCHAR(100);
217UPDATE tblPeserta SET alamat = 'Semarang';
218
219ALTER TABLE tblPeserta DROP COLUMN alamat;
220
221/*TUGAS HARI INI:
2221. NOMOR 6 dan 7 tulislah perintah JOIN nya (ALL) x 4 perintah join = 8
2232. NOMOR 20 DAN 21
2243. BAGIAN 6 BONUS NILAI
225*/