10 rows in set (0.24 sec)
15121038_basis_data
MariaDB [(none)]> use kbm_disekolah Database changed MariaDB [kbm_disekolah]> create table( -> nip int (16) not null primary key -> nama char (40), -> jenis_kelamin; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '( nip int (16) not null primary key nama char (40), jenis_kelamin' at line 1 MariaDB [kbm_disekolah]> create table guru( -> nip char (16) not null primary key, -> nama varchar (40), -> jenis_klm char (32), -> alamat char (33)); Query OK, 0 rows affected (1.86 sec) MariaDB [kbm_disekolah]> desc guru; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | nip | char(16) | NO | PRI | NULL | | | nama | varchar(40) | YES | | NULL | | | jenis_klm | char(32) | YES | | NULL | | | alamat | char(33) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 4 rows in set (0.30 sec) MariaDB [kbm_disekolah]> create table siswa( -> nama char (23), -> jenis_kln char (46), -> alamat char (64), -> no_siswa char (63) not null primary key); Query OK, 0 rows affected (0.37 sec) MariaDB [kbm_disekolah]> desc siswa; +-----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+----------+------+-----+---------+-------+ | nama | char(23) | YES | | NULL | | | jenis_kln | char(46) | YES | | NULL | | | alamat | char(64) | YES | | NULL | | | no_siswa | char(63) | NO | PRI | NULL | | +-----------+----------+------+-----+---------+-------+ 4 rows in set (0.11 sec) MariaDB [kbm_disekolah]> create table materi( -> kode_mapel char (17) not null primary key, -> nama_mapel char (13), -> jlm_jam varchar (20); MariaDB [kbm_disekolah]> create table materi( -> kode_mapel char (17) not null primary key, -> nama_mapel char (13) not null, -> jml_jam char (20) not null); Query OK, 0 rows affected (0.41 sec) MariaDB [kbm_disekolah]> desc materi; +------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | Page 1
15121038_basis_data +------------+----------+------+-----+---------+-------+ | kode_mapel | char(17) | NO | PRI | NULL | | | nama_mapel | char(13) | NO | | NULL | | | jml_jam | char(20) | NO | | NULL | | +------------+----------+------+-----+---------+-------+ 3 rows in set (0.02 sec) MariaDB [kbm_disekolah]> create table buku( -> id_buku char (5) not null primary key, -> jenis_buku vachar (21) null, -> tgl_pinjam char (10) null); tgl_pinjam char (10) null)' at line 3 MariaDB [kbm_disekolah]> create table buku( -> id_buku char (5) not null primary key, -> jenis_buku vachar (21) null, -> tgl_pinjam char(10) null); MariaDB [kbm_disekolah]> create table buku( -> id_buku char (5) not null primary key, -> jenis_buku varchar (21) null, -> tgl_pinjam varchar (10) null); Query OK, 0 rows affected (0.39 sec) MariaDB [kbm_disekolah]> desc buku -> ; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | id_buku | char(5) | NO | PRI | NULL | | | jenis_buku | varchar(21) | YES | | NULL | | | tgl_pinjam | varchar(10) | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ 3 rows in set (0.02 sec) MariaDB [kbm_disekolah]> insert into materi -> kode_mapel,nama_mapel,jml_jam) -> values -> ('21','matematika','2jam'), -> ('15','bahasa','1jam'), -> ('17','sejarah','2jam'); MariaDB [kbm_disekolah]> insert into materi -> (kode_mapel,nama_mapel,jml_jam) -> values -> ('21','matematika','2jam')' '> ('15','bahasa','1jam')' -> ('17','sejarah','2jam'); MariaDB [kbm_disekolah]> insert into materi -> (kode_mapel,nama_mapel,jml_jam) -> values -> ('21','matematika','2jam'), -> ('15','bahasa','2jam'), -> ('17','sejarah','2jam'); Query OK, 3 rows affected (0.20 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [kbm_disekolah]> desc buku; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | id_buku | char(5) | NO | PRI | NULL | | | jenis_buku | varchar(21) | YES | | NULL | | | tgl_pinjam | varchar(10) | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ Page 2
3 rows in set (0.01 sec)
15121038_basis_data
MariaDB [kbm_disekolah]> insert into buku -> (id_buku,jenis_buku,tgl_pinjam) -> values -> ('BK01','pelajaran','15-4-2016'), -> ('BK02','pelajaran','16-4-2016'), -> ('BK03','pelajaran','17-4-2016'); Query OK, 3 rows affected (0.12 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [kbm_disekolah]> desc guru; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | nip | char(16) | NO | PRI | NULL | | | nama | varchar(40) | YES | | NULL | | | jenis_klm | char(32) | YES | | NULL | | | alamat | char(33) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 4 rows in set (0.08 sec) MariaDB [kbm_disekolah]> insert into guru -> (nip,nama) -> values -> ('142526478','Syaifudin'), -> ('142376543','Indah'), -> ('142536634','Amat'); Query OK, 3 rows affected (0.10 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [kbm_disekolah]> select*from guru; +-----------+-----------+-----------+--------+ | nip | nama | jenis_klm | alamat | +-----------+-----------+-----------+--------+ | 142376543 | Indah | NULL | NULL | | 142526478 | Syaifudin | NULL | NULL | | 142536634 | Amat | NULL | NULL | +-----------+-----------+-----------+--------+ 3 rows in set (0.08 sec) MariaDB [kbm_disekolah]> select*from buku; +---------+------------+------------+ | id_buku | jenis_buku | tgl_pinjam | +---------+------------+------------+ | BK01 | pelajaran | 15-4-2016 | | BK02 | pelajaran | 16-4-2016 | | BK03 | pelajaran | 17-4-2016 | +---------+------------+------------+ 3 rows in set (0.00 sec) MariaDB [kbm_disekolah]> select*from materi; +------------+------------+---------+ | kode_mapel | nama_mapel | jml_jam | +------------+------------+---------+ | 15 | bahasa | 2jam | | 17 | sejarah | 2jam | | 21 | matematika | 2jam | +------------+------------+---------+ 3 rows in set (0.00 sec) MariaDB [kbm_disekolah]> alter table guru add id_buku char(5) not null; Query OK, 0 rows affected (1.08 sec) Page 3
Records: 0
Duplicates: 0
Warnings: 0
15121038_basis_data
MariaDB [kbm_disekolah]> update guru set id_buku ='BK01' where nip = '142376543'; Query OK, 1 row affected (0.20 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [kbm_disekolah]> select*from guru; +-----------+-----------+-----------+--------+---------+ | nip | nama | jenis_klm | alamat | id_buku | +-----------+-----------+-----------+--------+---------+ | 142376543 | Indah | NULL | NULL | BK01 | | 142526478 | Syaifudin | NULL | NULL | | | 142536634 | Amat | NULL | NULL | | +-----------+-----------+-----------+--------+---------+ 3 rows in set (0.00 sec) MariaDB [kbm_disekolah]> update guru set id_buku ='BK02' where nip = '142526478'; Query OK, 1 row affected (0.42 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [kbm_disekolah]> select*from guru; +-----------+-----------+-----------+--------+---------+ | nip | nama | jenis_klm | alamat | id_buku | +-----------+-----------+-----------+--------+---------+ | 142376543 | Indah | NULL | NULL | BK01 | | 142526478 | Syaifudin | NULL | NULL | BK02 | | 142536634 | Amat | NULL | NULL | | +-----------+-----------+-----------+--------+---------+ 3 rows in set (0.00 sec) MariaDB [kbm_disekolah]> update guru set id_buku ='BK03' where nip = '142536634'; Query OK, 1 row affected (0.14 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [kbm_disekolah]> select*from guru; +-----------+-----------+-----------+--------+---------+ | nip | nama | jenis_klm | alamat | id_buku | +-----------+-----------+-----------+--------+---------+ | 142376543 | Indah | NULL | NULL | BK01 | | 142526478 | Syaifudin | NULL | NULL | BK02 | | 142536634 | Amat | NULL | NULL | BK03 | +-----------+-----------+-----------+--------+---------+ 3 rows in set (0.06 sec) MariaDB [kbm_disekolah]> alter table guru add constraint foreign key (id_buku) references buku (id_buku); Query OK, 3 rows affected (1.38 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [kbm_disekolah]> select*from materi; +------------+------------+---------+ | kode_mapel | nama_mapel | jml_jam | +------------+------------+---------+ | 15 | bahasa | 2jam | | 17 | sejarah | 2jam | | 21 | matematika | 2jam | +------------+------------+---------+ 3 rows in set (0.02 sec) MariaDB [kbm_disekolah]> select max(jml_jam) from materi; +--------------+ | max(jml_jam) | +--------------+ Page 4
| 2jam | +--------------+ 1 row in set (0.11 sec)
15121038_basis_data
MariaDB [kbm_disekolah]> select min(jml_jam) from materi; +--------------+ | min(jml_jam) | +--------------+ | 2jam | +--------------+ 1 row in set (0.00 sec) MariaDB [kbm_disekolah]> select avg(jml_jam) from materi; +--------------+ | avg(jml_jam) | +--------------+ | 2 | +--------------+ 1 row in set, 3 warnings (0.03 sec) MariaDB [kbm_disekolah]> select buku.*,guru.*from buku,guru where buku.id_buku = guru.id_buku; +---------+------------+------------+-----------+-----------+-----------+--------+---------+ | id_buku | jenis_buku | tgl_pinjam | nip | nama | jenis_klm | alamat | id_buku | +---------+------------+------------+-----------+-----------+-----------+--------+---------+ | BK01 | pelajaran | 15-4-2016 | 142376543 | Indah | NULL | NULL | BK01 | | BK02 | pelajaran | 16-4-2016 | 142526478 | Syaifudin | NULL | NULL | BK02 | | BK03 | pelajaran | 17-4-2016 | 142536634 | Amat | NULL | NULL | BK03 | +---------+------------+------------+-----------+-----------+-----------+--------+---------+ 3 rows in set (0.11 sec) MariaDB [kbm_disekolah]>
Page 5
