关于数据库:COMP1121-Databases

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
9. of what you hand in.
10. 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.
11. 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.
12. 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.
13. 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.

14. 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.

    VX: codinghelp