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:
- 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.
- 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!
- Add some data.
Write some INSERT statements to do the following:
Keep track of the statements you write!
- Add two users, one with username "derp" and one with username
- 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.
- Try viewing your data.
Execute the following statements:
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.
- SELECT * FROM `users`;
- SELECT * FROM `messages` ORDER BY `time` DESC;
- DELETE FROM `messages` WHERE `username` = "derp";
- SELECT * FROM `messages`;
- Save your statements to turn in.
Put the CREATE TABLE and INSERT statements into a
file called chat.sql, one after the other.
- 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.