Oracle Database SQL Workshop

Identify the SELECT statements that execute successfully.

• SELECT first_name, last_name, job_id, salary*12,

  AS Yearly Sal

  FROM employees;

• SELECT first_name, last_name, job_id, salary*12

  "yearly sal"

  FROM employees;

• SELECT first_name, last_name, job_id, salary AS

  "yearly sal"

  FROM employees;

• SELECT first_name+last_name AS name, job_Id,

  salary*12 yearly sal

  FROM employees;

Which four of the following are valid operators for the WHERE clause?

• >=

• IS NULL

• !=

• IS LIKE

• IN BETWEEN

Which four of the following statements are true about single-row functions?

• Manipulate data items

• Accept arguments and return one value per argument

• Act on each row that is returned

• Can be nested

• Accept arguments that can be a column or an expression

The TO_NUMBER function converts either character strings or date values to a number in the format specified by the optional format model.

• True

• False

Identify the two guidelines for group functions and the GROUP BY clause.

• You cannot use a column alias in the GROUP BY clause.

• The GROUP BY column must be in the SELECT clause.

• By using a WHERE clause, you can exclude rows before dividing them into groups.

• The GROUP BY clause groups rows and ensures the order of the result set.

• If you include a group function in a SELECT clause, you must include a GROUP BY

  clause.

If you join a table to itself, what kind of join are you using?

• Left OUTER join

• Right OUTER join

• Full OUTER join

• Self-join

• Natural join

Using a subquery is equivalent to performing two sequential queries and using the result of the first query as the search values in the second query.

• True

• False

Identify two set operator guidelines.

• The expressions in the SELECT lists must match in number.

• Parentheses cannot be used to alter the sequence of execution.

• The data type of each column in the second query must match the data type of its

  corresponding column in the first query.

• The ORDER BY clause can be used only once in a compound query, unless a UNION

  ALL operator is used.

The following statements produce the same results:

DELETE FROM copy_emp;

TRUNCATE TABLE copy_emp;

• True

• False

Identify three actions that you perform by using constraints.

• Enforce rules on the data in a table whenever a row is inserted, updated, or deleted.

• Prevent the dropping of a table.

• Prevent the creation of a table.

• Prevent the creation of data in a table.