-- Part A
1.
select empno, empname, empjob, empmsal, 12 * empmsal as "Annual Salary", 12 *
empmsal * 1.1 as "Annual Salary After Pay Rise"
from employee;
2. ???
select empname, to_char(empbdate, 'dd-Mon-yyyy'), floor((sysdate - empbdate) / 12)
as "Age in years"
from employee;
--- USE months_between to calculate the age!!
floor(months_between(SYSDATE,empbdate) / 12) AS "Age in Yrs"
3.
select empno, empname, empjob, empmsal, empcomm, 12 * (empmsal + NVL(empcomm, 0))
as "Annual Salary",
from employee;
4.
select 'EMPLOYEE ' || empname || ' IS A ' || empjob || ' AND WORKS IN THE ' ||
deptname || ' DEPARTMENT'
from employee e join department d on e.deptno = d.deptno;
--- REMEMBER to use initcap() !!!
5. ???
select empname, to_char(empbdate, 'dd-Mon-yyyy'), floor((sysdate - empbdate)) as
"Age in months"
from employee;
--- USE months_between to calculate the age!!
round(
months_between(SYSDATE,empbdate),
1
) AS age_in_months
6.
select empname
from employee
where to_char(empbdate, 'Mon') = 'Feb';
7. select empname, empmsal, empcomm,
from employee
where empcomm = GREATEST(empsal, empcomm);
8. ??? (How to display week?)
select 'EMPLOYEE ' || empname || ' was born on ' || extract(week from empbdate)
|| ' the ' || extract(day from empbdate) || ' of ' || extract(month from empbdate)
|| ', ' || extract(year from empbdate)
from employee;
--- use to_char!!!
SELECT
'EMPLOYEE '
|| empinit
|| '. '
|| initcap(empname)
|| ' was born on '
|| rtrim(TO_CHAR(empbdate,'DAY') )
|| ' the '
|| EXTRACT(DAY FROM empbdate)
|| ' of '
|| rtrim(TO_CHAR(empbdate,'MONTH') )
|| ','
|| EXTRACT(YEAR FROM empbdate) as "EMP DOB Details"
FROM
payroll.employee
ORDER BY empname;
9.
select empname, count(*)
from employee e join registration r on e.empno = r.empno
group by empname;
10.
select empno, empname, to_char(empbdate, 'dd-Mon-yyyy')
from employee
where empbdate = (select min(empbdate) from employee);
11.
select
deptno,
deptname,
count(empno) as "NbrOfEmployees",
min(empmsal) as "MinSalary",
max(empmsal) as "MaxSalary",
sum(empmsal) as 'TotalSalary',
avg(empmsal) as 'AvgSalary'
from department d left outer join employee e on d.deptno = e.deptno
group by deptno, deptname;
12. ???
select deptname, distinct empjob, empmsal
from from employee e join department d on e.deptno = d.deptno;
---Correct ans: (if "for each", consider using group by!!!)
SELECT
deptno,
empjob,
SUM(empmsal)
FROM
payroll.employee
GROUP BY
deptno,
empjob
ORDER BY deptno,empjob;
13.
select empno, empname, empmsal
from employee
where empmsal > (select avg(empmsal) from employee);
14.
select deptno, deptname, avg(empmsal)
from employee e join department d on e.deptno = d.deptno
group by deptno, deptname
having avg(empmsal) =
(select max(avg(empmsal))
from employee e join department d on e.deptno = d.deptno
group by deptno);
15.
select crscode, crsdesc, count(*) as "no of offerings"
from course c join offering o on c.crscode = o.crscode
group by crscode, crsdesc
having count(*) = (select max(count(*)) from course c join offering o on c.crscode
= o.crscode group by crscode, crsdesc)
16.
select empname, empjob, to_char(empbdate, 'dd-Mon-yyyy')
from employee
where upper(empname) != 'SCOTT'
and empjob = (select empjob from employee where upper(empname) = 'SCOTT')
and to_char(empbdate, 'yyyy') = (select to_char(empbdate, 'yyyy') from employee
where upper(empname) = 'SCOTT');
17.
select empno, empname
from employee
MINUS
select distinct empno, empname
from employee e join registration r on e.empno = r.empno;
--- distinct only use on one attribute is enough!!!
18. ???
select empno, empname
from registation
INTERSECT
select empno, empname
from offering;
---Correct ans:
SELECT DISTINCT
e.empno AS "TrainerAndStudent",
empname
FROM
payroll.offering o join payroll.employee e on (o.empno = e.empno)
INTERSECT SELECT DISTINCT
e.empno,
empname
FROM
payroll.registration r join payroll.employee e on (r.empno = e.empno)
ORDER BY "TrainerAndStudent";
