Database written test-------------Identify employees ' names, salary, department name and department average salary exceeding department's average salary.

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.