HALDIA INSTITUTE OF TECHNOLOGY 1st Year, 2nd Semester, Semest er, MCA Department of Computer Applications
Description of the table EM! Na"e N#ll$ T%pe T%pe &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& &&&&&&&&&&&&&&&&&&&&&&&&&&&&&& EMNO NOT NULL NUM'E()*+ ENAME ,A(CHA(-)./+ 0O' ,A(CHA(-)1+ MG( NUM'E()*+ HI(EDATE DATE SAL NUM'E()23-+ COMM NUM'E()23-+ DETNO NUM'E()-+ Description of the table DET! Na"e N#ll$ T%pe T%pe &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& DETNO NOT NULL NUM'E()-+ DNAME ,A(CHA(-).*+ LOC ,A(CHA(-).4+ Description of the table SALG(ADE! Na"e N#ll$ T%pe T%pe &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& &&&&&&&& &&&&&&&&&&&&&&&&&&& G(ADE NUM'E( LOSAL NUM'E( HISAL NUM'E(
Description of the table 'ONUS! Na"e N#ll$ T%pe T%pe &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& &&&&&&&& &&&&&&&&&&&&&&&&&&&&&&&& ENAME ,A(CHA(-)./+ 0O' ,A(CHA(-)1+ SAL NUM'E( COMM NUM'E(
E5peri"ent No6 7 /.
Using the default table of Oracle given above. Write SQL queries for the following : (a) (b) (c) (d) (e) (f) (g) (h) (i) (,) (#) (l)
List the names and code of all emlo!ees. List the names" emlo!ee code and deartment code of all cler#s. List the names" emlo!ee code and salar! of all managers. List the names" emlo!ee code and hiredate of all anal!sts. List the emlo!ees whose salar! lies between $%%% and &%%%. List the emlo!ees whose salar! less than '%%%. List the emlo!ees whose salar! greater than %%%. List the emlo!ees whose salaries are %%" '*%% or $+%. List the names of all emlo!ees who are either cler#s or salesman or anal!st . List the emlo!ee those who are not getting commission. List the emlo!ee those who are getting commission. List the emlo!ee name starts with -/.
E5peri"ent No6 7 /-
Write SQL queries for the following. (a) List the names and ,ob of all emlo!ees who have names e0actl! + characters in length. (b) List all emlo!ees whose names start with -1/ . (c) List all emlo!ees who name ends with -2/. (d) List the names and ,ob of all emlo!ees who have names e0actl! + characters in length and ends with -S/. (e) List all emlo!ees who have not ,oined between '3'3' and &'3'$3'. (f) List all emlo!ees whose ,ob does not start will 45L6. (g) List all managers who earn more than 7s. %%%38. (h) List all cler#s and salesman who earn more than 7s. '*%%38 (i) List the names and salaries of all emlo!ees who were ,oined as manager during '9'. E5peri"ent No6 7 /4
or the ;< relation" frame the following queries using SQL. (a) (b) (c) (d) (e)
5alculate the average salar! of all emlo!ees. 5alculate the average salar! of all ;anagers. 5alculate the total salar! of all emlo!ees. 5alculate the total salar! of all managers. ind the minimum salaries earned b! the emlo!ees.
(f) (g) (h) (i) (,) (#)
ind the ma0imum salaries earned b! the emlo!ees. ind the minimum salaries earned b! a cler#s. ind the ma0imum salaries earned b! a salesman. ind the minimum and ma0imum and average salaries earned b! a emlo!ees. ind the minimum and ma0imum and average salaries earned b! a cler#s. List the total number of emlo!ees and the average salaries of the different deartments. (l) 5alculate total number of emlo!ees. (m)5alculate total number of managers. (n) 5alculate the number of emlo!ees who are not getting an! commission. (o) 5alculate the number of emlo!ees who are getting an! commission. () List the details of all managers in ascending order of ,oining dates. (q) List the average salaries for each different ,ob. (r) =isla! the average salar! for each different ,ob. (s) =isla! the minimum" ma0imum" and average salaries for each ,ob grou. (t) ind all deartments which have less than & emlo!ees. (u) List the details of the emlo!ees in ascending order of deartment number" and within each deartment" in descending order of salar!. (v) =isla! the name" detno and annual salar! of each emlo!ee in order salar! and detno. (w)=isla! the name of emlo!ee who earns ma0imum salar!. (0) =isla! the name of emlo!ee who earns minimum salar!. (!) =isla! the name of emlo!ee who earns ma0imum salar! whose ,ob is salesman. (>) =isla! the name of emlo!ee who earns minimum salar! whose ,ob is cler#. (aa) =isla! the deartment number whose average salar! is ma0imum. E5peri"ent No6 7 /*
Using the default table of Oracle" such as m and =et. Write SQL queries for the following ? (a) (b) (c) (d)
List all emlo!ee names" det name and the cit!" in deartment name order. List all emlo!ee name" det number" det name and salar!. List all emlo!ees wor#ing in =allas in descending order of salar!. List all emlo!ee/s name" ,ob" salar! and deartment name for ever!one in the coman! e0cet cler#s. Sort the reort with resect to ,ob and salar!. (e) List all emlo!ee names who wor# in the same cit! as an emlo!ee named -O7=/. (f) =isla! the name of the det that has no emlo!ee.
E5peri"ent No6 7 /8
a) b) c) d) e) f) g) h)
List the emlo!ees belonging to the deartment $%. List the name and salar! of the emlo!ees whose salar! is more than '%%%. List the emlo!ee number and the name of the manager. List the names of the cler#s wor#ing in the deartment $%. List the name of the anal!sts and salesman. List the details of the emlo!ees who have ,oined before the end of Setember '. List the names of the emlo!ees ho are not managers. List the name of the emlo!ees whose emlo!ee numbers @&*9"@+$'"@&9"@9&"@@. i) List the emlo!ee details not belonging to the deartment '%"&% and %. ,) List the emlo!ee names who have ,oined before &%th Aune/' and after =ecember /'. #) List the name of the emlo!ee and designation (,ob) of the emlo!ee who does not reort to an!bod! (who doesn/t have manager). l) List the different ,obs (designations) available in the em table. m) List the emlo!ees not assigned to an! deartment. n) List the details of the emlo!ees whose salar! is greater than $%%% and not eligible for commission. o) List the emlo!ee names having -B/ as the second character. ) List the name" salar! and < amount of all the emlo!ees (< is calculated as '%C of salar!). q) List the emlo!ee number" name and salar! in ascending order of salar!. r) Lists the emlo!ee name and hiredate in descending order of hiredate. s) List the emlo!ee name" salar!" <" D7E" =E and gross salar!Forder the result in ascending order of gross.D7E is +%C of salar! and =E is &%C of salar!. t) List the deartment number and the total salar! a!able in each deartment. List the ,obs and number of emlo!ees in each ,ob.Ghe result should be in descending order of the number of emlo!ees. u) List the total salar!" ma0imum" minimum and average salar! of the emlo!ee/s ,ob wise. v) List the average salar! from each ,ob e0cluding manager. w) List the average monthl! salar! for each ,ob t!e within deartment. 0) List average salar! for all deartments emlo!ing more than five eole. !) List ,ob of all the emlo!ees where ma0imum salar! is greater than or equal to &%%%. >) List the total salar!" ma0imum and minimum salar! and the average salar! of emlo!ees ,ob wise for deartment number $% and disla! onl! those rows having average salar! greater than '%%%.
E5peri"ent No6 7 /9
a) b) c) d) e) f) g) h) i) ,) #) l) m) n) o)
List the emlo!ees earns more than an! emlo!ee in 5DB5E1O. List the name of the emlo!ee who wor#s in the same deartment as S;BGD. List the name" emlo!ee number "their manager name and manager number. List the name of the emlo!ee ,ob is same as -5LE7H/. List the name of emlo!ee whose salar! is more than -GU727/. List the name of emlo!ee who ,oined after -ELL2/. =isla! the name of the deartment whose ,ob is -SELS;E2/. =isla! the name of the deartment in which -O7=/ wor#s. =isla! the name of the deartment whose salar! is ma0imum. =isla! the name of the cit!(location) in which -S;BGD/ wor#s. =isla! the name of the cit! in which the manager wor#s. =isla! the grade of the emlo!ee named -;E7GB2/. List the emlo!ees earns more than ever! emlo!ee in -=ELLES/. =isla! the name of the deartment which has no emlo!ee. List name" emlo!ee number and the name" emlo!ee number of their managers/ manager . ) list the name of the emlo!ee who ,oined in the same !ear of -E=E;S/. q) list the name of the emlo!ee who ,oined in the same month of -ILEH/. r) list the name of the emlo!ee who ,oined in the same date of -E=E;S/. s) List the name of the deartment who gets commission.
E5peri"ent No6 7 /2
Write SQL queries for the following : (a) List all emlo!ee who wor# in =allas or have ,oined the coman! as manager before $. (b) List all emlo!ees who wor# in Ioston and earn more than an! emlo!ee wor#ing in 5hicago. (c) List name of the emlo!ee who earns the minimum salar!. (d) List all emlo!ees who wor# in the same ost as Smith. (e) List all emlo!ees who earn the lowest salar! in their resective det. (f) List all emlo!ees who earn more than ever! emlo!ee in the -Sales/ deartment. (g) ind the ,ob with the highest average salar!.
E5peri"ent No6 7 /:
(a) 5reate a Eccount table with following attribute EccJno()" EccJt!e(')" 5ustJno'(*)" 5ustJno$(*)" OJdate. (b) 5reate the same table using 2ot 2ull on all and =efault on OJdate constraint. (c) Edd a field called Ialance(@"$) to the table Eccount.
(d) (e) (f) (g)
Bncrease the field of EccJno to *. 7emove the constraint of 5ustJno$. =isable the constraint of EccJt!e. 7emove the table from the database.
'. 5reate a table called 57B5HG7S" with columns as secified below: Col#"n Na"e 5ountr! 2ame 7uns Wic#ets 5atches =ate8of8birth
Description 5haracter string 5haracter string (ma0 length $%) 2umber number number date
Ghe country and name fields should be declared NOT NULL. $. ;odif! the table C(IC;ETE(S to a) Edd a field centuries" which will hold the number of centuries scored. b) Edd a field five’s" which will hold the number at times he has ta#en five wic#ets in an innings. c) E Ioolean field caption" indicating whether the erson is currentl! the cation of the team. Use the =S5 command to chec# the column defines.
a) 5reate a Gransaction table with the following attribute : EccJno()" GJdate(date)" GJt!e(')" GJmode(*)" 5hequeJno(@)" Oerator($%)" =rawnJban#(&%)" GJamount(@"$)" 5lear('). • • • • •
GJdate should be S%s
. GJmode will be Che?#e or Cash. 5lear will be Yes or No. ;aintain the relationshi with Eccount table.
b) Bnsert some aroriate data in these tables.
E5peri"ent No6 7 1
Using the default table of Oracle" such as m and =et. a) =efine a view according to the following outut : Deptno '% $% &%
Na5Sal +%%% &%%% $+%
MinSal '%% %% 9+%
No6 of e"p & + *
b) rom the transaction table define a view of all deosits done in last $ months.
E5peri"ent No6 7 ./
'K Write a
'. Write a
E5peri"ent No6 7 .-
5reate a row trigger which will e0ecute after udation or deletion of clientJmaster in such a wa! that it will insert the old clientJnumber" oldJbalance" oeration and s!stem date into new table audit. Gable to be created as follows: 5lientJmaster(clientJno varchar$(*)" name varchar$($%)" balanceJdue number('%"$)) audit(clientJno varchar$(*)" balance number('%"$)" oeration varchar$(*)" s!sdate date) E5peri"ent No6 7 .4 5reate a row trigger (before trigger) in such a wa! whenever the disatched quantit! in challanJdetails is inserted as >ero or negative the trigger fires b! giving message -desatch quantit! can not be less than equal to >ero/. Bf it is ve
then the trigger will e0ecute to udate the order table b! udating the balance quantit!. Gable to created as follows : Order(orderJno varchar$(*) rimar! #e!" balanceJquantit! number('%"$)) 5hallJdtls(orderJno varchar$(*)" rodJno varchar$(*)" qnt!Jdes number('%"$) rimar! #e!(orderJno" rodJno)" foreign #e! (orderJno) references order)