Compose and execute SQL queries that retrieve the following information. For the later queries, you will have to do some research at w3schools in order to learn the proper SQL commands to complete the query. This is intended as an exercise to familiarize you with the w3schools website, as well as reinforce researching for new information on your own.  Each query should be terminated with a semicolon (;).  Make sure to verify that the results of each query are correct by comparing against the database tables themselves.

For each query listed below, copy and paste the following information into your text file:

* The exact query you came up with (including the "SQL>" prompt
* The exact result of the query (including the last line that starts with "OK").

Copy and paste the final working query and the results of the query from the Eclipse console underneath its respective question below.  I recommend that you develop each query in this document, and then copy and paste it to the "SQL>" prompt in the Eclipse console, and then execute it.  That way, you can easily iterate editing and executing the SQL queries.

NOTE: You may NOT use specific author_id's or book_id's in your queries (except for problems 7 and 9, which specifically state to enter an author_id).  You must join the tables using the method given in the lecture and lab, and demonstrated in class.

NOTE: Look out for the "traps" of not specifying a semi-colon at the end of your SQL statement in SQLDemo, and for having an odd number of single quotes in a query.  Also, you will have to figure out how to specify a single quote as part of a text string, and you will also have to figure out how to insert a new row into a table (hint: w3schools).

NOTE: Rename your submission to lab04-username.txt, replacing "username" with your YCP username, before submitting your lab to Marmoset.


1) The title for each book written by Stephen Hawking (I'll give you this one - as an example for your entries).

SQL> select books.title
    from books, authors
    where books.author_id = authors.author_id and
          authors.lastname = 'Hawking' and authors.firstname = 'Stephen';
                     TITLE
--------------------------
   A Brief History of Time
The Universe in a Nutshell
 A Briefer History of Time
          The Grand Design
OK (4 rows(s))


2) The title and year of publishing for each book written by Douglas Adams



3) The author's name (first and last), the ISBN, and the year published for the book with the title "Something Under the Bed is Drooling"



4) All of the authors for "The Complete Monty Python's Flying Circus; All the Words, Volume 1"



5) The title and author for each of Berkeley Breathed's and Bill Watterson's books, sorted in ascending order by title



6) The author(s) and title for each book with the word "Time" or "Universe" in the title, sorted in ascending order by lastname, and then ascending by title.



7) Attempt to insert a new book into the books table, with an author_id that does not appear in the authors table (this attempt should fail, due to specifying an invalid foreign key for author_id)



8) Insert yourself as a new author in the authors table (do not specify an author_id, Derby will do that for you, since author_id is the auto-generated primary key for the authors table)



9) Retrieve the author_id from the authors table for your entry and insert a new book into the books table, using your author_id



10) Now retrieve all of the information for your book (title, ISBN, published, lastname, firstname)