Subversion Repositories programming

irasnyd@duallie irasnyd $ mysql -u root -p cs435
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 4.1.8

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from members;
+------------+----------------+---------+
| name       | address        | balance |
+------------+----------------+---------+
| Brooks, B. | 7 Apple Rd.    |   10.50 |
| Field, W.  | 43 Cherry La.  |    0.00 |
| Robin, R.  | 12 Heather St. | -123.45 |
| Hart, W.   | 65 Lark Rd.    |  -43.00 |
+------------+----------------+---------+
4 rows in set (0.02 sec)

mysql> select * from orders;
+----------+------------+------------------+----------+
| order_no | name       | item             | quantity |
+----------+------------+------------------+----------+
|        1 | Brooks, B. | Granola          |        5 |
|        2 | Brooks, B. | Unbleached Flour |       10 |
|        3 | Robin, R.  | Granola          |        3 |
|        4 | Hart, W.   | Whey             |        5 |
|        5 | Robin, R.  | Sunflower Seeds  |        2 |
|        6 | Robin, R.  | Lettuce          |        8 |
+----------+------------+------------------+----------+
6 rows in set (0.00 sec)

mysql> select * from suppliers;
+-------------------+--------------------+------------------+-------+
| sname             | saddress           | item             | price |
+-------------------+--------------------+------------------+-------+
| Sunshine Produce  | 16 River St.       | Granola          |  1.29 |
| Sunshine Produce  | 16 River St.       | Lettuce          |  0.89 |
| Sunshine Produce  | 16 River St.       | Sunflower Seeds  |  1.09 |
| Purity Foodstuffs | 180 Industrial Rd. | Whey             |  0.70 |
| Purity Foodstuffs | 180 Industrial Rd. | Curds            |  0.80 |
| Purity Foodstuffs | 180 Industrial Rd. | Granola          |  1.25 |
| Purity Foodstuffs | 180 Industrial Rd. | Unbleached Flour |  0.65 |
| Tasti Supply Co.  | 17 River St.       | Lettuce          |  0.79 |
| Tasti Supply Co.  | 17 River St.       | Whey             |  0.79 |
| Tasti Supply Co.  | 17 River St.       | Sunflower Seeds  |  1.19 |
+-------------------+--------------------+------------------+-------+
10 rows in set (0.00 sec)

mysql> SELECT name FROM members
    -> WHERE  name NOT IN
    ->     (SELECT name FROM orders
    ->      WHERE  item='Lettuce')
    -> ;
+------------+
| name       |
+------------+
| Brooks, B. |
| Field, W.  |
| Hart, W.   |
+------------+
3 rows in set (0.00 sec)

mysql> SELECT name,address FROM members
    -> WHERE  name IN
    ->     (SELECT name FROM orders
    ->      WHERE  item IN
    ->         (SELECT item FROM suppliers
    ->          WHERE  sname='Sunshine Produce')
    ->     )
    -> ;
+------------+----------------+
| name       | address        |
+------------+----------------+
| Brooks, B. | 7 Apple Rd.    |
| Robin, R.  | 12 Heather St. |
+------------+----------------+
2 rows in set (0.00 sec)

mysql> SELECT sname,item,price FROM suppliers
    -> WHERE  item NOT IN
    ->     (SELECT item FROM orders
    ->      WHERE  name='Brooks, B.')
    -> ;
+-------------------+-----------------+-------+
| sname             | item            | price |
+-------------------+-----------------+-------+
| Sunshine Produce  | Lettuce         |  0.89 |
| Sunshine Produce  | Sunflower Seeds |  1.09 |
| Purity Foodstuffs | Whey            |  0.70 |
| Purity Foodstuffs | Curds           |  0.80 |
| Tasti Supply Co.  | Lettuce         |  0.79 |
| Tasti Supply Co.  | Whey            |  0.79 |
| Tasti Supply Co.  | Sunflower Seeds |  1.19 |
+-------------------+-----------------+-------+
7 rows in set (0.00 sec)

mysql> SELECT sname FROM suppliers
    -> WHERE  item IN
    ->     (SELECT item FROM orders
    ->      WHERE  name='Brooks, B.')
    -> GROUP BY sname
    -> HAVING COUNT(sname) =
    ->     (SELECT COUNT(DISTINCT item) FROM orders
    ->      WHERE  name='Brooks, B.')
    -> ;
+-------------------+
| sname             |
+-------------------+
| Purity Foodstuffs |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT s.sname FROM suppliers s, orders o
    -> WHERE  o.item=s.item 
    -> AND o.name='Brooks, B.' 
    -> GROUP BY s.sname
    -> HAVING COUNT(*)=(SELECT COUNT(*) FROM orders 
    ->                  WHERE name='Brooks, B.');
+-------------------+
| sname             |
+-------------------+
| Purity Foodstuffs |
+-------------------+
1 row in set (0.03 sec)

mysql> SELECT DISTINCT sname FROM suppliers
    -> WHERE item IN
    ->     (SELECT item FROM orders
    ->      WHERE name='Brooks, B.')
    -> ;
+-------------------+
| sname             |
+-------------------+
| Sunshine Produce  |
| Purity Foodstuffs |
+-------------------+
2 rows in set (0.00 sec)

mysql> SELECT item,AVG(price) FROM suppliers
    -> WHERE  item IN
    ->     (SELECT DISTINCT item FROM suppliers)
    -> GROUP BY item;
+------------------+------------+
| item             | AVG(price) |
+------------------+------------+
| Curds            |   0.800000 |
| Granola          |   1.270000 |
| Lettuce          |   0.840000 |
| Sunflower Seeds  |   1.140000 |
| Unbleached Flour |   0.650000 |
| Whey             |   0.745000 |
+------------------+------------+
6 rows in set (0.00 sec)

mysql> SELECT sname,item FROM suppliers;
+-------------------+------------------+
| sname             | item             |
+-------------------+------------------+
| Sunshine Produce  | Granola          |
| Sunshine Produce  | Lettuce          |
| Sunshine Produce  | Sunflower Seeds  |
| Purity Foodstuffs | Whey             |
| Purity Foodstuffs | Curds            |
| Purity Foodstuffs | Granola          |
| Purity Foodstuffs | Unbleached Flour |
| Tasti Supply Co.  | Lettuce          |
| Tasti Supply Co.  | Whey             |
| Tasti Supply Co.  | Sunflower Seeds  |
+-------------------+------------------+
10 rows in set (0.00 sec)

mysql> SELECT name FROM members
    -> WHERE name IN
    ->     (SELECT name FROM
    ->         (SELECT name,COUNT(name) FROM orders
    ->          GROUP BY name HAVING COUNT(name)>=2)
    ->      AS temp_tbl1)
    -> ;
+------------+
| name       |
+------------+
| Brooks, B. |
| Robin, R.  |
+------------+
2 rows in set (0.00 sec)

mysql> SELECT sname,COUNT(sname) FROM suppliers
    -> GROUP BY sname;
+-------------------+--------------+
| sname             | COUNT(sname) |
+-------------------+--------------+
| Purity Foodstuffs |            4 |
| Sunshine Produce  |            3 |
| Tasti Supply Co.  |            3 |
+-------------------+--------------+
3 rows in set (0.00 sec)

mysql> SELECT sname FROM suppliers
    -> WHERE item IN
    ->     (SELECT item FROM
    ->         (SELECT item,COUNT(item) FROM orders
    ->          GROUP BY item
    ->          HAVING COUNT(item)>1)
    ->      AS temp_tbl1)
    -> ;
+-------------------+
| sname             |
+-------------------+
| Sunshine Produce  |
| Purity Foodstuffs |
+-------------------+
2 rows in set (0.00 sec)

mysql> SELECT DISTINCT sname FROM suppliers
    -> WHERE  sname IN
    ->     (SELECT sname FROM suppliers
    ->      WHERE  item='Lettuce')
    -> AND sname IN
    ->     (SELECT sname FROM suppliers
    ->      WHERE  item='Whey')
    -> ;
+------------------+
| sname            |
+------------------+
| Tasti Supply Co. |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT DISTINCT name FROM members
    -> WHERE  name IN
    ->     (SELECT name FROM orders
    ->      WHERE  item='Lettuce')
    -> OR name IN
    ->     (SELECT name FROM orders
    ->      WHERE  item='Whey')
    -> ;
+-----------+
| name      |
+-----------+
| Hart, W.  |
| Robin, R. |
+-----------+
2 rows in set (0.00 sec)

mysql> SELECT name FROM members
    -> WHERE  name NOT IN
    ->     (SELECT name FROM orders
    ->      WHERE  item IN
    ->         (SELECT DISTINCT item FROM orders
    ->          WHERE  item<>'Whey')
    ->     )
    -> AND name IN
    ->     (SELECT DISTINCT name FROM orders)
    -> ;
+----------+
| name     |
+----------+
| Hart, W. |
+----------+
1 row in set (0.00 sec)

mysql> quit
Bye