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 searches. In the process, you will use the CREATE, SHOW, INSERT, and SELECT statements from SQL (the Structured Query Language).
Do the following:
Log in to your Math/CS account, and then log in to prism via SSH:
ssh yourusername@prism.mathcs.carleton.edu
You should now see the prism> prompt. Once you do, follow the steps shown below in the Transcript of Jeff's MySQL Session section. Wherever the transcript uses "jondich", you should use your own user name. Your MySQL password is the same as your original Unix password, which you used earlier in the term.
Once you're done with the previous step, you should have two small tables named authors and books. Use the INSERT statement to add a new US author and a few of his or her books to the corresponding tables. Use "SELECT * FROM authors;" and "SELECT * FROM books;" to see that your new data got entered properly.
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.
Type quit in response to the mysql> prompt.
The following is a transcript of a MySQL session. This transcript includes everything I saw on screen--both the things I typed myself, and the prompts and responses the computer gave me. Try to follow the steps here exactly, while at the same time making sure that you and your lab partner understand the nature of each step.
If you have questions, ask.
prism> mysql -u jondich -p jondich
Enter password:
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> CREATE TABLE authors
-> (id INT,
-> first_name TEXT,
-> last_name TEXT,
-> death_year INT,
-> country TEXT);
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE books
-> (id INT,
-> author_id INT,
-> title TEXT);
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW TABLES;
+-------------------+
| Tables_in_jondich |
+-------------------+
| authors |
| books |
+-------------------+
2 rows in set (0.00 sec)
mysql> SHOW COLUMNS FROM authors;
+------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| first_name | text | YES | | NULL | |
| last_name | text | YES | | NULL | |
| death_year | int(11) | YES | | NULL | |
| country | text | YES | | NULL | |
+------------+---------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> SHOW COLUMNS FROM books;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| author_id | int(11) | YES | | NULL | |
| title | text | YES | | NULL | |
+-----------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> INSERT INTO authors
-> VALUES (1, "Jane", "Austen", 1817, "England");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO authors
-> VALUES (2, "Mark", "Twain", 1910, "US");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO books
-> VALUES (1, 2, "Tom Sawyer");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO books
-> VALUES (2, 1, "Sense and Sensibility");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO books
-> VALUES (3, 1, "Pride and Prejudice");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO books
-> VALUES (4, 2, "Life on the Mississippi");
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM books;
+------+-----------+-------------------------+
| id | author_id | title |
+------+-----------+-------------------------+
| 1 | 2 | Tom Sawyer |
| 2 | 1 | Sense and Sensibility |
| 3 | 1 | Pride and Prejudice |
| 4 | 2 | Life on the Mississippi |
+------+-----------+-------------------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM books WHERE author_id = 2;
+------+-----------+-------------------------+
| id | author_id | title |
+------+-----------+-------------------------+
| 1 | 2 | Tom Sawyer |
| 4 | 2 | Life on the Mississippi |
+------+-----------+-------------------------+
2 rows in set (0.00 sec)
mysql> SELECT authors.last_name, books.title
-> FROM authors, books
-> WHERE authors.id = books.author_id;
+-----------+-------------------------+
| last_name | title |
+-----------+-------------------------+
| Twain | Tom Sawyer |
| Austen | Sense and Sensibility |
| Austen | Pride and Prejudice |
| Twain | Life on the Mississippi |
+-----------+-------------------------+
4 rows in set (0.00 sec)
mysql>