-- Part A
1.
select *
from employee
where empmsal < 1000;
2. ???
select deptno
from department
where empno is not null;
---
select distinct deptno
from employee
order by deptno;
3.
select *
from employee
where upper(empjob) = 'TRAINER' and empmsal < 2500 and deptno = 20;
4. ??? (Double quotes needed? --- yes)
select empname as "Name", empjob as "Job", empmsal as "Monthly Salary", empcomm as
"Commission"
from employee
where empmsal > empcomm;
5. ??? (upper() needed?)
select empname, empjob
from employee
where upper(empjob) like '%R';
6.
select empname, empjob
from employee
where upper(empname) like 'J%'
or upper(empname) like 'K%'
or upper(empname) like 'M%';
7.
select empname, empjob, empbdate, empmsal
from employee
where empbdate < TO_DATE('01-JAN-1990','dd-MON-YYYY') and empmsal > 1500;
8.
select empname, empjob, empbdate, empmsal
from employee
where empcomm is null;
9.
select empname, empjob, deptname, deptlocation, empmsal
from employee e join department d on [Link] = [Link]
where upper(deptlocation) = 'NEW YORK';
10.
select empname, empjob
from employee e join department d on [Link] = [Link]
where upper(deptlocation) != 'NEW YORK' and deptlocation != 'CHICAGO';
---
or we can use: NOT IN ('NEW YORK', 'CHICAGO')
11.
select empname, empjob, empbdate, empmsal
from employee
where empbdate between to_date('1970', 'yyyy') and to_date('1975', 'yyyy')
order by empbdate;
---
date should be exactly!!!
empbdate BETWEEN TO_DATE('01-JAN-1970','DD-MON-YYYY') AND TO_DATE('31-DEC-
1974','DD-MON-YYYY')
12.
select empname, empjob, empmsal
from employee
where empmsal < 1500 or empmsal > 3000;
---
or we can use: empmsal NOT BETWEEN 1500 and 3000
13.
select empname, empjob, mgrno
from employee
where mgrno is not null;
14. ???
select empname, empjob, deptname, deptlocation, empmsal
from employee e join department d on [Link] = [Link]
where (upper(deptlocation) = 'DALLAS' or
upper(empjob) = 'MANAGER'
) and empmsal > 2500;
15. ??? (salary grade?)
select empname, empjob, empmsal
from
order by
---
SELECT
[Link],
[Link],
[Link],
[Link]
FROM
[Link] e,
[Link] s
WHERE
[Link] BETWEEN [Link] AND [Link]
ORDER BY
[Link],
[Link];
------ OR
SELECT
[Link],
[Link],
[Link],
[Link]
FROM
[Link] e
JOIN [Link] s ON (
[Link] BETWEEN [Link] AND [Link]
)
ORDER BY
[Link],
[Link];
16. --- if all, then use left/right outer join!!!
select deptname, empname
from department d right outer join employee e on [Link] = [Link]
orderby deptname, empname;
17. ???
select [Link], [Link], [Link]
from employee e1 left outer join employee e2 on [Link] = [Link]
order by [Link], [Link];
18.
select empname, deptname, to_char(histbegindate, 'dd-Mon-yyyy'),
to_char(histenddate, 'dd-Mon-yyyy'), empmsal
from employee e join department d on [Link] = [Link] join history h on [Link]
= [Link]
order by empname, histbegindate DESC;
19. ???
selct empname, empjob, empmsal, 12*empmsal
from employee;
20.
selct empname, empjob, empmsal, empcomm, 12 * (empmsal + empcomm)
from employee;
---
SELECT
empname,
empjob,
empmsal,
empcomm,
12 * (empmsal + nvl(empcomm,0)) AS "Annual Income"
FROM
[Link]
ORDER BY empname;