MySQL Introduction: authors and books

Assigned 11/1.
Due electronically by 9 PM on 11/4.
Since we are starting this in class in teams, you may turn in this lab in pairs if you wish.

This lab exercise will take you through the process of creating tables in your MySQL database, adding data to those tables manually, and doing some simple queries. In the process, you will use the CREATE, SHOW, INSERT, and SELECT statements from SQL (the Structured Query Language).

Work through the following steps.

1. Create a Notepad document on your K drive called "mysqllab.txt", and answer in it any questions you see here marked with bullets.

2. Open up a command prompt for MySQL. To do this, double click on "My Computer", followed by the "T" drive. In this folder there are two icons of interest: one labeled "Win98 mysql prompt" and one labeled "WinXP mysql prompt". Double click on the appropriate one for the operating system that your computer is running.

3. An MS-DOS window should now be open with a prompt for you to start typing. To start MySQL, type (but use your username instead of mine):

   mysql -u dmusican -h prism -p

   Enter in the password that you were given earlier this term when you signed the department account agreement.
   If MySQL started correctly, you should see something like:

   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 79 to server version: 3.23.39-log
   
   Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
   mysql>

   If you do not get to this point, ask for help before going forward.

4. Connect to your personal database within MySQL by typing

   use dmusican

   where you use your username instead of mine.

5. At the MySQL prompt, enter the following MySQL commands:

   mysql> CREATE TABLE authors
   	-> (auth_id INT,
   	-> first_name TEXT,
   	-> last_name TEXT,
   	-> death_year INT,
   	-> country TEXT);
   
   mysql> CREATE TABLE books
   	-> (book_id INT,
   	-> author_id INT,
   	-> title TEXT);
   
   mysql> SHOW TABLES;
   
   mysql> SHOW COLUMNS FROM authors;
   
   mysql> SHOW COLUMNS FROM books;

6. Answer the following questions:

   • What does the CREATE TABLE command do?

   • What does the SHOW TABLES command do?

   • What does the SHOW COLUMNS FROM command do?

   • In the above CREATE TABLE commands, what do INT and TEXT mean?

7. At the MySQL prompt, enter the following commands:

   mysql> INSERT INTO authors
   	-> VALUES (1, "Jane", "Austen", 1817, "England");
   
   mysql> INSERT INTO authors
   	-> VALUES (2, "Mark", "Twain", 1910, "US");
   
   mysql> INSERT INTO books
   	-> VALUES (1, 2, "Tom Sawyer");
   
   mysql> INSERT INTO books
   	-> VALUES (2, 1, "Sense and Sensibility");
   
   mysql> INSERT INTO books 
   	-> VALUES (3, 1, "Pride and Prejudice");
   
   mysql> INSERT INTO books
   	-> VALUES (4, 2, "Life on the Mississippi");
   
   mysql> SELECT * FROM books;

   • What does the command INSERT INTO do?

   • What does the query SELECT * FROM ---- do?

8. At the MySQL prompt, enter the following query:

   mysql> SELECT author_id,title FROM books;

   • What do you see?

   • In general, what follows the word SELECT? (columns or tables?)

   • In general, what follows the word FROM? (columns or tables?)

   • What, then, does * after SELECT mean?

9. At the MySQL prompt, enter the following command:

   mysql> SELECT * FROM books WHERE author_id = 2;

   • What do you see?

   • What does the WHERE addition to SELECT * FROM do?

10. At the MySQL prompt, enter the following SQL query:

    mysql> SELECT authors.last_name, books.title                    
    	-> FROM authors, books
    	-> WHERE authors.auth_id = books.author_id;

    • What do you see?

    • When you see something written like A.B, does A represent a table or a column? What about B?

    • In your own words, what does the above query do?

11. Use a SELECT statement modeled after one of the ones in the transcript to show the titles and author last names of all the books written by US authors.

    • Copy your SELECT statement into the Notepad document that you will submit.

12. In order to store this information, we did so in two separate tables: authors and books.

    • Alternatively, how could the same information be stored in just one table?

    • Why might I have chosen to store it in two tables instead on one? Explain with an example what might go wrong. Hint: if I store it in one table, am I more likely to end up with inconsistencies? If you don't see the answer, actually create one table with all the data and think about where inconsistencies might pop up.

    • Type quit in response to the mysql> prompt.

    • Copy and paste your mysqllab.txt file into your homework folder.