MskProdComputing
Back to the topic

The mark scheme starts on a new page, so you can print pages one and two for a student and keep the last page yourself. Everything prints in black on white whichever theme you are using.

Python · GCSE 5

Databases and SQL

Section A: 10 multiple choice questions, 10 marks. Section B: 5 written questions, 14 marks. Total 24 marks.

Name

Class

Date

Mark  / 24

Section A

Circle the letter of the correct answer. One mark each.

  1. 1What is a primary key?1
    1. AThe first field in a table
    2. BA password for the database
    3. CA field whose value is unique for every record
    4. DA field that links to another table
  2. 2What is one row of a table called?1
    1. AA field
    2. BA record
    3. CA key
    4. DA query
  3. 3What does SELECT Name FROM Student WHERE Mark > 50 return?1
    1. AThe marks of every student called Name
    2. BNothing, because the query is invalid
    3. CEvery field of every student
    4. DThe name of every student with a mark over 50
  4. 4Which wildcard in a LIKE pattern matches any run of characters?1
    1. A?
    2. B#
    3. C*
    4. D%
  5. 5Which clause shows the highest marks first?1
    1. AGROUP BY Mark
    2. BSORT Mark
    3. CORDER BY Mark DESC
    4. DORDER BY Mark ASC
  6. 6What does UPDATE Student SET Mark = 0 do when it has no WHERE clause?1
    1. ASets the mark of every student to 0
    2. BCauses a syntax error
    3. CSets the mark of the first student to 0
    4. DDoes nothing
  7. 7Which statement removes records from a table?1
    1. ADROP RECORD
    2. BCLEAR
    3. CDELETE FROM
    4. DREMOVE
  8. 8What is a foreign key?1
    1. AA second primary key, used when the first one has already been taken
    2. BA key that is stored in a separate file so that it cannot be copied
    3. CA field that can hold any value because the database does not check it
    4. DA field that holds the primary key of a record in another table
  9. 9Why should a Python program use ? placeholders in an SQL query?1
    1. AIt is only needed for numbers
    2. BIt stops typed text being run as part of the query
    3. CIt makes the query run faster
    4. DIt lets the query return more records
  10. 10What does the * in SELECT * FROM Student mean?1
    1. AEvery field
    2. BThe first field only
    3. CEvery table
    4. DMultiply

Section B

Answer in the space provided. The number of marks tells you how many separate points to make.

  1. 1StateState what is meant by a primary key, and why every table needs one.2
  2. 2WriteA table called Book has the fields BookID, Title, Author and Pages. Write an SQL query that displays the title of every book that has more than 300 pages, with the book with the most pages first.3
  3. 3WriteWrite an SQL statement that changes the author of the book with BookID 7 to the text A. Khan.3
  4. 4StateThe records in the Student table are shown. State the result of the query SELECT Name FROM Student WHERE Year = 11 AND Mark >= 60.2
    StudentIDNameYearMark
    1Asha1056
    2Ben1171
    3Chloe1038
    4Dev1190
    5Esme1064
    6Alan1145
  5. 5ExplainA program asks the user to type a name and joins it into an SQL query using string concatenation. Explain the security risk, and describe how it should be done instead.4

Mark scheme

Section A

1C2B3D4D5C6A7C8D9B10A

Section B

1 State what is meant by a primary key, and why every table needs one. 2

One mark for each of the following, to a maximum of 2.

  • A field that holds a value that is unique to each record
  • So that each record can be identified or found without confusion

Model answer. A primary key is a field whose value is unique to each record in the table. Every table needs one so that each record can be identified exactly, because other fields such as a name could be repeated.

2 A table called Book has the fields BookID, Title, Author and Pages. Write an SQL query that displays the title of every book that has more than 300 pages, with the book with the most pages first. 3

One mark for each of the following, to a maximum of 3.

  • SELECT Title FROM Book
  • WHERE Pages > 300
  • ORDER BY Pages DESC

Model answer.

SELECT Title
FROM Book
WHERE Pages > 300
ORDER BY Pages DESC

SELECT names the field to display and FROM the table. WHERE restricts the records to those with more than 300 pages, and ORDER BY with DESC puts the longest book first.

3 Write an SQL statement that changes the author of the book with BookID 7 to the text A. Khan. 3

One mark for each of the following, to a maximum of 3.

  • UPDATE Book
  • SET Author = 'A. Khan'
  • WHERE BookID = 7

Model answer.

UPDATE Book
SET Author = 'A. Khan'
WHERE BookID = 7

The WHERE clause is essential. Without it every book in the table would be given A. Khan as its author.

4 The records in the Student table are shown. State the result of the query SELECT Name FROM Student WHERE Year = 11 AND Mark >= 60. 2

One mark for each of the following, to a maximum of 2.

  • Ben is included
  • Dev is included, and no other names

Model answer. Only Ben and Dev are in Year 11 with a mark of 60 or more. Alan is in Year 11 but scored 45, and Esme scored 64 but is in Year 10.

5 A program asks the user to type a name and joins it into an SQL query using string concatenation. Explain the security risk, and describe how it should be done instead. 4

One mark for each of the following, to a maximum of 4.

  • The user could type SQL code instead of a name
  • This would be run as part of the query, so data could be read or changed that should not be
  • Use placeholders or a parameterised query
  • The value is passed separately so it is treated only as data

Model answer. A user could type SQL code into the name box instead of a name. Because the text is joined into the query, it is run as part of the instruction, which is called SQL injection, and it could return every record or change or delete data. The query should instead use a placeholder, or parameterised query, where the value is passed to the database separately, so whatever is typed is treated only as data and never as part of the query.