SQL Assignment 1

SQL Assignment 1 (10%)
Questions:
1. List the “LastName”, “FirstName” and “MemberSince” of the customers who
became members between 2010 and 2015. For this question, do not use the “IN”
keyword. Sort the “MemberSince” in descending order then the “LastName” in
ascending order.
2. List the “Title” and “MediaID” of the books that contain the words “.NET” or
“iOS” in the title and with MediaID=3 or 4. Sort the “Title” in ascending order
(Note: “iOS” refers to operating system and .NET contains a “.”)
3. List the distinct “Author” whose books do not contain the word “.NET” in the
title and exclude items with MediaID equal to “1”,”2” or “6”. Sort the “Author”
in ascending order.
4. The library manager intends to increase the ReplacementCost for items
published in 2013, 2014 or 2015. The new ReplacementCost is the old
ReplacementCost plus additional 10%. Produce a table showing the original
ReplacementCost and the new ReplacementCost. Round all numbers to 1
decimal. The output format should be the same as the format given below. Use
the ‘IN’ keyword in the SQL and sort the Year in descending order.
Title Year
Published
ReplacementCost(OLD) ReplacementCost(NEW)
5. Find all the books with a single word as the title and exclude those titles that
contain a hyphen (‘-‘). Format the output to be the same as the table below. Sort
the “Book Title” in descending order.
Book Author Book Title
*Important*
(1) Use the SQL syntaxes that are taught in INFO151. Other syntax will not be accepted.
(2) For numeric attributes use the “=” sign, for example “percent_time =100” not
“percent_time IS 100”.
(3) No need to use JOIN in this assignment.
6. Display a list of customers whose last name does not contain the letter “a” or
the first name does not contain the letter “e”. Sort the last name in ascending
order.
7. List all the items that are published in an odd year but exclude the year 2013.
Display the ItemID, Title and PubYear. Sort the PubYear in ascending order.
8. Query the loan table to show all non-repeated rows of CustomerID and
InventoryitemID. Show only the rows with no overdue fee. Sort the CustomerID
in ascending order. Because a customer can borrow the same item more than
once, your query must ensure non-repeated rows are shown.
CustomerID InventoryitemID
END