Rabu, 16 Januari 2013

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

Tidak ada komentar:

Posting Komentar