SQL Programming Lab 6

October 19, 1999

 

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

Use the pubs2 database.

  1. Generate a list of the authors’ first and last name that live in TN, MN, and IN.

    2. 1> SELECT au_fname First, au_lname Last

    2> FROM authors

    3> WHERE state IN ('TN', 'MN', 'IN')

    4> go

    First Last

    -------------------- ----------------------------------------

    Morningstar Greene

    Michel DeFrance

  2. Show a price comparison of the titles after a $2 increase for the business and psychology books. List should have the title, type, present price and price after increase.

    3. 1> SELECT title Title, type Type, price PresentPrice, (price+2) AdjPrice

    2> FROM titles

    3> WHERE type IN ('business', 'psychology')

     

     

  3. The company would like to do a geographic analysis of its authors and the states in which they reside. They want a report that presents a list of the states and the total number of authors within each state. Assign appropriate headers to report.

    4. 1> SELECT state, count(state) FROM authors

    2> GROUP BY state

    3> go

    state

    ----- -----------

    CA 15

    IN 1

    KS 1

    MD 1

    MI 1

    OR 1

    TN 1

    UT 2

  4. Create a list of titles, title ids, and their total sales where the total sales was between 4095 and 12000.

    5. 1> SELECT title, title_id, total_sales

    2> FROM titles

    3> where totlal_sales between 4095 and 12000

     

     

  5. The company is trying to update its total sales record for it titles. Generate a list of the titles and title ids that are missing total sales values.

    6. 1> SELECT title , title_id, FROM titles

    2> WHERE total_sales IS NULL

  6. For your project you will need to add the users in your group to the account of whoever will be keeping the tables in their database. If you are still in pubs2 go to your database by typing "use username" where username is your login. Then type "go". To add users to your database type the following while in ISQL:

sp_adduser username

go

Again, the username here is the username is the login of the user you are adding. Repeat this for each user in your group. They should now be able to access your database with the "use" command. Try it out.