COMP CO859 - Practice Midterm

Fall 2025
Your Name: Student ID:
Instructions

ˆ This test is out of 2 points on 12 pages. Make sure you have them all.

ˆ You have 90 minutes to complete this test.

ˆ Do not turn over or open your test booklet until instructed to do so.

ˆ No talking, whispering, hand signals or telepathy are permitted during the test.

ˆ You are not permitted to have electronic devices in your possession during the test.

ˆ You are not permitted to wear coats, hats, or watches during the test, except in the
case of religious articles or items of medical necessity.

ˆ Violation of these instructions may result in a formal academic integrity charge.

ˆ Read each question carefully and make sure you answer all parts of each question.
Database Theory - Practice Midterm - Fall 2025 COMP CO859

<this page has been intentionally left blank >

Page 2
Database Theory - Practice Midterm - Fall 2025 COMP CO859

TOTAL ( / 2)
A. Multiple Choice: Ginos Pizza ( / 0)
Consider the following diagram:

1. The relationship between the pizzas table and the orders table would be described
as:
A. zero to one B. zero to many C. one to one D. one to many E. many
to many

2. In the stores table, address and city together make up the concept of location;
location is
A. a foreign attribute B. an atomic attribute C. a primary attribute D. a
composite attribute E. None of these

3. order id in the orders table is a:

A. composite atttribute B. foreign key C. primary key D. entity
E. derived attribute

4. If the orders table did not have an order id, a unique order could still be identified by
store id, customer id and order date time. These three components taken together
would form a(n)
A. atomic attribute B. synonym C. alias D. foreign key E. concatenated
key

Page 3
Database Theory - Practice Midterm - Fall 2025 COMP CO859

5. phone in the customers table is known as a

A. candidate key B. foreign key C. concatenated key D. entity E. cata-
log

6. The collection of all related data used by the chain of Gino’s Pizzeria is a:
A. data record B. database C. data file D. data node E. data dictionary

7. store id in stores is
A. a primary key B. an entity C. a subschema D. a dependent domain
E. a data value

8. In the stores table store id, address and phone are

A. dependent domains B. attributes C. key data elements D. data records
E. entities

9. stores, customers, pizzas and orders are:

A. attributes B. data values C. key data elements D. subschemas
E. entities

10. store id, customer id and pizza id in the orders table are known as
A. composite attributes B. atomic keys C. foreign keys D. concatenated
keys E. candidate keys

B. Multiple Choice: General ( / 0)

1. Each index consumes extra storage space, and also requires overhead maintenance
time whenever indexed data change value.
A. True B. False

2. A SELECT statement within another SELECT statement and enclosed in [square

brackets] is called a subquery
A. True B. False

3. When three or more AND and OR conditions are combined, it is easeir to use the
SQL keywords:
A. LIKE B. IN C. NOT IN D. IN and NOT IN

4. Which is not one of the stages of database design?

A. conceptual model B. physical model C. internal model D. hierarchical
model

5. Which of the following statements about subqueries is false?

Page 4
Database Theory - Practice Midterm - Fall 2025 COMP CO859

A. They must return only one column

B. They can be nested
C. They may return a single row
D. They may return multiple rows
E. They must be used when deleting rows

6. What is a foreign key?

A. A field in a table that uniquely identifies a row of another table
B. None of these
C. An attribute which uniquely identifies each row
D. One of the attributes which could be used as a primary key.
E. A key that doesn’t belong in a table.

7. What is a data dictionary?

A. The respository of all data defiitions for all objects within the scope of
the database
B. An alphabetical listing of all of the columns in one table
C. An ordered set of domains within the database
D. None of these
E. The set of indices that speed up table access

8. Which keyword removes duplicate rows in a SELECT query result set?

A. DELETE B. DISTINCT C. NOT EXISTS D. UPDATE

9. Which of the following is a syntactically correct SQL statement?

A. SELECT ALL FROM patients B. SELECT * FROM patients C. LIST
ALL FROM patients D. DISPLAY * FROM patients

10. What type of relationship can a relational database system not implement directly?
A. one to many B. many to many C. one to one D. many to one E. All
of these

11. Which of the following statements about indexes is false?

A. They can prevent full table scans
B. They can contain more than 1 column
C. They can be used to speed up INSERT operations
D. Their use is transparent once created
E. Only one index may be clustered per table

Page 5
Database Theory - Practice Midterm - Fall 2025 COMP CO859

12. In the statement:

SELECT COUNT(*) AS total FROM patients
What is total?
A. function alias B. table alias C. aggregate alias D. column alias
E. result alias

13. Which of the following is not a statement in Data Manipulation Language (DML)?
A. CREATE TABLE test (test id INT NOT NULL, test name VARCHAR(50));
B. SELECT * FROM test;
C. DELETE FROM test;
D. INSERT INTO test VALUES (1, ’This is a test’);
E. UPDATE test SET test name = ’Continue testing...’

14. An attribute whose value is obtained by applying a formula to other data elements
is a(n):
A. derived attribute B. multi-valued attribute C. intrinsic attribute D. ab-
stract attribute E. None of these

15. The CREATE TABLE command manipulates:

A. data in records B. creation of new views C. creation of new indexes
D. privileges granted to designated users E. structure of tables

16. In SQL, the clause “employee id INT NOT NULL” would be used in which state-
ment?
A. INSERT B. UPDATE C. CREATE INDEX D. CREATE TABLE
E. SELECT

17. What does CRUD stand for?

A. Create, Reuse, Update, Delete B. Concatenate, Read, Update, Delete
C. Create, Reuse, Update, Drop D. Create, Read, Upgrade, Drop E. Create,
Read, Update, Delete

18. Which function is used to compute the number of rows in an table?

A. AVG B. COUNT C. MAX D. SUM

19. Which SELECT statement clause provides the capability to logically collect rows
on common values within a particular column?
A. FROM B. ORDER BY C. HAVING D. GROUP BY

20. Which function returns the greates value in a set of values?

A. HIGH B. MAX C. GREATEST D. FUNCTION

Page 6
Database Theory - Practice Midterm - Fall 2025 COMP CO859

21. Which wildcard character is used to represent zero or more characters in search
criteria?
A. underscore B. asterisk C. percent sign D. blank E. octothorpe

22. Which wildcard character (in search criteria) is used to represent a single character
only?
A. underscore B. asterisk C. percent sign D. blank

23. Which SQL keyword within the SELECT statement allows yo uto provide for pat-
tern recognition within character data?
A. AND B. IN C. BETWEEN D. LIKE

24. Which SQL keyword within the SELECT statement allows you to define an inclusive
range of accepted values?
A. AND B. IN C. BETWEEN D. LIKE

25. Which clause of the SELECT statement would be useful to sort a table of informa-
tion?
A. WHERE B. ORDER BY C. GROUP BY D. FROM

26. Which clause of the SELECT statment provides for a search condition?
A. WHERE B. ORDER BY C. GROUP BY D. FROM

27. Which SQL verb is used to add a new data record into a table?
A. INSERT B. SELECT C. APPEND D. ADDNEW

28. Which SQL verb is used to remove unwanted rows from a table?
A. INSERT B. SELECT C. DELETE D. UPDATE

29. Which SQL verb is used to alter the data values of specific columns that exist in
the table?
A. INSERT B. SELECT C. DELETE D. UPDATE

30. When an outer join is used, column data from tables where no matching rows were
found...
A. display as zero for numeric columns
B. display as blanks for character columns
C. are not displayed at all
D. display as NULL
E. no columns are displayed, because the query fails

Page 7
Database Theory - Practice Midterm - Fall 2025 COMP CO859

31. The domain of an attribute refers to the attribute’s

A. data type B. position in the table C. set of possible values D. cardinality
E. None of these

32. A CREATE VIEW command generates:

A. an additional instance of a physical table that replicates the data from
the original table or tables
B. a virtual table that is not permanently stored, but is generated as needed
C. a display of the column definitions for a table
D. a display of the constraints affecting a table
E. None of these

33. The term “metadata” refers to:

A. false or erroneous data
B. data i nthe data dictionary
C. foreign keys
D. data on relationships between entities
E. the data extracted by a SELECT statement

C. Short Answer ( / 2)
This section refers to the tables given on the last page of the test, which may be detached.
1. Write a SELECT query which extracts the sum of costs which are between 50
and 100 (inclusive) for each supplier, displaying the supp id and the sum of costs.
Column alias is not required.
..................................................................................
..................................................................................
..................................................................................

2. Write a SELECT query to extract all columns from the sales table for item id under
10 and qty over 5.
..................................................................................
..................................................................................
..................................................................................

3. Write a SELECT statement to extract the sales id, item id and descr for each record
in the sales table with “Light” anywhere in the descr sorted by descr.
..................................................................................
..................................................................................
..................................................................................

Page 8
Database Theory - Practice Midterm - Fall 2025 COMP CO859

4. Write an SQL query to extract the item id, descr and markup for each item from
an Ontario supplier, for items whose markup is greater than the average markup of
all items.
..................................................................................
..................................................................................
..................................................................................

5. Write the resulting set including column headings generated by:

SELECT supp_id, MAX(cost) AS max_cost
FROM items
GROUP BY supp_id
HAVING MAX(cost) > 25;

..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................

6. Write the resulting set, including column headings, generated by:

SELECT i.item_id, qty
FROM items i
JOIN sales s
ON i.item_id = s.item_id
WHERE stock < reorder

..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................

7. (1 point) Write the resulting set including column headings generated by:

Page 9
Database Theory - Practice Midterm - Fall 2025 COMP CO859

SELECT prov, COUNT(*) AS count

FROM suppliers
GROUP BY prov;

..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................

8. (1 point) Write the resulting set including column headings generated by:
SELECT descr, i.supp_id, tel
FROM items i
JOIN suppliers s
ON i.supp_id = s.supp_id
WHERE descr LIKE ’%Kit’;

..................................................................................
..................................................................................
..................................................................................
..................................................................................

9. Write the resulting set including column headings generated by:

SELECT item_id, stock
FROM items
WHERE cost = (SELECT MAX(cost) FROM items)

..................................................................................
..................................................................................
..................................................................................

10. Write the result set including column headings generated by:
SELECT i.item_id, cost, reorder, qty
FROM items i
JOIN sales s
ON i.item_id = s.item_id

Page 10
Database Theory - Practice Midterm - Fall 2025 COMP CO859

WHERE markup < 4 AND stock < 10

..................................................................................
..................................................................................
..................................................................................
..................................................................................

END OF TEST

Page 11
Database Theory - Practice Midterm - Fall 2025 COMP CO859

<this page has been intentionally left blank >

Page 12
Use these tables for questions 41 to 50.

items
item_i descr supp_id cos markup stock reorder
d t
1 Handlebars 1 10 6 4 6
2 Instant Awakener 1 20 6 2 4
3 Giant Kite Kit 1 30 6 7 4
4 Detonator 7 40 2 11 2
5 Adding Machine 7 20 2 1 3
6 Spikes Kit 4 2 4 17 12
7 Water Pistol 3 100 3 11 10
8 Jet Motor 3 100 3 17 10
9 Birdseed 3 100 3 4 5
10 Light Bed Springs 5 20 7 11 5
11 Trick Bone 5 20 7 8 5
12 Railroad Light Track 5 20 7 5 5
13 Pest Control Light 5 20 7 3 5
14 Hi-Speed Tonic 6 30 7 8 5
15 Boomerang 6 50 7 2 5

suppliers
supp_i company addr city prov postal contact tel
d
1 Orbit Tool 36 Waterford Winnipeg MB W8F3A6 Kelly Truton 204-560-9812
2 CageCo 43 Hillway Alliston ON S3A4D1 Al Stansill 705-435-8741
3 Allair Pumps 2765 Blankint Edmonton AB G6F5S2 Giselle Mack 403-335-3607
4 Trager 897 Franklin Peterboroug ON F5D4S8 Marg Granger 705-743-5692
h
5 Allamps 5613 Sumer Winnipeg MB W8G2A4 Ellen Szabo 204-562-3456
6 Allamps 56 Brightway Thunder Bay ON J8F6G7 Frank Thorne 807-344-2883
7 DeWalt Tools 4576 Industrial Kalamazoo MI 49004 Larry Gellman 517-654-1987
8 J.A. Henkels 1741-16B Glanmore Scarborough ON B7C3J8 Gordon Hall 416-785-1287
9 Fusion Glass 187 Dallard Iroquois Falls ON A3D3S5 Stacy Weman 705-232-9078
10 Fusion Glass 12 Dunford Berwick NS G6F7S8 Bill Strong 902-538-4571

sales
sales_i item_id qty
d
1 2 1
2 7 1 Result Set Example
3 9 4
4 4 1 choc_id name grams
5 2 3 1 Kit Kat 45
6 1 6 2 Caramilk 50
7 13 1 3 Oh Henry! 62
8 6 1
9 8 3
10 12 4
11 14 2