· 6 years ago · Oct 11, 2019, 05:58 AM
1CREATE TABLE Memek(
2 ID_Memek VARCHAR(5),
3 NamaMemek VARCHAR(30) NOT NULL,
4 JenisMemek VARCHAR(20) NOT NULL,
5 PRIMARY KEY(ID_Memek)
6);
7
8INSERT INTO Memek(ID_Memek, NamaMemek, JenisMemek)
9VALUES ('Q0001', 'Memek Basah', 'Licin'),
10 ('Q0002', 'Memek Sunda', 'Keset'),
11 ('Q0003', 'Memek Cina', 'Licin'),
12 ('Q0004', 'Memek Jawa', 'Keset');
13
14CREATE TABLE Umur(
15 Nama Varchar(20) NOT NULL,
16 tgl_lahir DATE NOT NULL
17);
18
19INSERT INTO Umur(Nama, tgl_lahir)
20VALUES ('Topher', '1999-04-19'),
21 ('Tian', '1998-04-28');
22
23//////// CARA MENGHITUNG UMUR \\\\\\\\
24
25SELECT*, YEAR(CURDATE())-YEAR(tgl_lahir) AS usia
26 FROM Umur;
27
28CREATE TABLE Pecahan(
29 Angka_Pembilang VARCHAR(5) NOT NULL,
30 Angka_Penyebut VARCHAR(5) NOT NULL
31);
32
33
34
35//////// CARA UPDATE DAN DELETE ROWS DITABLE \\\\\\\\
36
37
38UPDATE Memek
39SET NamaMemek = "Memek Sunda"
40WHERE ID_Memek = "Q0002";
41
42DELETE
43FROM MEMEK
44WHERE NamaMemek = "Memek Cina";
45
46
47
48
49
50INSERT INTO Pecahan(Angka_Pembilang, Angka_Penyebut)
51VALUES ('25', '100'),
52 ('45', '450'),
53 ('630', '280'),
54 ('250', '25'),
55 ('232', '29');
56
57CREATE TABLE Guitar(
58 Nama_Guitar VARCHAR(30) NOT NULL,
59 Jenis_Guitar VARCHAR(30) NOT NULL,
60 Harga INT NOT NULL
61);
62
63INSERT INTO Guitar(Nama_Guitar, Jenis_Guitar, Harga)
64VALUES ('Yamaha', 'Akustik', '780000'),
65 ('Schecter', 'Elektrik', '1500000'),
66 ('Gibson', 'Elektrik', '1000000'),
67 ('Roland', 'Elektrik', '7200000'),
68 ('Behringer', 'Elektrik', '2500000');
69
70
71///////// CARA MENGGUNAKAN SUBQUERY \\\\\\\\
72
73SELECT Nama_Guitar AS "Nama Gitar"
74FROM Guitar
75WHERE Harga < (SELECT MAX(HARGA)
76FROM GUITAR);
77
78
79//////// CARA MENGGUNAKAN CASE EXPRESSION \\\\\\\\
80
81SELECT Nama_Guitar AS "Nama Gitar",
82 CASE
83 WHEN Harga < 7200000 THEN 'Standard'
84 WHEN Harga < 2500000 THEN 'Murah'
85 WHEN Harga >= 2500000 THEN 'Mahal'
86 End AS Price
87FROM Guitar;
88
89
90//////// SENSOR NAMA KWKWKKW \\\\\\\\\
91
92SELECT
93 CASE WHEN Jenis_Guitar IS NOT NULL THEN
94 (CONCAT(LPAD('',LENGTH(Nama_Guitar),'*'),' ',RPAD('',LENGTH(Jenis_Guitar),'*'))) END AS "Kepo lo ngentot" FROM Guitar;
95
96
97
98
99
100
101
102
103
104
105
106/* Christian Imanuel wenji - 00000032463 - Kelas DL */
107
108
109/* NO 1 */
110CREATE TABLE Customer(
111 ID_Customer VARCHAR(5),
112 NamaDepan VARCHAR(30) NOT NULL,
113 NamaBelakang VARCHAR(30),
114 Alamat VARCHAR(30) NOT NULL,
115 Email VARCHAR(30) NOT NULL,
116 NomorTelepon VARCHAR(15) NOT NULL,
117 PRIMARY KEY(ID_Customer)
118);
119
120CREATE TABLE Employee(
121 ID_Employee VARCHAR(5),
122 NamaDepan VARCHAR(30) NOT NULL,
123 NamaBelakang VARCHAR(30),
124 Jabatan VARCHAR(30) NOT NULL,
125 Alamat VARCHAR(30) NOT NULL,
126 Email VARCHAR(30) NOT NULL,
127 NomorTelepon VARCHAR(15) NOT NULL,
128 Gaji INT NOT NULL,
129 PRIMARY KEY(ID_Employee)
130);
131
132CREATE TABLE Paket(
133 ID_Paket VARCHAR(5),
134 HargaPaket INT NOT NULL,
135 PRIMARY KEY (ID_Paket)
136);
137
138CREATE TABLE Menu(
139 ID_Menu VARCHAR(5),
140 NamaMenu VARCHAR(30) NOT NULL,
141 JenisMenu VARCHAR(20) NOT NULL,
142 ID_Paket VARCHAR(5),
143 PRIMARY KEY(ID_Menu),
144 FOREIGN KEY(ID_Paket) REFERENCES Paket(ID_Paket)
145)engine=InnoDB;
146
147CREATE TABLE Transaction(
148 ID_Customer VARCHAR(5),
149 ID_Paket VARCHAR(5),
150 ID_Employee VARCHAR(5),
151 TransactionDate DATE,
152 PRIMARY KEY(ID_Customer, ID_Paket, ID_Employee),
153 FOREIGN KEY(ID_Customer) REFERENCES Customer(ID_Customer),
154 FOREIGN KEY(ID_Employee) REFERENCES Employee(ID_Employee),
155 FOREIGN KEY(ID_Paket) REFERENCES Paket(ID_Paket)
156)engine=InnoDB;
157
158/* No 2 */
159INSERT INTO Employee(ID_Employee, NamaDepan, NamaBelakang, Jabatan, Alamat, Email, NomorTelepon, Gaji)
160VALUES ('E0001', 'Anthony', 'NULL', 'Manager', '70 Cool Guy', 'anthony@umn.ac.id', '123-456-4564', '500000'),
161 ('E0002', 'Akino', 'Archilles', 'Chef', '21 Kino Kino', 'akino@student.com', '354-124-5786', '3500000'),
162 ('E0003', 'Kevin', 'Alexander', 'Cashier', '34 Nishinoya', 'alex@yahoo.com', '098-123-7832', '3000000'),
163 ('E0004', 'Ivy', 'Marcia', 'Chef', '06 Wall Street', 'ivy@yahoo.com', '846-732-8427', '3500000'),
164 ('E0005', 'Martha', 'Saphira', 'Customer Service', '78 Ruby Fort', 'martha@umn.ac.id', '718-298-2873', '3200000'),
165 ('E0006', 'Nofiandy', 'NULL', 'Cashier', '03 Smooth Hair', 'nofiandy@gmail.com', '516-927-9268', '325000'),
166 ('E0007', 'Octavany', 'NULL', 'Cashier', '23 Relaxing Cafe', 'ocat@iseng.com', '654-876-1928', '3150000');
167
168/* NO 3 */
169INSERT INTO Customer(ID_Customer, NamaDepan, NamaBelakang, Alamat, Email, NomorTelepon)
170VALUES ('C0001', 'Michael','Oswin','125 Laugh Land','oswin@yahoo.com','745-387-2874'),
171 ('C0002', 'Anna','Jeane','163 Lolita Land','anna@gmail.com','982-397-4862'),
172 ('C0003', 'Raymond', 'NULL', '287 Chucky Suburb', 'raymond@gmail.com', '917-376-4672'),
173 ('C0004', 'Theodore','Mulia', '287 Idea Annex', 'mulia@gmail.com', '122-187-1111'),
174 ('C0005', 'Niki','Emersan', '297 Cult Annex', 'niki@yahoo.com', '198-292-2223'),
175 ('C0006', 'Christine','Liviani', '275 Love Annex', 'liviani@live.com', '000-198-2836'),
176 ('C0007', 'Hermawan','NULL', '212 Tall Building', 'hermawan@yahoo.com', '192-168-1001'),
177 ('C0008', 'Agung','NULL' ,'293 Short Building', 'agung@yahoo.com', '192-168-1993'),
178 ('C0009', 'Samuel','Sandro', '206 Oppa Annex', 'sam@live.com', '192-168-2973'),
179 ('C0010', 'Kennard','Alcander', '202 Brother Annex', 'ken@live.com', '198-782-3864');
180
181/* NO 4 */
182INSERT INTO Paket(ID_Paket, HargaPaket)
183VALUES ('P0001', '3000000'),
184 ('P0002', '2500000'),
185 ('P0003', '2000000');
186
187/* NO 5 */
188INSERT INTO Menu(ID_Menu, NamaMenu, JenisMenu, ID_Paket)
189VALUES ('M0001', 'Nasi Goreng Cinta', 'Makanan', 'P0001'),
190 ('M0002', 'Ayam Bumbu PHP', 'Makanan', 'P0002'),
191 ('M0003', 'Soto Daging CPP', 'Makanan', 'P0003'),
192 ('M0004', 'Siomay Pak BoBi', 'Makanan', 'P0001'),
193 ('M0005', 'Blue Integer', 'Minuman', 'P0001'),
194 ('M0006', 'Mocha Float', 'Dessert', 'P0003'),
195 ('M0007', 'Choco Banana Split', 'Dessert', 'P0002'),
196 ('M0008', 'Es Teh Panas', 'Minuman', 'P0002'),
197 ('M0009', 'Nasi Micin', 'Makanan', 'P0002'),
198 ('M0010', 'Sapi Chabe', 'Makanan', 'P0003');
199
200/* NO 6 */
201INSERT INTO Transaction (ID_Customer, ID_Paket, ID_Employee, TransactionDate)
202VALUES ('C0001','P0003','E0002','2016-02-25'),
203 ('C0002','P0002','E0001','2016-03-12'),
204 ('C0003','P0002','E0002','2016-04-10'),
205 ('C0004','P0001','E0004','2016-06-28'),
206 ('C0005','P0002','E0001','2016-03-30'),
207 ('C0006','P0003','E0003','2016-03-10'),
208 ('C0007','P0001','E0005','2016-03-01'),
209 ('C0008','P0002','E0002','2016-08-30'),
210 ('C0009','P0003','E0002','2016-11-02'),
211 ('C0010','P0001','E0005','2016-08-18');
212
213
214/* NO 7 */
215SELECT CONCAT("Nilai Phi pada rumus lingkaran adalah ",(TRUNCATE(22/7, 5))) AS "Nilai Phi Lingkaran";
216
217/* NO 8 */
218SELECT NamaDepan AS "Nama Depan", NamaBelakang AS "Nama Belakang", Jabatan,
219CASE
220 WHEN NamaBelakang IS NULL THEN NamaDepan
221 ELSE LOWER(CONCAT(NamaDepan, '.', NamaBelakang, '@chef.rs.id'))
222 END AS "E-Mail"
223FROM Employee
224WHERE Jabatan LIKE 'Chef';
225
226/* NO 9 */
227SELECT (SELECT NamaDepan FROM Customer WHERE Customer.ID_Customer = Transaction.ID_Customer) AS "Customer",
228 (SELECT NamaDepan FROM Employee WHERE Employee.ID_Employee = Transaction.ID_Employee) AS "Karyawan" ,
229 (SELECT CONCAT('Rp', HargaPaket) FROM Paket WHERE Paket.ID_Paket = Transaction.ID_Paket) AS "Harga",
230 DATE_FORMAT(TransactionDate, '%d %M %Y') AS "Tanggal"
231FROM Transaction
232ORDER BY YEAR(TransactionDate), MONTH(TransactionDate), DATE(TransactionDate);
233
234/* NO 10 */
235SELECT (SELECT
236 CASE
237 WHEN NamaBelakang IS NULL THEN NamaDepan
238 ELSE CONCAT(NamaDepan, ' ', NamaBelakang)
239 END
240 FROM Employee WHERE Employee.ID_Employee = Transaction.ID_Employee) AS Employee,
241 (SELECT CONCAT('Rp', HargaPaket) FROM Paket WHERE Paket.ID_Paket = Transaction.ID_Paket) AS Harga,
242 TransactionDate AS Tanggal
243FROM Transaction
244ORDER BY TransactionDate desc
245LIMIT 3;
246
247/* NO 11 */
248SELECT
249 CASE WHEN NamaBelakang IS NOT NULL THEN
250 (CONCAT(LPAD('',LENGTH(NamaDepan),'*'),' ',RPAD('',LENGTH(NamaBelakang),'*'))) END AS Enkrpsi FROM Customer;
251
252-- 7
253SELECT CONCAT(namaDepan,' ',IFNULL(namaBelakang,'')) as 'Nama' FROM customer order by namaDepan asc;
254
255-- 8
256SELECT MONTHNAME(transactionDate) as 'Bulan', count(transactionDate) as 'Jumlah' FROM transaction GROUP BY 1 ORDER BY transactionDate;
257
258-- 9
259SELECT CONCAT(namaDepan,' ',IFNULL(namaBelakang,'')) as 'Nama', gaji FROM employee where email like '%gmail.com' ORDER BY Gaji asc LIMIT 1;
260
261-- 10
262SELECT idEmployee as 'Kode Pekerja', count(idEmployee) 'Banyak Transaksi', GROUP_CONCAT(DATE_FORMAT(transactionDate,'%d %M %Y')) 'Jadwal Transaksi' FROM transaction GROUP BY 1 order by 2 desc;
263
264-- 11
265SELECT
266(SELECT COUNT(*) as google from employee where email LIKE "%gmail.com") as google,
267(SELECT COUNT(*) as google from employee where email LIKE "%live.com") as live,
268(SELECT COUNT(*) as google from employee where email LIKE "%yahoo.com") as yahoo;
269
270-- 12
271SELECT CONCAT(emp.namaDepan,"",emp.namaBelakang) from employee as emp, transaction as trans WHERE emp.Id_Employee = trans.idEmployee GROUP BY 1;
272
273-- 13
274SELECT date_format(transactionDate, "%d %M %Y") AS "Waktu Transaksi",
275IF(transactionDate<"2016-05-01","Transaksi Lama","Transaksi Baru") as "Kategori Waktu Transaksi"
276 FROM transaction WHERE transactionDate BETWEEN "2016-03-1" AND "2016-09-31" ORDER BY transactionDate;
277
278-- 14
279SELECT CONCAT(namaDepan,' ',namaBelakang) AS "Nama" FROM Customer
280ORDER BY LENGTH(CONCAT(namaDepan,'',namaBelakang)) DESC LIMIT 1;
281
282-- 15
283SELECT CONCAT(namaDepan, ' ', IFNULL(namaBelakang,"")) AS "Nama" FROM Employee
284ORDER BY LENGTH(CONCAT(namaDepan, ' ', namaBelakang));
285
286-- 16
287SELECT
288 (SELECT CONCAT (namaDepan,' ',IFNULL(namaBelakang,''))
289 FROM Employee
290 WHERE Id_Employee = transaction.idEmployee) AS 'Nama Karyawan',
291 GROUP_CONCAT((SELECT CONCAT (namaDepan,' ',IFNULL(namaBelakang,''))
292 FROM Customer
293 WHERE id_customer = transaction.idCustomer
294ORDER BY 1
295)) AS 'Nama Customer',
296SUM((SELECT HargaPaket FROM Paket WHERE id_paket = transaction.idPaket)) AS 'Harga'
297FROM transaction GROUP BY 1 DESC LIMIT 1;
298
299-- 17
300SELECT
301 CASE
302 WHEN namaBelakang IS NULL THEN
303 CASE
304 WHEN LENGTH(namaDepan) % 3 = 0 THEN
305 CASE
306 WHEN LENGTH(SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1))%3 = 0 THEN LOWER(CONCAT(REVERSE(LEFT(namaDepan,3)),"pen", LEFT(REVERSE(SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1)),4)))
307 WHEN LENGTH(SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1))%3 = 1 THEN LOWER(CONCAT(REVERSE(LEFT(namaDepan,3)),"pen", SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1)))
308 ELSE LOWER(CONCAT(REVERSE(LEFT(namaDepan,3)),"pen", REVERSE(SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1))))
309 END
310 WHEN LENGTH(namaDepan) % 3 = 1 THEN
311 CASE
312 WHEN LENGTH(SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1))%3 = 0 THEN LOWER(CONCAT(REVERSE(LEFT(namaDepan,3)),"apple", LEFT(REVERSE(SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1)),4)))
313 WHEN LENGTH(SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1))%3 = 1 THEN LOWER(CONCAT(REVERSE(LEFT(namaDepan,3)),"apple", SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1)))
314 ELSE LOWER(CONCAT(REVERSE(LEFT(namaDepan,3)),"apple", REVERSE(SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1))))
315 END
316 ELSE
317 CASE
318 WHEN LENGTH(SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1))%3 = 0 THEN LOWER(CONCAT(REVERSE(LEFT(namaDepan,3)),"pineapple", LEFT(REVERSE(SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1)),4)))
319 WHEN LENGTH(SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1))%3 = 1 THEN LOWER(CONCAT(REVERSE(LEFT(namaDepan,3)),"pineapple", SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1)))
320 ELSE LOWER(CONCAT(REVERSE(LEFT(namaDepan,3)),"pineapple", REVERSE(SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1))))
321 END
322 END
323 ELSE
324 CASE
325 WHEN LENGTH(CONCAT( namaDepan, namaBelakang)) % 3 = 0 THEN
326 CASE
327 WHEN LENGTH(SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1))%3 = 0 THEN LOWER(CONCAT(REVERSE(LEFT(namaDepan,3)),"pen", LEFT(REVERSE(SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1)),4)))
328 WHEN LENGTH(SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1))%3 = 1 THEN LOWER(CONCAT(REVERSE(LEFT(namaDepan,3)),"pen", SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1)))
329 ELSE LOWER(CONCAT(REVERSE(LEFT(namaDepan,3)),"pen", REVERSE(SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1))))
330 END
331 WHEN LENGTH(CONCAT( namaDepan, namaBelakang)) % 3 = 1 THEN
332 CASE
333 WHEN LENGTH(SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1))%3 = 0 THEN LOWER(CONCAT(REVERSE(LEFT(namaDepan,3)),"apple", LEFT(REVERSE(SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1)),4)))
334 WHEN LENGTH(SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1))%3 = 1 THEN LOWER(CONCAT(REVERSE(LEFT(namaDepan,3)),"apple", SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1)))
335 ELSE LOWER(CONCAT(REVERSE(LEFT(namaDepan,3)),"apple", REVERSE(SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1))))
336 END
337 ELSE
338 CASE
339 WHEN LENGTH(SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1))%3 = 0 THEN LOWER(CONCAT(REVERSE(LEFT(namaDepan,3)),"pineapple", LEFT(REVERSE(SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1)),4)))
340 WHEN LENGTH(SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1))%3 = 1 THEN LOWER(CONCAT(REVERSE(LEFT(namaDepan,3)),"pineapple", SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1)))
341 ELSE LOWER(CONCAT(REVERSE(LEFT(namaDepan,3)),"pineapple", REVERSE(SUBSTRING_INDEX(TRIM(SUBSTRING(Alamat,4,12)),' ',1))))
342 END
343 END
344 END AS Hashing
345from Customer
346ORDER BY REVERSE(LEFT(namaDepan,3));