mysql的测试数据 mysql测试语句

转载

mob6454cc6bf0b7 2024-02-22 23:41:39

文章标签 mysql的测试数据 java 开发语言后端 IT 文章分类 MySQL 数据库

文章目录

一、子查询

1、查询和Zlotkey相同部门的员工姓名和工资
2、查询工资比公司平均工资高的员工的员工号，姓名和工资
3、选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary
4、查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
5、查询管理者是King的员工姓名和工资
6、查询平均工资最低的部门信息
7、查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
8、查询平均工资高于公司平均工资的部门有哪些
9、查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
10、查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
11、查询每个部门下的部门人数大于 5 的部门名称

二、学生表、课程表、教师表

1、查询“1”课程比“2”课程成绩高的所有学生的学号
2、查询平均成绩大于60分的同学的学号和平均成绩
3、查询所有同学的学号、姓名、选课数、总成绩
4、查询没学过“叶平”老师课的同学的学号、姓名
5、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名
6、查询学过“叶平”老师所教的所有课的同学的学号、姓名
7、查询所有课程成绩小于60分的同学的学号、姓名

一、子查询

准备数据

CREATE DATABASE /*!32312 IF NOT EXISTS*/`atguigudb` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `atguigudb`;

/*Table structure for table `countries` */

DROP TABLE IF EXISTS `countries`;

CREATE TABLE `countries` (
  `country_id` char(2) NOT NULL,
  `country_name` varchar(40) DEFAULT NULL,
  `region_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`country_id`),
  KEY `countr_reg_fk` (`region_id`),
  CONSTRAINT `countr_reg_fk` FOREIGN KEY (`region_id`) REFERENCES `regions` (`region_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `countries` */

insert  into `countries`(`country_id`,`country_name`,`region_id`) values ('AR','Argentina',2),('AU','Australia',3),('BE','Belgium',1),('BR','Brazil',2),('CA','Canada',2),('CH','Switzerland',1),('CN','China',3),('DE','Germany',1),('DK','Denmark',1),('EG','Egypt',4),('FR','France',1),('HK','HongKong',3),('IL','Israel',4),('IN','India',3),('IT','Italy',1),('JP','Japan',3),('KW','Kuwait',4),('MX','Mexico',2),('NG','Nigeria',4),('NL','Netherlands',1),('SG','Singapore',3),('UK','United Kingdom',1),('US','United States of America',2),('ZM','Zambia',4),('ZW','Zimbabwe',4);

/*Table structure for table `departments` */

DROP TABLE IF EXISTS `departments`;

CREATE TABLE `departments` (
  `department_id` int(4) NOT NULL DEFAULT '0',
  `department_name` varchar(30) NOT NULL,
  `manager_id` int(6) DEFAULT NULL,
  `location_id` int(4) DEFAULT NULL,
  PRIMARY KEY (`department_id`),
  UNIQUE KEY `dept_id_pk` (`department_id`),
  KEY `dept_loc_fk` (`location_id`),
  KEY `dept_mgr_fk` (`manager_id`),
  CONSTRAINT `dept_loc_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`),
  CONSTRAINT `dept_mgr_fk` FOREIGN KEY (`manager_id`) REFERENCES `employees` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `departments` */

insert  into `departments`(`department_id`,`department_name`,`manager_id`,`location_id`) values (10,'Administration',200,1700),(20,'Marketing',201,1800),(30,'Purchasing',114,1700),(40,'Human Resources',203,2400),(50,'Shipping',121,1500),(60,'IT',103,1400),(70,'Public Relations',204,2700),(80,'Sales',145,2500),(90,'Executive',100,1700),(100,'Finance',108,1700),(110,'Accounting',205,1700),(120,'Treasury',NULL,1700),(130,'Corporate Tax',NULL,1700),(140,'Control And Credit',NULL,1700),(150,'Shareholder Services',NULL,1700),(160,'Benefits',NULL,1700),(170,'Manufacturing',NULL,1700),(180,'Construction',NULL,1700),(190,'Contracting',NULL,1700),(200,'Operations',NULL,1700),(210,'IT Support',NULL,1700),(220,'NOC',NULL,1700),(230,'IT Helpdesk',NULL,1700),(240,'Government Sales',NULL,1700),(250,'Retail Sales',NULL,1700),(260,'Recruiting',NULL,1700),(270,'Payroll',NULL,1700);

/*Table structure for table `employees` */

DROP TABLE IF EXISTS `employees`;

CREATE TABLE `employees` (
  `employee_id` int(6) NOT NULL DEFAULT '0',
  `first_name` varchar(20) DEFAULT NULL,
  `last_name` varchar(25) NOT NULL,
  `email` varchar(25) NOT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `hire_date` date NOT NULL,
  `job_id` varchar(10) NOT NULL,
  `salary` double(8,2) DEFAULT NULL,
  `commission_pct` double(2,2) DEFAULT NULL,
  `manager_id` int(6) DEFAULT NULL,
  `department_id` int(4) DEFAULT NULL,
  PRIMARY KEY (`employee_id`),
  UNIQUE KEY `emp_email_uk` (`email`),
  UNIQUE KEY `emp_emp_id_pk` (`employee_id`),
  KEY `emp_dept_fk` (`department_id`),
  KEY `emp_job_fk` (`job_id`),
  KEY `emp_manager_fk` (`manager_id`),
  CONSTRAINT `emp_dept_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
  CONSTRAINT `emp_job_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`),
  CONSTRAINT `emp_manager_fk` FOREIGN KEY (`manager_id`) REFERENCES `employees` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `employees` */

insert  into `employees`(`employee_id`,`first_name`,`last_name`,`email`,`phone_number`,`hire_date`,`job_id`,`salary`,`commission_pct`,`manager_id`,`department_id`) values (100,'Steven','King','SKING','515.123.4567','1987-06-17','AD_PRES',24000.00,NULL,NULL,90),(101,'Neena','Kochhar','NKOCHHAR','515.123.4568','1989-09-21','AD_VP',17000.00,NULL,100,90),(102,'Lex','De Haan','LDEHAAN','515.123.4569','1993-01-13','AD_VP',17000.00,NULL,100,90),(103,'Alexander','Hunold','AHUNOLD','590.423.4567','1990-01-03','IT_PROG',9000.00,NULL,102,60),(104,'Bruce','Ernst','BERNST','590.423.4568','1991-05-21','IT_PROG',6000.00,NULL,103,60),(105,'David','Austin','DAUSTIN','590.423.4569','1997-06-25','IT_PROG',4800.00,NULL,103,60),(106,'Valli','Pataballa','VPATABAL','590.423.4560','1998-02-05','IT_PROG',4800.00,NULL,103,60),(107,'Diana','Lorentz','DLORENTZ','590.423.5567','1999-02-07','IT_PROG',4200.00,NULL,103,60),(108,'Nancy','Greenberg','NGREENBE','515.124.4569','1994-08-17','FI_MGR',12000.00,NULL,101,100),(109,'Daniel','Faviet','DFAVIET','515.124.4169','1994-08-16','FI_ACCOUNT',9000.00,NULL,108,100),(110,'John','Chen','JCHEN','515.124.4269','1997-09-28','FI_ACCOUNT',8200.00,NULL,108,100),(111,'Ismael','Sciarra','ISCIARRA','515.124.4369','1997-09-30','FI_ACCOUNT',7700.00,NULL,108,100),(112,'Jose Manuel','Urman','JMURMAN','515.124.4469','1998-03-07','FI_ACCOUNT',7800.00,NULL,108,100),(113,'Luis','Popp','LPOPP','515.124.4567','1999-12-07','FI_ACCOUNT',6900.00,NULL,108,100),(114,'Den','Raphaely','DRAPHEAL','515.127.4561','1994-12-07','PU_MAN',11000.00,NULL,100,30),(115,'Alexander','Khoo','AKHOO','515.127.4562','1995-05-18','PU_CLERK',3100.00,NULL,114,30),(116,'Shelli','Baida','SBAIDA','515.127.4563','1997-12-24','PU_CLERK',2900.00,NULL,114,30),(117,'Sigal','Tobias','STOBIAS','515.127.4564','1997-07-24','PU_CLERK',2800.00,NULL,114,30),(118,'Guy','Himuro','GHIMURO','515.127.4565','1998-11-15','PU_CLERK',2600.00,NULL,114,30),(119,'Karen','Colmenares','KCOLMENA','515.127.4566','1999-08-10','PU_CLERK',2500.00,NULL,114,30),(120,'Matthew','Weiss','MWEISS','650.123.1234','1996-07-18','ST_MAN',8000.00,NULL,100,50),(121,'Adam','Fripp','AFRIPP','650.123.2234','1997-04-10','ST_MAN',8200.00,NULL,100,50),(122,'Payam','Kaufling','PKAUFLIN','650.123.3234','1995-05-01','ST_MAN',7900.00,NULL,100,50),(123,'Shanta','Vollman','SVOLLMAN','650.123.4234','1997-10-10','ST_MAN',6500.00,NULL,100,50),(124,'Kevin','Mourgos','KMOURGOS','650.123.5234','1999-11-16','ST_MAN',5800.00,NULL,100,50),(125,'Julia','Nayer','JNAYER','650.124.1214','1997-07-16','ST_CLERK',3200.00,NULL,120,50),(126,'Irene','Mikkilineni','IMIKKILI','650.124.1224','1998-09-28','ST_CLERK',2700.00,NULL,120,50),(127,'James','Landry','JLANDRY','650.124.1334','1999-01-14','ST_CLERK',2400.00,NULL,120,50),(128,'Steven','Markle','SMARKLE','650.124.1434','2000-03-08','ST_CLERK',2200.00,NULL,120,50),(129,'Laura','Bissot','LBISSOT','650.124.5234','1997-08-20','ST_CLERK',3300.00,NULL,121,50),(130,'Mozhe','Atkinson','MATKINSO','650.124.6234','1997-10-30','ST_CLERK',2800.00,NULL,121,50),(131,'James','Marlow','JAMRLOW','650.124.7234','1997-02-16','ST_CLERK',2500.00,NULL,121,50),(132,'TJ','Olson','TJOLSON','650.124.8234','1999-04-10','ST_CLERK',2100.00,NULL,121,50),(133,'Jason','Mallin','JMALLIN','650.127.1934','1996-06-14','ST_CLERK',3300.00,NULL,122,50),(134,'Michael','Rogers','MROGERS','650.127.1834','1998-08-26','ST_CLERK',2900.00,NULL,122,50),(135,'Ki','Gee','KGEE','650.127.1734','1999-12-12','ST_CLERK',2400.00,NULL,122,50),(136,'Hazel','Philtanker','HPHILTAN','650.127.1634','2000-02-06','ST_CLERK',2200.00,NULL,122,50),(137,'Renske','Ladwig','RLADWIG','650.121.1234','1995-07-14','ST_CLERK',3600.00,NULL,123,50),(138,'Stephen','Stiles','SSTILES','650.121.2034','1997-10-26','ST_CLERK',3200.00,NULL,123,50),(139,'John','Seo','JSEO','650.121.2019','1998-02-12','ST_CLERK',2700.00,NULL,123,50),(140,'Joshua','Patel','JPATEL','650.121.1834','1998-04-06','ST_CLERK',2500.00,NULL,123,50),(141,'Trenna','Rajs','TRAJS','650.121.8009','1995-10-17','ST_CLERK',3500.00,NULL,124,50),(142,'Curtis','Davies','CDAVIES','650.121.2994','1997-01-29','ST_CLERK',3100.00,NULL,124,50),(143,'Randall','Matos','RMATOS','650.121.2874','1998-03-15','ST_CLERK',2600.00,NULL,124,50),(144,'Peter','Vargas','PVARGAS','650.121.2004','1998-07-09','ST_CLERK',2500.00,NULL,124,50),(145,'John','Russell','JRUSSEL','011.44.1344.429268','1996-10-01','SA_MAN',14000.00,0.40,100,80),(146,'Karen','Partners','KPARTNER','011.44.1344.467268','1997-01-05','SA_MAN',13500.00,0.30,100,80),(147,'Alberto','Errazuriz','AERRAZUR','011.44.1344.429278','1997-03-10','SA_MAN',12000.00,0.30,100,80),(148,'Gerald','Cambrault','GCAMBRAU','011.44.1344.619268','1999-10-15','SA_MAN',11000.00,0.30,100,80),(149,'Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018','2000-01-29','SA_MAN',10500.00,0.20,100,80),(150,'Peter','Tucker','PTUCKER','011.44.1344.129268','1997-01-30','SA_REP',10000.00,0.30,145,80),(151,'David','Bernstein','DBERNSTE','011.44.1344.345268','1997-03-24','SA_REP',9500.00,0.25,145,80),(152,'Peter','Hall','PHALL','011.44.1344.478968','1997-08-20','SA_REP',9000.00,0.25,145,80),(153,'Christopher','Olsen','COLSEN','011.44.1344.498718','1998-03-30','SA_REP',8000.00,0.20,145,80),(154,'Nanette','Cambrault','NCAMBRAU','011.44.1344.987668','1998-12-09','SA_REP',7500.00,0.20,145,80),(155,'Oliver','Tuvault','OTUVAULT','011.44.1344.486508','1999-11-23','SA_REP',7000.00,0.15,145,80),(156,'Janette','King','JKING','011.44.1345.429268','1996-01-30','SA_REP',10000.00,0.35,146,80),(157,'Patrick','Sully','PSULLY','011.44.1345.929268','1996-03-04','SA_REP',9500.00,0.35,146,80),(158,'Allan','McEwen','AMCEWEN','011.44.1345.829268','1996-08-01','SA_REP',9000.00,0.35,146,80),(159,'Lindsey','Smith','LSMITH','011.44.1345.729268','1997-03-10','SA_REP',8000.00,0.30,146,80),(160,'Louise','Doran','LDORAN','011.44.1345.629268','1997-12-15','SA_REP',7500.00,0.30,146,80),(161,'Sarath','Sewall','SSEWALL','011.44.1345.529268','1998-11-03','SA_REP',7000.00,0.25,146,80),(162,'Clara','Vishney','CVISHNEY','011.44.1346.129268','1997-11-11','SA_REP',10500.00,0.25,147,80),(163,'Danielle','Greene','DGREENE','011.44.1346.229268','1999-03-19','SA_REP',9500.00,0.15,147,80),(164,'Mattea','Marvins','MMARVINS','011.44.1346.329268','2000-01-24','SA_REP',7200.00,0.10,147,80),(165,'David','Lee','DLEE','011.44.1346.529268','2000-02-23','SA_REP',6800.00,0.10,147,80),(166,'Sundar','Ande','SANDE','011.44.1346.629268','2000-03-24','SA_REP',6400.00,0.10,147,80),(167,'Amit','Banda','ABANDA','011.44.1346.729268','2000-04-21','SA_REP',6200.00,0.10,147,80),(168,'Lisa','Ozer','LOZER','011.44.1343.929268','1997-03-11','SA_REP',11500.00,0.25,148,80),(169,'Harrison','Bloom','HBLOOM','011.44.1343.829268','1998-03-23','SA_REP',10000.00,0.20,148,80),(170,'Tayler','Fox','TFOX','011.44.1343.729268','1998-01-24','SA_REP',9600.00,0.20,148,80),(171,'William','Smith','WSMITH','011.44.1343.629268','1999-02-23','SA_REP',7400.00,0.15,148,80),(172,'Elizabeth','Bates','EBATES','011.44.1343.529268','1999-03-24','SA_REP',7300.00,0.15,148,80),(173,'Sundita','Kumar','SKUMAR','011.44.1343.329268','2000-04-21','SA_REP',6100.00,0.10,148,80),(174,'Ellen','Abel','EABEL','011.44.1644.429267','1996-05-11','SA_REP',11000.00,0.30,149,80),(175,'Alyssa','Hutton','AHUTTON','011.44.1644.429266','1997-03-19','SA_REP',8800.00,0.25,149,80),(176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265','1998-03-24','SA_REP',8600.00,0.20,149,80),(177,'Jack','Livingston','JLIVINGS','011.44.1644.429264','1998-04-23','SA_REP',8400.00,0.20,149,80),(178,'Kimberely','Grant','KGRANT','011.44.1644.429263','1999-05-24','SA_REP',7000.00,0.15,149,NULL),(179,'Charles','Johnson','CJOHNSON','011.44.1644.429262','2000-01-04','SA_REP',6200.00,0.10,149,80),(180,'Winston','Taylor','WTAYLOR','650.507.9876','1998-01-24','SH_CLERK',3200.00,NULL,120,50),(181,'Jean','Fleaur','JFLEAUR','650.507.9877','1998-02-23','SH_CLERK',3100.00,NULL,120,50),(182,'Martha','Sullivan','MSULLIVA','650.507.9878','1999-06-21','SH_CLERK',2500.00,NULL,120,50),(183,'Girard','Geoni','GGEONI','650.507.9879','2000-02-03','SH_CLERK',2800.00,NULL,120,50),(184,'Nandita','Sarchand','NSARCHAN','650.509.1876','1996-01-27','SH_CLERK',4200.00,NULL,121,50),(185,'Alexis','Bull','ABULL','650.509.2876','1997-02-20','SH_CLERK',4100.00,NULL,121,50),(186,'Julia','Dellinger','JDELLING','650.509.3876','1998-06-24','SH_CLERK',3400.00,NULL,121,50),(187,'Anthony','Cabrio','ACABRIO','650.509.4876','1999-02-07','SH_CLERK',3000.00,NULL,121,50),(188,'Kelly','Chung','KCHUNG','650.505.1876','1997-06-14','SH_CLERK',3800.00,NULL,122,50),(189,'Jennifer','Dilly','JDILLY','650.505.2876','1997-08-13','SH_CLERK',3600.00,NULL,122,50),(190,'Timothy','Gates','TGATES','650.505.3876','1998-07-11','SH_CLERK',2900.00,NULL,122,50),(191,'Randall','Perkins','RPERKINS','650.505.4876','1999-12-19','SH_CLERK',2500.00,NULL,122,50),(192,'Sarah','Bell','SBELL','650.501.1876','1996-02-04','SH_CLERK',4000.00,NULL,123,50),(193,'Britney','Everett','BEVERETT','650.501.2876','1997-03-03','SH_CLERK',3900.00,NULL,123,50),(194,'Samuel','McCain','SMCCAIN','650.501.3876','1998-07-01','SH_CLERK',3200.00,NULL,123,50),(195,'Vance','Jones','VJONES','650.501.4876','1999-03-17','SH_CLERK',2800.00,NULL,123,50),(196,'Alana','Walsh','AWALSH','650.507.9811','1998-04-24','SH_CLERK',3100.00,NULL,124,50),(197,'Kevin','Feeney','KFEENEY','650.507.9822','1998-05-23','SH_CLERK',3000.00,NULL,124,50),(198,'Donald','OConnell','DOCONNEL','650.507.9833','1999-06-21','SH_CLERK',2600.00,NULL,124,50),(199,'Douglas','Grant','DGRANT','650.507.9844','2000-01-13','SH_CLERK',2600.00,NULL,124,50),(200,'Jennifer','Whalen','JWHALEN','515.123.4444','1987-09-17','AD_ASST',4400.00,NULL,101,10),(201,'Michael','Hartstein','MHARTSTE','515.123.5555','1996-02-17','MK_MAN',13000.00,NULL,100,20),(202,'Pat','Fay','PFAY','603.123.6666','1997-08-17','MK_REP',6000.00,NULL,201,20),(203,'Susan','Mavris','SMAVRIS','515.123.7777','1994-06-07','HR_REP',6500.00,NULL,101,40),(204,'Hermann','Baer','HBAER','515.123.8888','1994-06-07','PR_REP',10000.00,NULL,101,70),(205,'Shelley','Higgins','SHIGGINS','515.123.8080','1994-06-07','AC_MGR',12000.00,NULL,101,110),(206,'William','Gietz','WGIETZ','515.123.8181','1994-06-07','AC_ACCOUNT',8300.00,NULL,205,110);

/*Table structure for table `job_grades` */

DROP TABLE IF EXISTS `job_grades`;

CREATE TABLE `job_grades` (
  `grade_level` varchar(3) DEFAULT NULL,
  `lowest_sal` int(11) DEFAULT NULL,
  `highest_sal` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `job_grades` */

insert  into `job_grades`(`grade_level`,`lowest_sal`,`highest_sal`) values ('A',1000,2999),('B',3000,5999),('C',6000,9999),('D',10000,14999),('E',15000,24999),('F',25000,40000);

/*Table structure for table `job_history` */

DROP TABLE IF EXISTS `job_history`;

CREATE TABLE `job_history` (
  `employee_id` int(6) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `job_id` varchar(10) NOT NULL,
  `department_id` int(4) DEFAULT NULL,
  PRIMARY KEY (`employee_id`,`start_date`),
  UNIQUE KEY `jhist_emp_id_st_date_pk` (`employee_id`,`start_date`),
  KEY `jhist_job_fk` (`job_id`),
  KEY `jhist_dept_fk` (`department_id`),
  CONSTRAINT `jhist_dept_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
  CONSTRAINT `jhist_emp_fk` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`),
  CONSTRAINT `jhist_job_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `job_history` */

insert  into `job_history`(`employee_id`,`start_date`,`end_date`,`job_id`,`department_id`) values (101,'1989-09-21','1993-10-27','AC_ACCOUNT',110),(101,'1993-10-28','1997-03-15','AC_MGR',110),(102,'1993-01-13','1998-07-24','IT_PROG',60),(114,'1998-03-24','1999-12-31','ST_CLERK',50),(122,'1999-01-01','1999-12-31','ST_CLERK',50),(176,'1998-03-24','1998-12-31','SA_REP',80),(176,'1999-01-01','1999-12-31','SA_MAN',80),(200,'1987-09-17','1993-06-17','AD_ASST',90),(200,'1994-07-01','1998-12-31','AC_ACCOUNT',90),(201,'1996-02-17','1999-12-19','MK_REP',20);

/*Table structure for table `jobs` */

DROP TABLE IF EXISTS `jobs`;

CREATE TABLE `jobs` (
  `job_id` varchar(10) NOT NULL DEFAULT '',
  `job_title` varchar(35) NOT NULL,
  `min_salary` int(6) DEFAULT NULL,
  `max_salary` int(6) DEFAULT NULL,
  PRIMARY KEY (`job_id`),
  UNIQUE KEY `job_id_pk` (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*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(4) NOT NULL DEFAULT '0',
  `street_address` varchar(40) DEFAULT NULL,
  `postal_code` varchar(12) DEFAULT NULL,
  `city` varchar(30) NOT NULL,
  `state_province` varchar(25) DEFAULT NULL,
  `country_id` char(2) DEFAULT NULL,
  PRIMARY KEY (`location_id`),
  UNIQUE KEY `loc_id_pk` (`location_id`),
  KEY `loc_c_id_fk` (`country_id`),
  CONSTRAINT `loc_c_id_fk` FOREIGN KEY (`country_id`) REFERENCES `countries` (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*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');

1、查询和Zlotkey相同部门的员工姓名和工资

SELECT last_name, salary 
FROM employees 
WHERE department_id = ( 
	SELECT department_id 
	FROM employees 
	WHERE last_name = 'Zlotkey' 
	)

2、查询工资比公司平均工资高的员工的员工号，姓名和工资

select e.employee_id ,e.last_name ,e.salary
from employees e
where e.salary > (
	select avg(e2.salary) 
	from employees e2
	)

3、选择工资大于所有JOB_ID = 'SA_MAN’的员工的工资的员工的last_name, job_id, salary

方法一：

select last_name, job_id, salary 
from employees e where e.salary > (
	select max(salary) 
	from employees e2 
	where e2.job_id = 'SA_MAN'
	)

方法二：all方式

select last_name, job_id, salary 
from employees e 
where e.salary > all (
	select e2.salary 
	from employees e2 
	where e2.job_id = 'SA_MAN'
	)

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

方法一：in方式

select e.employee_id ,e.last_name 
from employees e 
where e.department_id in (
	select e2.department_id 
	from employees e2 
	where e2.last_name like '%u%'
	)

方法二：any方法

SELECT employee_id, last_name 
FROM employees 
WHERE department_id = ANY( 
	SELECT DISTINCT department_id 
	FROM employees 
	WHERE last_name 
	LIKE '%u%' 
	)

5、查询管理者是King的员工姓名和工资

方式一：自连接

select e1.last_name ,e1.salary 
from employees e1 , employees e2
where e1.manager_id = e2.employee_id 
and e2.last_name = 'King'

方式二：in方式

select e1.last_name ,e1.salary 
from employees e1 
where e1.manager_id in (
	select e.employee_id 
	from employees e 
	where e.last_name = 'King'
	)

方式二：exists方式

select e1.last_name ,e1.salary 
from employees e1 
where exists (
	select 1 
	from employees e 
	where e.last_name = 'King' 
	and e1.manager_id = e.employee_id 
	)

6、查询平均工资最低的部门信息

方式一：

# 1.先查询部门平均工资最低的工资
# 2.根据部门分组，having = 最低工资
select d.* 
from employees e2,departments d 
where e2.department_id = d.department_id 
group by e2.department_id 
having avg(e2.salary) =  (
	select min(t1.avg_salary)
	from ( 
		select avg(e.salary) avg_salary 
		from employees e 
		group by e.department_id
		) t1
	)

方式二：all方式

select d.* 
from employees e2,departments d 
where e2.department_id = d.department_id 
group by e2.department_id 
having avg(e2.salary) <= all(
		select avg(e.salary) avg_salary 
		from employees e 
		group by e.department_id
	)

方式三：limit方式

select d.* 
from employees e2,departments d 
where e2.department_id = d.department_id 
group by e2.department_id 
having avg(e2.salary) = (
		select avg(e.salary) avg_salary 
		from employees e 
		group by e.department_id
		order by avg_salary asc
		limit 1
	)

方式四：临时表方式

select d.* 
from departments d ,(
		select e.department_id,avg(e.salary) avg_salary 
		from employees e 
		group by e.department_id
		order by avg_salary asc
		limit 1
	) t where d.department_id = t.department_id

7、查询平均工资最低的部门信息和该部门的平均工资（相关子查询）

方式一：

select d.*,(select avg(salary) from employees e2 where e2.department_id = d.department_id) 
from employees e2,departments d 
where e2.department_id = d.department_id 
group by e2.department_id 
having avg(e2.salary) =  (
	select min(t1.avg_salary)
	from ( 
		select avg(e.salary) avg_salary 
		from employees e 
		group by e.department_id
		) t1
	)

方式二：

select d.*,t.avg_salary 
from departments d ,(
		select e.department_id,avg(e.salary) avg_salary 
		from employees e 
		group by e.department_id
		order by avg_salary asc
		limit 1
	) t where d.department_id = t.department_id

8、查询平均工资高于公司平均工资的部门有哪些

select e.department_id  
from employees e 
where e.department_id  is not null
group by e.department_id 
having avg(e.salary) > (
	select avg(salary) from employees
	)

9、查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

select last_name, department_id, email, salary 
from employees e 
where e.employee_id = (
	select manager_id 
	from departments d 
	where d.department_id = (
		select department_id
		from employees 
		group by department_id 
		order by avg(salary) desc
		limit 1
		)
	)

10、查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资

方式一：

SELECT employee_id,last_name,salary 
FROM employees e1 
WHERE salary > ( 
	SELECT AVG(salary) 
	FROM employees e2 
	WHERE e2.department_id = e1.department_id
	)

方式二：

select e.employee_id ,e.last_name ,e.salary 
from employees e,
(select department_id,avg(salary) avg_salary from employees group by department_id) t
where e.department_id = t.department_id
and e.salary > t.avg_salary

11、查询每个部门下的部门人数大于 5 的部门名称

方式一：

select d.department_name,d.department_id 
from departments d 
where d.department_id in (
	select e.department_id 
	from employees e
	group by e.department_id 
	having count(*) > 5 
	)

方式二：

SELECT department_name,department_id 
FROM departments d 
WHERE 5 < ( 
	SELECT COUNT(*) 
	FROM employees e 
	WHERE d.department_id = e.department_id
	)

二、学生表、课程表、教师表

准备数据

--创建学生信息表
CREATE TABLE Student(
Sid int,
Sname VARCHAR(10),
Sage int,
Ssex VARCHAR(10)
);

--课程表
CREATE TABLE Course(
CId VARCHAR(10),
Cname VARCHAR(10),
TId VARCHAR(10)
);

--教师表
CREATE TABLE Teacher(
Tid VARCHAR(10),
Tname VARCHAR(10)
);

--成绩表
CREATE TABLE SC(
sid VARCHAR(10),
cid VARCHAR(10),
score DECIMAL(18,1)
);

insert into Student select 1,'刘一',18,'男' union all
 select 2,'钱二',19,'女' union all
 select 3,'张三',17,'男' union all
 select 4,'李四',18,'女' union all
 select 5,'王五',17,'男' union all
 select 6,'赵六',19,'女';
 
 insert into Teacher select 1,N'叶平' union all
 select 2,N'贺高' union all
 select 3,N'杨艳' union all
 select 4,N'周磊';
 
 insert into Course select 1,N'语文',1 union all
 select 2,N'数学',2 union all
 select 3,N'英语',3 union all
 select 4,N'物理',4;
 
 insert into SC 
 select 1,1,56 union all 
 select 1,2,78 union all 
 select 1,3,67 union all 
 select 1,4,58 union all 
 select 2,1,79 union all 
 select 2,2,81 union all 
 select 2,3,92 union all 
 select 2,4,68 union all 
 select 3,1,91 union all 
 select 3,2,47 union all 
 select 3,3,88 union all 
 select 3,4,56 union all 
 select 4,2,88 union all 
 select 4,3,90 union all 
 select 4,4,93 union all 
 select 5,1,46 union all 
 select 5,3,78 union all 
 select 5,4,53 union all 
 select 6,1,35 union all 
 select 6,2,68 union all 
 select 6,4,71;

1、查询“1”课程比“2”课程成绩高的所有学生的学号

方式一：

select a.sid from sc a , sc b 
where a.sid = b.sid 
and a.score > b.score 
and a.cid = '1'
and b.cid = '2'

方式二：

select a.sid from 
(select * from sc where cid = '1') a,
(select * from sc where cid = '2') b
where a.sid = b.sid 
and a.score > b.score

2、查询平均成绩大于60分的同学的学号和平均成绩

select sid, avg(score) 
from sc 
group by sid 
having avg(score) > 60

3、查询所有同学的学号、姓名、选课数、总成绩

select s.Sid,s.Sname,count(sc.cid),sum(sc.score) 
from student s left join sc sc
on s.Sid = sc.sid 
group by s.Sid,s.Sname

4、查询没学过“叶平”老师课的同学的学号、姓名

# 1.先查询学过的学生的id
# 2.根据not in获取没学过的
select s.Sid,s.Sname 
from student s 
where s.Sid not in (
	select Sid 
	from sc 
	where cid 
	in (
		select c.CId 
		from teacher t ,course c 
		where t.Tid = c.TId 
		and t.Tname = '叶平'
		)
)

5、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名

方式一：

select s.Sid,s.Sname 
from sc a,student s 
where a.cid = '1' 
and a.sid = s.Sid 
and exists (
	select 1 
	from sc b 
	where b.cid = '2' 
	and a.sid = b.sid 
)

方式二：

select s.Sid ,s.Sname 
from sc a ,sc b,student s 
where a.sid = b.sid 
and a.sid = s.Sid 
and a.cid = '1' 
and b.cid = '2'

6、查询学过“叶平”老师所教的所有课的同学的学号、姓名

# 1.查询叶平老师教的学生信息
# 2.根据学生分组查询每个人学过的数量
# 3.每个人数量 = 老师所教所有学科的数量
select s.Sid,s.Sname 
from student s 
where s.Sid in (	
	select s2.sid 
	from course c ,teacher t ,sc s2 
	where c.TId = t.Tid 
	and s2.cid = c.CId 
	and t.Tname = '叶平'
	group by s2.sid
	having count(s2.cid) = (
		select count(*) 
		from course c2,teacher t2 
		where c2.TId = t2.Tid 
		and t2.Tname = '叶平'
		)
	)

7、查询所有课程成绩小于60分的同学的学号、姓名

# 1.查询所有成绩大于60的学习id
# 2.not in就是所有成绩小于60的
select s.Sid,s.Sname 
from student s 
where s.Sid not in (
	select sid 
	from sc 
	where score > 60
	)

…未完待续

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：docker ubuntu 图像界面可视化 docker安装ubuntu镜像图形界面

下一篇：DBA怎么提升PYTHON开发能力 python dblquad

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

mysql的测试数据 mysql测试语句

mysql的测试数据 mysql测试语句

文章目录

一、子查询

1、查询和Zlotkey相同部门的员工姓名和工资

2、查询工资比公司平均工资高的员工的员工号，姓名和工资

3、选择工资大于所有JOB_ID = 'SA_MAN’的员工的工资的员工的last_name, job_id, salary

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

5、查询管理者是King的员工姓名和工资

6、查询平均工资最低的部门信息

7、查询平均工资最低的部门信息和该部门的平均工资（相关子查询）

8、查询平均工资高于公司平均工资的部门有哪些

9、 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

10、查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资

11、查询每个部门下的部门人数大于 5 的部门名称

二、学生表、课程表、教师表

1、查询“1”课程比“2”课程成绩高的所有学生的学号

2、查询平均成绩大于60分的同学的学号和平均成绩

3、查询所有同学的学号、姓名、选课数、总成绩

4、查询没学过“叶平”老师课的同学的学号、姓名

5、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名

6、查询学过“叶平”老师所教的所有课的同学的学号、姓名

7、查询所有课程成绩小于60分的同学的学号、姓名

51CTO博客

9、查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary