Quizzuri Oracle

SQL Programming #11

Posted by Feroviarul on

Oracle Database Programming with SQL Section 11

1.         To store time with fractions of seconds, which datatype should be used for a table column?
♦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?
♦Four (*)
♦Zero
♦Six
♦Two
3.         The TIMESTAMP data type allows what?
♦Time to be stored as an interval of years and months.
♦Time to be stored as a date with fractional seconds. (*)
♦Time to be stored as an interval of days to hours, minutes and seconds.
♦None of the above.
4.         Which data types stores variable-length character data? Select two.     (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?
♦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?
♦True
♦False (*)
7.         You can use the ALTER TABLE statement to:
♦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?
♦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?
♦TRUNCATE TABLE
♦DELETE TABLE
♦ALTER TABLE
♦DROP TABLE (*)
10.       When you use ALTER TABLE to add a column, the new column:
♦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?
♦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 ____________________________________.
            ♦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?
♦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?
♦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?
♦3
♦1
♦4 (*)
♦2
16.         The TIMESTAMP data type allows what?
♦Time to be stored as an interval of years and months.
♦Time to be stored as a date with fractional seconds. (*)
♦Time to be stored as an interval of days to hours, minutes and seconds.
♦None of the above.
17.         Which statement about data types is true?
♦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. (*)
18.         INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. True or False?
♦True (*)
♦False
19.         You are designing a table for the Human Resources department. This table must include a column that contains each employee’s hire date. Which data type should you specify for this column?
♦CHAR
♦DATE (*)
♦INTERVAL YEAR TO MONTH
♦TIMESTAMP
20.         To store large amounts of text you should simply create a series of VARCHAR2 columns in a table. True or False?
♦True
♦False (*)
21.         Which SQL statement below will correctly create the EMP table based on the structure of the EMPLOYEES table? Include only the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, and DEPARTMENT_ID columns.
            ♦CREATE TABLE employee
AS SELECT employee_id, first_name, last_name, salary, department_id
FROM employees;
            ♦CREATE TABLE emp
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);
22.         Which CREATE TABLE statement will fail?
♦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));
23.         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 ____________________________________.
            ♦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.
24.         Which statement about table and column names is true?
♦Table and column names must begin with a letter. (*)
♦Table and column names can begin with a letter or a number.
♦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.
25.       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:
♦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
26.       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?
♦True
♦False (*)
27.       You want to issue the following command on a database that includes your company’s inventory information:
ALTER TABLE products SET UNUSED COLUMN color;
What will be the result of issuing this command?
♦The column named COLOR in the table named PRODUCTS will be 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. (*)
28.       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?
♦ALTER TABLE
♦DROP TABLE (*)
♦DELETE
♦TRUNCATE TABLE
29.       The FLASHBACK QUERY statement can restore data back to a point in time before the last COMMIT. True or False?
♦True
♦False (*)
30.       Evaluate this statement:
Which statement about this TRUNCATE TABLE statement is true?
♦You can produce the same results by issuing the ‘DROP TABLE employee’ statement.
♦You can issue this statement to retain the structure of the employees table. (*)
♦You can reverse this statement by issuing the ROLLBACK statement.
♦You can produce the same results by issuing the ‘DELETE employees’ statement.
31.         You need to change the name of the EMPLOYEES table to the EMP table. Which statement should you use?
♦ALTER TABLE employees RENAME TO emp;
♦RENAME employees emp;
♦RENAME employees TO emp; (*)
♦ALTER TABLE employees TO emp;
32.         You can use the ALTER TABLE statement to:
♦Add a new column
♦Modify an existing column
♦Drop a column
♦All of the above (*)
33.         The TEAMS table contains these columns:
TEAM_ID NUMBER(4) Primary Key
TEAM_NAME VARCHAR2(20)
MGR_ID NUMBER(9)
The TEAMS table is currently empty. You need to allow users to include text characters in the manager identification values. Which statement should you use to implement this?
            ♦ALTER teams 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.
34.         Evaluate the structure of the EMPLOYEE table:
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER(9)
MANAGER_ID NUMBER(9)
SALARY NUMBER(7,2)
Which statement should you use to increase the LAST_NAME column length to 35 if the column currently contains 200 records?
♦ALTER 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.
35.         Which statement about a column is NOT true?
♦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.
36.         Once they are created, external tables are accessed with normal SQL statements. (True or False?)
♦True (*)
♦False
37.         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?
♦4 (*)
♦1
♦3
♦2
38.         Which column name is valid?
♦NUMBER
♦1NUMBER
♦NUMBER_1$ (*)
♦1_NUMBER#
39.         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 ____________________________________.
♦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.
40.       DCL, which is the acronym for Data Control Language, allows:
♦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.
41.       The TIMESTAMP data type allows what?
♦Time to be stored as an interval of years and months.
♦Time to be stored as a date with fractional seconds. (*)
♦Time to be stored as an interval of days to hours, minutes and seconds.
♦None of the above.
42.       A column that will be used to store binary data up to 4 Gigabytes in size should be defined as which datatype?
♦LONGRAW
♦LONG
♦NUMBER
♦BLOB (*)
43.       Which of the following are valid Oracle datatypes?
♦TIMESTAMP, LOB, VARCHAR2, NUMBER
♦DATE, BLOB, LOB, VARCHAR2
♦DATE, TIMESTAMP WITH LOCAL TIME ZONE, BLOB (*)
♦SYSDATE, TIMESTAMP, DATE, LOCAL TIME ZONE

44.       Which statement about data types is true?

            ♦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.
45.       Which data types stores variable-length character data? Select two.
(Choose all correct answers)♦NCHAR
♦VARCHAR2 (*)
♦CLOB (*)
♦CHAR
46.         INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. True or False?
♦True (*)
♦False
47.         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?
♦TIMESTAMP
♦INTERVAL YEAR TO MONTH
♦CHAR
♦DATE (*)
48.         To store large amounts of text you should simply create a series of VARCHAR2 columns in a table. True or False?
♦True
♦False (*)
49.         A column that will be used to store binary data up to 4 Gigabytes in size should be defined as which datatype?
♦NUMBER
♦LONGRAW
♦BLOB (*)
♦LONG
50.         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?
♦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. (*)
51.         Examine the structure of the DONATIONS table.
DONATIONS:
PLEDGE_ID NUMBER
DONOR_ID NUMBER
PLEDGE_DT DATE
AMOUNT_PLEDGED NUMBER (7,2)
AMOUNT_PAID NUMBER (7,2)
PAYMENT_DT DATE
You need to reduce the precision of the AMOUNT_PLEDGED column to 5 with a scale of 2 and ensure that when inserting a row into the DONATIONS table without a value for the AMOUNT_PLEDGED column, a price of $10.00 will automatically be inserted. The DONATIONS table currently contains NO records. Which statement is true?
♦You 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.
52.         Which command could you use to quickly remove all data from the rows in a table without deleting the table itself?
♦MODIFY
♦ALTER TABLE
♦DROP TABLE
♦TRUNCATE TABLE (*)
53.         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?
♦The TRUNCATE TABLE statement (*)
♦The ALTER TABLE statement
♦The DROP TABLE statement
♦The DELETE statement
54.         The FLASHBACK QUERY statement can restore data back to a point in time before the last COMMIT. True or False?
♦True
♦False (*)
55.       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?
♦TRUNCATE TABLE
♦DELETE TABLE
♦ALTER TABLE
♦DROP TABLE (*)
56.       Evaluate this CREATE TABLE statement:
CREATE TABLE line_item ( line_item_id NUMBER(9), order_id NUMBER(9), product_id NUMBER(9));
You are a member of the SYSDBA role, but are logged in under your own schema. You issue this CREATE TABLE statement. Which statement is true?
♦You created the 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. (*)
57.       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?
♦True (*)
♦False
58.       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 ____________________________________.
            ♦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.
59.       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);
♦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.
60.       Once they are created, external tables are accessed with normal SQL statements. (True or False?)
♦True (*)
♦False
61.         It is possible to create a table by using the CREATE TABLE command in conjunction with a subquery. True or False?
♦True (*)
♦False
62.         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?
♦2001_PRODUCTS
♦PRODUCTS_(2001)
♦PRODUCTS_2001 (*)
♦PRODUCTS–2001

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

♦CREATE TABLE travel
(destination_id primary key, departure_date date, return_date date, emp_id REFERENCES employees (emp_id));

♦CREATE TABLE travel
(destination_id number primary key, departure_date date, return_date date, t.emp_id = e.emp_id);
♦CREATE TABLE travel
(destination_id number primary key, departure_date date, return_date date, JOIN emp_id number(10) ON employees (emp_id));
♦CREATE TABLE travel
(destination_id number primary key, departure_date date, return_date date, emp_id number(10) REFERENCES employees (emp_id));
(*)

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

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

65.         DCL, which is the acronym for Data Control Language, allows:

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

66.         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?
♦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. (*)
67.         A table has a column: RESPONSE_TIME. This is used to store the difference between the time the problem was reported and the time the problem was resolved. Data in the RESPONSE_TIME column needs to be stored in days, hours, minutes and seconds. Which data type should you use?
♦DATETIME
♦INTERVAL YEAR TO MONTH
♦TIMESTAMP
♦INTERVAL DAY TO SECOND (*)
68.         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?
♦NUMBER (*)
♦VARCHAR2
♦DATE
♦CHAR
69.         To store time with fractions of seconds, which datatype should be used for a table column?
♦DATE
♦INTERVAL YEAR TO MONTH
♦TIMESTAMP (*)
♦INTERVAL DAY TO SECOND
70.       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?
♦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. (*)
71.       RENAME old_name to new_name can be used to:
♦Rename a row.
♦Rename a column.
♦Rename a table. (*)
♦All of the above.
72.       The data type of a column can never be changed once it has been created. True or False?
♦True
♦False (*)
73.       When you use ALTER TABLE to add a column, the new column:
♦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 (*)
74.       You want to issue the following command on a database that includes your company’s inventory information:
ALTER TABLE products SET UNUSED COLUMN color;
What will be the result of issuing this command?
♦The column named COLOR in the table named PRODUCTS will be 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.
75.       The TEAMS table contains these columns:
TEAM_ID NUMBER(4) Primary Key
TEAM_NAME VARCHAR2(20)
MGR_ID NUMBER(9)
The TEAMS table is currently empty. You need to allow users to include text characters in the manager identification values. Which statement should you use to implement this?
♦ALTER 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));
76.         You can use the ALTER TABLE statement to:
♦Add a new column
♦Modify an existing column
♦Drop a column
♦All of the above (*)
77.         When should you use the SET UNUSED command?
♦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.
78.         Which command could you use to quickly remove all data from the rows in a table without deleting the table itself?
♦ALTER TABLE
♦DROP TABLE
♦TRUNCATE TABLE (*)
♦MODIFY
79.         Comments on tables and columns can be stored for documentation by:
♦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 (*)
80.         Evaluate this statement:
ALTER TABLE employees SET UNUSED (fax);
Which task will this statement accomplish?
♦Deletes the FAX column
♦Frees the disk space used by the data in the FAX column
♦Prevents 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 (*)
81.         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?
♦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.
82.         Which SQL statement below will correctly create the EMP table based on the structure of the EMPLOYEES table? Include only the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, and DEPARTMENT_ID columns.
♦CREATE TABLE 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);
83.         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 ____________________________________.
            ♦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.
84.         When creating a new table, which of the following naming rules apply. (Choose three)  (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
85.       Which CREATE TABLE statement will fail?
♦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)); (*)
86.       INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. True or False?
♦True (*)
♦False
87.       Which of the following are valid Oracle datatypes?
♦DATE, BLOB, LOB, VARCHAR2
♦TIMESTAMP, LOB, VARCHAR2, NUMBER
♦DATE, TIMESTAMP WITH LOCAL TIME ZONE, BLOB (*)
♦SYSDATE, TIMESTAMP, DATE, LOCAL TIME ZONE
88.       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?
♦TIMESTAMP
♦INTERVAL YEAR TO MONTH
♦DATETIME
♦INTERVAL DAY TO SECOND (*)
89.       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?
♦CHAR
♦NUMBER (*)
♦DATE
♦VARCHAR2
90.       Which data types stores variable-length character data? Select two.   (Choose all correct answers)
♦NCHAR
♦CLOB (*)
♦CHAR
♦VARCHAR2 (*)
91.         You need to store the SEASONAL data in months and years. Which data type should you use?
♦INTERVAL YEAR TO MONTH (*)
♦TIMESTAMP
♦INTERVAL DAY TO SECOND
♦DATE
92.         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?
♦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.
93.         The TIMESTAMP data type allows what?
♦Time to be stored as an interval of years and months.
♦Time to be stored as a date with fractional seconds. (*)
♦Time to be stored as an interval of days to hours, minutes and seconds.
♦None of the above.
94.         Which statement about data types is true?
♦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.
95.         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?
♦CHAR
♦DATE
♦NUMBER (*)
♦VARCHAR2
96.         The TEAMS table contains these columns:
TEAM_ID NUMBER(4) Primary Key
TEAM_NAME VARCHAR2(20)
MGR_ID NUMBER(9)
The TEAMS table is currently empty. You need to allow users to include text characters in the manager identification values. Which statement should you use to implement this?
            ♦ALTER 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));
97.         Evaluate this statement:
ALTER TABLE inventory
MODIFY backorder_amount NUMBER(8,2);
Which task will this statement accomplish?
♦Alters the definition of the BACKORDER_AMOUNT column to NUMBER(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) (*)
98.         Which statement about decreasing the width of a column is true?
            ♦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.
99.         The FLASHBACK QUERY statement can restore data back to a point in time before the last COMMIT. True or False?
♦True
♦False (*)
100.       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?
♦True
♦False (*)
101.       Once they are created, external tables are accessed with normal SQL statements. (True or False?)
♦True (*)
♦False
102.       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:
♦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 (*)
103.       Which statement about creating a table is true?
            ♦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.
104.       Which SQL statement below will correctly create the EMP table based on the structure of the EMPLOYEES table? Include only the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, and DEPARTMENT_ID columns.
            ♦CREATE TABLE 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);
105.       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?
♦2
♦4 (*)
♦1
♦3

Leave a comment