Assignment 3 1.
Cre Create ate tabl able DEPARTMENT Column Name DeptCode DeptName HOD
Data Type Varchar2 Varchar2 Varchar2
Size 4 15 4
Constraints Not null, Primary key Not null orei!n key re"erence# acultyCode o" table FACULTY
Data Type Varchar2 Varchar2 Date Varchar2
Size 4 15
Constraints Not null, Primary key, $tart# %ith &' Not null Not null )u#t be either C$*,+, C-, CH*), )H$, PH$, H/), 00-
FACULTY Column Name acultyCode acultyName DateO"(oin DeptCode
4
create table "aculty411 acultyCode 3archar24 3 archar24 P+)- 6*, acultyName 3archar215 NO N/77, DateO"(oin Date NO N/77, DeptCode 3archar248 create table D*P-)*N41 DeptCode 3archar24 P+)- 6*, 6*, D*ptName 3archar215 NO N/77, HOD 3archar248 -lter table D*P-)*N41 add O*+9N 6*HOD re"erence# "aculty411acultyCode8 -lter table "aculty41 add checkacultyCode like :;:8 2. +n#ert appropriate 3alue# in the abo3e table. in#ert into "aculty41 3alue#:<1:,:$.Chakraborty:,:22=Dec=<1:,:+:8 in#ert into "aculty41 3alue#:<2:,:).)ohanto:,:1<=)ay=<2:,:C$*:8 in#ert into "aculty41 3alue#:<>:,:$.).oy:,:15=-u!=<1:,:C$*:8 in#ert into "aculty41 3alue#:<4:,:6.6.Patil:,:2<=-u!=<2:,:C-:8 in#ert into "aculty41 3alue#:<5:,:$.C.6areem:,:1<=(un=<1:,:CH*):8
in#ert into "aculty41 3alue#:<:,:P.oy:,:1<=eb=<2:,:H/):8 in#ert into "aculty41 3alue#:
in#ert into D*P-)*N41 3alue#:C$*:,:Computer $ci:,:<>:8 in#ert into D*P-)*N41 3alue#:+:,:+n"oech:,:<1:8 in#ert into D*P-)*N41 3alue#:C-:,:Comp -ppli.:,:<4:8 in#ert into D*P-)*N41 3alue#:CH*):,:Chemi#try:,:<5:8 in#ert into D*P-)*N41 3alue#:)H$:,:)-thematic#:,:<@:8 in#ert into D*P-)*N41 3alue#:PH$:,:Phy#ic#:,: -dd con#traint DeptCode o" Faulty i# "orei!n key and re"erence# DeptCode in Department -lter table aculty41 add O*+9N 6*DeptCode re"erence# D*P-)*N41DeptCode8
4 ind the name# o" "acultie# o" C$* Department. $elect acultyName "rom "aculty41 %here DeptCode:C$*:8
-C/7N-)* =============== ).)ohanto $.).oy ).Da#!upta 5. ind the number o" "acultie# in the Computer application department $elect acultyName "rom "aculty41 %here DeptCode:C-:8 -C/7N-)* =============== 6.6.Patil
. $ho% the name# o" the head# o" department# %ith department name. $elect acultyName,DeptName "rom "aculty41,D*P-)*N41 %here D*P-)*N41.HOD"aculty41.acultyCode8 -C/7N-)* D*PN-)* =============== =============== $.).oy Computer $ci $.Chakraborty +n"oech 6.6.Patil Comp -ppli. $.C.6areem Chemi#try P.)ukherAee )-thematic# 6.)ondal Phy#ic# P.oy Humanitie# ).$in!h 0u#i. -dmn#
ind the name# o" >rd year C$* #tudent# %ho#e date o" birth i# earlier than 15th -u!u#t 1B@1.
@. ind the number o" "acultie# %ho Aoined in -u!u#t. $E7F $elect countacultyCode "rom "aculty41 2 %here DateO"(oin like :;-/9;:8 CO/N-C/7COD* ================== 2
B. -dd an eGtra attribute to the "aculty table = Salary Num!er"#$%& -lter table aculty41 add #alary number@,28 1< +n#ert 3alue# into the corre#pondin! "ield Salary Num!er"#$%&' /pdate "aculty41 #et #alary15<<< %here acultyCode:<1:8
/pdate "aculty41 #et #alary?<<< %here acultyCode:<2:8 /pdate "aculty41 #et #alary25<<< %here acultyCode:<>:8 /pdate "aculty41 #et #alary1<<<< %here acultyCode:<4:8 /pdate "aculty41 #et #alary1<<<<.5< %here acultyCode:<5:8 /pdate "aculty41 #et #alary125<< %here acultyCode:<:8 /pdate "aculty41 #et #alary15<5< %here acultyCode:
$-7- 15<<< 25<<< 1<<<< 1<<<<.5 125<< 15<5< 112<<.?5 12<<< 11<<<
12 ind the name, department o" the "acultie# %ho earn bet%een @<<< and 12<<<. #elect acultyName,DeptCode "rom "aculty41 %here #alary bet%een @<<< and 12<<<8 -C/7N-)* =============== ====
D*P
6.6.Patil $.C.6areem P.)ukherAee 6.)ondal 0.Da#
CCH*) )H$ PH$ +
1> ind the maGimum #alary amon! the "acultie#. #elect maG#alary "rom "aculty418 )-$-7- =========== 25<<<
Assignment ( 1.
Create table SU)*ECT and in#ert appropriate 3alue#. Column Name $ubAectCode $ubAectName aculty
Data Type Varchar2 Varchar2 Varchar2
Size 4 15 4
Constraints Not null, Primary key Not null orei!n key re"erence# acultyCode o" table FACULTY
create table #ubAect41 $ubAectCode 3archar24 P+)- 6*, $ubAectName 3archar215 NO N/77, aculty 3archar24, O*+9N 6*aculty re"erence# "aculty41acultyCode8
in#ert into #ubAect41 3alue#:+21:,:Control $y#tem:,:<1:8 in#ert into #ubAect41 3alue#:C<1:,:D0)$:,:11:8 in#ert into #ubAect41 3alue#:H2>:,:Public $peakin!:,:<:8 in#ert into #ubAect41 3alue#:022:,:*conomic#:,:
3alue#:C->1:,:PP7:,:<4:8
1
ind the number o" #tudent# in each department %ith their department name.
#elect countN-)*, D*PCOD* "rom #tudentI> !roup by D*PCOD*8 2
+ncrement the #alary o" each "aculty by # 5<<.
update "aculty41 #et #alary#alaryJ5<<8 4
ind the name# o" #tudent# and "acultie# %ho#e name #tart %ith &$'.
#elect #tudent411.N-)*,"aculty41.acultyName "rom #tudent411,"aculty41 %here #tudent411.N-)* like :$;: -ND "aculty41.acultyName like :$;:8 N-)* -C/7N-)* ========== =============== $amik $.Chakraborty $riAa $.Chakraborty $aibal $.Chakraborty $antanu $.Chakraborty $amik $.).oy $riAa $.).oy $aibal $.).oy $antanu $.).oy $amik $.C.6areem $riAa $.C.6areem $aibal $.C.6areem N-)* -C/7N-)* ========== =============== $antanu $.C.6areem 12 ro%# #elected.
5 ind the #tudent# %ho #tay in 6aikhali #elect N-)* "rom #tudent411 %here -DD*$$:6aikhali:8 N-)* ========== +p#ita
ind the name# o" "acultie# %ho take cla##e# in the + department. #elect acultyName "rom "aculty41 %here DeptCode:+:8 -C/7N-)* ===============
$.Chakraborty 0.Da# ?
ind the name# o" all "acultie# %ho#e HOD i# !i3en.
#elect acultyName,acultyCode "rom "aculty41, D*P-)*N41 %here acultyCodeHOD8 -C/7N-)* =============== ==== $.).oy $.Chakraborty 6.6.Patil $.C.6areem P.)ukherAee 6.)ondal P.oy ).$in!h
-C/ <> <1 <4 <5 <@
@ ro%# #elected. @ -dd eGtra attribute to the $ubAect table = +epartment ,ar-ar% "(&$ year ,ar-ar% ".&
-lter table #ubAect41 add department 3archar248
-lter table #ubAect41 add year 3archar218 B
+n#ert 3alue# into the "ield# = +epartment$ year .
update #ubAect41 #et department:C$*:,year:1: %here "aculty:11:8 update #ubAect41 #et department:+:,year:2: %here "aculty:<1:8 update #ubAect41 #et department:C-:,year:2: %here "aculty:<4:8 update #ubAect41 #et department:CH*):,year:1: %here "aculty:<5:8 update #ubAect41 #et department:H/):,year:>: %here "aculty:<:8 update #ubAect41 #et department:00-:,year:>: %here "aculty:
$E7F #elect K "rom #ubAect418 $/0( $/0(*CN-)* ==== =============== ==== ==== = +21 Control $y#tem H2> Public $peakin! 022 *conomic# P2B -erodynamic# C->1 PP7 CH11 Or!anic Chem C<1 D0)$ )25 0a#ic -l!ebra
-C/ D*P<1 <
+ H/) 00PH$ CCH*) C$* )H$
2 > > 2 2 1 1 1
@ ro%# #elected. 1< ind the name# o" "acultie# %ho earn more than the a3era!e o" all "acultie#.
#elect acultyName,#alary "rom "aculty41 %here #alaryFall #elect a3!#alary "rom "aculty418 -C/7N-)* =============== ========== $.Chakraborty $.).oy P.oy ).$in!h
$-7- 155<< 255<< 1><<< 1555<
11 7i#t the name# o" "acultie# o" C$* department %ho earn more than the a3era!e #alary o" the department. #elect acultyName,#alary "rom "aculty41 %here DeptCode:C$*: and #alaryFall #elect a3!#alary "rom "aculty41 %here DeptCode:C$*:8 -C/7N-)* =============== ========== $.).oy
$-7- 255<<
12 ind the maGimum and minimum #alarie# amon! "acultie#. 1>
#elect maG#alary,min#alary "rom "aculty418 )-$-7- )+N$-7- =========== =========== 255<< 55<< 14 ind the #econd maGimum #alary amon! all "acultie#.
#elect maG#alary "rom "aculty41 %here #alary not in #elect maG#alary "rom "aculty418 )-$-7- ===========
1555<
15 ind the name# o" "acultie# %ho are not the HOD'# o" any department.
#elect acultyName "rom "aculty41 %here acultyCode not in #elect HOD "rom D*P-)*N418 -C/7N-)* =============== ).)ohanto 0.Da# ).Da#!upta 1 ind the name# o" #ubAect# "or #tudent# o" C$* >rd year.
#elect $ubAectName "rom #ubAect41 %here Department:C$*: and year:1:8 $/0(*CN-)* =============== D0)$ 1? Name the department# ha3in! hi!he#t number o" "acultie# and di#play the name# o" "acultie#
N*$*D E/*+*$ create 3ie% count"aculty41I3ie% a##elect DeptCode, countacultyCode a# $ "rom "aculty41 !roup by DeptCode8 $E7F #elect K "rom count"aculty41I3ie%8 D*P $ ==== ========== 001 C1 CH*) 1 C$* > H/) 1 + 2 )H$ 1 PH$ 1 @ ro%# #elected.
#elect DeptCode "rom count"aculty41I3ie% %here $ in #elect maG$ "rom count"aculty41I3ie%8 D*P ==== C$*
-N$ #elect acultyName "rom "aculty41 %here DeptCode in #elect DeptCode "rom count"aculty41I3ie% %here $ in #elect maG$ "rom count"aculty41I3ie%8 -C/7N-)* =============== ).)ohanto $.).oy ).Da#!upta
