SECTION 2 LESSON 5
2. Create two different queries that produce the cd number and title as "94CARPE DIEM." Use the d_cds table in the DJ on Demand database.

----select cd_number//''//upper(title)
from d_cds;
select concat(cd_number,upper(title))
from d_cds;

3. Mark the following statements as True or False.
__f__a. LOWER converts numbers to lowercase.
__f__b. Use RPAD to move numbers to the right to place an * on the left.
__f__c.. TRIM can be used to trim one or more characters from a string.
__t__d. LENGTH returns a number.
__f__e. SUBSTR is used to substitute one string for another.
__t__f. CONCAT is limited to using two parameters.
__t__g. TRUNC will return zero decimal places if a decimal value is omitted.

4. Create a query to show the cost of events for DJ on Demand in the format $0000.00

----select to_char(cost,'$99999.99')
from d_events;

5. For the f_staffs table in the Global Fast Foods database, change the ID of those staff members whose IDs are only single digits to two digits by adding an asterisk * to the front of the number. For example, change ID 9 to ID *9.

----select last_name,first_name,lpad(id,2,'*')
from f_staffswhere length(id)=1;

6. As the database administrator, you have been asked to store historical employee records in the current database. The records have termination dates from 1990 to 1999. Write a query using DUAL to show how you could store 15-DEC-95.

----insert into dual
value('15-dec-95');

7. Using DUAL, format 19-JUN-04 to appear as: 19th of june two thousand four

----select to_char('19-JUN-04','DD')' of 'to_char('19-JUN-04','month year')
from dual;

8. Create a query that will return only the last word from "Oracle Academy."

----select substr('Oracle Academy.',8,7)
from dual;

9. Lance and Arnie created the following SQL query but it did not return any results. What is the problem with this query?
----SELECT loc_type
FROM d_venues
WHERE ID = 200;

10. What type of function would you use in each case?D = Date function,N = Number function,C = Conversion/Character functions,G = General function,CE = Conditional expression
__n__a. To convert varchar2 to number data
__c__b. To format a date to other than the default format
__c__c. To convert a date such as June 19, 2000 to default format
__n__d. To format a number to appear as currency
__g__e. To substitute a value in a table for null
__ce_f. To do an IF-THEN-ELSE statement
__g__g. To find the first not null expression among a list of expressions
__c__h. To replace a section of a string with another string
__d__i. To format a 20th-century date
__c__j. To present output all in uppercase
_____k. To find the numeric position of a character in a string
_____l. To find the last day of the month