Part A - Multiple Choice
1. Most DBMS are referred to as _____________database management systems.
elemental
linked
hierarchical
relational
2. Data constitutes the building blocks of _____________.
information
processing
applications
programming
3. If a foreign key contains either matching values or nulls, the table(s) that make use of such a foreign key is/are said to exhibit __________ integrity.
referential
restrictive
secondary
redundant
4. In a "one-to-many" relationship, which constraint is usually added to the "many" table?
UNIQUE
PRIMARY KEY
FOREIGN KEY
NOT NULL
5. A ____ occurs when a relationship is improperly or incompletely identified and, therefore, is represented in a way that is not consistent with the real world.
surrogate primary keys
time-variant data
design trap
fan trap
6. All of the following are true about a prime attribute except_____________.
it is a key attribute
it is at least part of a key
it is not a key attribute
it may be part of a composite key
7. The conflicts between design efficiency, information requirements, and processing speed are often resolved through_____________.
conversion from 1NF to 2NF
conversion from 2NF to 3NF
compromises that include denormalization
conversion from 3NF to 4NF
8. When a constraint is created at the ______ level with the CREATE TABLE command, the constraint definition is simply included as part of the column definition.
table
column
database
row
Part B - Multiple Choice
1. In Oracle, the CONSTRAINT clause is used to ___________________.
create only referential constraints
define and name any constraint
create a short cut so the constraint does not have to be named
create only entity entegrity constraints
2. The SQL command that lets you list the table contents is
insert.
select.
commit.
update.
rollback.
3. The Crow's Foot model is more ___________-oriented than the Chen model.
object
user
implementation
processor
4. The ___________ model is both software and hardware dependent.
conceptual
logical
condensed
physical
5. Which of the following commands will add a new column named FIRSTORDERDATE to the CUSTOMERS table to store the date that the customer first placed an order with the company?
CREATE COLUMN firstorderdate, DATE TO customers;
ALTER TABLE customers ADD COLUMN firstorderdate DATE;
ALTER TABLE customers ADD firstorderdate DATE;
ALTER TABLE customers ADD (firstorderdate DATE);
6. When modifying the data characteristics of a column in Oracle, which of the following is correct?
The column size can be increased.
The size of the column can be decreased.
Changing the default value of a column will change the values of data already in a table.
If a NUMBER column is empty, its precision and scale cannot be changed.
7. Which of the followig SQL commands would alter the table DEVRY and add a foreign key that references the CITY table?
ALTER table DEVRY
ADD CONSTRAINT devry_studentcity_fk references city;
ALTER table DEVRY
ADD CONSTRAINT devry_studentcity_fk FOREIGN KEY references city;
ALTER table DEVRY
ADD CONSTRAINT devry_studentcity_fk FOREIGN KEY (student_city) references city;
ALTER table DEVRY
ADD CONSTRAINT FOREIGN KEY (student_city) references city;
8. Given a table named EMPLOYEE, the SQL command to sort records in a specific order would be:
SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE
FROM EMPLOYEE
LIST BY EMP_LNAME, EMP_FNAME, EMP_INITIAL;
SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE
FROM EMPLOYEE
ORDER BY EMP_LNAME, EMP_FNAME, EMP_INITIAL;
SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE
FROM EMPLOYEE
DISPLAY BY EMP_LNAME, EMP_FNAME, EMP_INITIAL;
SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE
FROM EMPLOYEE
SEQUENCE BY EMP_LNAME, EMP_FNAME, EMP_INITIAL;
Part C - Multiple Choice
1. Which of the following subqueries returns more than one row of results to the outer query?
multiple-column subquery
single-row subquery
multiple-row subquery
correlated subquery
2. Which of the following keywords can be used to join two tables that do not contain a commonly named and defined column?
NATURAL JOIN
JOIN...ON
JOIN...USING
JOIN...WHERE
3. Based upon the contents of the BOOKS table shown below, which of the following SQL statements will return the number of categories contained in the table?
SELECT COUNT(DISTINCT category) FROM books;
SELECT DISTINCT COUNT(category) FROM books;
SELECT DISTINCT (COUNT(category)) FROM books;
SELECT DISTINCT COUNT(category) FROM books;
4. The _____________ function can be used to display upper-case characters in lower-case.
CONVERT
MIN
LESS
LOWER
5. Which of the following statements will produce the value 21.1?
SELECT ROUND(21.12, 1) FROM dual;
SELECT ROUND(21.12, -1) FROM dual;
SELECT ROUND(21.12, 0) FROM dual;
SELECT ROUND(21.12, 0+) FROM dual;
6. The column to be updated by the UPDATE command is specified in the ______ clause.
UPDATE
SET
WHERE
COL
7. If the INSERT INTO command is used to insert data values that violate an existing constraint in a table, which of the following will happen.
Oracle will raise an error and you will have to go update the incorrect data.
Oracle will raise an error and the entire row will be rejected.
Oracle will raise an error and the invalid piece of data will be rejected.
Oracle will raise and error and all current transactions will be rolled back.
8. Which of the following commands is used to establish a user account?
CREATE NEW USER username
IDENTIFIED BY password;
CREATE USERNAME username
IDENTITY password;
CREATE USER username PASSWORD password;
CREATE USER username IDENTIFIED BY password;
9. Which command will grant a system privilege to a user?
GRANT systemprivilege USERNAME username
GRANT PRIVILEGE systemprivilege TO username
GRANT systemprivilege TO
username
GRANT systemprivilege ON
username.