Section 2ORACLE Database Programming

Additional Review for Section 2

Name ______Date ______

S. Sherman, E. Williams Academy Instructors

Directions: answer each question.

  1. What are the two functions you should be able to accomplish after Section 2?
  1. What clause do you use to restrict the rows of data in a search?
  1. Where in the script is this clause placed?
  1. What three things does a WHERE clause consist of?
  1. What are the four comparisons a WHERE clause can complete?
  1. True or FALSE: WHERE job_id = SA_REP and WHERE job_id = Sa_Rep will return the same result.
  1. Of the three comparisons a WHERE clause can do, list each as well as any special marks that must accompany each.
  1. What can NOT be used in a WHERE clause?
  1. Write a WHERE clause for each of the ten (10) Comparison Conditions. Make sure that each clause you write is correct in terms of format and markings (such as quotation marks). You must use at least one date comparison, one numeric comparison, and one character comparison.
  1. Write today’s date using the Default Oracle Date format.
  1. Suppose you wanted to find all rows containing commission percentages of 15%, 20%, or 25%. Which Comparison Condition would you use?
  1. If you wanted to find the employees with the salary between $2000 and $4000, and you wrote the following select statement, it would fail. Give the two reasons why this statement is not accurate based on your desired results.

SELECT last_name, salary

FROM employees

WHERE salary BETWEEN 4001 and 2499;

  1. TRUE or False: The IN condition can be used with any data type (date, character, number).
  1. TRUE or False: When using the IN condition, you must use parenthesis ( ) to list comparison values.
  1. True or FALSE: When using the IN condition, you must use single quotes ( ‘ ) for numeric values.
  1. Which Comparison Condition allows you to search for a partial character string?
  1. What two things can be included in this search condition?
  1. True or False: Using literal characters to search for conditions is not case sensitive.
  1. True or FALSE: The following two scripts would return the exact same value:

SELECT last_name, hire_date

FROM employees

WHERE hire_date LIKE ‘%95’;

SELECT last_name, hire_date

FROM employees

WHERE hire_date BETWEEN ‘01-JAN-95’ AND ‘01-DEC-95’;

  1. What results would the following query return:
  1. SELECT last_name, hire_date
  2. FROM employees
  3. WHERE last_name LIKE ‘_i%’;
  1. What are the three Logical Conditions to restrict results?
  1. What does a Logical Condition do?
  1. When is a row returned when using Logical Conditions?
  1. What does the AND condition mean or return?
  1. What does the OR condition mean or return?
  1. What does the NOT condition mean or return?
  1. Where do you put a Logical Condition in a statement?
  1. How many conditions can you have in one WHERE clause?
  1. TRUE or False: When using the AND operator, all character string searches are case sensitive.
  1. True or FALSE: When using the AND operator, if the first condition is true and the second condition is false, the row will be returned.
  1. TRUE or False: When using the OR operator, if the first condition is true and the second condition is null, the row will be returned.
  1. Which operator is more likely to return rows, the AND operator or the OR operator?
  1. True or FALSE: You must have at least two conditions for the NOT operator to function properly.
  1. True or FALSE: The order of precedence is fixed and cannot be modified.
  1. What is the default order of rows when returning rows in a query?
  1. What clause do you use to sort rows returned?
  1. Where must this clause be placed?
  1. What can you specify as the sort condition?
  1. TRUE or False: If you do not use the ORDER BY clause, and you run the same query twice, you could get two different orders of rows returned.
  1. If you type this line:

ORDER BY hire_date

Which would come first, 24-MAR-03, 12-DEC-03, 29-JAN-04?

  1. If you typed this line:

ORDER BY commission_pct

Which would come first, .20, .15, null

  1. True or FALSE: You can use a column alias in the ORDER BY clause if you have not used an alias in the SELECT clause.
  1. What is the maximum number of columns you can sort data by?
  1. True or FALSE: You can only sort by columns listed in the SELECT clause of your query.
  1. True or FALSE: When sorting by more than one column, all columns need to be ascending or descending order, you cannot mix.
  1. What will the output be of this statement (be very specific):

SELECT last_name, salary * .1 AS raises

FROM employees

WHERE job_id LIKE '%SA\_%' ESCAPE '\'

AND raises >2000

ORDER BY raises DESC, job_id