School of Computing: Assessment brief Module title Databases
sqlite作业 | 代做IT | sql代做 | 代写database – 这道题目是利用sql进行的编程代写任务, 包括了IT/sql/database等方面, 这个项目是assignment代写的代写题目
Module code (^) COMP
assignment title Coursework 1
Assignment type and
description
In-course assessment, writing SQLite queries
Rationale (^) Write SQLite queries to retrieve data from a database
Word lim IT and
guidance
This coursework should take less than 10 hours to complete
Weighting 30%
Submission deadline 10:00 16 March 2023
Submission method Online through Gradescope
Feedback provision Within 3 weeks after submission deadline
Learning outcomes
assessed
LO2: use appropriate tools to manipulate database systems
LO3: design and implement a database using appropriate tools
Module lead Hui Lau
Other Staff contact
1. Assignment guidance
A. Introduction
For this coursework, you will be utilising the Chinook database, which comprises of 11 tables:
albums, artists, employees, customers, genres, invoice_items, invoices,
media_types, playlist_track, playlists and tracks. Your task is to write SQLite queries
to retrieve data from multiple tables within the Chinook database. The queries will demonstrate
your understanding of the structure of the Chinook database and your ability to retrieve data from
multiple tables through the use of SQLite queries. This coursework will be marked using the current
SQLite version 3.40.1.
B. Preparation
Please follow the following instructions:
- Download the zip file cwk1-files.zip from Minerva. The file cwk1-files.zip contains the chinook database file (chinook.db), a database diagram on the relationships between tables in the database for your reference (chinook_database_diagram.pdf), and template sql file (sqlcwk.sql) in which you write the SQLite queries.
- Unzip cwk1-files.zip and put these files in a folder/directory.
- Write your SQLite queries in the template sql file sqlcwk.sql. You can edit sqlcwk.sql with any document editing software such as Notepad or Notepad++. Write your name or username at the top of the file indicated by @author.
- To test your written SQLite queries: i. Launch a terminal or navigate to the same directory containing both chinook.db and sqlcwk.sql, and then open the database with the command sqlite3 chinook.db. ii. Run the sql file by typing .read sqlcwk.sql on the terminal. iii. Check to make sure views were created, which can be done with a basic SELECT * statement.
2. Assessment tasks
- Write a SQLite query to create a view called vCustomerPerEmployee for each employees LastName, FirstName, EmployeeID, and the total number of customers served by them (named as TotalCustomer) as shown below. [5 marks]
LastName FirstName EmployeeID TotalCustomer
Example output is shown in Figure 1. You can check the created view by selecting all data from
the view.
Figure 1: Sample output for vCustomerPerEmployee
2. Write a SQLite query to create a view called v10WorstSellingGenres for the 10 worst-selling genres
(named as Genre) based on the quantity of tracks sold (named as Sales), order by Sales in
ascending order as shown by sample output in Figure 2. [6 marks]
Figure 2: Sample output for v 10 WorstSellingGenres
3. Write a SQLite query to create a view called vBestSellingGenreAlbum for the best-selling album in
each genre with sales (based on the quantity of tracks sold, named as Sales) with the following named
columns. [6 marks]
Genre Album Artist Sales
Sample output is shown in Figure 3*. You can check the created view by selecting all data from
the view.
Figure 3: Sample output for vBestSellingGenreAlbum
* Other album with same sales in the same genre also acceptable.
- Write a SQLite query to create a view called v10BestSellingArtists for the 10 best-selling artists based on the total quantity of tracks sold (named as TotalTrackSales) order by TotalTrackSales in descending order as shown in the sample output in Figure 4. TotalAlbum is the number of albums
with tracks sold for each artist. [6 marks]
Artist TotalAlbum TotalTrackSales
You can check the created view by selecting all data from the view.
Figure 4: Sample output for v10BestSellingArtists
- Write a SQLite query to create a view called vTopCustomerEachGenre for the customer (named as
TopSpender) that spent the most (based on quantity x unitprice, named as TotalSpending) on
each genre of music as shown in sample output in Figure 5*. [7 marks]
Figure 5: sample output for vTopCustomerEachGenre
* Other customer with same TotalSpending in the same genre also acceptable.
Genre TopSpender TotalSpending
3. General guidance and study support
Developing your academic skills will enable you to become a more effective learner. Online
resources on critical thinking, reading, academic writing and more can be found at Skills@Library
website at https://library.leeds.ac.uk/info/1401/academic_skills#minerva.
4. Assessment criteria and marking process
When you submit work for assessment it is expected that it will meet the Universitys academic
integrity standards. Standard university penalty of 5% of available marks per day, or part of a day,
will apply to late work. Late submissions are acceptable up to 7 days late. Feedback on late
submissions may not be provided within 3 weeks of submission deadline.
5. Presentation and referencing
All SQLite queries must be written in the file sqlcwk.sql , do not change the filename.
6. Submission requirements
You only need to submit the SQL file sqlcwk.sql containing all your SQL queries to Gradescope.
Please do not submit any other files and do not change the filename. Make sure you have
tested sqlcwk.sql with no errors and check the views created with SELECT * statements.
7. Academic misconduct and plagiarism
Academic integrity means engaging in good academic practice. This involves essential academic
skills, such as keeping track of where you find ideas and information and referencing these
accurately in your work.
By submitting this assignment you are confirming that the work is a true expression of your own
work and ideas and that you have given credit to others where their work has contributed to yours.
8. Assessment/ marking criteria grid
All question
All values correct for all columns and rows
Some missing/incorrect values for columns/rows
3 marks
1 mark
The spelling of all columns is correct 1 mark
Question specific
Correct use of GROUP BY + COUNT/MAX/DISTINCT/SUM 1 mark
Correct use of ORDER BY + DESC + LIMIT 1 mark
Correct use of || to concatenate strings 1 mark