Laboratory: SQL Basics

Summary:

Contents:

• Preparation
• Exercises
  • Exercise 1: Finding Tables
  • Exercise 2: Simple Queries
  • Exercise 3: Query Syntax
  • Exercise 4: Hybrid Queries
  • Exercise 5: Adding Your Own Data
  • Exercise 6: Adding Books, Revisited
  • Exercise 7: Removing Data
  • Exercise 8: Updating
  • Exercise 9: Libraries

Preparation

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 www.cs.grinnell.edu, the machine that hosts our Web browser.

d. Connect to our SQL server with mysql -p.

e. Arrange to use the database area for this class with use CSC325;.

Exercises

Exercise 1: Finding Tables

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.

Exercise 2: Simple Queries

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.

Exercise 3: Query Syntax

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

Exercise 4: Hybrid Queries

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.

Exercise 5: Adding Your Own Data

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.

Exercise 6: Adding Books, Revisited

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.

Exercise 7: Removing Data

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?

Exercise 8: Updating

a. Change the title of your book.

b. Set the price of all books with NULL prices to $0.00.

Exercise 9: Libraries

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. (Use 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;