SQL Programming Lab 3

September 28, 1999

In this lab you will be answering some questions by performing queries against the pubs2 database I introduced you to last week.

  1. do a select on the books table

    2. SELECT * FROM titles

  2. show all title ID’s

    3. SELECT title_id FROM titles

  3. show title ID’s authored by authors with the last name of ‘Green’

SELECT t.title FROM titles t, titleauthor ta, authors a

WHERE a.au_lname = ‘Green’ and

t.title_id = ta.title_id and

a.au_id = ta.au_id

 

Now answer the following questions by creating and executing SQL statements against the pubs2 database. Use the table handouts to help figure out which tables contain the data you want to access.
  1. What are the first and last names of the authors that live in California?

    2. SELECT au_fname, au_lname FROM authors WHERE state = 'CA'

  2. What are the names of the stores that are located in California?

    3. SELECT stor_name FROM stores WHERE state = 'CA'

  3. What are the titles of the books written by Marjorie Green?

    4. SELECT t.title

    FROM titles t, titleauthor ta, authors a

    WHERE t.title_id = ta.title_id and

    ta.au_id = a.au_id and

    au_fname = 'Marjorie' and

    au_lname = 'Green'

     

  4. What are the titles of the books published by Algodata Infosystems?

SELECT t.title

FROM titles t, publishers p

WHERE t.pub_id = p.pub_id and

p.pub_name = 'Algodata Infosystems'