MODUL 6 SQL

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...