Exercises

For questions 1 to 18 use the PUBS sample database created by Microsoft. To download the script to generate this database please go to the following site: http://www.microsoft.com/en-ca/download/details.aspx?id=23654.

  1. Display a list of publication dates and titles (books) that were published in 2011.
  2. Display a list of titles that have been categorized as either traditional or modern cooking. Use the Books table.
  3. Display all authors whose first names are five letters long.
  4. Display from the Books table: type, price, pub_id, title about the books put out by each publisher. Rename the column type with "Book Category". Sort by type (descending) and then price (ascending).
  5. Display title_id, pubdate and pubdate plus three days, using the Books table.
  6. Using the datediff and getdate function determine how much time has elapsed in months since the books in the Books table were published.
  7. List the title IDs and quantity of all books that sold more than 30 copies.
  8. Display a list of all last names of the authors who live in Ontario (ON) and the cities where they live.
  9. Display all rows that contain a 60 in the payterms field. Use the Sales table.
  10. Display all authors whose first names are five letters long , end in O or A, and start with M or P.
  11. Display all titles that cost more than $30 and either begin with T or have a publisher ID of  0877.
  12. Display from the Employees table the first name (fname), last name (lname), employe ID(emp_id), and job level (job_lvl) columns for those employees with a job level greater than 200; and rename the column headings to:  "First Name," "Last Name," "IDENTIFICATION#" and "Job Level".
  13. Display the royalty, royalty plus 50% as "royalty plus 50" and title_id. Use the Roysched table.
  14. Using the STUFF function create a string "12xxxx567" from the string "1234567".
  15. Display the first 40 characters of each title, along with the average monthly sales for that title to date (ytd_sales/12). Use the Title table.
  16. Show how many books have assigned prices.
  17. Display a list of cookbooks with the average cost for all of the books of each type. Use the GROUP BY.