xii C- IP -LIST OF PRACTICALS

Assignment (SQL ) – Class XII- IP 2012-13

 

1.                                                                    

 

Table : EMP

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7839	REA	MANAGER	67	12-DEC-98	5000	0	10
1234	PREM	CLERK	87	11-FEB-77	12000	1500	20
6754	SITA	MANAGER	89	12-MAR-99	10000	1000	20
6574	GITA	SALESMAN	98	11-JUN-99	9000	0	30
9876	HONEY	CLERK	65	12-JUN-00	12000	800	20
8976	REEMA	SALESMAN	91	10-SEP-88	6000	100	30

 

(e) Display names of employees whose names include either of the substring “TH” or “LL”.

(f) Display data of all employees sorted by their department, seniority and salary.

(g) Find all the employees who have no manager.

(h) To display all employees who were hired during 1995.

(i) Show the average salary for all departments with more than 3 people for a job.

(j) Find out number of employees having ‘MANAGER’ as job.

(k) Create view DEPT20 with name and the salary of employees for dept 20.

(l) Display department no. and number of employees in each department.        

(m) Find the output of the following :

     1. SELECT SYSDATE FROM DUAL;

     2. SELECT ENAME,SAL FROM EMPLOYEE WHERE DEPTNO=20;

     3. SELECT COUNT(*) FROM EMP;

            4. SELECT AVG(SAL) FROM EMP;                                                         

 

2. Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (v) to (viii)

 

WORKERS

W_ID	FIRSTNAME	LASTNAME	ADDRESS	CITY
102	Sam	Tones	33 Elm St.	Paris
105	Sarah	Ackerman	440 U.S. 110	New York
144	Manila	Sengupta	24 Friends Street	New Delhi
210	George	Smith	83 First Street	Howard
255	Mary	Jones	842 Vine Ave.	Losantiville
300	Robert	Samuel	9 Fifth Cross	Wasington
335	Henry	Williams	12Moore Street	Boston
403	Ronny	Lee	121 Harrison St.	New York
451	Pat	Thompson	11 Red Road	Paris

 

DESIG

W_ID	SALARY	BENEFITS	DESIGNATION
102	75000	15000	Manager
105	85000	25000	Director
144	70000	15000	Manager
210	75000	12500	Manager
255	50000	12000	Clerk
300	45000	10000	Clerk
335	40000	10000	Clerk
400	32000	7500	Salesman
451	28000	7500	Salesman

(i)                             To display w_id, firstname, address and city of all employees living in new york from the table workers.

(ii)                           To display the content of workers table in ascending order of lastname.

(iii)                         To display the firstname, lastname and total salary of all alerks from the tables workers and design, where total salary is calculated as salary + benefits.

(iv)                         To display the minimum salary among Managers and Clerks from the table DESIG.

(v)                           SELECT FIRSTNAME, SALARY FROM WORKERS, DESIG WHERE DESIGNATION = ‘Manager’ AND WORKERS.W_ID = DESIG.W_ID

(vi)                         SELECT COUNT(DISTINCT DESIGNATION) FROM DESIG.

(vii)                       SELECT DESIGNATION, SUM(SALARY FROM DESIGNATION GROUP BY DESIGNATION HAVING COUNT(*)<3;

(viii)                     SELECT SUM(BENEFITS) FROM WORKERS WHERE DESIGNATION = ‘Salesman’;

(IX)            Perform all types of join operations on the above two tables.

 

3. (a)Write SQL commands for (a) to ( j) and write output for (h) on the basis of Teacher relation given below.

 

No	Name	Age	Department	Date of Join	Salary	Sex
1.	jigal	34	Computer	10/01/97	12000	M
2.	Sharmila	31	History	24/03/98	20000	F
3.	Sandeep	32	Maths	12/12/96	30000	M
4.	Sangeeta	35	History	01/07/99	40000	F
5.	Rakesh	42	Maths	05/09/97	25000	M
6.	Shyam	50	History	27/02/97	30000	M
7.	Shiv Om	44	Computer	25/02/97	21000	M
8.	Shalakha	33	Maths	31/07/97	20000	F

 

(a)    To show all information about the teacher of history department.

(b)   To list the names of female teachers who are in Maths department

(c)    To list names of all teachers with their date of joining in ascending order.

(d)   To display students name, fee,age for male teacher only

(e)    To count the number of teachers with age>23.

(f)    To insert a new row in the TEACHER table with the following data:

9,”Raja”,26,”Computer”,13/05/95,2300,”M”.

       (g)To show all information about the teachers in this table

       (h)Add a new column named “Address”.

       (i)Arrange the whole table in the alphabetical order to name

       (j)Display the age of the teachers whose name starts with ‘S,.

       (k)Give the output of following statement.

(i)                 Select COUNT(distinct department) from TEACHER.

(ii)               Select MAX(Age)from Teacher where sex=”F”

(iii)             Select AVG(Salary) from Teacher where Dateofjoin< ‘12/07/96’

(iv)             Select SUM(Salary) from teacher where Dateofjoin< ‘12/07/96’

4.  Write SQL commands for the statements (i) to (vi) on the basis of the table EMPLOYEE:

 

Name	EmpNo	DeptNo	Job	Sal	Comm
G. Hussain	2098	10	President	7000
Pallav	3099	30	Manager	9000	1400
Y.D. Sharma	8001	20	Clerk	8500
Bhawna	7901	10	President	4520	300
A. Dasgupta	5400	20	Analyst	6580
P. Arora	3400	10	Clerk	12000
Col. Singhvi	2100	30	Manager	1200	500
Amit	3100	30	Analyst	3250
A D’souza	2211	10	Clerk	6900

 

i.        Show sum and average salary for marketing deptt.                                                    

ii.      Check all employees have unique names.                                                                     

iii.    Find al employess whose deptt is same as of ‘Amit’.                                                    

iv.    Increase the salary of all employees by 10%,                                                                

v.      Find the deptt that is paying max salaries to its employees.                                        

vi.    Display the details of all the employees having salary less than 10000.                                 

5.            Write the SQL commands for (a) to (d) and write the output for (e) on

               the basis of table Hospital :

 

No	Name	Age	Department	Dateofadmin	Charge	Sex
1	Arpit	62	Surgery	21/01/06	300	M
2	Zayana	18	ENT	12/12/05	250	F
3	Kareem	22	Orthopedic	19/02/06	450	M
4	Abhilash	26	Surgery	24/11/06	300	M
5	Dhanya	24	ENT	20/10/06	350	F
6	Siju	23	Cardiology	10/10/06	800	M
7	Ankita	16	ENT	13/04/06	100	F
8	Divya	15	Cardiology	10/11/06	500	F
9	Nidhin	25	Orthopedic	12/05/06	700	M
10	Hari	28	Surgery	19/03/06	450	M

 

(a)              To show all information about the patients of cardiology department.         

(b)              To list the name of female patients who are in ENT department.        

(c)              To list names of all patients with their date of admission in ascending order.                                 

(d)              To count the no of patients with age > 20.                                              

(e)              Give the output of the following SQL commands:                                

(i)          Select COUNT(DISTINCT charges ) from hospital;

(ii)       Select MIN(age) from hospital where Sex=’M’;

(iii)           Select SUM(charges) from hospital where Sex =’F’;

(iv)       Select avg(charges) from hospital where dateofadm>{12/05/06};

 

6.Write down the output produced by following queries after execution.

a) SELECT CONCAT(‘PRIYA’,’JAIN’) FROM DUAL;

b) SELECT SUBSTR(‘INFORMATICS PRACTICES’,3,4) FROM DUAL;

c) SELECT INSTR(‘INDOOR OUTDOOR’,’OR’) FROM DUAL;

d) SELECT LENGTH(‘INFORMATICS PRACTICES’) FROM DUAL;

e) SELECT LOWER(RIGHT(‘JAVA’,2))FROM DUAL;

f) SELECT MOD(11,3) FROM DUAL;

g) SELECT SIGN(-67) FROM DUAL;

h) SELECT SQRT(81) FROM DUAL;

i) SELECT DATEOFMONTH(‘2009-02-03’)FROM DUAL;

j) SELECT ROUND(15.193,1) FROM DUAL;

 

7. Write down the output produced by following queries after execution.

a) SELECT CONCAT (‘VISHAL’,’KUMAR’) FROM DUAL;

 

b) SELECT SUBSTR (‘INFORMATICS PRACTICES’,3,4) FROM DUAL;

c) SELECT INSTR (‘INDOOR OUTDOOR’,’OR’) FROM DUAL;

d) SELECT LENGTH (‘INFORMATICS PRACTICES’) FROM DUAL;

e) SELECT LOWER (RIGHT (‘JAVA’, 2)) FROM DUAL;

f) SELECT MOD (11,3) FROM DUAL;

g) SELECT SIGN (-67) FROM DUAL;

h) SELECT SQRT (49) FROM DUAL;

i) SELECT DATEOFMONTH (‘2010-02-16’) FROM DUAL;

j) SELECT ROUND (15.193,1) FROM DUAL;