FInal Exam Oracle Academy Database Design [PDF]

Citation preview

1. Which of the following is a valid reason for considering a Subtype Implementatio n?



Mark for Review (1) Points



The resulting table will reside in a single database and be used by just ONE user. The common access paths for the supertypes are different. Business functionality, business rules, access paths, and frequency of access are all very different between the subtypes. (*) Most of the relationships are at the supertype level. Correct 2. The transformation from an ER diagram to a physical design involves changing terminology. Secondary Unique Identifiers become



Mark for Review (1) Points



Columns Tables Unique Constraints (*) Primary Key Constraints Correct 3. Attributes become tables in a database. True or False?



Mark for Review (1) Points



True False (*) Correct 4. In an Oracle database, why would 1_TABLE not work as a table name?



Mark for Review (1) Points



The database does not understand all capital letters. There is no problem here. You can create a table called 1_TABLE. Object names must not start with a number. They must begin with a letter. (*) TABLE is a reserved word.



Correct 5. The text below is an example of what constraint type? If the number of BOOKS lent to a BORROWER in the LIBRARY exceeds 5, then we must send him a letter requesting the return of the BOOKS; this will require extra programming to enforce.



Mark for Review (1) Points



Entity integrity User-defined integrity (*) Column integrity Referential integrity Incorrect. Refer to Section 11 Lesson 1. 6. Foreign keys must be null. True or False?



Mark for Review (1) Points



True False (*) Correct 7. A foreign key cannot refer to a primary key in the same table. True or False?



Mark for Review (1) Points



True False (*) Correct 8. 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. (*) Incorrect. Refer to Section 11 Lesson 1.



9. A table must have at least one candidate key, as well as its primary key. True or False?



Mark for Review (1) Points



True False (*) Correct 10. It is possible to implement non-transferability via a simple Foreign Key Relationship. True or False?



Mark for Review (1) Points



True False (*) Correct 11. 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



Section 12 (Answer all questions in this section) 12. 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. 13. The f_customers table contains the following data:



ID Name



Address



City



State Zip



Mark for



1



Cole Bee



123 Main Street



Orlando FL



2



Zoe Twee



1009 Oliver Avenue Boston MA 02116



3



Sandra Lee 22 Main Street



Tampa



FL



32838



Review (1) Points



32444



If you run the following statement: DELETE FROM F_CUSTOMERS WHERE ID 20000; Which values would cause the logical condition to return TRUE?



DEPARTMENT_ID = 10 and SALARY = 20000 DEPARTMENT_ID = 20 and SALARY = 20000



Mark for Review (1) Points



DEPARTMENT_ID = null and SALARY = 20001 DEPARTMENT_ID = 10 and SALARY = 20001 (*) Correct. 34. Which of the following commands will display the last name concatenated with the job ID from the employees table, separated by a comma and space, and label the resulting column "Employee and Title"?



Mark for Review (1) Points



SELECT " last name" ||', '|| "job_id" + "Employee and Title" FROM employees; SELECT last_name||', '|| job_id "Employee and Title" FROM employees; (*) SELECT " last name" ||', '|| "job_id" + "Employee and Title" FROM emp; SELECT last_name||","|| job_id "Employee and Title" FROM employees; Correct. 35. Evaluate this SELECT statement: SELECT last_name, first_name, salary FROM employees;



Mark for Review (1) Points



How will the heading for the FIRST_NAME column appear in the display by default in Oracle Application Express?



The heading will display with the first character capitalized and centered. The heading will display with the first character capitalized and left justified. The heading will display as uppercase and centered. (*) The heading will display as uppercase and left justified. Incorrect. See Section 16 Lesson 2. 36. Which SELECT statement will display both unique and non-unique combinations of the MANAGER_ID and DEPARTMENT_ID values from the EMPLOYEES table?



Mark for Review (1) Points



SELECT manager_id, department_id DISTINCT FROM employees; SELECT manager_id, department_id FROM employees; (*) SELECT DISTINCT manager_id, department_id FROM employees; SELECT manager_id, DISTINCT department_id FROM employees; Correct.



Section 17 (Answer all questions in this section) 37. The ORDER BY clause always comes last. True or False?



Mark for Review (1) Points



True (*) False Correct. 38. Which of the following best describes the meaning of the LIKE operator?



Mark for Review (1) Points



Display rows based on a range of values. To test for values in a list. Match a character pattern. (*) To find Null values. Correct. 39. Which statement about the ORDER BY clause is true?



Mark for Review (1) Points



You can use a column alias in the ORDER BY clause. (*) The default sort order of the ORDER BY clause is descending. The ORDER BY clause can only contain columns that are included in the SELECT list. The ORDER BY clause should immediately precede the FROM clause in a SELECT statement Correct. 40. You need to change the default sort order of the ORDER BY clause so that the data is displayed in reverse alphabetical order. Which keyword should you include in the ORDER BY clause?



Mark for Review (1) Points



DESC (*) ASC SORT CHANGE



Correct. 41. Which stateme nt about the default sort order is true?



Mark for Review (1) Points



The lowest numeric values are displayed last. The earliest date values are displayed first. (*) Null values are displayed first. Character values are displayed in reverse alphabetical order. Correct. 42. Which statement about the logical operators is true?



Mark for Review (1) Points



The order of operator precedence is AND, OR, and NOT. The order of operator precedence is AND, NOT, and OR. The order of operator precedence is NOT, OR, and AND. The order of operator precedence is NOT, AND, and OR. (*) Correct. 43. Which SELECT statement should you use to limit the display of product information to those products with a price of less than 50?



Mark for Review (1) Points



SELECT product_id, product_name FROM products WHERE price < 50; (*) SELECT product_id, product_name FROM products HAVING price < 50; SELECT product_id, product_name FROM products WHERE price 30000 AND department_id = 10 OR email IS NOT NULL;



Mark for Review (1) Points



Which statement is true?



The OR condition will be evaluated before the AND condition. The AND condition will be evaluated before the OR condition. (*) The OR and AND conditions have the same precedence and will be evaluated from left to right The OR and AND conditions have the same precedence and will be evaluated from right to left Correct. 46. You query the database with this SQL statement: SELECT price FROM products WHERE price IN(1, 25, 50, 250) AND (price BETWEEN 25 AND 40 OR price > 50); Which two values could the statement return? (Choose two.)



(Choose all correct answers) 1 50 25 (*)



Mark for Review (1) Points



10 250 (*) Incorrect! See Section 17 Lesson 2. 47. 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:



Employee 14837 - Smith



Start Date and Salary 10-MAY-1992 / 5000



Mark for Review (1) Points



Which SELECT statement could you use?



SELECT employee_id || - || last_name "Employee", hire_date || / || salary "Start Date and Salary FROM employees WHERE hire_date