REMINDER: When following the instructions below you need to use names and type exactly.
Perform these actions:
- (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.
- (3 pts) Create a new InnoDB table called contributors with the
contributor_id, firstname, lastname, email,
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 pts) Create a new InnoDB table called contributions with the
contributor_id field should be an INT
contrib_date should be a DATE type, and
amount should be DECIMAL type with 10,2 entered in the
- (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:
- (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:
- (2 pts) Create a query that will list all contributors who are from the
city named ``Mart''.
- (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
- (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.