mysql sql教程（MySQL基础之SQL练习）

mysql sql教程（MySQL基础之SQL练习）-- 注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要多行注解mysql注解：#单行注解-- 单行注解(注意有空格)

1.数据脚本导入

/* SQLyog Ultimate v10.00 Beta1 MySQL - 5.7.24 : Database - myemployees ********************************************************************* */ /*!40101 SET NAMES utf8 */; /*!40101 SET SQL_MODE=''*/; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES SQL_NOTES=0 */; CREATE DATABASE /*!32312 IF NOT EXISTS*/`myemployees` /*!40100 DEFAULT CHARACTER SET gb2312 */; USE `myemployees`; /*Table structure for table `departments` */ DROP TABLE IF EXISTS `departments`; CREATE TABLE `departments` ( `department_id` int(4) NOT NULL AUTO_INCREMENT `department_name` varchar(3) DEFAULT NULL `manager_id` int(6) DEFAULT NULL `location_id` int(4) DEFAULT NULL PRIMARY KEY (`department_id`) KEY `loc_id_fk` (`location_id`) CONSTRAINT `loc_id_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`) ) ENGINE=InnoDB AUTO_INCREMENT=271 DEFAULT CHARSET=gb2312; /*Data for the table `departments` */ insert into `departments`(`department_id` `department_name` `manager_id` `location_id`) values (10 'Adm' 200 1700) (20 'Mar' 201 1800) (30 'Pur' 114 1700) (40 'Hum' 203 2400) (50 'Shi' 121 1500) (60 'IT' 103 1400) (70 'Pub' 204 2700) (80 'Sal' 145 2500) (90 'Exe' 100 1700) (100 'Fin' 108 1700) (110 'Acc' 205 1700) (120 'Tre' NULL 1700) (130 'Cor' NULL 1700) (140 'Con' NULL 1700) (150 'Sha' NULL 1700) (160 'Ben' NULL 1700) (170 'Man' NULL 1700) (180 'Con' NULL 1700) (190 'Con' NULL 1700) (200 'Ope' NULL 1700) (210 'IT ' NULL 1700) (220 'NOC' NULL 1700) (230 'IT ' NULL 1700) (240 'Gov' NULL 1700) (250 'Ret' NULL 1700) (260 'Rec' NULL 1700) (270 'Pay' NULL 1700); /*Table structure for table `employees` */ DROP TABLE IF EXISTS `employees`; CREATE TABLE `employees` ( `employee_id` int(6) NOT NULL AUTO_INCREMENT `first_name` varchar(20) DEFAULT NULL `last_name` varchar(25) DEFAULT NULL `email` varchar(25) DEFAULT NULL `phone_number` varchar(20) DEFAULT NULL `job_id` varchar(10) DEFAULT NULL `salary` double(10 2) DEFAULT NULL `commission_pct` double(4 2) DEFAULT NULL `manager_id` int(6) DEFAULT NULL `department_id` int(4) DEFAULT NULL `hiredate` datetime DEFAULT NULL PRIMARY KEY (`employee_id`) KEY `dept_id_fk` (`department_id`) KEY `job_id_fk` (`job_id`) CONSTRAINT `dept_id_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`) CONSTRAINT `job_id_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`) ) ENGINE=InnoDB AUTO_INCREMENT=207 DEFAULT CHARSET=gb2312; /*Data for the table `employees` */ insert into `employees`(`employee_id` `first_name` `last_name` `email` `phone_number` `job_id` `salary` `commission_pct` `manager_id` `department_id` `hiredate`) values (100 'Steven' 'K_ing' 'SKING' '515.123.4567' 'AD_PRES' 24000.00 NULL NULL 90 '1992-04-03 00:00:00') (101 'Neena' 'Kochhar' 'NKOCHHAR' '515.123.4568' 'AD_VP' 17000.00 NULL 100 90 '1992-04-03 00:00:00') (102 'Lex' 'De Haan' 'LDEHAAN' '515.123.4569' 'AD_VP' 17000.00 NULL 100 90 '1992-04-03 00:00:00') (103 'Alexander' 'Hunold' 'AHUNOLD' '590.423.4567' 'IT_PROG' 9000.00 NULL 102 60 '1992-04-03 00:00:00') (104 'Bruce' 'Ernst' 'BERNST' '590.423.4568' 'IT_PROG' 6000.00 NULL 103 60 '1992-04-03 00:00:00') (105 'David' 'Austin' 'DAUSTIN' '590.423.4569' 'IT_PROG' 4800.00 NULL 103 60 '1998-03-03 00:00:00') (106 'Valli' 'Pataballa' 'VPATABAL' '590.423.4560' 'IT_PROG' 4800.00 NULL 103 60 '1998-03-03 00:00:00') (107 'Diana' 'Lorentz' 'DLORENTZ' '590.423.5567' 'IT_PROG' 4200.00 NULL 103 60 '1998-03-03 00:00:00') (108 'Nancy' 'Greenberg' 'NGREENBE' '515.124.4569' 'FI_MGR' 12000.00 NULL 101 100 '1998-03-03 00:00:00') (109 'Daniel' 'Faviet' 'DFAVIET' '515.124.4169' 'FI_ACCOUNT' 9000.00 NULL 108 100 '1998-03-03 00:00:00') (110 'John' 'Chen' 'JCHEN' '515.124.4269' 'FI_ACCOUNT' 8200.00 NULL 108 100 '2000-09-09 00:00:00') (111 'Ismael' 'Sciarra' 'ISCIARRA' '515.124.4369' 'FI_ACCOUNT' 7700.00 NULL 108 100 '2000-09-09 00:00:00') (112 'Jose Manuel' 'Urman' 'JMURMAN' '515.124.4469' 'FI_ACCOUNT' 7800.00 NULL 108 100 '2000-09-09 00:00:00') (113 'Luis' 'Popp' 'LPOPP' '515.124.4567' 'FI_ACCOUNT' 6900.00 NULL 108 100 '2000-09-09 00:00:00') (114 'Den' 'Raphaely' 'DRAPHEAL' '515.127.4561' 'PU_MAN' 11000.00 NULL 100 30 '2000-09-09 00:00:00') (115 'Alexander' 'Khoo' 'AKHOO' '515.127.4562' 'PU_CLERK' 3100.00 NULL 114 30 '2000-09-09 00:00:00') (116 'Shelli' 'Baida' 'SBAIDA' '515.127.4563' 'PU_CLERK' 2900.00 NULL 114 30 '2000-09-09 00:00:00') (117 'Sigal' 'Tobias' 'STOBIAS' '515.127.4564' 'PU_CLERK' 2800.00 NULL 114 30 '2000-09-09 00:00:00') (118 'Guy' 'Himuro' 'GHIMURO' '515.127.4565' 'PU_CLERK' 2600.00 NULL 114 30 '2000-09-09 00:00:00') (119 'Karen' 'Colmenares' 'KCOLMENA' '515.127.4566' 'PU_CLERK' 2500.00 NULL 114 30 '2000-09-09 00:00:00') (120 'Matthew' 'Weiss' 'MWEISS' '650.123.1234' 'ST_MAN' 8000.00 NULL 100 50 '2004-02-06 00:00:00') (121 'Adam' 'Fripp' 'AFRIPP' '650.123.2234' 'ST_MAN' 8200.00 NULL 100 50 '2004-02-06 00:00:00') (122 'Payam' 'Kaufling' 'PKAUFLIN' '650.123.3234' 'ST_MAN' 7900.00 NULL 100 50 '2004-02-06 00:00:00') (123 'Shanta' 'Vollman' 'SVOLLMAN' '650.123.4234' 'ST_MAN' 6500.00 NULL 100 50 '2004-02-06 00:00:00') (124 'Kevin' 'Mourgos' 'KMOURGOS' '650.123.5234' 'ST_MAN' 5800.00 NULL 100 50 '2004-02-06 00:00:00') (125 'Julia' 'Nayer' 'JNAYER' '650.124.1214' 'ST_CLERK' 3200.00 NULL 120 50 '2004-02-06 00:00:00') (126 'Irene' 'Mikkilineni' 'IMIKKILI' '650.124.1224' 'ST_CLERK' 2700.00 NULL 120 50 '2004-02-06 00:00:00') (127 'James' 'Landry' 'JLANDRY' '650.124.1334' 'ST_CLERK' 2400.00 NULL 120 50 '2004-02-06 00:00:00') (128 'Steven' 'Markle' 'SMARKLE' '650.124.1434' 'ST_CLERK' 2200.00 NULL 120 50 '2004-02-06 00:00:00') (129 'Laura' 'Bissot' 'LBISSOT' '650.124.5234' 'ST_CLERK' 3300.00 NULL 121 50 '2004-02-06 00:00:00') (130 'Mozhe' 'Atkinson' 'MATKINSO' '650.124.6234' 'ST_CLERK' 2800.00 NULL 121 50 '2004-02-06 00:00:00') (131 'James' 'Marlow' 'JAMRLOW' '650.124.7234' 'ST_CLERK' 2500.00 NULL 121 50 '2004-02-06 00:00:00') (132 'TJ' 'Olson' 'TJOLSON' '650.124.8234' 'ST_CLERK' 2100.00 NULL 121 50 '2004-02-06 00:00:00') (133 'Jason' 'Mallin' 'JMALLIN' '650.127.1934' 'ST_CLERK' 3300.00 NULL 122 50 '2004-02-06 00:00:00') (134 'Michael' 'Rogers' 'MROGERS' '650.127.1834' 'ST_CLERK' 2900.00 NULL 122 50 '2002-12-23 00:00:00') (135 'Ki' 'Gee' 'KGEE' '650.127.1734' 'ST_CLERK' 2400.00 NULL 122 50 '2002-12-23 00:00:00') (136 'Hazel' 'Philtanker' 'HPHILTAN' '650.127.1634' 'ST_CLERK' 2200.00 NULL 122 50 '2002-12-23 00:00:00') (137 'Renske' 'Ladwig' 'RLADWIG' '650.121.1234' 'ST_CLERK' 3600.00 NULL 123 50 '2002-12-23 00:00:00') (138 'Stephen' 'Stiles' 'SSTILES' '650.121.2034' 'ST_CLERK' 3200.00 NULL 123 50 '2002-12-23 00:00:00') (139 'John' 'Seo' 'JSEO' '650.121.2019' 'ST_CLERK' 2700.00 NULL 123 50 '2002-12-23 00:00:00') (140 'Joshua' 'Patel' 'JPATEL' '650.121.1834' 'ST_CLERK' 2500.00 NULL 123 50 '2002-12-23 00:00:00') (141 'Trenna' 'Rajs' 'TRAJS' '650.121.8009' 'ST_CLERK' 3500.00 NULL 124 50 '2002-12-23 00:00:00') (142 'Curtis' 'Davies' 'CDAVIES' '650.121.2994' 'ST_CLERK' 3100.00 NULL 124 50 '2002-12-23 00:00:00') (143 'Randall' 'Matos' 'RMATOS' '650.121.2874' 'ST_CLERK' 2600.00 NULL 124 50 '2002-12-23 00:00:00') (144 'Peter' 'Vargas' 'PVARGAS' '650.121.2004' 'ST_CLERK' 2500.00 NULL 124 50 '2002-12-23 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') (151 'David' 'Bernstein' 'DBERNSTE' '011.44.1344.345268' 'SA_REP' 9500.00 0.25 145 80 '2014-03-05 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') (153 'Christopher' 'Olsen' 'COLSEN' '011.44.1344.498718' 'SA_REP' 8000.00 0.20 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') (155 'Oliver' 'Tuvault' 'OTUVAULT' '011.44.1344.486508' 'SA_REP' 7000.00 0.15 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') (157 'Patrick' 'Sully' 'PSULLY' '011.44.1345.929268' 'SA_REP' 9500.00 0.35 146 80 '2014-03-05 00:00:00') (158 'Allan' 'McEwen' 'AMCEWEN' '011.44.1345.829268' 'SA_REP' 9000.00 0.35 146 80 '2014-03-05 00:00:00') (159 'Lindsey' 'Smith' 'LSMITH' '011.44.1345.729268' 'SA_REP' 8000.00 0.30 146 80 '2014-03-05 00:00:00') (160 'Louise' 'Doran' 'LDORAN' '011.44.1345.629268' 'SA_REP' 7500.00 0.30 146 80 '2014-03-05 00:00:00') (161 'Sarath' 'Sewall' 'SSEWALL' '011.44.1345.529268' 'SA_REP' 7000.00 0.25 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') (163 'Danielle' 'Greene' 'DGREENE' '011.44.1346.229268' 'SA_REP' 9500.00 0.15 147 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') (165 'David' 'Lee' 'DLEE' '011.44.1346.529268' 'SA_REP' 6800.00 0.10 147 80 '2014-03-05 00:00:00') (166 'Sundar' 'Ande' 'SANDE' '011.44.1346.629268' 'SA_REP' 6400.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') (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') (170 'Tayler' 'Fox' 'TFOX' '011.44.1343.729268' 'SA_REP' 9600.00 0.20 148 80 '2014-03-05 00:00:00') (171 'William' 'Smith' 'WSMITH' '011.44.1343.629268' 'SA_REP' 7400.00 0.15 148 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') (173 'Sundita' 'Kumar' 'SKUMAR' '011.44.1343.329268' 'SA_REP' 6100.00 0.10 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') (175 'Alyssa' 'Hutton' 'AHUTTON' '011.44.1644.429266' 'SA_REP' 8800.00 0.25 149 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') (177 'Jack' 'Livingston' 'JLIVINGS' '011.44.1644.429264' 'SA_REP' 8400.00 0.20 149 80 '2014-03-05 00:00:00') (178 'Kimberely' 'Grant' 'KGRANT' '011.44.1644.429263' 'SA_REP' 7000.00 0.15 149 NULL '2014-03-05 00:00:00') (179 'Charles' 'Johnson' 'CJOHNSON' '011.44.1644.429262' 'SA_REP' 6200.00 0.10 149 80 '2014-03-05 00:00:00') (180 'Winston' 'Taylor' 'WTAYLOR' '650.507.9876' 'SH_CLERK' 3200.00 NULL 120 50 '2014-03-05 00:00:00') (181 'Jean' 'Fleaur' 'JFLEAUR' '650.507.9877' 'SH_CLERK' 3100.00 NULL 120 50 '2014-03-05 00:00:00') (182 'Martha' 'Sullivan' 'MSULLIVA' '650.507.9878' 'SH_CLERK' 2500.00 NULL 120 50 '2014-03-05 00:00:00') (183 'Girard' 'Geoni' 'GGEONI' '650.507.9879' 'SH_CLERK' 2800.00 NULL 120 50 '2014-03-05 00:00:00') (184 'Nandita' 'Sarchand' 'NSARCHAN' '650.509.1876' 'SH_CLERK' 4200.00 NULL 121 50 '2014-03-05 00:00:00') (185 'Alexis' 'Bull' 'ABULL' '650.509.2876' 'SH_CLERK' 4100.00 NULL 121 50 '2014-03-05 00:00:00') (186 'Julia' 'Dellinger' 'JDELLING' '650.509.3876' 'SH_CLERK' 3400.00 NULL 121 50 '2014-03-05 00:00:00') (187 'Anthony' 'Cabrio' 'ACABRIO' '650.509.4876' 'SH_CLERK' 3000.00 NULL 121 50 '2014-03-05 00:00:00') (188 'Kelly' 'Chung' 'KCHUNG' '650.505.1876' 'SH_CLERK' 3800.00 NULL 122 50 '2014-03-05 00:00:00') (189 'Jennifer' 'Dilly' 'JDILLY' '650.505.2876' 'SH_CLERK' 3600.00 NULL 122 50 '2014-03-05 00:00:00') (190 'Timothy' 'Gates' 'TGATES' '650.505.3876' 'SH_CLERK' 2900.00 NULL 122 50 '2014-03-05 00:00:00') (191 'Randall' 'Perkins' 'RPERKINS' '650.505.4876' 'SH_CLERK' 2500.00 NULL 122 50 '2014-03-05 00:00:00') (192 'Sarah' 'Bell' 'SBELL' '650.501.1876' 'SH_CLERK' 4000.00 NULL 123 50 '2014-03-05 00:00:00') (193 'Britney' 'Everett' 'BEVERETT' '650.501.2876' 'SH_CLERK' 3900.00 NULL 123 50 '2014-03-05 00:00:00') (194 'Samuel' 'McCain' 'SMCCAIN' '650.501.3876' 'SH_CLERK' 3200.00 NULL 123 50 '2014-03-05 00:00:00') (195 'Vance' 'Jones' 'VJONES' '650.501.4876' 'SH_CLERK' 2800.00 NULL 123 50 '2014-03-05 00:00:00') (196 'Alana' 'Walsh' 'AWALSH' '650.507.9811' 'SH_CLERK' 3100.00 NULL 124 50 '2014-03-05 00:00:00') (197 'Kevin' 'Feeney' 'KFEENEY' '650.507.9822' 'SH_CLERK' 3000.00 NULL 124 50 '2014-03-05 00:00:00') (198 'Donald' 'OConnell' 'DOCONNEL' '650.507.9833' 'SH_CLERK' 2600.00 NULL 124 50 '2014-03-05 00:00:00') (199 'Douglas' 'Grant' 'DGRANT' '650.507.9844' 'SH_CLERK' 2600.00 NULL 124 50 '2014-03-05 00:00:00') (200 'Jennifer' 'Whalen' 'JWHALEN' '515.123.4444' 'AD_ASST' 4400.00 NULL 101 10 '2016-03-03 00:00:00') (201 'Michael' 'Hartstein' 'MHARTSTE' '515.123.5555' 'MK_MAN' 13000.00 NULL 100 20 '2016-03-03 00:00:00') (202 'Pat' 'Fay' 'PFAY' '603.123.6666' 'MK_REP' 6000.00 NULL 201 20 '2016-03-03 00:00:00') (203 'Susan' 'Mavris' 'SMAVRIS' '515.123.7777' 'HR_REP' 6500.00 NULL 101 40 '2016-03-03 00:00:00') (204 'Hermann' 'Baer' 'HBAER' '515.123.8888' 'PR_REP' 10000.00 NULL 101 70 '2016-03-03 00:00:00') (205 'Shelley' 'Higgins' 'SHIGGINS' '515.123.8080' 'AC_MGR' 12000.00 NULL 101 110 '2016-03-03 00:00:00') (206 'William' 'Gietz' 'WGIETZ' '515.123.8181' 'AC_ACCOUNT' 8300.00 NULL 205 110 '2016-03-03 00:00:00'); /*Table structure for table `jobs` */ DROP TABLE IF EXISTS `jobs`; CREATE TABLE `jobs` ( `job_id` varchar(10) NOT NULL `job_title` varchar(35) DEFAULT NULL `min_salary` int(6) DEFAULT NULL `max_salary` int(6) DEFAULT NULL PRIMARY KEY (`job_id`) ) ENGINE=InnoDB DEFAULT CHARSET=gb2312; /*Data for the table `jobs` */ insert into `jobs`(`job_id` `job_title` `min_salary` `max_salary`) values ('AC_ACCOUNT' 'Public Accountant' 4200 9000) ('AC_MGR' 'Accounting Manager' 8200 16000) ('AD_ASST' 'Administration Assistant' 3000 6000) ('AD_PRES' 'President' 20000 40000) ('AD_VP' 'Administration Vice President' 15000 30000) ('FI_ACCOUNT' 'Accountant' 4200 9000) ('FI_MGR' 'Finance Manager' 8200 16000) ('HR_REP' 'Human Resources Representative' 4000 9000) ('IT_PROG' 'Programmer' 4000 10000) ('MK_MAN' 'Marketing Manager' 9000 15000) ('MK_REP' 'Marketing Representative' 4000 9000) ('PR_REP' 'Public Relations Representative' 4500 10500) ('PU_CLERK' 'Purchasing Clerk' 2500 5500) ('PU_MAN' 'Purchasing Manager' 8000 15000) ('SA_MAN' 'Sales Manager' 10000 20000) ('SA_REP' 'Sales Representative' 6000 12000) ('SH_CLERK' 'Shipping Clerk' 2500 5500) ('ST_CLERK' 'Stock Clerk' 2000 5000) ('ST_MAN' 'Stock Manager' 5500 8500); /*Table structure for table `locations` */ DROP TABLE IF EXISTS `locations`; CREATE TABLE `locations` ( `location_id` int(11) NOT NULL AUTO_INCREMENT `street_address` varchar(40) DEFAULT NULL `postal_code` varchar(12) DEFAULT NULL `city` varchar(30) DEFAULT NULL `state_province` varchar(25) DEFAULT NULL `country_id` varchar(2) DEFAULT NULL PRIMARY KEY (`location_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3201 DEFAULT CHARSET=gb2312; /*Data for the table `locations` */ insert into `locations`(`location_id` `street_address` `postal_code` `city` `state_province` `country_id`) values (1000 '1297 Via Cola di Rie' '00989' 'Roma' NULL 'IT') (1100 '93091 Calle della Testa' '10934' 'Venice' NULL 'IT') (1200 '2017 Shinjuku-ku' '1689' 'Tokyo' 'Tokyo Prefecture' 'JP') (1300 '9450 Kamiya-cho' '6823' 'Hiroshima' NULL 'JP') (1400 '2014 Jabberwocky Rd' '26192' 'Southlake' 'Texas' 'US') (1500 '2011 Interiors Blvd' '99236' 'South San Francisco' 'California' 'US') (1600 '2007 Zagora St' '50090' 'South Brunswick' 'New Jersey' 'US') (1700 '2004 Charade Rd' '98199' 'Seattle' 'Washington' 'US') (1800 '147 Spadina Ave' 'M5V 2L7' 'Toronto' 'Ontario' 'CA') (1900 '6092 Boxwood St' 'YSW 9T2' 'Whitehorse' 'Yukon' 'CA') (2000 '40-5-12 Laogianggen' '190518' 'Beijing' NULL 'CN') (2100 '1298 Vileparle (E)' '490231' 'Bombay' 'Maharashtra' 'IN') (2200 '12-98 Victoria Street' '2901' 'Sydney' 'New South Wales' 'AU') (2300 '198 Clementi North' '540198' 'Singapore' NULL 'SG') (2400 '8204 Arthur St' NULL 'London' NULL 'UK') (2500 'Magdalen Centre The Oxford Science Park' 'OX9 9ZB' 'Oxford' 'Oxford' 'UK') (2600 '9702 Chester Road' '09629850293' 'Stretford' 'Manchester' 'UK') (2700 'Schwanthalerstr. 7031' '80925' 'Munich' 'Bavaria' 'DE') (2800 'Rua Frei Caneca 1360 ' '01307-002' 'Sao Paulo' 'Sao Paulo' 'BR') (2900 '20 Rue des Corps-Saints' '1730' 'Geneva' 'Geneve' 'CH') (3000 'Murtenstrasse 921' '3095' 'Bern' 'BE' 'CH') (3100 'Pieter Breughelstraat 837' '3029SK' 'Utrecht' 'Utrecht' 'NL') (3200 'Mariano Escobedo 9991' '11932' 'Mexico City' 'Distrito Federal ' 'MX'); /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;2.SQL练习

#MySQL练习

/*

数据存放到表中，然后表再放到库中

一个库中可以有多张表，每张表具有唯一的表名用来标识自己

mysql注解：

#单行注解

-- 单行注解(注意有空格)

多行注解

-- 注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要

*/

-- 0.查询当前数据库的版本

SELECT VERSION();

-- 1.查看当前所有数据库

SHOW DATABASES;

-- 2.使用指定的数据库(myemployees是数据库)

USE myemployees;

-- 3.查看当前库下的所有表

SHOW TABLES;

-- 4.查看指定的表结构(employees/departments是表)

DESC employees;

DESC departments;

#5.查询单个字段

SELECT last_name FROM employees;

-- 6.查询多个字段(多个字段之间使用逗号隔开)

SELECT last_name salary email FROM employees;

-- 7.查询所有字段(*)

SELECT * FROM employees;

-- 8.查询所有last_name 并起：姓名

SELECT last_name AS 姓名 FROM employees;

SELECT last_name 姓名 FROM employees;

-- 9.查询所有last_name 并起：Name

SELECT last_name "Name" FROM employees;

-- 10.查询员工表中涉及到的所有的部门编号(去重distinct)

SELECT DISTINCT department_id '部门' FROM employees;

-- 11.显示"我的工资是7200" ;CONCAT()连接符

SELECT CONCAT('我的工资是' salary) FROM employees;

-- 12.显示公司员工的姓名，奖金率，若奖金率为空的显示0 ;IFNULL()

SELECT last_name AS 姓名 IFNULL(commission_pct 0) AS 奖金率 FROM employees;

-- 13.显示出表 employees 的全部列，各个列之间用逗号连接，列头显示成 OUT_PUT

SELECT CONCAT(last_name ' ' email ' ' salary ' ' hiredate) AS 'OUT_PUT' FROM employees;

-- 14.查询工资>12000的员工信息

SELECT * FROM employees WHERE salary>12000;

-- 15.查询部门编号不等于90号的员工名和部门编号

SELECT last_name department_id FROM employees WHERE department_id <>90;

SELECT last_name department_id FROM employees WHERE department_id !=90;

SELECT last_name department_id FROM employees WHERE department_id NOT IN(90);

-- 16.查询工资在10000到20000之间的员工名、工资及奖金

SELECT last_name salary commission_pct FROM employees

WHERE salary BETWEEN 10000 AND 20000;

-- 17.查询员工名中包含字符a的员工信息

SELECT last_name FROM employees WHERE last_name LIKE '%a%';

-- 18.查询员工中第三个字符为n 第五个字符为l的员工名和工资

SELECT last_name salary FROM employees WHERE last_name LIKE '__n_l%';

-- 19.查询员工名中第二个字符为_(下划线)的员工名

-- 转义关键字ESCAPE 建议使用$ 其中使用\报错

SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';

-- 20.查询1992年入职的员工姓名和入职时间

SELECT last_name hiredate FROM employees WHERE hiredate LIKE '1992%';

-- 21.查询员工的姓名及部门编号，其中部门编号为1开头的

SELECT last_name department_id FROM employees WHERE department_id LIKE '1%'

-- 22.查询奖金率不是空的并且工资小于18000员工工资，员工名

SELECT salary last_name FROM employees WHERE commission_pct IS NOT NULL AND salary<18000;

-- 22.查询job_id不在'IT_PROG'这个或者工资大于12000的信息

SELECT * FROM employees WHERE job_id NOT IN('IT_PROG') OR salary>12000;

-- 23.查询员工号为176的员工姓名和部门号及年薪(commission_pct有可能为null 与null运算都为空)

SELECT last_name department_id salary*12*(1 IFNULL(commission_pct 0)) FROM employees

WHERE employee_id = 176;

-- 24.选择工资不在 5000 到 12000 的员工的姓名和工资

SELECT last_name salary FROM employees WHERE salary NOT BETWEEN 5000 AND 12000;

-- 25.选择在 20 或 50 号部门工作的员工姓名和部门号

SELECT last_name department_id FROM employees WHERE department_id IN(20 50);

-- 26.选择公司中没有管理者的员工姓名及 job_id

SELECT last_name job_id FROM employees WHERE manager_id IS NULL;

-- 27.选择姓名中有字母 a 和 e 的员工姓名

SELECT last_name FROM employees

WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';

-- 28.显示出表 employees 表中 first_name 以 'e'结尾的员工信息

SELECT * FROM employees WHERE first_name LIKE '%e';

-- 29.查询员工的信息并按照工资的从高到低排序(desc降序;asc升序)

SELECT * FROM employees ORDER BY salary DESC;

-- 30.按照年薪的高低显示员工的信息和年薪 [表达式排序]

SELECT * salary*12*(1 IFNULL(commission_pct 0)) '年薪' FROM employees

ORDER BY salary*12*(1 IFNULL(commission_pct 0)) DESC;

-- 31.选择工资不在8000到17000的员工的姓名和工资，按工资降序

SELECT last_name salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000

ORDER BY salary DESC;

-- 32.查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序

SELECT last_name AS 姓名 department_id AS 部门 salary*12*(1 IFNULL(commission_pct 0)) AS 年薪

FROM employees ORDER BY 年薪 DESC 姓名 ASC;

-- 33.选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序

SELECT last_name salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000

ORDER BY salary DESC;

-- 34.查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序

SELECT email LENGTH(email) department_id FROM employees WHERE email LIKE '%e%'

ORDER BY LENGTH(email) DESC department_id ASC;

-- 35.显示当前日期 时间s;其中DUAL是伪表

SELECT NOW();

SELECT NOW() FROM DUAL;

-- 36.显示当前时间

SELECT CURTIME();

-- 37.显示当前日期

SELECT CURDATE();

-- 38.显示2016-02-11到现在相差天数

SELECT DATEDIFF(NOW() '2016-02-11');

-- 39.用英文显示当前的月份

SELECT MONTHNAME('2018-02-04');

-- 40.显示员工的名字字节数长度

SELECT LENGTH(last_nam) 长度 FROM employees;

-- 41.把员工的姓变成大写名变成小写连接显示姓名

SELECT CONCAT(UPPER(last_name) LOWER(first_name)) 姓名 FROM employees;

-- 42.从"我是中国运动员"中截取中国显示出来

SELECT SUBSTR('我是中国运动员' 3 2);

SELECT SUBSTRING('我是中国运动员' 3 2);

-- 43.从"我是中国运动员我爱中国这个国家"中 "中国"首次出现的位置？

SELECT INSTR('我是中国运动员我爱中国这个国家' '中国') ;

-- 44.从" 我是 运动员 "中去除空格(TRIM只能去除前后空格)

SELECT TRIM(' 我是 运动员 ') ;

-- 45.员工的工资用10位数显示，不够的用空格补齐

SELECT LPAD(salary 10 '*') FROM employees;

SELECT RPAD(salary 10 '*') FROM employees;

-- 46.从"我是美国运动员我爱美国这个国家"中，把美国替换成中国

SELECT REPLACE('我是美国运动员我爱美国这个国家' '美国' '中国');

-- 47.从" 我是 运动员 *"中去除空格

SELECT REPLACE(' 我是 运动员 *' ' ' '') AS ji;

-- 48.将员工的姓名按 首字母排序，并写出姓名的长度（length）

SELECT last_name LENGTH(last_name) '长度' FROM employees

ORDER BY last_name ASC;

-- 49.查询员工工资在10000到15000的员工姓名、工资，且工资保留两位小数(四舍五入) 并且按工资降序排列

SELECT last_name ROUND(salary 2) FROM employees

WHERE salary BETWEEN 10000 AND 15000

ORDER BY salary DESC;

SELECT TRUNCATE(100.85 1);

-- 50.查询员工表中的最大入职时间和最小入职时间的相差天数 （DIFFRENCE）

SELECT DATEDIFF(MAX(hiredate) MIN(hiredate)) DIFFRENCE

FROM employees;

/*

51.使用 case-when，按照下面的条件：

job grade

AD_PRES A

ST_MAN B

IT_PROG C

SA_REP D

ST_CLERK E

产生以下结果

Last_name Job_id Grade

king AD_PRES A

....

*/

SELECT last_name 'Last_name' job_id 'Job_id'

CASE job_id WHEN 'AD_PRES' THEN 'A'

WHEN 'ST_MAN' THEN 'B'

WHEN 'IT_PROG' THEN 'C'

WHEN 'SA_REP' THEN 'D'

WHEN 'ST_CLERK' THEN 'E'

END AS 'grade'

FROM employees;

-- 52.查询每个工种的员工平均工资

SELECT AVG(salary) job_id

FROM employees

GROUP BY job_id;

-- 53.查询邮箱中包含a字符的 每个部门的最高工资

SELECT MAX(salary) department_id

FROM employees

WHERE email LIKE '%a%'

GROUP BY department_id;

-- 54.查询有奖金的每个领导手下员工的平均工资

SELECT AVG(salary) manager_id

FROM employees

WHERE commission_pct IS NOT NULL

GROUP BY manager_id;

-- 55.每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

SELECT job_id MAX(salary)

FROM employees

WHERE commission_pct IS NOT NULL

GROUP BY job_id

HAVING MAX(salary)>12000;

-- 56.每个工种有奖金的员工的最高工资>6000的工种编号和最高工资 按最高工资升序

SELECT job_id MAX(salary) m

FROM employees

WHERE commission_pct IS NOT NULL

GROUP BY job_id

HAVING m>6000

ORDER BY m ;

-- 57.查询每个工种每个部门的最低工资 并按最低工资降序

SELECT MIN(salary) job_id department_id

FROM employees

GROUP BY department_id job_id

ORDER BY MIN(salary) DESC;

-- 58.姓名中首字符大写，其他字符小写然后用_拼接，显示出来

SELECT CONCAT(UPPER(SUBSTR(last_name 1 1)) '_' LOWER(SUBSTR(last_name 2))) out_put FROM employees;

-- 59.查询有奖金的员工名和入职日期(xx月/xx日 xx年)

SELECT last_name DATE_FORMAT(hiredate '%m月/%d日 %y年') 入职日期

FROM employees

WHERE commission_pct IS NOT NULL;

/*60.

查询员工的工资，要求

部门号=30，显示的工资为1.1倍

部门号=40，显示的工资为1.2倍

部门号=50，显示的工资为1.3倍

其他部门，显示的工资为原工资

*/

SELECT salary 原始工资 department_id

CASE department_id

WHEN 30 THEN salary*1.1

WHEN 40 THEN salary*1.2

WHEN 50 THEN salary*1.3

ELSE salary

END AS 新工资

FROM employees;

/*61.

查询员工的工资的情况

如果工资>20000 显示A级别

如果工资>15000 显示B级别

如果工资>10000，显示C级别

否则，显示D级别

*/

SELECT salary

CASE

WHEN salary>20000 THEN 'A'

WHEN salary>15000 THEN 'B'

WHEN salary>10000 THEN 'C'

ELSE 'D'

END AS 工资级别

FROM employees;

-- 62.查询每个部门的员工个数

SELECT COUNT(*) FROM employees WHERE department_id=90;

-- 63.查询每个工种的员工平均工资

SELECT AVG(salary) job_id

FROM employees

GROUP BY job_id;

-- 64.查询每个位置的部门个数

SELECT COUNT(*) location_id

FROM departments

GROUP BY location_id;

-- 65.查询邮箱中包含a字符的 每个部门的最高工资

SELECT MAX(salary) department_id

FROM employees

WHERE email LIKE '%a%'

GROUP BY department_id;

-- 66.每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

SELECT job_id MAX(salary)

FROM employees

WHERE commission_pct IS NOT NULL

GROUP BY job_id

HAVING MAX(salary)>12000;

-- 67.领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资

SELECT manager_id MIN(salary)

FROM employees

GROUP BY manager_id

HAVING MIN(salary)>5000;

-- 68.每个工种有奖金的员工的最高工资>6000的工种编号和最高工资 按最高工资升序

SELECT job_id MAX(salary) m

FROM employees

WHERE commission_pct IS NOT NULL

GROUP BY job_id

HAVING m>6000

ORDER BY m ;

-- 69.查询每个工种每个部门的最低工资 并按最低工资降序

SELECT MIN(salary) job_id department_id

FROM employees

GROUP BY department_id job_id

ORDER BY MIN(salary) DESC;

-- 70查询员工名和对应的部门名

SELECT last_name department_name FROM employees departments

WHERE employees.department_id = departments.department_id;

-- 71.查询员工名、工种号、工种名

SELECT last_name j.job_id job_title

FROM employees e jobs j

WHERE e.job_id = j.job_id;

-- 72.查询有奖金的员工名、部门名

SELECT last_name department_name commission_pct

FROM employees e departments d

WHERE e.department_id = d.department_id AND commission_pct IS NOT NULL;

-- 73.查询城市名中第二个字符为0的部门名和城市名

SELECT department_name city

FROM departments d locations l

WHERE d.location_id = l.location_id AND city LIKE '_o%' ;

-- 74.查询每个城市部门的个数

SELECT COUNT(*) city FROM departments d locations l

WHERE d.location_id = l.location_id

GROUP BY city;

-- 75.查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

SELECT department_name d.manager_id MIN(salary)

FROM employees e departments d

WHERE e.department_id = d.department_id

AND commission_pct IS NOT NULL

GROUP BY department_name d.manager_id;

-- 76.查询每个工种的工种名和员工的个数，并且按员工个数降序

SELECT job_title COUNT(*)

FROM employees e jobs j

WHERE e.job_id = j.job_id

GROUP BY job_title

ORDER BY COUNT(*) DESC;

-- 77.查询员工名、部门名和所在的城市

SELECT last_name department_name city

FROM employees e departments d locations l

WHERE e.department_id = d.department_id

AND d.location_id = l.location_id;

-- 78.查询员工的工资和工资级别 并且工资级别为'D'

SELECT salary grade_level

FROM employees e job_grades j

WHERE salary BETWEEN j.`lowest_sal` AND j.`highest_sal`

AND j.grade_level='D';

-- 79.查询工资级别的个数>20的个数，并且按工资级别降序

SELECT grade_level COUNT(*)

FROM employees e job_grades j

WHERE salary BETWEEN j.`lowest_sal` AND j.`highest_sal`

GROUP BY grade_level

HAVING COUNT(*)>20

ORDER BY grade_level DESC;

-- 80.查询员工名和上级的名称

SELECT e.last_name m.last_name

FROM employees e employees m

WHERE e.manager_id = m.`employee_id`;

-- 81.显示所有员工的姓名，部门号和部门名称。

SELECT last_name d.department_id department_name

FROM employees e departments d

WHERE e.`department_id` = d.`department_id`;

-- 82.查询90号部门员工的job_id和90号部门的location_id

SELECT job_id location_id

FROM employees e departments d

WHERE e.`department_id`=d.`department_id`

AND e.`department_id`=90;

-- 83.选择所有有奖金的员工的last_name department_name location_id city

SELECT last_name department_name l.location_id city

FROM employees e departments d locations l

WHERE e.department_id = d.department_id

AND d.location_id=l.location_id

AND e.commission_pct IS NOT NULL;

-- 84.选择city在Toronto工作的员工的last_name job_id department_id department_name

SELECT last_name job_id d.department_id department_name

FROM employees e departments d locations l

WHERE e.department_id = d.department_id

AND d.location_id=l.location_id

AND city = 'Toronto';

-- 85.查询每个工种、每个部门的部门名、工种名和最低工资

SELECT department_name job_title MIN(salary) 最低工资

FROM employees e departments d jobs j

WHERE e.`department_id`=d.`department_id`

AND e.`job_id`=j.`job_id`

GROUP BY department_name job_title;

-- 86.查询每个国家下的部门个数大于2的国家编号

SELECT country_id COUNT(*) 部门个数

FROM departments d locations l

WHERE d.`location_id`=l.`location_id`

GROUP BY country_id

HAVING 部门个数>2;

/*87.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式

employees Emp# manager Mgr#

kochhar 101 king 100

*/

SELECT e.last_name employees e.employee_id "Emp#" m.last_name manager m.employee_id "Mgr#"

FROM employees e employees m

WHERE e.manager_id = m.employee_id

AND e.last_name='kochhar';

-- 88.查询名字中包含e的员工名和工种名（添加筛选）

SELECT last_name job_title

FROM employees e

INNER JOIN jobs j

ON e.`job_id`= j.`job_id`

WHERE e.`last_name` LIKE '%e%';

-- 89.查询部门个数>3的城市名和部门个数

SELECT city COUNT(*) 部门个数

FROM departments d

INNER JOIN locations l

ON d.`location_id`=l.`location_id`

GROUP BY city

HAVING COUNT(*)>3;

-- 90.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序

SELECT COUNT(*) 个数 department_name

FROM employees e

INNER JOIN departments d

ON e.`department_id`=d.`department_id`

GROUP BY department_name

HAVING COUNT(*)>3

ORDER BY COUNT(*) DESC;

-- 91.查询员工名、部门名、工种名，并按部门名降序

SELECT last_name department_name job_title

FROM employees e

INNER JOIN departments d ON e.`department_id`=d.`department_id`

INNER JOIN jobs j ON e.`job_id` = j.`job_id`

ORDER BY department_name DESC;

-- 92.查询员工的工资级别

SELECT salary grade_level

FROM employees e INNER JOIN job_grades j

ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

-- 93.查询每个工资级别的个数>2的个数，并且按工资级别降序

SELECT salary grade_level COUNT(*) 个数

FROM employees e JOIN job_grades g

ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`

GROUP BY salary grade_level

HAVING COUNT(*)>2

ORDER BY grade_level DESC ;

-- 94.查询姓名中包含字符k员工的名字、上级的名字

SELECT e.last_name m.last_name

FROM employees e JOIN employees m

ON e.`manager_id` = m.`employee_id`

WHERE e.`last_name` LIKE '%k%';

-- 95.谁的工资比Abel高？

SELECT last_name salary

FROM employees

WHERE salary>(

SELECT salary

FROM employees

WHERE last_name = LOWER('abel')

);

-- 96.返回job_id 与141 号员工相同，salary 比143 号员工多的员工姓名，job_id 和工资

SELECT last_name job_id salary

FROM employees WHERE job_id=

(SELECT job_id FROM employees WHERE employee_id=141)

AND salary>(SELECT salary FROM employees WHERE employee_id = 143);

-- 97.返回公司工资最少的员工的last_name job_id 和salary

SELECT last_name job_id salary

FROM employees WHERE salary =

(SELECT MIN(salary) FROM employees);

-- 98.查询最低工资大于50 号部门最低工资的部门id 和其最低工资

SELECT department_id MIN(salary)

FROM employees

GROUP BY department_id

HAVING MIN(salary)>(SELECT MIN(salary) FROM employees WHERE department_id=50);

-- 99.返回location_id 是1400 或1700 的部门中的所有员工姓名

SELECT last_name

FROM employees

WHERE department_id IN (SELECT department_id FROM departments WHERE location_id IN(1400 1700));

/*100.

返回其它工种中比job_id 为‘IT_PROG’ 工种任一 工资低的员工的员

工号、姓名、job_id 以及salary

*/

SELECT employee_id last_name job_id salary

FROM employees WHERE salary < ANY (SELECT DISTINCT salary FROM employees WHERE job_id='IT_PROG' )

AND job_id !='IT_PROG';

SELECT employee_id last_name job_id salary

FROM employees

WHERE salary < ANY

(SELECT salary

FROM employees

WHERE job_id = 'IT_PROG')

AND job_id <> 'IT_PROG';

/*101.

返回其它工种中比job_id 为‘IT_PROG’ 工种 所有 工资都低的员工

的员工号、姓名、job_id 以及salary

*/

SELECT employee_id last_name job_id salary

FROM employees WHERE salary< ALL(SELECT salary FROM employees WHERE job_id='IT_PROG')

AND job_id <> 'IT_PROG';

-- 102.查询每个部门的员工的个数

SELECT d.department_id

(SELECT COUNT(*)

FROM employees e WHERE e.department_id = d.`department_id`) 个数

FROM departments d;

-- 103.查询和Zlotkey相同部门的员工姓名和工资

SELECT last_name salary

FROM employees

WHERE department_id = (SELECT department_id

FROM employees WHERE last_name='Zlotkey');

-- 104.查询工资比公司平均工资高的员工的员工号，姓名和工资。

SELECT employee_id last_name salary

FROM employees WHERE salary > (

SELECT AVG(salary) FROM employees

);

-- 105查询各部门中工资比本部门平均工资高的员工的员工号 姓名和工资

SELECT employee_id last_name e.salary

FROM employees e JOIN

(SELECT department_id AVG(salary) ag

FROM employees

GROUP BY department_id) dg ON

e.`department_id` = dg.department_id

WHERE e.`salary`>dg.ag;

-- 106.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

SELECT last_name employee_id

FROM employees

WHERE department_id IN(

SELECT DISTINCT department_id

FROM employees

WHERE last_name LIKE '%u%'

);

SELECT employee_id

FROM employees

WHERE department_id =ANY(

SELECT DISTINCT department_id

FROM departments

WHERE location_id = 1700

);

点赞收藏不迷路，更多精彩请关注#蝌蚪成长记#，敬请期待！