SQL QUESTION 4

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      Sales    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

Display NAME of all the staff who is in SALES having more than 10 year experience from the table.
  




SELECT NAME FROM STAFF
WHERE DEPT = ‘Sales’ AND EXPERIENCE > 10;                                          
 
Display the average Basic Salary of all staff working in ”Finance” department using the table staff and salary. 

SELECT AVG(Basic) FROM STAFF, SALARY
WHERE STAFF.ID = SALARY.ID AND DEPT = ‘Finance’;          

Display the minimum ALLOWANCE of female staff.

SELECT MIN(Allowance) FROM STAFF, SALARY
WHERE STAFF.ID = SALARY.ID AND SEX = ‘F’;              
 

Display the highest commission among all male staff.

SELECT MAX(Commission) FROM STAFF, SALARY
WHERE STAFF.ID = SALARY.ID AND SEX = ‘M’;             

GIVE OUTPUT:
 
Select count(*) from STAFF where sex=’F’;
 ANS:4
                       
SELECT NAME,DEPT,BASIC FROM STAFF,SALARY 
WHERE DEPT=’SALES’ AND STAFF.ID=SALARY.ID;    
                      

ANSWER:

Name    DEPT    Basic
Siddharat    Sales    15000
Nupur    Sales    20000
James    Sales    18000
Samuel     Sales    18000