Page 79 - IP

ID BASIC ALLOWANCE CONSULTATION
101 12000 1000 300
104 23000 2300 500
107 32000 4000 500
114 12000 5200 100
109 42000 1700 200
105 18900 1690 300
130 21700 2600 300

(a). Display NAME of all doctors who are in “MEDICINE” having more than 10
years experience and basic more than 10000.
(b). Display the average of all doctors working in “ENT” department using the
DOCTOR and where salary=basic + allowance.
(c). Display the minimum ALLOWANCE of female doctors.
(d). Display the highest consultation fee among all male doctors.
(e) SELECT DOC.DEPT, SUM(SAL.BASIC) FROM DOCTOR DOC , SALARY
SAL WHERE DOC.ID=SAL.ID;

Q6. Write the resulting output of the following : [0.5 X 6 = 3]
(a) Select SUBSTR(‘NetBeans IDE Programmer’, 10,3);
(b) Select INSTR(TRIM(‘ ABS Public School ‘)5);
(c) Select 200 + SQRT(144);
(d) Select MOD ( ROUND ( 125.60,1) , 5 );
(e) Select LEFT(‘RAUNAK SHARMA’ , 5);
(f) Select ROUND(1045.439 , 2) + MOD (12.12 , 3)
Q7. Create table “Employee” as per following table Instance Chart. 2
Column EmpID EmpNa EmpAddre EmpPho EmpSal DeptID
Name me ss ne
Key Type Primary Foreign
Nulls/Uniq NOT
ue NULL
Fk Table Departme
nt
Fk Column Dept_ID
Data Type NUMBE VARCHA VARCHAR VARCHA DECIMA VARCHAR
R R R L
Length 6 20 30 10 9,2 2

Q – 8 Consider the table LAB given below. Write commands in SQL for (i) to (vi)
and output for (vii) [1x10=10]