合 PG中有关职工表SQL查询的20道题
👉 本文共约1755个字，系统预计阅读时间或需7分钟。
目录
- 1、查询每个部门的员工数量？
- 2、查询每个部门，每个职务的平均工资？
- 3、按名字长度分组，并筛选出数量大于5的名字长度？
- 4、统计表的总行数？
- 5、查询部门编号在[90,150]之间的员工信息？
- 6、like、between and、in等的用法
- 6.1、查询last_name第二个字符是a的员工
- 6.2、查询工资在10000到20000的员工信息，包括10000和20000
- 6.3、查询职位为D_VP和FI_MGR的员工信息
- 6.4、查询没有奖金的员工信息，取3行
- 7、按名字长度降序排序，取前3行
- 8、先按salary降序排序，同样salary的按employee_id升序排序，取前6行
- 9、查询每个员工的job_title，取6个员工
- 10、查询每个员工的工资等级，取6个员工？
- 11、查询每个员工及其对应的领导？
- 12、查询没有迟到记录的员工名？
- 13、查询每个部门的信息加部门的人数？
- 14、查询每个部门的平均工资的工资等级？
- 15、查询工资最少的员工信息？
- 16、查询location_id为1400或1500或2700的部门中的所有员工姓名？
- 17、查询编号最小并且工资最高的员工信息？
- 18、查询有员工的部门名？
- 19、查询有奖金的员工中工资最高的10个员工的信息？
- 20、查询department_id>50和salary>5000的员工信息，取10个？
1、查询每个部门的员工数量？
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | lhrdb=# SELECT department_id,count(*) FROM employees GROUP by department_id order by department_id; department_id | count ---------------+------- 10 | 1 20 | 2 30 | 6 40 | 1 50 | 45 60 | 5 70 | 1 80 | 34 90 | 3 100 | 6 110 | 2 | 1 (12 rows) |
2、查询每个部门，每个职务的平均工资？
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | lhrdb=# SELECT trunc(AVG(salary)),department_id,job_id FROM employees GROUP BY department_id,job_id; trunc | department_id | job_id -------+---------------+------------ 24000 | 90 | AD_PRES 6500 | 40 | HR_REP 10000 | 70 | PR_REP 12200 | 80 | SA_MAN 12000 | 110 | AC_MGR 7280 | 50 | ST_MAN 13000 | 20 | MK_MAN 2785 | 50 | ST_CLERK 12000 | 100 | FI_MGR 17000 | 90 | AD_VP 5760 | 60 | IT_PROG 7000 | | SA_REP 8396 | 80 | SA_REP 2780 | 30 | PU_CLERK 7920 | 100 | FI_ACCOUNT 4400 | 10 | AD_ASST 3215 | 50 | SH_CLERK 8300 | 110 | AC_ACCOUNT 6000 | 20 | MK_REP 11000 | 30 | PU_MAN (20 rows) |
3、按名字长度分组，并筛选出数量大于5的名字长度？
1 2 3 4 5 6 7 8 9 10 | lhrdb=# SELECT COUNT(1) c,LENGTH(last_name) len_name FROM employees GROUP BY len_name HAVING COUNT(1) >5; c | len_name ----+---------- 11 | 4 28 | 6 8 | 9 15 | 7 29 | 5 7 | 8 (6 rows) |
4、统计表的总行数？
1 2 3 4 5 6 7 8 | SELECT COUNT(*) FROM employees; --统计表的总行数107 SELECT COUNT(1) FROM employees; --统计表的总行数107 lhrdb=# SELECT COUNT(*) FROM employees; count ------- 107 (1 row) |
5、查询部门编号在[90,150]之间的员工信息？
1 2 3 4 5 6 7 8 9 10 11 12 | lhrdb=# SELECT * FROM employees WHERE department_id > 90 AND department_id < 150; employee_id | first_name | last_name | email | phone_number | job_id | salary | commission_pct | manager_id | department_id | hiredate -------------+-------------+-----------+----------+--------------+------------+----------+----------------+------------+---------------+--------------------- 108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | FI_MGR | 12000.00 | | 101 | 100 | 1998-03-03 00:00:00 109 | Daniel | Faviet | DFAVIET | 515.124.4169 | FI_ACCOUNT | 9000.00 | | 108 | 100 | 1998-03-03 00:00:00 110 | John | Chen | JCHEN | 515.124.4269 | FI_ACCOUNT | 8200.00 | | 108 | 100 | 2000-09-09 00:00:00 111 | Ismael | Sciarra | ISCIARRA | 515.124.4369 | FI_ACCOUNT | 7700.00 | | 108 | 100 | 2000-09-09 00:00:00 112 | Jose Manuel | Urman | JMURMAN | 515.124.4469 | FI_ACCOUNT | 7800.00 | | 108 | 100 | 2000-09-09 00:00:00 113 | Luis | Popp | LPOPP | 515.124.4567 | FI_ACCOUNT | 6900.00 | | 108 | 100 | 2000-09-09 00:00:00 205 | Shelley | Higgins | SHIGGINS | 515.123.8080 | AC_MGR | 12000.00 | | 101 | 110 | 2016-03-03 00:00:00 206 | William | Gietz | WGIETZ | 515.123.8181 | AC_ACCOUNT | 8300.00 | | 205 | 110 | 2016-03-03 00:00:00 (8 rows) |
6、like、between and、in等的用法
6.1、查询last_name第二个字符是a的员工
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | lhrdb=# SELECT * FROM employees WHERE last_name LIKE '_a%'; employee_id | first_name | last_name | email | phone_number | job_id | salary | commission_pct | manager_id | department_id | hiredate -------------+------------+-----------+----------+--------------------+------------+----------+----------------+------------+---------------+--------------------- 106 | Valli | Pataballa | VPATABAL | 590.423.4560 | IT_PROG | 4800.00 | | 103 | 60 | 1998-03-03 00:00:00 109 | Daniel | Faviet | DFAVIET | 515.124.4169 | FI_ACCOUNT | 9000.00 | | 108 | 100 | 1998-03-03 00:00:00 114 | Den | Raphaely | DRAPHEAL | 515.127.4561 | PU_MAN | 11000.00 | | 100 | 30 | 2000-09-09 00:00:00 116 | Shelli | Baida | SBAIDA | 515.127.4563 | PU_CLERK | 2900.00 | | 114 | 30 | 2000-09-09 00:00:00 122 | Payam | Kaufling | PKAUFLIN | 650.123.3234 | ST_MAN | 7900.00 | | 100 | 50 | 2004-02-06 00:00:00 125 | Julia | Nayer | JNAYER | 650.124.1214 | ST_CLERK | 3200.00 | | 120 | 50 | 2004-02-06 00:00:00 127 | James | Landry | JLANDRY | 650.124.1334 | ST_CLERK | 2400.00 | | 120 | 50 | 2004-02-06 00:00:00 128 | Steven | Markle | SMARKLE | 650.124.1434 | ST_CLERK | 2200.00 | | 120 | 50 | 2004-02-06 00:00:00 131 | James | Marlow | JAMRLOW | 650.124.7234 | ST_CLERK | 2500.00 | | 121 | 50 | 2004-02-06 00:00:00 133 | Jason | Mallin | JMALLIN | 650.127.1934 | ST_CLERK | 3300.00 | | 122 | 50 | 2004-02-06 00:00:00 137 | Renske | Ladwig | RLADWIG | 650.121.1234 | ST_CLERK | 3600.00 | | 123 | 50 | 2002-12-23 00:00:00 140 | Joshua | Patel | JPATEL | 650.121.1834 | ST_CLERK | 2500.00 | | 123 | 50 | 2002-12-23 00:00:00 141 | Trenna | Rajs | TRAJS | 650.121.8009 | ST_CLERK | 3500.00 | | 124 | 50 | 2002-12-23 00:00:00 142 | Curtis | Davies | CDAVIES | 650.121.2994 | ST_CLERK | 3100.00 | | 124 | 50 | 2002-12-23 00:00:00 143 | Randall | Matos | RMATOS | 650.121.2874 | ST_CLERK | 2600.00 | | 124 | 50 | 2002-12-23 00:00:00 144 | Peter | Vargas | PVARGAS | 650.121.2004 | ST_CLERK | 2500.00 | | 124 | 50 | 2002-12-23 00:00:00 146 | Karen | Partners | KPARTNER | 011.44.1344.467268 | SA_MAN | 13500.00 | 0.30 | 100 | 80 | 2002-12-23 00:00:00 148 | Gerald | Cambrault | GCAMBRAU | 011.44.1344.619268 | SA_MAN | 11000.00 | 0.30 | 100 | 80 | 2002-12-23 00:00:00 152 | Peter | Hall | PHALL | 011.44.1344.478968 | SA_REP | 9000.00 | 0.25 | 145 | 80 | 2014-03-05 00:00:00 154 | Nanette | Cambrault | NCAMBRAU | 011.44.1344.987668 | SA_REP | 7500.00 | 0.20 | 145 | 80 | 2014-03-05 00:00:00 164 | Mattea | Marvins | MMARVINS | 011.44.1346.329268 | SA_REP | 7200.00 | 0.10 | 147 | 80 | 2014-03-05 00:00:00 167 | Amit | Banda | ABANDA | 011.44.1346.729268 | SA_REP | 6200.00 | 0.10 | 147 | 80 | 2014-03-05 00:00:00 172 | Elizabeth | Bates | EBATES | 011.44.1343.529268 | SA_REP | 7300.00 | 0.15 | 148 | 80 | 2014-03-05 00:00:00 176 | Jonathon | Taylor | JTAYLOR | 011.44.1644.429265 | SA_REP | 8600.00 | 0.20 | 149 | 80 | 2014-03-05 00:00:00 180 | Winston | Taylor | WTAYLOR | 650.507.9876 | SH_CLERK | 3200.00 | | 120 | 50 | 2014-03-05 00:00:00 184 | Nandita | Sarchand | NSARCHAN | 650.509.1876 | SH_CLERK | 4200.00 | | 121 | 50 | 2014-03-05 00:00:00 187 | Anthony | Cabrio | ACABRIO | 650.509.4876 | SH_CLERK | 3000.00 | | 121 | 50 | 2014-03-05 00:00:00 190 | Timothy | Gates | TGATES | 650.505.3876 | SH_CLERK | 2900.00 | | 122 | 50 | 2014-03-05 00:00:00 196 | Alana | Walsh | AWALSH | 650.507.9811 | SH_CLERK | 3100.00 | | 124 | 50 | 2014-03-05 00:00:00 201 | Michael | Hartstein | MHARTSTE | 515.123.5555 | MK_MAN | 13000.00 | | 100 | 20 | 2016-03-03 00:00:00 202 | Pat | Fay | PFAY | 603.123.6666 | MK_REP | 6000.00 | | 201 | 20 | 2016-03-03 00:00:00 203 | Susan | Mavris | SMAVRIS | 515.123.7777 | HR_REP | 6500.00 | | 101 | 40 | 2016-03-03 00:00:00 204 | Hermann | Baer | HBAER | 515.123.8888 | PR_REP | 10000.00 | | 101 | 70 | 2016-03-03 00:00:00 (33 rows) |
6.2、查询工资在10000到20000的员工信息，包括10000和20000
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | lhrdb=# SELECT * FROM employees WHERE salary BETWEEN 10000 AND 20000; employee_id | first_name | last_name | email | phone_number | job_id | salary | commission_pct | manager_id | department_id | hiredate -------------+------------+-----------+----------+--------------------+--------+----------+----------------+------------+---------------+--------------------- 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | AD_VP | 17000.00 | | 100 | 90 | 1992-04-03 00:00:00 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | AD_VP | 17000.00 | | 100 | 90 | 1992-04-03 00:00:00 108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | FI_MGR | 12000.00 | | 101 | 100 | 1998-03-03 00:00:00 114 | Den | Raphaely | DRAPHEAL | 515.127.4561 | PU_MAN | 11000.00 | | 100 | 30 | 2000-09-09 00:00:00 145 | John | Russell | JRUSSEL | 011.44.1344.429268 | SA_MAN | 14000.00 | 0.40 | 100 | 80 | 2002-12-23 00:00:00 146 | Karen | Partners | KPARTNER | 011.44.1344.467268 | SA_MAN | 13500.00 | 0.30 | 100 | 80 | 2002-12-23 00:00:00 147 | Alberto | Errazuriz | AERRAZUR | 011.44.1344.429278 | SA_MAN | 12000.00 | 0.30 | 100 | 80 | 2002-12-23 00:00:00 148 | Gerald | Cambrault | GCAMBRAU | 011.44.1344.619268 | SA_MAN | 11000.00 | 0.30 | 100 | 80 | 2002-12-23 00:00:00 149 | Eleni | Zlotkey | EZLOTKEY | 011.44.1344.429018 | SA_MAN | 10500.00 | 0.20 | 100 | 80 | 2002-12-23 00:00:00 150 | Peter | Tucker | PTUCKER | 011.44.1344.129268 | SA_REP | 10000.00 | 0.30 | 145 | 80 | 2014-03-05 00:00:00 156 | Janette | K_ing | JKING | 011.44.1345.429268 | SA_REP | 10000.00 | 0.35 | 146 | 80 | 2014-03-05 00:00:00 162 | Clara | Vishney | CVISHNEY | 011.44.1346.129268 | SA_REP | 10500.00 | 0.25 | 147 | 80 | 2014-03-05 00:00:00 168 | Lisa | Ozer | LOZER | 011.44.1343.929268 | SA_REP | 11500.00 | 0.25 | 148 | 80 | 2014-03-05 00:00:00 169 | Harrison | Bloom | HBLOOM | 011.44.1343.829268 | SA_REP | 10000.00 | 0.20 | 148 | 80 | 2014-03-05 00:00:00 174 | Ellen | Abel | EABEL | 011.44.1644.429267 | SA_REP | 11000.00 | 0.30 | 149 | 80 | 2014-03-05 00:00:00 201 | Michael | Hartstein | MHARTSTE | 515.123.5555 | MK_MAN | 13000.00 | | 100 | 20 | 2016-03-03 00:00:00 204 | Hermann | Baer | HBAER | 515.123.8888 | PR_REP | 10000.00 | | 101 | 70 | 2016-03-03 00:00:00 205 | Shelley | Higgins | SHIGGINS | 515.123.8080 | AC_MGR | 12000.00 | | 101 | 110 | 2016-03-03 00:00:00 (18 rows) |
6.3、查询职位为D_VP和FI_MGR的员工信息
1 2 3 4 5 | lhrdb=# SELECT * FROM employees WHERE job_id IN ('D_VP','FI_MGR'); employee_id | first_name | last_name | email | phone_number | job_id | salary | commission_pct | manager_id | department_id | hiredate -------------+------------+-----------+----------+--------------+--------+----------+----------------+------------+---------------+--------------------- 108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | FI_MGR | 12000.00 | | 101 | 100 | 1998-03-03 00:00:00 (1 row) |
6.4、查询没有奖金的员工信息，取3行
1 2 3 4 5 6 7 | lhrdb=# SELECT * FROM employees WHERE commission_pct IS NULL limit 3; employee_id | first_name | last_name | email | phone_number | job_id | salary | commission_pct | manager_id | department_id | hiredate -------------+------------+-----------+----------+--------------+---------+----------+----------------+------------+---------------+--------------------- 100 | Steven | K_ing | SKING | 515.123.4567 | AD_PRES | 24000.00 | | | 90 | 1992-04-03 00:00:00 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | AD_VP | 17000.00 | | 100 | 90 | 1992-04-03 00:00:00 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | AD_VP | 17000.00 | | 100 | 90 | 1992-04-03 00:00:00 (3 rows) |
LIKE模糊匹配,%任意0个或多个字符; _任意1个字符；无法匹配null;
BETWEEN a AND b,匹配a-b范围内的内容,包括a和b临界;
IN(list),匹配list中的内容;
IS NULL / IS NOT NULL ,判断是否为null,建议使用这个而非<=>。
7、按名字长度降序排序，取前3行
1 2 3 4 5 6 7 | lhrdb=# SELECT * FROM employees ORDER BY LENGTH(last_name) DESC limit 3; employee_id | first_name | last_name | email | phone_number | job_id | salary | commission_pct | manager_id | department_id | hiredate -------------+------------+-------------+----------+--------------+----------+---------+----------------+------------+---------------+--------------------- 126 | Irene | Mikkilineni | IMIKKILI | 650.124.1224 | ST_CLERK | 2700.00 | | 120 | 50 | 2004-02-06 00:00:00 119 | Karen | Colmenares | KCOLMENA | 515.127.4566 | PU_CLERK | 2500.00 | | 114 | 30 | 2000-09-09 00:00:00 136 | Hazel | Philtanker | HPHILTAN | 650.127.1634 | ST_CLERK | 2200.00 | | 122 | 50 | 2002-12-23 00:00:00 (3 rows) |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
