pengguanaa perintah sql inner join dan outer join
stmik@stmik-lab:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 141
Server version: 5.1.41-3ubuntu12.3+kharisma1 (Kharisma)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use yuliyanti;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from film;
+-----------+-------------------+-------+
| kode_film | judul | tahun |
+-----------+-------------------+-------+
| BATFO | BATMAN FOREVER | 1995 |
| GJANE | G.I JANE | 1997 |
| GLORI | GLORIA | 1999 |
| RAMBO | RAMBO FIRST BLOOD | 1990 |
| SPECI | THE SPECIALIST | 1994 |
| SPEED | SPEED | 1994 |
+-----------+-------------------+-------+
6 rows in set (0.01 sec)
mysql> select * from main;
+----------+-----------+--------------+
| id_artis | kode_film | peran |
+----------+-----------+--------------+
| DENIM | GJANE | JORDAN ONEIL |
| DREWB | BATFO | SUGAR |
| KEANU | SPEED | JACK TRAVEN |
| SANDR | SPEED | ANNIE PORTER |
| STONE | SPECI | MAYMUNRO |
| SYLVE | SPECI | RAY QUICK |
+----------+-----------+--------------+
6 rows in set (0.00 sec)
mysql> select * from artis;
+-------+--------------------+------------+------+
| ID | NAMA | TGL_LAHIR | JK |
+-------+--------------------+------------+------+
| 43E1 | DREW BARRYMORE | 1995-10-10 | P |
| 43E2 | SHARON STONE | 2016-11-10 | P |
| 43E3 | SYLVESTER STALLONE | 2016-11-10 | L |
| 43E4 | KEVIN COSTER | 2015-12-09 | L |
| 43E5 | JULLA ROBERT | 1997-08-09 | P |
| 43E6 | DEMI MO0RE | 1990-07-12 | P |
| 43E7 | CINDY CRAWFORD | 1996-05-11 | P |
| 43E8 | CAMERON DLAZ | 1998-12-11 | P |
| 43E9 | KEANU REEVES | 1998-12-12 | L |
| 43E10 | SANDRA BULLOK | 1999-05-01 | P |
| 43E11 | RICHARD GERE | 1996-02-01 | L |
+-------+--------------------+------------+------+
11 rows in set (0.01 sec)
mysql> select * from info_artis;
+----------+-------------------+
| id_artis | nama_artis |
+----------+-------------------+
| DEMIM | DEMI MOORE |
| DREWB | DREW BARRYMORE |
| JOLIE | ANGELINA JOLIE |
| KEANU | KEANU REEVES |
| SANDR | SANDRA BULLOCK |
| STONE | SHARON STONE |
| SYLVE | SYLVESTER STALLON |
+----------+-------------------+
7 rows in set (0.03 sec)
mysql> select i.nama_artis,m.peran,f.judul,f.tahun from info_artis i, main m, film f where i.id_artis = m.id_artis;
+-------------------+--------------+-------------------+-------+
| nama_artis | peran | judul | tahun |
+-------------------+--------------+-------------------+-------+
| DREW BARRYMORE | SUGAR | BATMAN FOREVER | 1995 |
| KEANU REEVES | JACK TRAVEN | BATMAN FOREVER | 1995 |
| SANDRA BULLOCK | ANNIE PORTER | BATMAN FOREVER | 1995 |
| SHARON STONE | MAYMUNRO | BATMAN FOREVER | 1995 |
| SYLVESTER STALLON | RAY QUICK | BATMAN FOREVER | 1995 |
| DREW BARRYMORE | SUGAR | G.I JANE | 1997 |
| KEANU REEVES | JACK TRAVEN | G.I JANE | 1997 |
| SANDRA BULLOCK | ANNIE PORTER | G.I JANE | 1997 |
| SHARON STONE | MAYMUNRO | G.I JANE | 1997 |
| SYLVESTER STALLON | RAY QUICK | G.I JANE | 1997 |
| DREW BARRYMORE | SUGAR | GLORIA | 1999 |
| KEANU REEVES | JACK TRAVEN | GLORIA | 1999 |
| SANDRA BULLOCK | ANNIE PORTER | GLORIA | 1999 |
| SHARON STONE | MAYMUNRO | GLORIA | 1999 |
| SYLVESTER STALLON | RAY QUICK | GLORIA | 1999 |
| DREW BARRYMORE | SUGAR | RAMBO FIRST BLOOD | 1990 |
| KEANU REEVES | JACK TRAVEN | RAMBO FIRST BLOOD | 1990 |
| SANDRA BULLOCK | ANNIE PORTER | RAMBO FIRST BLOOD | 1990 |
| SHARON STONE | MAYMUNRO | RAMBO FIRST BLOOD | 1990 |
| SYLVESTER STALLON | RAY QUICK | RAMBO FIRST BLOOD | 1990 |
| DREW BARRYMORE | SUGAR | THE SPECIALIST | 1994 |
| KEANU REEVES | JACK TRAVEN | THE SPECIALIST | 1994 |
| SANDRA BULLOCK | ANNIE PORTER | THE SPECIALIST | 1994 |
| SHARON STONE | MAYMUNRO | THE SPECIALIST | 1994 |
| SYLVESTER STALLON | RAY QUICK | THE SPECIALIST | 1994 |
| DREW BARRYMORE | SUGAR | SPEED | 1994 |
| KEANU REEVES | JACK TRAVEN | SPEED | 1994 |
| SANDRA BULLOCK | ANNIE PORTER | SPEED | 1994 |
| SHARON STONE | MAYMUNRO | SPEED | 1994 |
| SYLVESTER STALLON | RAY QUICK | SPEED | 1994 |
+-------------------+--------------+-------------------+-------+
30 rows in set (0.00 sec)
mysql> select i.nama_artis,m.peran,f.judul,f.tahun from info_artis i, main m, film f where i.id_artis = m.id_artis and f.id_artis;
ERROR 1054 (42S22): Unknown column 'f.id_artis' in 'where clause'
mysql> select i.nama_artis,m.peran,f.judul,f.tahun from info_artis i, main m, film f where i.id_artis = m.id_artis and ^CCtrl-C -- exit!
Aborted
stmik@stmik-lab:~$ sudo mysql -u root -p
[sudo] password for stmik:
Sorry, try again.
[sudo] password for stmik:
Sorry, try again.
[sudo] password for stmik:
Sorry, try again.
sudo: 3 incorrect password attempts
stmik@stmik-lab:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 142
Server version: 5.1.41-3ubuntu12.3+kharisma1 (Kharisma)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use yuliyanti;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select i.nama_artis,m.peran,f.judul,f.tahun from info_artis i, main m, film f where i.id_artis = m.id_artis and m.kode_film = f.kode_film;
+-------------------+--------------+----------------+-------+
| nama_artis | peran | judul | tahun |
+-------------------+--------------+----------------+-------+
| DREW BARRYMORE | SUGAR | BATMAN FOREVER | 1995 |
| KEANU REEVES | JACK TRAVEN | SPEED | 1994 |
| SANDRA BULLOCK | ANNIE PORTER | SPEED | 1994 |
| SHARON STONE | MAYMUNRO | THE SPECIALIST | 1994 |
| SYLVESTER STALLON | RAY QUICK | THE SPECIALIST | 1994 |
+-------------------+--------------+----------------+-------+
5 rows in set (0.00 sec)
mysql> select * from info_artis inner join main on info_artis.id_artis = main.id_artis;
+----------+-------------------+----------+-----------+--------------+
| id_artis | nama_artis | id_artis | kode_film | peran |
+----------+-------------------+----------+-----------+--------------+
| DREWB | DREW BARRYMORE | DREWB | BATFO | SUGAR |
| KEANU | KEANU REEVES | KEANU | SPEED | JACK TRAVEN |
| SANDR | SANDRA BULLOCK | SANDR | SPEED | ANNIE PORTER |
| STONE | SHARON STONE | STONE | SPECI | MAYMUNRO |
| SYLVE | SYLVESTER STALLON | SYLVE | SPECI | RAY QUICK |
+----------+-------------------+----------+-----------+--------------+
5 rows in set (0.00 sec)
mysql> select * from info_artis inner join main on info_artis.id_artis = main.id_artis inner join film on main.kode_film = film.kode_film;
+----------+-------------------+----------+-----------+--------------+-----------+----------------+-------+
| id_artis | nama_artis | id_artis | kode_film | peran | kode_film | judul | tahun |
+----------+-------------------+----------+-----------+--------------+-----------+----------------+-------+
| DREWB | DREW BARRYMORE | DREWB | BATFO | SUGAR | BATFO | BATMAN FOREVER | 1995 |
| KEANU | KEANU REEVES | KEANU | SPEED | JACK TRAVEN | SPEED | SPEED | 1994 |
| SANDR | SANDRA BULLOCK | SANDR | SPEED | ANNIE PORTER | SPEED | SPEED | 1994 |
| STONE | SHARON STONE | STONE | SPECI | MAYMUNRO | SPECI | THE SPECIALIST | 1994 |
| SYLVE | SYLVESTER STALLON | SYLVE | SPECI | RAY QUICK | SPECI | THE SPECIALIST | 1994 |
+----------+-------------------+----------+-----------+--------------+-----------+----------------+-------+
5 rows in set (0.00 sec)
mysql> select nama_artis,peran,judul,tahun from info_artis inner join main on info_artis.id_artis = main.id_artis inner join film on main.kode_film = film.kode_film;
+-------------------+--------------+----------------+-------+
| nama_artis | peran | judul | tahun |
+-------------------+--------------+----------------+-------+
| DREW BARRYMORE | SUGAR | BATMAN FOREVER | 1995 |
| KEANU REEVES | JACK TRAVEN | SPEED | 1994 |
| SANDRA BULLOCK | ANNIE PORTER | SPEED | 1994 |
| SHARON STONE | MAYMUNRO | THE SPECIALIST | 1994 |
| SYLVESTER STALLON | RAY QUICK | THE SPECIALIST | 1994 |
+-------------------+--------------+----------------+-------+
5 rows in set (0.00 sec)
mysql> select * from info_artis LEFT outer join main on info_artis.id_artis =main.id_artis;
+----------+-------------------+----------+-----------+--------------+
| id_artis | nama_artis | id_artis | kode_film | peran |
+----------+-------------------+----------+-----------+--------------+
| DEMIM | DEMI MOORE | NULL | NULL | NULL |
| DREWB | DREW BARRYMORE | DREWB | BATFO | SUGAR |
| JOLIE | ANGELINA JOLIE | NULL | NULL | NULL |
| KEANU | KEANU REEVES | KEANU | SPEED | JACK TRAVEN |
| SANDR | SANDRA BULLOCK | SANDR | SPEED | ANNIE PORTER |
| STONE | SHARON STONE | STONE | SPECI | MAYMUNRO |
| SYLVE | SYLVESTER STALLON | SYLVE | SPECI | RAY QUICK |
+----------+-------------------+----------+-----------+--------------+
7 rows in set (0.02 sec)
mysql> select * from info_artis RIGHT outer join main on info_artis.id_artis =main.id_artis;
+----------+-------------------+----------+-----------+--------------+
| id_artis | nama_artis | id_artis | kode_film | peran |
+----------+-------------------+----------+-----------+--------------+
| NULL | NULL | DENIM | GJANE | JORDAN ONEIL |
| DREWB | DREW BARRYMORE | DREWB | BATFO | SUGAR |
| KEANU | KEANU REEVES | KEANU | SPEED | JACK TRAVEN |
| SANDR | SANDRA BULLOCK | SANDR | SPEED | ANNIE PORTER |
| STONE | SHARON STONE | STONE | SPECI | MAYMUNRO |
| SYLVE | SYLVESTER STALLON | SYLVE | SPECI | RAY QUICK |
+----------+-------------------+----------+-----------+--------------+
6 rows in set (0.00 sec)
mysql>
0 comments