mysql子查询习题98

#1.查询工资最低的员工信息：last name, salary 2 SELECT 3 last_name, 4 salary 5 FROM 6 employees 7 WHERE 8 salary = ( 9 SELECT 10 MIN(salary) 11 FROM 12 employees 13 ); 14 15 #2.查询平均工资最低的部门信息 16 SELECT 17 * 18 FROM 19 departments 20 WHERE 21 department_id = ( 22 SELECT 23 department_id 24 FROM 25 employees 26 GROUP BY 27 department_id 28 ORDER BY 29 avg(salary) 30 LIMIT 1 31 ); 32 33 #3.查询平均工资最低的部门信息和该部门的平均工资 34 #①各部门的平均工资 35 SELECT 36 avg(salary), 37 department_id 38 FROM 39 employees 40 GROUP BY 41 department_id #②求出最低平均工资的部门编号 42 SELECT 43 avg(salary), 44 department_id 45 FROM 46 employees 47 GROUP BY 48 department_id 49 ORDER BY 50 avg(salary) 51 LIMIT 1; 52 53 #③查询部门信息 54 SELECT 55 d.*, ag 56 FROM 57 departments d 58 JOIN ( 59 SELECT 60 avg(salary), 61 department_id 62 FROM 63 employees 64 GROUP BY 65 department_id 66 ORDER BY 67 avg(salary) 68 LIMIT 1 69 ) ag_dep ON d.department_id = ag_dep.department_id #4.查询平均工资最高的job信息 70 SELECT 71 avg(salary) 72 FROM 73 employees 74 GROUP BY 75 job_id 76 ORDER BY 77 avg(salary) DESC 78 LIMIT 1 ②查询job信息 SELECT 79 * 80 FROM 81 jobs 82 WHERE 83 job_id = ( 84 SELECT 85 job_id 86 FROM 87 employees 88 GROUP BY 89 job_id 90 ORDER BY 91 avg(salary) DESC 92 LIMIT 1 93 ); 94 95 #5.查询平均工资高于公司平均工资的部门有哪些？ 96 SELECT 97 avg(salary), 98 department_id 99 FROM 100 employees 101 GROUP BY 102 department_id; 103 104 105 HAVING 106 avg(salary) > ( 107 SELECT 108 avg(salary) 109 FROM 110 employees 111 ); 112 113 #6.查询出公司中所有 manager的详细信息 114 SELECT 115 * 116 FROM 117 employees 118 WHERE 119 employee_id IN ( 120 SELECT DISTINCT 121 manager_id 122 FROM 123 employees 124 ); 125 126 #7.各个部门中最高工资中最低的那个部门的最低工资是多少 127 SELECT 128 min(salary), 129 department_id 130 FROM 131 employees 132 WHERE 133 department_id = ( 134 SELECT 135 department_id 136 FROM 137 employees 138 GROUP BY 139 department_id 140 ORDER BY 141 max(salary) 142 LIMIT 1 143 ); 144 145 #8.查询平均工资最高的部门的 manager的详细信息：last_name, department id, email 146 SELECT 147 last_name, 148 d.department_id, 149 email, 150 salary 151 FROM 152 employees e 153 JOIN departments d ON d.manager_id = e.manager_id 154 WHERE 155 d.department_id = ( 156 SELECT 157 department_id 158 FROM 159 employees 160 GROUP BY 161 department_id 162 ORDER BY 163 avg(salary) DESC 164 LIMIT 1 165 );

 

mysql子查询习题98

标签：mail   公司   子查询   平均工资   mysq   manage   span   rtm   desc