Section 13 Quiz Database Programming With SQL

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