Manual Geografie Clasa a 12a

Section 12 Quiz
            (Answer all questions in this section)

1.         A column in a table can be given a default value. This option prevents NULL values from automatically being assigned to the column if a row is inserted without a specified value for the column. True or False ?   Mark for Review
(1) Points
            True (*)
            False

2.         In developing the Employees table, you create a column called hire_date. You assign the hire_date column a DATE datatype with a DEFAULT value of 0 (zero). A user can come back later and enter the correct hire_date. This is __________.           Mark for Review
(1) Points

            A great idea. When a new employee record is entered, if no hire_date is specified, the 0 (zero) will be automatically specified.

            A great idea. When new employee records are entered, they can be added faster by allowing the 0's (zeroes) to be automatically specified.

            Both a and b are correct.

            A bad idea. The default value must match the DATE datatype of the column. (*)

3.         Aliases can be used with MERGE statements. True or False?  Mark for Review
(1) Points
            True (*)
            False

4.         Multi-table inserts can be conditional or unconditional. True or False?           Mark for Review
(1) Points
            True (*)
            False

5.         A multi-table insert statement can insert into more than one table. (True or False?)     Mark for Review
(1) Points
            True (*)
            False

6.         Which of the following represents the correct syntax for an INSERT statement?        Mark for Review
(1) Points
            INSERT VALUES INTO customers (3178 J. Smith 123 Main Street Nashville TN 37777;
            INSERT INTO customers VALUES '3178' 'J.' 'Smith' '123 Main Street' 'Nashville' 'TN' '37777';

            INSERT INTO customers VALUES ('3178', 'J.', 'Smith', '123 Main Street', 'Nashville', 'TN', '37777'); (*)

            INSERT customers VALUES 3178, J., Smith, 123 Main Street, Nashville, TN, 37777;

7.         You need to delete a record in the EMPLOYEES table for Tim Jones, whose unique employee identification number is 348. The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(5) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
ADDRESS VARCHAR2(30)
PHONE NUMBER(10)

Which DELETE statement will delete the appropriate record without deleting any additional records?

 Mark for Review
(1) Points

            DELETE FROM employees
WHERE employee_id = 348;
(*)

            DELETE FROM employees
WHERE last_name = jones;

            DELETE 'jones'
FROM employees;

            DELETE *
FROM employees
WHERE employee_id = 348;

8.         The EMPLOYEES table contains the following columns:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FAST_NAME VARCHAR2(20)
DEPARTMENT_ID VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(9,2)
BONUS NUMBER(9,2)

You want to execute one DML statement to change the salary of all employees in department 10 to equal the new salary of employee number 89898. Currently, all employees in department 10 have the same salary value. Which statement should you execute?

 Mark for Review
(1) Points

            UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id = 89898)
WHERE department_id = 10;
(*)

            UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id = 89898);

            UPDATE employees
SET salary = SELECT salary FROM employees WHERE employee_id = 89898;

            UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id = 89898 AND department_id = 10);

9.         If the subquery returns one row, how many rows will be deleted from the employees table?
DELETE FROM employees
WHERE department_id =
    (SELECT department_id
     FROM departments
     WHERE department_name LIKE '%Public%');

 Mark for Review
(1) Points
            One row will be deleted, as the subquery only returns one row.
            All rows in the employees table will be deleted, no matter the department_id.
            All rows in the employees table of employees who work in the given department will be deleted. (*)
            No rows will be deleted.

10.       What keyword in an UPDATE statement speficies the column that you want to change?        Mark for Review
(1) Points
            SET (*)
            SELECT
            WHERE
            HAVING

11.       To return a table summary on the customers table, which of the following is correct?            Mark for Review
(1) Points
            DEFINE customers, or DEF customers
            SHOW customers, or SEE customers
            DISTINCT customers, or DIST customers
            DESCRIBE customers, or DESC customers (*)

12.       The PRODUCTS table contains these columns:
PRODUCT_ID NUMBER NOT NULL
PRODUCT_NAME VARCHAR2 (25)
SUPPLIER_ID NUMBER NOT NULL
LIST_PRICE NUMBER (7,2)
COST NUMBER (5,2)
QTY_IN_STOCK NUMBER(4)
LAST_ORDER_DT DATE DEFAULT SYSDATE NOT NUL

Which INSERT statement will execute successfully?

 Mark for Review
(1) Points

            INSERT INTO products(product_id, product_name)
VALUES (2958, 'Cable');

            INSERT INTO products(product_id, product_name, supplier_id
VALUES (2958, 'Cable', 8690, SYSDATE);

            INSERT INTO products (product_id, product_name, supplier_id, list_price, cost, qty_in_stock)
VALUES(2958, 'Cable', 8690, 7.09, 4.04, 700) (*)

            INSERT INTO products
VALUES (2958, 'Cable', 8690, 7.09, 4.04, SYSDATE);

13.       Assume all the column names are correct. The following SQL statement will execute which of the following?
INSERT INTO departments
(department_id, department_name, manager_id, location_id)
VALUES (70, 'Public Relations', 100, 1700);

 Mark for Review
(1) Points
            'Public Relations' will be inserted into the manager_name column.
            70 will be inserted into the department_id column. (*)
            1700 will be inserted into the manager_id column.
            100 will be inserted into the department_id column.

14.       You need to copy rows from the EMPLOYEE table to the EMPLOYEE_HIST table. What could you use in the INSERT statement to accomplish this task?       Mark for Review
(1) Points
            A subquery (*)
            A function
            A SET clause
            An ON clause

15.       The STUDENTS table contains these columns:
STU_ID NUMBER(9) NOT NULL
LAST_NAME VARCHAR2 (30) NOT NULL
FIRST_NAME VARCHAR2 (25) NOT NULL
DOB DATE
STU_TYPE_ID VARCHAR2(1) NOT NULL
ENROLL_DATE DATE

You create another table, named FT_STUDENTS, with an identical structure.You want to insert all full-time students who have a STU_TYPE_ID value of "F" into the new table. You execute this INSERT statement:

INSERT INTO ft_students
   (SELECT stu_id, last_name, first_name, dob, stu_type_id, enroll_date
FROM students
WHERE UPPER(stu_type_id) = 'F');

What is the result of executing this INSERT statement?

 Mark for Review
(1) Points
            An error occurs because the INSERT statement does NOT contain a VALUES clause.
            All full-time students are inserted into the FT_STUDENTS table. (*)
            An error occurs because the FT_STUDENTS table already exists.
            An error occurs because you CANNOT use a subquery in an INSERT statement.

1.         Which of the following statements will add a new customer to the customers table in the Global Fast Foods database?          Mark for Review
(1) Points

            INSERT INTO customers (id, first_name, last_name, address, city, state, zip, phone_number)
VALUES (145, 'Katie', 'Hernandez', '92 Chico Way', 'Los Angeles', 'CA', 98008, 8586667641);
(*)

            INSERT IN customers (id, first_name, last_name, address, city, state, zip, phone_number);

            INSERT INTO customers
(id 145, first_name 'Katie', last_name 'Hernandez', address '92 Chico Way', city 'Los Angeles', state 'CA', zip 98008, phone_number 8586667641);

            INSERT INTO customers (id, first_name, last_name, address, city, state, zip, phone_number)
VALUES ("145", 'Katie', 'Hernandez', '92 Chico Way', 'Los Angeles', 'CA', "98008", "8586667641");

2.         Which statement about the VALUES clause of an INSERT statement is true? Mark for Review
(1) Points
            To specify a null value in the VALUES clause, use an empty string (" ").

            Character, date, and numeric data must be enclosed within single quotes in the VALUES clause.
            The VALUES clause in an INSERT statement is mandatory in a subquery.

            If no column list is specified, the values must be listed in the same order that the columns are listed in the table. (*)

3.         If the employees table has 7 rows, how many rows are inserted into the copy_emps table with the following statement:
INSERT INTO copy_emps (employee_id, first_name, last_name, salary, department_id)
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees
 Mark for Review
(1) Points
            No rows, as the SELECT statement is invalid.
            10 rows will be created.
            No rows, as you cannot use subqueries in an insert statement.
            7 rows, as no WHERE-clause restricts the rows returned on the subquery. (*)

4.         Using the INSERT statement and assuming that a column can accept null values, how can you implicitly insert a null value in a column?           Mark for Review
(1) Points
            It is not possible to implicitly insert a null value in a column.
            Omit the column in the column list. (*)
            Use the NULL keyword.
            Use the ON clause

5.         Insert statements can be combined with subqueries to create more than one row per statement. True or False?  Mark for Review
(1) Points
            True (*)
            False

6.         Which statement below will not insert a row of data into a table?        Mark for Review
(1) Points

            INSERT INTO student_table
VALUES (143354, 'Roberts', 'Cameron', 6543);

            INSERT INTO (id, lname, fname, lunch_num)
VALUES (143354, 'Roberts', 'Cameron', 6543);
(*)

            INSERT INTO student_table (id, lname, fname, lunch_num)
VALUES (143352, 'Roberts', 'Cameron', DEFAULT);

            INSERT INTO student_table (id, lname, fname, lunch_num)
VALUES (143354, 'Roberts', 'Cameron', 6543);

7.         Aliases can be used with MERGE statements. True or False?  Mark for Review
(1) Points
            True (*)
            False

8.         Multi-table inserts are used when the same source data should be inserted into _____________ target table.    Mark for Review
(1) Points
            A very large
            A data warehouse
            Ten
            More than one (*)

9.         The DEFAULT keyword can be used in the following statements:      Mark for Review
(1) Points
            INSERT and UPDATE (*)
            INSERT and DELETE
            DELETE and UPDATE
            All of the above

10.       The default value must match the __________ of the column.           Mark for Review
(1) Points
            Datatype (*)
            Table
            Column name
            Size

11.       One of the sales representatives, Janet Roper, has informed you that she was recently married, and she has requested that you update her name in the employee database. Her new last name is Cooper. Janet is the only person with the last name of Roper that is employed by the company. The EMPLOYEES table contains these columns and all data is stored in lowercase:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPARTMENT_ID VARCHAR2 (20)
HIRE_DATE DATE
SALARY NUMBER(10)

Which UPDATE statement will accomplish your objective?

 Mark for Review
(1) Points

            UPDATE employees
SET cooper = 'last_name'
WHERE last_name = 'roper';

            UPDATE employees last_name = 'cooper'
WHERE last_name = 'roper';

            UPDATE employees
SET last_name = 'cooper'
WHERE last_name = 'roper'; (*)

            UPDATE employees
SET last_name = 'roper'
WHERE last_name = 'cooper';

12.       To change an existing row in a table, you can use the UPDATE or INSERT statements. True or False?            Mark for Review
(1) Points
            True
            False (*)

13.       One of your employees was recently married. Her employee ID is still 189, however, her last name is now Rockefeller. Which SQL statement will allow you to reflect this change?            Mark for Review
(1) Points
            INSERT INTO my_employees SET last_name = 'Rockefeller' WHERE employee_ID = 189;
            INSERT my_employees SET last_name = 'Rockefeller' WHERE employee_ID = 189;
            UPDATE INTO my_employees SET last_name = 'Rockefeller' WHERE employee_ID = 189;
            UPDATE my_employees SET last_name = 'Rockefeller' WHERE employee_ID = 189; (*)

14.       The TEACHERS and CLASS_ASSIGNMENTS tables contain these columns:
TEACHERS:
TEACHER_ID NUMBER(5)
NAME VARCHAR2(25)
SUBJECT_ID NUMBER(5)
HIRE_DATE DATE
SALARY NUMBER(9,2)

CLASS_ASSIGNMENTS:
CLASS_ID NUMBER(5)
TEACHER_ID NUMBER(5)
START_DATE DATE
MAX_CAPACITY NUMBER(3)

Which scenario would require a subquery to return the desired results?

 Mark for Review
(1) Points
            You need to display the start date for each class taught by a given teacher.
            You need to create a report to display the teachers who were hired more than five years ago.
            You need to display the names of the teachers who teach classes that start within the next week.
            You need to create a report to display the teachers who teach more classes than the average number of classes taught by each teacher. (*)

15.       You need to remove a row from the EMPLOYEES table. Which statement would you use?   Mark for Review
(1) Points
            UPDATE with a WHERE clause
            DELETE with a WHERE clause (*)
            MERGE with a WHERE clause
            INSERT with a WHERE clause

1.         A column in a table can be given a default value. This option prevents NULL values from automatically being assigned to the column if a row is inserted without a specified value for the column. True or False ?   Mark for Review
(1) Points
            True (*)
            False

2.         Multi-table inserts are used when the same source data should be inserted into _____________ target table.    Mark for Review
(1) Points
            A data warehouse
            A very large
            More than one (*)
            Ten

3.         In developing the Employees table, you create a column called hire_date. You assign the hire_date column a DATE datatype with a DEFAULT value of 0 (zero). A user can come back later and enter the correct hire_date. This is __________.           Mark for Review
(1) Points

            A great idea. When a new employee record is entered, if no hire_date is specified, the 0 (zero) will be automatically specified.

            A great idea. When new employee records are entered, they can be added faster by allowing the 0's (zeroes) to be automatically specified.

            Both a and b are correct.
            A bad idea. The default value must match the DATE datatype of the column. (*)

4.         Aliases can be used with MERGE statements. True or False?  Mark for Review
(1) Points
            True (*)
            False

5.         The MERGE statement first tries to update one or more rows in a table that match the criteria; if no row matches the criteria for the update, a new row will automatically be inserted instead. True or False?  Mark for Review
(1) Points
            True (*)
            False

6.         Assume all the column names are correct. The following SQL statement will execute which of the following?
INSERT INTO departments
(department_id, department_name, manager_id, location_id)
VALUES (70, 'Public Relations', 100, 1700);

 Mark for Review
(1) Points
            100 will be inserted into the department_id column.
            1700 will be inserted into the manager_id column.
            'Public Relations' will be inserted into the manager_name column.
            70 will be inserted into the department_id column. (*)

7.         To return a table summary on the customers table, which of the following is correct?            Mark for Review
(1) Points
            DESCRIBE customers, or DESC customers (*)
            DISTINCT customers, or DIST customers
            SHOW customers, or SEE customers
            DEFINE customers, or DEF customers

8.         The PRODUCTS table contains these columns:
PRODUCT_ID NUMBER NOT NULL
PRODUCT_NAME VARCHAR2 (25)
SUPPLIER_ID NUMBER NOT NULL
LIST_PRICE NUMBER (7,2)
COST NUMBER (5,2)
QTY_IN_STOCK NUMBER(4)
LAST_ORDER_DT DATE DEFAULT SYSDATE NOT NUL

Which INSERT statement will execute successfully?

 Mark for Review
(1) Points

            INSERT INTO products
VALUES (2958, 'Cable', 8690, 7.09, 4.04, SYSDATE);

            INSERT INTO products(product_id, product_name)
VALUES (2958, 'Cable');

            INSERT INTO products (product_id, product_name, supplier_id, list_price, cost, qty_in_stock)
VALUES(2958, 'Cable', 8690, 7.09, 4.04, 700) (*)

            INSERT INTO products(product_id, product_name, supplier_id
VALUES (2958, 'Cable', 8690, SYSDATE);

9.         Using the INSERT statement and assuming that a column can accept null values, how can you implicitly insert a null value in a column?           Mark for Review
(1) Points
            It is not possible to implicitly insert a null value in a column.
            Use the NULL keyword.
            Omit the column in the column list. (*)
            Use the ON clause

10.       DML is an acronym that stands for:     Mark for Review
(1) Points
            Debit Markup Language
            Data Markup Language
            Data Manipulation Language (*)
            Don't Manipulate Language

11.       Using your knowledge of the employees table, what would be the result of the following statement:
DELETE FROM employees;    Mark for Review
(1) Points
            Deletes employee number 100.
            All rows in the employees table will be deleted if there are no constraints on the table. (*)
            Nothing, no data will be changed.
            The first row in the employees table will be deleted.

12.       Examine the structures of the PLAYERS, MANAGERS, and TEAMS tables:
PLAYERS:
PLAYER_ID NUMBER Primary Key
LAST_NAME VARCHAR2 (30)
FIRST_NAME VARCHAR2 (25)
TEAM_ID NUMBER
MGR_ID NUMBER
SIGNING_BONUS NUMBER(9,2)
SALARY NUMBER(9,2)

MANAGERS:
MANAGER_ID NUMBER Primary Key
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
TEAM_ID NUMBER

TEAMS:
TEAM_ID NUMBER Primary Key
TEAM_NAME VARCHAR2 (20)
OWNER_LAST_NAME VARCHAR2 (20)
OWNER_FIRST_NAME VARCHAR2 (20)

Which situation would require a subquery to return the desired result?

 Mark for Review
(1) Points

            To display the names of each player on the Lions team
            To display the maximum and minimum player salary for each team
            To display the names of the managers for all the teams owned by a given owner (*)
            To display each player, their manager, and their team name for all teams with an id value greater than 5000

13.       You need to update the area code of employees that live in Atlanta. Evaluate this partial UPDATE statement:
UPDATE employee
SET area_code = 770

Which of the following should you include in your UPDATE statement to achieve the desired results?

 Mark for Review
(1) Points

            WHERE city = 'Atlanta'; (*)
            UPDATE city = Atlanta;
            SET city = 'Atlanta';
            LIKE 'At%';

14.       One of the sales representatives, Janet Roper, has informed you that she was recently married, and she has requested that you update her name in the employee database. Her new last name is Cooper. Janet is the only person with the last name of Roper that is employed by the company. The EMPLOYEES table contains these columns and all data is stored in lowercase:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPARTMENT_ID VARCHAR2 (20)
HIRE_DATE DATE
SALARY NUMBER(10)

Which UPDATE statement will accomplish your objective?

 Mark for Review
(1) Points

            UPDATE employees last_name = 'cooper'
WHERE last_name = 'roper';

            UPDATE employees
SET last_name = 'roper'
WHERE last_name = 'cooper';

            UPDATE employees
SET cooper = 'last_name'
WHERE last_name = 'roper';

            UPDATE employees
SET last_name = 'cooper'
WHERE last_name = 'roper'; (*)

15.       What would happen if you issued a DELETE statement without a WHERE clause?    Mark for Review
(1) Points
            All the rows in the table would be deleted. (*)
            Only one row would be deleted.
            No rows would be deleted.

Section 13 Quiz
            (Answer all questions in this section)

1.         To store time with fractions of seconds, which datatype should be used for a table column? Mark for Review
(1) Points
            DATE
            INTERVAL DAY TO SECOND
            INTERVAL YEAR TO MONTH
            TIMESTAMP (*)

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

 Mark for Review
(1) Points
            Four (*)
            Zero
            Six
            Two

3.         The TIMESTAMP data type allows what?       Mark for Review
(1) Points
            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.

4.         Which data types stores variable-length character data? Select two.    Mark for Review
(1) Points
                                    (Choose all correct answers)
            NCHAR
            CHAR
            CLOB (*)
            VARCHAR2 (*)

5.         INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. True or False?  Mark for Review
(1) Points
            True (*)
            False

6.         A column's data type can always be changed from NUMBER to VARCHAR2 but not from VARCHAR2 to NUMBER, provided the table is empty. True or False?       Mark for Review
(1) Points
            True
            False (*)

7.         You can use the ALTER TABLE statement to:            Mark for Review
(1) Points
            Add a new column
            Modify an existing column
            Drop a column
            All of the above (*)

8.         Evaluate this statement:
ALTER TABLE inventory
MODIFY backorder_amount NUMBER(8,2);

Which task will this statement accomplish?

 Mark for Review
(1) Points
            Alters the definition of the BACKORDER_AMOUNT column to NUMBER(2,8)
            Alters the definition of the BACKORDER_AMOUNT column to NUMBER(8.2)
            Alters the definition of the BACKORDER_AMOUNT column to NUMBER(8 2)
            Changes the definition of the BACKORDER_AMOUNT column to NUMBER(8,2) (*)
            Alters the definition of the BACKORDER_AMOUNT column to NUMBER

9.         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?    Mark for Review
(1) Points
            TRUNCATE TABLE
            DELETE TABLE
            ALTER TABLE
            DROP TABLE (*)

10.       When you use ALTER TABLE to add a column, the new column:     Mark for Review
(1) Points
            Becomes the last column in the table (*)
            Becomes the first column in the table
            Will not be created because you cannot add a column after the table is created
            Can be placed by adding a GROUP BY clause

11.       It is possible to create a table by using the CREATE TABLE command in conjunction with a subquery. True or False?  Mark for Review
(1) Points
            True (*)
            False

12.       I have a table named School_Friends in my schema. You want to build a table in your schema named School_Friends. This is ______________, because ____________________________________.  Mark for Review
(1) Points

            possible; our data will merge into one table, and we can more easily access our mutual friends information.

            possible; my schema is separate from yours, and it is okay for us to have like-named tables in our separate schemas. (*)

            impossible; no matter what, there can never be two tables with the same name, even if they are in separate schemas.

            impossible; School_Friends is a reserved term in SQL.

13.       Which statement about table and column names is true?         Mark for Review
(1) Points
            Table and column names cannot include special characters.
            Table and column names can begin with a letter or a number.
            Table and column names must begin with a letter. (*)
            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.

14.       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?    Mark for Review
(1) Points
            commission_pct NUMBER(4,2) IS DEFAULT 0.10
            commission_pct NUMBER(4,2) DEFAULT 0.10 (*)
            commission_pct NUMBER(4,2) (DEFAULT, 0.10)
            commission_pct NUMBER(4,2) DEFAULT = 0.10

15.       Evaluate this CREATE TABLE statement:
1. CREATE TABLE customer#1 (
2. cust_1 NUMBER(9),
3. sales$ NUMBER(9),
4. 2date DATE DEFAULT SYSDATE);

Which line of this statement will cause an error?

 Mark for Review
(1) Points
            3
            1
            4 (*)
            2

1.         The TIMESTAMP data type allows what?       Mark for Review
(1) Points
            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.

2.         Which statement about data types is true?       Mark for Review
(1) Points
            The TIMESTAMP data type is a character data type.
            The VARCHAR2 data type should be used for fixed-length character data.
            The BFILE data type stores character data up to four gigabytes in the database.
            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. (*)

3.         INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. True or False?  Mark for Review
(1) Points
            True (*)
            False

4.         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?     Mark for Review
(1) Points
            CHAR
            DATE (*)
            INTERVAL YEAR TO MONTH
            TIMESTAMP

5.         To store large amounts of text you should simply create a series of VARCHAR2 columns in a table. True or False?          Mark for Review
(1) Points
            True
            False (*)

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

            CREATE TABLE employee
AS 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;
(*)

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

7.         Which CREATE TABLE statement will fail?   Mark for Review
(1) Points
            CREATE TABLE time_date (time NUMBER(9));
            CREATE TABLE date_1 (date_1 DATE);
            CREATE TABLE date (date_id NUMBER(9)); (*)
            CREATE TABLE time (time_id NUMBER(9));

8.         I have a table named School_Friends in my schema. You want to build a table in your schema named School_Friends. This is ______________, because ____________________________________.  Mark for Review
(1) Points

            possible; my schema is separate from yours, and it is okay for us to have like-named tables in our separate schemas. (*)

            possible; our data will merge into one table, and we can more easily access our mutual friends information.

            impossible; no matter what, there can never be two tables with the same name, even if they are in separate schemas.

            impossible; School_Friends is a reserved term in SQL.

9.         Which statement about table and column names is true?         Mark for Review
(1) Points
            Table and column names must begin with a letter. (*)
            Table and column names can begin with a letter or a number.
            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.
            Table and column names cannot include special characters.

10.       CREATE TABLE student_table
    (id NUMBER(6),
     lname VARCHAR(20),
     fname VARCHAR(20),
     lunch_num NUMBER(4));
Which of the following statements best describes the above SQL statement:

 Mark for Review
(1) Points
            Creates a table named student_table with four columns: id, lname, fname, lunch_num (*)
            Creates a table named student_table with four columns: lname, fname, lunch, num
            Creates a table named student with four columns: id, lname, fname, lunch_num
            Creates a table named student_table with four columns: lname, fname, lunch, num

11.       A column's data type can always be changed from NUMBER to VARCHAR2 but not from VARCHAR2 to NUMBER, provided the table is empty. True or False?       Mark for Review
(1) Points
            True
            False (*)

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

 Mark for Review
(1) Points
            The column named COLOR in the table named PRODUCTS will be created.
            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 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. (*)

13.       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?         Mark for Review
(1) Points
            ALTER TABLE
            DROP TABLE (*)
            DELETE
            TRUNCATE TABLE

14.       The FLASHBACK QUERY statement can restore data back to a point in time before the last COMMIT. True or False?  Mark for Review
(1) Points
            True
            False (*)

15.       Evaluate this statement:
Which statement about this TRUNCATE TABLE statement is true?    Mark for Review
(1) Points
            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.

1.         You need to change the name of the EMPLOYEES table to the EMP table. Which statement should you use?     Mark for Review
(1) Points
            ALTER TABLE employees RENAME TO emp;
            RENAME employees emp;
            RENAME employees TO emp; (*)
            ALTER TABLE employees TO emp;

2.         You can use the ALTER TABLE statement to:            Mark for Review
(1) Points
            Add a new column
            Modify an existing column
            Drop a column
            All of the above (*)

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

 Mark for Review
(1) Points

            ALTER teams TABLE
MODIFY COLUMN (mgr_id VARCHAR2(15));

            ALTER TABLE teams
REPLACE (mgr_id VARCHAR2(15));

            ALTER teams
MODIFY (mgr_id VARCHAR2(15));

            ALTER TABLE teams
MODIFY (mgr_id VARCHAR2(15));
(*)

            You CANNOT modify the data type of the MGR_ID column.

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

 Mark for Review
(1) Points

            ALTER TABLE employee
RENAME last_name VARCHAR2(35);

            ALTER employee TABLE
ALTER COLUMN (last_name VARCHAR2(35));

            ALTER TABLE employee
MODIFY (last_name VARCHAR2(35));
(*)
            You CANNOT increase the width of the LAST_NAME column.

5.         Which statement about a column is NOT true?            Mark for Review
(1) Points
            You can convert a DATE data type column to a VARCHAR2 column.
            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.

6.         Once they are created, external tables are accessed with normal SQL statements. (True or False?)     Mark for Review
(1) Points
            True (*)
            False

7.         Evaluate this CREATE TABLE statement:
1. CREATE TABLE customer#1 (
2. cust_1 NUMBER(9),
3. sales$ NUMBER(9),
4. 2date DATE DEFAULT SYSDATE);

Which line of this statement will cause an error?

 Mark for Review
(1) Points
            4 (*)
            1
            3
            2

8.         Which column name is valid?  Mark for Review
(1) Points
            NUMBER
            1NUMBER
            NUMBER_1$ (*)
            1_NUMBER#

9.         I have a table named School_Friends in my schema. You want to build a table in your schema named School_Friends. This is ______________, because ____________________________________.  Mark for Review
(1) Points
            impossible; School_Friends is a reserved term in SQL.

            impossible; no matter what, there can never be two tables with the same name, even if they are in separate schemas.

            possible; my schema is separate from yours, and it is okay for us to have like-named tables in our separate schemas. (*)

            possible; our data will merge into one table, and we can more easily access our mutual friends information.

10.       DCL, which is the acronym for Data Control Language, allows:         Mark for Review
(1) Points
            The ALTER command to be used.
            The TRUNCATE command to be used.
            A Database Administrator the ability to grant privileges to users. (*)
            The CONROL TRANSACTION statement can be used.

11.       The TIMESTAMP data type allows what?       Mark for Review
(1) Points
            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.

12.       A column that will be used to store binary data up to 4 Gigabytes in size should be defined as which datatype?         Mark for Review
(1) Points
            LONGRAW
            LONG
            NUMBER
            BLOB (*)

13.       Which of the following are valid Oracle datatypes?    Mark for Review
(1) Points
            TIMESTAMP, LOB, VARCHAR2, NUMBER
            DATE, BLOB, LOB, VARCHAR2
            DATE, TIMESTAMP WITH LOCAL TIME ZONE, BLOB (*)
            SYSDATE, TIMESTAMP, DATE, LOCAL TIME ZONE
14.       Which statement about data types is true?       Mark for Review
(1) Points

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

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

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

            The TIMESTAMP data type is a character data type.

15.       Which data types stores variable-length character data? Select two.    Mark for Review
(1) Points

                                    (Choose all correct answers)

            NCHAR
            VARCHAR2 (*)
            CLOB (*)
            CHAR

1.         INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. True or False?  Mark for Review
(1) Points
            True (*)
            False

2.         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?     Mark for Review
(1) Points
            TIMESTAMP
            INTERVAL YEAR TO MONTH
            CHAR
            DATE (*)

3.         To store large amounts of text you should simply create a series of VARCHAR2 columns in a table. True or False?          Mark for Review
(1) Points
            True
            False (*)

4.         A column that will be used to store binary data up to 4 Gigabytes in size should be defined as which datatype?         Mark for Review
(1) Points
            NUMBER
            LONGRAW
            BLOB (*)
            LONG

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

 Mark for Review
(1) Points
            Description values can range from 0 to 30 characters so the column should be fixed in length.

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

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

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

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

 Mark for Review
(1) Points
            You must use the ADD OR REPLACE option to achieve these results.
            You must drop and recreate the DONATIONS table to achieve these results.
            Both changes can be accomplished with one ALTER TABLE statement. (*)
            You CANNOT decrease the width of the AMOUNT_PLEDGED column.

7.         Which command could you use to quickly remove all data from the rows in a table without deleting the table itself?      Mark for Review
(1) Points
            MODIFY
            ALTER TABLE
            DROP TABLE
            TRUNCATE TABLE (*)

8.         You need to remove all the rows from the SALES_HIST table. You want to release the storage space, but do not want to remove the table structure. Which statement should you use?  Mark for Review
(1) Points
            The TRUNCATE TABLE statement (*)
            The ALTER TABLE statement
            The DROP TABLE statement
            The DELETE statement

9.         The FLASHBACK QUERY statement can restore data back to a point in time before the last COMMIT. True or False?  Mark for Review
(1) Points
            True
            False (*)

10.       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?    Mark for Review
(1) Points
            TRUNCATE TABLE
            DELETE TABLE
            ALTER TABLE
            DROP TABLE (*)

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

 Mark for Review
(1) Points
            You created the table in the SYSDBA schema.
            You created the LINE_ITEM table in the SYS schema.
            You created the LINE_ITEM table in the public schema.
            You created the table in your schema. (*)

12.       CREATE TABLE bioclass
    (hire_date DATE DEFAULT SYSDATE,
     first_name varchar2(15),
     last_name varchar2(15));
The above CREATE TABLE statement is acceptable, and will create a Table named bioclass that contains a hire_date, first_name, and last_name column. True or False?

 Mark for Review
(1) Points
            True (*)
            False

13.       I have a table named School_Friends in my schema. You want to build a table in your schema named School_Friends. This is ______________, because ____________________________________.  Mark for Review
(1) Points

            possible; my schema is separate from yours, and it is okay for us to have like-named tables in our separate schemas. (*)

            impossible; no matter what, there can never be two tables with the same name, even if they are in separate schemas.

            possible; our data will merge into one table, and we can more easily access our mutual friends information.

            impossible; School_Friends is a reserved term in SQL.

14.       Given this employee table:
(employee_id NUMBER(10) NOT NULL,
first_name VARCHAR2(25) NOT NULL,
last_name VARCHAR2(30) NOT NULL,
hire_date DATE DEFAULT sysdate)

What will be the result in the hire_date column following this insert statement:

INSERT INTO employees VALUES (10, 'Natacha', 'Hansen', DEFAULT);

 Mark for Review
(1) Points

            Statement will fail, as you must list the columns into which you are inserting.
            The column for hire_date will be null.

            Statement will work and the hire_date column will have the value of the date when the statement was run. (*)

            The character string SYSDATE.

15.       Once they are created, external tables are accessed with normal SQL statements. (True or False?)     Mark for Review
(1) Points
            True (*)
            False

1.         It is possible to create a table by using the CREATE TABLE command in conjunction with a subquery. True or False?  Mark for Review
(1) Points
            True (*)
            False

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

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

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

4.         Which statement about table and column names is true?         Mark for Review
(1) Points

            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.

            Table and column names can begin with a letter or a number.
            Table and column names cannot include special characters.
            Table and column names must begin with a letter. (*)

5.         DCL, which is the acronym for Data Control Language, allows:         Mark for Review
(1) Points
            The ALTER command to be used.
            The TRUNCATE command to be used.
            A Database Administrator the ability to grant privileges to users. (*)
            The CONROL TRANSACTION statement can be used.

6.         Evaluate this CREATE TABLE statement:
CREATE TABLE sales
(sales_id NUMBER,
customer_id NUMBER,
employee_id NUMBER,
sale_date TIMESTAMP WITH TIME ZONE,
sale_amount NUMBER(7,2));

Which statement about the SALE_DATE column is true?

 Mark for Review
(1) Points
            Data will be stored using a fractional seconds precision of 5.
            Data will be normalized to the client time zone.
            Data stored will not include seconds.
            Data stored in the column will be returned in the database's local time zone. (*)

7.         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?           Mark for Review
(1) Points
            DATETIME
            INTERVAL YEAR TO MONTH
            TIMESTAMP
            INTERVAL DAY TO SECOND (*)

8.         You are designing a table for the Sales department. You need to include a column that contains each sales total. Which data type should you specify for this column?        Mark for Review
(1) Points
            NUMBER (*)
            VARCHAR2
            DATE
            CHAR

9.         To store time with fractions of seconds, which datatype should be used for a table column? Mark for Review
(1) Points
            DATE
            INTERVAL YEAR TO MONTH
            TIMESTAMP (*)
            INTERVAL DAY TO SECOND

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

 Mark for Review
(1) Points
            Description values can range from 0 to 30 characters so the column should be fixed in length.

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

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

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

11.       RENAME old_name to new_name can be used to:     Mark for Review
(1) Points
            Rename a row.
            Rename a column.
            Rename a table. (*)
            All of the above.

12.       The data type of a column can never be changed once it has been created. True or False?     Mark for Review
(1) Points
            True
            False (*)

13.       When you use ALTER TABLE to add a column, the new column:     Mark for Review
(1) Points
            Can be placed by adding a GROUP BY clause
            Will not be created because you cannot add a column after the table is created
            Becomes the first column in the table
            Becomes the last column in the table (*)

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

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

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

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

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

 Mark for Review
(1) Points

            ALTER TABLE teams
MODIFY (mgr_id VARCHAR2(15));
(*)

            ALTER TABLE teams
REPLACE (mgr_id VARCHAR2(15));

            You CANNOT modify the data type of the MGR_ID column.

            ALTER teams
MODIFY (mgr_id VARCHAR2(15));

            ALTER teams TABLE
MODIFY COLUMN (mgr_id VARCHAR2(15));

1.         You can use the ALTER TABLE statement to:            Mark for Review
(1) Points
            Add a new column
            Modify an existing column
            Drop a column
            All of the above (*)

2.         When should you use the SET UNUSED command?  Mark for Review
(1) Points

            You should only use this command if you want the column to still be visible when you DESCRIBE the table.
            You should use it if you think the column may be needed again later.

            You should use it when you need a quick way of dropping a column. (*)

            Never, there is no SET UNUSED command.

3.         Which command could you use to quickly remove all data from the rows in a table without deleting the table itself?      Mark for Review
(1) Points
            ALTER TABLE
            DROP TABLE
            TRUNCATE TABLE (*)
            MODIFY

4.         Comments on tables and columns can be stored for documentation by:          Mark for Review
(1) Points
            Using the ALTER TABLE CREATE COMMENT syntax
            Embedding /* comment */ within the definition of the table.
            Using an UPDATE statement on the USER_COMMENTS table
            Using the COMMENT ON TABLE or COMMENT on COLUMN (*)

5.         Evaluate this statement:
ALTER TABLE employees SET UNUSED (fax);
Which task will this statement accomplish?

 Mark for Review
(1) Points
            Deletes the FAX column
            Frees the disk space used by the data in the FAX column
            Prevents a new FAX column from being added to the EMPLOYEES table
            Prevents data in the FAX column from being displayed, by performing a logical drop of the column (*)

6.         Examine this CREATE TABLE statement:
CREATE TABLE emp_load
(employee_number CHAR(5),
employee_dob CHAR(20),
employee_last_name CHAR(20),
employee_first_name CHAR(15),
employee_middle_name CHAR(15),
employee_hire_date DATE)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY def_dir1
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
FIELDS (employee_number CHAR(2),
      employee_dob CHAR(20),
      employee_last_name CHAR(18),
      employee_first_name CHAR(11),
      employee_middle_name CHAR(11),
      employee_hire_date CHAR(10) date_format DATE mask "mm/dd/yyyy"))
LOCATION ('info.dat'));

What kind of table is created here?

 Mark for Review
(1) Points
            An external table with the data stored in a file outside the database. (*)
            A View.
            An external table with the data stored in a file inside the database.
            None. This is in invalid statement.

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

            CREATE TABLE emp
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 employee
AS SELECT employee_id, first_name, last_name, salary, department_id
FROM employees;

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

8.         I have a table named School_Friends in my schema. You want to build a table in your schema named School_Friends. This is ______________, because ____________________________________.  Mark for Review
(1) Points

            possible; our data will merge into one table, and we can more easily access our mutual friends information.

            impossible; School_Friends is a reserved term in SQL.

            possible; my schema is separate from yours, and it is okay for us to have like-named tables in our separate schemas. (*)

            impossible; no matter what, there can never be two tables with the same name, even if they are in separate schemas.

9.         When creating a new table, which of the following naming rules apply. (Choose three)         Mark for Review
(1) Points
                                    (Choose all correct answers)

            Can have the same name as another object owned by the same user
            Must begin with a letter (*)
            Must contain ONLY A - Z, a - z, 0 - 9, _ (underscore), $, and # (*)
            Must be between 1 to 30 characters long (*)
            Must be an Oracle reserved word

10.       Which CREATE TABLE statement will fail?   Mark for Review
(1) Points
            CREATE TABLE time_date (time NUMBER(9));
            CREATE TABLE time (time_id NUMBER(9));
            CREATE TABLE date_1 (date_1 DATE);
            CREATE TABLE date (date_id NUMBER(9)); (*)

11.       INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. True or False?  Mark for Review
(1) Points
            True (*)
            False

12.       Which of the following are valid Oracle datatypes?    Mark for Review
(1) Points
            DATE, BLOB, LOB, VARCHAR2
            TIMESTAMP, LOB, VARCHAR2, NUMBER
            DATE, TIMESTAMP WITH LOCAL TIME ZONE, BLOB (*)
            SYSDATE, TIMESTAMP, DATE, LOCAL TIME ZONE

13.       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?           Mark for Review
(1) Points
            TIMESTAMP
            INTERVAL YEAR TO MONTH
            DATETIME
            INTERVAL DAY TO SECOND (*)

14.       You are designing a table for the Sales department. You need to include a column that contains each sales total. Which data type should you specify for this column?        Mark for Review
(1) Points
            CHAR
            NUMBER (*)
            DATE
            VARCHAR2

15.       Which data types stores variable-length character data? Select two.    Mark for Review
(1) Points
                                    (Choose all correct answers)
            NCHAR
            CLOB (*)
            CHAR
            VARCHAR2 (*)

1.         You need to store the SEASONAL data in months and years. Which data type should you use?        Mark for Review
(1) Points
            INTERVAL YEAR TO MONTH (*)
            TIMESTAMP
            INTERVAL DAY TO SECOND
            DATE

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

 Mark for Review
(1) Points
            Today's date should be used if no value is provided for the sale date. (*)
            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.

3.         The TIMESTAMP data type allows what?       Mark for Review
(1) Points
            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.

4.         Which statement about data types is true?       Mark for Review
(1) Points
            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. (*)
            The TIMESTAMP data type is a character data type.
            The BFILE data type stores character data up to four gigabytes in the database.

5.         You are designing a table for the Sales department. You need to include a column that contains each sales total. Which data type should you specify for this column?        Mark for Review
(1) Points
            CHAR
            DATE
            NUMBER (*)
            VARCHAR2

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

 Mark for Review
(1) Points

            ALTER TABLE teams
REPLACE (mgr_id VARCHAR2(15));

            ALTER teams TABLE
MODIFY COLUMN (mgr_id VARCHAR2(15));

            ALTER TABLE teams
MODIFY (mgr_id VARCHAR2(15));
(*)

            You CANNOT modify the data type of the MGR_ID column.

            ALTER teams
MODIFY (mgr_id VARCHAR2(15));

7.         Evaluate this statement:
ALTER TABLE inventory
MODIFY backorder_amount NUMBER(8,2);

Which task will this statement accomplish?

 Mark for Review
(1) Points
            Alters the definition of the BACKORDER_AMOUNT column to NUMBER(2,8)
            Alters the definition of the BACKORDER_AMOUNT column to NUMBER
            Alters the definition of the BACKORDER_AMOUNT column to NUMBER(8 2)
            Alters the definition of the BACKORDER_AMOUNT column to NUMBER(8.2)
            Changes the definition of the BACKORDER_AMOUNT column to NUMBER(8,2) (*)

8.         Which statement about decreasing the width of a column is true?       Mark for Review
(1) Points

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

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

            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.

9.         The FLASHBACK QUERY statement can restore data back to a point in time before the last COMMIT. True or False?  Mark for Review
(1) Points
            True
            False (*)

10.       A column's data type can always be changed from NUMBER to VARCHAR2 but not from VARCHAR2 to NUMBER, provided the table is empty. True or False?       Mark for Review
(1) Points
            True
            False (*)

11.       Once they are created, external tables are accessed with normal SQL statements. (True or False?)     Mark for Review
(1) Points
            True (*)
            False

12.       CREATE TABLE student_table
    (id NUMBER(6),
     lname VARCHAR(20),
     fname VARCHAR(20),
     lunch_num NUMBER(4));
Which of the following statements best describes the above SQL statement:

 Mark for Review
(1) Points
            Creates a table named student_table with four columns: lname, fname, lunch, num
            Creates a table named student with four columns: id, lname, fname, lunch_num
            Creates a table named student_table with four columns: lname, fname, lunch, num
            Creates a table named student_table with four columns: id, lname, fname, lunch_num (*)

13.       Which statement about creating a table is true?            Mark for Review
(1) Points

            If no schema is explicitly included in a CREATE TABLE statement, the CREATE TABLE
statement will fail.

            With a CREATE TABLE statement, a table will always be created in the current user's schema.

            If no schema is explicitly included in a CREATE TABLE statement, the table is created in the current user's schema. (*)

            If a schema is explicitly included in a CREATE TABLE statement and the schema does not exist, it will be created.

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

            CREATE TABLE emp
AS SELECT employee_id, first_name, last_name, salary, department_id
FROM employees;
(*)

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

15.       Evaluate this CREATE TABLE statement:
1. CREATE TABLE customer#1 (
2. cust_1 NUMBER(9),
3. sales$ NUMBER(9),
4. 2date DATE DEFAULT SYSDATE);

Which line of this statement will cause an error?

 Mark for Review
(1) Points
            2
            4 (*)
            1

            3



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.
By Deni Ace at February 11, 2017


Section 15 Quiz
                (Answer all questions in this section)

1.            Evaluate this CREATE VIEW statement:
CREATE VIEW emp_view
AS SELECT SUM(salary)
FROM employees;

Which statement is true?

 Mark for Review
(1) Points
                You cannot update data in the EMPLOYEES table using the EMP_VIEW view. (*)
                You can update any data in the EMPLOYEES table using the EMP_VIEW view.
                You can update only the SALARY column in the EMPLOYEES table using the EMP_VIEW view.
                You can delete records from the EMPLOYEES table using the EMP_VIEW view.

2.            Which keyword(s) would you include in a CREATE VIEW statement to create the view whether or not the base table exists?            Mark for Review
(1) Points
                FORCE (*)
                WITH READ ONLY
                NOFORCE
                OR REPLACE

3.            Views must be used to select data from a table. As soon as a view is created on a table, you can no longer select directly from the table. True or False?       Mark for Review
(1) Points
                True
                False (*)

4.            A view can be used to keep a history record of old data from the underlying tables, so even if a row is deleted from a table, you can still select the row through the view. True or False?            Mark for Review
(1) Points
                True
                False (*)

5.            In order to query a database using a view, which of the following statements applies?   Mark for Review
(1) Points
                You can never see all the rows in the table through the view.
                You can retrieve data from a view as you would from any table. (*)
                The tables you are selecting from can be empty, yet the view still returns the original data from those tables.
                Use special VIEW SELECT keywords.

6.            If a database administrator wants to ensure that changes performed through a view do not violate existing constraints, which clause should he include when creating the view?      Mark for Review
(1) Points
                FORCE
                WITH CHECK OPTION (*)
                WITH READ ONLY
                WITH CONSTRAINT CHECK

7.            What is the purpose of including the WITH CHECK OPTION clause when creating a view?               Mark for Review
(1) Points
                To keep views form being queried by unauthorized persons
                To make sure that the parent table(s) actually exist
                To make sure that data is not duplicated in the view

                To insure that no rows are updated through the view that would prevent those rows from being returned by the view in the future. (*)

8.            Only one type of view exists. True or False?        Mark for Review
(1) Points
                True
                False (*)

9.            For a View created using the WITH CHECK OPTION keywords, which of the following statements are true?                Mark for Review
(1) Points
                Prohibits changing rows not returned by the subquery in the view definition. (*)
                The view will allow the user to check it against the data dictionary
                Prohibits DML actions without administrator CHECK approval
                Allows for DELETES from other tables, including ones not listed in subquery

10.          You cannot insert data through a view if the view includes ______.         Mark for Review
(1) Points
                A join
                A WHERE clause
                A column alias
                A GROUP BY clause (*)

11.          When you drop a view, the data it contains is also deleted. True or False?             Mark for Review
(1) Points
                True
                False (*)

12.          Evaluate this CREATE VIEW statement:
CREATE VIEW sales_view
AS SELECT customer_id, region, SUM(sales_amount)
FROM sales
WHERE region IN (10, 20, 30, 40)
GROUP BY region, customer_id;

Which statement is true?

 Mark for Review
(1) Points
                You can modify data in the SALES table using the SALES_VIEW view.
                You can only insert records into the SALES table using the SALES_VIEW view.
                The CREATE VIEW statement generates an error.
                You cannot modify data in the SALES table using the SALES_VIEW view. (*)

13.          You want to create a view based on the SALESREP table. You plan to grant access to this view to members of the Sales department. You want Sales employees to be able to update the SALESREP table through the view, which you plan to name SALESREP_VIEW. What should not be specified in your CREATE VIEW statement?         Mark for Review
(1) Points
                The AS keyword
                A GROUP BY clause (*)
                A WHERE clause
                The IN keyword

14.          How do you remove a view?      Mark for Review
(1) Points
                DELETE VIEW view_name
                REMOVE VIEW view_name
                DROP VIEW view_name (*)
                You cannot remove a view

15.          When you drop a table referenced by a view, the view is automatically dropped as well. True or False?  Mark for Review
(1) Points
                True
                False (*)

1.            Which statement about an inline view is true?   Mark for Review
(1) Points
                An inline view is a complex view.
                An inline view is a subquery in the FROM clause, often named with an alias. (*)
                An inline view is a schema object.
                An inline view can be used to perform DML operations.

2.            A Top-N Analysis is capable of ranking a top or bottom set of results. True or False?         Mark for Review
(1) Points
                True (*)
                False

3.            Which of these Keywords is typically used with a Top-N Analysis?             Mark for Review
(1) Points
                Number
                Rowid
                Rownum (*)
                Sequence

4.            You must create a view that will display the name, customer identification number, new balance, finance charge, and credit limit of all customers.
You issue this statement:
CREATE OR REPLACE VIEW CUST_CREDIT_V
AS SELECT c.last_name, c.customer_id, a.new_balance, a.finance_charge, a.credit_limit
FROM customers c, accounts a
WHERE c.account_id = a.account_id WITH READ ONLY;

Which type of SQL command can be issued on the CUST_CREDIT_V view?

 Mark for Review
(1) Points

                SELECT (*)
                UPDATE
                DELETE
                INSERT

5.            Evaluate this CREATE VIEW statement:
CREATE VIEW sales_view
AS SELECT customer_id, region, SUM(sales_amount)
FROM sales
WHERE region IN (10, 20, 30, 40)
GROUP BY region, customer_id;

Which statement is true?

 Mark for Review
(1) Points
                You can only insert records into the SALES table using the SALES_VIEW view.
                You can modify data in the SALES table using the SALES_VIEW view.
                You cannot modify data in the SALES table using the SALES_VIEW view. (*)
                The CREATE VIEW statement generates an error.

6.            You administer an Oracle database. Jack manages the Sales department. He and his employees often find it necessary to query the database to identify customers and their orders. He has asked you to create a view that will simplify this procedure for himself and his staff. The view should not accept INSERT, UPDATE, or DELETE operations. Which of the following statements should you issue?     Mark for Review
(1) Points

                CREATE VIEW sales_view
   AS (SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total
   FROM customers c, orders o
   WHERE c.custid = o.custid)
WITH READ ONLY;
(*)

                CREATE VIEW sales_view
AS (SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total
FROM customers c, orders o
WHERE c.custid = o.custid);

                CREATE VIEW sales_view
   AS (SELECT companyname, city, orderid, orderdate, total
   FROM customers, orders
   WHERE custid = custid)
WITH READ ONLY;

                CREATE VIEW sales_view
    (SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total
    FROM customers c, orders o
    WHERE c.custid = o.custid)
WITH READ ONLY;

7.            You cannot insert data through a view if the view includes ______.         Mark for Review
(1) Points
                A WHERE clause
                A GROUP BY clause (*)
                A column alias
                A join

8.            For a View created using the WITH CHECK OPTION keywords, which of the following statements are true?                Mark for Review
(1) Points
                Prohibits DML actions without administrator CHECK approval
                The view will allow the user to check it against the data dictionary
                Prohibits changing rows not returned by the subquery in the view definition. (*)
                Allows for DELETES from other tables, including ones not listed in subquery

9.            Which statement about performing DML operations on a view is true?  Mark for Review
(1) Points

                You can perform DML operations on a view that contains columns defined by expressions, such as COST + 1.

                You can perform DML operations on simple views. (*)

                You can perform DML operations on a view that contains the WITH READ ONLY option.

                You cannot perform DML operations on a view that contains the WITH CHECK OPTION clause.

10.          Which option would you use when creating a view to ensure that no DML operations occur on the view?                Mark for Review
(1) Points
                NOFORCE
                FORCE
                WITH ADMIN OPTION
                WITH READ ONLY (*)

11.          Unlike tables, views contain no data of their own. True or False?               Mark for Review
(1) Points
                True (*)
                False

12.          Views must be used to select data from a table. As soon as a view is created on a table, you can no longer select directly from the table. True or False?       Mark for Review
(1) Points
                True
                False (*)

13.          A view can be used to keep a history record of old data from the underlying tables, so even if a row is deleted from a table, you can still select the row through the view. True or False?            Mark for Review
(1) Points
                True
                False (*)

14.          Which of the following statements is a valid reason for using a view?      Mark for Review
(1) Points
                Views are used when you only want to restrict DML operations using a WITH CHECK OPTION.

                Views provide data independence for infrequent users and application programs. One view can be used to retrieve data from several tables. Views can be used to provide data security. (*)

                Views are not valid unless you have more than one user.

                Views allow access to the data because the view displays all of the columns from the table.

15.          You need to create a view on the SALES table, but the SALES table has not yet been created. Which statement is true?           Mark for Review
(1) Points

                You can create the table and the view at the same time using the FORCE option.
                You must create the SALES table before creating the view.

                You can use the FORCE option to create the view before the SALES table has been created. (*)

                By default, the view will be created even if the SALES table does not exist.

1.            Which of the following keywords cannot be used when creating a view?               Mark for Review
(1) Points
                HAVING
                WHERE
                ORDER BY (*)
                They are all valid keywords when creating views.

2.            A view can contain a select statement with a subquery. True or False?   Mark for Review
(1) Points
                True (*)
                False

3.            You need to create a view on the SALES table, but the SALES table has not yet been created. Which statement is true?           Mark for Review
(1) Points
                You must create the SALES table before creating the view.
                By default, the view will be created even if the SALES table does not exist.
                You can create the table and the view at the same time using the FORCE option.

                You can use the FORCE option to create the view before the SALES table has been created. (*)

4.            The FACULTY table contains these columns:
FACULTYID VARCHAR2(5) NOT NULL PRIMARY KEY
FIRST_NAME VARCHAR2(20)
LAST_NAME VARCHAR2(20)
ADDRESS VARCHAR2(35)
CITY VARCHAR2(15)
STATE VARCHAR2(2)
ZIP NUMBER(9)
TELEPHONE NUMBER(10)
STATUS VARCHAR2(2) NOT NULL

The COURSE table contains these columns:

COURSEID VARCHAR2(5) NOT NULL PRIMARY KEY
SUBJECT VARCHAR2(5)
TERM VARCHAR2(6)
FACULTYID VARCHAR2(5) NOT NULL FOREIGN KEY

You have been asked to compile a report that identifies all adjunct professors who will be teaching classes in the upcoming term. You want to create a view that will simplify the creation of this report. Which CREATE VIEW statements will accomplish this task?

 Mark for Review
(1) Points

                CREATE VIEW pt_view AS
   (SELECT first_name, last_name, status, courseid, subject, term
   FROM faculty f, course c
   WHERE f.facultyid = c.facultyid);
(*)

                CREATE VIEW pt_view IN    (SELECT first_name, last_name, status, courseid, subject, term
FROM faculty course);

                CREATE VIEW
   (SELECT first_name, last_name, status, courseid, subject, term
   FROM faculty, course
   WHERE facultyid = facultyid);

                CREATE VIEW pt_view
ON (SELECT first_name, last_name, status, courseid, subject, term
FROM faculty f and course c
WHERE f.facultyid = c.facultyid);

5.            What is one advantage of using views?  Mark for Review
(1) Points
                To be able to store the same data in more than one place
                To provide data dependence
                To provide restricted data access (*)

6.            You administer an Oracle database. Jack manages the Sales department. He and his employees often find it necessary to query the database to identify customers and their orders. He has asked you to create a view that will simplify this procedure for himself and his staff. The view should not accept INSERT, UPDATE, or DELETE operations. Which of the following statements should you issue?     Mark for Review
(1) Points

                CREATE VIEW sales_view
   AS (SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total
   FROM customers c, orders o
   WHERE c.custid = o.custid)
WITH READ ONLY;
(*)

                CREATE VIEW sales_view
   AS (SELECT companyname, city, orderid, orderdate, total
   FROM customers, orders
   WHERE custid = custid)
WITH READ ONLY;

                CREATE VIEW sales_view
    (SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total
    FROM customers c, orders o
    WHERE c.custid = o.custid)
WITH READ ONLY;

                CREATE VIEW sales_view
AS (SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total
FROM customers c, orders o
WHERE c.custid = o.custid);

7.            You can create a view if the view subquery contains an inline view. True or False?            Mark for Review
(1) Points
                True (*)
                False

8.            For a View created using the WITH CHECK OPTION keywords, which of the following statements are true?                Mark for Review
(1) Points
                Prohibits DML actions without administrator CHECK approval
                The view will allow the user to check it against the data dictionary
                Prohibits changing rows not returned by the subquery in the view definition. (*)
                Allows for DELETES from other tables, including ones not listed in subquery

9.            Only one type of view exists. True or False?        Mark for Review
(1) Points
                True
                False (*)

10.          What is the purpose of including the WITH CHECK OPTION clause when creating a view?               Mark for Review
(1) Points
                To make sure that data is not duplicated in the view

                To insure that no rows are updated through the view that would prevent those rows from being returned by the view in the future. (*)

                To keep views form being queried by unauthorized persons
                To make sure that the parent table(s) actually exist

11.          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)
COMMISSOIN NUMBER(7,2)
HIRE_DATE DATE

Which SELECT statement could be used to display the 10 lowest paid clerks that belong to department 70?

 Mark for Review
(1) Points

                SELECT ROWNUM "Ranking", last_name||' ,'||first_name "Employee", salary "Salary"
FROM (SELECT last_name, first_name, salary
     FROM employees
     ORDER BY salary)
WHERE ROWNUM <=10 AND job_id LIKE 'CLERK' AND department_id = 70;

                SELECT ROWNUM "Ranking",last_name||','||first_name "Employee", salary "Salary"
FROM (SELECT last_name, first_name, salary, job_id
     FROM employees
     WHERE job_id LIKE 'CLERK' AND department_id = 70
     ORDER BY salary)
WHERE ROWNUM <=10;
(*)

                SELECT ROWNUM "Ranking", last_name||' ,'||first_name "Employee", salary "Salary"
FROM (SELECT last_name, first_name, salary, job_id, dept_id
     FROM employees
     WHERE ROWNUM <=10
     ORDER BY salary)
WHERE job_id LIKE 'CLERK' AND department_id = 70;

                The only way is to use the data dictionary.

12.          Which of the following describes a top-N query?              Mark for Review
(1) Points

                A top-N query returns a limited result set, returning data based on highest or lowest criteria. (*)

                A top-N query returns a result set that is sorted according to the specified column values.
                A top-N query returns the top 15 records from the specified table.
                A top-N query returns the bottom 15 records from the specified table.

13.          Which statement about an inline view is true?   Mark for Review
(1) Points
                An inline view is a subquery in the FROM clause, often named with an alias. (*)
                An inline view can be used to perform DML operations.
                An inline view is a complex view.
                An inline view is a schema object.

14.          Which of these is not a valid type of View?          Mark for Review
(1) Points
                INLINE
                COMPLEX
                SIMPLE
                ONLINE (*)

15.          You must create a view that will display the name, customer identification number, new balance, finance charge, and credit limit of all customers.
You issue this statement:
CREATE OR REPLACE VIEW CUST_CREDIT_V
AS SELECT c.last_name, c.customer_id, a.new_balance, a.finance_charge, a.credit_limit
FROM customers c, accounts a
WHERE c.account_id = a.account_id WITH READ ONLY;

Which type of SQL command can be issued on the CUST_CREDIT_V view?

 Mark for Review
(1) Points
                UPDATE
                INSERT
                DELETE

                SELECT (*)

Section 16 Quiz Database Programming With SQL
Section 16 Quiz
            (Answer all questions in this section)

1.         Sequences can be used to: (Choose three)       Mark for Review
(1) Points
                                    (Choose all correct answers)
            Generate a range of numbers and optionally cycle through them again (*)
            Set a fixed interval between successively generated numbers. (*)
            Ensure primary key values will be unique and consecutive
            Guarantee that no primary key values are unused
            Ensure primary key values will be unique even though gaps may exist (*)

2.         Evaluate this CREATE SEQUENCE statement:
CREATE SEQUENCE line_item_id_seq INCREMENT BY -1;

Which statement is true?

 Mark for Review
(1) Points
            The statement will not execute successfully.
            The minimum value of the LINE_ITEM_ID_SEQ will be the smallest possible integer value.
            The starting value of the LINE_ITEM_ID_SEQ sequence will by -1.
            The sequence will generate sequential descending values. (*)

3.         Examine the code for creating this sequence:
CREATE SEQUENCE track_id_seq
INCREMENT BY 10
START WITH 1000 MAXVALUE 10000
What are the first three values that would be generated by the sequence?

 Mark for Review
(1) Points
            0, 1, 2
            1000, 1010, 1020 (*)
            1100, 1200, 1300
            100010011002

4.         You create a CUSTOMERS table in which CUSTOMER_ID is designated as a primary key. You want the values that are entered into the CUSTOMER_ID column to be generated automatically. Which of the following actions should you perform?   Mark for Review
(1) Points

            Do nothing. Oracle automatically generates unique values for columns that are defined as primary keys.

            Specify a UNIQUE constraint on the CUSTOMER_ID column.
            Create a synonym.
            Create a sequence. (*)

5.         You need to retrieve the next available value for the SALES_IDX sequence.
Which would you include in your SQL statement?      Mark for Review
(1) Points
            sales_idx.CURRVAL
            sales_idx.NEXTVAL (*)
            sales_idx.NEXT
            sales_idx

6.         Which statement would you use to modify the EMP_ID_SEQ sequence used to populate the EMPLOYEE_ID column in the EMPLOYEES table?   Mark for Review
(1) Points

            ALTER SEQUENCE emp_id_seq; (*)
            ALTER TABLE employees ;
            CREATE SEQUENCE emp_id_seq;
            ALTER SEQUENCE emp_id_seq.employee_id;

7.         Why do gaps in sequences occur?       Mark for Review
(1) Points
            A rollback is executed
            The system crashes
            The sequence is used in another table
            All of the above (*)

8.         You create a sequence with the following statement:
CREATE SEQUENCE my_emp_seq;

Which of the following statements about this sequence are true? (Choose two)

 Mark for Review
(1) Points
                                    (Choose all correct answers)
            The sequence will not cache a range of numbers in memory.
            MAXVALUE is 10^27 for an ascending sequence. (*)
            When the sequence exceeds its maximum value it will continue to generate numbers starting with MINVALUE.
            MINVALUE is equal to 1. (*)

9.         The EMPLOYEES table has an index named LN_IDX on the LAST_NAME column. You want to change this index so that it is on the FIRST_NAME column instead. Which SQL statement will do this?        Mark for Review
(1) Points
            ALTER INDEX ln_idx ON employees(first_name);
            ALTER INDEX ln_idx TO employees(first_name);
            ALTER INDEX ln_idx TO fn_idx ON employees(first_name);
            None of the above; you cannot ALTER an index. (*)

‘;10.     User Mary's schema contains an EMP table. Mary has Database Administrator privileges and executes the following statement:
CREATE PUBLIC SYNONYM emp FOR mary.emp;

User Susan now needs to SELECT from Mary's EMP table. Which of the following SQL statements can she use? (Choose two)

 Mark for Review
(1) Points

                                    (Choose all correct answers)
            SELECT * FROM emp; (*)
            SELECT * FROM mary.emp; (*)
            CREATE SYNONYM marys_emp FOR mary(emp);
            SELECT * FROM emp.mary;

11.       Barry creates a table named INVENTORY. Pam must be able to query the same table. Barry wants to enable Pam to query the table without being required to specify the table's schema. Which of the following should Barry create?    Mark for Review
(1) Points
            A view
            A synonym (*)
            An index
            A schema

12.       You need to determine the table name and column name(s) on which the SALES_IDX index is defined. Which data dictionary view would you query?            Mark for Review
(1) Points
            USER_OBJECTS
            USER_INDEXES
            USER_IND_COLUMNS (*)
            USER_TABLES

13.       The following indexes exist on the EMPLOYEES table:
A unique index on the EMPLOYEE_ID primary key column
A non-unique index on the JOB_ID column
A composite index on the FIRST_NAME and LAST_NAME columns.
If the EMPLOYEES table is dropped, which indexes are automatically dropped at the same time?

 Mark for Review
(1) Points
            EMP_ID only
            JOB_ID only
            DEPT_ID only
            EMP_ID and JOB_ID
            All Indexes (*)

14.       Unique indexes are automatically created on columns that have which two types of constraints?       Mark for Review
(1) Points
            NOT NULL and UNIQUE
            UNIQUE and PRIMARY KEY (*)
            UNIQUE and FOREIGN KEY
            PRIMARY KEY and FOREIGN KEY

15.       The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
DEPARTMENT_ID NUMBER Foreign Key to PRODUCT_ID column of the PRODUCT table
HIRE_DATE DATE DEFAULT SYSDATE
SALARY NUMBER (8,2) NOT NULL

On which column is an index automatically created for the EMPLOYEES table?

 Mark for Review
(1) Points
            DEPARTMENT_ID
            HIRE_DATE
            EMPLOYEE_ID (*)
            LAST_NAME
            SALARY

1.         Evaluate this CREATE SEQUENCE statement:
CREATE SEQUENCE order_id_seq NOCYCLE NOCACHE;

Which statement is true?

 Mark for Review
(1) Points
            The sequence will start with 1. (*)
            The sequence preallocates values and retains them in memory.
            The sequence has no maximum value.
            The sequence will continue to generate values after reaching its maximum value.

2.         In order to be able to generate primary key values that are not likely to contain gaps, which phrase should be included in the sequence creation statement?          Mark for Review
(1) Points
            MAXVALUE
            CACHE
            NOCACHE (*)

3.         You need to retrieve the next available value for the SALES_IDX sequence.
Which would you include in your SQL statement?      Mark for Review
(1) Points
            sales_idx.CURRVAL
            sales_idx.NEXT
            sales_idx
            sales_idx.NEXTVAL (*)

4.         Evaluate this statement:
CREATE SEQUENCE sales_item_id_seq
START WITH 101 MAXVALUE 9000090 CYCLE;

Which statement about this CREATE SEQUENCE statement is true?

 Mark for Review
(1) Points
            The sequence will generate sequence numbers starting with 101, but will not reuse numbers.
            The statement fails because no INCREMENT BY value is specified.
            The sequence will generate decrementing sequence numbers starting at 101.
            The sequence will reuse numbers and will start with 101. (*)

5.         You created the LOCATION_ID_SEQ sequence to generate sequential values for the LOCATION_ID column in the MANUFACTURERS table. You issue this statement:
ALTER TABLE manufacturers
MODIFY (location_id NUMBER(6));

Which statement about the LOCATION_ID_SEQ sequence is true?

 Mark for Review
(1) Points
            The sequence is unchanged. (*)
            The current value of the sequence is reset to zero.
            The sequence is deleted and must be recreated.
            The current value of the sequence is reset to the sequence's START WITH value.

6.         Which dictionary view would you query to display the number most recently generated by a sequence?            Mark for Review
(1) Points
            USER_CURRVALUES
            USER_TABLES
            USER_SEQUENCES (*)
            USER_OBJECTS

7.         Why do gaps in sequences occur?       Mark for Review
(1) Points
            A rollback is executed
            The system crashes
            The sequence is used in another table
            All of the above (*)

8.         Nextval and Currval are known as column aliases. True or False?      Mark for Review
(1) Points
            True
            False (*)

9.         Which of the following is created automatically by Oracle when a UNIQUE integrity constraint is created?           Mark for Review
(1) Points
            A PRIMARY KEY constraint
            A FOREIGN KEY constraint
            An index (*)
            A CHECK constraint

10.       Which statement would you use to remove the LAST_NAME_IDX index on the LAST_NAME column of the EMPLOYEES table?      Mark for Review
(1) Points
            DROP INDEX last_name_idx(last_name);

            ALTER TABLE employees
DROP INDEX last_name_idx;

            DROP INDEX last_name_idx(employees.last_name);

            DROP INDEX last_name_idx;(*)

11.       The following indexes exist on the EMPLOYEES table:
A unique index on the EMPLOYEE_ID primary key column
A non-unique index on the JOB_ID column
A composite index on the FIRST_NAME and LAST_NAME columns.
If the EMPLOYEES table is dropped, which indexes are automatically dropped at the same time?

 Mark for Review
(1) Points
            EMP_ID only
            JOB_ID only
            DEPT_ID only
            EMP_ID and JOB_ID
            All Indexes (*)

12.       All tables must have indexes on them otherwise they cannot be queried. True or False?         Mark for Review
(1) Points
            True
            False (*)

13.       Which of the following SQL statements shows a correct syntax example of creating a synonym accessible to all users of a database?   Mark for Review
(1) Points
            CREATE UNRESTRICTED SYNONYM emp FOR EMPLOYEES
            CREATE PUBLIC SYNONYM emp FOR EMPLOYEES (*)
            CREATE SHARED SYNONYM emp FOR EMPLOYEES
            CREATE SYNONYM emp FOR EMPLOYEES

14.       What is the correct syntax for creating an index?        Mark for Review
(1) Points
            CREATE index_name INDEX ON table_name.column_name;
            CREATE INDEX ON table_name(column_name);
            CREATE OR REPLACE INDEX index_name ON table_name(column_name);
            CREATE INDEX index_name ON table_name(column_name); (*)

15.       The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
DEPARTMENT_ID NUMBER Foreign Key to PRODUCT_ID column of the PRODUCT table
HIRE_DATE DATE DEFAULT SYSDATE
SALARY NUMBER (8,2) NOT NULL

On which column is an index automatically created for the EMPLOYEES table?

 Mark for Review
(1) Points
            LAST_NAME
            EMPLOYEE_ID (*)
            DEPARTMENT_ID
            HIRE_DATE
            SALARY

1.         What is the correct syntax for creating a private synonym d_sum for the view DEPT_SUM_VU?      Mark for Review
(1) Points

            CREATE d_sum SYNONYM
FOR dept_sum_vu;

            CREATE SYNONYM d_sum
FOR dept_sum_vu;(*)

            CREATE SYNONYM d_sum
ON dept_sum_vu;

            UPDATE dept_sum_vu
ON SYNONYM d_sum;

2.         What kind of INDEX is created by Oracle when you create a primary key?   Mark for Review
(1) Points
            UNIQUE INDEX (*)
            NONUNIQUE INDEX
            INDEX
            Oracle cannot create indexes automatically.

3.         Indexes can be used to speed up queries. True or False?         Mark for Review
(1) Points
            True (*)
            False

4.         When creating an index on one or more columns of a table, which of the following statements are true?
(Choose two)   Mark for Review
(1) Points
                                    (Choose all correct answers)

            You should create an index if one or more columns are frequently used together in a join condition. (*)

            You should create an index if the table is large and most queries are expected to retrieve less than 2 to 4 percent of the rows. (*)

            You should create an index if the table is very small.

            You should always create an index on tables that are frequently updated.

5.         The CUSTOMERS table exists in user Mary's schema. Which statement should you use to create a synonym for all database users on the CUSTOMERS table?   Mark for Review
(1) Points
            CREATE PUBLIC SYNONYM cust ON mary.customers;
            CREATE PUBLIC SYNONYM cust FOR mary.customers;(*)
            CREATE SYNONYM cust ON mary.customers FOR PUBLIC;
            CREATE SYNONYM cust ON mary.customers;
GRANT SELECT ON cust TO PUBLIC;

6.         Which of the following best describes the function of an index?        Mark for Review
(1) Points
            An index can run statement blocks when DML actions occur against a table.
            An index can reduce the time required to grant multiple privileges to users.
            An index can prevent users from viewing certain data in a table.
            An index can increase the performance of SQL queries that search large tables. (*)

7.         Which of the following is created automatically by Oracle when a UNIQUE integrity constraint is created?           Mark for Review
(1) Points
            A PRIMARY KEY constraint
            An index (*)
            A CHECK constraint
            A FOREIGN KEY constraint

8.         Which statement would you use to modify the EMP_ID_SEQ sequence used to populate the EMPLOYEE_ID column in the EMPLOYEES table?   Mark for Review
(1) Points
            ALTER TABLE employees ;
            CREATE SEQUENCE emp_id_seq;
            ALTER SEQUENCE emp_id_seq; (*)
            ALTER SEQUENCE emp_id_seq.employee_id;

9.         The ALTER SEQUENCE statement can be used to:    Mark for Review
(1) Points
            Change the maximum value to a lower number than was last used
            Change the amount a sequence increments each time a number is generated (*)
            Change the START WITH value of a sequence
            Change the name of the sequence

10.       A sequence is a database object. True or False?          Mark for Review
(1) Points
            True (*)
            False

11.       You issue this statement:
ALTER SEQUENCE po_sequence INCREMENT BY 2;

Which statement is true?

 Mark for Review
(1) Points
            Sequence numbers will be cached.
            Future sequence numbers generated will increase by 2 each time a number is generated. (*)
            If the PO_SEQUENCE sequence does not exist, it will be created.
            The statement fails if the current value of the sequence is greater than the START WITH value.

12.       Sequences can be used to: (Choose three)       Mark for Review
(1) Points
                                    (Choose all correct answers)
            Generate a range of numbers and optionally cycle through them again (*)
            Guarantee that no primary key values are unused
            Set a fixed interval between successively generated numbers. (*)
            Ensure primary key values will be unique even though gaps may exist (*)
            Ensure primary key values will be unique and consecutive

13.       Evaluate this CREATE SEQUENCE statement:
CREATE SEQUENCE line_item_id_seq CYCLE;

Which statement is true?

 Mark for Review
(1) Points
            The sequence cannot be used with more than one table.
            The sequence preallocates values and retains them in memory.
            The sequence cannot generate additional values after reaching its maximum value.
            The sequence will continue to generate values after the maximum sequence value has been generated. (*)

14.       Evaluate this CREATE SEQUENCE statement:
CREATE SEQUENCE order_id_seq NOCYCLE NOCACHE;

Which statement is true?

 Mark for Review
(1) Points
            The sequence will start with 1. (*)
            The sequence will continue to generate values after reaching its maximum value.
            The sequence has no maximum value.
            The sequence preallocates values and retains them in memory.

15.       You created the LOCATION_ID_SEQ sequence to generate sequential values for the LOCATION_ID column in the MANUFACTURERS table. You issue this statement:
ALTER TABLE manufacturers
MODIFY (location_id NUMBER(6));

Which statement about the LOCATION_ID_SEQ sequence is true?

 Mark for Review
(1) Points
            The current value of the sequence is reset to the sequence's START WITH value.
            The sequence is deleted and must be recreated.
            The current value of the sequence is reset to zero.
            The sequence is unchanged. (*)

1.         Creating a sequence with NOCACHE ensures that all numbers in the sequence's range will be used successfully. True or False?     Mark for Review
(1) Points
            True
            False (*)

2.         Which of the following best describes the function of the NEXTVAL virtual column?          Mark for Review
(1) Points

            The NEXTVAL virtual column displays the order in which Oracle retrieves row data from a table.

            The NEXTVAL virtual column returns the integer that was most recently supplied by the sequence.

            The NEXTVAL virtual column increments a sequence by a predetermined value. (*)

            The NEXTVAL virtual column displays only the physical locations of the rows in a table.

3.         Evaluate this CREATE SEQUENCE statement:
CREATE SEQUENCE line_item_id_seq INCREMENT BY -1;

Which statement is true?

 Mark for Review
(1) Points
            The minimum value of the LINE_ITEM_ID_SEQ will be the smallest possible integer value.
            The starting value of the LINE_ITEM_ID_SEQ sequence will by -1.
            The statement will not execute successfully.
            The sequence will generate sequential descending values. (*)

4.         Why do gaps in sequences occur?       Mark for Review
(1) Points
            A rollback is executed
            The system crashes
            The sequence is used in another table
            All of the above (*)

5.         When used in a CREATE SEQUENCE statement, which keyword specifies that a range of sequence values will be preloaded into memory?           Mark for Review
(1) Points
            LOAD
            NOCYCLE
            NOCACHE
            CACHE (*)
            MEMORY

6.         Which pseudocolumn returns the latest value supplied by a sequence?           Mark for Review
(1) Points
            NEXT
            CURRENT
            NEXTVAL
            CURRVAL (*)

7.         Which statement would you use to remove the EMP_ID_SEQ sequence?       Mark for Review
(1) Points
            ALTER SEQUENCE emp_id_seq;
            DROP SEQUENCE emp_id_seq; (*)
            REMOVE SEQUENCE emp_id_seq;
            DELETE SEQUENCE emp_id_seq;

8.         Evaluate this statement:
CREATE SEQUENCE line_item_id_seq
MINVALUE 100 MAXVALUE 130 INCREMENT BY -10 CYCLE;

What will be the first five numbers generated by this sequence?

 Mark for Review
(1) Points
            130120110100130
            The fifth number cannot be generated.
            100110120130100
            The CREATE SEQUENCE statement will fail because a START WITH value was not specified. (*)

9.         The CLIENTS table contains these columns:
CLIENT_ID NUMBER(4) NOT NULL PRIMARY KEY
LAST_NAME VARCHAR2(15)
FIRST_NAME VARCHAR2(10)
CITY VARCHAR2(15)
STATE VARCHAR2(2)

You want to create an index named ADDRESS_INDEX on the CITY and STATE columns of the CLIENTS table. You execute this statement:

CREATE INDEX clients
ON address_index (city, state);

Which result does this statement accomplish?

 Mark for Review
(1) Points
            An index named CLIENTS is created on the CITY and STATE columns.
            An index named CLIENTS_INDEX is created on the CLIENTS table.
            An error message is produced, and no index is created. (*)
            An index named ADDRESS_INDEX is created on the CITY and STATE columns.

10.       In SQL what is a synonym?     Mark for Review
(1) Points
            A table with the same number of columns as another table
            A table with the same name as another view
            A different name for a table, view, or other database object (*)
            A table that must be qualified with a username

11.       Indexes can be used to speed up queries. True or False?         Mark for Review
(1) Points
            True (*)
            False

12.       Barry creates a table named INVENTORY. Pam must be able to query the same table. Barry wants to enable Pam to query the table without being required to specify the table's schema. Which of the following should Barry create?    Mark for Review
(1) Points
            An index
            A view
            A synonym (*)
            A schema

13.       The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
DEPARTMENT_ID NUMBER Foreign Key to PRODUCT_ID column of the PRODUCT table
HIRE_DATE DATE DEFAULT SYSDATE
SALARY NUMBER (8,2) NOT NULL

On which column is an index automatically created for the EMPLOYEES table?

 Mark for Review
(1) Points
            EMPLOYEE_ID (*)
            LAST_NAME
            HIRE_DATE
            SALARY
            DEPARTMENT_ID

14.       The following indexes exist on the EMPLOYEES table:
A unique index on the EMPLOYEE_ID primary key column
A non-unique index on the JOB_ID column
A composite index on the FIRST_NAME and LAST_NAME columns.
If the EMPLOYEES table is dropped, which indexes are automatically dropped at the same time?

 Mark for Review
(1) Points
            EMP_ID only
            JOB_ID only
            DEPT_ID only
            EMP_ID and JOB_ID
            All Indexes (*)

15.       You want to speed up the following query by creating an index:
SELECT * FROM employees WHERE (salary * 12) > 100000;

Which of the following will achieve this?

 Mark for Review
(1) Points
            Create an index on (salary).
            Create a function_based index on ((salary * 12) > 100000).
            Create a composite index on (salary,12).
            Create a function-based index on (salary * 12). (*)

1.         Which of the following SQL statements will display the index name, table name, and the uniqueness of the index for all indexes on the EMPLOYEES table?  Mark for Review
(1) Points

            SELECT index_name, table_name, uniqueness
FROM user_indexes
WHERE index = EMPLOYEES;

            CREATE index_name, table_name, uniqueness
FROM user_indexes
WHERE table_name = 'EMPLOYEES';

            SELECT index_name, table_name, uniqueness
FROM 'EMPLOYEES';

            SELECT index_name, table_name, uniqueness
FROM user_indexes
WHERE table_name = 'EMPLOYEES';
(*)

2.         Which statement would you use to remove the LAST_NAME_IDX index on the LAST_NAME column of the EMPLOYEES table?      Mark for Review
(1) Points
            DROP INDEX last_name_idx(last_name);

            ALTER TABLE employees
DROP INDEX last_name_idx;

            DROP INDEX last_name_idx(employees.last_name);

            DROP INDEX last_name_idx;
(*)

3.         Indexes can be used to speed up queries. True or False?         Mark for Review
(1) Points
            True (*)
            False

4.         As user Julie, you issue this statement:
CREATE SYNONYM emp FOR sam.employees;

Which task was accomplished by this statement?

 Mark for Review
(1) Points
            You created a public synonym on the EMPLOYEES table owned by user Sam.
            You created a public synonym on the EMP table owned by user Sam.
            You created a private synonym on the EMPLOYEES table that you own.
            You created a private synonym on the EMPLOYEES table owned by user Sam. (*)
5.         For which column would you create an index?           Mark for Review
(1) Points
            A column that is updated frequently
            A column that is infrequently used as a query search condition
            A column which has only 4 distinct values.
            A column with a large number of null values (*)

6.         You want to create a composite index on the FIRST_NAME and LAST_NAME columns of the EMPLOYEES table. Which SQL statement will accomplish this task? Mark for Review
(1) Points

            CREATE INDEX fl_idx ON employees(first_name);
CREATE INDEX fl_idx ON employees(last_name);

            CREATE INDEX fl_idx
ON employees(first_name || last_name);

            CREATE INDEX fl_idx
ON employees(first_name), employees(last_name);

            CREATE INDEX fl_idx
ON employees(first_name,last_name);
(*)

7.         What kind of INDEX is created by Oracle when you create a primary key?   Mark for Review
(1) Points
            UNIQUE INDEX (*)
            NONUNIQUE INDEX
            INDEX
            Oracle cannot create indexes automatically.

8.         When creating a sequence, which keyword or option specifies the minimum sequence value?           Mark for Review
(1) Points
            CYCLE
            MINVALUE (*)
            MAXVALUE
            NOMAXVALUE

9.         You create a CUSTOMERS table in which CUSTOMER_ID is designated as a primary key. You want the values that are entered into the CUSTOMER_ID column to be generated automatically. Which of the following actions should you perform?   Mark for Review
(1) Points
            Specify a UNIQUE constraint on the CUSTOMER_ID column.

            Create a sequence. (*)

            Do nothing. Oracle automatically generates unique values for columns that are defined as primary keys.

            Create a synonym.

10.       Evaluate this statement:
CREATE SEQUENCE sales_item_id_seq
START WITH 101 MAXVALUE 9000090 CYCLE;

Which statement about this CREATE SEQUENCE statement is true?

 Mark for Review
(1) Points

            The sequence will generate sequence numbers starting with 101, but will not reuse numbers.
            The sequence will reuse numbers and will start with 101. (*)
            The statement fails because no INCREMENT BY value is specified.
            The sequence will generate decrementing sequence numbers starting at 101.

11.       Creating a sequence with NOCACHE ensures that all numbers in the sequence's range will be used successfully. True or False?     Mark for Review
(1) Points
            True
            False (*)

12.       When used in a CREATE SEQUENCE statement, which keyword specifies that a range of sequence values will be preloaded into memory?           Mark for Review
(1) Points
            NOCYCLE
            NOCACHE
            CACHE (*)
            LOAD
            MEMORY

13.       A sequence is a database object. True or False?          Mark for Review
(1) Points
            True (*)
            False

14.       When you alter a sequence, a new increased MAXVALUE can be entered without changing the existing number order. True or False?   Mark for Review
(1) Points


            True (*)
            False

15.       CURRVAL is a pseudocolumn used to refer to a sequence number that the current user has just generated by referencing NEXTVAL. True or False?      Mark for Review
(1) Points
            True (*)
            False

1.         You need to determine the table name and column name(s) on which the SALES_IDX index is defined. Which data dictionary view would you query?            Mark for Review
(1) Points
            USER_INDEXES
            USER_OBJECTS
            USER_TABLES
            USER_IND_COLUMNS (*)

2.         The CLIENTS table contains these columns:
CLIENT_ID NUMBER(4) NOT NULL PRIMARY KEY
LAST_NAME VARCHAR2(15)
FIRST_NAME VARCHAR2(10)
CITY VARCHAR2(15)
STATE VARCHAR2(2)

You want to create an index named ADDRESS_INDEX on the CITY and STATE columns of the CLIENTS table. You execute this statement:

CREATE INDEX clients
ON address_index (city, state);

Which result does this statement accomplish?

 Mark for Review
(1) Points
            An index named CLIENTS is created on the CITY and STATE columns.
            An index named CLIENTS_INDEX is created on the CLIENTS table.
            An index named ADDRESS_INDEX is created on the CITY and STATE columns.
            An error message is produced, and no index is created. (*)

3.         What is the correct syntax for creating a private synonym d_sum for the view DEPT_SUM_VU?      Mark for Review
(1) Points

            UPDATE dept_sum_vu
ON SYNONYM d_sum;

            CREATE SYNONYM d_sum
ON dept_sum_vu;

            CREATE d_sum SYNONYM
FOR dept_sum_vu;

            CREATE SYNONYM d_sum
FOR dept_sum_vu;
(*)

4.         All tables must have indexes on them otherwise they cannot be queried. True or False?         Mark for Review
(1) Points
            True
            False (*)

5.         As user Julie, you issue this statement:
CREATE SYNONYM emp FOR sam.employees;

Which task was accomplished by this statement?

 Mark for Review
(1) Points
            You created a public synonym on the EMP table owned by user Sam.
            You created a private synonym on the EMPLOYEES table that you own.
            You created a private synonym on the EMPLOYEES table owned by user Sam. (*)
            You created a public synonym on the EMPLOYEES table owned by user Sam.

6.         Which of the following SQL statements will display the index name, table name, and the uniqueness of the index for all indexes on the EMPLOYEES table?  Mark for Review
(1) Points

            SELECT index_name, table_name, uniqueness
FROM 'EMPLOYEES';

            SELECT index_name, table_name, uniqueness
FROM user_indexes
WHERE index = EMPLOYEES;

            SELECT index_name, table_name, uniqueness
FROM user_indexes
WHERE table_name = 'EMPLOYEES';
(*)

            CREATE index_name, table_name, uniqueness
FROM user_indexes
WHERE table_name = 'EMPLOYEES';

7.         Which of the following is created automatically by Oracle when a UNIQUE integrity constraint is created?           Mark for Review
(1) Points
            An index (*)
            A CHECK constraint
            A PRIMARY KEY constraint
            A FOREIGN KEY constraint

8.         Examine the code for creating this sequence:
CREATE SEQUENCE track_id_seq
INCREMENT BY 10
START WITH 1000 MAXVALUE 10000
What are the first three values that would be generated by the sequence?

 Mark for Review
(1) Points
            100010011002
            1000, 1010, 1020 (*)
            0, 1, 2
            1100, 1200, 1300

9.         In order to be able to generate primary key values that are not likely to contain gaps, which phrase should be included in the sequence creation statement?          Mark for Review
(1) Points
            NOCACHE (*)
            CACHE
            MAXVALUE

10.       Which keyword is used to modify a sequence?           Mark for Review
(1) Points
            Alter (*)
            Update
            Change
            Create

11.       Evaluate this statement:
SELECT po_itemid_seq.CURRVAL
FROM dual;

What does this statement accomplish?

 Mark for Review
(1) Points
            It displays the current value of the PO_ITEM_ID_SEQ sequence. (*)
            It displays the next available value of the PO_ITEM_ID_SEQ sequence.
            It resets the current value of the PO_ITEM_ID_SEQ sequence.
            It sets the current value of the PO_ITEM_ID_SEQ sequence to the value of the PO_ITEMID column.

12.       Which is the correct syntax for specifying a maximum value in a sequence?  Mark for Review
(1) Points
            Maxval
            Max_value
            Maxvalue (*)
            Maximumvalue

13.       Which statement would you use to remove the EMP_ID_SEQ sequence?       Mark for Review
(1) Points
            DELETE SEQUENCE emp_id_seq;
            DROP SEQUENCE emp_id_seq; (*)
            REMOVE SEQUENCE emp_id_seq;
            ALTER SEQUENCE emp_id_seq;

14.       When you alter a sequence, a new increased MAXVALUE can be entered without changing the existing number order. True or False?   Mark for Review
(1) Points
            True (*)
            False

15.       A gap can occur in a sequence because a user generated a number from the sequence and then rolled back the transaction. True or False?     Mark for Review
(1) Points
            True (*)

            False

Section 17 Quiz
            (Answer all questions in this section)

1.         Regular expressions used as check constraints are another way to ensure data is formatted correctly prior to being written into the database table. True or False?            Mark for Review
(1) Points
            True (*)
            False

2.         Regular expressions are a method of describing both simple and complex patterns for searching and manipulating. True or False?    Mark for Review
(1) Points
            True (*)
            False

3.         REGULAR EXPRESSIONS can be used as part of a contraint definition. (True or False?)     Mark for Review
(1) Points
            True (*)
            False

4.         A role can be granted to another role. True or False?   Mark for Review
(1) Points
            True (*)
            False

5.         Which of the following simplifies the administration of privileges?    Mark for Review
(1) Points
            A role (*)
            A trigger
            An index
            A view

6.         Which keyword would you use to grant an object privilege to all database users?       Mark for Review
(1) Points
            ADMIN
            PUBLIC (*)
            ALL
            USERS

7.         User CRAIG creates a view named INVENTORY_V, which is based on the INVENTORY table. CRAIG wants to make this view available for querying to all database users. Which of the following actions should CRAIG perform?         Mark for Review
(1) Points

            He is not required to take any action because, by default, all database users can automatically access views.

            He must grant each user the SELECT privilege on both the INVENTORY table and INVENTORY_V view.

            He should assign the SELECT privilege to all database users for INVENTORY_V view. (*)

            He should assign the SELECT privilege to all database users for the INVENTORY table.

8.         If you are granted privileges to your friend's object, by default you may also grant access to this same object to other users. True or False?    Mark for Review
(1) Points
            True
            False (*)

9.         Which data dictionary view shows which system privileges have been granted to a user?       Mark for Review
(1) Points

            USER_SYSTEM_PRIVS
            USER_TAB_PRIVS
            USER_SYS_PRIVS (*)
            USER_SYSTEM_PRIVILEGES

10.       A Schema is a collection of Objects such as Tables, Views, and Sequences. True or False?   Mark for Review
(1) Points
            True (*)
            False

11.       The database administrator wants to allow user Marco to create new tables in his own schema. Which privilege should be granted to Marco? Mark for Review
(1) Points
            CREATE OBJECT
            CREATE ANY TABLE
            CREATE TABLE (*)
            SELECT

12.       You are the database administrator. You want to create a new user JONES with a password of MARK, and allow this user to create his own tables. Which of the following should you execute?      Mark for Review
(1) Points

            CREATE USER jones IDENTIFIED BY mark;
GRANT CREATE SESSION TO jones;

            CREATE USER jones IDENTIFIED BY mark;
GRANT CREATE SESSION TO jones;
GRANT CREATE TABLE TO jones;
(*)

            CREATE USER jones IDENTIFIED BY mark;
GRANT CREATE TABLE TO jones;

            GRANT CREATE SESSION TO jones;
GRANT CREATE TABLE TO jones;

13.       Which Object Privilege (other than Alter) can be granted to a Sequence?        Mark for Review
(1) Points
            INSERT
            DELETE
            UPDATE
            SELECT (*)

14.       Object privileges are:   Mark for Review
(1) Points
            Required to gain access to the database.
            Required to manipulate the content of objects in the database. (*)
            Named groups of related privileges given to a user.
            A collection of objects, such as tables, views, and sequences.

15.       The following table shows some of the output from one of the data dictionary views. Which view is being queried?
USERNAME    PRIVILEGE     ADMIN_OPTION
USCA_ORACLE_SQL01_S08            CREATE          VIEW NO
USCA_ORACLE_SQL01_S08            CREATE          TABLE NO
USCA_ORACLE_SQL01_S08            CREATE          SYNONYM NO
USCA_ORACLE_SQL01_S08            CREATE          TRIGGER NO
USCA_ORACLE_SQL01_S08            CREATE          SEQUENCE NO
USCA_ORACLE_SQL01_S08            CREATE          DATABASE NO
 Mark for Review
(1) Points
            user_tab_privs_recd (lists object privileges granted to the user)
            role_sys_privs (lists system privileges granted to roles)
            role_tab_privs (lists table privileges granted to roles)
            user_sys_privs (lists system privileges granted to the user) (*)

1.         Which of the following best describes a role in an Oracle database?   Mark for Review
(1) Points
            A role is a type of system privilege.
            A role is a name for a group of privileges. (*)
            A role is the part that a user plays in querying the database.
            A role is an object privilege which allows a user to update a table.

2.         A schema is:    Mark for Review
(1) Points
            A named group of related privileges given to a user.
            A collection of objects, such as tables, views, and sequences. (*)
            Required to gain access to the database.
            Required to manipulate the content of objects in the database.

3.         System privileges are:  Mark for Review
(1) Points
            Required to manipulate the content of objects in the database.
            Named groups of related privileges given to a user.
            Required to gain access to the database. (*)
            A collection of objects, such as tables, views, and sequences.

4.         You grant user AMY the CREATE SESSION privilege. Which type of privilege have you granted to AMY?  Mark for Review
(1) Points
            A user privilege
            An object privilege
            An access privilege
            A system privilege (*)

5.         User ADAM has successfully logged on to the database in the past, but today he receives an error message stating that (although he has entered his password correctly) he cannot log on. What is the most likely cause of the problem? Mark for Review
(1) Points
            One or more object privileges have been REVOKEd from Adam.
            ADAM's user account has been removed from the database.
            ADAM's CREATE USER privilege has been revoked.
            ADAM's CREATE SESSION privilege has been revoked. (*)

6.         Which of the following are system privileges?
(Choose two)   Mark for Review
(1) Points
                                    (Choose all correct answers)
            INDEX
            UPDATE
            CREATE TABLE (*)
            CREATE SYNONYM (*)

7.         _________________ are special characters that have a special meaning, such as a wildcard character, a repeating character, a non-matching character, or a range of characters. You can use several of these symbols in pattern matching.         Mark for Review
(1) Points
            Reference checks
            Clip Art
            Alphanumeric values
            Meta characters (*)

8.         Regular expressions used as check constraints are another way to ensure data is formatted correctly prior to being written into the database table. True or False?            Mark for Review
(1) Points
            True (*)
            False

9.         REGULAR EXPRESSIONS does exactly the same as LIKE--no more and no less. (True or False?)   Mark for Review
(1) Points
            True
            False (*)

10.       A role can be granted to another role. True or False?   Mark for Review
(1) Points
            True (*)
            False

11.       Which of the following best describes the purpose of the REFERENCES object privilege on a table?            Mark for Review
(1) Points
            It allows the user to create new tables which contain the same data as the referenced table.
            It allows a user's session to read from the table but only so that foreign key constraints can be checked.
            It allows a user to refer to the table in a SELECT statement.
            It allows a user to create foreign key constraints on the table. (*)

12.       When a user is logged into one database, he is restricted to working with objects found in that database. True or False?  Mark for Review
(1) Points
            True
            False (*)

13.       What Oracle feature simplifies the process of granting and revoking privileges?         Mark for Review
(1) Points
            Object
            Data dictionary
            Role (*)
            Schema

14.       Which of the following statements about granting object privileges is false?   Mark for Review
(1) Points
            Object privileges can only be granted through roles. (*)

            An object owner can grant any object privilege on the object to any other user or role of the database.

            To grant privileges on an object, the object must be in your own schema, or you must have been granted the object privileges WITH GRANT OPTION.

            The owner of an object automatically acquires all object privileges on that object.

15.       When granting an object privilege, which option would you include to allow the grantee to grant the privilege to another user?         Mark for Review
(1) Points
            WITH GRANT OPTION (*)
            WITH ADMIN OPTION
            PUBLIC
            FORCE

1.         Which of the following best describes a role in an Oracle database?   Mark for Review
(1) Points
            A role is a name for a group of privileges. (*)
            A role is a type of system privilege.
            A role is an object privilege which allows a user to update a table.
            A role is the part that a user plays in querying the database.

2.         Which of the following are system privileges?
(Choose two)   Mark for Review
(1) Points
                                    (Choose all correct answers)
            CREATE SYNONYM (*)
            UPDATE
            CREATE TABLE (*)
            INDEX

3.         User ADAM has successfully logged on to the database in the past, but today he receives an error message stating that (although he has entered his password correctly) he cannot log on. What is the most likely cause of the problem? Mark for Review
(1) Points
            ADAM's CREATE USER privilege has been revoked.
            One or more object privileges have been REVOKEd from Adam.
            ADAM's user account has been removed from the database.
            ADAM's CREATE SESSION privilege has been revoked. (*)

4.         Which of the following are object privileges? (Choose two)    Mark for Review
(1) Points
                                    (Choose all correct answers)
            CREATE TABLE
            INSERT (*)
            SELECT (*)
            DROP TABLE

5.         By Controlling User Access with Oracle Database Security, you can give access to specific Objects in the Database. True or False?          Mark for Review
(1) Points
            True (*)
            False

6.         User JAMES has created a CUSTOMERS table and wants to allow all other users to SELECT from it. Which command should JAMES use to do this?          Mark for Review
(1) Points
            CREATE PUBLIC SYNONYM customers FOR james.customers;
            GRANT SELECT ON customers TO ALL;
            GRANT SELECT ON customers TO PUBLIC; (*)
            GRANT customers(SELECT) TO PUBLIC;

7.         Parentheses are not used to identify the sub expressions within the expression. True or False?           Mark for Review
(1) Points
            True
            False (*)

8.         REGULAR EXPRESSIONS can be used on CHAR, CLOB, and VARCHAR2 datatypes? (True or False)            Mark for Review
(1) Points
            True (*)
            False

9.         REGULAR EXPRESSIONS does exactly the same as LIKE--no more and no less. (True or False?)   Mark for Review
(1) Points
            True
            False (*)

10.       Roles are:         Mark for Review
(1) Points
            Required to gain access to the database.
            Named groups of related privileges given to a user or another role. (*)
            A collection of objects, such as tables, views, and sequences.
            Required to manipulate the content of objects in the database.

11.       Which data dictionary view shows which system privileges have been granted to a user?       Mark for Review
(1) Points
            USER_SYS_PRIVS (*)
            USER_SYSTEM_PRIVS
            USER_SYSTEM_PRIVILEGES
            USER_TAB_PRIVS

12.       Which statement would you use to remove an object privilege granted to a user?       Mark for Review
(1) Points
            ALTER USER
            REMOVE
            DROP
            REVOKE (*)

13.       You need to grant user BOB SELECT privileges on the EMPLOYEES table. You want to allow BOB to grant this privileges to other users. Which statement should you use? Mark for Review
(1) Points

            GRANT SELECT ON employees TO bob;
            GRANT SELECT ON employees TO bob WITH ADMIN OPTION;
            GRANT SELECT ON employees TO bob WITH GRANT OPTION; (*)
            GRANT SELECT ON employees TO PUBLIC WITH GRANT OPTION;

14.       If you are granted privileges to your friend's object, by default you may also grant access to this same object to other users. True or False?    Mark for Review
(1) Points
            True
            False (*)

15.       User BOB's schema contains an EMPLOYEES table. BOB executes the following statement:
GRANT SELECT ON employees TO mary WITH GRANT OPTION;

Which of the following statements can MARY now execute successfully? (Choose two)

 Mark for Review
(1) Points
                                    (Choose all correct answers)
            DROP TABLE bob.employees;
            GRANT SELECT ON bob.employees TO PUBLIC; (*)
            REVOKE SELECT ON bob.employees FROM bob;
            SELECT FROM bob.employees; (*)

1.         REGULAR EXPRESSIONS can be used as part of a contraint definition. (True or False?)     Mark for Review
(1) Points
            True (*)
            False

2.         Select the correct REGULAR EXPRESSION functions: (Choose two) Mark for Review
(1) Points
                                    (Choose all correct answers)
            REGEXP_INSTR, REGEXP_SUBSTR (*)
            REGEXP_LIKE, REGEXP_NEAR
            REGEXP_LIKE, REGEXP_REPLACE (*)
            REGEXP_REPLACE, REGEXP_REFORM

3.         Which of these SQL functions used to manipulate strings is NOT a valid regular expression function ?            Mark for Review
(1) Points
            REGEXP_LIKE
            REGEXP (*)
            REGEXP_REPLACE
            REGEXP_SUBSTR

4.         By Controlling User Access with Oracle Database Security, you can give access to specific Objects in the Database. True or False?          Mark for Review
(1) Points
            True (*)
            False

5.         You want to grant privileges to user CHAN that will allow CHAN to update the data in the EMPLOYEES table. Which type of privileges will you grant to CHAN?        Mark for Review
(1) Points
            User privileges
            Administrator privileges
            System privileges
            Object privileges (*)

6.         Which of the following privileges must be assigned to a user account in order for that user to connect to an Oracle database?     Mark for Review
(1) Points
            ALTER SESSION
            OPEN SESSION
            CREATE SESSION (*)
            RESTRICTED SESSION

7.         The following table shows some of the output from one of the data dictionary views. Which view is being queried?
USERNAME    PRIVILEGE     ADMIN_OPTION
USCA_ORACLE_SQL01_S08            CREATE          VIEW NO
USCA_ORACLE_SQL01_S08            CREATE          TABLE NO
USCA_ORACLE_SQL01_S08            CREATE          SYNONYM NO
USCA_ORACLE_SQL01_S08            CREATE          TRIGGER NO
USCA_ORACLE_SQL01_S08            CREATE          SEQUENCE NO
USCA_ORACLE_SQL01_S08            CREATE          DATABASE NO
 Mark for Review
(1) Points
            user_sys_privs (lists system privileges granted to the user) (*)
            role_tab_privs (lists table privileges granted to roles)
            user_tab_privs_recd (lists object privileges granted to the user)
            role_sys_privs (lists system privileges granted to roles)

8.         Which of the following best describes a role in an Oracle database?   Mark for Review
(1) Points
            A role is the part that a user plays in querying the database.
            A role is a type of system privilege.
            A role is an object privilege which allows a user to update a table.
            A role is a name for a group of privileges. (*)

9.         You create a view named EMPLOYEES_VIEW on a subset of the EMPLOYEES table. User AUDREY needs to use this view to create reports. Only you and Audrey should have access to this view. Which of the following actions should you perform?           Mark for Review
(1) Points
            GRANT SELECT ON employees AND employees_view TO audrey;
            GRANT SELECT ON employees_view TO public;
            GRANT SELECT ON employees_view TO audrey; (*)
            Do nothing. As a database user, Audrey's user account has automatically been granted the SELECT privilege for all database objects.

10.       User CRAIG creates a view named INVENTORY_V, which is based on the INVENTORY table. CRAIG wants to make this view available for querying to all database users. Which of the following actions should CRAIG perform?         Mark for Review
(1) Points
            He should assign the SELECT privilege to all database users for the INVENTORY table.
            He should assign the SELECT privilege to all database users for INVENTORY_V view. (*)
            He is not required to take any action because, by default, all database users can automatically access views.
            He must grant each user the SELECT privilege on both the INVENTORY table and INVENTORY_V view.

11.       Scott King owns a table called employees. He issues the following statement:
GRANT select ON employees TO PUBLIC;
Allison Plumb has been granted CREATE SESSION by the DBA. She logs into the database and issues the following statement:
GRANT ﾠselect ON ﾠscott_king.employees TO jennifer_cho;

True or False: Allison's statement will fail.

 Mark for Review
(1) Points
            True (*)
            False

12.       A role can be granted to another role. True or False?   Mark for Review
(1) Points
            True (*)
            False

13.       Which of the following statements about granting object privileges is false?   Mark for Review
(1) Points
            Object privileges can only be granted through roles. (*)
            An object owner can grant any object privilege on the object to any other user or role of the database.
            To grant privileges on an object, the object must be in your own schema, or you must have been granted the object privileges WITH GRANT OPTION.
            The owner of an object automatically acquires all object privileges on that object.

14.       Which keyword would you use to grant an object privilege to all database users?       Mark for Review
(1) Points
            ALL
            PUBLIC (*)
            ADMIN
            USERS

15.       When a user is logged into one database, he is restricted to working with objects found in that database. True or False?  Mark for Review
(1) Points
            True
            False (*)

1.         A Schema is a collection of Objects such as Tables, Views, and Sequences. True or False?   Mark for Review
(1) Points
            True (*)
            False

2.         Which Object Privilege (other than Alter) can be granted to a Sequence?        Mark for Review
(1) Points
            INSERT
            UPDATE
            DELETE
            SELECT (*)

3.         The following table shows some of the output from one of the data dictionary views. Which view is being queried?
USERNAME    PRIVILEGE     ADMIN_OPTION
USCA_ORACLE_SQL01_S08            CREATE          VIEW NO
USCA_ORACLE_SQL01_S08            CREATE          TABLE NO
USCA_ORACLE_SQL01_S08            CREATE          SYNONYM NO
USCA_ORACLE_SQL01_S08            CREATE          TRIGGER NO
USCA_ORACLE_SQL01_S08            CREATE          SEQUENCE NO
USCA_ORACLE_SQL01_S08            CREATE          DATABASE NO
 Mark for Review
(1) Points
            role_sys_privs (lists system privileges granted to roles)
            user_sys_privs (lists system privileges granted to the user) (*)
            role_tab_privs (lists table privileges granted to roles)
            user_tab_privs_recd (lists object privileges granted to the user)

4.         What system privilege must be held in order to login to an Oracle database?  Mark for Review
(1) Points
            CREATE LOGIN
            CREATE SESSION (*)
            CREATE LOGON
            No special privilege is needed; if your username exists in the database, you can login.

5.         Which of the following best describes a role in an Oracle database?   Mark for Review
(1) Points
            A role is an object privilege which allows a user to update a table.
            A role is a name for a group of privileges. (*)
            A role is a type of system privilege.
            A role is the part that a user plays in querying the database.

6.         Which of the following Object Privileges can be granted on an individual column on a table? (Choose two)     Mark for Review
(1) Points
                                    (Choose all correct answers)
            Delete
            Select
            Update (*)
            References (*)

7.         REGULAR EXPRESSIONS can be used on CHAR, CLOB, and VARCHAR2 datatypes? (True or False)            Mark for Review
(1) Points
            True (*)
            False

8.         Parentheses are not used to identify the sub expressions within the expression. True or False?           Mark for Review
(1) Points
            True
            False (*)

9.         REGULAR EXPRESSIONS does exactly the same as LIKE--no more and no less. (True or False?)   Mark for Review
(1) Points
            True
            False (*)

10.       Which of the following best describes the purpose of the REFERENCES object privilege on a table?            Mark for Review
(1) Points
            It allows a user to create foreign key constraints on the table. (*)
            It allows a user to refer to the table in a SELECT statement.
            It allows a user's session to read from the table but only so that foreign key constraints can be checked.
            It allows the user to create new tables which contain the same data as the referenced table.

11.       User CRAIG creates a view named INVENTORY_V, which is based on the INVENTORY table. CRAIG wants to make this view available for querying to all database users. Which of the following actions should CRAIG perform?         Mark for Review
(1) Points
            He should assign the SELECT privilege to all database users for the INVENTORY table.
            He is not required to take any action because, by default, all database users can automatically access views.
            He must grant each user the SELECT privilege on both the INVENTORY table and INVENTORY_V view.
            He should assign the SELECT privilege to all database users for INVENTORY_V view. (*)

12.       Which statement would you use to remove an object privilege granted to a user?       Mark for Review
            DROP
            REMOVE
            REVOKE (*)
            ALTER USER

13.       Roles are:         Mark for Review
(1) Points
            Named groups of related privileges given to a user or another role. (*)
            A collection of objects, such as tables, views, and sequences.
            Required to gain access to the database.
            Required to manipulate the content of objects in the database.

14.       Which of the following statements about granting object privileges is false?   Mark for Review
(1) Points

            An object owner can grant any object privilege on the object to any other user or role of the database.
            The owner of an object automatically acquires all object privileges on that object.
            Object privileges can only be granted through roles. (*)
            To grant privileges on an object, the object must be in your own schema, or you must have been granted the object privileges WITH GRANT OPTION.

15.       Which statement would you use to grant a role to users?         Mark for Review
(1) Points
            ASSIGN
            ALTER USER
            CREATE USER

            GRANT (*)
By Deni Ace at February 11, 2017
Labels: database, Database programming with SQL, programming, quiz, section 17, sql
4 comments:

Ordinary Oracle Student28 April 2017 at 08:48
Forgot these questions.

1. User CHANG has been granted SELECT, UPDATE, INSERT, and DELETE privileges on the EMPLOYEES table. You now want to prevent Chang from adding or deleting rows from the table, while still allowing him to read and modify existing rows. Which statement should you use to do this? Mark for Review
(1) Points


REVOKE INSERT AND DELETE ON employees FROM chang;


REMOVE INSERT, DELETE ON employees FROM chang;


REVOKE ALL ON employees FROM chang;


REVOKE INSERT, DELETE ON employees FROM chang; (*)

2. Which of the following is NOT a database object? Mark for Review
(1) Points


Table


Sequence


View


Subquery (*)

3.Which statement would you use to add privileges to a role? Mark for Review
(1) Points


ASSIGN


ALTER ROLE


GRANT (*)


CREATE ROLE

4.Which of the following statements is true? Mark for Review
(1) Points


Database Links allow users to work on remote database objects without having to log into the other database. (*)


Database Links can be created by any user of a database. You do not need any special privileges to create them.


Database Links are pointers to another schema in the same database.


Database Links are never used in the real world.

Reply
Replies

Deni Ace31 July 2017 at 11:04
thank you ^_^

Reply

Ordinary Oracle Student28 April 2017 at 09:08
Forgot these as well

1.Which of the following are object privileges? (Choose two) Mark for Review
(1) Points

(Choose all correct answers)


CREATE TABLE


INSERT (*)


DROP TABLE


SELECT (*)

2.What Oracle feature simplifies the process of granting and revoking privileges? Mark for Review
(1) Points


Role (*)


Schema


Data dictionary


Object

Section 18 Quiz
            (Answer all questions in this section)

1.         Steven King's row in the EMPLOYEES table has EMPLOYEE_ID = 100 and SALARY = 24000. A user issues the following statements in the order shown:
UPDATE employees
SET salary = salary * 2
WHERE employee_id = 100;
COMMIT;

UPDATE employees
SET salary = 30000
WHERE employee_id = 100;

The user's database session now ends abnormally. What is now King's salary in the table?

 Mark for Review
(1) Points
            78000
            30000
            24000
            48000 (*)

2.         Table MYTAB contains only one column of datatype CHAR(1). A user executes the following statements in the order shown.
INSERT INTO mytab VALUES ('A');
INSERT INTO mytab VALUES ('B');
COMMIT;
INSERT INTO mytab VALUES ('C');
ROLLBACK;

Which rows does the table now contain?

 Mark for Review
(1) Points
            A, B, and C
            A and B (*)
            C
            None of the above

3.         If Oracle crashes, your changes are automatically rolled back. True or False? Mark for Review
(1) Points
            True (*)
            False

4.         Examine the following statements:
UPDATE employees SET salary = 15000;
SAVEPOINT upd1_done;
UPDATE employees SET salary = 22000;
SAVEPOINT upd2_done;
DELETE FROM employees;

You want to retain all the employees with a salary of 15000; What statement would you execute next?

 Mark for Review
(1) Points
            ROLLBACK;
            ROLLBACK TO SAVEPOINT upd1_done; (*)
            ROLLBACK TO SAVEPOINT upd2_done;
            ROLLBACK TO SAVE upd1_done;
            There is nothing you can do; either all changes must be rolled back, or none of them can be rolled back.

5.         COMMIT saves all outstanding data changes? True or False?  Mark for Review
(1) Points
            True (*)
            False

6.         Examine the following statements:
INSERT INTO emps SELECT * FROM employees; -- 107 rows inserted.
SAVEPOINT Ins_Done;
DELETE employees; -- 107 rows deleted
SAVEPOINT Del_Done;
UPDATE emps SET last_name = 'Smith';

How would you undo the last Update only?

 Mark for Review
(1) Points
            ROLLBACK to SAVEPOINT Del_Done; (*)
            COMMIT Del_Done;
            There is nothing you can do.
            ROLLBACK UPDATE;

7.         Examine the following statements:
INSERT INTO emps SELECT * FROM employees; -- 107 rows inserted.
SAVEPOINT Ins_Done;
CREATE INDEX emp_lname_idx ON employees(last_name);
UPDATE emps SET last_name = 'Smith';

What happens if you issue a Rollback statement?

 Mark for Review
(1) Points

            The update of last_name is undone, but the insert was committed by the CREATE INDEX statement. (*)

            Both the UPDATE and the INSERT will be rolled back.
            The INSERT is undone but the UPDATE is committed.
            Nothing happens.

8.         Which SQL statement is used to remove all the changes made by an uncommitted transaction?         Mark for Review
(1) Points
            REVOKE;
            ROLLBACK TO SAVEPOINT;
            ROLLBACK; (*)
            UNDO;

9.         You need not worry about controlling your transactions. Oracle does it all for you. True or False?    Mark for Review
(1) Points
            True
            False (*)

10.       When you logout of Oracle, your data changes are automatically rolled back. True or False? Mark for Review
(1) Points
            True
            False (*)

11.       User BOB's CUSTOMERS table contains 20 rows. BOB inserts two more rows into the table but does not COMMIT his changes. User JANE now executes:
SELECT COUNT(*) FROM bob.customers;

What result will JANE see?

 Mark for Review
(1) Points
            22
            20 (*)
            JANE will receive an error message because she is not allowed to query the table while BOB is updating it.
            2

12.       Which of the following best describes the term "read consistency"?   Mark for Review
(1) Points
            It prevents other users from querying a table while updates are being executed on it

            It prevents other users from seeing changes to a table until those changes have been committed (*)

            It prevents users from querying tables on which they have not been granted SELECT privilege
            It ensures that all changes to a table are automatically committed

13.       A transaction makes several successive changes to a table. If required, you want to be able to rollback the later changes while keeping the earlier changes. What must you include in your code to do this?       Mark for Review
(1) Points
            An object privilege
            An update statement
            A savepoint (*)
            A sequence
            A database link

14.       If a database crashes, all uncommitted changes are automatically rolled back. True or False? Mark for Review
(1) Points
            True (*)
            False

15.       If UserB has privileges to see the data in a table, as soon as UserA has entered data into that table, UserB can see that data. True or False?          Mark for Review
(1) Points
            True
            False (*)

1.         Table MYTAB contains only one column of datatype CHAR(1). A user executes the following statements in the order shown.
INSERT INTO mytab VALUES ('A');
INSERT INTO mytab VALUES ('B');
COMMIT;
INSERT INTO mytab VALUES ('C');
ROLLBACK;

Which rows does the table now contain?

 Mark for Review
(1) Points
            A, B, and C
            A and B (*)
            C
            None of the above

2.         If UserB has privileges to see the data in a table, as soon as UserA has entered data into that table, UserB can see that data. True or False?          Mark for Review
(1) Points
            True
            False (*)

3.         User BOB's CUSTOMERS table contains 20 rows. BOB inserts two more rows into the table but does not COMMIT his changes. User JANE now executes:
SELECT COUNT(*) FROM bob.customers;

What result will JANE see?

 Mark for Review
(1) Points
            JANE will receive an error message because she is not allowed to query the table while BOB is updating it.
            2
            20 (*)
            22

4.         Examine the following statements:
INSERT INTO emps SELECT * FROM employees; -- 107 rows inserted.
SAVEPOINT Ins_Done;
CREATE INDEX emp_lname_idx ON employees(last_name);
UPDATE emps SET last_name = 'Smith';

What happens if you issue a Rollback statement?

 Mark for Review
(1) Points

            The update of last_name is undone, but the insert was committed by the CREATE INDEX
statement. (*)
            Both the UPDATE and the INSERT will be rolled back.
            The INSERT is undone but the UPDATE is committed.
            Nothing happens.

5.         Steven King's row in the EMPLOYEES table has EMPLOYEE_ID = 100 and SALARY = 24000. A user issues the following statements in the order shown:
UPDATE employees
SET salary = salary * 2
WHERE employee_id = 100;
COMMIT;

UPDATE employees
SET salary = 30000
WHERE employee_id = 100;

The user's database session now ends abnormally. What is now King's salary in the table?

 Mark for Review
(1) Points
            24000
            30000
            48000 (*)
            78000

6.         When you logout of Oracle, your data changes are automatically rolled back. True or False? Mark for Review
(1) Points
            True
            False (*)

7.         Which of the following best describes the term "read consistency"?   Mark for Review
(1) Points

            It prevents users from querying tables on which they have not been granted SELECT privilege

            It prevents other users from querying a table while updates are being executed on it

            It prevents other users from seeing changes to a table until those changes have been committed (*)
            It ensures that all changes to a table are automatically committed

8.         Examine the following statements:
INSERT INTO emps SELECT * FROM employees; -- 107 rows inserted.
SAVEPOINT Ins_Done;
DELETE employees; -- 107 rows deleted
SAVEPOINT Del_Done;
UPDATE emps SET last_name = 'Smith';

How would you undo the last Update only?

 Mark for Review
(1) Points
            ROLLBACK UPDATE;
            There is nothing you can do.
            COMMIT Del_Done;
            ROLLBACK to SAVEPOINT Del_Done; (*)

9.         If a database crashes, all uncommitted changes are automatically rolled back. True or False? Mark for Review
(1) Points
            True (*)
            False

10.       A transaction makes several successive changes to a table. If required, you want to be able to rollback the later changes while keeping the earlier changes. What must you include in your code to do this?       Mark for Review
(1) Points
            An update statement
            A savepoint (*)
            A database link
            An object privilege
            A sequence

11.       You need not worry about controlling your transactions. Oracle does it all for you. True or False?    Mark for Review
(1) Points
            True
            False (*)

12.       If Oracle crashes, your changes are automatically rolled back. True or False? Mark for Review
(1) Points
            True (*)
            False

13.       Which SQL statement is used to remove all the changes made by an uncommitted transaction?         Mark for Review
(1) Points
            ROLLBACK; (*)
            UNDO;
            REVOKE;
            ROLLBACK TO SAVEPOINT;

14.       COMMIT saves all outstanding data changes? True or False?  Mark for Review
(1) Points
            True (*)
            False

15.       Examine the following statements:
UPDATE employees SET salary = 15000;
SAVEPOINT upd1_done;
UPDATE employees SET salary = 22000;
SAVEPOINT upd2_done;
DELETE FROM employees;

You want to retain all the employees with a salary of 15000; What statement would you execute next?

 Mark for Review
(1) Points
            ROLLBACK;
            ROLLBACK TO SAVEPOINT upd1_done; (*)
            ROLLBACK TO SAVEPOINT upd2_done;
            ROLLBACK TO SAVE upd1_done;