You need to display the current year as a character value (for example: Two Thousand and One). Which element would you use?

             

RR

YY

YYYY

YEAR (*)

             

            Incorrect. Refer to Section 1

             

You need to display the number of months between today's date and each employee's hiredate. Which function should you use?

             

ROUND

BETWEEN

ADD_MONTHS

MONTHS_BETWEEN (*)

             

            Correct

             

Which SELECT statement will NOT return a date value?

             

SELECT (30 + hire_date) + 1440/24 

FROM employees;

SELECT (SYSDATE - hire_date) + 10*8 

FROM employees;

(*)

SELECT SYSDATE - TO_DATE('25-JUN-02') + hire_date 

FROM employees;

SELECT (hire_date - SYSDATE) + TO_DATE('25-JUN-02') 

FROM employees;

             

            Incorrect. Refer to Section 1

             

You want to create a report that displays all orders and their amounts that were placed during the month of January. You want the orders with the highest amounts to appear first. Which query should you issue?

             

SELECT orderid, total 

FROM orders 

WHERE order_date LIKE '01-jan-02' AND '31-jan-02' 

ORDER BY total DESC;

SELECT orderid, total 

FROM orders 

WHERE order_date IN ( 01-jan-02 , 31-jan-02 ) 

ORDER BY total;

SELECT orderid, total 

FROM orders 

WHERE order_date BETWEEN '01-jan-02' AND '31-jan-02' 

ORDER BY total DESC;

(*)

SELECT orderid, total 

FROM orders 

WHERE order_date BETWEEN '31-jan-02' AND '01-jan-02' 

ORDER BY total DESC;

             

            Correct

             

Which function would you use to return the current database server date and time?

             

DATE

SYSDATE (*)

DATETIME

CURRENTDATE

             

            Correct

             

 

             

Section 2 Lesson 1

(Answer all questions in this section)

             

Which three statements concerning explicit data type conversions are true? (Choose three.)

             

(Choose all correct answers)

             

Use the TO_NUMBER function to convert a number to a character string.

Use the TO_DATE function to convert a character string to a date value. (*)

Use the TO_NUMBER function to convert a character string of digits to a number. (*)

Use the TO_DATE function to convert a date value to character string or number.

Use the TO_CHAR function to convert a number or date value to character string. (*)

             

            Correct

             

You have been asked to create a report that lists all customers who have placed orders of at least $2,500. The report's date should be displayed in the Day, Date Month, Year format (For example, Tuesday, 13 April, 2004 ). Which statement should you issue?

             

SELECT companyname, TO_CHAR (sysdate, 'fmdd, dy month, yyyy'), total 

FROM customers NATURAL JOIN orders 

WHERE total >= 2500;

SELECT companyname, TO_DATE (date, 'day, dd month, yyyy'), total 

FROM customers NATURAL JOIN orders 

WHERE total >= 2500;

SELECT companyname, TO_DATE (sysdate, 'dd, dy month, yyyy'), total 

FROM customers NATURAL JOIN orders 

WHERE total >= 2500;

SELECT companyname, TO_CHAR (sysdate, 'fmDay, dd Month, yyyy'), total 

FROM customers NATURAL JOIN orders 

WHERE total >= 2500;

(*)

             

            Incorrect. Refer to Section 2

             

Which best describes the TO_CHAR function?

             

The TO_CHAR function can be used to specify meaningful column names in an SQL statement's result set.

The TO_CHAR function can be used to remove text from column data that will be returned by the database.

The TO_CHAR function can be used to display dates and numbers according to formatting conventions that are supported by Oracle. (*)

The TO_CHAR function can only be used on DATE columns.

             

            Correct

             

Which statement concerning single row functions is true?

             

Single row functions can accept only one argument, but can return multiple values.

Single row functions cannot modify a data type.

Single row functions can be nested. (*)

Single row functions return one or more results per row.

             

            Incorrect. Refer to Section 2

             

Which functions allow you to perform explicit data type conversions?

             

ROUND, TRUNC, ADD_MONTHS

LENGTH, SUBSTR, LPAD, TRIM

TO_CHAR, TO_DATE, TO_NUMBER (*)

NVL, NVL2, NULLIF

 

The PRICE table contains this data:

PRODUCT_ID            MANUFACTURER_ID

86950 59604

You query the database and return the value 95. Which script did you use?

 

 

 

SELECT SUBSTR(product_id, 3, 2)

FROM price

WHERE manufacturer_id = 59604;

(*)

 

 

SELECT LENGTH(product_id, 3, 2)

FROM price

WHERE manufacturer_id = 59604;

 

 

SELECT SUBSTR(product_id, -1, 3)

FROM price

WHERE manufacturer_id = 59604;

 

 

SELECT TRIM(product_id, -3, 2)

FROM price

WHERE manufacturer_id = 59604;

 

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? 

CONCAT

SUBSTR

LOWER (*)

All three will be evaluated simultaneously.

 

Which SQL function is used to return the position where a specific character string begins within a larger character string?

CONCAT

INSTR (*)

LENGTH

SUBSTR

Incorrect. Refer to Section 1

 

Which functions can be used to manipulate character, number, and date column values?

 

CONCAT, RPAD, and TRIM (*)

UPPER, LOWER, and INITCAP

ROUND, TRUNC, and MOD

ROUND, TRUNC, and ADD_MONTHS

Incorrect. Refer to Section 1

 

Which three statements about functions are true? (Choose three.) (Choose all correct answers)

The SYSDATE function returns the Oracle Server date and time. (*)

The ROUND number function rounds a value to a specified decimal place or the nearest whole number. (*)

The CONCAT function can only be used on character strings, not on numbers.

The SUBSTR character function returns a portion of a string beginning at a defined character position to a specified length. (*)

Incorrect. Refer to Section 1

 

 

Evaluate this SELECT statement:

SELECT LENGTH(email)

FROM employees;

What will this SELECT statement display?

 

The longest e-mail address in the EMPLOYEES table.

The email address of each employee in the EMPLOYEES table.

The number of characters for each value in the EMAIL column in the EMPLOYEES table. (*)

The maximum number of characters allowed in the EMAIL column.

Incorrect. Refer to Section 1

 

 

What will the following SQL statement display?

SELECT last_name, LPAD(salary, 15, '$')SALARY

FROM employees;

 

The last name of employees that have a salary that includes a $ in the value, size of 15 and the column labeled SALARY.

 

 

The last name and the format of the salary limited to 15 digits to the left of the decimal and the column labeled SALARY.

 

 

The last name and salary for all employees with the format of the salary 15 characters long, left-padded with the $ and the column labeled SALARY. (*)

 

 

The query will result in an error: "ORA-00923: FROM keyword not found where expected."

Incorrect. Refer to Section 1

Section 1 Lesson 2

(Answer all questions in this section)

Evaluate this function: MOD (25, 2) Which value is returned?

1 (*)

2

25

0

 

You issue this SQL statement:

SELECT TRUNC(751.367,-1)

FROM dual;

Which value does this statement display? 

700

750 (*)

751

751.3

 

Which two functions can be used to manipulate number or date column values, but NOT character column values? (Choose two.)

RPAD

TRUNC (*)

ROUND (*)

INSTR

CONCAT

 

What would happen if you issued a DELETE statement without a WHERE clause?

 

 

 

All the rows in the table would be deleted. (*)

 

 

An error message would be returned.

 

 

No rows would be deleted.

 

 

Only one row would be deleted.

 

 

Evaluate this statement: DELETE FROM customer; Which statement is true?

 

The statement deletes all the rows from the CUSTOMER table. (*)

 

The statement deletes the CUSTOMER column.

 

The statement deletes the first row in the CUSTOMERS table.

 

The statement removes the structure of the CUSTOMER table from the database

 

Correct

Examine the structures of the PRODUCTS and SUPPLIERS tables:

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)

PRODUCTS

PRODUCT_ID NUMBER NOT NULL, Primary Key

PRODUCT_NAME VARCHAR2 (25)

SUPPLIER_ID NUMBER Foreign key to SUPPLIER_ID of the SUPPLIERS table

CATEGORY_ID NUMBER

QTY_PER_UNIT NUMBER

UNIT_PRICE NUMBER (7,2)

QTY_IN_STOCK NUMBER

QTY_ON_ORDER NUMBER

REORDER_LEVEL NUMBER

You want to delete any products supplied by the five suppliers located in Atlanta. Which script should you use?

 

 

 

DELETE FROM products

WHERE supplier_id IN

   (SELECT supplier_id

    FROM suppliers

    WHERE UPPER(city) = 'ATLANTA');

(*)

 

 

DELETE FROM products

WHERE UPPER(city) = 'ATLANTA';

 

 

DELETE FROM products

WHERE supplier_id =

    (SELECT supplier_id

    FROM suppliers

    WHERE UPPER(city) = 'ATLANTA');

 

 

DELETE FROM products

WHERE supplier_id <

    (SELECT supplier_id

    FROM suppliers

    WHERE UPPER(city) = 'ALANTA');

Correct

 

What keyword in an UPDATE statement specifies the columns you want to change?

 

SELECT

WHERE

SET (*)

HAVING

Incorrect. Refer to Section 7

 

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?

 

 

UPDATE city = Atlanta;

SET city = 'Atlanta';

WHERE city = 'Atlanta'; (*)

LIKE 'At%';

Incorrect. Refer to Section 7

 

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?

 

 

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

 

When the WHERE clause is missing in a DELETE statement, what is the result?

 

All rows are deleted from the table. (*)

The table is removed from the database.

An error message is displayed indicating incorrect syntax.

Nothing. The statement will not execute.

Correct

 

You want to enter a new record into the CUSTOMERS table. Which two commands can be used to create new rows?

INSERT, CREATE

MERGE, CREATE

INSERT, MERGE (*)

INSERT, UPDATE

Incorrect. Refer to Section 7

 

The EMPLOYEES table contains the following columns:

EMPLOYEE_ID NUMBER(10) PRIMARY KEY

LAST_NAME VARCHAR2(20)

FIRST_NAME VARCHAR2(20)

DEPARTMENT_ID NUMBER(10)

HIRE_DATE DATE

SALARY NUMBER(9,2)

BONUS NUMBER(9,2)

You need to increase the salary for all employees in department 10 by 10 percent. You also need to increase the bonus for all employees in department 10 by 15 percent. Which statement should you use?

 

 

 

UPDATE employees

SET salary = salary * 1.10, bonus = bonus * 1.15

WHERE department_id = 10;

(*)

 

 

UPDATE employees

SET salary = salary * 1.10 AND bonus = bonus * 1.15

WHERE department_id = 10;

 

 

UPDATE employees

SET (salary = salary * 1.10) SET (bonus = bonus * 1.15)

WHERE department_id = 10;

 

 

UPDATE employees

SET salary = salary * .10, bonus = bonus * .15

WHERE department_id = 10;

Correct

 

The EMPLOYEES table contains the following columns:

EMPLOYEE_ID NUMBER(10) PRIMARY KEY

LAST_NAME VARCHAR2(20)

FIRST_NAME VARCHAR2(20)

DEPARTMENT_ID NUMBER(10)

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 id 89898. Currently, all employees in department 10 have the same salary value. Which statement should you execute?

 

 

 

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)

WHERE department_id = 10;

(*)

 

 

UPDATE employees

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

You need to compute the total salary for all employees in department 10. Which group function will you use?

MAX

SUM (*)

VARIANCE

 

COUNT

 

Incorrect. Refer to Section 5

The AVG, SUM, VARIANCE, and STDDEV functions can be used with which of the following?

Only numeric data types (*)

Integers only

Any data type

All except numeric

 

Correct

Which group function would you use to display the lowest value in the SALES_AMOUNT column?

AVG

COUNT

MAX

MIN (*)

 

 

Incorrect. Refer to Section 5

 

 

 

Which group functions below act on character, number and date data types?

(Choose three)

 

 

(Choose all correct answers)

 

 

 

SUM

MAX (*)

MIN (*)

AVG

COUNT (*)

 

 

 

 

 

 

Incorrect. Refer to Section 5

 

 

 

Which aggregate function can be used on a column of the DATE data type?

 

AVG

 

 

MAX (*)

 

 

STDDEV

 

 

SUM

 

Incorrect. Refer to Section 5

 

Section 5 Lesson 3

(Answer all questions in this section)

 

 

Evaluate this SQL statement:

SELECT COUNT (amount)

FROM inventory;

What will occur when the statement is issued?

 

The statement will return the greatest value in the INVENTORY table.

 

 

The statement will return the total number of rows in the AMOUNT column.

 

 

The statement will replace all NULL values that exist in the AMOUNT column.

 

 

The statement will count the number of rows in the INVENTORY table where the AMOUNT column is not null. (*)

 

Evaluate this SELECT statement:

SELECT COUNT(*)

FROM employees

WHERE salary > 30000;

Which results will the query display?

 

 

 

The number of employees that have a salary less than 30000.

 

 

The total of the SALARY column for all employees that have a salary greater than 30000.

 

 

The number of rows in the EMPLOYEES table that have a salary greater than 30000. (*)

 

 

The query generates an error and returns no results.

 

 

The EMPLOYEES table contains these columns:

EMPLOYEE_ID NUMBER(9)

LAST_NAME VARCHAR2(20)

FIRST_NAME VARCHAR2(20)

SALARY NUMBER(7,2)

DEPARTMENT_ID NUMBER(9)

You need to display the number of employees whose salary is greater than $50,000? Which SELECT would you use?

 

SELECT * FROM employees

WHERE salary > 50000;

 

 

SELECT * FROM employees

WHERE salary < 50000;

 

 

SELECT COUNT(*) FROM employees

WHERE salary < 50000;

 

 

SELECT COUNT(*) FROM employees

WHERE salary > 50000;

(*)

 

 

SELECT COUNT(*) FROM employees

WHERE salary > 50000

GROUP BY employee_id, last_name, first_name, salary, department_id;

 

 

Evaluate this SELECT statement:

SELECT COUNT(*)

FROM products;

Which statement is true?

The number of rows in the table is displayed. (*)

The number of unique PRODUCT_IDs in the table is displayed.

An error occurs due to an error in the SELECT clause.

An error occurs because no WHERE clause is included in the SELECT statement.

Correct

Section 6 Lesson 1

(Answer all questions in this section)

 

 

The PLAYERS table contains these columns:

PLAYER_ID NUMBER PK

PLAYER_NAME VARCHAR2 (30)

TEAM_ID NUMBER

HIRE_DATE DATE

SALARY NUMBER (8,2)

Which two clauses represent valid uses of aggregate functions? (Choose three.)

 

 

(Choose all correct answers)

 

ORDER BY AVG(salary) (*)

GROUP BY MAX(salary)

SELECT AVG(NVL(salary, 0)) (*)

HAVING MAX(salary) > 10000 (*)

WHERE hire_date > AVG(hire_date)