SQL留学生代做、代写datatype、SQL编程语言调试

日期：2020-03-11 10:44

likely give you a wrong result.

attention to rounding. Do not use PostgreSQL’s round function, which is not SQL standard and would

plus 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 to

products 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 of

do not contribute to the average for that type (rather than contributing 0). Return the product type

in 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 included

all 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 specific

Assignment. 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 your

constraint in the database schema that enforces that, and your query must work independently of which and

product 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 each

MUSIC and MOVIE. If a query asks for books, this means products where the value of ptype is the string BOOK

cannot 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 product

do not in any way constrain the database.

to generate more realistic-looking test data. These directives will be completely ignored by the DBMS and

Resources/Assignments. The schema file is annotated with comments consisting of directives for datafiller

Database schema. For this assignment we will use the schema schema.sql available on Piazza under

Lab Homework 2, 6% of the final grade

DATABASE 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 more

customers are tied for a specific product type they will all be included in the output. If no products of

a 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 average

total (taking into account quantities and unit prices of ordered products) across all orders placed by

that 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 considered

in the calculation of the average as having a total of 0. Customers who did not place any orders will

be 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 with the SQL queries (so that we could run them). Before producing

the .zip file, put all your queries in a directory (folder) called

Lastname-Firstname-queries

Then produce .zip file of that folder.

? Each query must be written in a text file named <Lastname-Firstname-xx>.sql where <xx> is the

two-digit number in the list of queries above. For example, the first query will be written in file

Lastname-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 not

contain exactly one semicolon will be discarded when the submission is processed and consequently

they will not be assessed (as if they were not submitted). Please pay attention to this; even if it looks

like 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 latest

version 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 10

marks, 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: you

could 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 on

Piazza. It is posted on Piazza under Resources/Assignments. Feel free to create your own instances for

testing. You already know how to insert data by hand, but you can also use datafiller if you want (this

is neither required nor supported by us) for your own interest.

2

Query answers. The answers your queries are supposed to return on the given database instances are

available in CSV format in Piazza, under Resources/Assignment. The order in which the rows appear in

the answer to your queries is irrelevant for this assignment (no ordering is enforced on the answers, so the

DBMS 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. What

is important is the number of columns and the order in which they appear in the output: (1, 2) is not the

same 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 to

use those and nothing else (unless you are really stuck), to get more practice. If you really want to use views

in your queries (we have not covered views), define them using the WITH construct, not the CREATE VIEW

statement.

3