Answers Final Exam Semester 2 Part 1 2013-2015 Oracle Academy English Database Programming with SQL - Student

Here you have Final Exam Semester 2 Part 1 at Oracle Academy. I hope this will help you.

PS: The test has just 50 question, but here you have more possible question.

1.            Evaluate this CREATE TABLE statement:

CREATE TABLE line_item ( line_item_id NUMBER(9), order_id NUMBER(9), product_id NUMBER(9));

You are a member of the SYSDBA role, but are logged in under your own schema. You issue this CREATE TABLE statement. Which statement is true?                                                                         

                                              

·         You created the LINE_ITEM table in the public schema.              

·         You created the LINE_ITEM table in the SYS schema.   

·         You created the table in your schema. (*)                         

·         You created the table in the SYSDBA schema.

                              

[Incorrect]                         Incorrect. Refer to Section 8 Lesson 1.

                                                                             

2.            You want to create a database table that will contain information regarding products that your company released during 2001. Which name can you assign to the table that you create?                 Mark for Review

(1) Points

                                                                             

·         2001_PRODUCTS           

·         PRODUCTS_2001 (*)    

·         PRODUCTS_(2001)

·         PRODUCTS--2001

                              

[Correct]                             Correct

               

3.            You want to create a table named TRAVEL that is a child of the EMPLOYEES table. Which of the following statements should you issue?

                                              

·         CREATE TABLE travel

(destination_id primary key, departure_date date, return_date date, emp_id REFERENCES employees (emp_id));

·         CREATE TABLE travel

(destination_id number primary key, departure_date date, return_date date, t.emp_id = e.emp_id);

·         CREATE TABLE travel

(destination_id number primary key, departure_date date, return_date date, JOIN emp_id number(10) ON employees (emp_id));

·         CREATE TABLE travel

(destination_id number primary key, departure_date date, return_date date, emp_id number(10) REFERENCES employees (emp_id));

(*)

                              

[Incorrect]                         Incorrect. Refer to Section 8 Lesson 1.

                              

               

4.            You are creating the EMPLOYEES table. This table should contain the COMMISSION_PCT column and use a value of 10 percent if no commission value is provided when a record is inserted. Which line should you include in the CREATE TABLE statement to accomplish this task?       

                              

·         commission_pct NUMBER(4,2) DEFAULT 0.10 (*)                          

·         commission_pct NUMBER(4,2) DEFAULT = 0.10                              

·         commission_pct NUMBER(4,2) IS DEFAULT 0.10                             

·         commission_pct NUMBER(4,2) (DEFAULT, 0.10)

                                              

[Incorrect]                         Incorrect. Refer to Section 8 Lesson 1.

                                              

5.            Which column name is valid?    

               

·         1NUMBER

·         NUMBER

·         NUMBER_1$ (*)

·         1_NUMBER#

               

[Incorrect]                         Incorrect. Refer to Section 8 Lesson 1.

                                                              

6.            Which statement about table and column names is true?          

               

·         Table and column names must begin with a letter. (*)

·         Table and column names can begin with a letter or a number.

·         Table and column names cannot include special characters.

·         If any character other than letters or numbers is used in a table or column name, the name must be enclosed in double quotation marks.

               

[Incorrect]                         Incorrect. Refer to Section 8 Lesson 1.

               

7.            To do a logical delete of a column without the performance penalty of rewriting all the table datablocks, you can issue the following command:

·         Alter table modify column

·         Alter table drop column

·         Alter table set unused (*)

·         Drop column 'columname'

                                                              

[Incorrect]                         Incorrect. Refer to Section 8 Lesson 3.

               

8.            Which command could you use to quickly remove all data from the rows in a table without deleting the table itself?

               

·         ALTER TABLE

·         DROP TABLE

·         MODIFY

·         TRUNCATE TABLE (*)

               

[Correct]                             Correct

               

                                                                             

9.            Which statement about decreasing the width of a column is true?

                                                                                             

·         When a character column contains data, you cannot decrease the width of the column.           

·         When a character column contains data, you can decrease the width of the column without any restrictions.

·         When a character column contains data, you can decrease the width of the column if the existing data does not violate the new size. (*)

·         You cannot decrease the width of a character column unless the table in which the column resides is empty.

               

[Correct]                             Correct

               

10.          Evaluate the structure of the EMPLOYEE table:

EMPLOYEE_ID NUMBER(9)

LAST_NAME VARCHAR2(25)

FIRST_NAME VARCHAR2(25)

DEPARTMENT_ID NUMBER(9)

MANAGER_ID NUMBER(9)

SALARY NUMBER(7,2)

Which statement should you use to increase the LAST_NAME column length to 35 if the column currently contains 200 records?

·         ALTER employee TABLE

ALTER COLUMN (last_name VARCHAR2(35));

·         ALTER TABLE employee

RENAME last_name VARCHAR2(35);

·         ALTER TABLE employee

MODIFY (last_name VARCHAR2(35));

(*)

·         You CANNOT increase the width of the LAST_NAME column.

                              

[Correct]                             Correct

11.          Evaluate this statement:

Which statement about this TRUNCATE TABLE statement is true?

·         You can produce the same results by issuing the 'DROP TABLE employee' statement.

·         You can issue this statement to retain the structure of the employees table. (*)

·         You can reverse this statement by issuing the ROLLBACK statement.

·         You can produce the same results by issuing the 'DELETE employees' statement.

                              

[Incorrect]                         Incorrect. Refer to Section 8 Lesson 3.

               

                                              

12.          The previous administrator created a table named CONTACTS, which contains outdated data. You want to remove the table and its data from the database. Which statement should you issue?

               

·         DROP TABLE (*)

·         DELETE

·         TRUNCATE TABLE

·         ALTER TABLE

                                                                                                                                            

[Incorrect]                         Incorrect. Refer to Section 8 Lesson 3.

               

13.          The TEAMS table contains these columns:

TEAM_ID NUMBER(4) Primary Key

TEAM_NAME VARCHAR2(20)

MGR_ID NUMBER(9)

The TEAMS table is currently empty. You need to allow users to include text characters in the manager identification values. Which statement should you use to implement this?                                                                                                                 

·         ALTER teams

MODIFY (mgr_id VARCHAR2(15));

·         ALTER TABLE teams

MODIFY (mgr_id VARCHAR2(15));

(*)

·         ALTER TABLE teams

REPLACE (mgr_id VARCHAR2(15));

·         ALTER teams TABLE

MODIFY COLUMN (mgr_id VARCHAR2(15));

               

You CANNOT modify the data type of the MGR_ID column.

[Incorrect]                         Incorrect. Refer to Section 8 Lesson 3.

               

14.          You need to remove all the data in the SCHEDULE table, the structure of the table, and the indexes associated with the table. Which statement should you use?   

               

·         DROP TABLE (*)

·         TRUNCATE TABLE

·         ALTER TABLE

·         DELETE TABLE

               

[Incorrect]                         Incorrect. Refer to Section 8 Lesson 3.

               

15.          Examine the structure of the DONATIONS table.

DONATIONS:

PLEDGE_ID NUMBER

DONOR_ID NUMBER

PLEDGE_DT DATE

AMOUNT_PLEDGED NUMBER (7,2)

AMOUNT_PAID NUMBER (7,2)

PAYMENT_DT DATE

You need to reduce the precision of the AMOUNT_PLEDGED column to 5 with a scale of 2 and ensure that when inserting a row into the DONATIONS table without a value for the AMOUNT_PLEDGED column, a price of $10.00 will automatically be inserted. The DONATIONS table currently contains NO records. Which statement is true?

                                              

·         You CANNOT decrease the width of the AMOUNT_PLEDGED column.

·         Both changes can be accomplished with one ALTER TABLE statement. (*)

·         You must drop and recreate the DONATIONS table to achieve these results.

·         You must use the ADD OR REPLACE option to achieve these results.

                                                              

[Incorrect]                         Incorrect. Refer to Section 8 Lesson 3.

               

16.          You need to change the name of the EMPLOYEES table to the EMP table. Which statement should you use?

               

·         RENAME employees emp;

·         RENAME employees TO emp; (*)

·         ALTER TABLE employees TO emp;

·         ALTER TABLE employees RENAME TO emp;

                                                              

[Incorrect]                         Incorrect. Refer to Section 8 Lesson 3.

               

17.          Your supervisor has asked you to modify the AMOUNT column in the ORDERS table. He wants the column to be configured to accept a default value of 250. The table contains data that you need to keep. Which statement should you issue to accomplish this task?

                                              

·         ALTER TABLE orders

CHANGE DATATYPE amount TO DEFAULT 250;

·         ALTER TABLE orders

MODIFY (amount DEFAULT 250);

(*)

·         DROP TABLE orders;

CREATE TABLE orders

(orderno varchar2(5) CONSTRAINT pk_orders_01 PRIMARY KEY,

customerid varchar2(5) REFERENCES customers (customerid),

orderdate date,

amount DEFAULT 250);

·         DELETE TABLE orders;

CREATE TABLE orders

(orderno varchar2(5) CONSTRAINT pk_orders_01 PRIMARY KEY,

customerid varchar2(5) REFERENCES customers (customerid),

orderdate date,

amount DEFAULT 250)

[Incorrect]                         Incorrect. Refer to Section 8 Lesson 3.

               

                                                                             

18.          The ELEMENTS column is defined as:

NUMBER(6,4)

How many digits to the right of the decimal point are allowed for the ELEMENTS column?

               

·         Zero

·         Two

·         Four (*)

·         Six

               

[Correct]                             Correct

               

19.          Which data types stores variable-length character data? Select two.

(Choose all correct answers)    

                                                                                             

·         CHAR   

·         NCHAR

·         CLOB (*)

·         VARCHAR2 (*)

               

[Correct]                             Correct

               

20.          The SPEED_TIME column should store a fractional second value.

Which data type should you use?                          

                                              

·         DATE

·         DATETIME

·         TIMESTAMP (*)

·         INTERVAL DAY TO SECOND

[Incorrect]                         Incorrect. Refer to Section 8 Lesson 2.

                                                                                             

21.          Which statement about data types is true?

·         The BFILE data type stores character data up to four gigabytes in the database.            

·         The TIMESTAMP data type is a character data type.

·         The VARCHAR2 data type should be used for fixed-length character data.

·         The CHAR data type should be defined with a size that is not too large for the data it contains (or could contain) to save space in the database. (*)

[Correct]                             Correct

               

22.          A table has a column: RESPONSE_TIME. This is used to store the difference between the time the problem was reported and the time the problem was resolved. Data in the RESPONSE_TIME column needs to be stored in days, hours, minutes and seconds. Which data type should you use?

                                                                             

·         DATETIME

·         TIMESTAMP

·         INTERVAL YEAR TO MONTH

·         INTERVAL DAY TO SECOND (*)

                                                              

[Correct]                             Correct

               

23.          You are designing a table for the Human Resources department. This table must include a column that contains each employee's hire date. Which data type should you specify for this column?

                              

·         CHAR

·         DATE (*)

·         TIMESTAMP

·         INTERVAL YEAR TO MONTH

                                                              

[Incorrect]                         Incorrect. Refer to Section 8 Lesson 2.

               

24.          To store time with fractions of seconds, which datatype should be used for a table column?

                                                              

·         DATE

·         INTERVAL YEAR TO MONTH

·         TIMESTAMP (*)

·         INTERVAL DAY TO SECOND

                                                              

[Incorrect]                         Incorrect. Refer to Section 8 Lesson 2.

25.          You need to ensure that the LAST_NAME column only contains certain character values. No numbers or special characters are allowed.

Which type of constraint should you define on the LAST_NAME column?

·         CHECK (*)

·         UNIQUE

·         NOT NULL

·         PRIMARY KEY

                                                              

[Correct]                             Correct

               

26.          You need to add a NOT NULL constraint to the COST column in the PART table. Which statement should you use to complete this task?

·         ALTER TABLE part

MODIFY (cost part_cost_nn NOT NULL);

·         ALTER TABLE part

MODIFY (cost CONSTRAINT part_cost_nn NOT NULL);

(*)

·         ALTER TABLE part

MODIFY COLUMN (cost part_cost_nn NOT NULL);

·         ALTER TABLE part

ADD (cost CONSTRAINT part_cost_nn NOT NULL);

                                                              

[Correct]                             Correct

               

27.          Primary Key, Foreign Key, Unique Key, and Check Constraints can be added at which two levels? (Choose two)                  (Choose all correct answers)   

               

·         Null Field

·         Table (*)

·         Row

·         Dictionary

·         Column (*)

               

[Incorrect]                         Incorrect. Refer to Section 10 Lesson 1.

                              

28.          Which two statements about NOT NULL constraints are true? (Choose two)

(Choose all correct answers)    

               

·         The Oracle Server creates a name for an unnamed NOT NULL constraint. (*)

·         A NOT NULL constraint can be defined at either the table or column level.

·         The NOT NULL constraint requires that every value in a column be unique.

·         Columns with a NOT NULL constraint can contain null values by default.

·         You CANNOT add a NOT NULL constraint to an existing column using the ALTER TABLE ADD CONSTRAINT statement. (*)

[Incorrect]                         Incorrect. Refer to Section 10 Lesson 1.

               

29.          You need to ensure that the LAST_NAME column does not contain null values. Which type of constraint should you define on the LAST_NAME column?

                              

·         CHECK

·         UNIQUE

·         NOT NULL (*)

·         PRIMARY KEY                  

                                                              

[Incorrect]                         Incorrect. Refer to Section 10 Lesson 1.

               

30.          What is the highest number of NOT NULL constraints you can have on a table?

                              

·         5

·         10

·         3

·         You can have as many NOT NULL constraints as you have columns in your table. (*)

 [Correct]                            Correct

               

31.          Which constraint can only be created at the column level?

                                                                              

·         NOT NULL (*)

·         FOREIGN KEY

·         UNIQUE

·         CHECK

               

[Incorrect]                         Incorrect. Refer to Section 10 Lesson 1.

               

32.          You need to display the names and definitions of constraints only in your schema. Which data dictionary view should you query?

                                                                             

·         DBA_CONSTRAINTS

·         USER_CONSTRAINTS (*)

·         ALL_CONS_COLUMNS

·         USER_CONS_COLUMNS

                                                              

[Incorrect]                         Incorrect. Refer to Section 10 Lesson 3.

               

33.          You want to disable the FOREIGN KEY constraint that is defined in the EMPLOYEES table on the DEPARTMENT_ID column. The constraint is referenced by the name FK_DEPT_ID_01. Which statement should you issue?

               

·         ALTER TABLE employees

DISABLE 'fk_dept_id_01';

·         ALTER TABLE employees

DISABLE CONSTRAINT 'fk_dept_id_01';

·         ALTER TABLE employees

DISABLE fk_dept_id_01;

·         ALTER TABLE employees

DISABLE CONSTRAINT fk_dept_id_01;

(*)

                              

[Incorrect]                         Incorrect. Refer to Section 10 Lesson 3.

               

34.          You need to add a PRIMARY KEY constraint on the EMP_ID column of the EMPLOYEES table. Which ALTER TABLE statement should you use?      

·         ALTER TABLE employees

ADD CONSTRAINT PRIMARY KEY (emp_id);

·         ALTER TABLE employees

ADD CONSTRAINT emp_emp_id_pk PRIMARY KEY(emp_id); (*)

·         ALTER TABLE employees

MODIFY emp_id PRIMARY KEY;

·         ALTER TABLE employees

MODIFY CONSTRAINT PRIMARY KEY (emp_id);

               

[Incorrect]                         Incorrect. Refer to Section 10 Lesson 3.

               

                              

                                              

35.          What is the syntax for removing a PRIMARY KEY constraint and all its dependent constraints?

                                                                                             

·         ALTER TABLE table_name

DROP CONSTRAINT constraint_name CASCADE;

(*)        

·         ALTER TABLE table_name

DROP CONSTRAINT FOREIGN KEY CASCADE;

·         DROP CONSTRAINT table_name (constraint_name);

·         ALTER TABLE table_name

DROP CONSTRAINT constraint_name;

 [Incorrect]                        Incorrect. Refer to Section 10 Lesson 3.

               

36.          Which of the following would definitely cause an integrity constraint error?

·         Using a subquery in an INSERT statement.

·         Using the MERGE statement to conditionally insert or update rows.

·         Using the DELETE command on a row that contains a primary key with a dependent foreign key declared without either an ON DELETE CASCADE or ON DELETE SET NULL. (*)

·         Using the UPDATE command on rows based in another table.                

                                                              

[Correct]                             Correct

37.          You need to add a NOT NULL constraint to the EMAIL column in the EMPLOYEES table. Which clause should you use?     

                                              

·         ADD

·         CHANGE

·         MODIFY (*)

·         DISABLE

               

[Incorrect]                         Incorrect. Refer to Section 10 Lesson 3.

               

38.          The LINE_ITEM table contains these columns:

LINE_ITEM_ID NUMBER PRIMARY KEY

PRODUCT_ID NUMBER(9) FOREIGN KEY references the ID column of the PRODUCT table

QUANTITY NUMBER(9)

UNIT_PRICE NUMBER(5,2)

You need to disable the FOREIGN KEY constraint. Which statement should you use?                  

·         ALTER TABLE line_item

DISABLE CONSTRAINT product_id_fk;

(*)        

·         ALTER TABLE line_item

DROP CONSTRAINT product_id_fk;      

·         ALTER TABLE line_item

ENABLE CONSTRAINT product_id_fk;

·         ALTER TABLE line_item

DELETE CONSTRAINT product_id_fk;    

[Incorrect]                         Incorrect. Refer to Section 10 Lesson 3.

39.          The PO_DETAILS table contains these columns:

PO_NUM NUMBER NOT NULL, Primary Key

PO_LINE_ID NUMBER NOT NULL, Primary Key

PRODUCT_ID NUMBER Foreign Key to PRODUCT_ID column of the PRODUCTS table

QUANTITY NUMBER

UNIT_PRICE NUMBER(5,2)

Evaluate this statement:

ALTER TABLE po_details

DISABLE CONSTRAINT product_id_pk CASCADE;

For which task would you issue this statement?                                                                            

                                              

·         To create a new PRIMARY KEY constraint on the PO_NUM column

·         To drop and recreate the PRIMARY KEY constraint on the PO_NUM column

·         To disable the PRIMARY KEY and any FOREIGN KEY constraints that are dependent on the PO_NUM column (*)

·         To disable the constraint on the PO_NUM column while creating a PRIMARY KEY index

               

 [Correct]                            Correct

               

40.          You disabled the EMPLOYEE_ID_PK PRIMARY KEY constraint on the ID column in the EMPLOYEES table and imported 100 records. You need to enable the constraint and verify that the new and existing ID column values do not violate the PRIMARY KEY constraint. Evaluate this statement:

ALTER TABLE employees

ENABLE employee_id_pk;

Which statement is true?

               

·         The statement will achieve the desired result.

·         The statement will execute, but will ensure that the new ID values are unique.

·         The statement will execute, but will not verify that the existing values are unique.

·         The statement will NOT execute because it contains a syntax error. (*)                                                            

[Incorrect]                         Incorrect. Refer to Section 10 Lesson 3.

                                                                                             

41.          You need to remove the EMP_FK_DEPT constraint from the EMPLOYEE table in your schema. Which statement should you use?

·         DROP CONSTRAINT EMP_FK_DEPT FROM employees;

·         DELETE CONSTRAINT EMP_FK_DEPT FROM employees;

·         ALTER TABLE employees DROP CONSTRAINT EMP_FK_DEPT; (*)

·         ALTER TABLE employees REMOVE CONSTRAINT EMP_FK_DEPT;

               

[Incorrect]                         Incorrect. Refer to Section 10 Lesson 3.

               

                                                                             

42.          Which clause could you use to ensure that cost values are greater than 1.00?

                                              

·         CONSTRAINT CHECK cost > 1.00

·         CONSTRAINT part_cost_ck CHECK (cost > 1.00) (*)

·         CHECK CONSTRAINT part_cost_ck (cost > 1.00)

·         CONSTRAINT CHECK part_cost_ck (cost > 1.00)

               

[Incorrect]                         Incorrect. Refer to Section 10 Lesson 2.

                                                                             

43.          Evaluate the structure of the DONATIONS table.

DONATIONS:

PLEDGE_ID NUMBER NOT NULL, Primary Key

DONOR_ID NUMBER Foreign key to DONOR_ID column of DONORS table

PLEDGE_DT DATE

AMOUNT_PLEDGED NUMBER (7,2)

AMOUNT_PAID NUMBER (7,2)

PAYMENT_DT DATE

Which CREATE TABLE statement should you use to create the DONATIONS table?

               

·         CREATE TABLE donations

(pledge_id NUMBER PRIMARY KEY,

donor_id NUMBER FOREIGN KEY REFERENCES donors(donor_id),

pledge_date DATE,

amount_pledged NUMBER,

amount_paid NUMBER,

payment_dt DATE);

·         CREATE TABLE donations

(pledge_id NUMBER PRIMARY KEY NOT NULL,

donor_id NUMBER FOREIGN KEY donors(donor_id),

pledge_date DATE,

amount_pledged NUMBER(7,2),

amount_paid NUMBER(7,2),

payment_dt DATE);

·         CREATE TABLE donations

pledge_id NUMBER PRIMARY KEY,

donor_id NUMBER FOREIGN KEY donor_id_fk REFERENCES donors(donor_id),

pledge_date DATE,

amount_pledged NUMBER(7,2),

amount_paid NUMBER(7,2),

payment_dt DATE;

·         CREATE TABLE donations

(pledge_id NUMBER PRIMARY KEY,

donor_id NUMBER CONSTRAINT donor_id_fk REFERENCES donors(donor_id),

pledge_date DATE,

amount_pledged NUMBER(7,2),

amount_paid NUMBER(7,2),

payment_dt DATE);

(*)

                [Incorrect]                         Incorrect. Refer to Section 10 Lesson 2.

               

                                                                             

44.          When creating a referential constraint, which keyword(s) identifies the table and column in the parent table?                

·         FOREIGN KEY

·         REFERENCES (*)

·         ON DELETE CASCADE

·         ON DELETE SET NULL

 [Incorrect]                        Incorrect. Refer to Section 10 Lesson 2.

               

45.          You need to create the PROJECT_HIST table. The table must meet these requirements:

    The table must contain the EMPLOYEE_ID and TASKED_HOURS columns for numeric data.

    The table must contain the START_DATE and END_DATE column for date values.

    The table must contain the HOURLY_RATE and PROJECT_COST columns for numeric data with precision and scale of 5,2 and 10,2 respectively.

    The table must have a composite primary key on the EMPLOYEE_ID and START_DATE columns.

Evaluate this CREATE TABLE statement:

CREATE TABLE project_hist

( employee_id NUMBER,

start_date DATE,

end_date DATE,

tasked_hours NUMBER,

hourly_rate NUMBER(5,2),

project_cost NUMBER(10,2),

CONSTRAINT project_hist_pk PRIMARY KEY(employee_id, start_date));

How many of the requirements does the CREATE TABLE statement satisfy?

·         None of the four requirements

·         All four of the requirements (*)

·         Only three of the requirements

·         Only two of the requirements

               

[Correct]                             Correct

46.          Evaluate this CREATE TABLE statement:

    CREATE TABLE part(

    part_id NUMBER,

    part_name VARCHAR2(25),

    manufacturer_id NUMBER(9),

    retail_price NUMBER(7,2) NOT NULL,

    CONSTRAINT part_id_pk PRIMARY KEY(part_id),

    CONSTRAINT cost_nn NOT NULL(cost),

    CONSTRAINT FOREIGN KEY (manufacturer_id) REFERENCES manufacturer(id));

Which line will cause an error?

·         5            

·         6            

·         7 (*)

·         8

 [Incorrect]                                Incorrect. Refer to Section 10 Lesson 2.

               

47.          Which constraint type enforces uniqueness?   

                CHECK

                FOREIGN KEY

                PRIMARY KEY (*)

                NOT NULL

               

[Correct]                             Correct

               

                                                                             

48.          Which of the following best describes the function of a CHECK constraint?

                                              

·         A CHECK constraint enforces referential data integrity.

·         A CHECK constraint defines restrictions on the values that can be entered in a column or combination of columns. (*)

·         A CHECK constraint enforces uniqueness of the values that can be entered in a column or combination of columns.

·         A CHECK constraint is created automatically when a PRIMARY KEY constraint is created             

                                                              

[Incorrect]                         Incorrect. Refer to Section 10 Lesson 2.

               

49.          When creating the EMPLOYEES table, which clause could you use to ensure that salary values are 1000.00 or more?

               

·         CONSTRAINT CHECK salary > 1000

·         CHECK CONSTRAINT (salary > 1000)

·         CONSTRAINT employee_salary_min CHECK salary > 1000

·         CONSTRAINT employee_salary_min CHECK (salary >= 1000) (*)

·         CHECK CONSTRAINT employee_salary_min (salary > 1000)

 [Incorrect]                        Incorrect. Refer to Section 10 Lesson 2.

               

                                                                             

50.          What must exist on the Parent table before Oracle will allow you to create a FOREIGN KEY constraint from a Child table?

·         A FOREIGN KEY constraint allows the constrained column to contain values that exist in the primary key column of the parent table.

·         A PRIMARY or UNIQUE KEY constraint must exist on the Parent table. (*)

·         An index must exist on the Parent table

·         A CHECK constraint must exist on the Parent table.

                                                              

[Correct]                             Correct

51.            Comments on tables and columns can be stored for documentation by:

                                              

·         Embedding /* comment */ within the definition of the table.

·         Using the ALTER TABLE CREATE COMMENT syntax

·         Using the COMMENT ON TABLE or COMMENT on COLUMN (*)

·         Using an UPDATE statement on the USER_COMMENTS table

[Incorrect]                         Incorrect. Refer to Section 8 Lesson 3.

52.            You need to truncate the EMPLOYEES table. The EMPLOYEES table is not in your schema. Which privilege must you have to truncate the table?      

               

·         The DROP ANY TABLE system privilege (*)

·         The TRUNCATE ANY TABLE system privilege

·         The CREATE ANY TABLE system privilege

·         The ALTER ANY TABLE system privilege

               

[Incorrect]                         Incorrect. Refer to Section 8 Lesson 3.

53.            You want to issue the following command on a database that includes your company's inventory information:

ALTER TABLE products SET UNUSED COLUMN color;

What will be the result of issuing this command?

·         The column named COLOR in the table named PRODUCTS will be assigned default values.

·         The column named COLOR in the table named PRODUCTS will be created.

·         The column named COLOR in the table named PRODUCTS will be deleted.

·         The column named COLOR in the table named PRODUCTS will not be returned in subsequent reads of the table by Oracle, as it has been deleted logically. (*)

               

[Correct]                             Correct

54.            Evaluate this statement:

ALTER TABLE inventory

MODIFY backorder_amount NUMBER(8,2);

Which task will this statement accomplish?

·         Alters the definition of the BACKORDER_AMOUNT column to NUMBER(8 2)

·         Alters the definition of the BACKORDER_AMOUNT column to NUMBER

·         Alters the definition of the BACKORDER_AMOUNT column to NUMBER(2,8)

·         Alters the definition of the BACKORDER_AMOUNT column to NUMBER(8.2)

·         Changes the definition of the BACKORDER_AMOUNT column to NUMBER(8,2) (*)

               

[Correct]                             Correct

55.            Evaluate this statement:

ALTER TABLE employees SET UNUSED (fax);

Which task will this statement accomplish?

·         Deletes the FAX column

·         Frees the disk space used by the data in the FAX column

·         Prevents data in the FAX column from being displayed, by performing a logical drop of the column (*)

·         Prevents a new FAX column from being added to the EMPLOYEES table

 [Correct]                            Correct

56.            The PLAYERS table contains these columns:

PLAYER_ID NUMBER(9) PRIMARY KEY

LAST_NAME VARCHAR2(20)

FIRST_NAME VARCHAR2(20)

TEAM_ID NUMBER(4)

SALARY NUMBER(9,2)

Which statement should you use to decrease the width of the FIRST_NAME column to 10 if the column currently contains 1500 records, but none are longer than 10 bytes or characters?                                             

·         ALTER players TABLE

MODIFY COLUMN first_name VARCHAR2(10);

·         ALTER players TABLE

MODIFY COLUMN (first_name VARCHAR2(10));

·         ALTER TABLE players

RENAME first_name VARCHAR2(10);

·         ALTER TABLE players

MODIFY (first_name VARCHAR2(10));

(*)                                                       

                                                              

[Incorrect]                         Incorrect. Refer to Section 8 Lesson 3.

57.            Which statement about a column is NOT true?               

               

·         You can increase the width of a CHAR column.

·         You can modify the data type of a column if the column contains non-null data. (*)

·         You can convert a CHAR data type column to the VARCHAR2 data type.

·         You can convert a DATE data type column to a VARCHAR2 column.

                                                              

[Correct]                             Correct

58.          Evaluate this CREATE TABLE statement:

CREATE TABLE sales

( sales_id NUMBER(9),

customer_id NUMBER(9),

employee_id NUMBER(9),

description VARCHAR2(30),

sale_date TIMESTAMP WITH LOCAL TIME ZONE DEFAULT SYSDATE,

sale_amount NUMBER(7,2));

Which business requirement will this statement accomplish?

                                              

·         Sales identification values could be either numbers or characters, or a combination of both.

·         All employee identification values are only 6 digits so the column should be variable in length.

·         Description values can range from 0 to 30 characters so the column should be fixed in length.

·         Today's date should be used if no value is provided for the sale date. (*)

                                                              

[Incorrect]                         Incorrect. Refer to Section 8 Lesson 2.

               

59.          You need to store the SEASONAL data in months and years. Which data type should you use?

               

·         DATE

·         TIMESTAMP

·         INTERVAL YEAR TO MONTH (*)

·         INTERVAL DAY TO SECOND

               

[Incorrect]                         Incorrect. Refer to Section 8 Lesson 2.

60.          The TIMESTAMP data type allows what?            

               

·         Time to be stored as an interval of years and months.

·         Time to be stored as a date with fractional seconds. (*)

·         Time to be stored as an interval of days to hours, minutes and seconds.

·         None of the above.

               

[Incorrect]                         Incorrect. Refer to Section 8 Lesson 2.

61.          Which SQL statement below will correctly create the EMP table based on the structure of the EMPLOYEES table? Include only the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, and DEPARTMENT_ID columns.                

               

·         CREATE TABLE employee

AS SELECT employee_id, first_name, last_name, salary, department_id

FROM employees;

·         CREATE TABLE emp (employee_id, first_name, last_name, salary, department_id);

·         CREATE TABLE emp

SELECT (employee_id, first_name, last_name, salary, department_id FROM employees);

·         CREATE TABLE emp

AS SELECT employee_id, first_name, last_name, salary, department_id

FROM employees;

(*)

                              

[Incorrect]                         Incorrect. Refer to Section 8 Lesson 1.

62.          Which of the following SQL statements will create a table called Birthdays with three columns for storing employee number, name and date of birth?

               

·         CREATE table BIRTHDAYS (EMPNO, EMPNAME, BIRTHDATE);

·         CREATE table BIRTHDAYS (employee number, name, date of birth);

·         CREATE TABLE Birthdays (Empno NUMBER, Empname CHAR(20), Birthdate DATE); (*)

·         CREATE TABLE Birthdays (Empno NUMBER, Empname CHAR(20), Date of Birth DATE);

                                                              

[Correct]                             Correct

63.          Which statement about the NOT NULL constraint is true?

               

·         The NOT NULL constraint must be defined at the column level. (*)

·         The NOT NULL constraint can be defined at either the column level or the table level.

·         The NOT NULL constraint requires a column to contain alphanumeric values.

·         The NOT NULL constraint prevents a column from containing alphanumeric values.

                                                              

[Incorrect]                         Incorrect. Refer to Section 10 Lesson 1.

64.          Which statement about constraints is true?

               

                A single column can have only one constraint applied.

                PRIMARY KEY constraints can only be specified at the column level.

                NOT NULL constraints can only be specified at the column level. (*)

                UNIQUE constraints are identical to PRIMARY KEY constraints.

                                                              

[Correct]                             Correct

65.          The DEPARTMENTS table contains these columns:

DEPARTMENT_ID NUMBER, Primary Key

DEPARTMENT_ABBR VARCHAR2(4)

DEPARTMENT_NAME VARCHAR2(30)

MANAGER_ID NUMBER

The EMPLOYEES table contains these columns:

EMPLOYEE_ID NUMBER

LAST_NAME VARCHAR2(25)

FIRST_NAME VARCHAR2(25)

DEPARTMENT_ID NUMBER

JOB_ID NUMBER

MANAGER_ID NUMBER

SALARY NUMBER(9,2)

HIRE_DATE DATE

Evaluate this statement:

ALTER TABLE employees

ADD CONSTRAINT REFERENTIAL (manager_id) TO departments(manager_id);

Which statement is true?

·         The ALTER TABLE statement creates a referential constraint from the EMPLOYEES table to the DEPARTMENTS table.

·         The ALTER TABLE statement creates a referential constraint from the DEPARTMENTS table to the EMPLOYEES table.

·         The ALTER TABLE statement fails because the ADD CONSTRAINT clause contains a syntax error. (*)

·         The ALTER TABLE statement succeeds, but does NOT recreate a referential constraint.

[Incorrect]                         Incorrect. Refer to Section 10 Lesson 3.

66.          You successfully create a table named SALARY in your company's database. Now, you want to establish a parent/child relationship between the EMPLOYEES table and the SALARY table by adding a FOREIGN KEY constraint to the SALARY table that references its matching column in the EMPLOYEES table. You have not added any data to the SALARY table. Which of the following statements should you issue?     

·         ALTER TABLE salary

ADD CONSTRAINT fk_employee_id_01 FOREIGN KEY (employee_id)

REFERENCES employees (employee_id);

(*)        

·         ALTER TABLE salary

ADD CONSTRAINT fk_employee_id_ FOREIGN KEY      

BETWEEN salary (employee_id) AND employees (employee_id);                         

·         ALTER TABLE salary

FOREIGN KEY CONSTRAINT fk_employee_id_ REFERENCES employees (employee_id);            

·         ALTER TABLE salary

ADD CONSTRAINT fk_employee_id_ FOREIGN KEY salary (employee_id) = employees (employee_id);

                              

[Incorrect]                         Incorrect. Refer to Section 10 Lesson 3.

67.          Which statement should you use to add a FOREIGN KEY constraint to the DEPARTMENT_ID column in the EMPLOYEES table to refer to the DEPARTMENT_ID column in the DEPARTMENTS table?

               

·         ALTER TABLE employees

MODIFY COLUMN dept_id_fk FOREIGN KEY (department_id) REFERENCES departments(department_id);

·         ALTER TABLE employees

ADD CONSTRAINT dept_id_fk FOREIGN KEY (department_id) REFERENCES departments(department_id);

(*)                        

·         ALTER TABLE employees

ADD FOREIGN KEY CONSTRAINT dept_id_fk ON (department_id) REFERENCES departments(department_id);

·         ALTER TABLE employees

ADD FOREIGN KEY departments(department_id) REFERENCES (department_id);

               

[Incorrect]                         Incorrect. Refer to Section 10 Lesson 3.

68.          When dropping a constraint, which keyword(s) specifies that all the referential integrity constraints that refer to the primary and unique keys defined on the dropped columns are dropped as well?  

·         FOREIGN KEY

·         REFERENCES

·         CASCADE (*)

·         ON DELETE SET NULL                                                                   

                                                              

[Incorrect]                         Incorrect. Refer to Section 10 Lesson 3.

69.          Evaluate this statement

ALTER TABLE employees

ENABLE CONSTRAINT emp_id_pk;

For which task would you issue this statement?

               

·         To add a new constraint to the EMPLOYEES table

·         To disable an existing constraint on the EMPLOYEES table

·         To activate a new constraint while preventing the creation of a PRIMARY KEY index

·         To activate the previously disabled constraint on the EMPLOYEE_ID column while creating a PRIMARY KEY index (*)

                              

[Correct]                             Correct

70.          What actions can be performed on or with Constraints?            

               

·         Add, Drop, Enable, Disable, Cascade (*)
·         Add, Minus, Enable, Disable, Collapse
·         Add, Subtract, Enable, Cascade
·         Add, Drop, Disable, Disregard

               

[Incorrect]                         Incorrect. Refer to Section 10 Lesson 3.

71.          Which statement about a non-mandatory foreign key constraint is true?          

               

·         A foreign key value cannot be null.
·         A foreign key value must be unique.
·         A foreign key value must match an existing value in the parent table.
·         A foreign key value must either be null or match an existing value in the parent table. (*)

                                                              

[Correct]                             Correct

72.          How many PRIMARY KEY constraints can be created for each table?

               

·         None
·         One and only one (*)
·         One or two
·         Unlimited

[Correct]                             Correct

73.          Which of the following FOREIGN KEY Constraint keywords identifies the table and column in the parent table?

                              

·         RESEMBLES
·         ON DELETE CASCADE
·         REFERENTIAL
·         REFERENCES (*)

               

[Incorrect]                         Incorrect. Refer to Section 10 Lesson 2.

74.          What is an attribute of the data that is entered into a primary key column?      

·         Null and non-unique values cannot be entered into a primary key column. (*)
·         Data that is entered into a primary key column automatically increments by a value of 1 each time a new record is entered into the table.
·         Data that is entered into a primary key column references a column of the same datatype in another table.
·         Data that is entered into a primary key column is restricted to a range of numbers that is defined by the local Oracle database.

                                                              

[Incorrect]                         Incorrect. Refer to Section 10 Lesson 2.

75.          You need to enforce a relationship between the LOC_ID column in the FACILITY table and the same column in the MANUFACTURER table. Which type of constraint should you define on the LOC_ID column?  

·         UNIQUE
·         NOT NULL
·         FOREIGN KEY (*)
·         PRIMARY KEY

[Incorrect]                         Incorrect. Refer to Section 10 Lesson 2.