讲解：SQL、datatype、SQLProcessing|SQL

likely give you a wrong result.attention to rounding. Do not use PostgreSQL’s round function, which is not SQL standard and wouldplus 20%. Return the invoice ID. Keep in mind the datatype of ”amount” in invoices and pay special(03) Find invoices that have been taxed. The amount of such invoices is the total of the order they refer toproducts that have never been ordered are not included in the output.and the corresponding average; the latter must be a number with exactly two decimal places. Types ofdo not contribute to the average for that type (rather than contributing 0). Return the product typein an order (taking into account quantities). Orders that do not include any product of a certain type(02) For each product type calculate the average number of times products of that type have been includedall countries and corresponding total counts.country did not place any orders in 2016, the country appears in the output with 0 total orders. Return(01) Total number of orders placed in 2016 by customers of each country. If all customers from a specificAssignment. Write the following queries in SQL.queries will be executed may well contain other, different types of products (as well as customers’ countries).how many types of products there are in the database it is run against. Indeed, the instances on which yourconstraint in the database schema that enforces that, and your query must work independently of which andproduct type, you cannot assume that the values for ptype will only be BOOK, MUSIC and MOVIE: there is no(case-sensitive), and similarly for the other two product types. However, if a query asks something for eachMUSIC and MOVIE. If a query asks for books, this means products where the value of ptype is the string BOOKcannot have two different values of ptype. In the test database, there are three types of products: BOOK,Note that table Products has a column ptype that indicates the type of product. The same productdo not in any way constrain the database.to generate more realistic-looking test data. These directives will be completely ignored by the DBMS andResources/Assignments. The schema file is annotated with comments consisting of directives for datafillerDatabase schema. For this assignment we will use the schema schema.sql available on Piazza underLab Homework 2, 6% of the final gradeDATABASE SYSTEMS(04) For each type of product, find the customer who ordered the highest number of products of that type(taking into account quantities). Return the product type and the ID of the customer. If two or morecustomers are tied for a specific product type they will all be included in the output. If no products ofa specific type have ever been ordered, that type will not be in the output.(05) For each customer, calculate the number of orders placed and the average spend, which is the averagetotal (taking into account quantities and unit prices of ordered products) across all orders placed bythat customer. Return the customer ID, the number of orders, and the corresponding average spend.The latter must be a number with exactly two decimal places. Orders without detail must be consideredin the calculation of the average as having a total of 0. Customers who did not place any orders willbe included in the output with 0 orders (not 0.00 or anything else, just 0) and NULL average spend.Submission instructions.• You will be submitting your work through CourSys. What to submit:– a .zip file consisting of 5 files SQL留学生作业代做、代写datatype课程作业、SQL编程语言作业调试 代做留学生Processing|代做数据库Swith the SQL queries (so that we could run them). Before producingthe .zip file, put all your queries in a directory (folder) calledLastname-Firstname-queriesThen produce .zip file of that folder.• Each query must be written in a text file named .sql where is thetwo-digit number in the list of queries above. For example, the first query will be written in fileLastname-Firstname-01.sql and the last one in file Lastname-Firstname-05.sql.• Each file consists of a single SQL statement, terminated by semicolon (;). Submitted files that do notcontain exactly one semicolon will be discarded when the submission is processed and consequentlythey will not be assessed (as if they were not submitted). Please pay attention to this; even if it lookslike a trivial detail, it is not.• Files can be submitted more than once, in which case the previously submitted version will be overwritten.• Before submitting your files, you should check that they run smoothly in PostgreSQL Server in CSIL,using the exact database schema provided in the link above.• The final deadline for the submission is listed above. As file history is not recorded, files whose latestversion is submitted after the deadline will not be considered for assessment.Assessment Your queries will be executed on 5 database instances without nulls. Each query is worth 10marks, which are allocated as follows:• 2 marks are given for each test database on which the query returns all and only the correct answers.• 1 mark is given for each test database on which the query returns at least 50% of the correct answers(but not all of them) and no wrong answers.The queries will not be assessed for their performance, as long as they terminate after a “reasonable” time;each query should not take more than a few seconds to run. Style will not be assessed this time either: youcould write a query on single line, but this is not recommended for your own sake.Test data. One of the five instances on which your queries will be assessed has been provided to you onPiazza. It is posted on Piazza under Resources/Assignments. Feel free to create your own instances fortesting. You already know how to insert data by hand, but you can also use datafiller if you want (thisis neither required nor supported by us) for your own interest.2Query answers. The answers your queries are supposed to return on the given database instances areavailable in CSV format in Piazza, under Resources/Assignment. The order in which the rows appear inthe answer to your queries is irrelevant for this assignment (no ordering is enforced on the answers, so theDBMS will output rows in an arbitrary order). The names of the columns in the answers are also irrelevant(the CSV files we provide have no header) so they can be renamed as you wish in the SELECT clause. Whatis important is the number of columns and the order in which they appear in the output: (1, 2) is not thesame as (2, 1, 1). Your query gives a fully correct answer if it outputs all and only the rows (with repetitions,if that’s the case) listed in the corresponding CSV file, no matter on which line.Other comments. All of the queries can be written using the constructs we have seen in class. Try touse those and nothing else (unless you are really stuck), to get more practice. If you really want to use viewsin your queries (we have not covered views), define them using the WITH construct, not the CREATE VIEWstatement.3转自：http://www.6daixie.com/contents/15/4974.html