jawabtts0809
If you can't read please download the document
TRANSCRIPT
mysql> create database TTSNIM;Query OK, 1 row affected (0.27 sec)mysql> use ttsnimDatabase changedmysql> create table tblKartu ( -> KdKartu Char(5) Not Null Primary Key, -> Produk Varchar(15), -> Nominal MediumInt Unsigned Not Null Default 0, -> Berlaku Date Not Null Default '2009-12-12', -> Aktif TinyInt(3) Not Null Default 5, -> Harga MediumInt Unsigned Not Null Default 0);Query OK, 0 rows affected (1.36 sec)mysql> desc tblkartu;+---------+-----------------------+------+-----+------------+-------+| Field | Type | Null | Key | Default | Extra |+---------+-----------------------+------+-----+------------+-------+| KdKartu | char(5) | NO | PRI | NULL | | | Produk | varchar(15) | YES | | NULL | | | Nominal | mediumint(8) unsigned | NO | | 0 | | | Berlaku | date | NO | | 2009-12-12 | | | Aktif | tinyint(3) | NO | | 5 | | | Harga | mediumint(8) unsigned | NO | | 0 | | +---------+-----------------------+------+-----+------------+-------+6 rows in set (0.64 sec)mysql> drop table tblkartu;Query OK, 0 rows affected (0.05 sec)mysql> show tables;Empty set (0.06 sec)mysql> create table tblKartu ( -> KdKartu Char(5) Not Null Primary Key, -> Produk Varchar(15), -> Nominal MediumInt(6) Unsigned Not Null Default 0, -> Berlaku Date Not Null Default '2009-12-12', -> Aktif TinyInt(3) Not Null Default 5, -> Harga MediumInt(6) Unsigned Not Null Default 0);Query OK, 0 rows affected (0.05 sec)mysql> show tables;+------------------+| Tables_in_ttsnim |+------------------+| tblkartu | +------------------+1 row in set (0.00 sec)mysql> desc tblkartu;+---------+-----------------------+------+-----+------------+-------+| Field | Type | Null | Key | Default | Extra |+---------+-----------------------+------+-----+------------+-------+| KdKartu | char(5) | NO | PRI | NULL | | | Produk | varchar(15) | YES | | NULL | | | Nominal | mediumint(6) unsigned | NO | | 0 | | | Berlaku | date | NO | | 2009-12-12 | | | Aktif | tinyint(3) | NO | | 5 | | | Harga | mediumint(6) unsigned | NO | | 0 | | +---------+-----------------------+------+-----+------------+-------+6 rows in set (0.03 sec)mysql> insert into tblKartu values -> ('M20','Mentari',20000,'2009-05-03',10,21000);Query OK, 1 row affected (0.13 sec)mysql> insert into tblKartu values -> ('S50','Simpati',50000,'2009-12-05',30,50000), -> ('FR25','Fren',25000,'2009-10-26',15,28250), -> ('M30','Mentari',30000,'2009-11-30',15,31000), -> ('M75','Mentari',75000,'2009-12-31',45,72500), -> ('S100','Simpati',100000,'2010-05-31',60,97000), -> ('FL20','Flexi',20000,'2009-07-21',15,21250), -> ('FR50','Fren',50000,'2009-12-30',30,53500), -> ('FL50','Flexi',50000,'2009-12-31',30,48000), -> ('XLJ35','XL Jempol',35000,'2009-09-26',35,35000);Query OK, 9 rows affected (0.06 sec)Records: 9 Duplicates: 0 Warnings: 0mysql> select * from tblKartu;+---------+-----------+---------+------------+-------+-------+| KdKartu | Produk | Nominal | Berlaku | Aktif | Harga |+---------+-----------+---------+------------+-------+-------+| M20 | Mentari | 20000 | 2009-05-03 | 10 | 21000 | | S50 | Simpati | 50000 | 2009-12-05 | 30 | 50000 | | FR25 | Fren | 25000 | 2009-10-26 | 15 | 28250 | | M30 | Mentari | 30000 | 2009-11-30 | 15 | 31000 | | M75 | Mentari | 75000 | 2009-12-31 | 45 | 72500 | | S100 | Simpati | 100000 | 2010-05-31 | 60 | 97000 | | FL20 | Flexi | 20000 | 2009-07-21 | 15 | 21250 | | FR50 | Fren | 50000 | 2009-12-30 | 30 | 53500 | | FL50 | Flexi | 50000 | 2009-12-31 | 30 | 48000 | | XLJ35 | XL Jempol | 35000 | 2009-09-26 | 35 | 35000 | +---------+-----------+---------+------------+-------+-------+10 rows in set (0.09 sec)mysql> Alter table tblKartu add Jenis Enum('GSM','CDMA') Not Null after Produk;Query OK, 10 rows affected (0.55 sec)Records: 10 Duplicates: 0 Warnings: 0mysql> desc tblkartu;+---------+-----------------------+------+-----+------------+-------+| Field | Type | Null | Key | Default | Extra |+---------+-----------------------+------+-----+------------+-------+| KdKartu | char(5) | NO | PRI | NULL | | | Produk | varchar(15) | YES | | NULL | | | Jenis | enum('GSM','CDMA') | NO | | NULL | | | Nominal | mediumint(6) unsigned | NO | | 0 | | | Berlaku | date | NO | | 2009-12-12 | | | Aktif | tinyint(3) | NO | | 5 | | | Harga | mediumint(6) unsigned | NO | | 0 | | +---------+-----------------------+------+-----+------------+-------+7 rows in set (0.20 sec)mysql> select * from tblKartu;+---------+-----------+-------+---------+------------+-------+-------+| KdKartu | Produk | Jenis | Nominal | Berlaku | Aktif | Harga |+---------+-----------+-------+---------+------------+-------+-------+| M20 | Mentari | GSM | 20000 | 2009-05-03 | 10 | 21000 | | S50 | Simpati | GSM | 50000 | 2009-12-05 | 30 | 50000 | | FR25 | Fren | GSM | 25000 | 2009-10-26 | 15 | 28250 | | M30 | Mentari | GSM | 30000 | 2009-11-30 | 15 | 31000 | | M75 | Mentari | GSM | 75000 | 2009-12-31 | 45 | 72500 | | S100 | Simpati | GSM | 100000 | 2010-05-31 | 60 | 97000 | | FL20 | Flexi | GSM | 20000 | 2009-07-21 | 15 | 21250 | | FR50 | Fren | GSM | 50000 | 2009-12-30 | 30 | 53500 | | FL50 | Flexi | GSM | 50000 | 2009-12-31 | 30 | 48000 | | XLJ35 | XL Jempol | GSM | 35000 | 2009-09-26 | 35 | 35000 | +---------+-----------+-------+---------+------------+-------+-------+10 rows in set (0.00 sec)mysql> update tblkartu set jenis='CDMA' where Produk IN('Fren','Flexi');Query OK, 4 rows affected (0.13 sec)Rows matched: 4 Changed: 4 Warnings: 0mysql> select * from tblKartu;+---------+-----------+-------+---------+------------+-------+-------+| KdKartu | Produk | Jenis | Nominal | Berlaku | Aktif | Harga |+---------+-----------+-------+---------+------------+-------+-------+| M20 | Mentari | GSM | 20000 | 2009-05-03 | 10 | 21000 | | S50 | Simpati | GSM | 50000 | 2009-12-05 | 30 | 50000 | | FR25 | Fren | CDMA | 25000 | 2009-10-26 | 15 | 28250 | | M30 | Mentari | GSM | 30000 | 2009-11-30 | 15 | 31000 | | M75 | Mentari | GSM | 75000 | 2009-12-31 | 45 | 72500 | | S100 | Simpati | GSM | 100000 | 2010-05-31 | 60 | 97000 | | FL20 | Flexi | CDMA | 20000 | 2009-07-21 | 15 | 21250 | | FR50 | Fren | CDMA | 50000 | 2009-12-30 | 30 | 53500 | | FL50 | Flexi | CDMA | 50000 | 2009-12-31 | 30 | 48000 | | XLJ35 | XL Jempol | GSM | 35000 | 2009-09-26 | 35 | 35000 | +---------+-----------+-------+---------+------------+-------+-------+10 rows in set (0.00 sec)mysql> select * from tblkartu -> where aktif between 30 and 50 or jenis like 'CDMA' -> order by aktif desc;+---------+-----------+-------+---------+------------+-------+-------+| KdKartu | Produk | Jenis | Nominal | Berlaku | Aktif | Harga |+---------+-----------+-------+---------+------------+-------+-------+| M75 | Mentari | GSM | 75000 | 2009-12-31 | 45 | 72500 | | XLJ35 | XL Jempol | GSM | 35000 | 2009-09-26 | 35 | 35000 | | S50 | Simpati | GSM | 50000 | 2009-12-05 | 30 | 50000 | | FR50 | Fren | CDMA | 50000 | 2009-12-30 | 30 | 53500 | | FL50 | Flexi | CDMA | 50000 | 2009-12-31 | 30 | 48000 | | FR25 | Fren | CDMA | 25000 | 2009-10-26 | 15 | 28250 | | FL20 | Flexi | CDMA | 20000 | 2009-07-21 | 15 | 21250 | +---------+-----------+-------+---------+------------+-------+-------+7 rows in set (0.05 sec)mysql> select * from tblkartu -> where aktif >= 30 and aktif order by aktif desc;+---------+-----------+-------+---------+------------+-------+-------+| KdKartu | Produk | Jenis | Nominal | Berlaku | Aktif | Harga |+---------+-----------+-------+---------+------------+-------+-------+| M75 | Mentari | GSM | 75000 | 2009-12-31 | 45 | 72500 | | XLJ35 | XL Jempol | GSM | 35000 | 2009-09-26 | 35 | 35000 | | S50 | Simpati | GSM | 50000 | 2009-12-05 | 30 | 50000 | | FR50 | Fren | CDMA | 50000 | 2009-12-30 | 30 | 53500 | | FL50 | Flexi | CDMA | 50000 | 2009-12-31 | 30 | 48000 | | FR25 | Fren | CDMA | 25000 | 2009-10-26 | 15 | 28250 | | FL20 | Flexi | CDMA | 20000 | 2009-07-21 | 15 | 21250 | +---------+-----------+-------+---------+------------+-------+-------+7 rows in set (0.05 sec)mysql> select * from tblkartu -> where (aktif >= 30 or aktif order by aktif desc;+---------+-----------+-------+---------+------------+-------+-------+| KdKartu | Produk | Jenis | Nominal | Berlaku | Aktif | Harga |+---------+-----------+-------+---------+------------+-------+-------+| S100 | Simpati | GSM | 100000 | 2010-05-31 | 60 | 97000 | | M75 | Mentari | GSM | 75000 | 2009-12-31 | 45 | 72500 | | XLJ35 | XL Jempol | GSM | 35000 | 2009-09-26 | 35 | 35000 | | S50 | Simpati | GSM | 50000 | 2009-12-05 | 30 | 50000 | | FR50 | Fren | CDMA | 50000 | 2009-12-30 | 30 | 53500 | | FL50 | Flexi | CDMA | 50000 | 2009-12-31 | 30 | 48000 | | M30 | Mentari | GSM | 30000 | 2009-11-30 | 15 | 31000 | | FR25 | Fren | CDMA | 25000 | 2009-10-26 | 15 | 28250 | | FL20 | Flexi | CDMA | 20000 | 2009-07-21 | 15 | 21250 | | M20 | Mentari | GSM | 20000 | 2009-05-03 | 10 | 21000 | +---------+-----------+-------+---------+------------+-------+-------+10 rows in set (0.01 sec)mysql> select distinct produk -> from tblkartu -> order by produk;+-----------+| produk |+-----------+| Flexi | | Fren | | Mentari | | Simpati | | XL Jempol | +-----------+5 rows in set (0.16 sec)mysql> select Produk,jenis,Berlaku,Nominal,Harga -> from tblkartu -> where month(berlaku) between 10 and 12 or jenis not like 'Fren' -> order by Harga desc;+-----------+-------+------------+---------+-------+| Produk | jenis | Berlaku | Nominal | Harga |+-----------+-------+------------+---------+-------+| Simpati | GSM | 2010-05-31 | 100000 | 97000 | | Mentari | GSM | 2009-12-31 | 75000 | 72500 | | Fren | CDMA | 2009-12-30 | 50000 | 53500 | | Simpati | GSM | 2009-12-05 | 50000 | 50000 | | Flexi | CDMA | 2009-12-31 | 50000 | 48000 | | XL Jempol | GSM | 2009-09-26 | 35000 | 35000 | | Mentari | GSM | 2009-11-30 | 30000 | 31000 | | Fren | CDMA | 2009-10-26 | 25000 | 28250 | | Flexi | CDMA | 2009-07-21 | 20000 | 21250 | | Mentari | GSM | 2009-05-03 | 20000 | 21000 | +-----------+-------+------------+---------+-------+10 rows in set (0.23 sec)mysql> select Produk,jenis,Berlaku,Nominal,Harga -> from tblkartu -> where month(berlaku) between 10 and 12 and jenis not like 'Fren' -> order by Harga desc;+---------+-------+------------+---------+-------+| Produk | jenis | Berlaku | Nominal | Harga |+---------+-------+------------+---------+-------+| Mentari | GSM | 2009-12-31 | 75000 | 72500 | | Fren | CDMA | 2009-12-30 | 50000 | 53500 | | Simpati | GSM | 2009-12-05 | 50000 | 50000 | | Flexi | CDMA | 2009-12-31 | 50000 | 48000 | | Mentari | GSM | 2009-11-30 | 30000 | 31000 | | Fren | CDMA | 2009-10-26 | 25000 | 28250 | +---------+-------+------------+---------+-------+6 rows in set (0.00 sec)mysql> select Produk,jenis,Berlaku,Nominal,Harga -> from tblkartu -> where (month(berlaku) between 10 and 12) or jenis not like 'Fren' -> order by Harga desc;+-----------+-------+------------+---------+-------+| Produk | jenis | Berlaku | Nominal | Harga |+-----------+-------+------------+---------+-------+| Simpati | GSM | 2010-05-31 | 100000 | 97000 | | Mentari | GSM | 2009-12-31 | 75000 | 72500 | | Fren | CDMA | 2009-12-30 | 50000 | 53500 | | Simpati | GSM | 2009-12-05 | 50000 | 50000 | | Flexi | CDMA | 2009-12-31 | 50000 | 48000 | | XL Jempol | GSM | 2009-09-26 | 35000 | 35000 | | Mentari | GSM | 2009-11-30 | 30000 | 31000 | | Fren | CDMA | 2009-10-26 | 25000 | 28250 | | Flexi | CDMA | 2009-07-21 | 20000 | 21250 | | Mentari | GSM | 2009-05-03 | 20000 | 21000 | +-----------+-------+------------+---------+-------+10 rows in set (1.03 sec)mysql> select Produk,jenis,Berlaku,Nominal,Harga -> from tblkartu -> where (month(berlaku) between 10 and 12) or porduk not like 'Fren' -> order by Harga desc;ERROR 1054 (42S22): Unknown column 'porduk' in 'where clause'mysql> select Produk,jenis,Berlaku,Nominal,Harga -> from tblkartu -> where (month(berlaku) between 10 and 12) or produk not like 'Fren' -> order by Harga desc;+-----------+-------+------------+---------+-------+| Produk | jenis | Berlaku | Nominal | Harga |+-----------+-------+------------+---------+-------+| Simpati | GSM | 2010-05-31 | 100000 | 97000 | | Mentari | GSM | 2009-12-31 | 75000 | 72500 | | Fren | CDMA | 2009-12-30 | 50000 | 53500 | | Simpati | GSM | 2009-12-05 | 50000 | 50000 | | Flexi | CDMA | 2009-12-31 | 50000 | 48000 | | XL Jempol | GSM | 2009-09-26 | 35000 | 35000 | | Mentari | GSM | 2009-11-30 | 30000 | 31000 | | Fren | CDMA | 2009-10-26 | 25000 | 28250 | | Flexi | CDMA | 2009-07-21 | 20000 | 21250 | | Mentari | GSM | 2009-05-03 | 20000 | 21000 | +-----------+-------+------------+---------+-------+10 rows in set (4.39 sec)mysql> from tblkartu -> where (month(berlaku) between 10 and 12) and produk not like 'Fren' -> order by Harga desc;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from tblkartuwhere (month(berlaku) between 10 and 12) and produk not like 'Fren' at line 1mysql> select Produk,jenis,Berlaku,Nominal,Harga -> from tblkartu -> where (month(berlaku) between 10 and 12) and produk not like 'Fren' -> order by Harga desc;+---------+-------+------------+---------+-------+| Produk | jenis | Berlaku | Nominal | Harga |+---------+-------+------------+---------+-------+| Mentari | GSM | 2009-12-31 | 75000 | 72500 | | Simpati | GSM | 2009-12-05 | 50000 | 50000 | | Flexi | CDMA | 2009-12-31 | 50000 | 48000 | | Mentari | GSM | 2009-11-30 | 30000 | 31000 | +---------+-------+------------+---------+-------+4 rows in set (2.56 sec)mysql> select * from tblkartu;+---------+-----------+-------+---------+------------+-------+-------+| KdKartu | Produk | Jenis | Nominal | Berlaku | Aktif | Harga |+---------+-----------+-------+---------+------------+-------+-------+| M20 | Mentari | GSM | 20000 | 2009-05-03 | 10 | 21000 | | S50 | Simpati | GSM | 50000 | 2009-12-05 | 30 | 50000 | | FR25 | Fren | CDMA | 25000 | 2009-10-26 | 15 | 28250 | | M30 | Mentari | GSM | 30000 | 2009-11-30 | 15 | 31000 | | M75 | Mentari | GSM | 75000 | 2009-12-31 | 45 | 72500 | | S100 | Simpati | GSM | 100000 | 2010-05-31 | 60 | 97000 | | FL20 | Flexi | CDMA | 20000 | 2009-07-21 | 15 | 21250 | | FR50 | Fren | CDMA | 50000 | 2009-12-30 | 30 | 53500 | | FL50 | Flexi | CDMA | 50000 | 2009-12-31 | 30 | 48000 | | XLJ35 | XL Jempol | GSM | 35000 | 2009-09-26 | 35 | 35000 | +---------+-----------+-------+---------+------------+-------+-------+10 rows in set (3.42 sec)mysql> delete from tblkartu where month(berlaku) = 5 and year(berlaku) = 2009;Query OK, 1 row affected (3.45 sec)mysql> select * from tblkartu;+---------+-----------+-------+---------+------------+-------+-------+| KdKartu | Produk | Jenis | Nominal | Berlaku | Aktif | Harga |+---------+-----------+-------+---------+------------+-------+-------+| S50 | Simpati | GSM | 50000 | 2009-12-05 | 30 | 50000 | | FR25 | Fren | CDMA | 25000 | 2009-10-26 | 15 | 28250 | | M30 | Mentari | GSM | 30000 | 2009-11-30 | 15 | 31000 | | M75 | Mentari | GSM | 75000 | 2009-12-31 | 45 | 72500 | | S100 | Simpati | GSM | 100000 | 2010-05-31 | 60 | 97000 | | FL20 | Flexi | CDMA | 20000 | 2009-07-21 | 15 | 21250 | | FR50 | Fren | CDMA | 50000 | 2009-12-30 | 30 | 53500 | | FL50 | Flexi | CDMA | 50000 | 2009-12-31 | 30 | 48000 | | XLJ35 | XL Jempol | GSM | 35000 | 2009-09-26 | 35 | 35000 | +---------+-----------+-------+---------+------------+-------+-------+9 rows in set (0.38 sec)mysql> \t