INFOSYS 222代写、SQL语言代做、代写SQL编程语言、代做database script

日期：2019-05-12 09:53

INFOSYS 222 A2P2 –SQL (15%)

BACKGROUND

The purpose of this assignment is to assess the level of understanding and skill in composing SQL

statements and SQLite commands. The questions are based on the database of a fictitious

trading company called Northwind that manages their business records from 1996 to 1998.

Students need to download a copy of the database script file (i.e. nw.sql) from Canvas and create

a database instance (e.g. nw.db) before they proceed with the questions. All answers should be

captured in a single file for submission (i.e. username.txt or username.sql).

QUESTIONS

Q01: Use the comment feature of SQL to print your full name, AUID and username in three

separate lines at the top of the script file. From this point onwards, you should use the comment

feature to separate your answers for each question. (0.2 mark)

Q02: Write a SQL statement to retrieve all the rows from the Product table. All the columns

should be renamed with proper spacing using the alias feature. For example, the UnitPrice

column should be renamed as “Unit Price”. (0.2 mark)

Q03: Write a SQL statement to retrieve all the rows from the Product table with ProductName,

UnitPrice and UnitsInStock columns only. The rows should be sorted by UnitPrice in descending

order. (0.2 mark)

Q04: You need to locate the phone number of a shipper company named United Package. Write

a SQL statement to retrieve only that piece of information from the appropriate table. Your SQL

statement should cater for different casing scenarios. (0.2 mark)

Q05: You are going to send a product list to all the customers by fax. Write a SQL statement to

retrieve rows from the Customer table only if those customers have a fax number. (0.2 mark)

Q06: You want to study some particular orders. Write a SQL statement to retrieve rows from the

Order table when their OrderDate values are within the month of July in 1996. (0.2 mark)

2

Q07: You want to generate a list of countries that covers all the existing customers. Write a SQL

statement to retrieve a country list without any duplication (i.e. the same country should not

appear twice in that list) from the Customer table. (0.2 mark)

Q08: Write a SQL statement to return the total number of rows in the Order table. Rename the

column in the output as “Numbers of Order”. (0.2 mark)

Q09: Write a SQL statement without using any function to retrieve a list of products where the

ProductName is exactly 5 characters long. Write another SQL statement that uses appropriate

function to do the exact same thing. (0.2 mark)

Q10: You are interested to know the top 10 most stocked products in the inventory. Write a SQL

statement to retrieve that information from the Product table with both the ProductName and

UnitsInStock columns. (0.2 mark)

Q11: Write a SQL statement to generate a tidy employee list with only two columns, one with

their full name and one with their full address. The full name is composed of the LastName in all

caps, the FirstName, and a comma in between. (0.5 mark)

Q12: You need to examine the order detail of the order 10250. Write a SQL statement that

would retrieve the rows from the OrderDetail table with the columns OrderID, ProductID,

UnitPrice, Quantity, Discount and a derived column named Subtotal which is calculated from

other columns. For the output, the columns UnitPrice and Subtotal should have a dollar sign ($)

as prefix, and Discount should be shown as a percentage with the sign (%) as suffix. (0.5 mark)

Q13: Write a SQL statement that can retrieve all products with the following features:

ProductName begins with ‘C’; CategoryID equals to either 1 or 2; UnitPrice that is more than

$20; and Discontinued is false. (0.5 mark)

Q14: Write the SQL statements to insert 3 rows to the Shipper table:

CompanyName Phone

Trustworthy Delivery (503) 555-1122

Amazing Pace (503) 555-3421

Your Name Limited (503) Your AUID

For the last row use your full name as the CompanyName and use the first 7 digits of your own

AUID as the number for the Phone after the area code 503. Make sure the ShipperID is

automatically generated and so you do not enter them manually. (0.5 mark)

Q15: Write a SQL statement to generate a list of employees with FirstName, LastName and their

exact age from the Employee table. For the output, the age should be rounded to the nearest

number without any decimal value, and it should be shown as a whole number. (0.5 mark)

Q16: Two employees of Northwind, Nancy Davolio and Andrew Fuller, are married recently and

you are asked to update Nancy’s details in the Employee table. Write a SQL statement to update

3

Nancy’s LastName from Davolio to Fuller, and also the TitleOfCourtesy from Ms. to Mrs. Your

SQL statement should cater for different casing scenarios. (0.5 mark)

Q17: Since Nancy has moved in with Andrew after they get married, you need to change her

Address, City, Region, PostalCode and HomePhone columns from the Employee table. Write a

SQL statement with subqueries to update Nancy’s contact details. Your SQL statement should

cater for different casing scenarios. (0.5 mark)

Q18: Write a SQL statement to create a new table called ProductHistory with the following

column specifications:

Column name Data type Nullability

ProductID INTEGER No

EntryDate DATE No

UnitPrice REAL Yes

UnitsInStock INTEGER Yes

UnitsOnOrder INTEGER Yes

ReorderLevel INTEGER Yes

Discontinued INTEGER No

You need to set a primary key and a foreign key for the table. Add a primary key constraint based

on both the ProductID and EntryDate columns. Add a foreign key constraint based on the

ProductID columns of both ProductHistory and Product tables. (0.5 mark)

Q19: Write a SQL statement to fill up the ProductHistory table with all existing rows in the

Product table. The EntryDate column should be filled with the current date and time that can be

obtained from the appropriate function. (1 mark)

Q20: Write a SQL statement to create a list with 2 columns: Day of Week and Hired. The Day of

Week column has a possible value ranged from Monday to Sunday, and the Hired column shows

the exact number of employees from the Employee table being hired on a particular Day of

Week. (1 mark)

Q21: You are asked to find out the top sales representative (by the accumulated dollar amount

of orders) in Northwind. Write a SQL command to return the LastName, FirstName and the total

dollar amount of orders of that employee under the column Total with a dollar sign ($) as prefix.

(1 mark)

Q22: Write a SQL statement with no subquery to create a list with 2 columns: Employee and

Manager. The column Employee shows the FirstName of the employee, and the column

Manager shows the FirstName of the manager of that employee. Make sure that all employees

are listed whether they have a manager or not. For those employees who do not have a

manager, the value “No manager” should show up in the second column. (2 marks)

Q23: You notice that some products are sold lower than their recommended prices (i.e. the

UnitPrice with Discount in OrderDetail table is lower than the UnitPrice in the Product table).

Write a SQL statement to generate a full customer list with five columns:

4

1. CompanyName column from the Customer table renamed as Company;

2. Recommended column with the total amount they should have paid for all the products

ordered based on the UnitPrice from the Product table,

3. Ordered column with the total they have actually paid (calculated from the OrderDetail

table),

4. Discount column showing discount in absolute value, and

5. Percentage column showing discount in percentage value

All columns with numeric values should be rounded to two decimal places and displayed with

appropriate prefix or suffix. Sort the list by the Percentage column in descending order to show

which customer is enjoying the highest percentage of discount from Northwind. (2 marks)

Q24: According to the Order table, Northwind ships their products to over 20 different countries

via three companies in the Shipper table. Over the years staff in Northwind have learnt from

which shipper company would be the best for which country. Your task is to reveal that piece of

tacit knowledge from the existing data, assuming that the preferred shipper would have earned

the highest total Freight from Northwind for shipments to a particular country. Write a SQL

statement with subqueries to generate a list with two columns: the ShipCountry from the Order

table and also the preferred CompanyName from the Shipper table for that country. (2 marks)