Skip to main content

Section 14 Quiz Database Programming With SQL

Image
By
Section 14 Quiz
            (Answer all questions in this section)
                                                           
1.         Examine the structures of the PRODUCTS and SUPPLIERS tables.
PRODUCTS:
PRODUCT_ID NUMBER NOT NULL, PRIMARY KEY
PRODUCT_NAME VARCHAR2 (25)
SUPPLIER_ID NUMBER FOREIGN KEY to SUPPLIER_ID of the SUPPLIER table
LIST_PRICE NUMBER (7,2)
COST NUMBER (7,2)
QTY_IN_STOCK NUMBER
QTY_ON_ORDER NUMBER
REORDER_LEVEL NUMBER
REORDER_QTY NUMBER

SUPPLIERS:
SUPPLIER_ID NUMBER NOT NULL, PRIMARY KEY
SUPPLIER_NAME VARCHAR2 (25)
ADDRESS VARCHAR2 (30)
CITY VARCHAR2 (25)
REGION VARCHAR2 (10)
POSTAL_CODE VARCHAR2 (11)

Evaluate this statement:

ALTER TABLE suppliers
DISABLE CONSTRAINT supplier_id_pk CASCADE;

For which task would you issue this statement?

 Mark for Review
(1) Points

            To disable any dependent integrity constraints on the SUPPLIER_ID column in the PRODUCTS table

            To disable any dependent integrity constraints on the SUPPLIER_ID column in the SUPPLIERS table (*)

            To remove all constraint references to SUPPLIERS table

            To drop the FOREIGN KEY constraint on the PRODUCTS table

            To remove all constraint references to the PRODUCTS table

2.         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?

 Mark for Review
(1) Points

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

            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 constraint on the PO_NUM column while creating a PRIMARY KEY index

3.         Which of the following would definitely cause an integrity constraint error?  Mark for Review
(1) Points

            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.
            Using a subquery in an INSERT statement.
            Using the MERGE statement to conditionally insert or update rows.

4.         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?  Mark for Review
(1) Points
            CASCADE (*)
            ON DELETE SET NULL
            FOREIGN KEY
            REFERENCES

5.         All of a user's constraints can be viewed in the Oracle Data Dictionary view called:   Mark for Review
(1) Points
            CONSTRAINTS
            USER_CONSTRAINTS (*)
            TABLE_CONSTRAINTS
            USER_TABLES

6.         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?

 Mark for Review
(1) Points

            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);
(*)

            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 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 REFERENCES donors(donor_id),
pledge_date DATE,
amount_pledged NUMBER,
amount_paid NUMBER,
payment_dt DATE);

7.         Which clause could you use to ensure that cost values are greater than 1.00? Mark for Review
(1) Points
            CHECK CONSTRAINT part_cost_ck (cost > 1.00)
            CONSTRAINT CHECK cost > 1.00
            CONSTRAINT part_cost_ck CHECK (cost > 1.00) (*)
            CONSTRAINT CHECK part_cost_ck (cost > 1.00)

8.         What must exist on the Parent table before Oracle will allow you to create a FOREIGN KEY constraint from a Child table?      Mark for Review
(1) Points

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

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

9.         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?

 Mark for Review
(1) Points
            5
            6
            7 (*)
            8

10.       Which type of constraint by default requires that a column be both unique and not null?       Mark for Review
(1) Points
            UNIQUE
            FOREIGN KEY
            PRIMARY KEY (*)
            CHECK

11.       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?   Mark for Review
(1) Points
            CHECK (*)
            PRIMARY KEY
            UNIQUE
            NOT NULL

12.       Which constraint can only be created at the column level?      Mark for Review
(1) Points
            NOT NULL (*)
            UNIQUE
            FOREIGN KEY
            CHECK

13.       Evaluate this CREATE TABLE statement:
CREATE TABLE customers
    (customer_id NUMBER,
     customer_name VARCHAR2(25),
     address VARCHAR2(25),
     city VARCHAR2(25),
     region VARCHAR2(25),
     postal_code VARCHAR2(11),
     CONSTRAINT customer_id_un UNIQUE(customer_id),
     CONSTRAINT customer_name_nn NOT NULL(customer_name));

Why does this statement fail when executed?

 Mark for Review
(1) Points
            UNIQUE constraints must be defined at the column level.
            The NUMBER data types require precision values.
            The CREATE TABLE statement does NOT define a PRIMARY KEY.
            NOT NULL constraints CANNOT be defined at the table level. (*)

14.       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?       Mark for Review
(1) Points
            PRIMARY KEY
            NOT NULL (*)
            UNIQUE
            CHECK

15.       A table must have at least one not null constraint and one unique constraint. True or False?  Mark for Review
(1) Points
            True
            False (*)

1.         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?  Mark for Review
(1) Points
            REFERENCES
            FOREIGN KEY
            CASCADE (*)
            ON DELETE SET NULL

2.         You need to remove the EMP_FK_DEPT constraint from the EMPLOYEE table in your schema. Which statement should you use?       Mark for Review
(1) Points
            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;

3.         What mechamisn does Oracle use in the background to enforce uniqueness in Primary and Unique key constraints?      Mark for Review
(1) Points
            Nothing extra is created when Primary Keys and Unique Keys are created

            Unique key indexes are created in the background by Oracle when Primary key and Unique key constraints are created or enabled (*)
            Internal Pointers
            Ordered Lists

4.         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? Mark for Review
(1) Points

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

            ALTER TABLE salary
ADD CONSTRAINT fk_employee_id_ FOREIGN KEY
BETWEEN salary (employee_id) AND employees (employee_id);

            ALTER TABLE salary
ADD CONSTRAINT fk_employee_id_01 FOREIGN KEY (employee_id)
REFERENCES employees (employee_id);
(*)

            ALTER TABLE salary
FOREIGN KEY CONSTRAINT fk_employee_id_ REFERENCES employees (employee_id);

5.         What is the syntax for removing a PRIMARY KEY constraint and all its dependent constraints?       Mark for Review
(1) Points

            ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

            DROP CONSTRAINT table_name (constraint_name);

            ALTER TABLE table_name
DROP CONSTRAINT constraint_name CASCADE;
(*)

            ALTER TABLE table_name
DROP CONSTRAINT FOREIGN KEY CASCADE;

6.         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?  Mark for Review
(1) Points

            ALTER TABLE part
MODIFY COLUMN (cost part_cost_nn NOT NULL);

            ALTER TABLE part
MODIFY (cost CONSTRAINT part_cost_nn NOT NULL);
(*)

            ALTER TABLE part
ADD (cost CONSTRAINT part_cost_nn NOT NULL);

            ALTER TABLE part
MODIFY (cost part_cost_nn NOT NULL);

7.         You need to ensure that each value in the SEAT_ID column is unique or null. Which constraint should you define on the SEAT_ID column?  Mark for Review
(1) Points
            CHECK
            PRIMARY KEY
            UNIQUE (*)
            NOT NULL

8.         Which statement about the NOT NULL constraint is true?      Mark for Review
(1) Points
            The NOT NULL constraint requires a column to contain alphanumeric values.
            The NOT NULL constraint can be defined at either the column level or the table level.
            The NOT NULL constraint must be defined at the column level. (*)
            The NOT NULL constraint prevents a column from containing alphanumeric values.

9.         Which two statements about NOT NULL constraints are true? (Choose two)  Mark for Review
(1) Points
                                    (Choose all correct answers)   
            The NOT NULL constraint requires that every value in a column be unique.
            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.

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

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

10.       What is the highest number of NOT NULL constraints you can have on a table?        Mark for Review
(1) Points
            5
            10
            3
            You can have as many NOT NULL constraints as you have columns in your table. (*)

11.       Which line of the following code will cause an error:
CREATE TABLE clients
(client_number NUMBER(4) CONSTRAINT client_client_num_pk PRIMARY KEY client_number,
first_name VARCHAR2(14),
last_name VARCHAR2(13),
hire_date DATE CONSTRAINT emp_min_hire_date CHECK (hire_date < SYSDATE),
department_id VARCHAR(3),
CONSTRAINT clients_dept_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id));
 Mark for Review
(1) Points
            Line 2
            Line 3
            Line 5 (*)
            Line 7

12.       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?
Mark for Review
(1) Points

            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,
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 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 CONSTRAINT donor_id_fk REFERENCES donors(donor_id),
pledge_date DATE,
amount_pledged NUMBER(7,2),
amount_paid NUMBER(7,2),
payment_dt DATE);
(*)

13.       A Primary Key that is made up of more than one column is called a:  Mark for Review
(1) Points
            Multiple Primary Key
            Composite Primary Key (*)
            Double Key
            Primary Multi-Key
            None of the Above

14.       Which of the following FOREIGN KEY Constraint keywords identifies the table and column in the parent table?    Mark for Review
(1) Points
            REFERENTIAL
            ON DELETE CASCADE
            REFERENCES (*)
            RESEMBLES

15.       What must exist on the Parent table before Oracle will allow you to create a FOREIGN KEY constraint from a Child table?      Mark for Review
(1) Points

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

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

1.         A composite primary key may only be defined at the table level. True or False?        Mark for Review
(1) Points
            True (*)
            False

2.         Which of the following FOREIGN KEY Constraint keywords identifies the table and column in the parent table?    Mark for Review
(1) Points
            ON DELETE CASCADE
            REFERENCES (*)
            RESEMBLES
            REFERENTIAL

3.         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?            Mark for Review
(1) Points
            NOT NULL
            UNIQUE
            PRIMARY KEY
            FOREIGN KEY (*)

4.         What must exist on the Parent table before Oracle will allow you to create a FOREIGN KEY constraint from a Child table?      Mark for Review
(1) Points
            A PRIMARY or UNIQUE KEY constraint must exist on the Parent table. (*)
            A FOREIGN KEY constraint allows the constrained column to contain values that exist in the primary key column of the parent table.
            A CHECK constraint must exist on the Parent table.
            An index must exist on the Parent table

5.         Which constraint type enforces uniqueness?   Mark for Review
(1) Points
            NOT NULL
            PRIMARY KEY (*)
            CHECK
            FOREIGN KEY

6.         You need to ensure that each value in the SEAT_ID column is unique or null. Which constraint should you define on the SEAT_ID column?  Mark for Review
(1) Points
            UNIQUE (*)
            PRIMARY KEY
            CHECK
            NOT NULL

7.         Which of the following is not a valid Oracle constraint type?  Mark for Review
(1) Points
            NOT NULL
            EXTERNAL KEY (*)
            UNIQUE KEY
            PRIMARY KEY

8.         Which statement about constraints is true?      Mark for Review
(1) Points
            NOT NULL constraints can only be specified at the column level. (*)
            A single column can have only one constraint applied.
            PRIMARY KEY constraints can only be specified at the column level.
            UNIQUE constraints are identical to PRIMARY KEY constraints.

9.         If the employees table has a UNIQUE constraint on the DEPARTMENT_ID column, we can only have one employee per department. True or False? Mark for Review
(1) Points
            True (*)
            False

10.       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?   Mark for Review
(1) Points
            PRIMARY KEY
            NOT NULL
            CHECK (*)
            UNIQUE

11.       You need to add a NOT NULL constraint to the EMAIL column in the EMPLOYEES table. Which clause should you use?           Mark for Review
(1) Points
            CHANGE
            MODIFY (*)
            ADD
            DISABLE

12.       You need to remove the EMP_FK_DEPT constraint from the EMPLOYEE table in your schema. Which statement should you use?       Mark for Review
(1) Points
            ALTER TABLE employees DROP CONSTRAINT EMP_FK_DEPT; (*)
            DROP CONSTRAINT EMP_FK_DEPT FROM employees;
            DELETE CONSTRAINT EMP_FK_DEPT FROM employees;
            ALTER TABLE employees REMOVE CONSTRAINT EMP_FK_DEPT;

13.       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?        Mark for Review
(1) Points

            ALTER TABLE employees
DISABLE fk_dept_id_01;

            ALTER TABLE employees
DISABLE CONSTRAINT fk_dept_id_01;
(*)

            ALTER TABLE employees
DISABLE CONSTRAINT 'fk_dept_id_01';

            ALTER TABLE employees
DISABLE 'fk_dept_id_01';

14.       All of a user's constraints can be viewed in the Oracle Data Dictionary view called:   Mark for Review
(1) Points
            CONSTRAINTS
            USER_TABLES
            USER_CONSTRAINTS (*)
            TABLE_CONSTRAINTS

15.       You need to add a PRIMARY KEY constraint on the EMP_ID column of the EMPLOYEES table. Which ALTER TABLE statement should you use?     Mark for Review
(1) Points

            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 CONSTRAINT PRIMARY KEY (emp_id);

            ALTER TABLE employees
MODIFY emp_id PRIMARY KEY;

1.         A Primary Key that is made up of more than one column is called a:  Mark for Review
(1) Points
            Multiple Primary Key
            Composite Primary Key (*)
            Double Key
            Primary Multi-Key
            None of the Above

2.         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?

 Mark for Review
(1) Points

            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);
(*)

            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 FOREIGN KEY REFERENCES donors(donor_id),
pledge_date DATE,
amount_pledged NUMBER,
amount_paid NUMBER,
payment_dt DATE);

3.         To automatically delete rows in a child table when a parent record is deleted use:      Mark for Review
(1) Points

            ON DELETE SET NULL
            ON DELETE ORPHAN
            ON DELETE CASCADE (*)
            None of the Above

4.         The employees table contains a foreign key column department_id that references the id column in the departments table. Which of the following constraint modifiers will NOT allow the deletion of id values in the department table?        Mark for Review
(1) Points
            ON DELETE CASCADE
            ON DELETE SET NULL
            Neither A nor B (*)
            Both A and B

5.         Foreign Key Constraints are also known as:    Mark for Review
(1) Points
            Parental Key Constraints
            Child Key Constraints
            Referential Integrity Constraints (*)
            Multi-Table Constraints

6.         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?

 Mark for Review
(1) Points
            The statement will NOT execute because it contains a syntax error. (*)
            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 achieve the desired result.
7.         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? Mark for Review
(1) Points

            ALTER TABLE salary
ADD CONSTRAINT fk_employee_id_ FOREIGN KEY salary (employee_id) = 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
BETWEEN salary (employee_id) AND employees (employee_id);

            ALTER TABLE salary
ADD CONSTRAINT fk_employee_id_01 FOREIGN KEY (employee_id)
REFERENCES employees (employee_id);
(*)

8.         What is the syntax for removing a PRIMARY KEY constraint and all its dependent constraints?       Mark for Review
(1) Points

            ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

            ALTER TABLE table_name
DROP CONSTRAINT constraint_name CASCADE;
(*)

            ALTER TABLE table_name
DROP CONSTRAINT FOREIGN KEY CASCADE;

            DROP CONSTRAINT table_name (constraint_name);

9.         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?

 Mark for Review
(1) Points

            ALTER TABLE line_item
DROP CONSTRAINT product_id_fk;

            ALTER TABLE line_item
DISABLE CONSTRAINT product_id_fk;
(*)

            ALTER TABLE line_item
ENABLE CONSTRAINT product_id_fk;

            ALTER TABLE line_item
DELETE CONSTRAINT product_id_fk;

10.       This SQL command will do what?
ALTER TABLE employees
ADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id);

 Mark for Review
(1) Points

            Add a FOREIGN KEY constraint to the EMPLOYEES table restricting manager ID to match every employee ID.

            Add a FOREIGN KEY constraint to the EMPLOYEES table indicating that a manager must already be an employee. (*)

            Alter the table employees and disable the emp_manager_fk constraint.

            Alter table employees and add a FOREIGN KEY constraint that indicates each employee ID must be unique.

11.       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?   Mark for Review
(1) Points
            PRIMARY KEY
            NOT NULL
            CHECK (*)
            UNIQUE

12.       Which constraint can only be created at the column level?      Mark for Review
(1) Points
            CHECK
            UNIQUE
            FOREIGN KEY
            NOT NULL (*)

13.       Evaluate this CREATE TABLE statement:
CREATE TABLE customers
    (customer_id NUMBER,
     customer_name VARCHAR2(25),
     address VARCHAR2(25),
     city VARCHAR2(25),
     region VARCHAR2(25),
     postal_code VARCHAR2(11),
     CONSTRAINT customer_id_un UNIQUE(customer_id),
     CONSTRAINT customer_name_nn NOT NULL(customer_name));

Why does this statement fail when executed?

 Mark for Review
(1) Points
            NOT NULL constraints CANNOT be defined at the table level. (*)
            The CREATE TABLE statement does NOT define a PRIMARY KEY.
            The NUMBER data types require precision values.
            UNIQUE constraints must be defined at the column level.

14.       Which of the following is not a valid Oracle constraint type?  Mark for Review
(1) Points
            PRIMARY KEY
            UNIQUE KEY
            EXTERNAL KEY (*)
            NOT NULL

15.       What is the highest number of NOT NULL constraints you can have on a table?        Mark for Review
(1) Points
            5
            10
            3
            You can have as many NOT NULL constraints as you have columns in your table. (*)

1.         A unique key constraint can only be defined on a not null column. True or False?     Mark for Review
(1) Points
            True
            False (*)

2.         What is the highest number of NOT NULL constraints you can have on a table?        Mark for Review
(1) Points
            5
            10
            3
            You can have as many NOT NULL constraints as you have columns in your table. (*)

3.         A table can only have one unique key constraint defined. True or False?       Mark for Review
(1) Points
            True
            False (*)

4.         Primary Key, Foreign Key, Unique Key, and Check Constraints can be added at which two levels? (Choose two)   Mark for Review
(1) Points
                                    (Choose all correct answers)   
            Dictionary
            Null Field
            Column (*)
            Table (*)
            Row

5.         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?   Mark for Review
(1) Points
            NOT NULL
            PRIMARY KEY
            CHECK (*)
            UNIQUE

6.         You need to remove the EMP_FK_DEPT constraint from the EMPLOYEE table in your schema. Which statement should you use?       Mark for Review
(1) Points
            DROP CONSTRAINT EMP_FK_DEPT FROM employees;
            ALTER TABLE employees REMOVE CONSTRAINT EMP_FK_DEPT;
            DELETE CONSTRAINT EMP_FK_DEPT FROM employees;
            ALTER TABLE employees DROP CONSTRAINT EMP_FK_DEPT; (*)

7.         What actions can be performed on or with Constraints?          Mark for Review
(1) Points
            Add, Drop, Enable, Disable, Cascade (*)
            Add, Subtract, Enable, Cascade
            Add, Drop, Disable, Disregard
            Add, Minus, Enable, Disable, Collapse

8.         What is the syntax for removing a PRIMARY KEY constraint and all its dependent constraints?       Mark for Review
(1) Points

            ALTER TABLE table_name
DROP CONSTRAINT FOREIGN KEY CASCADE;

            DROP CONSTRAINT table_name (constraint_name);

            ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

            ALTER TABLE table_name
DROP CONSTRAINT constraint_name CASCADE;
(*)

9.         The command to 'switch off' a constraint is:    Mark for Review
(1) Points
            ALTER TABLE PAUSE CONSTRAINT
            ALTER TABLE STOP CONSTRAINTS
            ALTER TABLE DISABLE CONSTRAINT (*)
            ALTER TABLE STOP CHECKING

10.       Examine the structures of the PRODUCTS and SUPPLIERS tables.
PRODUCTS:
PRODUCT_ID NUMBER NOT NULL, PRIMARY KEY
PRODUCT_NAME VARCHAR2 (25)
SUPPLIER_ID NUMBER FOREIGN KEY to SUPPLIER_ID of the SUPPLIER table
LIST_PRICE NUMBER (7,2)
COST NUMBER (7,2)
QTY_IN_STOCK NUMBER
QTY_ON_ORDER NUMBER
REORDER_LEVEL NUMBER
REORDER_QTY NUMBER

SUPPLIERS:
SUPPLIER_ID NUMBER NOT NULL, PRIMARY KEY
SUPPLIER_NAME VARCHAR2 (25)
ADDRESS VARCHAR2 (30)
CITY VARCHAR2 (25)
REGION VARCHAR2 (10)
POSTAL_CODE VARCHAR2 (11)

Evaluate this statement:

ALTER TABLE suppliers
DISABLE CONSTRAINT supplier_id_pk CASCADE;

For which task would you issue this statement?

 Mark for Review
(1) Points
            To remove all constraint references to the PRODUCTS table
            To remove all constraint references to SUPPLIERS table

            To disable any dependent integrity constraints on the SUPPLIER_ID column in the PRODUCTS table

            To disable any dependent integrity constraints on the SUPPLIER_ID column in the SUPPLIERS table (*)

            To drop the FOREIGN KEY constraint on the PRODUCTS table

11.       Which clause could you use to ensure that cost values are greater than 1.00? Mark for Review
(1) Points
            CONSTRAINT part_cost_ck CHECK (cost > 1.00) (*)
            CONSTRAINT CHECK cost > 1.00
            CHECK CONSTRAINT part_cost_ck (cost > 1.00)
            CONSTRAINT CHECK part_cost_ck (cost > 1.00)

12.       Which line of the following code will cause an error:
CREATE TABLE clients
(client_number NUMBER(4) CONSTRAINT client_client_num_pk PRIMARY KEY client_number,
first_name VARCHAR2(14),
last_name VARCHAR2(13),
hire_date DATE CONSTRAINT emp_min_hire_date CHECK (hire_date < SYSDATE),
department_id VARCHAR(3),
CONSTRAINT clients_dept_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id));
 Mark for Review
(1) Points
            Line 2
            Line 3
            Line 5 (*)
            Line 7

13.       A composite primary key may only be defined at the table level. True or False?        Mark for Review
(1) Points
            True (*)
            False

14.       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?

 Mark for Review
(1) Points
            5
            6
            7 (*)
            8

15.       What must exist on the Parent table before Oracle will allow you to create a FOREIGN KEY constraint from a Child table?      Mark for Review
(1) Points
            A PRIMARY or UNIQUE KEY constraint must exist on the Parent table. (*)
            A CHECK constraint must exist on the Parent table.

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

            An index must exist on the Parent table

1.         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?            Mark for Review
(1) Points
            NOT NULL

            FOREIGN KEY (*)
            PRIMARY KEY
            UNIQUE

2.         A Primary Key that is made up of more than one column is called a:  Mark for Review
(1) Points
            Multiple Primary Key
            Composite Primary Key (*)
            Double Key
            Primary Multi-Key
            None of the Above

3.         Which of the following best describes the function of a CHECK constraint?  Mark for Review
(1) Points

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

            A CHECK constraint defines restrictions on 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.
            A CHECK constraint enforces referential data integrity.

4.         A composite primary key may only be defined at the table level. True or False?        Mark for Review
(1) Points
            True (*)
            False

5.         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?

 Mark for Review
(1) Points
            5
            6
            7 (*)
            8

6.         Evaluate this statement:
ALTER TABLE employees
ADD CONSTRAINT employee_id PRIMARY KEY;

Which result will the statement provide?

 Mark for Review
(1) Points
            An existing constraint on the EMPLOYEES table will be overwritten.
            A constraint will be added to the EMPLOYEES table.
            A syntax error will be returned. (*)
            An existing constraint on the EMPLOYEES table will be enabled.

7.         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?

 Mark for Review
(1) Points

            ALTER TABLE line_item
DELETE CONSTRAINT product_id_fk;

            ALTER TABLE line_item
DISABLE CONSTRAINT product_id_fk;
(*)

            ALTER TABLE line_item
ENABLE CONSTRAINT product_id_fk;

            ALTER TABLE line_item
DROP CONSTRAINT product_id_fk;

8.         You need to remove the EMP_FK_DEPT constraint from the EMPLOYEE table in your schema. Which statement should you use?       Mark for Review
(1) Points
            ALTER TABLE employees DROP CONSTRAINT EMP_FK_DEPT; (*)
            ALTER TABLE employees REMOVE CONSTRAINT EMP_FK_DEPT;
            DROP CONSTRAINT EMP_FK_DEPT FROM employees;
            DELETE CONSTRAINT EMP_FK_DEPT FROM employees;

9.         Evaluate this statement
ALTER TABLE employees
ENABLE CONSTRAINT emp_id_pk;

For which task would you issue this statement?

 Mark for Review
(1) Points
            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 (*)

10.       This SQL command will do what?
ALTER TABLE employees
ADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id);

 Mark for Review
(1) Points

            Alter table employees and add a FOREIGN KEY constraint that indicates each employee ID must be unique.

            Alter the table employees and disable the emp_manager_fk constraint.

            Add a FOREIGN KEY constraint to the EMPLOYEES table restricting manager ID to match every employee ID.

            Add a FOREIGN KEY constraint to the EMPLOYEES table indicating that a manager must already be an employee. (*)

11.       A column defined as NOT NULL can have a DEFAULT value of NULL. True or False?       Mark for Review
(1) Points
            True
            False (*)

12.       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?       Mark for Review
(1) Points
            CHECK
            PRIMARY KEY
            NOT NULL (*)
            UNIQUE

13.       Which constraint can only be created at the column level?      Mark for Review
(1) Points
            UNIQUE
            CHECK
            NOT NULL (*)
            FOREIGN KEY

14.       You need to ensure that each value in the SEAT_ID column is unique or null. Which constraint should you define on the SEAT_ID column?  Mark for Review
(1) Points
            CHECK
            NOT NULL
            PRIMARY KEY
            UNIQUE (*)

15.       Which statement about constraints is true?      Mark for Review
(1) Points
            UNIQUE constraints are identical to PRIMARY KEY constraints.
            PRIMARY KEY constraints can only be specified at the column level.
            NOT NULL constraints can only be specified at the column level. (*)

            A single column can have only one constraint applied.
Labels:

Comments

  1. soumya27 June 2018 at 14:15

    This is an excellent information I would like to say thanks for providing with us. check it once at MSBI Online Training Hyderabad

    ReplyDelete
  2. Unknown20 March 2020 at 19:09

    You need to create a composite primary key constraint on the EMPLOYEES table. Which statement is true?


    The PRIMARY KEY constraint must be defined for the first column of the composite primary key.


    The PRIMARY KEY constraint must be defined at the table level and for each column in the composite primary key.


    The PRIMARY KEY constraint must be defined at the table level. (*)


    A PRIMARY KEY constraint must be defined for each column in the composite primary key.

    ReplyDelete
  3. Unknown24 April 2020 at 05:03

    Once constraints have been created on a table, you will have to live with them as they are unless you drop and re-create the table. True or False?

    False (*)

    ReplyDelete
  4. Gananjay Thanekar7 August 2020 at 20:58

    1- 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 CONSTRAINT part_cost_nn NOT NULL);
    (*)



    ALTER TABLE part
    MODIFY COLUMN (cost part_cost_nn NOT NULL);


    ALTER TABLE part
    MODIFY (cost part_cost_nn NOT NULL);


    ALTER TABLE part
    ADD (cost CONSTRAINT part_cost_nn NOT NULL);

    2- The employees table contains a foreign key column department_id that references the id column in the departments table. Which of the following constraint modifiers will NOT allow the deletion of id values in the department table?

    ON DELETE CASCADE


    ON DELETE SET NULL


    Neither A nor B (*)


    Both A and B

    3-The table that contains the Primary Key in a Foreign Key Constraint is known as:

    Mother and Father Table


    Detail Table


    Child Table


    Parent Table (*)
    4-Which of the following best describes the function of a CHECK constraint?

    A CHECK constraint defines restrictions on 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.


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


    A CHECK constraint enforces referential data integrity.

    5-You need to create a composite primary key constraint on the EMPLOYEES table. Which statement is true?

    The PRIMARY KEY constraint must be defined for the first column of the composite primary key.


    A PRIMARY KEY constraint must be defined for each column in the composite primary key.


    The PRIMARY KEY constraint must be defined at the table level and for each column in the composite primary key.


    The PRIMARY KEY constraint must be defined at the table level. (*)

    6- How many PRIMARY KEY constraints can be created for each table?

    None


    One and only one (*)


    One or two


    Unlimited

    ReplyDelete
  5. Mihai10 January 2021 at 02:27

    6. A table can have more than one UNIQUE key constraint. True or False? Mark for Review
    (1) Points


    True (*)


    False

    13. Which statement about a non-mandatory foreign key constraint is true? Mark for Review
    (1) Points


    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. (*)


    A foreign key value cannot be null.

    5. If a Primary Key is made up of more than one column, one of the columns can be null. True or False? Mark for Review
    (1) Points


    True


    False (*)

    ReplyDelete
  6. Mihai10 January 2021 at 23:31

    21. You need to add a PRIMARY KEY to the DEPARTMENTS table. Which statement should you use?
    Mark for Review

    (1) Points
    ALTER TABLE departments
    ADD CONSTRAINT dept_id_pk PK (dept_id);
    ALTER TABLE departments
    ADD PRIMARY KEY dept_id_pk (dept_id);
    ALTER TABLE departments
    ADD CONSTRAINT PRIMARY KEY dept_id_pk (dept_id);
    ALTER TABLE departments
    ADD CONSTRAINT dept_id_pk PRIMARY KEY (dept_id); (*)

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

    (1) Points
    ON DELETE SET NULL
    FOREIGN KEY
    ON DELETE CASCADE
    REFERENCES (*)

    ReplyDelete
  7. Unknown28 June 2021 at 10:55

    Un ejemplo de la adición de una restricción de control para limitar el salario que un empleado puede ganar es:
    Marcar para Revisión

    (1) Puntos
    ALTER TABLE employees CONSTRAINT emp_salary_ck CHECK(salary < 100000)
    ALTER TABLE employees ADD CONSTRAINT emp_salary_ck CHECK (salary < 100000)
    MODIFY TABLE employees ADD CONSTRAINT emp_salary_ck CHECK(salary < 100000
    ALTER TABLE employees ADD CONSTRAINT emp_salary_ck SALARY < 100000

    ReplyDelete
  8. tutus19 August 2021 at 15:44

    You can view the columns used in a constraint defined for a specific table by looking at which data dictionary table?

    USER_CONS_COLUMNS (*)

    ReplyDelete
  9. tutus19 August 2021 at 15:45

    Which of the following pieces of code will successfully create a foreign key in the CDS table that references the SONGS table?

    All of the above (*)

    ReplyDelete
  10. candyies14 January 2022 at 21:28

    A unique key constraint can only be defined on a not null column. True or False?

    True
    False (*)

    ReplyDelete
  11. candyies14 January 2022 at 21:28

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

    USER_CONS_COLUMNS
    DBA_CONSTRAINTS
    ALL_CONS_COLUMNS
    USER_CONSTRAINTS (*)

    ReplyDelete
  12. Chia Malich3 August 2022 at 16:13

    Thanks for the information. I really like the way you express complex topics in lucid way. It really helps me understand it much better way. company database

    ReplyDelete
  13. Chia Malich9 August 2022 at 18:43

    Thanks to share this post.product details database is a very good product.

    ReplyDelete

Post a Comment

Popular posts from this blog

Section 6 Quiz Oracle Database Programming with SQL

By
Section 6 Quiz             (Answer all questions in this section)                                                             1.         Given the following descriptions of the employees and jobs tables, which of the following scripts will display each employee ï¾’ s possible minimum and maximum salaries based on their job title? EMPLOYEES Table: Name   Null?    Type EMPLOYEE_ID          NOT NULL     NUMBER (6) FIRST_NAME             VARCHAR2 (20) LAST_NAME  NOT NULL     VARCHAR2 (25) EMAIL NOT NULL     VARCHAR2 (25) PHONE_NUMBER                  VARCHAR2 (20) HIRE_DATE   NOT NULL     DATE JOB_ID           NOT NULL     VARCHAR2 (10) SALARY                     NUMBER (8,2) COMMISSION_PCT                NUMBER (2,2) MANAGER_ID                       NUMBER (6) DEPARTMENT_ID                 NUMBER (4) JOBS Table: Name   Null?    Type JOB_ID           NOT NULL     VARCHAR2 (10) JOB_TITLE     NOT NULL     VARCHAR2 (35) MIN_SALARY                      
29 comments
Read more

Section 10 Quiz Database Programming With SQL

By
Section 10 Quiz             (Answer all questions in this section) 1.         A multiple-row operator expects how many values?   Mark for Review (1) Points             One or more (*)             Only one             Two or more             None 2.         The salary column of the f_staffs table contains the following values: 4000 5050 6000 11000 23000 Which of the following statements will return the last_name and first_name of those employees who earn more than 5000?  Mark for Review (1) Points             SELECT last_name, first_name FROM f_staffs WHERE salary IN (SELECT last_name, first_name FROM f_staffs WHERE salary <5000 o:p="">             SELECT last_name, first_name FROM f_staffs WHERE salary = (SELECT salary FROM f_staffs WHERE salary < 5000);             SELECT last_name, first_name FROM f_staffs WHERE salary IN (SELECT salary FROM f_staffs WHERE salary > 5000); (*)             SELEC
17 comments
Read more

Section 6 Quiz Database Design Oracle

By
Section 6 Quiz             (Answer all questions in this section) 1.         Examine the following Entity and decide which rule of Normal Form is being violated: ENTITY: CLIENT ATTRIBUTES:     # CLIENT ID     FIRST NAME     LAST NAME     STREET     CITY     ZIP CODE  Mark for Review (1) Points             1st Normal Form.             2nd Normal Form.             3rd Normal Form.             None of the above, the entity is fully normalised. (*) 2.         A transitive dependency exists when any attribute in an entity is dependent on any other non-UID attribute in that entity.            Mark for Review (1) Points             True (*)             False 3.         When any attribute in an entity is dependent on any other non-UID attribute in that entity, this is known as:        Mark for Review (1) Points             Functional dependency             Dependency             Transitive dependency (*)             Non-dependency
16 comments
Read more