HW: Using MySQL due Tue 11 Apr 10:30

By completing this assignment you will demonstrate proficiency ...
...g MySQL to
create tables, import data, and perform some queries.


REMINDER: When following the instructions below you need to use names and type exactly.

Perform these actions:

  1. (2 pts) Log in to your CPanel account and create a new database called contributions. Add the MySQL user called ``class'' (created on lab day) to the new database and give that user all privileges. The remaining steps are to be taken in phpMyAdmin in the new database you just created.

  2. (3 pts) Create a new InnoDB table called contributors with the following fields: contributor_id, firstname, lastname, email, city, state. contributor_id should be an INT set to AUTO_INCREMENT (check the A_I checkbox) and should have an primary index set. The rest of the fields should be text fields.

  3. (3 pts) Create a new InnoDB table called contributions with the following fields: contrib_date, contributor_id, and amount. The contributor_id field should be an INT type, contrib_date should be a DATE type, and amount should be DECIMAL type with 10,2 entered in the ``Length/Values'' field.

  4. (2 pts) Using your browser download the file contributions.sql from the course website by right-clicking on it in the file list and saving it to your computer. See: http://josephus.hsutx.edu/classes/dw/source/

  5. (2 pts) Click on the ``Import'' tab (in phpMyAdmin) and browse to the newly downloaded file. Then click the ``Go'' button. If everything works correctly this will take 5 to 10 minutes to complete and will result in a green success message in the browser. If you get a red error message read it and try to figure out what went wrong. (Probably you did not use a proper name or a proper type ... or these instructions are wack).

Now that you have created tables and imported data into them you are ready to write some queries. Once you get a query working the way you want it to you will paste the query you typed into a text document that you will upload into canvas.

Click on the SQL tab and create queries as described below:

  1. (2 pts) Create a query that will list all contributors who are from the city named ``Mart''.

  2. (4 pts) Create a query that will list contribution date, amount, first name, last name, email address, city, and state for all contributions in the database. The query should be ordered by last name then by first name then by contribution date. To accomplish this you'll need to join the two tables on the contributor_id field.

  3. (2 pts) Create a query that is the same as the previous one except that it only shows contributions prior to January 10, 2013.

Turn in your text document by uploading it in Canvas.

Quick Links