1. A DBMS performs several important functions that guarantee the integrity and consistency of the data in the database. Which of the following is NOT one of those functions?
Multiuser access control
Data integrity management
Database access languages
End of month reports
2. Data constitutes the building blocks of _____________.
information
processing
applications
programming
3. A relationship is an association between_____________.
objects.
entities.
databases.
fields.
4. A field that consists of integer values is a _________ type field.
Date/Time
Yes/No
Memo
Numeric
5. Composite primary keys are particularly useful as identifiers of composite entities, where each primary key combination is allowed only once in the ____ relationship.
null
1:1
1:M
M:N
6. The extended entity relationship model (EERM) is sometimes referred to as the ____.
entity relationship model
enhanced entity relationship model
entity clustering relationship model
extended entity relationship doctrine
7. A table that has all key attributes defined, has no repeating groups, and all its attributes are dependent on the primary key, is said to be in_____________.
1NF.
2NF.
3NF.
4NF.
BCNF.
8. Which of the following SQL statements would select data from all of the rows and columns in the DEVRY table?
SELECT all FROM devry;
SELECT * FROM devry;
SELECT devry;
SHOW * FROM devry;
1. When creating a table, which of the following statements is NOT correct?
The schema of the table must be explicitly stated.
A user can have two tables with the same name, as long as the column names are different.
The column list must be enclosed in parentheses ( ).
Default values cannot be assigned to all columns in a table.
2. Which of the following SQL statements would you use to create a table named DEVRY?
CREATE Devry
(
Student_id NUMBER (12),
Student_name VARCHAR2 (15),
Student_city VARCHAR2 (12)
);
CREATE TABLE Devry
(
Student_id NUMBER (12)
Student_name VARCHAR2 (15)
Student_city VARCHAR2 (12)
);
CREATE TABLE Devry
(
Student_id NUMBER (12),
Student_name VARCHAR2 (15),
Student_city VARCHAR2 (12)
);
CREATE TABLE Devry
Student_id NUMBER (12),
Student_name VARCHAR2 (15),
Student_city VARCHAR2 (12);
3. The ____ constraint requires that a specific condition be met before a record can be added to a table.
UNIQUE
REFERENCE
CONDITION
CHECK
4. A FOREIGN KEY constraint can only reference a column in another table that has been assigned a(n) _________ constraint.
NOT NULL
UNIQUE
CHECK
PRIMARY KEY
5. Which of the following commands will increase the size of the CITY column in the CUSTOMERS table from 12 to 20 and increase size of the LASTNAME column from 10 to 14?
ALTER TABLE customers
MODIFY (city VARCHAR2(+8), lastname VARCHAR2(+4));
ALTER TABLE customers
MODIFY (city VARCHAR2(20), lastname VARCHAR2(14));
ALTER TABLE customers
MODIFY (city (+8), lastname (+4));
ALTER TABLE customers
MODIFY (city (20), lastname (14));
6. Which of the following keywords is used to remove a database table in Oracle?
DROP TABLE
ALTER TABLE...DROP
DELETE TABLE
TRUNCATE TABLE
7. When a column is added to a table, it is the _________ column in the table.
first
last
second
third
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;
1. (TCO 7) Which of the following can only be used to link tables that have a common column?
FULL OUTER JOIN
JOIN...ON
NATURAL JOIN
CROSS JOIN
2. To list a unique value, where the list will produce only a list of those values that are different from one another, you will write the command_____________.
SELECT ONLY V-CODE
FROM PRODUCT;
SELECT UNIQUE V-CODE
FROM PRODUCT;
SELECT DIFFERENT V-CODE
FROM PRODUCT;
SELECT DISTINCT V-CODE
FROM PRODUCT;
3. A(n) ___________ clause cannot include a group function.
WHERE
HAVING
ORDER BY
FROM
4. Which of the following format elements will display insignificant leading zeros?
$999
0009999
99.999
9,999.990
5. (TCO 8) Which of the following is an accurate statement?
When the LOWER function is used in a SELECT clause, it will automatically store the data in lower-case letters in the database table.
When the LOWER function is used in a SELECT clause, the function stays in affect for the remainder of that user's session.
When the LOWER function is used in a SELECT clause, the function only stays in affect for the duration of that SQL statement.
When the LOWER function is used in a SELECT clause, the function only affects the first column in the result set.
6. (TCO 9) The column to be updated by the UPDATE command is specified in the ______ clause.
UPDATE
SET
WHERE
COL
7. (TCO 9) Which of the following SQL statements would insert a line of data into the DEVRY table and supply a NULL value for the Student_city column?
INSERT into DEVRY VALUES
(012393, 'Jimmey Jones', 'NULL');
INSERT into DEVRY VALUES
(012393, 'Jimmey Jones', " ");
INSERT into DEVRY VALUES
(012393, 'Jimmey Jones', NULL);
INSERT into DEVRY VALUES
(012393, 'Jimmey Jones', ' ');
8. (TCO 10) A ____ is a uniquely identifiable object that allows a given person to log on to the database.
user
role
profile
manager
9. (TCO 10) Which of the following commands can be used to revoke system or object privileges previously granted to a user?
UNGRANT
REMOVE
REVOKE
DROP
1. (TCO 2) What is meant by logical independence?
2. (TCO 3) Explain in what two specific cases presented in the text composite primary keys are particularly useful.
3. (TCO 7) How are comparisons done on character string data in a queries WHERE clause, and why would this be especially useful in comparing certain attributes?
4. (TCO 8) Sally wants to query the EMP table and determine how many of the employees live in each of the cities the company has factories in. She writes the following query but it does not work. What is wrong with the way the query is constructed?
SELECT city, COUNT(emp_no) as "Number of Customers"
FROM emp
ORDER BY city
GROUP BY city;
5. (TCO 6) Write the SQL statement that will remove the database table EMPLOYEE.
1. (TCO 7) Write a join query that for every order placed on September 5, 1998, (use '05-SEP-1998') will list the order number and order date along with the customer number, last name, and first name of the customer who placed the order.
2. Write a query to determine the cost of the least expensive part in the part table? Format the returned price in dollars and cents using the correct function. Your result set should have only one amount in it.
3. Write a sub query that will list the part number, part description, and units on hand of all parts where the number of units on hand is greater than the average of units on hand for all parts.
4. Using the orders table, write a query that will list the order number, date, and customer number for every order that was made prior to September 5, 1998. Format the date in your result set to be in the same format as above (i.e., September 5, 1998).
5. Using a NATURAL JOIN method, write a query that will list the customer number, last name, and first name for every customer who currently has an order on file for an iron