SQLite generation Write, SQL execution statement generation

SQLite generation Write, SQL execution statement generation
SQL Assignment 2 (20%)
Questions:
1. Produce a table showing the total books that is currently "checkedout" by every major. Show the
Major and ' Total Books checkedout ' in the query result and sort the ' total Books checkedout ' in
Descending order. Use only the Mediacode in the Where condition.
2. Produce a table showing the total books published by each author between and 2015. Show only
The rows if the total published books is greater or equal 3. Sort the authors in ascending order.
Assuming you don't know the mediaid but you know the mediacode.
Author Total Books Published
3. Find the customers who had an item due on the 8th August and had not ' Checkedin ' in the loan
Table. Sort the full Name in ascending order. Show only the date without the time. Do not use
Strftime () in this question.
Full Name Title DueDate
4. The library would like to know the number of times a customer have a overdue fee and their total
Overdue fee. Sort the output by the "number of times overdue ' in descending order then" by the "total
Overdue fee ' in descending order. Show only overdue fee greater or equal to $ $ but less than
$.
Full Name number of times
Overdue
Total Overdue Fee
5. (a) Find all the unique customer full name with at least one loan.
*important*
(1) Use the join syntax below for questions this require an inner join. Other syntax would not
Be accepted.
SELECT column-list
From table 1 joins table 2 on Joint-condition
Where conditions
(2) Use ' = ' sign, for example ' Percent_time =100 ' not ' percent_time is 100 '.
(b) Use 5 (a) as a subquery, the Find all INFO students The Who do not do not do any loan. Include both their full
Name, CustomerID, and Major in your results.
CustomerID Full Name Major
{The format of full name should is "last name, first
Name ".}
INFO
6. (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") and a single overdue fee greater than
Half the total overdue fee of "Barclay, Fletcher"? You must use 6 (a) as a subquery. Must use
The ' not ' SQL in this question.
7. The manager is interested-know yearly borrowing volume by Gender. Present a table displaying the
Gender, 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 is
Less than 25.
Hint: (1) Use Checkedout date to extract, and month when the book is loaned; (2) Use
Strftime () function to deal with the year and month.
Gender year Month the number of Loans
M
...
F
8. (a) Find the total number of loan for each customerID.
(b) Show The total number of loan to each customer include customers who has no loan. You must
Use 8 (a) as sub-query to form the right part of the outer join table. Show The result according to the
Format below.
CustomerID LastName FirstName Totalloan
9. Show the number of books published each of the author. Show only the rows for author
Published more than 1 book in a year. The table should show author, Pubyear, and "total book
Published in year ". MediaID should not being used in this query, use Mediacode.
Use only a left outer join to find the customers who does not have a loan. Show the CustomerID, full
Name, and Major. Show only for the rows contain "INFO" major only.
CustomerID Full Name Major
{The format of full name should is "last name, first
Name ".}
INFO
How many students with a total overdue fee greater than half the total overdue fee of "Barclay,
Fletcher "(Exclude" Barclay, Fletcher ")?
Use the SQL date modifier expression in chapter for the Sqlite tutorial to add 3 days, 8 hours, 3
Minutes, and seconds to the date "2018-05-11 23:59:59". This question does not require a
Database.
Http://www.daixie0.com/contents/15/1278.htm

The core staff of the team mainly include Silicon Valley engineers, bat front-line engineers, domestic TOP5 master, PhD students, proficient in German English! Our main business scope is to do programming big homework, curriculum design and so on.

Our Direction field: Window Programming numerical algorithm AI Artificial Intelligence financial statistical Metrology analysis Big Data network programming Web programming Communication Programming game Programming Multimedia Linux plug-in programming API image processing embedded/Microcontroller database programming console process and thread Network security assembly language Hardware programming software Design Engineering Standard Rules. The generation of programming languages or tools including, but not limited to, the following ranges:

C/c++/c# Write

Java Write generation

It generation

Python writes

Tutoring Programming Jobs

The MATLAB Generation writes

Haskell writes

Processing Write

Linux Environment Setup

Rust Generation Write

Data Structure assginment Data structure generation

MIPS Generation Writing

Machine Learning Job Writing

Oracle/sql/postgresql/pig database Generation/Generation/Coaching

Web development, Web development, Web site jobs

Asp. NET Web site development

Finance insurace Statistics Statistics, regression, iteration

Prolog write

Computer Computational Method Generation

Because of professional, so trustworthy. If necessary, please add qq:99515681 or e-mail:[email protected]

: Codinghelp

SQLite generation Write, SQL execution statement generation