Answers to exercises given in 9-DEC-2011 batch related to HR Schema in racle Database 11 11g g Here are the answers to excercises related to queries and PL/SQL programs given in 9-DEC-2011 Oracle Database 11g batch !he "ollowing is the structure o" the tables provided b# Oracle in Human $esource Schema %H$&
Note:
'olumns in $(D color indicate primar# )e#%s&
!"eries
1 Displa# details o" *obs where the minimum salar# is greater than 1++++ SELECT * FROM JOBS WHERE MIN_SALARY > 10000
, Displa# the "irst name and *oin date o" the emplo#ees who *oined between ,++, and ,++- SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE, 'YYYY') BETWEEN 2002 AND 2005 ORDER BY HIRE_DATE
. Displa# "irst name and *oin date o" the emplo#ees who is either ! Programmer or Sales 0an SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES WHERE JOB_ID IN ('IT_PRO', 'SA_MAN')
Displa# emplo#ees who *oined a"ter 1st 2anuar# ,++3 SELECT * FROM EMPLOYEES
!"#$# "%$#_&# > '01+200'
- Displa# details o" emplo#ee with D 1-+ or 14+ SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID %+ (150,1-0)
4 Displa# "irst name5 salar#5 commission pct5 and hire date "or emplo#ees with salar# less than 1++++ SELECT FIRST_NAME, SALARY, COMMISSION_PCT, HIRE_DATE FROM EMPLOYEES WHERE SALARY . 10000
6 Displa# *ob !itle5 the di""erence between minimum and maximum salaries "or *obs with max salar# in the range 1++++ to ,++++ SELECT JOB_TITLE, MA/_SALARYMIN_SALARY DIFFERENCE FROM JOBS WHERE MA/_SALARY BETWEEN 10000 AND 20000
3 Displa# "irst name5 salar#5 and round the salar# to thousands SELECT FIRST_NAME, SALARY, ROND(SALARY, ) FROM EMPLOYEES
7 Displa# details o" *obs in the descending order o" the title SELECT * FROM JOBS ORDER BY JOB_TITLE
1+ Displa# emplo#ees where the "irst name or last name starts with S SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE OR LAST_NAME LIE 'S3'
11
FIRST_NAME
LIE 'S3'
Displa# emplo#ees who *oined in the month o" 0a#
SELECT * FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE, 'MON')4 'MAY'
1, Displa# details o" the emplo#ees where commission percentage is null and salar# in the range -+++ to 1++++ and department is .+ SELECT * FROM EMPLOYEES WHERE COMMISSION_PCT IS NLL AND SALARY BETWEEN 5000 AND 10000 AND DEPARTMENT_ID40
1. Displa# "irst name and date o" "irst salar# o" the emplo#ees SELECT FIRST_NAME, HIRE_DATE, LAST_DAY(HIRE_DATE)1 FROM EMPLOYEES
1
Displa# "irst name and experience o" the emplo#ees
SELECT FIRST_NAME, HIRE_DATE, FLOOR((SYSDATEHIRE_DATE)6-5)FROM EMPLOYEES
1-
Displa# "irst name o" emplo#ees who *oined in ,++1
SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE, 'YYYY')42001
14 Displa# "irst name and last name a"ter converting the "irst letter o" each name to upper case and the rest to lower case SELECT INITCAP(FIRST_NAME), INITCAP(LAST_NAME) FROM EMPLOYEES
16
Displa# the "irst word in *ob title
SELECT JOB_TITLE, JOBS
SBSTR(JOB_TITLE,1, INSTR(JOB_TITLE, ' ')1) FROM
13 Displa# the length o" "irst name "or emplo#ees where last name contain character 8b9 a"ter .rd position
SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE INSTR(LAST_NAME,'B') >
17 Displa# "irst name in upper case and email address in lower case "or emplo#ees where the "irst name and email address are same irrespective o" the case SELECT PPER(FIRST_NAME), LOWER(EMAIL) FROM EMPLOYEES WHERE PPER(FIRST_NAME)4 PPER(EMAIL)
,+
Displa# emplo#ees who *oined in the current #ear
SELECT * FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'YYYY')4TO_CHAR(SYSDATE, 'YYYY')
,1 Displa# the number o" da#s between s#stem date and 1st 2anuar# ,+11 SELECT SYSDATE 7_&#('01+2011') FROM DAL
,, Displa# how man# emplo#ees *oined in each month o" the current #ear SELECT TO_CHAR(HIRE_DATE,'MM'), CONT (*) FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'YYYY')4 TO_CHAR(SYSDATE,'YYYY') ROP BY TO_CHAR(HIRE_DATE,'MM')
,. Displa# manager D and number o" emplo#ees managed b# the manager SELECT MANAER_ID, CONT(*) FROM EMPLOYEES ROP BY MANAER_ID
, Displa# emplo#ee D and the date on which he ended his previous *ob SELECT EMPLOYEE_ID, MA/(END_DATE) FROM JOB_HISTORY ROP BY EMPLOYEE_ID
,- Displa# number o" emplo#ees *oined a"ter 1-th o" the month SELECT CONT(*) FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'DD') > 15
,4 Displa# the countr# D and number o" cities we have in the countr# SELECT CONTRY_ID,
CONT(*)
FROM LOCATIONS ROP BY CONTRY_ID
,6 Displa# average salar# o" emplo#ees in each department who have commission percentage SELECT DEPARTMENT_ID, A8(SALARY) FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NLL ROP BY DEPARTMENT_ID
,3 Displa# *ob D5 number o" emplo#ees5 sum o" salar#5 and di""erence between highest salar# and lowest salar# o" the emplo#ees o" the *ob SELECT JOB_ID, CONT(*), SM(SALARY), MA/(SALARY)MIN(SALARY) SALARY FROM EMPLOYEES ROP BY JOB_ID
,7 Displa# *ob D "or *obs with average salar# more than 1++++ SELECT JOB_ID, A8(SALARY) FROM EMPLOYEES ROP BY JOB_ID HA8IN A8(SALARY)>10000
.+
Displa# #ears in which more than 1+ emplo#ees *oined
SELECT TO_CHAR(HIRE_DATE,'YYYY') FROM EMPLOYEES ROP BY TO_CHAR(HIRE_DATE,'YYYY') HA8IN CONT(EMPLOYEE_ID) > 10
.1 Displa# departments in which more than "ive emplo#ees have commission percentage SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NLL ROP BY DEPARTMENT_ID HA8IN CONT(COMMISSION_PCT)>5
., Displa# emplo#ee D "or emplo#ees who did more than one *ob in the past SELECT EMPLOYEE_ID FROM JOB_HISTORY ROP BY EMPLOYEE_ID HA8IN CONT(*) > 1
.. Displa# *ob D o" *obs that were done b# more than . emplo#ees "or more than 1++ da#s SELECT JOB_ID FROM JOB_HISTORY WHERE END_DATESTART_DATE > 100 ROP BY JOB_ID HA8IN CONT(*)>
. Displa# department D5 #ear5 and :umber o" emplo#ees *oined SELECT DEPARTMENT_ID, TO_CHAR(HIRE_DATE,'YYYY'), CONT(EMPLOYEE_ID) FROM EMPLOYEES ROP BY DEPARTMENT_ID, TO_CHAR(HIRE_DATE, 'YYYY') ORDER BY DEPARTMENT_ID
.- Displa# departments where an# manager is managing more than - emplo#ees SELECT DISTINCT DEPARTMENT_ID FROM EMPLOYEES ROP BY DEPARTMENT_ID, MANAER_ID HA8IN CONT(EMPLOYEE_ID) > 5
.4 'hange salar# o" emplo#ee 11- to 3+++ i" the existing salar# is less than 4+++ PDATE EMPLOYEES SET SALARY 4 000 WHERE EMPLOYEE_ID 4 115 AND SALARY . -000
.6 nsert a new emplo#ee into emplo#ees with all the required details INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NMBER, HIRE_DATE,JOB_ID, SALARY, DEPARTMENT_ID) 8ALES (209, 'ANELA', 'SNYDER','ANELA','215 25 :99', SYSDATE, 'SA_MAN', 12000, 0)
.3
Delete department ,+
DELETE FROM DEPARTMENTS WHERE DEPARTMENT_ID420
.7 'hange *ob D o" emplo#ee 11+ to !;P$O< i" the emplo#ee belongs to department 1+ and the existing *ob D does not start with ! PDATE EMPLOYEES SET JOB_ID4 'IT_PRO' WHERE EMPLOYEE_ID4110 AND DEPARTMENT_ID410 AND NOT JOB_ID LIE 'IT3'
+ nsert a row into departments table with manager D 1,+ and location D in an# location D "or cit# !o)#o INSERT INTO DEPARTMENTS (150,'SPORTS',120,1200)
1 Displa# department name and number o" emplo#ees in the department SELECT DEPARTMENT_NAME, CONT(*) FROM EMPLOYEES NATRAL JOIN DEPARTMENTS ROP BY DEPARTMENT_NAME
, Displa# *ob title5 emplo#ee D5 number o" da#s between ending date and starting date "or all *obs in department .+ "rom *ob histor# SELECT EMPLOYEE_ID, JOB_TITLE, END_DATESTART_DATE DAYS FROM JOB_HISTORY NATRAL JOIN JOBS WHERE DEPARTMENT_ID40
.
Displa# department name and manager "irst name
SELECT DEPARTMENT_NAME, FIRST_NAME FROM DEPARTMENTS D JOIN EMPLOYEES E ON (D;MANAER_ID4E;EMPLOYEE_ID)
Displa# department name5 manager name5 and cit#
SELECT DEPARTMENT_NAME, FIRST_NAME, CITY FROM DEPARTMENTS D JOIN EMPLOYEES E ON (D;MANAER_ID4E;EMPLOYEE_ID) JOIN LOCATIONS L SIN (LOCATION_ID)
-
Displa# countr# name5 cit#5 and department name
SELECT CONTRY_NAME, CITY, DEPARTMENT_NAME FROM CONTRIES JOIN LOCATIONS SIN (CONTRY_ID) JOIN DEPARTMENTS SIN (LOCATION_ID)
4 Displa# *ob title5 department name5 emplo#ee last name5 starting date "or all *obs "rom ,+++ to ,++- SELECT JOB_TITLE, DEPARTMENT_NAME, LAST_NAME, START_DATE FROM JOB_HISTORY JOIN JOBS SIN (JOB_ID) JOIN DEPARTMENTS SIN (DEPARTMENT_ID) JOIN EMPLOYEES SIN (EMPLOYEE_ID) WHERE TO_CHAR(START_DATE,'YYYY') BETWEEN 2000 AND 2005
6
Displa# *ob title and average salar# o" emplo#ees
SELECT JOB_TITLE, A8(SALARY) FROM EMPLOYEES NATRAL JOIN JOBS ROP BY JOB_TITLE
3 Displa# *ob title5 emplo#ee name5 and the di""erence between maximum salar# "or the *ob and salar# o" the emplo#ee
SELECT JOB_TITLE, FIRST_NAME, MA/_SALARYSALARY DIFFERENCE FROM EMPLOYEES NATRAL JOIN JOBS
7 Displa# last name5 *ob title o" emplo#ees who have commission percentage and belongs to department .+ SELECT JOB_TITLE, FIRST_NAME, MA/_SALARYSALARY DIFFERENCE FROM EMPLOYEES NATRAL JOIN JOBS WHERE DEPARTMENT_ID 4 0
-+ Displa# details o" *obs that were done b# an# emplo#ee who is currentl# drawing more than 1-+++ o" salar# SELECT JH;* FROM JOB_HISTORY JH JOIN EMPLOYEES E ON (JH;EMPLOYEE_ID 4 E;EMPLOYEE_ID) WHERE SALARY > 15000
-1 Displa# department name5 manager name5 and salar# o" the manager "or all managers whose experience is more than - #ears SELECT DEPARTMENT_NAME, FIRST_NAME, SALARY FROM DEPARTMENTS D JOIN EMPLOYEES E ON (D;MANAER_ID4E;MANAER_ID) WHERE (SYSDATEHIRE_DATE) 6 -5 > 5
-, Displa# emplo#ee name i" the emplo#ee *oined be"ore his manager SELECT FIRST_NAME FROM EMPLOYEES E1 JOIN EMPLOYEES E2 ON (E1;MANAER_ID4E2;EMPLOYEE_ID) WHERE E1;HIRE_DATE . E2;HIRE_DATE
-. Displa# emplo#ee name5 *ob title "or the *obs emplo#ee did in the past where the *ob was done less than six months SELECT FIRST_NAME, JOB_TITLE FROM EMPLOYEES E JOIN JOB_HISTORY JH ON (JH;EMPLOYEE_ID 4 E;EMPLOYEE_ID) JOIN JOBS J ON( JH;JOB_ID 4 J;JOB_ID) WHERE MONTHS_BETWEEN(END_DATE,START_DATE) . -
- Displa# emplo#ee name and countr# in which he is wor)ing SELECT FIRST_NAME, CONTRY_NAME FROM EMPLOYEES JOIN DEPARTMENTS SIN(DEPARTMENT_ID) JOIN LOCATIONS SIN( LOCATION_ID) JOIN CONTRIES SIN ( CONTRY_ID)
-- Displa# department name5 average salar# and number o" emplo#ees with commission within the department SELECT DEPARTMENT_NAME, A8(SALARY), CONT(COMMISSION_PCT) FROM DEPARTMENTS JOIN EMPLOYEES SIN (DEPARTMENT_ID) ROP BY DEPARTMENT_NAME
-4 Displa# the month in which more than - emplo#ees *oined in an# department located in S#dne# SELECT TO_CHAR(HIRE_DATE,'MONYY') FROM EMPLOYEES JOIN DEPARTMENTS SIN (DEPARTMENT_ID) JOIN SIN (LOCATION_ID) WHERE CITY 4 'S#<#' ROP BY TO_CHAR(HIRE_DATE,'MONYY') HA8IN CONT(*) > 5
LOCATIONS
-6 Displa# details o" departments in which the maximum salar# is more than 1++++ SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_ID IN ( SELECT DEPARTMENT_ID FROM EMPLOYEES ROP BY DEPARTMENT_ID HA8IN MA/(SALARY)>10000)
-3
Displa# details o" departments managed b# 8Smith9
SELECT * FROM DEPARTMENTS WHERE MANAER_ID IN (SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE FIRST_NAME4'SMITH')
-7 Displa# *obs into which emplo#ees *oined in the current #ear SELECT * FROM JOBS WHERE JOB_ID IN (SELECT JOB_ID FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'YYYY')4TO_CHAR(SYSDATE,'YYYY'))
4+
Displa# emplo#ees who did not do an# *ob in the past
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID NOT IN (SELECT EMPLOYEE_ID FROM JOB_HISTORY)
41 Displa# *ob title and average salar# "or emplo#ees who did a *ob in the past SELECT JOB_TITLE, A8(SALARY) FROM JOBS NATRAL JOIN EMPLOYEES ROP BY JOB_TITLE
WHERE EMPLOYEE_ID IN (SELECT EMPLOYEE_ID FROM JOB_HISTORY)
4, Displa# countr# name5 cit#5 and number o" departments where department has more than - emplo#ees SELECT CONTRY_NAME, CITY, CONT(DEPARTMENT_ID) FROM CONTRIES JOIN LOCATIONS SIN (CONTRY_ID) JOIN DEPARTMENTS SIN (LOCATION_ID) WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES ROP BY DEPARTMENT_ID HA8IN CONT(DEPARTMENT_ID)>5) ROP BY CONTRY_NAME, CITY=
4. Displa# details o" manager who manages more than emplo#ees SELECT FIRST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID IN (SELECT MANAER_ID FROM EMPLOYEES ROP BY MANAER_ID HA8IN CONT(*)>5)
4 Displa# emplo#ee name5 *ob title5 start date5 and end date o" past *obs o" all emplo#ees with commission percentage null SELECT FIRST_NAME, JOB_TITLE, START_DATE, END_DATE FROM JOB_HISTORY JH JOIN JOBS J SIN (JOB_ID) JOIN EMPLOYEES E ( JH;EMPLOYEE_ID 4 E;EMPLOYEE_ID) WHERE COMMISSION_PCT IS NLL
ON
4- Displa# the departments into which no emplo#ee *oined in last two #ears SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_ID NOT IN ( SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE FLOOR((SYSDATE HIRE_DATE)6-5) . 2)
44 Displa# the details o" departments in which the max salar# is greater than 1++++ "or emplo#ees who did a *ob in the past SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES
WHERE EMPLOYEE_ID IN (SELECT EMPLOYEE_ID FROM JOB_HISTORY) ROP BY DEPARTMENT_ID HA8IN MA/(SALARY) >10000)
46 Displa# details o" current *ob "or emplo#ees who wor)ed as ! Programmers in the past SELECT * FROM JOBS WHERE JOB_ID IN (SELECT JOB_ID FROM EMPLOYEES WHERE EMPLOYEE_ID IN (SELECT EMPLOYEE_ID FROM JOB_HISTORY WHERE JOB_ID4'IT_PRO'))
43 Displa# the details o" emplo#ees drawing the highest salar# in the department SELECT DEPARTMENT_ID,FIRST_NAME, SALARY FROM EMPLOYEES OTER WHERE SALARY 4 (SELECT MA/(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID 4 OTER;DEPARTMENT_ID)
47
Displa# the cit# o" emplo#ee whose emplo#ee D is 1+-
SELECT CITY FROM LOCATIONS WHERE LOCATION_ID 4 (SELECT LOCATION_ID FROM DEPARTMENTS WHERE DEPARTMENT_ID 4 (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID4105) )
6+
Displa# third highest salar# o" all emplo#ees
#<#? <$@ $7 #<7@## %+ !"#$# 2 4 (#<#? ?7+( &%%+? <$@ ) $7 #<7@## !"#$# <$@ > %+;<$@)
#$%S!$ #rograms 1 =rite a program to interchange the salaries o" emplo#ee 1,+ and 1,, D#?<$# 8_<$@_120 #<7@##;<$@3@#= B#%+ S#<#? <$@ %+7 _<$@_120 F$7 #<7@## !"#$# #<7@##_%& 4 120= &# #<7@## # <$@ #<7@##_%& 4 122) W"#$# #<7@##_%& 4 120=
4 ( #<#? <$@ $7 #<7@## !"#$#
&# #<7@## # <$@
4
_<$@_120
W"#$# #<7@##_%& 4 122=
C7%= E+&=
, ncrease the salar# o" emplo#ee 11- based on the "ollowing conditions> " experience is more than 1+ #ears5 increase salar# b# ,+? " experience is greater than - #ears5 increase salar# b# 1+? Otherwise -? 'ase b# (xpression> &#?<$# _#G +#$(2)= _"%# +#$(5,2)= #%+ #<#? <77$((@&#"%$#_&#) 6 -5 ) %+7 _#G $7 #<7@## !"#$# #<7@##_%& 4 115= _"%# 4 1;05= ?# !"#+
_#G > 10 "#+ _"%# 4 1;20= !"#+ _#G > 5 "#+ _"%# 4 1;10= #+& ?#= &# #<7@## # <$@ 4 <$@ * _"%# !"#$# #<7@##_%& 4 115= #+&=
. 'hange commission percentage as "ollows "or emplo#ee with D @ 1-+ " salar# is more than 1++++ then commission is +?5 i" Salar# is less than 1++++ but experience is more than 1+ #ears then +.-?5 i" salar# is less than .+++ then commission is +,-? n the remaining cases commission is +1-? &#?<$# _<$@ #<7@##;<$@3@#= _#G +#$(2)= _? +#$(5,2)= #%+ #<#? _<$@, <77$ ( (@&#"%$#_&#)6-5) %+7 _<$@, _#G $7 #<7@## !"#$# #<7@##_%& 4 150=
% _<$@ > 10000 "#+ _? 4 0;:= #<% _#G > 10 "#+ _? 4 0;5= #<% _<$@ . 000 "#+ _? 4 0;25= #<# _? 4 0;15= #+& %= &# #<7@## # ?7%%7+_? 4 _? !"#$# #<7@##_%& 4 150=
#+&=
Aind out the name o" the emplo#ee and name o" the department "or the emplo#ee who is managing "or emplo#ee 1+. &#?<$# _+# #<7@##;%$_+#3@#= _&#+# &#$#+;&#$#+_+#3@#= #%+ #<#? %$_+# , &#$#+_+# %+7 _+#, _&#+# $7 #<7@## 7%+ &#$#+ %+ (&#$#+_%&) !"#$# #<7@##_%& 4 ( #<#? +#$_%& $7 #<7@## !"#$# #<7@##_%& 4 10)=
&_7;_<%+#(_+#)= &_7;_<%+#(_&#+#)=
#+&=
- Displa# missing emplo#ee Ds &#?<$# _%+ +#$()= _G +#$()= _? +#$(1)= #%+ #<#? %+(#<7@##_%&), G(#<7@##_%&) %+7 _%+, _G $7 #<7@##=
7$ % %+ _%+ 1 ;; _G 1 <77 #<#? ?7+(*) %+7 _? $7 #<7@## !"#$# #<7@##_%& 4 %=
%
_? 4 0 "#+ &_7;_<%+#(%)= #+& %= #+& <77=
#+&=
4 Displa# the #ear in which maximum number o" emplo#ees *oined along with how man# *oined in each month in that #ear &#?<$# _@#$ _?
+#$(:)= +#$(2)=
#%+ #<#? 7_?"$("%$#_&#,'@@@@') %+7 _@#$ $7 #<7@## $7 @ 7_?"$("%$#_&#,'@@@@') "%+ ?7+(*) 4 ( #<#? G( ?7+(*)) $7 #<7@## $7 @ 7_?"$("%$#_&#,'@@@@'))= &_7;_<%+#('Y#$ ' KK _@#$)= 7$ 7+" %+ 1 ;; 12 <77 #<#? ?7+(*) %+7 _? $7 #<7@## !"#$# 7_?"$("%$#_&#,'') 4 7+" +& 7_?"$("%$#_&#,'@@@@') 4 _@#$= &_7;_<%+#('M7+" ' KK 7_?"$(7+") KK ' E<7@## ' KK 7_?"$(_?))= #+& <77= #+&=
6 'hange salar# o" emplo#ee 1.+ to the salar# o" the emplo#ee with "irst name 82oe9 " 2oe is not "ound then ta)e average salar# o" all emplo#ees " more than one emplo#ee with "irst name 82oe9 is "ound then ta)e the least salar# o" the emplo#ees with "irst name 2oe &#?<$# _<$@ #<7@##;<$@3@#= #%+ #<#? <$@ %+7 _<$@ $7 #<7@## !"#$# %$_+# 4 'J7#'=
&# #<7@## # <$@ 4 _<$@ !"#$# #<7@##_%& 4 10= #G?#%7+ !"#+ +7_&_7+& "#+ &# #<7@## # <$@ 4 (#<#? (<$@) $7 #<7@##) !"#$# #<7@##_%& 4 10= #+&=
3 Displa# 2ob !itle and :ame o" the (mplo#ee who *oined the *ob "irst da# &#?<$# ?$7$ 7?$ % #<#? 7_%&, 7_%<# $7 7= _+# #<7@##;%$_+#3@#= #%+ 7$ 7$#? %+ 7?$ <77 #<#? %$_+# %+7 _+# $7 #<7@## !"#$# "%$#_&# 4 ( #<#? %+("%$#_&#) $7 #<7@## !"#$# 7_%& 4 7$#?;7_%&) +& 7_%& 4 7$#?;7_%&= &_7;_<%+#( 7$#?;7_%<# KK '' KK _+#)= #+& <77= #+&=
7 Displa# -th and 1+th emplo#ees in (mplo#ees table &#?<$# ?$7$ #?$ % #<#? #<7@##_%&, %$_+# $7 #<7@##= #%+
7$ #$#? %+ #?$ <77 % #?$3$7!?7+ > : "#+ &_7;_<%+#( #$#?;%$_+#)= #G% !"#+ #?$3$7!?7+ > 10= #+& %= #+& <77=
#+&=
1+ Bpdate salar# o" an emplo#ee based on department and commission percentage " department is + increase salar# b#
1+? " department is 6+ then 1-?5 i" commission is more than .? then -? otherwise 1+? &#?<$# ?$7$ #?$ % #<#? #<7@##_%&, &#$#+_%&, ?7%%7+_? $7 #<7@##= _"%# #%+
+#$(2)=
7$ #$#? %+ #?$ <77 % #$#?;&#$#+_%& 4 :0 "#+ _"%# 4 10= #<% #$#?;&#$#+_%& 4 90 "#+ _"%# 4 15= #<% #$#?;?7%%7+_? > 0;0 "#+ _"%# 4 5= #<# _"%# 4 10= #+& %= &# #<7@## # <$@ 4 <$@ <$@ * _"%#6100 !"#$# #<7@##_%& 4 #$#?;#<7@##_%&= #+& <77=
#+&=
11 'reate a "unction that ta)es department D and returns the name o" the manager o" the department ?$## 7$ $#<?# +?%7+ #_&#_+#$_+#(&#%& +#$) $#$+ $?"$ % _+# #<7@##;%$_+#3@#= #%+ #<#? %$_+# %+7 _+# $7 #<7@## !"#$# #<7@##_%& 4 ( #<#? +#$_%& $7 &#$#+ !"#$# &#$#+_%& 4 &#%&)= $#$+ _+#= #+&=
1, 'reate a "unction that ta)es emplo#ee D and return the number o" *obs done b# the emplo#ee in the past ?$## 7$ $#<?# +?%7+ #_+7_7_7_&7+#(#%& +#$) $#$+ +#$ % _?7+ +#$(2)= #%+ #<#? ?7+(*) %+7 _?7+
$7 7_"%7$@ !"#$# #<7@##_%& 4 #%&= $#$+ _?7+= #+&=
1. 'reate a procedure that ta)es department D and changes the manager D "or the department to the emplo#ee in the department with highest salar# %Bse (xceptions& ?$## 7$ $#<?# $7?#&$# ?"+#_&#_+#$(&#%& +#$) % _#%& #<7@##;#<7@##_%&3@#= #%+ #<#? #<7@##_%& %+7 _#%& $7 #<7@## !"#$# <$@ 4 ( #<#? G(<$@) $7 #<7@## !"#$# &#$#+_%& 4 &#%&) +& &#$#+_%& 4 &#%&= &# &#$#+ # +#$_%& 4 _#%& !"#$# &#$#+_%& 4 &#%&= #+&=
1 'reate a "unction that ta)es a manager D and return the names o" emplo#ees who report to this manager !he names must be returned as a string with comma separating names ?$## 7$ $#<?# +?%7+ #_#<7@##_7$_+#$(+#$ +#$) $#$+ $?"$2 % _#<7@## $?"$2(1000) 4 ''= ?$7$ #?$ % #<#? %$_+# $7 #<7@## !"#$# +#$_%& 4 +#$= #%+
7$ #$#? %+ #?$ <77 _#<7@## 4 _#<7@## KK ',' KK #$#?;%$_+#= #+& <77= $#7# #G$ , "# #%++%+ $#$+ <$%(_#<7@##,',')=
#+&=
1- (nsure no changes can be made to (0PLOC((S table be"ore 4am and a"ter 1+pm in a da# ?$## 7$ $#<?# $%#$ $_#<7@##_%#_?"#? #7$# &# 7$ %+#$ 7$ &#<## 7+ #<7@## 7$ #?" $7!
#%+ %
7_?"$(@&#,'""2:') . - 7$ 7_?"$(@&#,'""2:') > 10 "#+ $%#_<%?%7+_#$$7$(20111,'S7$$@ N7 ?"+# ?+ # &# #7$# - AM +& #$ 10 PM')= #+& %= #+&=
14 'reate a !rigger to ensure the salar# o" the emplo#ee is not decreased ?$## 7$ $#<?# $%#$ $_#<7@##_<$@_?"#? #7$# &# 7+ #<7@## 7$ #?" $7! #%+ % 7<&;<$@ > +#!;<$@ "#+ $%#_<%?%7+_#$$7$(20111,'S7$$@ S<$@ ?+ +7 # &#?$##&')= #+& %= #+&=
16 'reate a trigger to ensure the emplo#ee and manager belongs to the same department T"% $%#$ +##& 7 $#& "# $7! " % #%+ 7&%%#&, !"%?" ?# %+ $7<#; T"# 7<%7+ 7 %+ $7<# % #G<%+#& Work around for mutating problem in Oracle Triggers ; P<## ?"#? % 7;
Note:
13 =henever the *ob is changed "or an emplo#ee write the "ollowing details into *ob histor# (mplo#ee D5 old *ob D5 old department D5 hire date o" the emplo#ee "or start date5 s#stem date "or end date ut i" a row is alread# present "or emplo#ee *ob histor# then the start date should be the end date o" that row E1 ?$## 7$ $#<?# $%#$ $_<7_7_?"+# #$ &# 7 7_%& 7+ #<7@## 7$ #?" $7! &#?<$# _#+&&# &#= _$&# &#= #%+ %+& 7 !"#"#$ "# #<7@## " +@ $7! %+ 7_"%7$@ <# #<#? G(#+&_&#) %+7 _#+&&# $7 7_"%7$@ !"#$# #<7@##_%& 4 7<&;#<7@##_%&= % _#+&&# % +<< "#+
_$&# 4 7<&;"%$#_&#= #<# _$&# 4 _#+&&# 1= #+& %=
%+#$ %+7 7_"%7$@ <# (7<&;#<7@##_%&, _$&#, @&#, 7<&;7_%&, 7<&;&#$#+_%&)= #+&= Note: B#7$# #%+ "# 7# $%#$, @7 +##& 7 &%<#
PDATE_JOB_HISTORY $%#$, !"%?" % <$#&@ $##+ %+ HR ??7+, % &7# "# #;
