Sample Question No. 7 for IP - Informatics Practices ( AISSCE 2014 ) _ B

Sample Question No. 7 for IP - Informatics Practices ( AISSCE 2014 ) _ B

[1+1+1+1+1+1+2+1+1]

Answer the following questions:

(a) Study the following table STAFF and SALARY and write MySQL command for the questions (i) to (iv) and give output for the (v) to (vi)

                                                                       Table : STAFF

ID	Name	DEPT	SEX	EXPERIENCE
101	Siddharat	Sales	M	12
104	Raghav	Finance	M	6
107	Naman	Research	M	10
114	Nupur	Sales	F	3
109	Janvi	Finance	F	9
105	Rama	Research	M	10
117	James	Sale	F	3
111	Binoy	Finance	F	12
130	Samuel	Sales	M	15

                                                                       Table : SALARY

ID	BAsic	Allowance	Commission
101	15000	1500	800
104	17000	1200	500
107	16000	1000	200
114	20000	2200	550
109	18000	1000	250
105	15000	1200	150
117	18000	1700	100
111	20000	1500	300
130	18000	1200	500

(i) Display NAME of all the staff who is in SALES having more than 10 years experience from the table staff.

(ii) Display the average Basic Salary of all staff working in “Finance” department using the table staff and salary.

(iii) Display the minimum ALLOWANCE of female staff.

(iv) Display the highest commission among all male staff.

(v) Select count(*) from STAFF where sex=’F’;

(vi) SELECT name, dept, basic FROM staff, Salary WHERE dept=’sales’ and staff.id=salary.id;

Answer the following question based on the table VOTER given below:

                                                                       Table : VOTER

Column Name	Data Type	Size	Constraints	Description
V_id	BIGNT	8	Primary Key	Voter identification
Vname	VARCHAR	25	Not Null	Name of the voter
Age	INT	3	Check > 17	Age should not less than equal to 17
Address	VARCHAR2	30		Address of voter
Phone	VARCHAR	10		Phone number of the voter

(i) Write an SQL command to create the above table.

(ii) Write the command to delete all the rows of particular voter from the table voter where voter ID between 10 and 20.

(iii) Delete the table physically.