Obtain the first and last names of all authors of books ordered by the customer named "Clark Kent". Avoid repetitions using DISTINCT.

 

Q02. Obtain the average, minimum, and maximum ordered quantities of all books that were ordered and with their category as "Databases".

 

Q03. Obtain the Last and First Names of all third authors whose books were ordered. Print these names next to their book titles. Show also the total value of all these books in stock. The total value is the number of books in stock multiplied by their price. Order the results in descending order of total value. Avoid repetitions using DISTINCT.

 

Q04. Obtain the AuthorIDs and the last names of the authors that have books that were ordered more than three times (meaning that there are more than 3 orderline records for that book). List next to the last name also the title of a book and the number of all orderline records made for that book. Order the results in descending order of the number of orderline records.

 

Q05. Obtain the average price before discount of all titles that were delivered between May 1, 2016 and May 5, 2016, including those days.

 

Q06. Obtain the minimum, maximum, and average ordered quantities of all ordered books per category. Sort the list in descending order of the average ordered quantities. Do not include results with average order quantities smaller than 1.

 

Q07. Obtain the first and last names of all authors that have the most expensive book(s) in the database. Sort the last names alphabetically. Hint: Use a subquery to find the ISBN(s) of the most expensive book(s) and use it in the rest of the query.

 

Q08. Obtain the addition of all discounted prices of the books ordered by all customers from the State of "Texas". The discounted price is calculated by multiplying the Quantity sold of a title by its Price and by (1-PercentageDiscount/100).

 

Q09. Obtain the book titles together with the sum of all Quantities of that title ordered per State. Include only the titles with more than 15 copies ordered by customers in total. Sort the list in descending sum of the quantity of titles sold.

 

Q10. Obtain the Title of the only book that was never discounted in any order. Hint: Use a subquery to find a list with the ISBNs of all ordered books that were given a PercentageDiscount that is different than zero. The title that is being requested will not be on that list.

 

Transcribed Image Text:

Book ISBN Title Category Stock Price Used OrderLine OrderNo OrderRank ISBN Quantity PercentageDiscount Wrote 00 Authorld ISBN AuthorRank. 00 Order Order No OrderDate OrderDeliveryDate CustomerID 00 Author AuthorlD FirstName Initials LastName Customer CustomerID CustomerName CustomerAddress State Book ISBN Title Category Stock Price Used OrderLine OrderNo OrderRank ISBN Quantity PercentageDiscount Wrote 00 Authorld ISBN AuthorRank. 00 Order Order No OrderDate OrderDeliveryDate CustomerID 00 Author AuthorlD FirstName Initials LastName Customer CustomerID CustomerName CustomerAddress State

Answer rating: 100% (QA)

Solution 1 SQL SELECT DISTINCT AuthorFirstName AuthorLastName FROM Books INNER JOIN OrderLines ON BooksISBN OrderLinesISBN INNER JOIN Customers ON Ord... View the full answer