a. Log in to your MathLAN workstation. (Of course, you've probably already done that if you're reading this laboratory.)
b. Open a terminal window into which you can type commands.
c. Open a secure shell (ssh) connection to
the machine that hosts our Web browser.
d. Connect to our SQL server with
e. Arrange to use the database area for this class with
When you start using a database, it can be valuable to determine which
tables are available to you. Using the
show tables and
describe table commands, identify the tables
in this database.
Recall that the form of a simple query is
SELECT fields FROM table;
Recall that the form of a restricted query is
SELECT fields FROM table WHERE conditions;
a. Obtain a list of all the publishers and their ids.
b. Identify the titles of all the books published by Grinnell Press.
c. Identify the ISBN numbers of all the books with a price of at least $16.00.
Determine experimentally ...
a. Whether the keyword
SELECT needs to be capitalized
b. Whether the case used in naming a table matters
c. Whether the case used in naming a field matters
a. Write a query to print a table of author/title for all the books in the database. (You should only associate an author with his/her book.)
b. Write a query that orders that table by author last name.
c. Write a query that orders that table by book title.
d. Write a query to print all the information for each book, so that we need not look in separate tables for author and publisher.
In case you've forgotten, the traditionally command to insert into a table is
INSERT INTO table (fields) VALUES (values);
a. Add a (fictitious) book by you to the database.
b. Save the commands you used to add the book, since you may need them later.
Suppose you did not know the authorID for an author, but you knew her name. Write a query you could use to add a book by that author to the database.
In case you've forgotten, the command for deleting values is
DELETE FROM table WHERE condition;
a. Delete your book from the books table.
b. Verify that you succeeded.
c. Reinsert your book into the books table.
d. Delete yourself from the authors table.
e. Do you expect your book to be listed in the following query?
select books.title from books;
f. Check your answer experimentally.
g. Do you expect your book to be listed in the following query?
select authors.last,books.title from authors,books where authors.authorID=books.bookID;
h. Check you answer experimentally.
i. What issues does this last example raise? How would you address those issues?
a. Change the title of your book.
b. Set the price of all books with NULL prices to $0.00.
A library might have multiple copies of each book, and have notes about each copy.
a. Create a table that we might use to store a library's inventory.
username_library for the name of the table so that we
do not have conflicts.)
b. Give your classmates access to that table with
grant all on CSC325.table to userid;
Monday, 4 October 2010 [Samuel A. Rebelsky]
I usually create these pages
on the fly, which means that I rarely
proofread them and they may contain bad grammar and incorrect details.
It also means that I tend to update them regularly (see the history for
more details). Feel free to contact me with any suggestions for changes.
This document was generated by
Siteweaver on Mon Nov 29 09:06:20 2010.
The source to the document was last modified on Mon Oct 4 10:00:51 2010.
This document may be found at
A PDF version of this document may be found at
You may wish to validate this document's HTML ; ;Samuel A. Rebelsky, email@example.com