SQL Programming Lab 4

October 5, 1999

 

Start a scripting session and answer the following questions. Then mail your script to jlaker@udel.edu with "ISQL Lab 4" as the subject.

Use the pubs2 database.

  1. The company wants you to create a query that can be used in their customer search application. They want it to search for any titles that contain the word "Computer" and return the title of the book and the first and last name of the author.

    2. Solution:

    SELECT t.title, a.au_fname, a.au_lname

    FROM titles t, titleauthor ta, authors a

    WHERE a.au_id = ta.au_id and

    ta.title_id = t.title_id and

    title like '%Computer%'

  2. List all the book titles in which the price is greater than $15.

    3. Solution:

    SELECT title

    FROM titles

    WHERE price > $15.00

  3. List the book titles and their publishers whose total sales were greater than the title "Sushi Anyone?".

    4. Solution:

    SELECT total_sales

    FROM titles

    WHERE title = 'Sushi, Anyone?'

    SELECT title, pub_name

    FROM titles t, publishers p

    WHERE t.pub_id = p.pub_id and

    t.total_sales > (value from previous query)

  4. What are the title and authors’ first and last name of the business type books?

    5. Solution:

    SELECT t.title, a.au_fname, a.au_lname

    FROM titles t, titleauthor ta, authors a

    WHERE a.au_id = ta.au_id and

    ta.title_id = t.title_id and

    t.type = 'business'

     

     

  5. What are the title and authors’ first and last name AND publisher names of the psychology books?

Solution:

SELECT t.title, a.au_fname, a.au_lname, p.pub_name

FROM titles t, authors a, titleauthor ta, publishers p

WHERE a.au_id = ta.au_id and

ta.title_id = t.title_id and

t.pub_id = p.pub_id and

t.type = 'psychology'