O LevelComputer ScienceCambridge (CIE)Exam Questions9. DatabasesSQL

SQL (Cambridge (CIE) O Level Computer Science)

Exam Questions

25 mins7 questions
Easy
Medium
Hard
12 marks

A database table, PERFORMANCE, is used to keep a record of the performances at a local theatre.

ShowNumber

Type

Title

Date

SoldOut

SN091

Comedy

An Evening at Home

01 Sept

Yes

SN102

Drama

Old Places

02 Oct

No

SN113

Jazz

Acoustic Evening

03 Nov

No

SN124

Classical

Mozart Evening

04 Dec

Yes

SN021

Classical

Bach Favourites

01 Feb

Yes

SN032

Jazz

30 Years of Jazz

02 Mar

Yes

SN043

Comedy

Street Night

03 Apr

No

SN054

Comedy

Hoot

04 May

No

Show the output that would be given by this structured query language (SQL) statement:

SELECT Date, Title

FROM PERFORMANCE

WHERE NOT SoldOut AND Type = "Jazz";

How did you do?

Did this page help you?

12 marks

A database table, 2018MOV, is used to keep a record of movie details.

Table listing movie catalog numbers, titles, genres, and availability on Blu-ray, DVD, and streaming. Films include "Power Rangers," "Wonder Woman," and "Despicable Me."

Complete the structured query language (SQL) to return the category number and title for all Comedy movies.

SELECT CatNo, Title

....................2018MOV

WHERE Genre1 = ...................................;

How did you do?

Did this page help you?

23 marks

A computer game shop records its stock levels in a database table called GAMES. The fields used in the stock table are shown.

Name

Description

GameID

primary key

GameName

the name of each game

AgeRestriction

the minimum age at which a person is allowed to play each game

GamePrice

the selling price for each game

NumberStock

the quantity of each game currently in stock

OnOrder

whether or not each game is on order from the suppliers

DateLastOrdered

the date the most recent order for each game was placed

GameDescription

a summary of the contents and purpose of each game

Complete the query-by-example grid to output all the games that have no stock and that are on order with the supplier.

Display only the GameID, GameName and GamePrice fields in alphabetical order of the name of the game.

A grid with columns labeled Field, Table, Sort, Show, Criteria, and Or. Show row has checkboxes in its cells; others are empty.

How did you do?

Did this page help you?

34 marks

A database table, NURSE, is used to keep a record of disposable items worn by veterinary nurses.

This is part of the table:

ItemNumber

Description

SingleUse

Uses

StockLevel

ReorderLevel

DIG1

Glove (pair)

Y

1

500

800

DIA1

Apron

Y

1

700

800

DIM5

Hair net

Y

1

650

500

DIA2

Apron

N

5

25

100

DIS4

Suit

N

3

70

50

DIV9

Shoe cover (pair)

Y

1

400

250

Complete this query-by-example grid to display only the item number and the description of single use items, where the stock level is below the reorder level.

Table layout with columns for Field, Table, Sort, Show, Criteria, and Or, having checkboxes on the Show row and blank cells elsewhere.

How did you do?

Did this page help you?

44 marks

A database table called TVRange shows the main features and prices of a range of televisions.

Table listing various TV models with columns for TV Code, Screen Size, Satellite support, Smart TV support, Sound Bar inclusion, and Price in dollars.

Complete the structured query language (SQL) query to return the television (TV) code, screen size and price of all Smart TVs in the database table

SELECT TVCode,...................................,

.................TVRange

WHERE SmartTV =.............................;

How did you do?

Did this page help you?

53 marks

A music streaming service has a new database table named Songs to store details of songs available for streaming. The table contains the fields:

  • SongNumber – the catalogue number, for example AG123

  • Title – the title of the song

  • Author – the name of the song writer(s)

  • Singer – the name of the singer(s)

  • Genre – the type of music, for example rock

  • Minutes – the length of the song in minutes, for example 3.75

  • Recorded – the date the song was recorded.

Explain the purpose of the structured query language (SQL) statements.

SUM (Minutes) FROM Songs WHERE Genre = "rock";

COUNT (Title) FROM Songs WHERE Genre = "rock";

How did you do?

Did this page help you?

1a4 marks

The table students stores information about students and their exam results. The table has the following structure:

Field Name

Data Type

Description

StudentID

NUMERIC

Unique identifier for each student

FirstName

STRING

First name of the student

LastName

STRING

Last name of the student

Class

STRING

Class of the student (e.g., '10A')

Subject

STRING

Subject of the exam (e.g., 'Maths')

Score

NUMERIC

Score obtained in the exam (0-100)

Write an SQL query to find the total sum of scores obtained by students in each class. The results should be ordered by class, and the output should display the class and the total sum of scores

How did you do?

1b3 marks

 Write an SQL query to find the total number of exam records where the students scored above 75. The output should display just the total count

How did you do?

Did this page help you?