Sintkas SQL untuk soal sehingga menampilkan sbb:
| No_kar | Nama_kar | Jenkel |
| 001 | Azizah R. | L |
| 002 | Faridah M. | P |
| 003 | A. Lubis | L |
| 004 | Rozantia A. | L |
| 005 | Amwalia | P |
| 006 | Andi | L |
| 007 | Anggita | p |
| No_kar | Gol | Gaji_pokok | Tunjangan |
| 001 | II | 1000000 | 250000 |
| 002 | III | 1500000 | 350000 |
| 003 | II | 1000000 | 250000 |
| 004 | IV | 2000000 | 500000 |
| 005 | III | 1500000 | 350000 |
| 006 | II | 1000000 | 250000 |
| 007 | IV | 2000000 | 500000 |
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.45-community-nt-log MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE DATABASE KARYAWAN;
Query OK, 1 row affected (0.00 sec)
mysql> USE KARYAWAN;
Database changed
mysql> CREATE TABLE TBKAR(NO_KAR VARCHAR (3), NAMA_KAR VARCHAR (30), JENKEL CHAR
(1), PRIMARY KEY (NO_KAR));
Query OK, 0 rows affected (0.44 sec)
mysql> DESC TBKAR ;
+-----------------+-----------------+------+-------+-----------+---------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------+------+-------+-----------+---------+
| NO_KAR | varchar(3) | NO | PRI | | |
| NAMA_KAR | varchar(30) | YES | | NULL | |
| JENKEL | char(1) | YES | | NULL | |
+-----------------+-----------------+------+-------+-----------+---------+
3 rows in set (0.00 sec)
mysql> INSERT INTO TBKAR VALUES ('001','AZIZAH R.','L');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO TBKAR VALUES ('002','FARIDAH M.','P');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO TBKAR VALUES ('003','A. LUBIS','L');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO TBKAR VALUES ('004','ROZANTI A.','L');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO TBKAR VALUES ('005','AMWALIA','P');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO TBKAR VALUES ('006','ANDI','L');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO TBKAR VALUES ('007','ANGITA','P');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT*FROM TBKAR;
+------------+------------------+-----------+
| NO_KAR | NAMA_KAR | JENKEL |
+------------+------------------+-----------+
| 001 | AZIZAH R. | L |
| 002 | FARIDAH M. | P |
| 003 | A. LUBIS | L |
| 004 | ROZANTI A. | L |
| 005 | AMWALIA | P |
| 006 | ANDI | L |
| 007 | ANGITA | P |
+------------+------------------+-----------+
7 rows in set (0.00 sec)
mysql> CREATE TABLE TBGAJI (NO_KAR VARCHAR (3), GOL CHAR(3), GAJI_POKOK INT(
11),TUNJANGAN INT (11), PRIMARY KEY (NO_KAR));
Query OK, 0 rows affected (0.44 sec)
mysql> DESC TBGAJI;
+---------------------+---------------+-------+-----+------------+--------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------------+-------+-----+------------+--------+
| NO_KAR | varchar(3) | NO | PRI | | |
| GOL | char(3) | YES | | NULL | |
| GAJI_POKOK | int(11) | YES | | NULL | |
| TUNJANGAN | int(11) | YES | | NULL | |
+---------------------+---------------+-------+-----+------------+--------+
4 rows in set (0.00 sec)
mysql> INSERT INTO TBGAJI VALUES ('001','II','1.000.000','250.000');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO TBGAJI VALUES ('002','III','1.500.000','350.000');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO TBGAJI VALUES ('003','II','1.000.000','250.000');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO TBGAJI VALUES ('004','IV','2.000.000','500.000');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO TBGAJI VALUES ('005','III','1.500.000','350.000');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO TBGAJI VALUES ('006','II','1.000.000','250.000');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO TBGAJI VALUES ('007','IV','2.000.000','500.000');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM TBGAJI;
+------------+--------+------------------+------------------+
| NO_KAR | GOL | GAJI_POKOK | TUNJANGAN |
+------------+--------+------------------+------------------+
| 001 | II | 1000000 | 250000 |
| 002 | III | 1500000 | 350000 |
| 003 | II | 1000000 | 250000 |
| 004 | IV | 2000000 | 500000 |
| 005 | III | 1500000 | 350000 |
| 006 | II | 1000000 | 250000 |
| 007 | IV | 2000000 | 500000 |
+-----------+---------+------------------+-------------------+
7 rows in set (0.00 sec)
PEMBAHASAN
1. Buat sintaks SQL untuk menampilkan Daftar Karyawan gol IV
Jawab:
mysql> SELECT TBKAR.NO_KAR,TBKAR.NAMA_KAR,TBGAJI.GOL,TBGAJI.GAJI_POKOK FROM TBKA
R,TBGAJI WHERE TBKAR.NO_KAR=TBGAJI.NO_KAR AND TBGAJI.GOL='IV';
+------------+------------------+-------+-------------------+
| NO_KAR | NAMA_KAR | GOL | GAJI_POKOK |
+------------+------------------+-------+-------------------+
| 004 | ROZANTI A. | IV | 2.000.000 |
| 007 | ANGITA | IV | 2.000.000 |
+------------+------------------+-------+-------------------+
2 rows in set (0.00 sec)
2. Buat sintaks SQL untuk menampilka daftar Karyawan dengan Gaji_Pokok >1000.000
Jawab :
mysql> SELECT TBKAR.NO_KAR,TBKAR.NAMA_KAR,TBGAJI.GOL,TBGAJI.GAJI_POKOK,TBGAJI.TU
NJANGAN FROM TBKAR,TBGAJI WHERE TBKAR.NO_KAR=TBGAJI.NO_KAR AND TBGAJI.GAJI_POKOK
>'1.000.000';
+------------+------------------+--------+-------------------+------------------+
| NO_KAR | NAMA_KAR | GOL | GAJI_POKOK | TUNJANGAN |
+------------+------------------+--------+-------------------+------------------+
| 002 | FARIDAH M. | III | 1.500.000 | 350.000 |
| 004 | ROZANTI A. | IV | 2.000.000 | 500.000 |
| 005 | AMWALIA | III | 1.500.000 | 350.000 |
| 007 | ANGITA | IV | 2.000.000 | 500.000 |
+------------+------------------+--------+-------------------+------------------+
4 rows in set (0.00 sec)
3. buat sintaks SQL untuk rekapitulasi jumlah Karyawan berdasarkan Jenkel, sehingga hasilnya adalah sbb :
| Jenkel | Jumlah |
| L | 4 |
| P | 3 |
Jawab:
mysql> SELECT JENKEL, COUNT(*) AS JUMLAH FROM TBKAR GROUP BY JENKEL;
+----------+-------------+
| JENKEL | JUMLAH |
+----------+-------------+
| L | 4 |
| P | 3 |
+----------+-------------+
2 rows in set (0.00 sec)
4. jika pajak dari Gaji_pokok adalah 10% buatlah sintaks SQL untuk menampilka Daftar Gaji_bersih yang diterima oleh setiap karyawan dengan format sbb :
| No_kar | Nama_kar | Gol | Gaji_pokok | Pajak | Tunjangan | Gaji_bersih |
| | | | | | | |
Jawab:
mysql> SELECT NO_KAR, NAMA_KAR, GOL, GAJI_POKOK, FORMAT(10/100 * GAJI_POKOK,0) A
S PAJAK, TUNJANGAN, FORMAT( GAJI_POKOK - (10/100 * GAJI_POKOK) + TBGAJI.TUNJANGA
N,0) AS GAJIBERSIH FROM TBGAJI NATURAL JOIN TBKAR;
+------------+------------------+--------+-------------------+-----------+-------------------+-----------------+
| NO_KAR | NAMA_KAR | GOL | GAJI_POKOK | PAJAK | TUNJANGAN | GAJIBERSIH |
+------------+------------------+--------+-------------------+-----------+-------------------+-----------------+
| 001 | AZIZAH R. | II | 1000000 | 100,000 | 250000 | 1,150,000 |
| 002 | FARIDAH M. | III | 1500000 | 150,000 | 350000 | 1,700,000 |
| 003 | A. LUBIS | II | 1000000 | 100,000 | 250000 | 1,150,000 |
| 004 | ROZANTI A. | IV | 2000000 | 200,000 | 500000 | 2,300,000 |
| 005 | AMWALIA | III | 1500000 | 150,000 | 350000 | 1,700,000 |
| 006 | ANDI | II | 1000000 | 100,000 | 250000 | 1,150,000 |
| 007 | ANGITA | IV | 2000000 | 200,000 | 500000 | 2,300,000 |
+------------+------------------+--------+-------------------+-----------+-------------------+-----------------+
7 rows in set (0.00 sec)
Tidak ada komentar:
Posting Komentar