silo.cs.indiana.edu%./mysql_client
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27 to server version: 5.0.22-log

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

mysql> use demoOne
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> show tables like '%project%';
+-------------------------------+
| Tables_in_demoOne (%project%) |
+-------------------------------+
| projectMovies                 | 
| projectRatings                | 
| projectUsers                  | 
+-------------------------------+
3 rows in set (0.00 sec)

mysql> describe projectUsers;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| username  | varchar(8)  | NO   | PRI | NULL    |       | 
| lastName  | varchar(20) | YES  |     | NULL    |       | 
| firstName | varchar(20) | YES  |     | NULL    |       | 
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from projectUsers;
+----------+----------+-----------+
| username | lastName | firstName |
+----------+----------+-----------+
| lbird    | Bird     | Larry     | 
| mjordan  | Jordan   | Michael   | 
| mjackson | Jackson  | Mark      | 
| cmullen  | Mullen   | Chris     | 
| tkukoc   | Kukoc    | Toni      | 
+----------+----------+-----------+
5 rows in set (0.00 sec)

mysql> describe projectMovies;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| movieNum | varchar(8)  | NO   | PRI | NULL    |       | 
| title    | varchar(80) | YES  |     | NULL    |       | 
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from projectMovies;
+----------+--------------------+
| movieNum | title              |
+----------+--------------------+
| ryan1998 | SavingPrivateRyan  | 
| spongebo | Spongebob:TheMovie | 
| johnnyEn | JohnnyEnglish      | 
| incredib | TheIncredibles     | 
| princess | ThePrincess'Bride  | 
| nglishpt | TheEnglishPatient  | 
+----------+--------------------+
6 rows in set (0.00 sec)

mysql> describe projectRatings;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| movieNum | varchar(8)   | NO   | PRI | NULL    |       | 
| username | varchar(8)   | NO   | PRI | NULL    |       | 
| comment  | varchar(290) | YES  |     | NULL    |       | 
| rating   | int(11)      | YES  |     | NULL    |       | 
+----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> select * from projectRatings;
+----------+----------+---------+--------+
| movieNum | username | comment | rating |
+----------+----------+---------+--------+
| ryan1998 | lbird    | NULL    |    100 | 
| ryan1998 | mjordan  | NULL    |     92 | 
| spongebo | lbird    | NULL    |     92 | 
| spongebo | tkukoc   | NULL    |     10 | 
| spongebo | mjordan  | NULL    |     80 | 
| johnnyEn | lbird    | NULL    |     20 | 
| johnnyEn | tukoc    | NULL    |     95 | 
| johnnyEn | mjordan  | NULL    |     68 | 
| johnnyEn | cmullen  | NULL    |     92 | 
| incredib | mjordan  | NULL    |     92 | 
| princess | tkukoc   | NULL    |     82 | 
| princess | cmullen  | NULL    |     68 | 
| nglishpt | lbird    | NULL    |     80 | 
| nglishpt | mjordan  | NULL    |     60 | 
+----------+----------+---------+--------+
14 rows in set (0.00 sec)

mysql> select movieNum, avg(rating) from projectRatings group by movieNum order by avg(rating) desc;
+----------+-------------+
| movieNum | avg(rating) |
+----------+-------------+
| ryan1998 |     96.0000 | 
| incredib |     92.0000 | 
| princess |     75.0000 | 
| nglishpt |     70.0000 | 
| johnnyEn |     68.7500 | 
| spongebo |     60.6667 | 
+----------+-------------+
6 rows in set (0.00 sec)

mysql> exit
Bye
silo.cs.indiana.edu%