Contoh Database Pembuatan Cafe [PDF]

Citation preview

mysql> create database cafe; ERROR 1007 (HY000): Can't create database 'cafe'; database exists mysql> create database cafe; ERROR 1007 (HY000): Can't create database 'cafe'; database exists mysql> dop database cafe; 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 'dop d atabase cafe' at line 1 mysql> drop database cafe; Query OK, 1 row affected (1.16 sec) mysql> create database cafe; Query OK, 1 row affected (0.02 sec) mysql> show databases; +-----------------------+ | Database | +-----------------------+ | information_schema | | 141402021_ridhofariha | | cafe | | cdcol | | mysql | | performance_schema | | phpmyadmin | | test | | webauth | +-----------------------+ 9 rows in set (0.33 sec) mysql> use cafe; Database changed mysql> create table makanan; ERROR 1113 (42000): A table must have at least 1 column mysql> create table makanan -> ( -> kd_makanan varchar(7) not null primary key, -> nama_makanan varchar(20), -> harga int(10) -> ); Query OK, 0 rows affected (0.80 sec) mysql> desc makanan; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | kd_makanan | varchar(7) | NO | PRI | NULL | | | nama_makanan | varchar(20) | YES | | NULL | | | harga | int(10) | YES | | NULL | | +--------------+-------------+------+-----+---------+-------+ 3 rows in set (0.13 sec) mysql> insert into makanan -> values('MAK00001', 'Rainbow Cake', 25000); Query OK, 1 row affected, 1 warning (0.11 sec) mysql> insert into makanan -> values('MAK00002', 'Red Velvet Key', 24000); ERROR 1062 (23000): Duplicate entry 'MAK0000' for key 'PRIMARY' mysql> insert into makanan



-> values('MAK00002', 'Red Velvet Key', 24000); ERROR 1062 (23000): Duplicate entry 'MAK0000' for key 'PRIMARY' mysql> insert into makanan -> values('MAK00003', 'Red Velvet cake', 24000); ERROR 1062 (23000): Duplicate entry 'MAK0000' for key 'PRIMARY' mysql> insert into makanan -> ; 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 '' at line 1 mysql> select * from mahasiswa; ERROR 1146 (42S02): Table 'cafe.mahasiswa' doesn't exist mysql> select * from makanan; +------------+--------------+-------+ | kd_makanan | nama_makanan | harga | +------------+--------------+-------+ | MAK0000 | Rainbow Cake | 25000 | +------------+--------------+-------+ 1 row in set (0.00 sec) mysql> delete from makanan -> where kd_makanan='MK0000'; Query OK, 0 rows affected (0.20 sec) mysql> insert into makanan -> values('MAK0001', 'Rainbow cake', 25000); Query OK, 1 row affected (0.11 sec) mysql> insert into makanan -> values('MAK0002', 'Red velvet cake', 24000); Query OK, 1 row affected (0.08 sec) mysql> insert into makanan -> values('MAK0003', 'Black forest', 20000); Query OK, 1 row affected (0.09 sec) mysql> insert into makanan -> values('MAK0004', 'waffle', 25000); Query OK, 1 row affected (0.09 sec) mysql> insert into makanan -> values('MAK0005', 'cheesecake', 26000); Query OK, 1 row affected (0.05 sec) mysql> insert into makanan -> values('MAK0006', 'green tea cake', 30000); Query OK, 1 row affected (0.09 sec) mysql> alter table makanan -> add jumlah_stok int(3); Query OK, 0 rows affected (0.86 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select table makanan; 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 'table makanan' at line 1 mysql> select * from makanan; +------------+-----------------+-------+-------------+ | kd_makanan | nama_makanan | harga | jumlah_stok |



+------------+-----------------+-------+-------------+ | MAK0000 | Rainbow Cake | 25000 | NULL | | MAK0001 | Rainbow cake | 25000 | NULL | | MAK0002 | Red velvet cake | 24000 | NULL | | MAK0003 | Black forest | 20000 | NULL | | MAK0004 | waffle | 25000 | NULL | | MAK0005 | cheesecake | 26000 | NULL | | MAK0006 | green tea cake | 30000 | NULL | +------------+-----------------+-------+-------------+ 7 rows in set (0.00 sec) mysql> alter table makanan -> change jumlah_stok jumlah int(3); Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc makanan; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | kd_makanan | varchar(7) | NO | PRI | NULL | | | nama_makanan | varchar(20) | YES | | NULL | | | harga | int(10) | YES | | NULL | | | jumlah | int(3) | YES | | NULL | | +--------------+-------------+------+-----+---------+-------+ 4 rows in set (0.08 sec) mysql> insert into makanan -> values('MAK0007', 'brownies', 18000); ERROR 1136 (21S01): Column count doesn't match value count at row 1 mysql> insert into makanan -> values('MAK0007', 'brownies', 18000); ERROR 1136 (21S01): Column count doesn't match value count at row 1 mysql> insert into makanan -> values('MAK0007', 'brownies', 18000); ERROR 1136 (21S01): Column count doesn't match value count at row 1 mysql> select * from makanan; +------------+-----------------+-------+--------+ | kd_makanan | nama_makanan | harga | jumlah | +------------+-----------------+-------+--------+ | MAK0000 | Rainbow Cake | 25000 | NULL | | MAK0001 | Rainbow cake | 25000 | NULL | | MAK0002 | Red velvet cake | 24000 | NULL | | MAK0003 | Black forest | 20000 | NULL | | MAK0004 | waffle | 25000 | NULL | | MAK0005 | cheesecake | 26000 | NULL | | MAK0006 | green tea cake | 30000 | NULL | +------------+-----------------+-------+--------+ 7 rows in set (0.00 sec) mysql> values('MAK0007', 'brownies', 18000, ' '); 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 'value s('MAK0007', 'brownies', 18000, ' ')' at line 1 mysql> insert into makanan -> ; 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 '' at line 1 mysql> insert into makanan(kd_makanan, nama_makanan, harga)



-> values('MAK0007', 'brownies', 18000); Query OK, 1 row affected (0.09 sec) mysql> insert into makanan(kd_makanan, nama_makanan, harga) -> values('MAK0008', 'ice cream cake', 24000); Query OK, 1 row affected (0.13 sec) mysql> insert into makanan(kd_makanan, nama_makanan, harga) -> values('MAK0009', 'kue apem', 2000); Query OK, 1 row affected (0.08 sec) mysql> alter table makanan -> drop jumlah; Query OK, 0 rows affected (0.58 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into makanan(kd_makanan, nama_makanan, harga) -> values('MAK0010', 'cupcake', 14000); Query OK, 1 row affected (0.13 sec) mysql> select * from makanan; +------------+-----------------+-------+ | kd_makanan | nama_makanan | harga | +------------+-----------------+-------+ | MAK0000 | Rainbow Cake | 25000 | | MAK0001 | Rainbow cake | 25000 | | MAK0002 | Red velvet cake | 24000 | | MAK0003 | Black forest | 20000 | | MAK0004 | waffle | 25000 | | MAK0005 | cheesecake | 26000 | | MAK0006 | green tea cake | 30000 | | MAK0007 | brownies | 18000 | | MAK0008 | ice cream cake | 24000 | | MAK0009 | kue apem | 2000 | | MAK0010 | cupcake | 14000 | +------------+-----------------+-------+ 11 rows in set (0.00 sec) mysql> delete from makanan -> where kd_makanan='MAK0000'; Query OK, 1 row affected (0.09 sec) mysql> select * from makanan; +------------+-----------------+-------+ | kd_makanan | nama_makanan | harga | +------------+-----------------+-------+ | MAK0001 | Rainbow cake | 25000 | | MAK0002 | Red velvet cake | 24000 | | MAK0003 | Black forest | 20000 | | MAK0004 | waffle | 25000 | | MAK0005 | cheesecake | 26000 | | MAK0006 | green tea cake | 30000 | | MAK0007 | brownies | 18000 | | MAK0008 | ice cream cake | 24000 | | MAK0009 | kue apem | 2000 | | MAK0010 | cupcake | 14000 | +------------+-----------------+-------+ 10 rows in set (0.00 sec) mysql> select * from makanan



-> where harga=25000; +------------+--------------+-------+ | kd_makanan | nama_makanan | harga | +------------+--------------+-------+ | MAK0001 | Rainbow cake | 25000 | | MAK0004 | waffle | 25000 | +------------+--------------+-------+ 2 rows in set (0.00 sec) mysql> select * from makanan -> where harga select * from makanan -> where harga>25000; +------------+----------------+-------+ | kd_makanan | nama_makanan | harga | +------------+----------------+-------+ | MAK0005 | cheesecake | 26000 | | MAK0006 | green tea cake | 30000 | +------------+----------------+-------+ 2 rows in set (0.00 sec) mysql> select * from makanan -> where harga select * from makanan -> where harga>=25000; +------------+----------------+-------+ | kd_makanan | nama_makanan | harga | +------------+----------------+-------+ | MAK0001 | Rainbow cake | 25000 | | MAK0004 | waffle | 25000 | | MAK0005 | cheesecake | 26000 | | MAK0006 | green tea cake | 30000 | +------------+----------------+-------+ 4 rows in set (0.00 sec)



mysql> select * from makanan -> where harga25000; +------------+-----------------+-------+ | kd_makanan | nama_makanan | harga | +------------+-----------------+-------+ | MAK0002 | Red velvet cake | 24000 | | MAK0003 | Black forest | 20000 | | MAK0005 | cheesecake | 26000 | | MAK0006 | green tea cake | 30000 | | MAK0007 | brownies | 18000 | | MAK0008 | ice cream cake | 24000 | | MAK0009 | kue apem | 2000 | | MAK0010 | cupcake | 14000 | +------------+-----------------+-------+ 8 rows in set (0.00 sec) mysql> select * from makanan -> where nama_makanan='Rainbow cake' or harga='25000'; +------------+--------------+-------+ | kd_makanan | nama_makanan | harga | +------------+--------------+-------+ | MAK0001 | Rainbow cake | 25000 | | MAK0004 | waffle | 25000 | +------------+--------------+-------+ 2 rows in set (0.03 sec) mysql> where nama_makanan='Rainbow cake' or harga='25000'; 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 'where nama_makanan='Rainbow cake' or harga='25000'' at line 1 mysql> select * from makanan -> where nama_makanan='Brownies' or harga='25000'; +------------+--------------+-------+ | kd_makanan | nama_makanan | harga | +------------+--------------+-------+ | MAK0001 | Rainbow cake | 25000 | | MAK0004 | waffle | 25000 | | MAK0007 | brownies | 18000 | +------------+--------------+-------+ 3 rows in set (0.00 sec) mysql> select * from makanan -> where nama_makanan='Rainbow cake' and harga='25000'; +------------+--------------+-------+ | kd_makanan | nama_makanan | harga | +------------+--------------+-------+ | MAK0001 | Rainbow cake | 25000 | +------------+--------------+-------+ 1 row in set (0.00 sec) mysql> select * from makanan -> where harga != 25000 ; +------------+-----------------+-------+ | kd_makanan | nama_makanan | harga | +------------+-----------------+-------+ | MAK0002 | Red velvet cake | 24000 | | MAK0003 | Black forest | 20000 | | MAK0005 | cheesecake | 26000 | | MAK0006 | green tea cake | 30000 |



| MAK0007 | brownies | 18000 | | MAK0008 | ice cream cake | 24000 | | MAK0009 | kue apem | 2000 | | MAK0010 | cupcake | 14000 | +------------+-----------------+-------+ 8 rows in set (0.00 sec) mysql> select * from makanan -> where harga between 20000 and 250000 ; +------------+-----------------+-------+ | kd_makanan | nama_makanan | harga | +------------+-----------------+-------+ | MAK0001 | Rainbow cake | 25000 | | MAK0002 | Red velvet cake | 24000 | | MAK0003 | Black forest | 20000 | | MAK0004 | waffle | 25000 | | MAK0005 | cheesecake | 26000 | | MAK0006 | green tea cake | 30000 | | MAK0008 | ice cream cake | 24000 | +------------+-----------------+-------+ 7 rows in set (0.02 sec) mysql> select * from makanan -> where harga not between 20000 and 250000 ; +------------+--------------+-------+ | kd_makanan | nama_makanan | harga | +------------+--------------+-------+ | MAK0007 | brownies | 18000 | | MAK0009 | kue apem | 2000 | | MAK0010 | cupcake | 14000 | +------------+--------------+-------+ 3 rows in set (0.00 sec) mysql> select * from makanan -> where nama_makanan like 'R%' ; +------------+-----------------+-------+ | kd_makanan | nama_makanan | harga | +------------+-----------------+-------+ | MAK0001 | Rainbow cake | 25000 | | MAK0002 | Red velvet cake | 24000 | +------------+-----------------+-------+ 2 rows in set (0.00 sec) mysql> select * from makanan -> where nama_makanan like '%e' ; +------------+-----------------+-------+ | kd_makanan | nama_makanan | harga | +------------+-----------------+-------+ | MAK0001 | Rainbow cake | 25000 | | MAK0002 | Red velvet cake | 24000 | | MAK0004 | waffle | 25000 | | MAK0005 | cheesecake | 26000 | | MAK0006 | green tea cake | 30000 | | MAK0008 | ice cream cake | 24000 | | MAK0010 | cupcake | 14000 | +------------+-----------------+-------+ 7 rows in set (0.00 sec) mysql> select * from makanan -> where nama_makanan like '%i%' ;



+------------+----------------+-------+ | kd_makanan | nama_makanan | harga | +------------+----------------+-------+ | MAK0001 | Rainbow cake | 25000 | | MAK0007 | brownies | 18000 | | MAK0008 | ice cream cake | 24000 | +------------+----------------+-------+ 3 rows in set (0.00 sec) mysql> select * from makanan -> where nama_makanan like '%s%' ; +------------+--------------+-------+ | kd_makanan | nama_makanan | harga | +------------+--------------+-------+ | MAK0003 | Black forest | 20000 | | MAK0005 | cheesecake | 26000 | | MAK0007 | brownies | 18000 | +------------+--------------+-------+ 3 rows in set (0.00 sec) mysql> select * from makanan -> where nama_makanan like '%5%' ; Empty set (0.00 sec) mysql> select * from makanan -> where harga like '%5%' ; +------------+--------------+-------+ | kd_makanan | nama_makanan | harga | +------------+--------------+-------+ | MAK0001 | Rainbow cake | 25000 | | MAK0004 | waffle | 25000 | +------------+--------------+-------+ 2 rows in set (0.00 sec) mysql> select avg(harga) as 'Harga rata-rata' from makanan; +-----------------+ | Harga rata-rata | +-----------------+ | 20800.0000 | +-----------------+ 1 row in set (0.05 sec) mysql> select count(harga) as 'Harga rata-rata' from makanan; +-----------------+ | Harga rata-rata | +-----------------+ | 10 | +-----------------+ 1 row in set (0.00 sec) mysql> select min(harga) as 'Harga terendah' from makanan; +----------------+ | Harga terendah | +----------------+ | 2000 | +----------------+ 1 row in set (0.00 sec) mysql> select count(harga) as 'Jumlah menu' from makanan; +-------------+



| Jumlah menu | +-------------+ | 10 | +-------------+ 1 row in set (0.00 sec) mysql> select max(harga) as 'Harga tertinggi' from makanan; +-----------------+ | Harga tertinggi | +-----------------+ | 30000 | +-----------------+ 1 row in set (0.00 sec) mysql> select sum(harga) as 'Total harga' from makanan; +-------------+ | Total harga | +-------------+ | 208000 | +-------------+ 1 row in set (0.00 sec) mysql> select round(7.639); +--------------+ | round(7.639) | +--------------+ | 8 | +--------------+ 1 row in set (0.20 sec) mysql> select pow(8,2) -> ; +----------+ | pow(8,2) | +----------+ | 64 | +----------+ 1 row in set (0.07 sec) mysql> select pow(8,3); +----------+ | pow(8,3) | +----------+ | 512 | +----------+ 1 row in set (0.00 sec) mysql> select pow(harga,3); ERROR 1054 (42S22): Unknown column 'harga' in 'field list' mysql> select pow(harga,2); ERROR 1054 (42S22): Unknown column 'harga' in 'field list' mysql> select pow(harga,2) from makanan; +--------------+ | pow(harga,2) | +--------------+ | 625000000 | | 576000000 | | 400000000 | | 625000000 | | 676000000 |



| 900000000 | | 324000000 | | 576000000 | | 4000000 | | 196000000 | +--------------+ 10 rows in set (0.00 sec) mysql> select sqrt(81); +----------+ | sqrt(81) | +----------+ | 9 | +----------+ 1 row in set (0.04 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2015-05-06 15:14:06 | +---------------------+ 1 row in set (1.97 sec) mysql> select datediff('2015-06-05', '1997-05-17'); +--------------------------------------+ | datediff('2015-06-05', '1997-05-17') | +--------------------------------------+ | 6593 | +--------------------------------------+ 1 row in set (0.17 sec) mysql> exit