Chapter 7

A1: 
Select last_name, first_name, employment_date 
from employee 
where mod(to_number(to_char(employment_date,‘yy’)),3) = 0;
A2: 
Select last_name, first_name, avg(nvl(cost,0)) 
from employee, glasses 
where payroll_number = fk_payroll_number(+) 
group by last_name, first_name;
A3: 
Select fk_department, avg(nvl(cost,0)), round(avg(nvl(cost,0)), –1) 
from employee, glasses 
where payroll_number = fk_payroll_number(+) 
group by fk_department;
A4: 
Select fk_department, sum(cost), trunc(sum(cost),–2) 
from employee, glasses 
where payroll_number = fk_payroll_number(+) 
group by fk_department;
A5: 
Select last_name, first_name, to_char(add_months(birth_date, 21*12),‘Month 
yyyy’) 
From employee;
A6: 
 Select last_name, first_name, next_day(add_months(birth_date, ...

Get Oracle SQL and PL/SQL Handbook: A Guide for Data Administrators, Developers, and Business Analysts now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial