Module Info
DSA8002 – Database & Programming Fundamentals
2021/22
Mock Practical Test 2:
Database and SQL Programming
ADMINISTRATION:
– The assessment is under normal University conditions so you will not bring lecture notes or any supporting materials, have access to the internet, email and are strictly prohibited to communicate to anyone during the assessment.
– You can use “DB Browser for SQLite” to develop and debug your SQL scripts.
BACKGROUND:
Assume the experiment where you have measured the strength of the ultraviolet (UV) radiation and air pollution level in 8 cities in the world during a specific week of the year. You are given a database (lab_assessment2_mock_exam.db) with the following tables:
1. CITIES table that contains basic information about the cities used in the study, with the following columns:
a.
city_name – denotes names of all cities that are used in study
b. city_id – denotes primary key, i.e. the unique city identifier (each city has the unique identifier)
2. AIR table that contains collected information about the weather, with the following columns:
a.
city_id – denotes unique city identifier
b. day – denotes day when the measurement was made (1=”Monday”, 2=”Tuesday”, …)
c. uv – denotes measured the strength of the ultraviolet (UV) radiation, the range of uv index is from 1 to 11, shown as follows
Index
Exposure
1-2
low
3-5
moderate
6-7
high
8-10
very high
11
extreme
d. aqi – denotes measured Air Quality Index (AQI), shown as follows
AQI
Air Pollution Level
Health Implications
0 – 50
Good
Air quality is considered satisfactory, and air pollution poses little or no risk
51 -100
Moderate
Air quality is acceptable; however, for some pollutants, there may be a moderate health concern for a very small number of people who are unusually sensitive to air pollution.
101-150
Unhealthy for Sensitive Groups
Members of sensitive groups may experience health effects. The general public is not likely to be affected.
151-200
Unhealthy
Everyone may begin to experience health effects; members of sensitive groups may experience more serious health effects
201-300
Very Unhealthy
Health warnings of emergency conditions. The entire population is more likely to be affected.
300+
Hazardous
Health alert: everyone may experience more serious health effects
QUESTIONS:
1. Investigate the size (i.e., number of records/rows) of the AIR table. The correct answer is _____. [20 marks]
a) 40
b) 42
c) 43
d) 44
SQLite solution:
2. Investigate the highest Air Quality Index in each city (with “city_id”). The correct answer is ____. [20 marks]
a) 1: 100, 2: 80, 3: 75, 4: 65, 5: 60 and 7:800
b) 1: 100, 2: 80, 3: 75, 4: 60, 5: 65 and 7:800
c) 1: 60, 2: 100, 3: 75, 4: 60, 5: 65 and 7:800
d) 1: 60, 2: 60, 3: 75, 4: 100, 5: 65 and 7:800
SQLite solution:
3. Investigate if there is(are) city(cities) for which there is no information on air data. The correct answer is ____. [20 marks]
a) There is information on weather data for all cities.
b) There is no information on weather data for the city: Belfast
c) There is no information on weather data for the cities: NewYork, HongKong
d) There is no information on weather data for the cities: HongKong
SQLite Solution:
4. Investigate the minimum uv and the average aqi recorded in London, between the 2nd and the 5th day of the week (inclusive). The correct answer is ____. [20 marks]
a) The minimum uv is 6, and the average aqi is 40.25
b) The minimum uv is 2, and the average aqi is 40.25
c) The minimum uv is 2, and the average aqi is 41.25
d) The minimum uv is 3, and the average aqi is 41.25
SQLite Solution:
5. Investigate the city that had the lowest recorded average aqi. The correct answer is ____. [20 marks]
a) Belfast
b) London
c) Paris
d) Berlin
e) Roma
f) NewYork
g) BeiJing
h) HongKong
SQLite Solution:
Lab Assessment
Page 5 of 5