Penggunaan CAST & CASE pada Database SQL
stmik@stmik-lab:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 124
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> create table nilai (
-> nama varchar(10),
-> nilai float);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into nilai values("AB",70.5);
Query OK, 1 row affected (0.00 sec)
mysql> insert into nilai values("AC",60);
Query OK, 1 row affected (0.00 sec)
mysql> insert into nilai values("AD",20);
Query OK, 1 row affected (0.00 sec)
mysql> insert into nilai values("AE",30.2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into nilai values("AF",80);
Query OK, 1 row affected (0.00 sec)
mysql> insert into nilai values("AG",65);
Query OK, 1 row affected (0.00 sec)
mysql> select * from nilai;
+------+-------+
| nama | nilai |
+------+-------+
| AB | 70.5 |
| AC | 60 |
| AD | 20 |
| AE | 30.2 |
| AF | 80 |
| AG | 65 |
+------+-------+
6 rows in set (0.00 sec)
mysql> select nama, CAST(nilai as signed int) as nilaiakhir from nilai;
+------+------------+
| nama | nilaiakhir |
+------+------------+
| AB | 70 |
| AC | 60 |
| AD | 20 |
| AE | 30 |
| AF | 80 |
| AG | 65 |
+------+------------+
6 rows in set (0.00 sec)
mysql> use yuliyanti;
Database changed
mysql> select * from pegawai;
+-----+-------------+---------+------------+----------+
| nip | nama | gaji | departemen | kota |
+-----+-------------+---------+------------+----------+
| 1 | faud | 3000000 | akunting | jogja |
| 2 | john | 3600000 | pemasaran | jogja |
| 3 | freska | 1500000 | personalia | klaten |
| 4 | kadarisman | 960000 | akunting | sleman |
| 5 | krisna | 1200000 | personalia | magelang |
| 6 | rianto | 750000 | produksi | bantul |
| 7 | fajar | 2500000 | akunting | klaten |
| 8 | ida lestari | 750000 | produksi | sleman |
| 9 | dian | 8000000 | personalia | jogja |
| 10 | kartono | 7000000 | akunting | jogja |
+-----+-------------+---------+------------+----------+
10 rows in set (0.01 sec)
mysql> select nama, CASE kota when 'jogja' then 'yogya' else kota end from pegawai;
+-------------+---------------------------------------------------+
| nama | CASE kota when 'jogja' then 'yogya' else kota end |
+-------------+---------------------------------------------------+
| faud | yogya |
| john | yogya |
| freska | klaten |
| kadarisman | sleman |
| krisna | magelang |
| rianto | bantul |
| fajar | klaten |
| ida lestari | sleman |
| dian | yogya |
| kartono | yogya |
+-------------+---------------------------------------------------+
10 rows in set (0.00 sec)
mysql> select nama, CASE kota when 'jogja' then 'yogya' else kota end as kota from pegawai;
+-------------+----------+
| nama | kota |
+-------------+----------+
| faud | yogya |
| john | yogya |
| freska | klaten |
| kadarisman | sleman |
| krisna | magelang |
| rianto | bantul |
| fajar | klaten |
| ida lestari | sleman |
| dian | yogya |
| kartono | yogya |
+-------------+----------+
10 rows in set (0.00 sec)
mysql> select nama,nilai, CASE nilai when '70' then 'B' else nilai end as grade from nilai;
+------+-------+-------+
| nama | nilai | grade |
+------+-------+-------+
| AB | 70.5 | 70.5 |
| AC | 60 | 60 |
| AD | 20 | 20 |
| AE | 30.2 | 30.2 |
| AF | 80 | 80 |
| AG | 65 | 65 |
+------+-------+-------+
6 rows in set (0.00 sec)
mysql> select nama,nilai, CASE nilai when '70.5' then 'B' else nilai end as grade from nilai;
+------+-------+-------+
| nama | nilai | grade |
+------+-------+-------+
| AB | 70.5 | B |
| AC | 60 | 60 |
| AD | 20 | 20 |
| AE | 30.2 | 30.2 |
| AF | 80 | 80 |
| AG | 65 | 65 |
+------+-------+-------+
6 rows in set (0.00 sec)
mysql> select nama,nilai, CASE nilai when '>70.5' then 'B' else nilai end as grade from nilai;
+------+-------+-------+
| nama | nilai | grade |
+------+-------+-------+
| AB | 70.5 | 70.5 |
| AC | 60 | 60 |
| AD | 20 | 20 |
| AE | 30.2 | 30.2 |
| AF | 80 | 80 |
| AG | 65 | 65 |
+------+-------+-------+
6 rows in set, 6 warnings (0.00 sec)
mysql> select nama,nilai, CASE nilai when '70.5' then 'B' else nilai end as grade from nilai;
+------+-------+-------+
| nama | nilai | grade |
+------+-------+-------+
| AB | 70.5 | B |
| AC | 60 | 60 |
| AD | 20 | 20 |
| AE | 30.2 | 30.2 |
| AF | 80 | 80 |
| AG | 65 | 65 |
+------+-------+-------+
6 rows in set (0.00 sec)
mysql> select nama,nilai, CASE nilai when '70.5' then 'B' else nilai end as grade from nilai;
+------+-------+-------+
| nama | nilai | grade |
+------+-------+-------+
| AB | 70.5 | B |
| AC | 60 | 60 |
| AD | 20 | 20 |
| AE | 30.2 | 30.2 |
| AF | 80 | 80 |
| AG | 65 | 65 |
+------+-------+-------+
6 rows in set (0.00 sec)
mysql> select nama,CASE nilai when '>70.5' then 'B' else nilai end as grade from nilai;
+------+-------+
| nama | grade |
+------+-------+
| AB | 70.5 |
| AC | 60 |
| AD | 20 |
| AE | 30.2 |
| AF | 80 |
| AG | 65 |
+------+-------+
6 rows in set, 6 warnings (0.00 sec)
mysql> select nama,CASE nilai when nilai>'70.5' then 'B' else nilai end as grade from nilai;
+------+-------+
| nama | grade |
+------+-------+
| AB | 70.5 |
| AC | 60 |
| AD | 20 |
| AE | 30.2 |
| AF | 80 |
| AG | 65 |
+------+-------+
6 rows in set (0.00 sec)
mysql> select nama,CASE nilai when '70.5' then 'B' else nilai end as grade from nilai;
+------+-------+
| nama | grade |
+------+-------+
| AB | B |
| AC | 60 |
| AD | 20 |
| AE | 30.2 |
| AF | 80 |
| AG | 65 |
+------+-------+
6 rows in set (0.00 sec)
mysql> select nama,nilai,CASE nilai when '70.5' then 'B' else nilai end as grade from nilai;
+------+-------+-------+
| nama | nilai | grade |
+------+-------+-------+
| AB | 70.5 | B |
| AC | 60 | 60 |
| AD | 20 | 20 |
| AE | 30.2 | 30.2 |
| AF | 80 | 80 |
| AG | 65 | 65 |
+------+-------+-------+
6 rows in set (0.00 sec)
mysql> select nama,nilai,CASE nilai when nilai>70 then 'B' else nilai end as grade from nilai;
+------+-------+-------+
| nama | nilai | grade |
+------+-------+-------+
| AB | 70.5 | 70.5 |
| AC | 60 | 60 |
| AD | 20 | 20 |
| AE | 30.2 | 30.2 |
| AF | 80 | 80 |
| AG | 65 | 65 |
+------+-------+-------+
6 rows in set (0.00 sec)
mysql> select nama,nilai,CASE when nilai>70 then 'B' else nilai end as grade from nilai;
+------+-------+-------+
| nama | nilai | grade |
+------+-------+-------+
| AB | 70.5 | B |
| AC | 60 | 60 |
| AD | 20 | 20 |
| AE | 30.2 | 30.2 |
| AF | 80 | B |
| AG | 65 | 65 |
+------+-------+-------+
6 rows in set (0.00 sec)
mysql> select nama,nilai,CASE when nilai>=80 then 'A' when nilai>=70 then 'B' else nilai end as grade from nilai;
+------+-------+-------+
| nama | nilai | grade |
+------+-------+-------+
| AB | 70.5 | B |
| AC | 60 | 60 |
| AD | 20 | 20 |
| AE | 30.2 | 30.2 |
| AF | 80 | A |
| AG | 65 | 65 |
+------+-------+-------+
6 rows in set (0.00 sec)
mysql> select nama,nilai,CASE when nilai>=80 then 'A' when nilai>=70 then 'B' else nilai end as grade from nilai;
+------+-------+-------+
| nama | nilai | grade |
+------+-------+-------+
| AB | 70.5 | B |
| AC | 60 | 60 |
| AD | 20 | 20 |
| AE | 30.2 | 30.2 |
| AF | 80 | A |
| AG | 65 | 65 |
+------+-------+-------+
6 rows in set (0.00 sec)
mysql> select nama,nilai,CASE when nilai>=80 then 'A' when nilai>=70 then 'B' when nilai>=60 then 'c' when nilai>=50 then 'D' else nilai end as grade from nilai;
+------+-------+-------+
| nama | nilai | grade |
+------+-------+-------+
| AB | 70.5 | B |
| AC | 60 | c |
| AD | 20 | 20 |
| AE | 30.2 | 30.2 |
| AF | 80 | A |
| AG | 65 | c |
+------+-------+-------+
6 rows in set (0.00 sec)
mysql> select nama,nilai,CASE when nilai>=80 then 'A' when nilai>=70 then 'B' when nilai>=60 then 'c' when nilai>=50 then 'D' else 'E' end as grade from nilai;+------+-------+-------+
| nama | nilai | grade |
+------+-------+-------+
| AB | 70.5 | B |
| AC | 60 | c |
| AD | 20 | E |
| AE | 30.2 | E |
| AF | 80 | A |
| AG | 65 | c |
+------+-------+-------+
6 rows in set (0.00 sec)
mysql>
0 comments