Databases Reference
In-Depth Information
9.
Write a SELECT statement with a built-in function or functions that will format the string 'Queen' with the
'!' character padded for a total of 20 characters on the left side and with the '?' character padded for a total
of 30 characters on the right. (Hint: Use nested functions.)
Answer:
SELECT statement:
select rpad(lpad('Queen',20,'!'),30,'?') from dual;
RPAD(LPAD('QUEEN',20,'!'),30,'
------------------------------
!!!!!!!!!!!!!!!Queen??????????
10.
What functionality does the Oracle TIMESTAMP datatype have over the DATE datatype?
Answer:
The TIMESTAMP datatype stores the time in seconds to up to nine digits of precision.
Chapter 4
1.
Rewrite the following expression using the CONCAT function.
last_name || ', ' || first_name
Answer:
The expression is rewritten as:
concat(concat(last_name, ', '),first_name)
2.
What are two ways that you can indicate a comment in a SQL command?
Answer:
You can indicate a comment in a SQL command by using /* and */ or by using --.
3.
The SQL engine converts the IN operator to a series of ________.
Answer:
The SQL engine converts the IN operator to a series of OR operations.
4.
Rewrite the following WHERE clause to be case insensitive.
where job_title like '%Manager%';
Answer:
Use the UPPER function to convert the job title to uppercase:
where UPPER(job_title) like '%MANAGER%';
5.
What is the only group function that counts NULL values in its calculation without using NVL or other
special processing?
Answer:
The COUNT group function using the syntax COUNT(*) counts NULL values without using NVL.
6.
The query results from using aggregate functions with a GROUP BY clause can be filtered or restricted by using
what clause?
Answer:
The HAVING clause filters or restricts the query results of the GROUP BY clause.
7.
Identify the two special characters used with the LIKE operator and describe what they do.
Answer:
The % character matches zero or more characters, and the _ character matches exactly one character.
