O-LevelComputer ScienceDatabases2210/22 · May/June 2024 · Q108 Marks
A television subscription service has a new database table named Contract to store details of their subscribers' contracts. The table contains these fields: • ContractNumber – the contract number, for example CT567 • Months - the length of the contract in months, for example 6 • EndDate - the date the contract finishes, for example 30 November 2024 • News the news service, yes or no • Movie - the movie service, yes or no • Sport - the sports service, yes or no • Junior - the children's service, yes or no.
- (a)Identify the field that will be the most appropriate primary key for this table.[1]
- (b)Complete the table to identify the most appropriate data type for these fields in Contract Field ContractNumber Months EndDate Sport Data type[2]
- (c)Explain the purpose of these structured query language (SQL) statements. Statement 1: SELECT SUM (Months) FROM Contract; Statement 2: SELECT COUNT (News) FROM Contract WHERE News;[3]
- (d)Complete this SQL statement to find the contract numbers of the subscribers that take both the news and sports services. SELECT ... FROM Contract WHERE ... AND ... ;[2]
Mark scheme
Marking points from the official mark scheme (May/June 2024). The mark or mark type is shown in brackets.
- ContractNumber
- One mark for every two correct data types
- Field Data type
- ContractNumber text/alphanumeric
- Months integer
- EndDate date/time
- Sport Boolean
- One mark for each point
- MP1 to find the total number of months for all contracts
- MP2 to find the number of contracts
- MP3 that are subscribed to News
- ContractNumber
- News AND Sport // Sport AND News
- Example answer:
- SELECT ContractNumber
- FROM Contract
- WHERE News = TRUE AND Sport = TRUE ;
Write your answer and get it marked
On Oracle Prep you can write or photograph your own answer and have it marked by AI against this mark scheme, with feedback on what you missed.
Sign up free →