DBS425 Sections: Assignment 1                Posted: October 6/99

1)  Without changing any value in the database, query the EMP table to display employee names and their salaries after a 10% raise; give the resulting column a new heading called NEW SALARY.
Prepare the output as in the example below:

NEW SALARY
-------------------------------------------------------
The salary of KING after a 10% raise is 5500
The salary of BLAKE after a 10% raise is 3135
The salary of CLARK after a 10% raise is 2695
.
.
Solution:
select 'The salary of '||rtim(ename)||' after a 10% raise is '||sal*1.1 "NEW SALARY"
from emp;

2) Qualify the previous SQL statement to give the raise only to employees who do not have a commission.
Solution:
select 'The salary of '||rtim(ename)||' after a 10% raise is '||sal*1.1 "NEW SALARY"
from emp
where comm is NULL;

3) Show the name, job, salary, commission and the difference between these two for those employees whose salary is higher than their commission. Sort by the difference between salary and commission with the highest difference displayed first.  Similar to the following example (your data may be different than my example):

ENAME                JOB                                     SAL          COMM         DIFF
------------------- -------------------------       ------        ---------        ---------
Sedeghi                    Sales Representative           1515             152              1363
Murphy                    Sales Representative           1490             186             1304
Nguyen                    Sales Representative           1525             229            1296
Magee                      Sales Representative           1400            140             1260
Dumas                      Sales Representative           1450            254             1196
Solution:
select initcap(ename), initcap(job), sal, comm, sal-comm DIFF
from emp
where sal>comm
order by diff desc;

4) Show employee, department and job title for those employees whose jobs are in the SALES category and work in department 30.
Solution:
select ename, deptno, job
from emp
where upper(job) like '%SALES%'
and deptno = 30;
(Note: you could also have joined the dept table and print the dept name)

5) Show all the data of those employees whose last names have an A in the second position, have been hired after January 1, 1981 and do not work in department 10, or those employees whose last names begin with the letter T, have N as the fourth letter and end with the letter R (regardless of when they were hired and what department they belong to).
Solution:
select * from emp
where (ename like 'A__%' and hiredate > '01-JAN-81' and deptno <>10)
      or rtrim(ename) like 'T__N%R';      ---> if ename was defined as CHAR in the table
                          ename like 'T__N%R' ----> if ename was defined as VARCHAR2
 
Another solution is:
select * from emp
where (ename like 'A__%' and hiredate > '01-JAN-81' and deptno <>10)
      or (ename like 'T__N%' and substr(rtrim(ename),-1) = 'R' ;  ---> if ename was defined as CHAR in the table
                                                      substr(ename,-1) = 'R'               ---> if ename was defined as VARCHAR2
 

6) Monthly salaries are stored in the EMP table. Based on a five-day working week of 40 hours (20 days per month), write a query to calculate the hourly rate of employees. Round the rates to 2 decimal points, then truncate the rates to 2 decimal points, finally round and truncate again with no decimal points. All in one query.
You should have one column for each calculation, preceded by the employee name. Give each calculated column a meaningful heading, such as Hourly Rate, Round 2Dec, Trunc 2Dec, etc.
Solution:
select ename,
sal/160 "Hourly Rate",
round(sal/160, 2) "Round 2 Dec",
trunc(sal/160, 2) "Trunc 2 Dec",
round(sal/160, 0) "Round No Dec",
trunc(sal/160, 0) "Trunc No Dec"
from emp;

7) Write a query  to show the name of all employees together with the number of years and the number of completed months they have been employed.
Solution:
select ename, hiredate, trunc(months_between(sysdate , hiredate) /12) "Years",
 mod(trunc(months_between(sysdate , hiredate)),12) "Months"
 from emp;
It produces this kind of result. (You coulds also concatenate the columns and create one heading only).

ENAME       Years    Months
----------  --------- ---------
KING                   17        11
BLAKE                 18         5
CLARK                 18         4
JONES                  18         6
...etc.
...

8) Write a query to show the employee names, their job title, department and the manager to whom they report. Sort the results by manager in ascending order starting with the higher hiredate.
Solution:
select e.ename, e.job, e.deptno, m.ename MGR
from emp e, emp m
where e.mgr = m.empno
order by m.ename, e.hiredate desc;

9) Analyze the data in the employee table. Write a query to display the entire content of the table but make sure you replace any null values (in any column) with the literal No Data Available.

10)  Write a query  to display the employee names as they are in the database (uppercase) together with the names in lowercase and with initial capital. Give each column the following headings: Upper, Lower, InitCap