MEMBUAT 50 SYNTAX SQL
Nama : RENDIKA PERDANA PUTRA
Kelas : MIF R-41/16
NPM : 16402156
1. Create database Rendika;
2. create table Menu(Kode varchar(4)Primary key,Menu text(30)Not Null,Item text(10)Not
Null,Harga int default'0');
3. insert into Menu(Kode,Menu,Item,Harga)values('A01','Pop Ice','Drink',7000),('A02','Orange
Juice','Drink',10000),('A03','Mineral Water','Drink',5000),('A04','Beef Pepperoni','Pizza',80000),
('A05','Deluxe Cheese','Pizza',90000),('B01','Beef Lasagna','Pasta',30000),('B02','Oriental Chicken
Spaghetti','Pasta',35000);
4. Select Lower(Menu)from Menu;
5. Select Upper(Menu)from Menu;
6. Select Reverse(Menu)from Menu;
7. Select Repeat(Menu,2)from Menu;
8. Select Menu,Lenght(Menu)from Menu;
9. Select Replace(Menu,'a','o')from Menu;
10. Update Menu set Menu =' Mineral Water 'where Menu='Mineral Water';
11. select Menu,Ltrim(Menu)from Menu;
12. select Menu,Ltrim(Menu)from Menu;
13. Update Menu set Menu=Trim(Menu);
14. select Menu,Left(Menu,5)from Menu;
15. select Menu,Right(Menu,4)from Menu;
16. select Menu,mid(Menu,3,2)from Menu;
17. select concat(Kode,Menu)from Menu;
18. Alter Table Menu Add Diskon Varchar(30);
19. Update Menu set Jumlah_Diskon=if(Harga>=70000,'10%','Tidak Diskon')
20. Alter Table Menu Add Hasil_Diskon int;
21. Update Menu Set Hasil_Diskon=if(Harga>=70000,Harga-(Harga*0.1),0);
22. Create Table Karyawan(No Int Auto_Increment Primary Key,Nama Varchar (25),Tgl_Lahir
Date,Posisi varchar(25),Jam_Masuk Time,Jam_Pulang Time,Awal_Libur Date);
23. Insert Into Karyawan(Nama,Tgl_Lahir,Posisi)values('Acep','1997-1-21','Pelayan'),('Diki','1997-3-
20','Dapur'),('Antoni','1996-1-23','Kasir');
24. Select Nama,Tgl_Lahir,Round(DateDiff(Now(),Tgl_Lahir)/365,0)as Usia From Karyawan;
25. SELECT Nama,Tgl_Lahir,Date_Format(Tgl_Lahir,'%W')as Hari_Lahir From Karyawan;
26. Select Nama,Tgl_Lahir,DateDiff(Now(),Tgl_Lahir)as Jumlah_Hari From Karyawan;
27. Update Karyawan Set Jam_Masuk =
If(Posisi='Pelayan','06:30',If(Posisi='Dapur','06:45',If(Posisi='Kasir','06:00','')))
28. Update Karyawan Set Jam_Pulang =
if(Posisi='Pelayan',addtime(Jam_Masuk,'08:00'),if(Posisi='Dapur',addtime(Jam_Masuk,'08:45'),if(
Posisi='Kasir',addtime(Jam_Masuk,'08:50'),'')));
29. Update Karyawan set Awal_Libur = if(Posisi='Pelayan','2017-4-1',if(Posisi='Dapur','2017-4-
2',if(Posisi='Kasir','2017-4-3','')));
30. Alter Table karyawan Add Akhir_Libur Date;
31. Update Karyawan Set Akhir_Libur = If(Posisi='Pelayan',adddate(Awal_Libur,interval 7
day),If(Posisi='Dapur',adddate(Awal_Libur,interval 8
day),If(Posisi='Kasir',adddate(Awal_Libur,interval 9 day),'')));
32. Create Table Pembeli(Kode_Pembeli Varchar(3)Primary Key,Kode Varchar(4),Menu Text(30));
33. Alter Table Pembeli Add Nama_Pembeli Varchar(30);
34. Insert Into Pembeli(Kode_Pembeli,Kode,Menu,Nama_Pembeli)Values('111','A01','Pop
Ice','Acep'),('112','A02','Orange Juice','Diki'),('113','A03','Mineral Water','Rendi'),
('114','A04','Beef Pepperoni','Wili'),('115','A05','Deluxe Cheese','Adit'),('116','B01','Beef
Lasagna','Ridwan'),('117','B02','Oriental Chicken Spaghetti','Atep');
35. Select Menu,Count(*)as Jumlah_Menu From Menu Group BY Menu;
36. Select Menu,Count(*)as Jumlah_Menu From Menu Group BY Menu With Rollup;
37. Select Menu,AVG(Harga)as Rata_Rata_Biaya From Menu Group BY Menu With Rollup;
38. Select * From Menu WHERE Harga = (Select Min(Harga)From Menu);
39. Select * From Menu WHERE Harga>(Select AVG(Harga)From Menu);
40. Select * From Menu Where Menu in(Select Menu From Menu);
41. Select * From Menu WHERE Harga IN (Select Menu From Menu WHERE Harga > 20000);
42. Select Nama_Pembeli,Item,Harga From Pembeli,Menu WHERE [Link] = [Link];
43. Select * From Menu NATURAL JOIN Karyawan Where Item ='Drink' OR Tgl_Lahir = '1997-1-21';
44. Alter Table Pembeli ADD Banyak_Pesanan Int Not Null
45. Create View Menu_View as Select*, Harga * Banyak_Pesanan From Pembeli NATURAL JOIN
Menu;
46. Select * From Menu_View Where Harga>30000;
47. Select * ,Harga * (10/100) Where Harga>70000 From Menu_View
48. Create Table Tbl_Trigger LIKE Menu;
49. Select Menu,Count(*)as Jumlah_Menu From Menu Group BY Menu;
50. Select Menu,Count(*)as Jumlah_Menu From Menu Group BY Menu;