· 5 years ago · May 06, 2020, 02:50 PM
1#!/bin/bash
2
3N="\e[0m"
4R="\e[91m"
5C="\e[96m"
6
7name_db=("UTS_Muhamad_Zainal_Makruf");
8name_tb1=("Peminjaman_Buku" "Sewa_Buku");
9name_tb2=("Penjualan_Motor" "Jual_Motor");
10field_tb1=("no_anggota" "nama" "alamat" "judul_buku" "pengarang" "tgl_peminjaman" "jenis_kelamin" "tgl_pengembalian" "alamat_anggota");
11field_tb2=("no_pelanggan" "nama" "alamat" "jenis_kelamin" "type_motor" "harga_motor" "perusahaan_tipe" "jumlah_pembelian" "alamat_pelanggan");
12type_tb=("char" "varchar" "date" "int" "enum");
13enum_tp1=("'L'" "'P'");
14enum_tp2=("'Negeri'" "'Swasta'");
15
16# Soal A
17soal_a(){
18 echo -e $R"\nNo 1\n"$N
19 sudo mysql -uroot -e "create database if not exists $name_db;show databases;"
20
21 echo -e $R"\nNo 2\n"$N
22 sudo mysql -uroot -e "
23 use $name_db;
24 create table ${name_tb1[0]} (
25 ${field_tb1[0]} ${type_tb[0]} (4) not null primary key,
26 ${field_tb1[1]} ${type_tb[1]} (20),
27 ${field_tb1[2]} ${type_tb[1]} (30),
28 ${field_tb1[3]} ${type_tb[1]} (25),
29 ${field_tb1[4]} ${type_tb[1]} (15),
30 ${field_tb1[5]} ${type_tb[2]});
31 show tables;
32 describe ${name_tb1[0]};"
33
34 echo -e $R"\nNo 3\n"$N
35 sudo mysql -uroot -e "
36 use $name_db;
37 alter table ${name_tb1[0]} add ${field_tb1[6]} ${type_tb[4]} (${enum_tp1[0]}, ${enum_tp1[1]}) default ${enum_tp1[0]} after ${field_tb1[2]};
38 describe ${name_tb1[0]};"
39
40 echo -e $R"\nNo 4\n"$N
41 sudo mysql -uroot -e "
42 use $name_db;
43 alter table ${name_tb1[0]} add ${field_tb1[7]} ${type_tb[2]};
44 describe ${name_tb1[0]};"
45
46 echo -e $R"\nNo 5\n"$N
47 sudo mysql -uroot -e "
48 use $name_db;
49 alter table ${name_tb1[0]} drop ${field_tb1[4]};
50 describe ${name_tb1[0]};"
51
52 echo -e $R"\nNo 6\n"$N
53 sudo mysql -uroot -e "
54 use $name_db;
55 alter table ${name_tb1[0]} modify ${field_tb1[1]} ${type_tb[1]} (15);
56 describe ${name_tb1[0]};"
57
58 echo -e $R"\nNo 7\n"$N
59 sudo mysql -uroot -e "
60 use $name_db;
61 alter table ${name_tb1[0]} change ${field_tb1[2]} ${field_tb1[8]} ${type_tb[1]} (25);
62 describe ${name_tb1[0]};"
63
64 echo -e $R"\nNo 8\n"$N
65 sudo mysql -uroot -e "
66 use $name_db;
67 show tables;
68 alter table ${name_tb1[0]} rename ${name_tb1[1]};
69 show tables;"
70
71 echo -e $R"\nNo 9\n"$N
72 sudo mysql -uroot -e "
73 use $name_db;
74 insert into ${name_tb1[1]} value
75 ('1234','Arie Ramadhan','Ungaran','L','Matematika','2013-04-07','2013-04-17'),
76 ('2324','Baru Saputra','Semarang','L','Bahasa Jawa','2013-04-23','2013-04-30'),
77 ('2323','Duwi Riawati','Kendal','P','Bahasa Inggris','2013-05-12','2013-05-22'),
78 ('3333','Dwi Galih Saputro','Jepara','L','IPA','2013-05-16','2013-05-26'),
79 ('4345','Eva Oktaviani','Pati','P','IPS','2013-06-21','2013-06-29');
80 select * from ${name_tb1[1]};"
81}
82
83# Soal B
84soal_b(){
85 echo -e $R"\nNo 1\n"$N
86 sudo mysql -uroot -e "create database if not exists $name_db;show databases;"
87
88 echo -e $R"\nNo 2\n"$N
89 sudo mysql -uroot -e "
90 use $name_db;
91 create table ${name_tb2[0]} (
92 ${field_tb2[0]} ${type_tb[0]} (4) not null primary key,
93 ${field_tb2[1]} ${type_tb[1]} (20),
94 ${field_tb2[2]} ${type_tb[1]} (30),
95 ${field_tb2[3]} ${type_tb[0]} (1),
96 ${field_tb2[4]} ${type_tb[1]} (15),
97 ${field_tb2[5]} ${type_tb[3]});
98 show tables;
99 describe ${name_tb2[0]};"
100
101 echo -e $R"\nNo 3\n"$N
102 sudo mysql -uroot -e "
103 use $name_db;
104 alter table ${name_tb2[0]} add ${field_tb2[6]} ${type_tb[4]} (${enum_tp2[0]}, ${enum_tp2[1]}) default ${enum_tp2[1]} after ${field_tb2[3]};
105 describe ${name_tb2[0]};"
106
107 echo -e $R"\nNo 4\n"$N
108 sudo mysql -uroot -e "
109 use $name_db;
110 alter table ${name_tb2[0]} add ${field_tb2[7]} ${type_tb[3]};
111 describe ${name_tb2[0]};"
112
113 echo -e $R"\nNo 5\n"$N
114 sudo mysql -uroot -e "
115 use $name_db;
116 alter table ${name_tb2[0]} drop ${field_tb2[3]};
117 describe ${name_tb2[0]};"
118
119 echo -e $R"\nNo 6\n"$N
120 sudo mysql -uroot -e "
121 use $name_db;
122 alter table ${name_tb2[0]} modify ${field_tb2[1]} ${type_tb[1]} (35);
123 describe ${name_tb2[0]};"
124
125 echo -e $R"\nNo 7\n"$N
126 sudo mysql -uroot -e "
127 use $name_db;
128 alter table ${name_tb2[0]} change ${field_tb2[2]} ${field_tb2[8]} ${type_tb[1]} (25);
129 describe ${name_tb2[0]};"
130
131 echo -e $R"\nNo 8\n"$N
132 sudo mysql -uroot -e "
133 use $name_db;
134 show tables;
135 alter table ${name_tb2[0]} rename ${name_tb2[1]};
136 show tables;"
137
138 echo -e $R"\nNo 9\n"$N
139 sudo mysql -uroot -e "
140 use $name_db;
141 insert into ${name_tb2[1]} value
142 ('1234','Sella Adi Puspitawati','Ungaran','Swasta','Beat','13000000','1'),
143 ('2324','Septi Windriyani','Semarang','Negeri','Supra X','15000000','2'),
144 ('2323','Sution','Kendal','Swasta','Revo','14000000','1'),
145 ('3333','Tia Ferida','Jepara','Negeri','Mega Pro','17000000','3'),
146 ('4345','Rizal Afendy','Pati','Swasta','Tiger','21000000','2');
147 select * from ${name_tb2[1]};"
148}
149
150options[0]="Soal A"
151options[1]="Soal B"
152
153function ACTIONS {
154 if [[ ${choices[0]} ]]; then
155 echo -e $C"\nSoal A :\n"$N
156 soal_a
157 fi
158 if [[ ${choices[1]} ]]; then
159 echo -e $C"\nSoal B :\n"$N
160 soal_b
161 fi
162}
163
164ERROR=" "
165
166clear
167
168function MENU {
169 echo -e "============================="
170 echo -e $C"Nama :"$C $R"Muhamad Zainal Makruf\n"$N
171 echo -e $C"NIM :"$C $R"161240000573\n"$N
172 echo -e $C"Makul :"$C $R"Basis Data"$N
173 echo -e "============================="
174 echo -e $R"\nMenu Options\n"$N
175 for NUM in ${!options[@]}; do
176 echo -e $C"[""${choices[NUM]:- }""]" $(( NUM+1 ))") ${options[NUM]}"$N
177 done
178 echo -e $R"$ERROR"$N
179}
180
181while MENU && read -e -p "Pilih opsi yang diinginkan(Enter saat selesai): " -n1 SELECTION && [[ -n "$SELECTION" ]]; do
182 clear
183 if [[ "$SELECTION" == *[[:digit:]]* && $SELECTION -ge 1 && $SELECTION -le ${#options[@]} ]]; then
184 (( SELECTION-- ))
185 if [[ "${choices[SELECTION]}" == "+" ]]; then
186 choices[SELECTION]=""
187 else
188 choices[SELECTION]="+"
189 fi
190 ERROR=" "
191 else
192 ERROR="Opsi tidak valid: $SELECTION"
193 fi
194done
195
196ACTIONS