Question

Study the following tables STAFF and SALARY and write SQL commands for the questions

(i) to (iv) and give outputs for SQL queries (v) to (viii). /* CBSE 2006 */

TABLE: STAFF

ID NAME DEPT SEX EXPERIENCE

101 Siddharth SALES M 12

104 Raghav FINANCE M 5

107 Naman RESEARCH M 10

114 Nupur SALES F 3

109 Janvi FINANCE F 9

105 Rama RESEARCH M 10

117 James SALES F 3

111 Binoy FINANCE F 12

130 Samuel SALES M 15

TABLE: SALARY

ID BASIC ALLOWANCE COMMISSION%

101 12000 1000 3

104 23000 2300 5

107 32000 4000 5

114 12000 5200 10

109 42000 1700 20

105 18900 1690 3

130 21700 2600 30

i. Display NAME of all staff who are in “SALES” having more than 10 years

experience from the table STAFF.

ii. Display the average salary of all staff working in “FINANCE” department using

the tables STAFF and SALARY. (SALARY= BASIC + ALLOWANCE).

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;

vii. SELECT distinct DEPT from STAFF;

viii. SELECT MAX(EXPERIENCE) FROM STAFF GROUP BY SEX;

Answer 1

(i) select NAME from STAFF where EXPERIENCE > 10;

(ii) select BASIC + ALLOWANCE from  FINANCE;

(iii) select min(ALLOWANCE) from FINANCE,STAFF where FINANCE.ID = STAFF.ID and SEX = "F";

(iv) select max(COMMISSION%) from FINANCE,STAFF where FINANCE.ID = STAFF.ID and SEX = "M";

(v)

count(*)

4

(vi)

NAME	DEPT	BASIC
Siddharth	sales	12000
Nupur	sales	12000
Samuel	sales	21700

(vii)

distinct(DEPT)

Sales

Finance

Research

(viii)

Max(EXPERIENCE)	SEX
12	F
15	M