CSc 337 - Web Programming > Assignments > Assignment 9

Due: 20 Feb 2012, 11:59pm

This assignment will have you connect to the MySQL server and write some statements to get a little practice working with SQL.

Before you begin, make sure you have request a MySQL account. If your login information does not work, request an account, wait 24 hours, and try again.

Follow the directions below in order to complete this assignment:

1. Connect to the class MySQL server. The following command from a Linux machine with MySQL installed should get you in:

   mysql -p -h navytank.no-ip.org -P 33706 -u your_username your_databasename

   If you do not have MySQL installed on your machine, you can run this command from lectura.cs.arizona.edu. Your database name should be db_yourusername. For example, if your username is gunsch, your database name would be db_gunsch.
2. Create tables for your chat application. You should write CREATE TABLE statements to create the following tables. Take care to name the tables and columns as specified. Primary keys are underlined and types are specified. Keep track of the statements you write!

   users
   username	VARCHAR(20)
   password	VARCHAR(32)

   messages
   messageid	INT(10) AUTO_INCREMENT
   username	VARCHAR(20)
   time	TIMESTAMP
   message	TEXT

3. Add some data. Write some INSERT statements to do the following:
   • Add two users, one with username "derp" and one with username "derpette".
   • Add messages for a short conversation between the two users (at least two messages each!). For the time column, you can use the value CURRENT_TIMESTAMP to insert an entry with a timestamp of right now.
   Keep track of the statements you write!
4. Try viewing your data. Execute the following statements:
   • SELECT * FROM `users`;
   • SELECT * FROM `messages` ORDER BY `time` DESC;
   • DELETE FROM `messages` WHERE `username` = "derp";
   • SELECT * FROM `messages`;
   This is not part of what you should turn in but is just to get a feel for executing SQL queries and seeing the results.
5. Save your statements to turn in. Put the CREATE TABLE and INSERT statements into a file called chat.sql, one after the other.
6. Test your .sql file. If you want to verify that your homework works, you can run the SQL statements yourself! If your terminal's shell is in the same directory as your new chat.sql file, you can run the following command to execute all of that SQL at once:

   mysql -p -h navytank.no-ip.org -P 33706 -u your_username your_databasename < chat.sql

   You may need to delete your tables before creating them again. You can accomplish this with the DROP TABLES command inside SQL.

See the MySQL Reference Manual for detailed information on SQL syntax.

As detailed on the homework submission page, this homework must be on the class web server in a folder titled homework9 for it to be graded. The only required file to be in that directory is chat.sql.