SECTION 2 LESSON 4
1. The business manager of Global Fast Foods needs to update the customer list. She wants to find any zip/postal code that has fewer than 10 digits in order to identify those codes without the new format postfix 87392-8723. Create a query to identify those customers.

----select last_name//' '//first_name,zip
from f_customers
where length(zip)<10;>
a. LPAD(c) b. ROUND (n,d)
c. TRUNC(n,d) d. LENGTH(c)
e. LAST_DAY (d) f. INSTR(c) g. CONCAT (c)

3. You need to display the auth_expense_amt for each DJ on Demand partner. For those partners who do not have an expense account, the output should display "Not Approved."

----select last_name//' '//first_name,nvl(to_char(auth_expense_amt),'Not approved.')
from d_partners;

4. Jason and Jamie tried to run a report displaying the Global Fast Foods staff members who do not have an overtime rate. They wrote the following SQL query and received a report error: ORA-01722: invalid number.

SELECT first_name, last_name, NVL(overtime_rate, 'no overtime') As "Payrate"
FROM f_staffs;
What is wrong with their query and how can it be fixed?

----SELECT first_name, last_name, NVL(to_char(overtime_rate), 'no overtime') As "Payrate"
FROM f_staffs;

5. The president of Global Fast Foods likes to send a birthday card to all employees. He has asked you to send a reminder to him on the month of the employee's birthday so the card can be sent with that month's paycheck. Prepare a query to produce the table shown.

----select last_name//' '//first_name,trunc(birthdate,'month')
from f_staffs;

6. For each statement, mark T if the statement is True or F if the statement is False.

a. TO_CHAR is required to convert the date '03-JUN-04' to June 3, 2004.(f)
b. TO_NUMBER will convert '23-NOV-02' to use with ADD_MONTHS.(f)
c. TO_DATE will convert SYSDATE to today's date.(f)
d. TO_NUMBER('101', '$99999') will convert 101 to a number.(f)
e. TO_CHAR(salary, '$9999.99') will convert number to character format.(t)
f. TO_NUM(varchar2 column) will convert character data to a number.(t)
g. TO_CHAR(SYSDATE, 'Month fmdd, yyyy') will format the date. (f)

7. Employees in the Oracle database who have worked more than 10 years will be given one extra week of vacation. Create a report showing first name, last name, and years worked. Round the result to one decimal place.

----select last_name,first_name,round(months_between(sysdate,hire_date)/12,0)
from employees
where months_between(sysdate,hire_date)/12 > 10;

8. The manager of DJ on Demand needs you to change the zip code for the ID 105, New York venues. She wants to show the old zip code in one column and the new zip code in another column. The new zip code is the same as the old zip code except -2345 needs to be added to the end. The output should appear as shown in the table.

----select id,zip,concat(to_char(zip),'-2345')"new zip"
from dualwhere id=105;

9. Create a query using one SELECT statement that returns today's date. Assign an alias to each column.
a. rounded to the nearest year
b. rounded to the nearest month
c. truncated to the nearest year
d. truncated to the nearest month

----select round(sysdate,'year') from dual;
select round(sysdate,'month') from dual;
select trunc(sysdate,'year') from dual;
select trunc(sysdate,'month') from dual;

10. You need to find out how many days it has been since the start of the Global Fast Foods promotional menus. Round the result to the nearest day.

----select round(months_between(sysdate,start_date)*30.5,0)"nearest day"
from f_promotional_menus;

11. The Human Resources department (Oracle database) has decided that, for their purposes, the job title for all employees will be the first five letters of the job title followed by an asterisk. For example, the accounting manager will be changed to accou* Create a query to accomplish this request.

----select rpad(substr(job_title,1,5),6,'*')
from jobs;

12. What is the order of operations in question 11?

----pregdigest