CSc 337 - Web Programming > Assignments > Assignment 9

General Information

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 -P 33706 -u your_username your_databasename
    If you do not have MySQL installed on your machine, you can run this command from 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!
    username VARCHAR(20)
    password VARCHAR(32)
    messageid INT(10) AUTO_INCREMENT
    username VARCHAR(20)
    time TIMESTAMP
    message TEXT
  3. Add some data. Write some INSERT statements to do the following: Keep track of the statements you write!
  4. 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.
  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 -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.

Homework Submission

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.