SQL Practice Topics (MySQL)

The following employee table employees are available:

The table SQL is built as:

CREATE TABLE ' Employee ' (
' id ' int (one) is not NULL,
' Name ' varchar (DEFAULT NULL),
' Salary ' int (one) DEFAULT NULL,
' DeptID ' int (one) DEFAULT NULL,
' Age ' int (one) DEFAULT NULL,
PRIMARY KEY (' id ')

1. Find out the list of employees in each department above the departmental average salary

Select A.deptid,a.name from Employee A, (select Deptid,avg (Salary) as salary from employee Group by DeptID) b where a.depti D=b.deptid and a.salary>b.salary;

2. List the number of employees and department numbers in each department with wages higher than the average wage in the department, sorted by department number

Select A.deptid,count (a.id) as NUM from employee A, (select Deptid,avg (Salary) as salary from employee Group by DeptID) b W Here A.deptid=b.deptid and a.salary>b.salary GROUP by A.deptid Order by A.deptid;

3. Ask for the average salary of a person with a salary of less than 130000 per department

Select Deptid,avg (Salary) as average_salary from employee GROUP by DeptID have avg (salary) <130000;

4. Statistics of the number and proportion of employees in each salary stage

Select Salary_type,count (ID) as Num,count (ID) *100/(select count (ID) from employee) as percent from (select Id,case when s alary<120000 then ' <12w ' when salary>=120000 and salary<150000 then ' 12w~15w ' Else ' >15w ' end as Salary_typ E from employee) a group by Salary_type;

5. Statistics of average salaries for all ages in various sectors

Method One:

Select DeptID, sum (case when ages < then salary else 0 end)/sum (case-age <20 then 1 else 0 end) as ' 20-year-old average Wages ', sum (case when age >= and <30 then salary else 0 end)/sum (case is age >= and age <30 then 1 else 0 end) as ' 20-30-year-old average wage ', sum (case-age >= and salary else 0 end)/sum (case if age >=30 then 1 else 0 end) as ' >30-year-old and above average wage ' from the employee group by DeptID;

Method Two:

Select DeptID, Age_type,avg (salary) as salary from (select Id,salary,deptid,case when ages <=20 then ' average wage under 20 years ' when AG E>20 and age<=30 Then ' 20-30-year-old average salary ' else ' greater than 30 year-old average wage ' end as Age_type from employee ' a group by Deptid,age_type;

SQL Practice Topics (MySQL)