Sqlite代写、SQL执行语句代写

日期：2018-04-23 01:02

SQL Assignment 2 (20%)Questions:1. Produce a table showing the total books that are currently “CheckedOut” by every major. Show theMajor and “Total Books Checkedout” in the query result and sort the “Total Books Checkedout” indescending order. Use only the MediaCode in the Where condition.2. Produce a table showing the total books published by each author between 2000 and 2015. Show onlythe rows if the total published books is greater or equal 3. Sort the authors in ascending order.Assuming you do not know the MediaID but you know the MediaCode.Author Total Books Published3. Find the customers who have an item due on the 8th August 2015 and has not “checkedin” in the loantable. Sort the Full Name in ascending order. Show only the date without the time. Do not usestrftime() in this question.Full Name Title DueDate4. The library would like to know the number of times a customer has an overdue fee and their totaloverdue fee. Sort the output by the ‘Number of times overdue’ in descending order then by the ‘Totaloverdue fee’ in descending order. Show only the total overdue fee greater or equal to $5 but less than$100.Full Name Number of TimesOverdueTotal Overdue Fee5. (a) Find all the unique customer full name with at least one loan.*Important*(1) Use the join syntax below for questions that require an inner join. Other syntax will notbe accepted.SELECT column-listFROM table 1 JOIN table 2 ON joint-conditionWhere conditions(2) Use “=” sign, for example “percent_time =100” not “percent_time IS 100”.(b) Use 5(a) as a subquery, find all INFO students who did not make any loan. Include both their FullName, CustomerID, and Major in your results.CustomerID Full Name Major{The format of full name should be “Last Name, FirstName”.}INFO6. (a) Find the total overdue fee for the customer "Barclay, Fletcher" without using ID in the query.(b) How many distinct students (exclude "Barclay, Fletcher") with a single overdue fee greater thanhalf the total overdue fee of "Barclay, Fletcher"? You must use 6(a) as a subquery. You must usethe “NOT” SQL in this question.7. The manager is interested to know yearly borrowing volume by Gender. Present a table displaying theGender, Year, Month and The Number of Loans. Sort the results in descending order by Gender,ascending order by Year and ascending order by Month. Show only the rows if the number of loans isless than 25.Hint: (1) Use CheckedOut date to extract the year and month when the book is loaned; (2) Usestrftime() function to deal with the year and month.Gender Year Month The Number of LoansM…F8. (a) Find the total number of loan for each customerID.(b) Show the total number of loan for each customer include customers who have no loan. You mustuse 8(a) as sub-query to form the right part of the outer join table. Show the result according to theformat below.CustomerID LastName FirstName Totalloan9. Show the number of books published each year by each author. Show only the rows for author whopublished more than 1 book in a year. The table should show author, Pubyear, and “Total BookPublished in Year”. MediaID should not be used in this query, use MediaCode.10. Use only a left outer join to find the customers who did not make any loan. Show the CustomerID, FullName, and Major. Show only the rows contain “INFO” major only.CustomerID Full Name Major{The format of full name should be “Last Name, FirstName”.}INFO11. How many students with a total overdue fee greater than half the total overdue fee of "Barclay,Fletcher" (exclude "Barclay, Fletcher")?12. Use the SQL date modifier expression in chapter 44 of the Sqlite tutorial to add 3 days, 8 hours, 3minutes, and 45 seconds to the date “2018-05-11 23:59:59”. This question does not require adatabase.