COMP1121代写、Databases代做、代做SQLite编程、代写SQL程序

日期：2019-04-08 10:30

University of Leeds – School of Computing

COMP1121 Databases COURSEWORK 2 (10% of module)

Submission deadline: 10.00am Tuesday 26th March 2019

To be submitted as hard copy (printed) through the Coursework Post Box in the Long Room

or the DEC-10 Lab. Attach a Coursework Header Sheet.

Information about using SQLite has been given in the lectures and you need to understand this

before you start. Typing sqlite3 at the Linux prompt on a computer in a School of Computing

lab will start sqlite.

PART A

Part A is about tables which store data about the cost of products in a shop (Table 2 below),

and the number of each of these products bought by one person on a particular shopping trip

(Table 1 below).

Table 1:

Product Quantity

Dogfood 1

Milk 2

Soup 5

Cheese 1

Table 2:

Product Price

Fishfood 123

Soup 657

Dogfood 210

Eggs 230

Cheese 120

Rhubarb 255

Milk 135

Bananas 200

Apples 290

Lettuce 10

1. Start by creating an SQLite table containing the data in Table 1, call the table shopping.

You should do this by using a CREATE TABLE statement and four INSERT INTO statements.

Make sure you specify the Primary Key. Check that the table contains the correct data

by printing the rows using a SELECT query.

Include these six statements as part 1 of the coursework you hand in

2. Create a suitable text file called ProductData so that by using the right CREATE TABLE

statement first and then the SQLite command .import ProductData products you end

up with a table containing the data in table 2 above. Again, make sure you specify the

Primary Key.

Include the contents of the file ProductData as part 2 of the coursework you hand in

3. What SQL query do you need to find how many products there are in the table products?

Check that your query produces the correct answer.

Include the query as part 3 of the coursework you hand in

4. What SQL query do you need to find how many items were bought on the shopping trip.

The five cans of soup count as five items. Check that your query produces the correct

answer.

Include the query as part 4 of the coursework you hand in

5. What SQL query do you need to find out what products in the shop have a cost higher

than 250? Check that your query produces the correct answer.

Include the query as part 5 of the coursework you hand in

6. Try the following SQL query. Make sure you are using the SQLite column mode with

headers on as explained in the lectures. Note that the * here means multiply numbers.

Make sure you understand what the result means.

SELECT shopping.product, Quantity * Price

FROM products INNER JOIN shopping

ON products.Product = shopping.Product;

Include the output produced by the query in part 6 of what you hand in. Also

include a clear and simple description in words of what the query is producing. This

description should not refer to anything to do with databases – just a statement in

everyday language about the shops and shopping world being modelled here.

7. What SQL query do you need to find out the total cost of the items bought in the shopping

trip? Check that your query produces the correct answer.

Include the query as part 7 of the coursework you hand in

8. Try the following SQL query, and make sure you understand what the result means.

SELECT Quantity * Price

FROM products INNER JOIN shopping

ON products.Product = shopping.Product

WHERE shopping.Product = ’Soup’;

Include a clear and simple description in words of what the query is producing as part

8 of what you hand in.

9. What SQL query would you need to answer this question? (A single number is expected

as the answer to the query)

How much was spent on Milk and Cheese on the shopping trip?

Try out your query and make sure it produces the answer you expect.

Include the query as part 9 of the coursework you hand in

PART B

Part B is about tables similar to those introduced in Lecture 7 which stored data on

Lecturers and Students, with tables called Teaches and Studies. Teaches has attributes

Lecturer and Module, and Studies has attributes Student, Module and Grade. Example

data can be found in the VLE under the Coursework Folder.

10. Create appropriate tables in SQLite, and import the data. Write and test a query that

will list all the modules together with the number of students studying that module, in

order of the number of students, in descending order.

Include the queries used as part 10 of the coursework you hand in.

11. Write and test a query that will list each lecturer together with the total number of

students the lecturer is teaching across all modules (but don’t count any student twice

for the same lecturer).

Include the queries used as part 11 of the coursework you hand in.

12. Write and test a query that will list each lecturer together with each module they teach

and the number of students studying that module, in order of the lecturer name.

Include the queries used as part 12 of the coursework you hand in.

13. Write and test a query that will output the number of modules in which everyone passed

the module (assuming that the pass mark is 40).

Include the queries used as part 13 of the coursework you hand in.