Name: 
 

IT 240 Practice Midterm Exam


True/False
Indicate whether the statement is true or false.
 

 1. 
Each column in a table should have a unique name, and entries in each column should all “match” this column name.
 

 2. 
In a relation, the order of rows is important.
 

 3. 
A relational database is a collection of relations.
 

 4. 
An unnormalized relation is a table that has more than one row.
 

 5. 
Instead of listing all the field names in the SELECT clause, you can use the & symbol.
 

 6. 
A simple condition includes the field name, a comparison operator, and either another field name or a value.
 

 7. 
You can combine values in character fields.
 

 8. 
The IN operator provides a concise way of phrasing certain conditions.
 

 9. 
A table that is in first normal form is better than one that is in second normal form.
 

 10. 
Functional dependencies can be determined by looking at sample data.
 

 11. 
In general, when converting a non-first normal form table to first normal form, the primary key will usually include the original primary key concatenated with the key to the repeating group.
 

 12. 
A table that is in first normal form may contain problems that will require you to restructure it.
 

Multiple Choice
Identify the choice that best completes the statement or answers the question.
 

 13. 
Based on the statement below, which of the following is the primary key?
   Rep (RepNum, LastName, FirstName, Street, City, State, Zip, Commission, Rate)
a.
RepNum
c.
FirstName
b.
LastName
d.
State
 

 14. 
The ____ key of a table is the column or collection of columns that uniquely identifies a given row in that table.
a.
primary
c.
foreign
b.
secondary
d.
minor
 

 15. 
Count, Sum, Avg, Max, and Min are a few of the built-in statistics or ____ functions that can be used in a query.
a.
accumulated
c.
primary
b.
allowed
d.
aggregate
 

 16. 
When used after the word SELECT, the ____ symbol indicates that you want to include all fields in the query results in the order in which you described them to the DBMS when you created the table.
a.
*
c.
#
b.
&
d.
$
 

 17. 
To use a wildcard, include the ____ operator in the WHERE clause.
a.
LIKE
c.
BETWEEN
b.
AS
d.
UNION
 

 18. 
In Access SQL, the ____ is used as a wildcard to represent any individual character.
a.
asterisks (*)
c.
underscore (_)
b.
percent sign (%)
d.
question mark (?)
 

 19. 
The ____ function calculates the number of entries in a table.
a.
COUNT
c.
MAX
b.
SUM
d.
MIN
 

 20. 
When a subquery is used, ____ is(are) evaluated first.
a.
the subquery query
b.
the outer query
c.
both a and b simultaneously
d.
whichever query is selected to execute first by the user
 
 
Customer ( CustomerNum, CustomerName, Street, City, State, Zip, Balance, CreditLimit, RepNum )
 

 21. 
Based on the code above, list the number, name, and balance of all customers with balances greater than or equal to $1,000 and less than or equal to $5,000.
a.
SELECT CustomerNum, CustomerName, Balance FROM Customer WHERE Balance BETWEEN 1000 AND 5000 ;
b.
SELECT CustomerNum, CustomerName, Balance FROM Customer WHERE Balance > 1000 ;
c.
SELECT CustomerName, Balance FROM Customer WHERE Balance BETWEEN 1000 AND 5000 ;
d.
SELECT CustomerNum, CustomerName FROM Customer WHERE Balance BETWEEN 1000 AND 5000 ;
 

 22. 
Based on the code above, list the number, name, street, and credit limit of all customers. Order the customers by name within descending credit limit.
a.
SELECT CustomerNum, CustomerName, Street, CreditLimit FROM Customer SORT BY CreditLimit DESC, CustomerName ;
b.
SELECT CustomerNum, CustomerName, Street, CreditLimit FROM Customer SORT BY CreditLimit ASC, CustomerName ;
c.
SELECT CustomerNum, CustomerName, Street, CreditLimit FROM Customer ORDER BY CreditLimit DESC, CustomerName ;
d.
SELECT CustomerNum, CustomerName, Street, CreditLimit FROM Customer ORDER BY CreditLimit ASC, CustomerName ;
 

 23. 
Based on the code above, for each sales rep, list the rep number, the number of customers assigned to the rep, and the average balance of the rep’s customers. Group the records by rep number and order the records by rep number.
a.
SELECT RepNum, AVG(Balance) FROM Customer GROUP BY RepNum ORDER BY RepNum ;
b.
SELECT RepNum, COUNT(*), AVG(Balance) FROM Part GROUP BY RepNum ORDER BY RepNum ;
c.
SELECT RepNum, COUNT(*), AVG(Balance) FROM Customer GROUP BY RepNum ORDER BY RepNum ;
d.
SELECT RepNum, COUNT(*) FROM Customer GROUP BY RepNum ORDER BY RepNum ;
 

 24. 
A table is in first normal form if it does not contain ____.
a.
repeating groups
c.
a primary key
b.
a foreign key
d.
alternate keys
 

 25. 
Another name for a nonkey column is a ____.
a.
nonkey attribute
c.
nonkey row
b.
key attribute
d.
key table
 

 26. 
Partial dependencies are dependencies on only a portion of the ____.
a.
nonkey column
c.
primary key
b.
first column or attribute
d.
index
 

 27. 
The conversion of an unnormalized table to first normal form requires the removal of ____.
a.
determinants
c.
nonkey columns
b.
interrelation constraints
d.
repeating groups
 

Completion
Complete each statement.
 

 28. 
Multiple entries in tables are often called ____________________.
 

 

 29. 
Conditions that data must satisfy are called ____________________.
 

 

 30. 
A(n) ____________________ field is a field that is the result of a calculation using one or more existing fields.
 

 

 31. 
SQL has built-in functions, which are also called ____________________ functions.
 

 

 32. 
____________________ means creating groups of records that share some common characteristics.
 

 

 33. 
The ____________________ of two tables is a table containing all rows that are in either the first table, the second table, or both.
 

 

 34. 
The two tables involved in a union must have the same structure, or be ____________________.
 

 

 35. 
A column B is ____________________ on another column A if each value for A in the database is associated with exactly one value of B.
 

 

 36. 
Second normal form represents an improvement over ____________________ normal form.
 

 



 
Check Your Work     Start Over