PERTEMUAN 6 SQL
1. Membuat database dan tabel-tabelnya
-
Membuat
database
mysql>
create database universitas;
Query
OK, 1 row affected (0.06 sec)
a. Membuat tabel instruktur
mysql>
create table instruktur(nip varchar(5) primary key, namains varchar(30),
jurusan
varchar(20), asalkota varchar(20));
Query
OK, 0 rows affected (0.18 sec)
-
Mengisi
tabel instruktur
mysql>
insert into instruktur values('1',
'Steve Wozniak', 'Ilmu Komputer', 'Ba
ntul');
Query
OK, 1 row affected (0.10 sec)
mysql>
insert into instruktur values('2',
'Steve Jobs', 'Seni Rupa', 'Solo');
Query
OK, 1 row affected (0.07 sec)
mysql>
insert into instruktur values('3',
'James Gosling', 'Ilmu Komputer', 'Kl
aten');
Query
OK, 1 row affected (0.06 sec)
mysql>
insert into instruktur values('4', 'Bill
Gates', 'Ilmu Komputer', 'Magel
ang');
Query
OK, 1 row affected (0.07 sec)
mysql>
select*from instruktur;
+-----+---------------+---------------+----------+
|
nip | namains | jurusan | asalkota |
+-----+---------------+---------------+----------+
|
1 | Steve Wozniak | Ilmu Komputer |
Bantul |
|
2 | Steve Jobs | Seni Rupa | Solo
|
|
3 | James Gosling | Ilmu Komputer |
Klaten |
|
4 | Bill Gates | Ilmu Komputer | Magelang |
+-----+---------------+---------------+----------+
4
rows in set (0.10 sec)
a. Membuat tabel matakuliah
-
Membuat
tabel matakuliah
mysql>
create table matakuliah(nomk varchar(10) primary key, namamk varchar(30),
sks varchar(5));
Query
OK, 0 rows affected (0.13 sec)
mysql>
insert into matakuliah values('KOM101', 'Algoritma dan Pemrograman', '3')
;
Query
OK, 1 row affected (0.06 sec)
mysql>
insert into matakuliah values('KOM102', 'Basis Data', '3');
Query
OK, 1 row affected (0.07 sec)
mysql>
insert into matakuliah values('SR101', 'Desain Elementer', '3');
Query
OK, 1 row affected (0.06 sec)
mysql>
insert into matakuliah values('KOM201', 'Pemrograman Besbasis Objek', '3'
);
Query
OK, 1 row affected (0.07 sec)
a. Membuat tabel kuliah
mysql> create table kuliah(nip
varchar(5), nomk varchar(10), ruangan varchar(5),
jmlmhs varchar(5), foreign key(nip) references
instruktur(nip), foreign key(nom
k) references matakuliah(nomk));
Query OK, 0 rows affected (0.19 sec)
mysql> insert into kuliah values('1',
'KOM101', '101', '50');
Query OK, 1 row affected (0.30 sec)
mysql> insert into kuliah values('1',
'KOM102', '102', '35');
Query OK, 1 row affected (0.07 sec)
mysql> insert into kuliah values('2',
'SR101', '101', '45');
Query OK, 1 row affected (0.07 sec)
mysql> insert into kuliah values('3',
'KOM201', '101', '55');
Query OK, 1 row affected (0.06 sec)
1. Tuliskan Query untuk mendapatkan
data-data di bawah ini :
-
Membuat
tabel join yaitu tabel kuliah_join1
mysql>
alter table kuliah_join1 add instruktur varchar(20) after nomk;
Query
OK, 4 rows affected (0.36 sec)
Records:
4 Duplicates: 0 Warnings: 0
mysql>
update kuliah_join1, instruktur set kuliah_join1.instruktur=instruktur.namains
where kul
iah_join1.nip=instruktur.nip;
Query
OK, 2 rows affected (0.06 sec)
Rows
matched: 4 Changed: 2 Warnings: 0
mysql>
alter table kuliah_join1 add jurusan varchar(20) after instruktur;
Query
OK, 4 rows affected (0.54 sec)
Records:
4 Duplicates: 0 Warnings: 0
mysql>
update kuliah_join1, instruktur set kuliah_join1.jurusan=instruktur.jurusan
where kuliah
Query
OK, 4 rows affected (0.06 sec)
Rows matched: 4 Changed: 4
Warnings: 0
mysql>
alter table kuliah_join1 add matakuliah varchar(30) after jurusan;
Query
OK, 4 rows affected (0.49 sec)
Records:
4 Duplicates: 0 Warnings: 0
mysql>
update kuliah_join1, matakuliah set kuliah_join1.matakuliah=matakuliah.namamk
where kuli
ah_join1.nomk=matakuliah.nomk;
Query
OK, 4 rows affected (0.04 sec)
Rows
matched: 4 Changed: 4 Warnings: 0
Untuk lebih lengkap dan jelasnya silahkan lihat DISINI
terima kasih...
