TEST 2

Test: Final Exam Semester 1

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Top of FormSection 11

(Answer all questions in this section)

1.If a primary key is a set of columns, then one column must be null. True or False?Mark for Review(1) Points

True

False (*)

Correct

2.Entity integrity refers to:Mark for Review(1) Points

Tables always containing text data

Tables always containing numeric data

Columns having Primary Keys, Foreign Keys, Unique Keys, and Check constraints defined in the database.

Tables having Primary Keys, Foreign Keys, Unique Keys, and Check constraints defined in the database. (*)

Correct

3.A table must have a primary key. True or False?Mark for Review(1) Points

True

False (*)

Incorrect. Refer to Section 11 Lesson 1.

4.Foreign keys must be null. True or False?Mark for Review(1) Points

True

False (*)

Correct

5.The explanation below is a column integrity constraint. True or False?A column must contain only values consistent with the defined data format of the column.Mark for Review(1) Points

True (*)

False

Correct

6.An "Arc Implementation" can be done just like any other Relationship - you simply add the required Foreign Keys. True or False?Mark for Review(1) Points

True

False (*)

Correct

7.One-to-One relationships are transformed into Check Constraints in the tables created at either end of that relationship. True or False?Mark for Review(1) Points

True

False (*)

Correct

8.To resolve a many to many relationship in a physical model you create a/an ___________________?Mark for Review(1) Points

Unique key constraints

Intersection entity

Intersection table (*)

Two tables with Foreign key constraints between them

Correct

9.The transformation from an ER diagram to a physical design involves changing terminology. Primary Unique Identifiers in the ER diagram become __________ and relationships become ____________.Mark for Review(1) Points

Foreign keys, Primary keys

Primary keys, Foreign keys (*)

Foreign keys, mandatory business rules

Unique Keys, Primary keys

Correct

10.The transformation from an ER diagram to a physical design involves changing terminology. Secondary Unique Identifiers becomeMark for Review(1) Points

Columns

Tables

Unique Constraints (*)

Primary Key Constraints

Correct

Section 11

(Answer all questions in this section)

11.In a physical data model, a relationship is represented as a combination of: (Choose Two)Mark for Review(1) Points

(Choose all correct answers)

Column

Primary Key or Unique Key (*)

Check Constraint or Unique Key

Foreign Key (*)

Incorrect. Refer to Section 11 Lesson 2.

Section 12

(Answer all questions in this section)

12.What command will return data from the database to you?Mark for Review(1) Points

FETCH

GET

SELECT (*)

RETURN

Correct.

13.The DESCRIBE command returns all rows from a table. True or False?Mark for Review(1) Points

True

False (*)

Correct.

14.The _______ clause can be added to a SELECT statement to return a subset of the data.Mark for Review(1) Points

ANYWHERE

WHICH

WHERE (*)

EVERY

Correct.

15.During which phases of the System Development Life Cycle would you test the system before rolling it out to the users?Mark for Review(1) Points

Build and Transition

Strategy and Analysis

Design and Production

Transition and Production (*)

Correct.

16.Systems are always just rolled out as soon as the programming phase is finished. No further work is required once the development is finished. True or False?Mark for Review(1) Points

True

False (*)

Correct.

17.Once you have created a table, it is not possible to alter the definition of it. If you need to add a new column you must delete the table definition and create a new, correct table. True or False?Mark for Review(1) Points

True

False (*)

Correct.

18.The f_customers table contains the following data:IDNameAddressCityStateZip

1Cole Bee123 Main StreetOrlandoFL32838

2Zoe Twee1009 Oliver AvenueBostonMA02116

3Sandra Lee22 Main StreetTampaFL32444

If you run the following statement:DELETE FROM F_CUSTOMERS WHERE ID = '01-MAR-2000' and hire_date = '01-MAR-2000' and hire_date 25000 OR salary < 100000)AND team_id BETWEEN 1200 AND 1500ORDER BY team_id, salary;

SELECT last_name, first_name, team_id, salaryFROM playersWHERE salary BETWEEN 25000 AND 100000AND team_id BETWEEN 1200 AND 1500ORDER BY team_id, salary DESC;(*)

SELECT last_name, first_name, team_id, salaryFROM playersWHERE salary > 24999.99 AND salary < 100000AND team_id BETWEEN 1200 AND 1500ORDER BY team_id ASC, salary DESC;

SELECT last_name, first_name, team_id, salaryFROM playersWHERE salary BETWEEN 24999.99 AND 100000.01AND team_id BETWEEN 1200 AND 1500ORDER BY team_id DESC, salary DESC;

Correct.

44.The EMPLOYEES table contains these columns:EMPLOYEE_ID NUMBER(9) PKLAST_NAME VARCHAR2(25)FIRST_NAME VARCHAR2(25)DEPARTMENT_ID NUMBER(9)Compare these two SQL statements:1.SELECT DISTINCT department_id DEPT, last_name, first_nameFROM employeesORDER BY department_id;2.SELECT department_id DEPT, last_name, first_nameFROM employeesORDER BY DEPT;How will the results differ?Mark for Review(1) Points

One of the statements will return a syntax error.

One of the statements will eliminate all duplicate DEPARTMENT_ID values.

There is no difference in the result between the two statements.

The statements will sort on different column values. (*)

Incorrect! See Section 17 Lesson 3.

45.Evaluate this SQL statement:SELECT e.employee_id, e.last_name, e.first_name, m.manager_idFROM employees e, employees mORDER BY e.last_name, e.first_nameWHERE e.employee_id = m.manager_id;This statement fails when executed. Which change will correct the problem?Mark for Review(1) Points

Reorder the clauses in the query. (*)

Remove the table aliases in the WHERE clause.

Remove the table aliases in the ORDER BY clause.

Include a HAVING clause.

Correct.

46.Evaluate this SELECT statement:SELECT last_name, first_name, emailFROM employeesORDER BY email;If the EMAIL column contains null values, which statement is true?Mark for Review(1) Points

Null email values will be displayed first in the result.

Null email values will be displayed last in the result. (*)

Null email values will not be displayed in the result.

The result will not be sorted.

Correct.

47.Evaluate this SELECT statement:SELECT employee_id, last_name, first_name, salary 'Yearly Salary'FROM employeesWHERE salary IS NOT NULLORDER BY last_name, 3;Which clause contains an error?Mark for Review(1) Points

SELECT employee_id, last_name, first_name, salary Yearly Salary' (*)

FROM employees

WHERE salary IS NOT NULL

ORDER BY last_name, 3;

Correct.

48.You need to create a report to display all employees that were hired on or before January 1, 1996. The data should display in this format:EmployeeStart Date and Salary

14837 - Smith10-MAY-1992 / 5000

Which SELECT statement could you use?Mark for Review(1) Points

SELECT employee_id || - || last_name "Employee", hire_date || / || salary "Start Date and SalaryFROM employeesWHERE hire_date