create database pesantren1;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> use pesantren;
Database changed
MariaDB [pesantren]> create table santri (
-> IdSantri INT NOT NULL,
-> NamaSantri Varchar(50),
-> Alamat Varchar(50),
-> IdWali INT);
MariaDB [pesantren]> DESC santri;
+————+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————+————-+——+—–+———+——-+
| IdSantri | int(11) | NO | | NULL | |
| NamaSantri | varchar(50) | YES | | NULL | |
| Alamat | varchar(50) | YES | | NULL | |
| IdWali | int(11) | YES | | NULL | |
+————+————-+——+—–+———+——-+
4 rows in set (0.022 sec)
———————-
MariaDB [pesantren]> create table wali (
-> IdWali INT Not NULL,
-> NamaWali Varchar(50),
-> Alamat Varchar(50));
Query OK, 0 rows affected (0.013 sec)
MariaDB [pesantren]> desc wali;
+———-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———-+————-+——+—–+———+——-+
| IdWali | int(11) | NO | | NULL | |
| NamaWali | varchar(50) | YES | | NULL | |
| Alamat | varchar(50) | YES | | NULL | |
+———-+————-+——+—–+———+——-+
3 rows in set (0.017 sec)
MariaDB [pesantren]>
—————————
Menambahkan promary key di idsantri dan idwali:
MariaDB [pesantren]> ALTER TABLE santri add primary key (IdSantri);
Query OK, 0 rows affected (0.034 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [pesantren]> desc santri;
+————+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————+————-+——+—–+———+——-+
| IdSantri | int(11) | NO | PRI | NULL | |
| NamaSantri | varchar(50) | YES | | NULL | |
| Alamat | varchar(50) | YES | | NULL | |
| IdWali | int(11) | YES | | NULL | |
+————+————-+——+—–+———+——-+
4 rows in set (0.020 sec)
MariaDB [pesantren]> ALTER TABLE wali add primary key (IdWali);
Query OK, 0 rows affected (0.033 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [pesantren]> Desc wali;
+———-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———-+————-+——+—–+———+——-+
| IdWali | int(11) | NO | PRI | NULL | |
| NamaWali | varchar(50) | YES | | NULL | |
| Alamat | varchar(50) | YES | | NULL | |
+———-+————-+——+—–+———+——-+
3 rows in set (0.009 sec)
MariaDB [pesantren]>
——————————
Merubah tipe data di tabel santri dan tabel wali:
MariaDB [pesantren]> alter table santri modify IdSantri Char(4);
Query OK, 1 row affected (0.064 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [pesantren]> desc santri;
+————+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————+————-+——+—–+———+——-+
| IdSantri | char(4) | NO | PRI | NULL | |
| NamaSantri | varchar(50) | YES | | NULL | |
| Alamat | varchar(50) | YES | | NULL | |
| IdWali | int(11) | YES | | NULL | |
+————+————-+——+—–+———+——-+
4 rows in set (0.034 sec)
MariaDB [pesantren]> alter table wali modify IdWali Char(4);
Query OK, 0 rows affected (0.048 sec)
Records: 0 Duplicates: 0 Warnings: 0
alter table santri modify IdWali Char(4);
MariaDB [pesantren]> desc wali;
+———-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———-+————-+——+—–+———+——-+
| IdWali | char(4) | NO | PRI | NULL | |
| NamaWali | varchar(50) | YES | | NULL | |
| Alamat | varchar(50) | YES | | NULL | |
+———-+————-+——+—–+———+——-+
3 rows in set (0.018 sec)
MariaDB [pesantren]> select * from santri;
+———-+————+——–+——–+
| IdSantri | NamaSantri | Alamat | IdWali |
+———-+————+——–+——–+
| 0 | A | Bogor | 0 |
+———-+————+——–+——–+
1 row in set (0.000 sec)
MariaDB [pesantren]>
———————————
menghapus record di tabel santri
MariaDB [pesantren]> select * from santri;
+———-+————+——–+——–+
| IdSantri | NamaSantri | Alamat | IdWali |
+———-+————+——–+——–+
| 0 | A | Bogor | 0 |
+———-+————+——–+——–+
1 row in set (0.000 sec)
MariaDB [pesantren]> truncate table santri;
Query OK, 0 rows affected (0.020 sec)
MariaDB [pesantren]> select * from santri;
Empty set (0.000 sec)
MariaDB [pesantren]>
———————-
mengisi data santri dan data wali
—————–
MariaDB [pesantren]> insert into santri values
-> (‘s001′,’A’,’Bogor’,’w001′),
-> (‘s002′,’B’,’Bogor’,’w002′),
-> (‘s003′,’C’,’Bogor’,’w003′),
-> (‘s004′,’D’,’Bogor’,’w004′),
-> (‘s005′,’E’,’Bogor’,’w005′);
Query OK, 5 rows affected (0.003 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [pesantren]> select * from santri;
+———-+————+——–+——–+
| IdSantri | NamaSantri | Alamat | IdWali |
+———-+————+——–+——–+
| s001 | A | Bogor | w001 |
| s002 | B | Bogor | w002 |
| s003 | C | Bogor | w003 |
| s004 | D | Bogor | w004 |
| s005 | E | Bogor | w005 |
+———-+————+——–+——–+
5 rows in set (0.000 sec)
MariaDB [pesantren]>
MariaDB [pesantren]> insert into wali values
-> (‘w001′,’A’,’Bogor’),
-> (‘w002′,’A’,’Bogor’),
-> (‘w003′,’C’,’Bogor’),
-> (‘w004′,’D’,’Bogor’),
-> (‘w005′,’E’,’Bogor’);
Query OK, 5 rows affected (0.003 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [pesantren]>
MariaDB [pesantren]> select * from wali;
+——–+———-+——–+
| IdWali | NamaWali | Alamat |
+——–+———-+——–+
| w001 | A | Bogor |
| w002 | A | Bogor |
| w003 | C | Bogor |
| w004 | D | Bogor |
| w005 | E | Bogor |
+——–+———-+——–+
5 rows in set (0.000 sec)
MariaDB [pesantren]>
———————————
merubah record wali di id w002 menjadi B
MariaDB [pesantren]> update wali set NamaWali=’B’ Where IdWali=’w002′;
Query OK, 1 row affected (0.003 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [pesantren]> select * from wali;
+——–+———-+——–+
| IdWali | NamaWali | Alamat |
+——–+———-+——–+
| w001 | A | Bogor |
| w002 | B | Bogor |
| w003 | C | Bogor |
| w004 | D | Bogor |
| w005 | E | Bogor |
+——–+———-+——–+
5 rows in set (0.000 sec)
MariaDB [pesantren]>
————————————
mengganti alamat santri A id s001 menjadi Tayu
MariaDB [pesantren]> update santri set Alamat=’Tayu Pati’ Where IdSantri=’s001′;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [pesantren]> select * from santri;
+———-+————+———–+——–+
| IdSantri | NamaSantri | Alamat | IdWali |
+———-+————+———–+——–+
| s001 | A | Tayu Pati | w001 |
| s002 | B | Bogor | w002 |
| s003 | C | Bogor | w003 |
| s004 | D | Bogor | w004 |
| s005 | E | Bogor | w005 |
+———-+————+———–+——–+
5 rows in set (0.000 sec)
MariaDB [pesantren]>
——————-
menampilkan alamat mahasiswa yang di akhiri dengan ti
————
MariaDB [pesantren]> select * from santri where alamat like ‘%ti’;
+———-+————+———–+——–+
| IdSantri | NamaSantri | Alamat | IdWali |
+———-+————+———–+——–+
| s001 | A | Tayu Pati | w001 |
+———-+————+———–+——–+
1 row in set (0.000 sec)
MariaDB [pesantren]>
—————–
menambahkan kolom nilai di tabel santri
————–
MariaDB [pesantren]> alter table santri add nilai int;
Query OK, 0 rows affected (0.005 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [pesantren]> select * from santri;
——————-
menambahkan beberapa record di tabel nilai
—————–
MariaDB [pesantren]> update santri set nilai=9 where IdSantri=’s001′;
Query OK, 1 row affected (0.003 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [pesantren]> update santri set nilai=10 where IdSantri=’s002′;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [pesantren]> update santri set nilai=10 where IdSantri=’s003′;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [pesantren]> update santri set nilai=10 where IdSantri=’s004′;
Query OK, 1 row affected (0.003 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [pesantren]> update santri set nilai=10 where IdSantri=’s005′;
Query OK, 1 row affected (0.003 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [pesantren]> select * from santri;
+———-+————+———–+——–+——-+
| IdSantri | NamaSantri | Alamat | IdWali | nilai |
+———-+————+———–+——–+——-+
| s001 | A | Tayu Pati | w001 | 9 |
| s002 | B | Bogor | w002 | 10 |
| s003 | C | Bogor | w003 | 10 |
| s004 | D | Bogor | w004 | 10 |
| s005 | E | Bogor | w005 | 10 |
+———-+————+———–+——–+——-+
5 rows in set (0.001 sec)
MariaDB [pesantren]>
—————————–
menghitung banyaknya data di tabel nilai:
MariaDB [pesantren]> select count(*) from santri;
+———-+
| count(*) |
+———-+
| 5 |
+———-+
1 row in set (0.000 sec)
MariaDB [pesantren]>
———————–
menghitung santri yang nilainya 10
MariaDB [pesantren]> select count(*) from santri where nilai=10;
+———-+
| count(*) |
+———-+
| 4 |
+———-+
1 row in set (0.000 sec)
MariaDB [pesantren]>
—————————
mencari data nilai terkecil:
MariaDB [pesantren]> select min(nilai) from santri;
+————+
| min(nilai) |
+————+
| 9 |
+————+
1 row in set (0.000 sec)
MariaDB [pesantren]>
————————–
mencari data nilai terbesar:
MariaDB [pesantren]> select max(nilai) from santri;
+————+
| max(nilai) |
+————+
| 10 |
+————+
1 row in set (0.000 sec)
MariaDB [pesantren]>
——————————
menghitung rata-rata nilai
MariaDB [pesantren]> select avg(nilai) from santri;
+————+
| avg(nilai) |
+————+
| 9.8000 |
+————+
1 row in set (0.000 sec)
MariaDB [pesantren]>
———–
menghitung total nilai
———–
MariaDB [pesantren]> select sum(nilai) from santri;
+————+
| sum(nilai) |
+————+
| 49 |
+————+
1 row in set (0.000 sec)
MariaDB [pesantren]>
—————–
membuat view namasantri dengan alamat di bogor
MariaDB [pesantren]> create view vbogor as select NamaSantri from santri where alamat like ‘%bogor%’;
Query OK, 0 rows affected (0.003 sec)
MariaDB [pesantren]> select * from vbogor;
+————+
| NamaSantri |
+————+
| B |
| C |
| D |
| E |
+————+
4 rows in set (0.001 sec)
MariaDB [pesantren]>
———————
membuat store prosedur mencari namasantri dari bogor
MariaDB [pesantren]> create procedure santribogor()
-> select NamaSantri from santri
-> where alamat like ‘%bogor%’;
Query OK, 0 rows affected (0.025 sec)
MariaDB [pesantren]> call santribogor;
+————+
| NamaSantri |
+————+
| B |
| C |
| D |
| E |
+————+
4 rows in set (0.001 sec)
Query OK, 0 rows affected (0.009 sec)
MariaDB [pesantren]>
———————-
menghapus database pesantren
MariaDB [pesantren]> drop database pesantren;
Query OK, 3 rows affected (0.018 sec)
Selesai dan semoga bermanfaat