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

Database programming section 4

1. You query the database with this SQL statement:
SELECT LOWER(SUBSTR(CONCAT(last_name, first_name)), 1, 5) "ID"
FROM employee;
In which order are the functions evaluated?
CONCAT, SUBSTR, LOWER (*)

2. You query the database with this SQL statement:
SELECT CONCAT(last_name, (SUBSTR(LOWER(first_name), 4))) "Default Password"
FROM employees;
Which function will be evaluated first?
LOWER (*)

3. The STYLES table contains this data:
STYLE_IDSTYLE_NAMECATEGORYCOST895840SANDAL8594012.00968950SANDAL8590910.00869506SANDAL8969015.00809090LOAFER8909810.00890890LOAFER8978914.00857689HEEL8594011.00758960SANDAL8697912.00
You query the database and return the value 79. Which script did you use?

SELECT SUBSTR(category, -2,2)
FROM styles
WHERE style_id = 758960;

4. Which query selects the first names of the DJ On Demand clients who have a first name beginning with "A"?
SELECT UPPER(first_name)
FROM d_clients
WHERE LOWER(first_name) LIKE 'a%'
(*)

5. What does the following SQL SELECT statement return?
SELECT UPPER( SUBSTR('Database Programming', INSTR('Database Programming','P'),20))
FROM dual;
PROGRAMMING (*)

6. Which of the following Date Functions will add calendar months to a date?
ADD_MONTHS (*)

7. Which SELECT statement will NOT return a date value?
SELECT (SYSDATE - hire_date) + 10*8
FROM employees;

8. Round and Trunc cannot be used on Date datatypes. True or False?
False(*)

9. Evaluate this SELECT statement:
SELECT SYSDATE + 30
FROM dual;
Which value is returned by the query?

The current date plus 30 days. (*)

10. Which of the following SQL statements will correctly display the last name and the number of weeks employed for all employees in department 90?
SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM employees
WHERE department_id = 90;
(*)

11. Which two functions can be used to manipulate number or date column values, but NOT character column values? (Choose two.)
(Choose all correct answers)
ROUND(*)
TRUNC(*)

12. The answer to the following script is 456. True or False?
SELECT TRUNC(ROUND(456.98))
FROM dual;

False (*)

13. Which number function may be used to determine if a value is odd or even?
MOD(*)

14. Which comparison operator retrieves a list of values?
IN(*)

15. Which script displays '01-May-2004' when the HIRE_DATE value is '20-May-2004'?

SELECT TRUNC(hire_date, 'MONTH')
FROM employees;
(*)