Database programming section 13

1. You need to store the HIRE_DATE value with a time zone displacement value and allow data to be returned in the user's local session time zone. Which data type should you use?
TIMESTAMP WITH LOCAL TIME ZONE (*)

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

3. The TIMESTAMP data type allows what?
Time to be stored as a date with fractional seconds. (*)

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

5. A column that will be used to store binary data up to 4 Gigabytes in size should be defined as which datatype?
BLOB (*)

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

7. When you use ALTER TABLE to add a column, the new column:
Becomes the last column in the table (*)

8. 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?
Both changes can be accomplished with one ALTER TABLE statement. (*)

9. Which statement about decreasing the width of a column is true?
When a character column contains data, you can decrease the width of the column if the existing data does not violate the new size. (*)

10. Evaluate the structure of the EMPLOYEE table:
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER(9)
MANAGER_ID NUMBER(9)
SALARY NUMBER(7,2)
The EMPLOYEE_ID column currently contains 500 employee identification numbers. Business requirements have changed and you need to allow users to include text characters in the identification values. Which statement should you use to change this column's data type?
You CANNOT modify the data type of the EMPLOYEE_ID column, as the table is not empty. (*)

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

12. 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 number primary key, departure_date date, return_date date, emp_id number(10) REFERENCES employees (emp_id));
(*)

13. Which statement about table and column names is true?
Table and column names must begin with a letter. (*)

14.  When creating a new table, which of the following naming rules apply. (Choose three)
(Choose all correct answers)

Must contain ONLY A - Z, a - z, 0 - 9, _ (underscore), $, and # (*)
Must begin with a letter (*)
Must be between 1 to 30 characters long (*)

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

A Database Administrator the ability to grant privileges to users. (*)