Paula Noone
SQL - Summer, 2009
Fruit Table Commands
Fruitcmds

#Write comments for each of these queries
#Using two tables fruit and inventory

#1 write a query that will show the MySQL variables;
show variables;

| server_id                       | 1                          |
| skip_external_locking           | ON                         |
| skip_networking                 | OFF                        |
| skip_show_database              | OFF                        |
| slave_compressed_protocol       | OFF                        |
| slave_load_tmpdir               | /var/lib/mysql/.tmp/       |
| slave_net_timeout               | 3600                       |
| slave_skip_errors               | OFF                        |
| slave_transaction_retries       | 10                         |
| slow_launch_time                | 2                          |
| socket                          | /var/lib/mysql/mysql.sock  |
| sort_buffer_size                | 524280                     |
| sql_big_selects                 | ON                         |
| sql_mode                        |                            |
| sql_notes                       | ON                         |
| sql_warnings                    | OFF                        |
| ssl_ca                          |                            |
| ssl_capath                      |                            |
| ssl_cert                        |                            |
| ssl_cipher                      |                            |
| ssl_key                         |                            |
| storage_engine                  | MyISAM                     |
| sync_binlog                     | 0                          |
| sync_frm                        | ON                         |
| system_time_zone                | PDT                        |
| table_cache                     | 64                         |
| table_lock_wait_timeout         | 50                         |
| table_type                      | MyISAM                     |
| thread_cache_size               | 0                          |
| thread_stack                    | 262144                     |
| time_format                     | %H:%i:%s                   |
| time_zone                       | SYSTEM                     |
| timed_mutexes                   | OFF                        |
| tmp_table_size                  | 33554432                   |
| tmpdir                          | /var/lib/mysql/.tmp/       |
| transaction_alloc_block_size    | 8192                       |
| transaction_prealloc_size       | 4096                       |
| tx_isolation                    | REPEATABLE-READ            |
| updatable_views_with_limit      | YES                        |
| version                         | 5.0.51a                    |
| version_comment                 | SUSE MySQL RPM             |
| version_compile_machine         | x86_64                     |
| version_compile_os              | suse-linux-gnu             |
| wait_timeout                    | 28800                      |
+---------------------------------+----------------------------+
226 rows in set (0.00 sec)

#2 write a SQL command that will set num to 23

set @num=23;
mysql> set @num=23;
Query OK, 0 rows affected (0.00 sec)

mysql> select @num;
+------+
| @num |
+------+
| 23   |
+------+
1 row in set (0.00 sec)

#3 select each number;

select @num;

mysql> select @num;
+------+
| @num |
+------+
| 23   |
+------+
1 row in set (0.00 sec)

#4 select all data from fruit table

select * from fruit;
mysql> select * from fruit;
+---------+--------+-------+
| fruitID | name   | price |
+---------+--------+-------+
|       1 | apple  |  2.50 |
|       2 | pear   |  3.00 |
|       4 | Grpe   |  0.75 |
|       8 | pluot  |  4.50 |
|       6 | plum   |  1.25 |
|       3 | banana |  1.00 |
+---------+--------+-------+
6 rows in set (0.00 sec)

#5 select all data from fruit table where fruit id =1

select * from fruit  where fruitID=1;

mysql> select * from fruit where fruitID=1;
+---------+-------+-------+
| fruitID | name  | price |
+---------+-------+-------+
|       1 | apple |  2.50 |
+---------+-------+-------+
1 row in set (0.00 sec)

#6 show all tables

show tables;
mysql> show tables;
+---------------------+
| Tables_in_DBpnoone0 |
+---------------------+
| fruit               |
| inventory           |
+---------------------+
2 rows in set (0.00 sec)

#7 select all fruit where price is greater than 2

select * from fruit where price > 2.0;

mysql> select * fruit where price > 2.0;
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 'fruit where price > 2.0' at line 1
mysql> select * from fruit where price > 2.0;
+---------+-------+-------+
| fruitID | name  | price |
+---------+-------+-------+
|       1 | apple |  2.50 |
|       2 | pear  |  3.00 |
|       8 | pluot |  4.50 |
+---------+-------+-------+
3 rows in set (0.01 sec)

#8 select average price of fruit

select avg( price ) from fruit;
mysql> select avg (price) from fruit;
+-------------+
| avg (price) |
+-------------+
|    2.166667 |
+-------------+
1 row in set (0.01 sec)

#9 update fruit name with 'grape' where name is 'grpe'

update fruit SET name="Grape" where name = "Grpe";

mysql> update fruit SET name="Grape" where name="Grpe";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#10 insert into fruit table under name, and price - 'peach' and '1.25'

insert into fruit (name, price)values( "peach", 1.25 );

mysql> Insert into fruit (name, price) values ("peach", 1.25);
Query OK, 1 row affected (0.00 sec)

#11 count all fruit

select count( * ) from fruit;
mysql> select count(*) from fruit;
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

#12 select the sum of quantity from inventory table and organize by fruitID

select sum( quantity ) from inventory group by fruitID;
mysql> select sum(quantity) from inventory group by fruitID;
+---------------+
| sum(quantity) |
+---------------+
|           350 |
|           300 |
|           500 |
+---------------+
3 rows in set (0.00 sec)

#13 insert inventory value '275' where fruitID name is 'peach'

insert into inventory values( (select fruitID from fruit where name='peach'), 275 );
mysql> insert into inventory values ((select fruitID from fruit where name='peach'), 275);
Query OK, 1 row affected (0.01 sec)

#14 select the name and price of fruits from fruit table where price is greater than the average price of all fruit

select name,price from fruit where price > (select avg( price ) from fruit );
mysql> select name, price from fruit where price > (select avg(price) from fruit);
+-------+-------+
| name  | price |
+-------+-------+
| apple |  2.50 |
| pear  |  3.00 |
| pluot |  4.50 |
+-------+-------+
3 rows in set (0.00 sec)

#15 select from inventory.fruitID the quantity, name, price where fruitID = 16

select inventory.fruitID, quantity, name, price from inventory, fruit where inventory.fruitID = #16
mysql> select inventory.fruitID, quantity, name, price from inventory, fruit where inventory.fruitID=#16
    -> fruit.fruitID;
+---------+----------+--------+-------+
| fruitID | quantity | name   | price |
+---------+----------+--------+-------+
|       1 |      100 | apple  |  2.50 |
|       3 |      500 | banana |  1.00 |
|       2 |      100 | pear   |  3.00 |
|       1 |      250 | apple  |  2.50 |
|       2 |      200 | pear   |  3.00 |
|       9 |      275 | peach  |  1.25 |
+---------+----------+--------+-------+
6 rows in set (0.00 sec)

#16 name file fruit.fruitID

fruit.fruitID;
-> fruit.fruitID;

#17 select and join $ and 3.0*300

select concat( "$", 3.0*300 );

mysql> select concat("$", 3.0*300);
+----------------------+
| concat("$", 3.0*300) |
+----------------------+
| $900.0               |
+----------------------+
1 row in set (0.00 sec)

#18 select name, and join $ and price, and title 'cost' from fruit table

select name, concat( "$", price) as "cost" from fruit;
mysql> select name, concat("$", price) as cost from fruit;
+--------+-------+
| name   | cost  |
+--------+-------+
| apple  | $2.50 |
| pear   | $3.00 |
| Grape  | $0.75 |
| pluot  | $4.50 |
| plum   | $1.25 |
| banana | $1.00 |
| peach  | $1.25 |
+--------+-------+
7 rows in set (0.01 sec)

#19 select name, and join $ and price and name 'price'.  Join $ and price*sum(quantity) and name 'inventory value' from fruit table and inventory table where fruit.fruit.ID = inventory.fruitID, and organize by inventory.fruitID

select name,concat( "$", price) as "price",concat( "$", price*sum(quantity) ) as "inventory value" from fruit, inventory where fruit.fruitID = inventory.fruitID group by inventory.fruitID;
mysql> select name, concat("$", price) as "price", concat("$", price*sum(quantity)) as "inventory value" from fruit, inventory where fruit.fruitID=inventory.fruitID group by inventory.fruitID;
+--------+-------+-----------------+
| name   | price | inventory value |
+--------+-------+-----------------+
| apple  | $2.50 | $875.00         |
| pear   | $3.00 | $900.00         |
| banana | $1.00 | $500.00         |
| peach  | $1.25 | $343.75         |
+--------+-------+-----------------+
4 rows in set (0.00 sec)

#20 delete fruit and inventory tables
drop table fruit, inventory;
source /home/premiere/fruit.sql;

pnoone0@cim:~>