N5 Computing Database Revision Part 2

N5 Computing Science

DATABASE REVISION - PART 2

Countries Database

For this lesson we will look at a geographical database holding
information about cities and countries.

Multiple Choice

What is the primary / foreign key join between these tables?

• Country.countryName = City.cityName
  

• Country.countryCode = City.cityID
  

• Country.countryCode = City.countryCode
  

• Country.cityID = City.CityID

Data Dictionary

This is a plan for the data base and lists the tables, field names and their attributes.

Multiple Choice

What is missing in part (b)?

• PK

• FK

• Text

• 4

• Yes

Multiple Choice

What is missing in part (a)?

• PK

• FK

• Text

• 4

• Yes

Multiple Choice

What is missing in part (c)?

• PK

• FK

• Text

• 4

• Yes

Multiple Choice

What is missing in part (d)?

• PK

• FK

• Text

• 4

• Yes

Multiple Choice

What is missing in part (a)?

• PF

• FK

• Text

• Number

• No

Multiple Choice

What is missing in part (b)?

• PF

• FK

• Text

• Number

• No

Multiple Choice

What is missing in part (c)?

• PF

• FK

• Text

• Number

• No

Multiple Choice

What is missing in part (d)?

• PF

• FK

• Text

• Number

• No

Multiple Choice

What is missing in part (e)?

• PF

• FK

• Text

• Number

• No

E-R diagram

Entity relationship diagrams are graphical representations of the link between the tables in a database.

They can be 1:1, 1:m or M:M

Draw

Draw the relationship that exists between City and Country

Dropdown

Write the code that will produce the output shown - Select ​

Select answer

, ​

Select answer

FROM ​

Select answer

ORDER BY ​

Select answer

​

Select answer

;

Dropdown

Write the code that will produce the output shown -

Select ​ ​

Select answer

, ​ ​

Select answer

FROM ​ ​ ​

Select answer

ORDER BY ​ ​ ;​

Select answer

Dropdown

Write the code to produce to produce an alphabetical list of all cities names,
their countries and their population for all countries with

Select answer

​ , ​ ​ ​

Select answer

, ​

Select answer

FROM ​ ​ ​ City ​ , ​ Country

WHERE ​ ​

Select answer

SQL Queries

Write an SQL query to produce a list of all
cities names and populations for cities
with a population greater than 2,152,423
(the population of Paris)
Copy your working query into
the message chat
https://www.db-fiddle.com/f/qYUKpr2Rdv
3LSLAETbZRnH/5

Dropdown

Write the code to produce a list of all cities names and populations for cities

with a population greater than 2,152,423
(the population of Paris)

Select ​ ​

Select answer

​ , ​ ​

Select answer

FROM ​ ​ ​

Select answer

​

WHERE ​

Select answer

Dropdown

Write the code to find the names of all cities, and the
country they are in for all cities further north than Amsterdam

Select ​ ​

Select answer

FROM ​ ​ ​ ​

Select answer

​

WHERE ​ ​

Select answer

​

Select answer

​

Select answer