SQLL PROG
GRAM
MS
41. Cre
eate a STU
UDENT tab
ble and in
nsert data as per following tab
ble.
CREATING TABLE STUDENT:‐
S
INSERTING RECOR
RDS:‐
Write SQL comm
mands forr the stateements (a) to (h) an
nd write thhe outputts for (i).
(a) List the name
n of all the studdents, who have taken strea m as COM MPUTER.
(b) To countt the num mber of fem male students.
(c) To displaay the nummber of sttudents sttream wisse.
(d) To displaay a reporrt, listing N
NAME, ST TREAM, SEEX and sti pend, where stipen nd
is 20%
% of fees.
(e) To displaay all the records inn sorted order
o of na
ame.
(f) Add a new w column n FEES_PA AID of data
a type cha
ar (size 1) having de
efault valu
ue
‘Y’.
(g) Increase thet FEES BY B 100 of those stu udents who have taaken the stream
COMP PUTER.
(h) Delete the record of o studentt “NISHA ARORA”.
(i) Givee the outp
put of the
e followingg SQL stattements based
b on SSTUDENT table:
(1) SELLECT AVG (FEES) FR ROM STUD DENT WHEERE STREA AM = 'COM MPUTER';;
(2) SELLECT MAX X(AGE) FRO OM STUD DENT;
(3) SELLECT COUNT (DISTINCT STRE AM) FROM M STUDENNT;
(4) SELLECT SUM M( FEES) FR
ROM STUD DENT GRO OUP BY STTREAM;
Answ
wer:
(a) SELLECT NAMME FROM STUDENT
S WHERE STREAM
S ='COMPUTTER';
(b) SELLECT COUNT(*) FRO OM STUDEENT WHER RE SEX = 'F';
(c) SELLECT STREAM, COUNT(*) FRO OM STUDEENT GROU UP BY STR
REAM;
(d) SELLECT NAMME, STREAM, SEX, F EES*20/1100 'STIPEND' FROMM STUDEN
NT;
(e) SELLECT * FROOM STUDENT ORDEER BY NAME;
(f) ALTTER TABLEE STUDENT T
ADDD FEES_PA AID CHAR(1) DEFAU
ULT ‘Y’;
(g) UPDATE STU UDENT
SETT FEES = FEES + 100
0
WH HERE STREEAM ='CO OMPUTER'';
(h) DELETE FROM STUDENT WHER RE NAME = 'NISHA ARORA';
A
(i) Thee output iss given aftter executting from (a) to (h)::‐
(1)
(2)
(3)
(4)
42. Creeate a PRO
ODUCT ta
able and in
nsert data
a as per fo
ollowing taable.
Table: PR
RODUCT
PRODUCCT
P_ID ProducttName Manufaccturer P rice
TP01 TalcomP Powder LAK 400
FW05 Face Waash ABC 455
BS01 Bath Soap ABC 555
SH06 Shampo oo XYZ 1220
FW12 Face Waash XYZ 955
CREATING TABLE PRODUCT:‐
P
INSERTING RECOR
RDS:‐
Write SQL comm mands forr the stateements (i)) to (v).
i) To display the
e details of
o those Prroducts whose
w Prod
ductNamee is ‘Face Wash’.
Ans: Select * fro
om PRODU UCT wherre ProducttName = ‘Face
‘ Wassh’ ;
(ii) To display th
he details of Produccts whose
e Price is in
n the rangge of 50 to
o
1000(Both values includded).
Ans: Select * fro om product where Price betw ween 50 anda 100 ;
(iii) To display th
he ProducctName, MManufactu urer in asccending oorder of
Manuffacturer.
Ans: Select Prod ductName e, Manufaacturer fro
om Product order bby Manufa acturer;
(iv) To increase the Price of all Prod
ducts by 10
1
Ans: Update Product
P
Set Price==Price +10
0;
After executing update com
mmand:‐
(v) To ddisplay th
he Maximu um Price o
of the Pro
oduct who
ose Manuffacturer
is ‘XYZZ’.
Ans: Select MAX X(Price) frrom Produuct wheree Manufaccturer = ‘XXYZ’ ;
43. Creeate two tables
t ITE
EM and CU
USTOMER
R and inserrt data ass per follow
wing table
es.
CREATTING TABLLE ITEM & INSERTIN
NG RECOR
RDS:‐
CREATTING TABLLE CUSTOM
MER & INSERTING RECORDS
S:‐
Write SQL comm mands forr the stateements (i)) to (iv) an
nd give ouutputs for SQL queries
(v) to ((viii)
(i) To displayy the deta
ails of tho
ose Custom
mers whosse city is D
Delhi.
Ans: SSelect * frrom Custo
omer Wheere City=””Delhi”;
(ii) To display th
he details of Item w
whose Pricce is in the
e range off 35000 to
o 55000
(Both vvalues inccluded).
Ans: Select * from
f Item
m Where P
Price betw
ween 3500
00 and 550000;
o display the
(iii) To t Custo omerNamee, City fro om table Customeer, and Ite
emName and
Price ffrom tablee Item, wiith their co
orrespond
ding matcching I_ID .
Ans: SSelect Cu
ustomerNa ame, Cityy, ItemNa
ame, Price
e from Ittem, Custtomer wh
here
Item.I__ID=Custo
omer.I_ID
D;
iv) To increase the
t Price of
o all Item
ms by 1000
0 in the ta
able Item.
Ans: Update Ittem
set Price==Price+100
00;
v) SELEECT DISTIN
NCT City FROM
F Cusstomer;
vi) SELLECT ItemName, MA
AX(Price),, Count(*)) FROM Item GROU
UP BY Item
mName;
(vii) SSELECT Customer
C Name, M Manufactu
urer FRO
OM Item
m, Custom
mer WHERE
Item.Ittem_Id=C
Customer.Item_Id;
(viii) SELECT Item
mName, Price
P * 1000 FROM Item WHERE Manuffacturer = ‘ABC’;
onsider the
44. Co e followin ng tables D
DOCTOR and
a SALAR RY. Write SQL comm mands forr
the staatements (i) to (iv) and give o
outputs fo
or SQL queries (v) tto (vi)
CREATTING TABLLE DOCTO
OR & INSER
RTING REC
CORDS:‐
CREATTING TABLLE SALARY
Y & INSERTTING REC
CORDS:‐
play NAM
(i) Disp ME of all dooctors wh
ho are in “MEDICINE
“ E” having more tha
an 10 yearrs
of expperience frrom the Table DOCCTOR.
Ans: Select Nam
me from Doctor
D where Dept=
=”Medicin
ne” and Exxperience>10
(ii) Dissplay the average
a sa
alary of alll doctors working in
i “ENT”ddepartmen
nt using th
he
tables. DOCTOR RS and SALARY Salaary =BASIC C+ALLOWANCE.
Ans: Select avg((basic+allo
owance) ffrom Docttor,Salary where Deept=”ENT” and
[Link]=[Link];
(iii) Dissplay the minimum
m ALLOWA
ANCE of fe
emale docctors.
Ans: Select min(Allowancce) from D
Doctor,Salary where Sex=”F”” and
[Link]=[Link];
(iv) Dissplay the highest co
onsultatio
on fee am
mong all male doctoors.
Ans: Select maxx(Consulattion) from
m Doctor,SSalary where Sex=””M” and
[Link]=[Link];
(v) SELLECT coun
nt (*) from
m DOCTOR
R where SEEX = “F”
(vi) SELECT NAM ME, DEPT , BASIC fro
om DOCTOR, SALRY
Y Where D
DEPT = “E
ENT” AND
DOCTO [Link] = SA
[Link]
45. Co
onsider the
e followin
ng table G RADUATEE. Write SQ
QL comm ands for the
t
statem
ments (i) to
t (v)
TABBLE : GRA
ADUATE
SN
NO NAM ME STIPENDD SUBJECT GE
AVERAG DIVV
1 KAR RAN 400 PHYSSICS 68 I
2 DIWWAKAR 450 COMP SC 68 I
3 REKKHA 350 PHYSSICS 56 II
4 ARJUN 500 MATH HS 70 I
5 SABBINA 400 CHEM
MISTRY 55 II
6 RUB BINA 500 COMP SC 62 I
CREATTING TABLLE GRADU
UATE:‐
INSERTTING RECO
ORD:‐
INSERTT INTO GR
RADUATE VALUES(11, ‘KARAN
N’, 400, ’PHYSICS’, 668, ‘I’) ;
i) List th
he names of those sstudents who
w have
e obtainedd DIV I sorrted by
NAMEE.
Anss: select NAME
N from GRADU
UATE whe
ere DIV = ‘I’ order byy NAME ;
ii) Display NAME, STIPEND,, SUBJECT
T of those
e studentss whose name startts
R’
with ‘R
Anss: select NAME,
N STIIPEND, SU om GRADUATE wheere NAME
UBJECT fro E like ‘R%’’ ;
iii) To cou
unt the nu
umber of sstudents who
w are either
e PHYYSICS or COMPUTER
R
SC graduates.
Ans: seelect COU
UNT(SUBJEECT) from GRADUA
ATE where
e SUBJECTT = ‘PHYSIC
CS’ or
SUBJEC CT = ‘COM
MP SC’ ;
iv) Display total ST udents who have obbtained DIV I
TIPEND of those stu
Anss: Select SUM(STIPE
S END) from
m GRADUA
ATE WHER
RE DIV = ‘‘I’;
v) Display averagee STIPEND
D of those students who havee obtained
d AVERAG
GE
more than
t 65.
Anss: Select AVG(STIPE
A END) from
m GRADUA
ATE where
e AVERAG
GE>=65;