There is a period of time did not do the database problem, the interview by chance to do so a topic, feel good, with memory will be written down, and then implemented in the database once.
The topic is like this: an Employee table employee, including field id,name,salary,dep_no; A Departmental Information table department, which contains field Id,dep_no,name, where employee dep_no is associated department dep_no; Write SQL find out the names, salaries, department names and departments of employees who have exceeded the department's average salary. Average salary.
Here are the two tables I created:
DROP TABLE IF EXISTSDepartment;CREATE TABLEDepartment (IDint not NULLAuto_incrementPRIMARY KEY, Dep_noint not NULL, ' name 'VARCHAR( -) not NULL);INSERT intoDepartment (Dep_no,name)VALUES(Ten,'Research Department');INSERT intoDepartment (Dep_no,name)VALUES( -,'Testing Department');INSERT intoDepartment (Dep_no,name)VALUES( -,'Finance Department');
DROP TABLE IF EXISTSemployee;CREATE TABLEemployee (' ID 'int not NULLauto_increment, ' name 'varchar( -) not NULL, ' salary 'Double(Ten,2) not NULL DEFAULT 0, ' Dep_no 'INTEGER not NULL,PRIMARY KEY(' id '));INSERT intoEmployee (NAME,SALARY,DEP_NO)SELECT 'Bruce',15000.00, Dep_no fromDepartmentWHERE' Name '= 'Research Department';INSERT intoEmployee (NAME,SALARY,DEP_NO)SELECT 'Kevin',16000.00, Dep_no fromDepartmentWHERE' Name '= 'Research Department';INSERT intoEmployee (NAME,SALARY,DEP_NO)SELECT 'LWW',12000.00, Dep_no fromDepartmentWHERE' Name '= 'Finance Department';INSERT intoEmployee (NAME,SALARY,DEP_NO)SELECT 'Linda',10000.00, Dep_no fromDepartmentWHERE' Name '= 'Finance Department';INSERT intoEmployee (NAME,SALARY,DEP_NO)SELECT 'David',10000.00, Dep_no fromDepartmentWHEREName= 'Testing Department';INSERT intoEmployee (NAME,SALARY,DEP_NO)SELECT 'Sandy',8000.00, Dep_no fromDepartmentWHEREName= 'Testing Department';INSERT intoEmployee (NAME,DEP_NO)SELECT 'Dennis', Dep_no fromDepartmentWHEREName= 'Testing Department';
The analysis of the topic: first of all to get the name of the target staff, pay needless to be obtained from the employee table; As for the latter two data department name and department average salary, direct check department certainly not get, need two table joint query, may write this piece of sql:
SELECT AVG as avg_sal,d.dep_no,d. ' Name ' from WHERE= e.dep_noGROUP by d.dep_no,d. ' Name ';
The reason why Dep_no,name two fields as a group field is because there is name in the following query target, and Dep_no as a unique identifier of the department, it is a natural grouping field, taking into account that the query results in this section are linked to the employee table, and the two can only be Dep_no connection.
Put the above results as temporary table tmp into the main query, and finally get:
SELECTE. ' Name ', e.salary,tmp.dep_name,tmp.avg_sal fromemployee E, (SELECT AVG(e.salary) asAvg_sal,d.dep_no,d. ' Name ' asDep_name fromEmployee E,department DWHERED.dep_no=E.dep_noGROUP byd.dep_no,d. ' name ') TMPWHEREE.dep_no=Tmp.dep_no andE.salary>Tmp.avg_sal;
Search a circle, as if other places also have similar problems, but I think I do this version is very detailed, very valuable reference.
Database written test-------------Identify employees ' names, salary, department name and department average salary exceeding department's average salary.