目录
#4,计算 employees 表中的员工全年薪水加 100 以后的薪水是多少，并显示他们的员工ID与名字。
#5,计算 employees 表中的员工薪水加 100 以后的全年薪水是多少，并显示他们的员工ID与名字。
#7,查询 employees 表将雇员 last_name 列定义别名为 name。
#8,查询 employees 表为表定义别名为emp，将雇员 last_name 列定义别名为 name。
#10,查询 departments 表中部门 ID 为 90 的部门名称与工作地点 ID。
#11,查询 employees 表中员工薪水大于等于 3000 的员工的姓名与薪水。
#12,查询 employees 表中员工薪水不等于 5000 的员工的姓名与薪水。
#13,查询 employees 表，薪水在 3000-8000 之间的雇员ID、名字与薪水。
#14,查询 employees 表，找出薪水是 5000,6000,8000 的雇员ID、名字与薪水。
#15,查询 employees 中雇员名字第二个字母是 e 的雇员名字。
#16,找出 emloyees 表中那些没有佣金的雇员雇员ID、名字与佣金。
#17,找出 employees 表中那些有佣金的雇员ID、名字与佣金。
# 18,查询 employees 表中雇员薪水是 8000 的并且名字中含有e 的雇员 名字与薪水。
#19,查询 employees 表中雇员薪水是 8000 的或者名字中含有e 的雇员名字与薪水。
#20,查询 employees 表中雇员名字中不包含 u 的雇员的名字。
#21,查询 employees 表中的所有雇员，显示他们的ID、名字与薪水，并按薪水升序排序。
#22,查询 employees 表中的所有雇员，显示他们的ID与名字，并按雇员名字降序排序。
#23,显示雇员ID，名字。计算雇员的年薪，年薪列别名为annsal，并对该列进行升序排序，
#24,以升叙排序显示 DEPARTMENT_ID 列，同时以降序排序显示SALARY 列。
#25,创建一个查询，显示收入超过 12,000 的雇员的名字和薪水。
#26,创建一个查询，显示雇员号为 176 的雇员的名字和部门号。
#27,.显示所有薪水不在 5000 和 12000 之间的雇员的名字和薪水。
#28,显示所有在部门 20 和 50 中的雇员的名字和部门号，并以名字按字母顺序排序。
#29,.列出收入在 5,000 和 12,000 之间，并且在部门 20 或50 工作的雇员的名字和薪水。将列标题分别显示为 Employee 和 MonthlySalary
#31,显示所有有佣金的雇员的名字、薪水和佣金。以薪水和佣金的降序排序数据。
#32,显示所有名字中有一个 a 和一个 e 的雇员的名字。
#33,显示所有工作岗位是销售代表（SA_REP）或者普通职员(ST_CLERK)，并且薪水不等于 2,500、3,500 或 7,000 的雇员的名字、工作岗位和薪水。
#34,显示雇员 Davies [名称要last_name] 的雇员号、姓名和部门号，将姓名转换为大写。
#35,显示所有工作岗位名称从第 4 个字符位置开始，包含字符串REP的雇员的ID信息，将雇员的姓和名连接显示在一起，还显示雇员名的的长度，以及名字中字母 a 的位置。
#36,所有job_id是SA_REP的雇员的名字，薪水以及薪水被5000除后的余数。
#37,显示所有在部门 90 中的雇员的名字和从业的周数。雇员的总工作时间以周计算，用当前日期 (SYSDATE) 减去雇员的受顾日期，再除以 7。
# 38,查询 employees 表中雇员名字为 King 的雇员的入职日期，要求显示格式为 yyyy 年 MM 月 dd 日。
#39,查询部门编号是50或者80的员工信息，包含他们的名字、薪水、佣金。在income列中，如果有佣金则显示‘SAL+COMM’，无佣金则显示'SAL'。
#40,计算雇员的年报酬，你需要用 12 乘以月薪，再加上它的佣金 (等于年薪乘以佣金
#41,查询员工表，显示他们的名字、名字的长度该列名为expr1，姓氏、姓氏的长度该列名为expr2。在result列中，如果名字与姓氏的长度相同则显示空，如果不相同则显示名字长度。
#44,显示受雇日期在 1998 年 2 月 20 日 和 2005 年 5 月 1 日 之间的雇员的名字、岗位和受雇日期。按受雇日期顺序排序查询结果。
#45,显示每一个在 2002 年受雇的雇员的名字和受雇日期。
#46,对每一个雇员，显示 employee number、last_name、salary 和salary 增加 15%，并且表示成整数，列标签显示为 New Salary。
#47，写一个查询，显示名字的长度，对所有名字开始字母是 J、A 或 M的雇员。用雇员的 lastname排序结果。
#48,创建一个查询显示所有雇员的 last name 和 salary。将薪水格式化为 15 个字符长度，用 $左填充 。
#50,创建一个查询显示雇员的 last names 和 commission (佣金) 比率。如果雇员没有佣金，显示 “No Commission”，列标签COMM。
#53, 显示每个雇员的 last name、departmentname 和 city。
#54,/*创建 job_grades 表，包含 lowest_sal ，highest_sal ，grade_level。
插入数据1000 2999 A2000 4999 B5000 7999 C8000 12000 D
#55,查询每个雇员的经理的名字以及雇员的名字，雇员名字列别名为W，经理列别名为M。
#57,使用交叉连接查询 employees 表与 departments 表。-- 组成一个笛卡尔乘积表
#58,使用自然连接查询所有有部门的雇员的名字以及部门名称。
#59,查询雇员名字为 Fox 的雇员 ID ，薪水与部门名称。
#60,查询所有雇员的名字以及他们的部门名称，包含那些没有部门的雇员。
#61,查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门。
#62,查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门以及没有部门的雇 员。
#63,写一个查询显示所有雇员的 last name、department id、and department name。
#64,创建一个在部门 80 中的所有工作岗位的唯一列表，在输出中包括部门的地点。
#65,写一个查询显示所有有佣金的雇员的 last name、department name、location ID 和城市
#66,显示所有在其 last names 中有一个小写 a 的雇员的 last name 和department name。
#67,用sql99的内连接写一个查询显示那些工作在 Toronto 的所有雇员的 last name、job、department number 和 departmentname。
#69,计算员工表中工作编号含有REP的工作岗位的平均薪水与薪水总和。
#70,查询员工表中入职时间最短与最长的员工，并显示他们的入职时间。
#73,在组函数中使用 IFNULL 函数 统计部门中有佣金的平均值
#76,找到每个部门中的最高薪水，而且只显示最高薪水大于 $10,000 的那些部门
#77,显示那些合计薪水超过 13,000 的每个工作岗位的合计薪水。排除那些JOB_ID中含有REP的工作岗位，并且用合计月薪排序列表。
#78,显示所有雇员的最高、最低、合计和平均薪水，列标签分别为：Max、Min、Sum 和 Avg。四舍五入结果为最近的整数。
#80,确定经理人数，不需要列出他们，列标签是 Number of Managers。提示：用MANAGER_ID列决定经理号。
#82,显示经理号和经理付给雇员的最低薪水。排除那些经理未知的人。排除最低薪水小于等于 $6,000 的组。按薪水降序排序输出。
#83,写一个查询显示每个部门的名字、地点、人数和部门中所有雇员的平均薪水。四舍五入薪水到两位小数。
#84,查询与Fox同一部门的同事，并显示他们的名字与部门ID。
#86,查找各部门收入为部门最低的那些雇员。显示他们的名字，薪水以及部门 ID。
#87,写一个查询显示与 Zlotkey 在同一部门的雇员的 last name 和hire date，结果中不包括
#88,创建一个查询显示所有其薪水高于平均薪水的雇员的雇员号和名字。按薪水的升序排序。
#89,写一个查询显示所有工作在有任一雇员的名字中包含一个 u 的部门的雇员的雇员号和名字。
#90,显示所有部门地点号 (department location ID ) 是 1700 的雇员的 last name、department
#92,显示在 Executive 部门的每个雇员的 department number、lastname 和 job ID。
#93,查询雇员表中所有数据按 id 排序，实现分页查询，每次返回两条结果。
#94,查询雇员表中所有数据按 id 排序，使用 LIMIT OFFSET 实现分页查询，每次返回两条结果。
#100 查询雇员的详细信息,包括部门,地区,国家,洲.要最全的明细表
-- 101.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
-- 102.查询同时存在" 01 "课程和" 02 "课程的情况
-- 103 .查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
-- 104.查询不存在" 01 "课程但存在" 02 "课程的情况
-- 105.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
-- 107.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
-- 113.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
-- 114.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
-- 115.查询没学过"张三"老师讲授的任一门课程的学生姓名
-- 116.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
-- 117.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
-- 118.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
-- 120.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
-- 121.查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
-- 122.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
-- 130.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
-- 131.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
-- 132.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
-- 133.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
-- 134.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
-- 136.查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名
-- 138.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
-- 139.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
-- 140.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
-- 142.统计每门课程的学生选修人数（超过 5 人的课程才统计）
-- 146,按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
-- 152、查询所有职工所属部门的部门号，不显示重复的部门号。
-- 156、创建一个只有职工号、姓名和参加工作的新表，名为工作日期表。
-- 158、列出1960年以前出生的职工的姓名、参加工作日期。
-- 159、列出工资在1000－2000之间的所有职工姓名。
-- 161、列出所有部门号为2和3的职工号、姓名、党员否。 否？ 不是党员的？
-- 162、将职工表worker中的职工按出生的先后顺序排序。
-- 168、查询1833班，语文成绩大于80小于90的成绩信息
-- 170、显示1832班英语成绩为98，数学成绩为77的姓名与学号， 啥学号？ class?
-- 171、查询出1832班成绩并且按语文成绩排序(降序)
-- 172，查询1833班与1832班，语文成绩与数学成绩都小于80的姓名。
-- 174，求出班上语文成绩不及格的学生姓名 60分不及格？
-- 176，求出每个班级语文成绩总分--涉及到每个的时候都需要分组
-- 179，求出英语分数高于70且其它任何一科目大于60分的人和班级
# 187. 查询表Employee表中女雇员的地址和电话，使用AS子句将结果列中各列的标题分别指定为地址、电话;
# 189. 找出所有性王的雇员的部门号（部门号不能重复显示）;
# 190. 找出所有收入在2000－3000元之间的雇员编号
# 192. 查找在财务部且年龄不低于研发部任一个雇员年龄的雇员的姓名;
# 195. 查找财务部收入在2200元以上的雇员姓名及其薪水详细情况;
# 198. 求各部门的雇员数（要求显示，部门号、部门名称和部门雇员数）;
# 199. 求部门的平均薪水大于2500的部门信息（要求显示，部门号、部门名称和平均工资）
# 200， 求部门的平均薪水大于3500的部门信息（要求显示，部门号、部门名称和平均工资）
---201. 查出至少有一个员工的部门，显示部门编号、部门名称、部门位置、部门人数。
-- 204. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
-- 205. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
-- 206. 列出所有文员的姓名及其部门名称，部门的人数。
-- 207. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。
-- 208. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。
-- 209. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。
-- 211.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。
-- 212.列出每个部门的员工数量、平均工资(保留2位小数)。
--214，查询每一个员工的姓名及关联的部门名称；[内连接]
--215，查询员工表的所有数据和对应的部门信息；[左外连接]
--216，查询部门表的所有数据和对应的员工信息；[右外连接]
--218，查询所有员工及其领导的名字 , 如果员工没有领导, 也需要查询出来；
--219，将薪资低于5000和工龄大于2年的员工全部查询出来；
刷题建议 推荐先看以下几篇，熟悉查询语法。
七，MySQL数据查询语言DQL（Data Query Language ）-CSDN博客
八，mysql数据库之 常用函数【字符串，时间，数值】查询_mysql时间和字符串-CSDN博客
十四,MYSQL之窗口函数，详细篇，附大量窗口函数查询sql练习题-CSDN博客
第一批，100道sql查询练习题
表数据在100道题目之后,数据纯属杜撰，仅做查询练习使用
# 1,查询 departments 表中的所有数据。
select * from departments;#2,查询 departments 表中所有部门名称。
select DEPARTMENT_NAME from departments;#3,查询雇员的年薪，并显示他们的雇员ID，名字。
select SALARY*12,EMPLOYEE_ID,FIRST_NAME,LAST_NAME from employees;#4,计算 employees 表中的员工全年薪水加 100 以后的薪水是多少，并显示他们的员工ID与名字。
select (SALARY*12)+100,EMPLOYEE_ID,FIRST_NAME,LAST_NAME from employees;#5,计算 employees 表中的员工薪水加 100 以后的全年薪水是多少，并显示他们的员工ID与名字。
select (SALARY+100)*12,EMPLOYEE_ID,FIRST_NAME,LAST_NAME from employees;#6,计算员工表的年薪包含佣金。
select *,12*SALARY*COMMISSION_PCT from employees;#7,查询 employees 表将雇员 last_name 列定义别名为 name。
select LAST_NAME as name from employees;#8,查询 employees 表为表定义别名为emp，将雇员 last_name 列定义别名为 name。
select LAST_NAME name from employees as emp;#9,查询 employees 表，显示唯一的部门 ID。
select distinct DEPARTMENT_ID from employees;#10,查询 departments 表中部门 ID 为 90 的部门名称与工作地点 ID。
select DEPARTMENT_NAME,LOCATION_ID from departments where DEPARTMENT_ID ='90';#11,查询 employees 表中员工薪水大于等于 3000 的员工的姓名与薪水。
select FIRST_NAME,LAST_NAME,SALARY from employees where SALARY >=3000;#12,查询 employees 表中员工薪水不等于 5000 的员工的姓名与薪水。
select FIRST_NAME,LAST_NAME,SALARY from employees where SALARY !=5000;#13,查询 employees 表，薪水在 3000-8000 之间的雇员ID、名字与薪水。
select employee_id, first_name, last_name, SALARY from employees where SALARY between 3000 and 8000;#14,查询 employees 表，找出薪水是 5000,6000,8000 的雇员ID、名字与薪水。
select employee_id, first_name, last_name, SALARY from employees where SALARY in(5000,6000,8000);#15,查询 employees 中雇员名字第二个字母是 e 的雇员名字。
select * from employees where FIRST_NAME like '_e%';#16,找出 emloyees 表中那些没有佣金的雇员雇员ID、名字与佣金。
select * from employees where COMMISSION_PCT is null;#17,找出 employees 表中那些有佣金的雇员ID、名字与佣金。
select * from employees where COMMISSION_PCT is not null;# 18,查询 employees 表中雇员薪水是 8000 的并且名字中含有e 的雇员 名字与薪水。
select employee_id, first_name, last_name, SALARY from employees where SALARY = '8000' and FIRST_NAME like '%e%';#19,查询 employees 表中雇员薪水是 8000 的或者名字中含有e 的雇员名字与薪水。
select employee_id, first_name, last_name, SALARY from employees where SALARY = '8000' or FIRST_NAME like '%e%';#20,查询 employees 表中雇员名字中不包含 u 的雇员的名字。
select LAST_NAME from employees where LAST_NAME not like '%e%';#21,查询 employees 表中的所有雇员，显示他们的ID、名字与薪水，并按薪水升序排序。
select employee_id, first_name, last_name, SALARY from employees order by SALARY ;#22,查询 employees 表中的所有雇员，显示他们的ID与名字，并按雇员名字降序排序。
select employee_id, first_name, last_name, SALARY from employees order by SALARY desc ;#23,显示雇员ID，名字。计算雇员的年薪，年薪列别名为annsal，并对该列进行升序排序，
select employee_id, first_name, last_name, SALARY ,SALARY*12 as annsal from employees order by annsal;#24,以升叙排序显示 DEPARTMENT_ID 列，同时以降序排序显示SALARY 列。
select department_id,salary from employees order by DEPARTMENT_ID asc,SALARY desc ;#25,创建一个查询，显示收入超过 12,000 的雇员的名字和薪水。
select FIRST_NAME,LAST_NAME,SALARY from employees where SALARY>12000;#26,创建一个查询，显示雇员号为 176 的雇员的名字和部门号。
select FIRST_NAME,LAST_NAME,DEPARTMENT_ID from employees where DEPARTMENT_ID='176';#27,.显示所有薪水不在 5000 和 12000 之间的雇员的名字和薪水。
select FIRST_NAME,LAST_NAME,SALARY from employees where SALARY <5000 or SALARY>12000;
#或者
select FIRST_NAME,LAST_NAME,SALARY from employees where SALARY not between 5000 and 12000;#28,显示所有在部门 20 和 50 中的雇员的名字和部门号，并以名字按字母顺序排序。
select FIRST_NAME,LAST_NAME,DEPARTMENT_ID from employees where DEPARTMENT_ID between 20 and 50 order by DEPARTMENT_ID;#29,.列出收入在 5,000 和 12,000 之间，并且在部门 20 或50 工作的雇员的名字和薪水。将列标题分别显示为 Employee 和 MonthlySalary
select FIRST_NAME Employee ,SALARY MonthlySalary from employees where SALARY between 5000 and 12000 and DEPARTMENT_ID between 20 and 50;
#30,显示所有没有主管经理的雇员的名字和工作岗位。
select FIRST_NAME,JOB_ID from employees where MANAGER_ID is null;
#31,显示所有有佣金的雇员的名字、薪水和佣金。以薪水和佣金的降序排序数据。
select FIRST_NAME,SALARY,COMMISSION_PCT from employees where COMMISSION_PCT is not null order by SALARY desc ,COMMISSION_PCT desc ;
#32,显示所有名字中有一个 a 和一个 e 的雇员的名字。
select FIRST_NAME from employees where FIRST_NAME like '%a%' and FIRST_NAME like '%e%';#33,显示所有工作岗位是销售代表（SA_REP）或者普通职员(ST_CLERK)，并且薪水不等于 2,500、3,500 或 7,000 的雇员的名字、工作岗位和薪水。
select FIRST_NAME,JOB_ID,SALARY from employees where (JOB_ID ='SA_REP' or JOB_ID= 'ST_CLERK') and (SALARY not in (2500,3500,7000));
#34,显示雇员 Davies [名称要last_name] 的雇员号、姓名和部门号，将姓名转换为大写。
select EMPLOYEE_ID,upper(LAST_NAME),EMPLOYEE_ID from employees where LAST_NAME='Davies';#35,显示所有工作岗位名称从第 4 个字符位置开始，包含字符串REP的雇员的ID信息，将雇员的姓和名连接显示在一起，还显示雇员名的的长度，以及名字中字母 a 的位置。
### jop从第4个开始 要字符串截取函数 UBSTRING 4 个开始 然后包含字符 rep
## 雇员姓和名称连接在一起 要用拼接函数 concat
## 雇员的长度 要用 长度函数 length
## 显示a字母的位置 要字符串索引函数 instr 字母在字段的位置
SELECT employee_id,
CONCAT(last_name,first_name) NAME,
job_id,
LENGTH(last_name),
INSTR(last_name,'a') "Contains 'a'?"
FROM employees WHERE
SUBSTR(job_id, 4) = 'REP';#36,所有job_id是SA_REP的雇员的名字，薪水以及薪水被5000除后的余数。
select SALARY,MOD(SALARY,5000) from employees where JOB_ID ='SA_REP';#37,显示所有在部门 90 中的雇员的名字和从业的周数。雇员的总工作时间以周计算，用当前日期 (SYSDATE) 减去雇员的受顾日期，再除以 7。
/*
要的字段 雇员名称 和从业周数
条件 部门90
周的计算方式 当前日期-受雇日期 除以7
*/
SELECT
FIRST_NAME,TIMESTAMPDIFF(WEEK, HIRE_DATE, NOW()) AS Weeks_Diff from employees;
-- TIMESTAMPDIFF 时间差函数 第一个参数是年/月/日 第二个参数是起始日期 第三个参数是结束日期；# 38,查询 employees 表中雇员名字为 King 的雇员的入职日期，要求显示格式为 yyyy 年 MM 月 dd 日。
select date_format(HIRE_DATE,'%Y年%m月%d日') from employees where LAST_NAME='King';#39,查询部门编号是50或者80的员工信息，包含他们的名字、薪水、佣金。在income列中，如果有佣金则显示‘SAL+COMM’，无佣金则显示'SAL'。
select LAST_NAME,SALARY,COMMISSION_PCT,if(COMMISSION_PCT is not null,SALARY+COMMISSION_PCT,SALARY) income
from employees
where DEPARTMENT_ID between 50 and 80;#40,计算雇员的年报酬，你需要用 12 乘以月薪，再加上它的佣金 (等于年薪乘以佣金
select LAST_NAME,12*SALARY+12*if(COMMISSION_PCT,COMMISSION_PCT,0) from employees;#41,查询员工表，显示他们的名字、名字的长度该列名为expr1，姓氏、姓氏的长度该列名为expr2。在result列中，如果名字与姓氏的长度相同则显示空，如果不相同则显示名字长度。
select LAST_NAME,length(LAST_NAME) expr1,
(length(FIRST_NAME)+length(LAST_NAME) )as expr2,
if(length(FIRST_NAME)=length(LAST_NAME),null,length(FIRST_NAME))
from employees;
## nullif 函数用法
select LAST_NAME,length(LAST_NAME) expr1,
(length(FIRST_NAME)+length(LAST_NAME) )as expr2,
nullif(length(FIRST_NAME),length(LAST_NAME)) result
from employees;#42,查询员工表，显示他们的名字，如果 COMMISSION_PCT 值是非空，显示它。如果COMMISSION_PCT 值是空，则显示 SALARY 。
# 如果 COMMISSION_PCT 和SALARY 值都是空，那么显示 10。在结果中对佣金列升序排序。
select FIRST_NAME,if(COMMISSION_PCT,COMMISSION_PCT,SALARY) ,coalesce(SALARY,COMMISSION_PCT,'10') from employees;
# 43,查询员工表，如果 JOB_ID 是 IT_PROG，薪水增加 10%；如果JOB_ID 是 ST_CLERK，薪水增加 15%；如果 JOB_ID 是 SA_REP，
# 薪水增加 20%。对于所有其他的工作角色，不增加薪水。
select LAST_NAME,SALARY,JOB_ID,
case JOB_ID
when 'IT_PROG' then SALARY*0.18
when 'ST_CLERK' then SALARY*0.15
when 'SA_REP' then SALARY*0.2
else SALARY
end
from employees ;#44,显示受雇日期在 1998 年 2 月 20 日 和 2005 年 5 月 1 日 之间的雇员的名字、岗位和受雇日期。按受雇日期顺序排序查询结果。
select LAST_NAME,JOB_ID,HIRE_DATE from employees where HIRE_DATE between '1998-2-20' and '2005-5-1' order by HIRE_DATE;
#45,显示每一个在 2002 年受雇的雇员的名字和受雇日期。
select LAST_NAME,HIRE_DATE from employees where year(HIRE_DATE)='2002';#46,对每一个雇员，显示 employee number、last_name、salary 和salary 增加 15%，并且表示成整数，列标签显示为 New Salary。
select EMPLOYEE_ID,LAST_NAME,SALARY,round(employees.SALARY+SALARY*0.15) as new_SALARY from employees;#47，写一个查询，显示名字的长度，对所有名字开始字母是 J、A 或 M的雇员。用雇员的 lastname排序结果。
select LAST_NAME
from employees where LAST_NAME like 'A%' or LAST_NAME like 'J%' or LAST_NAME like 'M%' order by LAST_NAME;
#48,创建一个查询显示所有雇员的 last name 和 salary。将薪水格式化为 15 个字符长度，用 $左填充 。
select LAST_NAME,SALARY,rpad(SALARY,15,'$') from employees;#50,创建一个查询显示雇员的 last names 和 commission (佣金) 比率。如果雇员没有佣金，显示 “No Commission”，列标签COMM。
select LAST_NAME,COMMISSION_PCT,if(COMMISSION_PCT is null,'No Commission',COMMISSION_PCT) from employees;
#51,写一个查询，按照下面的数据显示所有雇员的基于 JOB_ID 列值的级别。
# AD_PRES A
# ST_MAN B
# IT_PROG C
# SA_REP D
# ST_CLERK E
# 不在上面的 0
select LAST_NAME,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'
else 0
end
from employees;# 52, 查询雇员 King 所在的部门名称。
select e.LAST_NAME,d.DEPARTMENT_NAME from employees e,departments d where e.DEPARTMENT_ID=d.DEPARTMENT_ID and e.LAST_NAME='King';
#53, 显示每个雇员的 last name、departmentname 和 city。
## 要啥 员工表的员工名称 部门表的部门名称 城市表的城市名称
select e.LAST_NAME,d.DEPARTMENT_NAME,l.CITY from employees e,departments d ,locations l
where e.DEPARTMENT_ID=d.DEPARTMENT_ID
and d.LOCATION_ID=l.LOCATION_ID;
#54,
/*
创建 job_grades 表，包含 lowest_sal ，highest_sal ，
grade_level。
插入数据
1000 2999 A
2000 4999 B
5000 7999 C
8000 12000 D
查询所有雇员的薪水级别。
*/
create table job_grades(
lowest_sal int,
highest_sal int,
grade_level varchar(10)
);
insert into job_grades value (1000,2999,'A');
insert into job_grades value (2000,4999,'B');
insert into job_grades value (5000,7999,'C');
insert into job_grades value (8000,12000,'D');
select e.LAST_NAME,e.SALARY,j.grade_level from employees e,job_grades j where e.SALARY between j.lowest_sal and j.highest_sal;
#55,查询每个雇员的经理的名字以及雇员的名字，雇员名字列别名为W，经理列别名为M。
select e1.LAST_NAME M,e2.LAST_NAME W from employees e1,employees e2 where e1.EMPLOYEE_ID=e2.MANAGER_ID;#56,查询Fox的经理是谁？显示他的名字。
select e1.LAST_NAME,e2.LAST_NAME from employees e1,employees e2 where e1.EMPLOYEE_ID=e2.MANAGER_ID and e2.LAST_NAME='Fox';
SELECT worker.LAST_NAME,manager.LAST_NAME from employees worker,employees manager where worker.MANAGER_ID = manager.EMPLOYEE_ID
AND
worker.LAST_NAME = 'Fox';#57,使用交叉连接查询 employees 表与 departments 表。
-- 组成一个笛卡尔乘积表
select * from employees e cross join departments d;
#58,使用自然连接查询所有有部门的雇员的名字以及部门名称。
select e.LAST_NAME,d.DEPARTMENT_NAME from employees e,departments d where e.DEPARTMENT_ID=d.DEPARTMENT_ID;
#59,查询雇员名字为 Fox 的雇员 ID ，薪水与部门名称。
select e.EMPLOYEE_ID,SALARY,d.DEPARTMENT_NAME from employees e,departments d where e.DEPARTMENT_ID=d.DEPARTMENT_ID and e.LAST_NAME='Fox';
#60,查询所有雇员的名字以及他们的部门名称，包含那些没有部门的雇员。
select * from employees e left join departments d on e.DEPARTMENT_ID=d.DEPARTMENT_ID;#61,查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门。
select * from departments d right join employees e on d.DEPARTMENT_ID=e.DEPARTMENT_ID;
#62,查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门以及没有部门的雇 员。
select * from employees e left join departments d on e.DEPARTMENT_ID=d.DEPARTMENT_ID
union
select * from departments d left join db3.employees e2 on d.DEPARTMENT_ID = e2.DEPARTMENT_ID;
#63,写一个查询显示所有雇员的 last name、department id、and department name。
select e.LAST_NAME,e.DEPARTMENT_ID,d.DEPARTMENT_NAME from employees e,departments d where e.DEPARTMENT_ID=d.DEPARTMENT_ID;
#64,创建一个在部门 80 中的所有工作岗位的唯一列表，在输出中包括部门的地点。
select e.*,d.*,l.CITY from employees e,departments d,locations l
where e.DEPARTMENT_ID=d.DEPARTMENT_ID
and d.LOCATION_ID=l.LOCATION_ID
and e.DEPARTMENT_ID='80';#65,写一个查询显示所有有佣金的雇员的 last name、department name、location ID 和城市
select e.LAST_NAME,d.DEPARTMENT_NAME,d.LOCATION_ID,l.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;#66,显示所有在其 last names 中有一个小写 a 的雇员的 last name 和department name。
select e.LAST_NAME,d.DEPARTMENT_NAME from employees e,departments d where e.DEPARTMENT_ID=d.DEPARTMENT_ID
and e.LAST_NAME like '%e%';#67,用sql99的内连接写一个查询显示那些工作在 Toronto 的所有雇员的 last name、job、department number 和 departmentname。
select e.LAST_NAME,e.JOB_ID,d.DEPARTMENT_ID,d.DEPARTMENT_NAME from employees e inner join departments d on e.DEPARTMENT_ID=d.DEPARTMENT_ID
inner join locations l on d.LOCATION_ID=l.LOCATION_ID where l.CITY='Toronto';#68,显示雇员的 last name 和 employee number 连同他们的经理的last name 和manager number。列标签分别为 Employee、Emp#、Manager 和 Mgr#
select e1.LAST_NAME as empname,e1.EMPLOYEE_ID as empid,e2.LAST_NAME as man_name ,e2.EMPLOYEE_ID as man_id
from employees e1 ,employees e2 where e1.MANAGER_ID=e2.EMPLOYEE_ID;
#69,计算员工表中工作编号含有REP的工作岗位的平均薪水与薪水总和。
select JOB_ID,avg(SALARY),sum(SALARY) from employees where JOB_ID like '%REP%' group by JOB_ID;
#70,查询员工表中入职时间最短与最长的员工，并显示他们的入职时间。
select max(HIRE_DATE),min(HIRE_DATE) from employees;
#71,显示员工表中部门编号是80中有佣金的雇员人数。
select count(COMMISSION_PCT) from employees where DEPARTMENT_ID='80';
#72，显示员工表中的部门数。
select count(distinct DEPARTMENT_ID) from employees ;
#73,在组函数中使用 IFNULL 函数 统计部门中有佣金的平均值
select avg(ifnull(COMMISSION_PCT,0)) from employees;
#74,计算每个部门的员工总数。
select DEPARTMENT_ID,count(EMPLOYEE_ID) from employees group by DEPARTMENT_ID;
#75,计算每个部门的不同工作岗位的员工总数。
select DEPARTMENT_ID,JOB_ID,count(EMPLOYEE_ID) from employees group by DEPARTMENT_ID, JOB_ID;
#76,找到每个部门中的最高薪水，而且只显示最高薪水大于 $10,000 的那些部门
select d.DEPARTMENT_NAME,max(e.SALARY)
from employees e,departments d
where e.DEPARTMENT_ID=d.DEPARTMENT_ID group by d.DEPARTMENT_ID having max(e.SALARY)>10000;
#77,显示那些合计薪水超过 13,000 的每个工作岗位的合计薪水。排除那些JOB_ID中含有REP的工作岗位，并且用合计月薪排序列表。
select JOB_ID,sum(SALARY)
from employees
where JOB_ID not like '%REP%' group by JOB_ID order by sum(SALARY);#78,显示所有雇员的最高、最低、合计和平均薪水，列标签分别为：Max、Min、Sum 和 Avg。四舍五入结果为最近的整数。
select round(max(SALARY)) Max,round(min(SALARY)) Min,round(SUM(SALARY)) Sum,round(avg(SALARY)) Avg from employees;
#79,写一个查询显示每一工作岗位的人数。
select JOB_ID,count(JOB_ID) from employees group by JOB_ID;#80,确定经理人数，不需要列出他们，列标签是 Number of Managers。提示：用MANAGER_ID列决定经理号。
select count(distinct MANAGER_ID) from employees ;
#81,写一个查询显示最高和最低薪水之间的差。
select max(SALARY)-min(SALARY) from employees#82,显示经理号和经理付给雇员的最低薪水。排除那些经理未知的人。排除最低薪水小于等于 $6,000 的组。按薪水降序排序输出。
select MANAGER_ID,min(SALARY)
from employees
where MANAGER_ID is not null group by MANAGER_ID having min(SALARY)>6000 order by min(SALARY) desc ;#83,写一个查询显示每个部门的名字、地点、人数和部门中所有雇员的平均薪水。四舍五入薪水到两位小数。
select e.DEPARTMENT_ID,d.DEPARTMENT_NAME,count(d.DEPARTMENT_ID) ,round(avg(SALARY),2)
from employees e, departments d
where e.DEPARTMENT_ID=d.DEPARTMENT_ID group by e.DEPARTMENT_ID;#84,查询与Fox同一部门的同事，并显示他们的名字与部门ID。
select DEPARTMENT_ID from employees where LAST_NAME='Fox';
select LAST_NAME,DEPARTMENT_ID from employees where DEPARTMENT_ID =(select DEPARTMENT_ID from employees where LAST_NAME='Fox');
#85,查询 Fox的同事，但是不包含他自己。
select DEPARTMENT_ID from employees where LAST_NAME='Fox';
select LAST_NAME from employees where DEPARTMENT_ID =(select DEPARTMENT_ID from employees where LAST_NAME='Fox') and LAST_NAME !='Fox';
#86,查找各部门收入为部门最低的那些雇员。显示他们的名字，薪水以及部门 ID。
select min(SALARY) from employees group by DEPARTMENT_ID;
select * from employees where SALARY in (select min(SALARY) from employees group by DEPARTMENT_ID)#87,写一个查询显示与 Zlotkey 在同一部门的雇员的 last name 和hire date，结果中不包括
select e.DEPARTMENT_ID from employees e,departments d where e.DEPARTMENT_ID=d.DEPARTMENT_ID and e.LAST_NAME='Zlotkey';
select LAST_NAME,HIRE_DATE from employees where DEPARTMENT_ID =
(
select e.DEPARTMENT_ID from employees e,departments d where e.DEPARTMENT_ID=d.DEPARTMENT_ID and e.LAST_NAME='Zlotkey'
) and LAST_NAME != 'Zlotkey';
#88,创建一个查询显示所有其薪水高于平均薪水的雇员的雇员号和名字。按薪水的升序排序。
select avg(SALARY) from employees; -- 平均薪水
select EMPLOYEE_ID,LAST_NAME,SALARY from employees where SALARY>(
select avg(SALARY) from employees
) order by SALARY ;#89,写一个查询显示所有工作在有任一雇员的名字中包含一个 u 的部门的雇员的雇员号和名字。
select DEPARTMENT_ID from employees where LAST_NAME like '%u%' ;
select EMPLOYEE_ID,LAST_NAME from employees where DEPARTMENT_ID in(select DEPARTMENT_ID from employees where LAST_NAME like '%u%' );
#90,显示所有部门地点号 (department location ID ) 是 1700 的雇员的 last name、department
select e.LAST_NAME,d.DEPARTMENT_ID,e.JOB_ID from departments d,employees e where e.DEPARTMENT_ID= d.DEPARTMENT_ID and LOCATION_ID='1700';
#用子查询
SELECT
e.LAST_NAME,e.DEPARTMENT_ID,e.JOB_ID
FROM employees e
WHERE e.DEPARTMENT_ID IN
(SELECT
d.DEPARTMENT_ID
FROM departments d
WHERE d.LOCATION_ID = 1700);#91,显示每个向 King 报告的雇员的名字和薪水。
select e1.LAST_NAME,e1.SALARY from employees e1,employees e2 where e1.MANAGER_ID=e2.EMPLOYEE_ID and e2.LAST_NAME='King';
#92,显示在 Executive 部门的每个雇员的 department number、lastname 和 job ID。
select d.DEPARTMENT_ID,e.LAST_NAME,e.JOB_ID
from employees e,departments d
where e.DEPARTMENT_ID=d.DEPARTMENT_ID and d.DEPARTMENT_NAME='Executive';
SELECT
e.DEPARTMENT_ID,e.LAST_NAME,e.JOB_ID
FROM employees e
WHERE e.DEPARTMENT_ID =
(select d.DEPARTMENT_ID FROM departments d
WHERE d.DEPARTMENT_NAME = 'Executive');#93,查询雇员表中所有数据按 id 排序，实现分页查询，每次返回两条结果。
select * from employees order by EMPLOYEE_ID limit 0,2;#94,查询雇员表中所有数据按 id 排序，使用 LIMIT OFFSET 实现分页查询，每次返回两条结果。
select * from employees order by EMPLOYEE_ID limit 2 offset 4;
#95,查询不同部门薪资排行最高的2个人; 分组求topN
select DEPARTMENT_ID,
LAST_NAME,
SALARY,
row_number() over (partition by DEPARTMENT_ID order by SALARY) rn
from employees;
select * from
(select DEPARTMENT_ID,
LAST_NAME,
SALARY,
row_number() over (partition by DEPARTMENT_ID order by SALARY) rn
from employees) t1 where t1.rn<=2;#96,查询员工id 是113的国家和大洲
select c.COUNTRY_NAME,r.REGION_NAME from employees e,departments d,locations l,countries c,regions r
where e.DEPARTMENT_ID=d.DEPARTMENT_ID
and d.LOCATION_ID=l.LOCATION_ID
and l.COUNTRY_ID=c.COUNTRY_ID
and c.REGION_ID=r.REGION_ID
and e.EMPLOYEE_ID='113';#97,查询不同大洲的雇员人数
select r.REGION_NAME,count(e.EMPLOYEE_ID) from employees e,departments d,locations l,countries c,regions r
where e.DEPARTMENT_ID=d.DEPARTMENT_ID
and d.LOCATION_ID=l.LOCATION_ID
and l.COUNTRY_ID=c.COUNTRY_ID
and c.REGION_ID=r.REGION_ID
group by r.REGION_NAME;#98,查询不同国家的雇员人数 和薪资总和
select c.COUNTRY_NAME,count(e.EMPLOYEE_ID),sum(SALARY) from employees e,departments d,locations l,countries c
where e.DEPARTMENT_ID=d.DEPARTMENT_ID
and d.LOCATION_ID=l.LOCATION_ID
and l.COUNTRY_ID=c.COUNTRY_ID
group by c.COUNTRY_NAME;#99,查询不同洲薪资最高的3人
select r.REGION_NAME,e.SALARY,
rank() over (partition by r.REGION_NAME order by e.SALARY) rn
from employees e,departments d,locations l,countries c,regions r
where e.DEPARTMENT_ID=d.DEPARTMENT_ID
and d.LOCATION_ID=l.LOCATION_ID
and l.COUNTRY_ID=c.COUNTRY_ID
and c.REGION_ID=r.REGION_ID;
select * from (
select r.REGION_NAME,e.SALARY,
rank() over (partition by r.REGION_NAME order by e.SALARY) rn
from employees e,departments d,locations l,countries c,regions r
where e.DEPARTMENT_ID=d.DEPARTMENT_ID
and d.LOCATION_ID=l.LOCATION_ID
and l.COUNTRY_ID=c.COUNTRY_ID
and c.REGION_ID=r.REGION_ID
)t1 where t1.rn<3;#100 查询雇员的详细信息,包括部门,地区,国家,洲.要最全的明细表
select *
from employees e,departments d,locations l,countries c,regions r
where e.DEPARTMENT_ID=d.DEPARTMENT_ID
and d.LOCATION_ID=l.LOCATION_ID
and l.COUNTRY_ID=c.COUNTRY_ID
and c.REGION_ID=r.REGION_ID;
第一批100道sql查询练习题数据。
表数据,数据纯属杜撰，仅做查询练习使用
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for countries
-- ----------------------------
DROP TABLE IF EXISTS `countries`;
CREATE TABLE `countries` (
`COUNTRY_ID` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Primary key of countries table.',
`COUNTRY_NAME` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Country name',
`REGION_ID` int NULL DEFAULT NULL COMMENT 'Region ID for the country. Foreign key to region_id column in the departments table.',
PRIMARY KEY (`COUNTRY_ID`) USING BTREE,
INDEX `COUNTR_REG_FK`(`REGION_ID`) USING BTREE,
CONSTRAINT `COUNTR_REG_FK` FOREIGN KEY (`REGION_ID`) REFERENCES `regions` (`REGION_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'country table. Contains 25 rows. References with locations table.' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of countries
-- ----------------------------
INSERT INTO `countries` VALUES ('AR', 'Argentina', 2.000000000000000000000000000000);
INSERT INTO `countries` VALUES ('AU', 'Australia', 3.000000000000000000000000000000);
INSERT INTO `countries` VALUES ('BE', 'Belgium', 1.000000000000000000000000000000);
INSERT INTO `countries` VALUES ('BR', 'Brazil', 2.000000000000000000000000000000);
INSERT INTO `countries` VALUES ('CA', 'Canada', 2.000000000000000000000000000000);
INSERT INTO `countries` VALUES ('CH', 'Switzerland', 1.000000000000000000000000000000);
INSERT INTO `countries` VALUES ('CN', 'China', 3.000000000000000000000000000000);
INSERT INTO `countries` VALUES ('DE', 'Germany', 1.000000000000000000000000000000);
INSERT INTO `countries` VALUES ('DK', 'Denmark', 1.000000000000000000000000000000);
INSERT INTO `countries` VALUES ('EG', 'Egypt', 4.000000000000000000000000000000);
INSERT INTO `countries` VALUES ('FR', 'France', 1.000000000000000000000000000000);
INSERT INTO `countries` VALUES ('IL', 'Israel', 4.000000000000000000000000000000);
INSERT INTO `countries` VALUES ('IN', 'India', 3.000000000000000000000000000000);
INSERT INTO `countries` VALUES ('IT', 'Italy', 1.000000000000000000000000000000);
INSERT INTO `countries` VALUES ('JP', 'Japan', 3.000000000000000000000000000000);
INSERT INTO `countries` VALUES ('KW', 'Kuwait', 4.000000000000000000000000000000);
INSERT INTO `countries` VALUES ('ML', 'Malaysia', 3.000000000000000000000000000000);
INSERT INTO `countries` VALUES ('MX', 'Mexico', 2.000000000000000000000000000000);
INSERT INTO `countries` VALUES ('NG', 'Nigeria', 4.000000000000000000000000000000);
INSERT INTO `countries` VALUES ('NL', 'Netherlands', 1.000000000000000000000000000000);
INSERT INTO `countries` VALUES ('SG', 'Singapore', 3.000000000000000000000000000000);
INSERT INTO `countries` VALUES ('UK', 'United Kingdom', 1.000000000000000000000000000000);
INSERT INTO `countries` VALUES ('US', 'United States of America', 2.000000000000000000000000000000);
INSERT INTO `countries` VALUES ('ZM', 'Zambia', 4.000000000000000000000000000000);
INSERT INTO `countries` VALUES ('ZW', 'Zimbabwe', 4.000000000000000000000000000000);
-- ----------------------------
-- Table structure for departments
-- ----------------------------
DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments` (
`DEPARTMENT_ID` int NOT NULL COMMENT 'Primary key column of departments table.',
`DEPARTMENT_NAME` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'A not null column that shows name of a department. Administration,\nMarketing, Purchasing, Human Resources, Shipping, IT, Executive, Public\nRelations, Sales, Finance, and Accounting. ',
`MANAGER_ID` int NULL DEFAULT NULL COMMENT 'Manager_id of a department. Foreign key to employee_id column of employees table. The manager_id column of the employee table references this column.',
`LOCATION_ID` int NULL DEFAULT NULL COMMENT 'Location id where a department is located. Foreign key to location_id column of locations table.',
PRIMARY KEY (`DEPARTMENT_ID`) USING BTREE,
INDEX `DEPT_LOCATION_IX`(`LOCATION_ID`) USING BTREE,
INDEX `DEPT_MGR_FK`(`MANAGER_ID`) USING BTREE,
CONSTRAINT `DEPT_LOC_FK` FOREIGN KEY (`LOCATION_ID`) REFERENCES `locations` (`LOCATION_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `DEPT_MGR_FK` FOREIGN KEY (`MANAGER_ID`) REFERENCES `employees` (`EMPLOYEE_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'Departments table that shows details of departments where employees\nwork. Contains 27 rows; references with locations, employees, and job_history tables.' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of departments
-- ----------------------------
INSERT INTO `departments` VALUES (10, 'Administration', 200, 1700);
INSERT INTO `departments` VALUES (20, 'Marketing', 201, 1800);
INSERT INTO `departments` VALUES (30, 'Purchasing', 114, 1700);
INSERT INTO `departments` VALUES (40, 'Human Resources', 203, 2400);
INSERT INTO `departments` VALUES (50, 'Shipping', 121, 1500);
INSERT INTO `departments` VALUES (60, 'IT', 103, 1400);
INSERT INTO `departments` VALUES (70, 'Public Relations', 204, 2700);
INSERT INTO `departments` VALUES (80, 'Sales', 145, 2500);
INSERT INTO `departments` VALUES (90, 'Executive', 100, 1700);
INSERT INTO `departments` VALUES (100, 'Finance', 108, 1700);
INSERT INTO `departments` VALUES (110, 'Accounting', 205, 1700);
INSERT INTO `departments` VALUES (120, 'Treasury', NULL, 1700);
INSERT INTO `departments` VALUES (130, 'Corporate Tax', NULL, 1700);
INSERT INTO `departments` VALUES (140, 'Control And Credit', NULL, 1700);
INSERT INTO `departments` VALUES (150, 'Shareholder Services', NULL, 1700);
INSERT INTO `departments` VALUES (160, 'Benefits', NULL, 1700);
INSERT INTO `departments` VALUES (170, 'Manufacturing', NULL, 1700);
INSERT INTO `departments` VALUES (180, 'Construction', NULL, 1700);
INSERT INTO `departments` VALUES (190, 'Contracting', NULL, 1700);
INSERT INTO `departments` VALUES (200, 'Operations', NULL, 1700);
INSERT INTO `departments` VALUES (210, 'IT Support', NULL, 1700);
INSERT INTO `departments` VALUES (220, 'NOC', NULL, 1700);
INSERT INTO `departments` VALUES (230, 'IT Helpdesk', NULL, 1700);
INSERT INTO `departments` VALUES (240, 'Government Sales', NULL, 1700);
INSERT INTO `departments` VALUES (250, 'Retail Sales', NULL, 1700);
INSERT INTO `departments` VALUES (260, 'Recruiting', NULL, 1700);
INSERT INTO `departments` VALUES (270, 'Payroll', NULL, 1700);
-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
`EMPLOYEE_ID` int NOT NULL COMMENT 'Primary key of employees table.',
`FIRST_NAME` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'First name of the employee. A not null column.',
`LAST_NAME` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Last name of the employee. A not null column.',
`EMAIL` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Email id of the employee',
`PHONE_NUMBER` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Phone number of the employee; includes country code and area code',
`HIRE_DATE` datetime NOT NULL COMMENT 'Date when the employee started on this job. A not null column.',
`JOB_ID` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Current job of the employee; foreign key to job_id column of the\njobs table. A not null column.',
`SALARY` float(8, 2) NULL DEFAULT NULL COMMENT 'Monthly salary of the employee. Must be greater\nthan zero (enforced by constraint emp_salary_min)',
`COMMISSION_PCT` int NULL DEFAULT NULL COMMENT 'Commission percentage of the employee; Only employees in sales\ndepartment elgible for commission percentage',
`MANAGER_ID` int NULL DEFAULT NULL COMMENT 'Manager id of the employee; has same domain as manager_id in\ndepartments table. Foreign key to employee_id column of employees table.\n(useful for reflexive joins and CONNECT BY query)',
`DEPARTMENT_ID` int NULL DEFAULT NULL COMMENT 'Department id where employee works; foreign key to department_id\ncolumn of the departments table',
PRIMARY KEY (`EMPLOYEE_ID`) USING BTREE,
INDEX `EMP_DEPARTMENT_IX`(`DEPARTMENT_ID`) USING BTREE,
INDEX `EMP_JOB_IX`(`JOB_ID`) USING BTREE,
INDEX `EMP_MANAGER_IX`(`MANAGER_ID`) USING BTREE,
INDEX `EMP_NAME_IX`(`LAST_NAME`, `FIRST_NAME`) USING BTREE,
CONSTRAINT `EMP_DEPT_FK` FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES `departments` (`DEPARTMENT_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `EMP_JOB_FK` FOREIGN KEY (`JOB_ID`) REFERENCES `jobs` (`JOB_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `EMP_MANAGER_FK` FOREIGN KEY (`MANAGER_ID`) REFERENCES `employees` (`EMPLOYEE_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'employees table. Contains 107 rows. References with departments,\njobs, job_history tables. Contains a self reference.' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES (100, 'Steven', 'King', 'SKING', '515.123.4567', '2003-06-17 00:00:00', 'AD_PRES', 24000.00, NULL, NULL, 90);
INSERT INTO `employees` VALUES (101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', '2005-09-21 00:00:00', 'AD_VP', 17000.00, NULL, 100, 90);
INSERT INTO `employees` VALUES (102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', '2001-01-13 00:00:00', 'AD_VP', 17000.00, NULL, 100, 90);
INSERT INTO `employees` VALUES (103, 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', '2006-01-03 00:00:00', 'IT_PROG', 9000.00, NULL, 102, 60);
INSERT INTO `employees` VALUES (104, 'Bruce', 'Ernst', 'BERNST', '590.423.4568', '2007-05-21 00:00:00', 'IT_PROG', 6000.00, NULL, 103, 60);
INSERT INTO `employees` VALUES (105, 'David', 'Austin', 'DAUSTIN', '590.423.4569', '2005-06-25 00:00:00', 'IT_PROG', 4800.00, NULL, 103, 60);
INSERT INTO `employees` VALUES (106, 'Valli', 'Pataballa', 'VPATABAL', '590.423.4560', '2006-02-05 00:00:00', 'IT_PROG', 4800.00, NULL, 103, 60);
INSERT INTO `employees` VALUES (107, 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567', '2007-02-07 00:00:00', 'IT_PROG', 4200.00, NULL, 103, 60);
INSERT INTO `employees` VALUES (108, 'Nancy', 'Greenberg', 'NGREENBE', '515.124.4569', '2002-08-17 00:00:00', 'FI_MGR', 12008.00, NULL, 101, 100);
INSERT INTO `employees` VALUES (109, 'Daniel', 'Faviet', 'DFAVIET', '515.124.4169', '2002-08-16 00:00:00', 'FI_ACCOUNT', 9000.00, NULL, 108, 100);
INSERT INTO `employees` VALUES (110, 'John', 'Chen', 'JCHEN', '515.124.4269', '2005-09-28 00:00:00', 'FI_ACCOUNT', 8200.00, NULL, 108, 100);
INSERT INTO `employees` VALUES (111, 'Ismael', 'Sciarra', 'ISCIARRA', '515.124.4369', '2005-09-30 00:00:00', 'FI_ACCOUNT', 7700.00, NULL, 108, 100);
INSERT INTO `employees` VALUES (112, 'Jose Manuel', 'Urman', 'JMURMAN', '515.124.4469', '2006-03-07 00:00:00', 'FI_ACCOUNT', 7800.00, NULL, 108, 100);
INSERT INTO `employees` VALUES (113, 'Luis', 'Popp', 'LPOPP', '515.124.4567', '2007-12-07 00:00:00', 'FI_ACCOUNT', 6900.00, NULL, 108, 100);
INSERT INTO `employees` VALUES (114, 'Den', 'Raphaely', 'DRAPHEAL', '515.127.4561', '2002-12-07 00:00:00', 'PU_MAN', 11000.00, NULL, 100, 30);
INSERT INTO `employees` VALUES (115, 'Alexander', 'Khoo', 'AKHOO', '515.127.4562', '2003-05-18 00:00:00', 'PU_CLERK', 3100.00, NULL, 114, 30);
INSERT INTO `employees` VALUES (116, 'Shelli', 'Baida', 'SBAIDA', '515.127.4563', '2005-12-24 00:00:00', 'PU_CLERK', 2900.00, NULL, 114, 30);
INSERT INTO `employees` VALUES (117, 'Sigal', 'Tobias', 'STOBIAS', '515.127.4564', '2005-07-24 00:00:00', 'PU_CLERK', 2800.00, NULL, 114, 30);
INSERT INTO `employees` VALUES (118, 'Guy', 'Himuro', 'GHIMURO', '515.127.4565', '2006-11-15 00:00:00', 'PU_CLERK', 2600.00, NULL, 114, 30);
INSERT INTO `employees` VALUES (119, 'Karen', 'Colmenares', 'KCOLMENA', '515.127.4566', '2007-08-10 00:00:00', 'PU_CLERK', 2500.00, NULL, 114, 30);
INSERT INTO `employees` VALUES (120, 'Matthew', 'Weiss', 'MWEISS', '650.123.1234', '2004-07-18 00:00:00', 'ST_MAN', 8000.00, NULL, 100, 50);
INSERT INTO `employees` VALUES (121, 'Adam', 'Fripp', 'AFRIPP', '650.123.2234', '2005-04-10 00:00:00', 'ST_MAN', 8200.00, NULL, 100, 50);
INSERT INTO `employees` VALUES (122, 'Payam', 'Kaufling', 'PKAUFLIN', '650.123.3234', '2003-05-01 00:00:00', 'ST_MAN', 7900.00, NULL, 100, 50);
INSERT INTO `employees` VALUES (123, 'Shanta', 'Vollman', 'SVOLLMAN', '650.123.4234', '2005-10-10 00:00:00', 'ST_MAN', 6500.00, NULL, 100, 50);
INSERT INTO `employees` VALUES (124, 'Kevin', 'Mourgos', 'KMOURGOS', '650.123.5234', '2007-11-16 00:00:00', 'ST_MAN', 5800.00, NULL, 100, 50);
INSERT INTO `employees` VALUES (125, 'Julia', 'Nayer', 'JNAYER', '650.124.1214', '2005-07-16 00:00:00', 'ST_CLERK', 3200.00, NULL, 120, 50);
INSERT INTO `employees` VALUES (126, 'Irene', 'Mikkilineni', 'IMIKKILI', '650.124.1224', '2006-09-28 00:00:00', 'ST_CLERK', 2700.00, NULL, 120, 50);
INSERT INTO `employees` VALUES (127, 'James', 'Landry', 'JLANDRY', '650.124.1334', '2007-01-14 00:00:00', 'ST_CLERK', 2400.00, NULL, 120, 50);
INSERT INTO `employees` VALUES (128, 'Steven', 'Markle', 'SMARKLE', '650.124.1434', '2008-03-08 00:00:00', 'ST_CLERK', 2200.00, NULL, 120, 50);
INSERT INTO `employees` VALUES (129, 'Laura', 'Bissot', 'LBISSOT', '650.124.5234', '2005-08-20 00:00:00', 'ST_CLERK', 3300.00, NULL, 121, 50);
INSERT INTO `employees` VALUES (130, 'Mozhe', 'Atkinson', 'MATKINSO', '650.124.6234', '2005-10-30 00:00:00', 'ST_CLERK', 2800.00, NULL, 121, 50);
INSERT INTO `employees` VALUES (131, 'James', 'Marlow', 'JAMRLOW', '650.124.7234', '2005-02-16 00:00:00', 'ST_CLERK', 2500.00, NULL, 121, 50);
INSERT INTO `employees` VALUES (132, 'TJ', 'Olson', 'TJOLSON', '650.124.8234', '2007-04-10 00:00:00', 'ST_CLERK', 2100.00, NULL, 121, 50);
INSERT INTO `employees` VALUES (133, 'Jason', 'Mallin', 'JMALLIN', '650.127.1934', '2004-06-14 00:00:00', 'ST_CLERK', 3300.00, NULL, 122, 50);
INSERT INTO `employees` VALUES (134, 'Michael', 'Rogers', 'MROGERS', '650.127.1834', '2006-08-26 00:00:00', 'ST_CLERK', 2900.00, NULL, 122, 50);
INSERT INTO `employees` VALUES (135, 'Ki', 'Gee', 'KGEE', '650.127.1734', '2007-12-12 00:00:00', 'ST_CLERK', 2400.00, NULL, 122, 50);
INSERT INTO `employees` VALUES (136, 'Hazel', 'Philtanker', 'HPHILTAN', '650.127.1634', '2008-02-06 00:00:00', 'ST_CLERK', 2200.00, NULL, 122, 50);
INSERT INTO `employees` VALUES (137, 'Renske', 'Ladwig', 'RLADWIG', '650.121.1234', '2003-07-14 00:00:00', 'ST_CLERK', 3600.00, NULL, 123, 50);
INSERT INTO `employees` VALUES (138, 'Stephen', 'Stiles', 'SSTILES', '650.121.2034', '2005-10-26 00:00:00', 'ST_CLERK', 3200.00, NULL, 123, 50);
INSERT INTO `employees` VALUES (139, 'John', 'Seo', 'JSEO', '650.121.2019', '2006-02-12 00:00:00', 'ST_CLERK', 2700.00, NULL, 123, 50);
INSERT INTO `employees` VALUES (140, 'Joshua', 'Patel', 'JPATEL', '650.121.1834', '2006-04-06 00:00:00', 'ST_CLERK', 2500.00, NULL, 123, 50);
INSERT INTO `employees` VALUES (141, 'Trenna', 'Rajs', 'TRAJS', '650.121.8009', '2003-10-17 00:00:00', 'ST_CLERK', 3500.00, NULL, 124, 50);
INSERT INTO `employees` VALUES (142, 'Curtis', 'Davies', 'CDAVIES', '650.121.2994', '2005-01-29 00:00:00', 'ST_CLERK', 3100.00, NULL, 124, 50);
INSERT INTO `employees` VALUES (143, 'Randall', 'Matos', 'RMATOS', '650.121.2874', '2006-03-15 00:00:00', 'ST_CLERK', 2600.00, NULL, 124, 50);
INSERT INTO `employees` VALUES (144, 'Peter', 'Vargas', 'PVARGAS', '650.121.2004', '2006-07-09 00:00:00', 'ST_CLERK', 2500.00, NULL, 124, 50);
INSERT INTO `employees` VALUES (145, 'John', 'Russell', 'JRUSSEL', '011.44.1344.429268', '2004-10-01 00:00:00', 'SA_MAN', 14000.00, 0.40, 100, 80);
INSERT INTO `employees` VALUES (146, 'Karen', 'Partners', 'KPARTNER', '011.44.1344.467268', '2005-01-05 00:00:00', 'SA_MAN', 13500.00, 0.30, 100, 80);
INSERT INTO `employees` VALUES (147, 'Alberto', 'Errazuriz', 'AERRAZUR', '011.44.1344.429278', '2005-03-10 00:00:00', 'SA_MAN', 12000.00, 0.30, 100, 80);
INSERT INTO `employees` VALUES (148, 'Gerald', 'Cambrault', 'GCAMBRAU', '011.44.1344.619268', '2007-10-15 00:00:00', 'SA_MAN', 11000.00, 0.30, 100, 80);
INSERT INTO `employees` VALUES (149, 'Eleni', 'Zlotkey', 'EZLOTKEY', '011.44.1344.429018', '2008-01-29 00:00:00', 'SA_MAN', 10500.00, 0.20, 100, 80);
INSERT INTO `employees` VALUES (150, 'Peter', 'Tucker', 'PTUCKER', '011.44.1344.129268', '2005-01-30 00:00:00', 'SA_REP', 10000.00, 0.30, 145, 80);
INSERT INTO `employees` VALUES (151, 'David', 'Bernstein', 'DBERNSTE', '011.44.1344.345268', '2005-03-24 00:00:00', 'SA_REP', 9500.00, 0.25, 145, 80);
INSERT INTO `employees` VALUES (152, 'Peter', 'Hall', 'PHALL', '011.44.1344.478968', '2005-08-20 00:00:00', 'SA_REP', 9000.00, 0.25, 145, 80);
INSERT INTO `employees` VALUES (153, 'Christopher', 'Olsen', 'COLSEN', '011.44.1344.498718', '2006-03-30 00:00:00', 'SA_REP', 8000.00, 0.20, 145, 80);
INSERT INTO `employees` VALUES (154, 'Nanette', 'Cambrault', 'NCAMBRAU', '011.44.1344.987668', '2006-12-09 00:00:00', 'SA_REP', 7500.00, 0.20, 145, 80);
INSERT INTO `employees` VALUES (155, 'Oliver', 'Tuvault', 'OTUVAULT', '011.44.1344.486508', '2007-11-23 00:00:00', 'SA_REP', 7000.00, 0.15, 145, 80);
INSERT INTO `employees` VALUES (156, 'Janette', 'King', 'JKING', '011.44.1345.429268', '2004-01-30 00:00:00', 'SA_REP', 10000.00, 0.35, 146, 80);
INSERT INTO `employees` VALUES (157, 'Patrick', 'Sully', 'PSULLY', '011.44.1345.929268', '2004-03-04 00:00:00', 'SA_REP', 9500.00, 0.35, 146, 80);
INSERT INTO `employees` VALUES (158, 'Allan', 'McEwen', 'AMCEWEN', '011.44.1345.829268', '2004-08-01 00:00:00', 'SA_REP', 9000.00, 0.35, 146, 80);
INSERT INTO `employees` VALUES (159, 'Lindsey', 'Smith', 'LSMITH', '011.44.1345.729268', '2005-03-10 00:00:00', 'SA_REP', 8000.00, 0.30, 146, 80);
INSERT INTO `employees` VALUES (160, 'Louise', 'Doran', 'LDORAN', '011.44.1345.629268', '2005-12-15 00:00:00', 'SA_REP', 7500.00, 0.30, 146, 80);
INSERT INTO `employees` VALUES (161, 'Sarath', 'Sewall', 'SSEWALL', '011.44.1345.529268', '2006-11-03 00:00:00', 'SA_REP', 7000.00, 0.25, 146, 80);
INSERT INTO `employees` VALUES (162, 'Clara', 'Vishney', 'CVISHNEY', '011.44.1346.129268', '2005-11-11 00:00:00', 'SA_REP', 10500.00, 0.25, 147, 80);
INSERT INTO `employees` VALUES (163, 'Danielle', 'Greene', 'DGREENE', '011.44.1346.229268', '2007-03-19 00:00:00', 'SA_REP', 9500.00, 0.15, 147, 80);
INSERT INTO `employees` VALUES (164, 'Mattea', 'Marvins', 'MMARVINS', '011.44.1346.329268', '2008-01-24 00:00:00', 'SA_REP', 7200.00, 0.10, 147, 80);
INSERT INTO `employees` VALUES (165, 'David', 'Lee', 'DLEE', '011.44.1346.529268', '2008-02-23 00:00:00', 'SA_REP', 6800.00, 0.10, 147, 80);
INSERT INTO `employees` VALUES (166, 'Sundar', 'Ande', 'SANDE', '011.44.1346.629268', '2008-03-24 00:00:00', 'SA_REP', 6400.00, 0.10, 147, 80);
INSERT INTO `employees` VALUES (167, 'Amit', 'Banda', 'ABANDA', '011.44.1346.729268', '2008-04-21 00:00:00', 'SA_REP', 6200.00, 0.10, 147, 80);
INSERT INTO `employees` VALUES (168, 'Lisa', 'Ozer', 'LOZER', '011.44.1343.929268', '2005-03-11 00:00:00', 'SA_REP', 11500.00, 0.25, 148, 80);
INSERT INTO `employees` VALUES (169, 'Harrison', 'Bloom', 'HBLOOM', '011.44.1343.829268', '2006-03-23 00:00:00', 'SA_REP', 10000.00, 0.20, 148, 80);
INSERT INTO `employees` VALUES (170, 'Tayler', 'Fox', 'TFOX', '011.44.1343.729268', '2006-01-24 00:00:00', 'SA_REP', 9600.00, 0.20, 148, 80);
INSERT INTO `employees` VALUES (171, 'William', 'Smith', 'WSMITH', '011.44.1343.629268', '2007-02-23 00:00:00', 'SA_REP', 7400.00, 0.15, 148, 80);
INSERT INTO `employees` VALUES (172, 'Elizabeth', 'Bates', 'EBATES', '011.44.1343.529268', '2007-03-24 00:00:00', 'SA_REP', 7300.00, 0.15, 148, 80);
INSERT INTO `employees` VALUES (173, 'Sundita', 'Kumar', 'SKUMAR', '011.44.1343.329268', '2008-04-21 00:00:00', 'SA_REP', 6100.00, 0.10, 148, 80);
INSERT INTO `employees` VALUES (174, 'Ellen', 'Abel', 'EABEL', '011.44.1644.429267', '2004-05-11 00:00:00', 'SA_REP', 11000.00, 0.30, 149, 80);
INSERT INTO `employees` VALUES (175, 'Alyssa', 'Hutton', 'AHUTTON', '011.44.1644.429266', '2005-03-19 00:00:00', 'SA_REP', 8800.00, 0.25, 149, 80);
INSERT INTO `employees` VALUES (176, 'Jonathon', 'Taylor', 'JTAYLOR', '011.44.1644.429265', '2006-03-24 00:00:00', 'SA_REP', 8600.00, 0.20, 149, 80);
INSERT INTO `employees` VALUES (177, 'Jack', 'Livingston', 'JLIVINGS', '011.44.1644.429264', '2006-04-23 00:00:00', 'SA_REP', 8400.00, 0.20, 149, 80);
INSERT INTO `employees` VALUES (178, 'Kimberely', 'Grant', 'KGRANT', '011.44.1644.429263', '2007-05-24 00:00:00', 'SA_REP', 7000.00, 0.15, 149, NULL);
INSERT INTO `employees` VALUES (179, 'Charles', 'Johnson', 'CJOHNSON', '011.44.1644.429262', '2008-01-04 00:00:00', 'SA_REP', 6200.00, 0.10, 149, 80);
INSERT INTO `employees` VALUES (180, 'Winston', 'Taylor', 'WTAYLOR', '650.507.9876', '2006-01-24 00:00:00', 'SH_CLERK', 3200.00, NULL, 120, 50);
INSERT INTO `employees` VALUES (181, 'Jean', 'Fleaur', 'JFLEAUR', '650.507.9877', '2006-02-23 00:00:00', 'SH_CLERK', 3100.00, NULL, 120, 50);
INSERT INTO `employees` VALUES (182, 'Martha', 'Sullivan', 'MSULLIVA', '650.507.9878', '2007-06-21 00:00:00', 'SH_CLERK', 2500.00, NULL, 120, 50);
INSERT INTO `employees` VALUES (183, 'Girard', 'Geoni', 'GGEONI', '650.507.9879', '2008-02-03 00:00:00', 'SH_CLERK', 2800.00, NULL, 120, 50);
INSERT INTO `employees` VALUES (184, 'Nandita', 'Sarchand', 'NSARCHAN', '650.509.1876', '2004-01-27 00:00:00', 'SH_CLERK', 4200.00, NULL, 121, 50);
INSERT INTO `employees` VALUES (185, 'Alexis', 'Bull', 'ABULL', '650.509.2876', '2005-02-20 00:00:00', 'SH_CLERK', 4100.00, NULL, 121, 50);
INSERT INTO `employees` VALUES (186, 'Julia', 'Dellinger', 'JDELLING', '650.509.3876', '2006-06-24 00:00:00', 'SH_CLERK', 3400.00, NULL, 121, 50);
INSERT INTO `employees` VALUES (187, 'Anthony', 'Cabrio', 'ACABRIO', '650.509.4876', '2007-02-07 00:00:00', 'SH_CLERK', 3000.00, NULL, 121, 50);
INSERT INTO `employees` VALUES (188, 'Kelly', 'Chung', 'KCHUNG', '650.505.1876', '2005-06-14 00:00:00', 'SH_CLERK', 3800.00, NULL, 122, 50);
INSERT INTO `employees` VALUES (189, 'Jennifer', 'Dilly', 'JDILLY', '650.505.2876', '2005-08-13 00:00:00', 'SH_CLERK', 3600.00, NULL, 122, 50);
INSERT INTO `employees` VALUES (190, 'Timothy', 'Gates', 'TGATES', '650.505.3876', '2006-07-11 00:00:00', 'SH_CLERK', 2900.00, NULL, 122, 50);
INSERT INTO `employees` VALUES (191, 'Randall', 'Perkins', 'RPERKINS', '650.505.4876', '2007-12-19 00:00:00', 'SH_CLERK', 2500.00, NULL, 122, 50);
INSERT INTO `employees` VALUES (192, 'Sarah', 'Bell', 'SBELL', '650.501.1876', '2004-02-04 00:00:00', 'SH_CLERK', 4000.00, NULL, 123, 50);
INSERT INTO `employees` VALUES (193, 'Britney', 'Everett', 'BEVERETT', '650.501.2876', '2005-03-03 00:00:00', 'SH_CLERK', 3900.00, NULL, 123, 50);
INSERT INTO `employees` VALUES (194, 'Samuel', 'McCain', 'SMCCAIN', '650.501.3876', '2006-07-01 00:00:00', 'SH_CLERK', 3200.00, NULL, 123, 50);
INSERT INTO `employees` VALUES (195, 'Vance', 'Jones', 'VJONES', '650.501.4876', '2007-03-17 00:00:00', 'SH_CLERK', 2800.00, NULL, 123, 50);
INSERT INTO `employees` VALUES (196, 'Alana', 'Walsh', 'AWALSH', '650.507.9811', '2006-04-24 00:00:00', 'SH_CLERK', 3100.00, NULL, 124, 50);
INSERT INTO `employees` VALUES (197, 'Kevin', 'Feeney', 'KFEENEY', '650.507.9822', '2006-05-23 00:00:00', 'SH_CLERK', 3000.00, NULL, 124, 50);
INSERT INTO `employees` VALUES (198, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', '2007-06-21 00:00:00', 'SH_CLERK', 2600.00, NULL, 124, 50);
INSERT INTO `employees` VALUES (199, 'Douglas', 'Grant', 'DGRANT', '650.507.9844', '2008-01-13 00:00:00', 'SH_CLERK', 2600.00, NULL, 124, 50);
INSERT INTO `employees` VALUES (200, 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444', '2003-09-17 00:00:00', 'AD_ASST', 4400.00, NULL, 101, 10);
INSERT INTO `employees` VALUES (201, 'Michael', 'Hartstein', 'MHARTSTE', '515.123.5555', '2004-02-17 00:00:00', 'MK_MAN', 13000.00, NULL, 100, 20);
INSERT INTO `employees` VALUES (202, 'Pat', 'Fay', 'PFAY', '603.123.6666', '2005-08-17 00:00:00', 'MK_REP', 6000.00, NULL, 201, 20);
INSERT INTO `employees` VALUES (203, 'Susan', 'Mavris', 'SMAVRIS', '515.123.7777', '2002-06-07 00:00:00', 'HR_REP', 6500.00, NULL, 101, 40);
INSERT INTO `employees` VALUES (204, 'Hermann', 'Baer', 'HBAER', '515.123.8888', '2002-06-07 00:00:00', 'PR_REP', 10000.00, NULL, 101, 70);
INSERT INTO `employees` VALUES (205, 'Shelley', 'Higgins', 'SHIGGINS', '515.123.8080', '2002-06-07 00:00:00', 'AC_MGR', 12008.00, NULL, 101, 110);
INSERT INTO `employees` VALUES (206, 'William', 'Gietz', 'WGIETZ', '515.123.8181', '2002-06-07 00:00:00', 'AC_ACCOUNT', 8300.00, NULL, 205, 110);
-- ----------------------------
-- Table structure for job_history
-- ----------------------------
DROP TABLE IF EXISTS `job_history`;
CREATE TABLE `job_history` (
`EMPLOYEE_ID` int NOT NULL COMMENT 'A not null column in the complex primary key employee_id+start_date.\nForeign key to employee_id column of the employee table',
`START_DATE` datetime NOT NULL COMMENT 'A not null column in the complex primary key employee_id+start_date.\nMust be less than the end_date of the job_history table. (enforced by\nconstraint jhist_date_interval)',
`END_DATE` datetime NOT NULL COMMENT 'Last day of the employee in this job role. A not null column. Must be\ngreater than the start_date of the job_history table.\n(enforced by constraint jhist_date_interval)',
`JOB_ID` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Job role in which the employee worked in the past; foreign key to\njob_id column in the jobs table. A not null column.',
`DEPARTMENT_ID` int NULL DEFAULT NULL COMMENT 'Department id in which the employee worked in the past; foreign key to deparment_id column in the departments table',
PRIMARY KEY (`EMPLOYEE_ID`, `START_DATE`) USING BTREE,
INDEX `JHIST_DEPARTMENT_IX`(`DEPARTMENT_ID`) USING BTREE,
INDEX `JHIST_EMPLOYEE_IX`(`EMPLOYEE_ID`) USING BTREE,
INDEX `JHIST_JOB_IX`(`JOB_ID`) USING BTREE,
CONSTRAINT `JHIST_DEPT_FK` FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES `departments` (`DEPARTMENT_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `JHIST_EMP_FK` FOREIGN KEY (`EMPLOYEE_ID`) REFERENCES `employees` (`EMPLOYEE_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `JHIST_JOB_FK` FOREIGN KEY (`JOB_ID`) REFERENCES `jobs` (`JOB_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'Table that stores job history of the employees. If an employee\nchanges departments within the job or changes jobs within the department,\nnew rows get inserted into this table with old job information of the\nemployee. Contains a complex primary key: employee_id+start_date.\nContains 25 rows. References with jobs, employees, and departments tables.' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of job_history
-- ----------------------------
INSERT INTO `job_history` VALUES (101, '1997-09-21 00:00:00', '2001-10-27 00:00:00', 'AC_ACCOUNT', 110);
INSERT INTO `job_history` VALUES (101, '2001-10-28 00:00:00', '2005-03-15 00:00:00', 'AC_MGR', 110);
INSERT INTO `job_history` VALUES (102, '2001-01-13 00:00:00', '2006-07-24 00:00:00', 'IT_PROG', 60);
INSERT INTO `job_history` VALUES (114, '2006-03-24 00:00:00', '2007-12-31 00:00:00', 'ST_CLERK', 50);
INSERT INTO `job_history` VALUES (122, '2007-01-01 00:00:00', '2007-12-31 00:00:00', 'ST_CLERK', 50);
INSERT INTO `job_history` VALUES (176, '2006-03-24 00:00:00', '2006-12-31 00:00:00', 'SA_REP', 80);
INSERT INTO `job_history` VALUES (176, '2007-01-01 00:00:00', '2007-12-31 00:00:00', 'SA_MAN', 80);
INSERT INTO `job_history` VALUES (200, '1995-09-17 00:00:00', '2001-06-17 00:00:00', 'AD_ASST', 90);
INSERT INTO `job_history` VALUES (200, '2002-07-01 00:00:00', '2006-12-31 00:00:00', 'AC_ACCOUNT', 90);
INSERT INTO `job_history` VALUES (201, '2004-02-17 00:00:00', '2007-12-19 00:00:00', 'MK_REP', 20);
-- ----------------------------
-- Table structure for jobs
-- ----------------------------
DROP TABLE IF EXISTS `jobs`;
CREATE TABLE `jobs` (
`JOB_ID` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Primary key of jobs table.',
`JOB_TITLE` varchar(35) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'A not null column that shows job title, e.g. AD_VP, FI_ACCOUNTANT',
`MIN_SALARY` int NULL DEFAULT NULL COMMENT 'Minimum salary for a job title.',
`MAX_SALARY` int NULL DEFAULT NULL COMMENT 'Maximum salary for a job title',
PRIMARY KEY (`JOB_ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'jobs table with job titles and salary ranges. Contains 19 rows.\nReferences with employees and job_history table.' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of jobs
-- ----------------------------
INSERT INTO `jobs` VALUES ('AC_ACCOUNT', 'Public Accountant', 4200, 9000);
INSERT INTO `jobs` VALUES ('AC_MGR', 'Accounting Manager', 8200, 16000);
INSERT INTO `jobs` VALUES ('AD_ASST', 'Administration Assistant', 3000, 6000);
INSERT INTO `jobs` VALUES ('AD_PRES', 'President', 20080, 40000);
INSERT INTO `jobs` VALUES ('AD_VP', 'Administration Vice President', 15000, 30000);
INSERT INTO `jobs` VALUES ('FI_ACCOUNT', 'Accountant', 4200, 9000);
INSERT INTO `jobs` VALUES ('FI_MGR', 'Finance Manager', 8200, 16000);
INSERT INTO `jobs` VALUES ('HR_REP', 'Human Resources Representative', 4000, 9000);
INSERT INTO `jobs` VALUES ('IT_PROG', 'Programmer', 4000, 10000);
INSERT INTO `jobs` VALUES ('MK_MAN', 'Marketing Manager', 9000, 15000);
INSERT INTO `jobs` VALUES ('MK_REP', 'Marketing Representative', 4000, 9000);
INSERT INTO `jobs` VALUES ('PR_REP', 'Public Relations Representative', 4500, 10500);
INSERT INTO `jobs` VALUES ('PU_CLERK', 'Purchasing Clerk', 2500, 5500);
INSERT INTO `jobs` VALUES ('PU_MAN', 'Purchasing Manager', 8000, 15000);
INSERT INTO `jobs` VALUES ('SA_MAN', 'Sales Manager', 10000, 20080);
INSERT INTO `jobs` VALUES ('SA_REP', 'Sales Representative', 6000, 12008);
INSERT INTO `jobs` VALUES ('SH_CLERK', 'Shipping Clerk', 2500, 5500);
INSERT INTO `jobs` VALUES ('ST_CLERK', 'Stock Clerk', 2008, 5000);
INSERT INTO `jobs` VALUES ('ST_MAN', 'Stock Manager', 5500, 8500);
-- ----------------------------
-- Table structure for locations
-- ----------------------------
DROP TABLE IF EXISTS `locations`;
CREATE TABLE `locations` (
`LOCATION_ID` int NOT NULL COMMENT 'Primary key of locations table',
`STREET_ADDRESS` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Street address of an office, warehouse, or production site of a company.\nContains building number and street name',
`POSTAL_CODE` varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Postal code of the location of an office, warehouse, or production site\nof a company. ',
`CITY` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'A not null column that shows city where an office, warehouse, or\nproduction site of a company is located. ',
`STATE_PROVINCE` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'State or Province where an office, warehouse, or production site of a\ncompany is located.',
`COUNTRY_ID` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Country where an office, warehouse, or production site of a company is\nlocated. Foreign key to country_id column of the countries table.',
PRIMARY KEY (`LOCATION_ID`) USING BTREE,
INDEX `LOC_CITY_IX`(`CITY`) USING BTREE,
INDEX `LOC_COUNTRY_IX`(`COUNTRY_ID`) USING BTREE,
INDEX `LOC_STATE_PROVINCE_IX`(`STATE_PROVINCE`) USING BTREE,
CONSTRAINT `LOC_C_ID_FK` FOREIGN KEY (`COUNTRY_ID`) REFERENCES `countries` (`COUNTRY_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'Locations table that contains specific address of a specific office,\nwarehouse, and/or production site of a company. Does not store addresses /\nlocations of customers. Contains 23 rows; references with the\ndepartments and countries tables. ' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of locations
-- ----------------------------
INSERT INTO `locations` VALUES (1000, '1297 Via Cola di Rie', '00989', 'Roma', NULL, 'IT');
INSERT INTO `locations` VALUES (1100, '93091 Calle della Testa', '10934', 'Venice', NULL, 'IT');
INSERT INTO `locations` VALUES (1200, '2017 Shinjuku-ku', '1689', 'Tokyo', 'Tokyo Prefecture', 'JP');
INSERT INTO `locations` VALUES (1300, '9450 Kamiya-cho', '6823', 'Hiroshima', NULL, 'JP');
INSERT INTO `locations` VALUES (1400, '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US');
INSERT INTO `locations` VALUES (1500, '2011 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US');
INSERT INTO `locations` VALUES (1600, '2007 Zagora St', '50090', 'South Brunswick', 'New Jersey', 'US');
INSERT INTO `locations` VALUES (1700, '2004 Charade Rd', '98199', 'Seattle', 'Washington', 'US');
INSERT INTO `locations` VALUES (1800, '147 Spadina Ave', 'M5V 2L7', 'Toronto', 'Ontario', 'CA');
INSERT INTO `locations` VALUES (1900, '6092 Boxwood St', 'YSW 9T2', 'Whitehorse', 'Yukon', 'CA');
INSERT INTO `locations` VALUES (2000, '40-5-12 Laogianggen', '190518', 'Beijing', NULL, 'CN');
INSERT INTO `locations` VALUES (2100, '1298 Vileparle (E)', '490231', 'Bombay', 'Maharashtra', 'IN');
INSERT INTO `locations` VALUES (2200, '12-98 Victoria Street', '2901', 'Sydney', 'New South Wales', 'AU');
INSERT INTO `locations` VALUES (2300, '198 Clementi North', '540198', 'Singapore', NULL, 'SG');
INSERT INTO `locations` VALUES (2400, '8204 Arthur St', NULL, 'London', NULL, 'UK');
INSERT INTO `locations` VALUES (2500, 'Magdalen Centre, The Oxford Science Park', 'OX9 9ZB', 'Oxford', 'Oxford', 'UK');
INSERT INTO `locations` VALUES (2600, '9702 Chester Road', '09629850293', 'Stretford', 'Manchester', 'UK');
INSERT INTO `locations` VALUES (2700, 'Schwanthalerstr. 7031', '80925', 'Munich', 'Bavaria', 'DE');
INSERT INTO `locations` VALUES (2800, 'Rua Frei Caneca 1360 ', '01307-002', 'Sao Paulo', 'Sao Paulo', 'BR');
INSERT INTO `locations` VALUES (2900, '20 Rue des Corps-Saints', '1730', 'Geneva', 'Geneve', 'CH');
INSERT INTO `locations` VALUES (3000, 'Murtenstrasse 921', '3095', 'Bern', 'BE', 'CH');
INSERT INTO `locations` VALUES (3100, 'Pieter Breughelstraat 837', '3029SK', 'Utrecht', 'Utrecht', 'NL');
INSERT INTO `locations` VALUES (3200, 'Mariano Escobedo 9991', '11932', 'Mexico City', 'Distrito Federal,', 'MX');
-- ----------------------------
-- Table structure for regions
-- ----------------------------
DROP TABLE IF EXISTS `regions`;
CREATE TABLE `regions` (
`REGION_ID` int NOT NULL,
`REGION_NAME` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`REGION_ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of regions
-- ----------------------------
INSERT INTO `regions` VALUES (1.000000000000000000000000000000, 'Europe');
INSERT INTO `regions` VALUES (2.000000000000000000000000000000, 'Americas');
INSERT INTO `regions` VALUES (3.000000000000000000000000000000, 'Asia');
INSERT INTO `regions` VALUES (4.000000000000000000000000000000, 'Middle East and Africa');
SET FOREIGN_KEY_CHECKS = 1;
第二批 50道 sql查询综合练习题
表数据,数据纯属杜撰，仅做查询练习使用
create database db2;
use db2;
-- 数据表介绍
--1.学生表
Student (SId, Sname, Sage, Ssex)
--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
--2.课程表
Course (CId, Cname, TId)
--CId 课程编号,Cname 课程名称,TId 教师编号
--3.教师表
Teacher (TId, Tname)
--TId 教师编号,Tname 教师姓名
--4.成绩表
SC (SId, CId, score)
--SId 学生编号,CId 课程编号,score 分数
-- 学生表 Student
create table Student (
SId varchar(10),
Sname varchar(10),
Sage datetime,
Ssex varchar(10)
);
insert into Student values ('01', '赵雷', '1990-01-01', '男');
insert into Student values ('02', '钱电', '1990-12-21', '男');
insert into Student values ('03', '孙风', '1990-12-20', '男');
insert into Student values ('04', '李云', '1990-12-06', '男');
insert into Student values ('05', '周梅', '1991-12-01', '女');
insert into Student values ('06', '吴兰', '1992-01-01', '女');
insert into Student values ('07', '郑竹', '1989-01-01', '女');
insert into Student values ('09', '张三', '2017-12-20', '女');
insert into Student values ('10', '李四', '2017-12-25', '女');
insert into Student values ('11', '李四', '2012-06-06', '女');
insert into Student values ('12', '赵六', '2013-06-13', '女');
insert into Student values ('13', '孙七', '2014-06-01', '女');
-- 科目表 Course
create table Course (
CId varchar(10),
Cname nvarchar (10),
TId varchar(10)
);
insert into Course values ('01', '语文', '02');
insert into Course values ('02', '数学', '01');
insert into Course values ('03', '英语', '03');
-- 教师表 Teacher
create table Teacher ( TId varchar(10), Tname varchar(10) );
insert into Teacher values ('01', '张三');
insert into Teacher values ('02', '李四');
insert into Teacher values ('03', '王五');
-- 成绩表 SC
create table SC (
SId varchar(10),
CId varchar(10),
score decimal(18, 1)
);
insert into SC values ('01', '01', 80);
insert into SC values ('01', '02', 90);
insert into SC values ('01', '03', 99);
insert into SC values ('02', '01', 70);
insert into SC values ('02', '02', 60);
insert into SC values ('02', '03', 80);
insert into SC values ('03', '01', 80);
insert into SC values ('03', '02', 80);
insert into SC values ('03', '03', 80);
insert into SC values ('04', '01', 50);
insert into SC values ('04', '02', 30);
insert into SC values ('04', '03', 20);
insert into SC values ('05', '01', 76);
insert into SC values ('05', '02', 87);
insert into SC values ('06', '01', 31);
insert into SC values ('06', '03', 34);
insert into SC values ('07', '02', 89);
insert into SC values ('07', '03', 98);-- 101.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
/*
01课程是啥 课程表 Cname语文
02课程是啥 课程表中 cname数学
01课程的学生是啥 学生表 成绩表 课程表
02课程的学生是啥
自链接 然后查询到 cid是01 cid是02的时候 01 成绩 比 02的成绩要高
然后连接学生表 获取学生的信息
*/
select * from sc s1 ,sc s2 ,student s3
where s1.sid =s2.sid
and s1.cid ='01'
and s2.`CId`=02
and s3.`SId`=s1.sid
and s1.score>s2.score;-- 102.查询同时存在" 01 "课程和" 02 "课程的情况
/*
同时存在01和02的课程是什么课程
成绩表 包含01和02
*/
*/
select * from sc s1,sc s2 where s1.`SId`=s2.`SId`
and s1.`CId`=01
and s2.cid=02
;-- 103 .查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
/*
01的课程存在 02课程不存在
*/
select * from sc where `CId`=01;
-- 课程存在01的表里面
-- 01的表包含02的表
select * from sc where `CId`=02;
select * from
(select * from sc where `CId` = 01) t1
LEFT JOIN
(select * from sc where `CId` = 02) t2
on t1.`SId`=t2.sid;
-- 104.查询不存在" 01 "课程但存在" 02 "课程的情况
select * from
(select * from sc where `CId` = 02) t1
LEFT JOIN
(select * from sc where `CId` = 01) t2
on t1.`SId`=t2.sid;
-- 105.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
/*
字段 学生编号 成绩表 学生姓名 需要学生表 平均成绩 需要成绩表
条件 平均成绩大于60分
*/
select s1.sid ,s2.sname,avg(s1.score) from sc s1,student s2 where s1.SId=s2.SId
group by s1.sid,s2.sname having avg(s1.score)>60 ;
-- 106.查询在 SC 表存在成绩的学生信息
# 要啥 学生表中的学生信息
#条件 score在成绩表存在 不是null
# 满足条件的查询语句
select * from sc where score is not null;
select * from student where sid in (select sid from sc where score is not null);
#用连接
SELECT distinct s.*
FROM student s
INNER JOIN sc ON s.sid = sc.sid;
-- 107.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
# 要啥 要同学的学生编号 在学生表里 学生姓名
# 所有同学 同学分组 聚聚合选课总数 sum成绩总数
-- 赵雷 来分析 他选课了三门 他的课程总数是3 他的课程总成绩也是80+90+99 269
select s1.Sname,count(cid),sum(score) from student s1,sc s2 where s1.SId=s2.SId group by s1.Sname;
-- 108.显示没选课的学生(显示为NULL)
## 学生表 有学生 成绩表总有课程cid 当cid是null的时候
select distinct s1.* from student s1 left join sc s2 on s1.SId=s2.SId
where s2.CID is null;
-- 109.查有成绩的学生信息
select distinct s1.* from student s1 left join sc s2 on s1.SId=s2.SId
where s2.score is not null;
-- 110.查询「李」姓老师的数量
select count(Tname) from teacher where Tname like '李%';-- 111.查询学过「张三」老师授课的同学的信息
#要啥 要学生的信息 学生的信息从哪里来 学生表里面来
# 条件 学过张三老师 已知张三的名字 求他的课程 已知道张三的课程 id 求他的cid 已知道cid 求sid 已知道sid求sid的全部信息
#先上连接试试
select s1.* from student s1,sc s2,course c,teacher t
where s1.SId=s2.SId
and s2.CId=c.CId
and c.TId=t.TId
and t.Tname ='张三';-- 112.查询没有学全所有课程的同学的信息
/*
没有学完所有课程的同学信息
列 学生表 学生信息
条件 成绩表有课程id 学完全部包含01和02和03的
*/
#拿到课程表的全部课程
select distinct cid from sc;
# 学生表和成绩表
#表和表之间的连接 学生表包括课程表
select s1.Sname
from student s1 join sc s2 on s1.SId=s2.sId
group by s1.Sname
having count(s2.CId) != (select count(cid) from course);
#根据学生名字查询学生全部信息
select * from student where Sname in (
select s1.Sname
from student s1 join sc s2 on s1.SId=s2.sId
group by s1.Sname
having count(s2.CId) != (select count(cid) from course)
);-- 113.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
/* 要同学信息 学生表 学生列
条件 学号是01的学生 课程cid跟他相等
*/
#学号是01的同学
select sid from student where sid =01;
#学号 01的cid是啥
select cid from student s1 join sc s2 on s1.SId=s2.SId
where s1.SId='01';
#根据cid 求sid 然后拿到学生信息
select distinct s1.* from student s1,sc s2
where s1.SId=s2.SId
and s2.CId in(select cid from student s1 join sc s2 on s1.SId=s2.SId
where s1.SId='01');-- 114.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
-- 解法一
#继续上题的思路 学号是01 他的课程总数是3 如果其他同学课程总数也是3 那么就相同了
select s1.Sname from student s1,sc s2
where s1.SId=s2.SId
group by s1.Sname
having count(s2.sid) = (select count(cid) from student s1 join sc s2 on s1.SId=s2.SId
where s1.SId='01');
#根据名称查询全部信息
select * from student
where Sname in
(select s1.Sname from student s1,sc s2
where s1.SId=s2.SId
group by s1.Sname
having count(s2.sid) = (select count(cid) from student s1 join sc s2 on s1.SId=s2.SId
where s1.SId='01'));
-- 解法二
# 先拿出来 学号是01的同学 他的全部课程 group_concat 可以把一列拼接成一个字符串
select group_concat(cid) from student s1,sc s2 where s1.SId=s2.SId
and s1.SId ='01';
select cid from student s1,sc s2 where s1.SId=s2.SId
and s1.SId ='01';
-- 更严谨一点
select s1.Sname,group_concat(s2.cid) from student s1,sc s2 where s1.SId=s2.SId
group by s1.Sname
having group_concat(s2.cid) =(select group_concat(cid) from student s1,sc s2 where s1.SId=s2.SId
and s1.SId ='01');
#最终结果
select * from student
where
Sname in (
select s1.Sname from student s1,sc s2 where s1.SId=s2.SId
group by s1.Sname
having group_concat(cid) =
(select group_concat(cid) from student s1,sc s2 where s1.SId=s2.SId and s1.SId ='01')
)
and SId != '01';
-- 115.查询没学过"张三"老师讲授的任一门课程的学生姓名
# 要学生信息 学生表 字段 学生信息
#条件 没有学过张三老师传授的课
# 张三老师教授的是啥课？
select TId from teacher where Tname='张三';
-- 张三老师 id 是 01 01都三了哪些课 张三是教授 数学的 他的cid是02
select c1.CId,c1.Cname from teacher t1,course c1 where t1.TId=c1.TId AND T1.Tname='张三';
-- 有哪些学生 他们的cid 是02的 以下学生
select Sname from student s1,sc s2 where s1.SId=s2.SId
and s2.CId = (select c1.CId from teacher t1,course c1 where t1.TId=c1.TId AND T1.Tname='张三');
-- 那么除了他们 其他人都没学过嘛
select * from student
where Sname not in
( select Sname from student s1,sc s2
where s1.SId=s2.SId and s2.CId = (select c1.CId from teacher t1,course c1 where t1.TId=c1.TId AND T1.Tname='张三'));
-- 116.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
# 字段 学生表的学号 sid sname avg成绩
# 学生表 成绩表
# 要求 2门以及2门以上 都不即可 分数<60
-- 先拿到不及格的分数把 找到了不及格的学生学号
select sid from sc where score<=60 group by sid having count(cid)>=2;
# 根据学号 查询学生的信息 姓名 以及平均分
select s1.SId,s1.Sname, avg(score) from student s1,sc s2
where s1.SId=s2.SId
and s1.SId in (select sid from sc where score<=60 group by sid having count(cid)>=2)
group by s1.SId,Sname;-- 117.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
# 字段 学生信息 成绩表 和学生表
#要求 01课程 分数小于60的 分数降序排列
# 根据这2个条件拿到了 sid的信息
select * from sc where cid ='01' and score<60;
#根据sid 来查询学生信息
select * from sc s1,student s2 where s1.SId=s2.SId and s1.cid ='01' and s1.score<60 order by score desc ;
-- 118.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
# 平均成绩 从高到低
# 所有学生 学生分组 所有课程 课程分组
# 按照平均成绩排序
select avg(score) from sc group by SId ,CId order by avg(score) desc ;
select * from (
select SId,CId,score,avg(score) over(partition by SId order by score desc rows between unbounded preceding and unbounded following) rn
from sc) t1 order by t1.rn desc;
select SId,
CId,
score,
avg(score) over(partition by sid) rn
from sc order by rn desc ;-- 119.查询各科成绩最高分、最低分和平均分以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
-- 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select s1.cid, c1.Cname,count(s1.SId) 选修人数,
max(s1.score),min(s1.score),avg(s1.score),
concat( round( count((case when s1.score>=60 then '及格' end))/count(s1.SId),2)*100,'%') jige,
concat( round( count((case when s1.score>=70 and s1.score<80 then '中等' end))/count(s1.SId)*100,2),'%') zhongdeng,
concat( round( count((case when s1.score>=80 and s1.score<90 then '优良' end))/count(s1.SId),2)*100,'%') youliang,
concat( round( count((case when s1.score>=90 then '优秀' end))/count(s1.SId),2)*100,'%') youxiu
from sc s1,course c1
where s1.CId=c1.CId
group by s1.cid ,c1.Cname order by 选修人数,s1.CId;
-- 120.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
-- // 字段要啥 各科的成绩
-- // 要求 排名 重复的时候保留空缺
-- 各科成绩 分组 以科目分组 cid
select
sid,
cid,
score,
rank() over (partition by cid order by score desc )
from sc;
-- 121.查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
-- 要总成绩
select sid,sum(score),
dense_rank() over (partition by sid order by score)
from sc;-- 122.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
# 语文 6个人学习 01 语文
## 各科成绩 成绩分组 cid
## 分数段 成绩分组 在以分数段分组
#要的列 课程表 编号 和名称
# 分数表 分数
###思路 窗口函数 科目表 查看 科目 成绩 信息 按照科目分组
select s1.CId,count(s1.SId) ,c2.Cname,
concat( round(count(case when s1.score<=100 and s1.score>85 then '1' end)/count(s1.SId)*100,2),'%') levle1,
concat( round(count(case when s1.score<=85 and s1.score>70 then '2' end)/count(s1.SId)*100,2),'%') level2,
concat( round(count(case when s1.score<=70 and s1.score>60 then '3' end)/count(s1.SId),2)*100,'%') level3,
concat( round(count(case when s1.score<=60 and s1.score>0 then '4' end)/count(s1.SId),2)*100,'%') level4
from sc s1,course c2 where s1.CId=c2.cid group by s1.CId,c2.Cname;
###
#测试 百分比
select count(case when SC.score<100 and score>85 then '1' end) from SC;-- 123.查询各科成绩前三名的记录
###经典案例 分组求topN
### 用窗口函数 分组求前三
### 然后外面在套一层壳
select * from (
select
sid,
cid,
score,
row_number() over (partition by cid order by score desc) rn
from sc) t2 where t2.rn<=3;
-- 124.查询每门课程被选修的学生数
###成绩表 根据课程分组 count(学生人数)
select cid,count(sid) from sc group by cid;-- 125.查询出只选修两门课程的学生学号和姓名
## 每个学生选修的科目 count 根据学生分表 countcid
select sid from sc group by sid having count(cid) =2;
## 根据学生id查询信息
select * from student where SId in(select sid from sc group by sid having count(cid) =2);
-- 126.查询男生、女生人数
select Ssex, count(Ssex) from student group by Ssex;-- 127.查询名字中含有「风」字的学生信息
select * from student where Sname like '%风%';-- 128.查询同名学生名单，并统计同名人数
select Sname,count(Sname) as cs from student group by Sname having count(Sname)>1;
-- 129.查询 1990 年出生的学生名单
select * from student where year(Sage)='1990';-- 130.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
### 不同课程 成绩表 课程分组 求平局成绩 排序 按照平均成绩
select cid,avg(score) from sc group by cid order by avg(score) desc,cid;
-- 131.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select s1.SId,s1.Sname,avg(s2.score) from student s1,sc s2 where s1.sid=s2.sid group by s1.SId,s1.Sname having avg(s2.score)>85;
-- 132.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select s2.Sname,s1.score from sc s1 ,student s2,course c1
where s1.SId=s2.SId
and s1.CId=c1.CId
and c1.Cname ='数学'
and s1.score <60;
-- 133.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select * from student s1 left join sc s2 on s1.SId=s2.SId;-- 134.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select * from sc where score>70;
select s2.Sname,c1.Cname,s1.score from sc s1,student s2,course c1 where
s1.SId=s2.SId
and s1.CId=c1.CId
and s1.score>70;
-- 135.查询存在不及格的课程
select s1.cid,c1.Cname,s1.score from sc s1,course c1 where s1.CId=c1.CId and score<=60;-- 136.查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名
## 学生表 学号 和姓名
## 条件 课程表课程编号是 01 课程成绩是80
select * from sc s1,student s2 where s1.SId=s2.SId and s1.CId= '01' and s1.score>=80;
select * from sc where CId ='01';-- 137.求每门课程的学生人数
select cid,count(sid) from sc group by cid;-- 138.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
##要啥字段 要学生信息 和成绩
## 条件 张三老师授课的
##条件 成绩最高的 max 学生
select s1.*,s2.score from student s1,sc s2,course c1,teacher t1
where s1.SId =s2.SId
and s2.CId=c1.CId
and c1.TId=t1.TId
and t1.Tname='张三'
order by score desc limit 1;-- 139.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select * from (select s1.*,s2.score ,
rank() over (order by s2.score desc ) rn
from student s1,sc s2,course c1,teacher t1
where s1.SId =s2.SId
and s2.CId=c1.CId
and c1.TId=t1.TId
and t1.Tname='张三'
order by score desc) t1 where t1.rn=1;-- 140.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select t2.cd from (select score,count(cid) as cd from sc group by sid,score) t2 group by t2.cd;
select sid,score,count(cid) as cd from sc group by sid,score having count(cid)=(select count( distinct cid) from sc);
select s1.*,s2.score from student s1,sc s2 where s1.SId=s2.SId
and s1.SId=(
select sid as cd from sc group by sid,score having count(cid)=(select count( distinct cid) from sc)
) ;
-- 141.查询每门功成绩最好的前两名
select CId,
SId,
score,
rank() over (partition by cid order by score) rn
from sc;
select * from
( select CId,
SId,
score,
rank() over (partition by cid order by score) rn
from sc) t1 where t1.rn<=2;-- 142.统计每门课程的学生选修人数（超过 5 人的课程才统计）
## 成绩表 分组 课程 统计学生人数 学生人数大于5
##
##
select cid,count(sid) from sc group by cid having count(sid)>5;
-- 143.检索至少选修两门课程的学生学号
##要求 学生学号
#条件 以学生分组 他的课程 count cid 大于2
select sid,count(cid) from sc group by sid having count(cid)>=2;
-- 144,查询选修了全部课程的学生信息
select sid,count(cid) from sc group by sid having count(cid)= ( select count( distinct cid) from sc);
-- 全部的课程
select count( distinct cid) from sc;-- 145,查询各学生的年龄，只按年份来算
## 字段 学生表 学生的年龄
## 条件 按照年份来算 当前年 - 年
select Sname, year(now())-year(Sage)
from student ;-- 146,按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select Sname,
if(month(now())<month(Sage),year(now())-year(Sage)-1,year(now())-year(Sage)) as 生日
from student;
-- 147,查询本周过生日的学生
SELECT Sname,Sage from student where week(now()) =week(Sage);
select * from student;
-- 148,查询下周过生日的学生同42
SELECT Sname,Sage from student where week(now()) +1 =week(Sage);
select * from student;
-- 149,查询本月过生日的学生
select * from student where month(sage)=month(now());
-- 150,查询下月过生日的学生
-- 注意本月是12月的话，下一个月份是1即可
# 如果是12月
select * from student where month(sage)=if(month(Sage) =12,1,month(now())+1);
select * from student;第三批，15道sql查询综合练习题
表数据,数据纯属杜撰，仅做查询练习使用
-- 一、单表查询
-- 素材： 表名：worker-- 表中字段均为中文，比如 部门号 工资 职工号 参加工作 等
CREATE TABLE worker (
部门号 int(11) NOT NULL,
职工号 int(11) NOT NULL,
工作时间 date NOT NULL,
工资 float(8,2) NOT NULL,
政治面貌 varchar(10) NOT NULL DEFAULT '群众',
姓名 varchar(20) NOT NULL,
出生日期 date NOT NULL,
PRIMARY KEY (职工号)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
INSERT INTO worker (部门号, 职工号, 工作时间, 工资, 政治面貌, 姓名, 出生日期) VALUES (101, 1001, '2015-5-4', 3500.00, '群众', '张三', '1990-7-1');
INSERT INTO worker (部门号, 职工号, 工作时间, 工资, 政治面貌, 姓名, 出生日期) VALUES (101, 1002, '2017-2-6', 3200.00, '团员', '李四', '1997-2-8');
INSERT INTO worker (部门号, 职工号, 工作时间, 工资, 政治面貌, 姓名, 出生日期) VALUES (102, 1003, '2011-1-4', 8500.00, '党员', '王亮', '1983-6-8');
INSERT INTO worker (部门号, 职工号, 工作时间, 工资, 政治面貌, 姓名, 出生日期) VALUES (102, 1004, '2016-10-10', 5500.00, '群众', '赵六', '1994-9-5');
INSERT INTO worker (部门号, 职工号, 工作时间, 工资, 政治面貌, 姓名, 出生日期) VALUES (102, 1005, '2014-4-1', 4800.00, '党员', '钱七', '1992-12-30');
INSERT INTO worker (部门号, 职工号, 工作时间, 工资, 政治面貌, 姓名, 出生日期) VALUES (102, 1006, '2017-5-5', 4500.00, '党员', '孙八', '1996-9-2');
-- 151、显示所有职工的基本信息。
select * from worker;-- 152、查询所有职工所属部门的部门号，不显示重复的部门号。
select DISTINCT 部门号 from worker;-- 153、求出所有职工的人数。
select count(部门号) from worker;-- 154、列出最高工和最低工资。
select max(工资),min(工资) from worker;-- 155、列出职工的平均工资和总工资。
select avg(工资) 平均工资,sum(工资) 总工资 from worker;-- 156、创建一个只有职工号、姓名和参加工作的新表，名为工作日期表。
select 职工号,姓名,工作时间 as 工作日期表 from worker ;-- 157、列出所有姓刘的职工的职工号、姓名和出生日期。
select 职工号,姓名,出生日期 from worker where 姓名 like '%刘%';-- 158、列出1960年以前出生的职工的姓名、参加工作日期。
select 姓名,工作时间 from worker where 出生日期 > '1960-0-0';-- 159、列出工资在1000－2000之间的所有职工姓名。
select 姓名 from worker where 工资 >= 1000 and 工资 <= 2000;
select 姓名 from worker where 工资 BETWEEN 1000 and 2000;-- 160、列出所有陈姓和李姓的职工姓名。
select 姓名 from worker where 姓名 like '陈%' or 姓名 like '李%';-- 161、列出所有部门号为2和3的职工号、姓名、党员否。 否？ 不是党员的？
select 职工号,姓名,政治面貌 from worker where (部门号 = 102 or 部门号 = 103) and 政治面貌 != '党员';-- 162、将职工表worker中的职工按出生的先后顺序排序。
select * from worker ORDER BY 出生日期;-- 163、显示工资最高的前3名职工的职工号和姓名。
select * from worker ORDER BY 工资 desc LIMIT 3;-- 164、求出各部门党员的人数。
select 部门号,count(部门号) from worker where 政治面貌 = '党员' GROUP BY 部门号 ;-- 165、统计各部门的工资和平均工资
select 部门号,avg(工资) 平均工资,sum(工资) 总工资 from worker GROUP BY 部门号;-- 166、列出总人数大于4的部门号和总人数。
SELECT 部门号,count(部门号) as 总人数 from worker GROUP BY 部门号 HAVING COUNT(部门号) > 4; #101 部门2 人 102部门4人 没有大于4的
第四批，15道sql查询练习题
表数据,数据纯属杜撰，仅做查询练习使用
create table student(
id int primary key auto_increment,
class int,
chinese int,
english int,
math int,
name varchar(20),
age int,
time date
);
#审题 阅读表
# 学生表 有 id 主键 ,有种类class , 有语文chinese, 有音乐english , 有数学math , 有姓名,有年龄 age , 有时间 日期类型
insert into student values
( 1, 1833, 86, 90, 40, 'zhangsan', 22, '2021-07-03' ),
( 2, 1832, 55, 86, 66, 'lisi', 25, '2021-07-04' ),
( 3, 1833, 93, 57, 98, 'wangwu', 23, '2021-07-03' ),
( 4, 1832, 84, 90, 88, 'zhaoliu', 24, '2021-07-03' ),
( 5, 1833, 93, 57, 22, 'niuqi', 25, '2021-06-18' ),
( 6, 1832, 84, 98, 77, 'qianba', 26, '2021-06-30' ),
( 7, 1832, 55, 57, 77, 'chenjiu', 29, '2021-05-20' ),
( 8, 1833, NULL, 79, 88, 'dingshi', 31, '2021-04-23' ),
( 9, 1832, 56, 53, 49, 'sunyi', 22, '2021-04-23' ),
( 10, 1833, 86, 88, 49, 'xiaoer', 28, '2021-05-25' );-- 167、查询1832班的成绩信息
select * from student where class = '1833';-- 168、查询1833班，语文成绩大于80小于90的成绩信息
select * from student where class = '1833' and (chinese >80 and chinese <90);
select * from student where class = '1833' and (chinese BETWEEN 80 and 90);-- 169、查询学生表中5到10行的数据
select * from student LIMIT 5,5;
select * from student LIMIT 4,5;-- 170、显示1832班英语成绩为98，数学成绩为77的姓名与学号， 啥学号？ class?
select name,class from student where class = '1832' and english = 98 and math = 77;-- 171、查询出1832班成绩并且按语文成绩排序(降序)
select * from student where class = '1832' ORDER BY chinese DESC;-- 172，查询1833班与1832班，语文成绩与数学成绩都小于80的姓名。
select name from student where class in('1833','1832') and (chinese <80 and math < 80);-- 173，查询出没有参加语文考试的学生姓名和班级名称。
select * from student;
select name,chinese,math,class from student where class in('1833','1832') and (chinese <80 and math < 80);-- 174，求出班上语文成绩不及格的学生姓名 60分不及格？
select name,class from student where chinese is null;
#验证看看
select name,class,chinese from student where chinese is null;-- 175，求出每个班的数学平均成绩
select name from student where chinese < 60;-- 176，求出每个班级语文成绩总分--涉及到每个的时候都需要分组
select avg(math) from student GROUP BY class;
select avg(math),class from student GROUP BY class;-- 177，将语文成绩不及格的学生成绩改为60分
select sum(chinese) from student GROUP BY class;
select sum(chinese) ,class from student GROUP BY class;-- 178，三科分数都大于70分的人名和年纪
-- select * from student where chinese < 60;
UPDATE student set chinese = 60 where chinese <60;-- 179，求出英语分数高于70且其它任何一科目大于60分的人和班级
select name,age from student where chinese > 70 and english >70 and math >70 ;-- 180，统计每个班的人数
select name,class from student where english >70 and chinese >60 and math >60;
-- 14、统计每个班的人数
select count(class) from student GROUP BY class;-- 181求每个班数学成绩大于80的人数
select * from student;
-- select * from student where math >80 GROUP BY class ;
select count(class) from student where math >80 GROUP BY class;
第五批，18道sql查询综合练习题
表数据,数据纯属杜撰，仅做查询练习使用
#创建一个新库 作为联系使用
create database db_hm1 char set utf8;
use db_hm1;
# 表1 Employee表
create table employee (
id int primary key auto_increment,
name varchar(30) not null,
addr varchar(30) not null unique,
zip varchar(30) not null, -- 邮编
tel varchar(30) not null,
email varchar(30) unique,
depno int not null,
birth date not null,
sex set('男', '女')
);
--分析此表结构
/*
雇员表
id 整数 主键 自增
姓名 字符串 非空
地址 字符串 非空 唯一
邮编 字符串 非空
电话tel 字符串 非空
邮箱email 字符串 唯一
depno部门编号 整数 非空
birth 出生 日期类型 非空
性别 枚举类型 男或者女
*/
# 表2 Department
create table department (
depno int primary key auto_increment,
depName varchar(30) unique not null,
remark varchar(50)
);
/*
部门表 department
部门编号 depno 整数 主键 自增
部门名称 字符串 唯一 非空
职称 字符串
*/
# 表3 salary -- 注意；工资表的id和员工表的id是一一对应的关系。
create table salay (
id int primary key auto_increment,
inCome double not null, -- 收入
outCome double not null -- 支出
);
/*
薪资表
id 整形 主键 自增
收入 inCome 双小数点的浮点数
指出 outCome 双小数点的浮点数
*/
# 员工表和部门表关联
alter table Employee
add constraint fk_Employee_department foreign key (depno) references department (depno);
/*
一个部门下有多个员工 部门和员工表是 一对多的关系 雇员表的字段 部门编号字段 depno 外键 = 部门表的depno【主键】字段
*/
/*
雇员表和薪资表 是一对一的关系 雇员的主键id 和薪资的id 做关联
*/
alter table Employee
add constraint fk_Employee_salary foreign key (id) references salay (id);
------------------插入数据 -------------------
------给部门表 插入数据
insert INTO
department (depName, remark)
values ('财务部', Null),
('人力资源部', Null),
('经理办公室', Null),
('研发部', Null),
('市场部', Null);
---插入数据 给薪资表插入数据
INSERT INTO
salay (inCome, outCome)
values (2100.7, 123.09),
(1582.62, 88.03),
(2569.88, 185.65),
(1987.01, 79.58),
(2066.15, 108.0),
(2980.7, 210.2),
(3259.98, 281.52),
(2860.0, 198),
(2347.68, 180),
(2531.98, 199.08),
(2240.0, 121.0);
------雇员表 给雇员表插入数据
# 往表里添加数据：
insert into
employee
VALUES (
1,
'王林',
'武汉大学',
'430074',
'87598405',
null,
2,
'1985-2-1',
'男'
);
insert into
employee
VALUES (
2,
'王芳 ',
'华中科大',
'430073',
'62534231',
null,
1,
'1966-3-28',
'男'
);
insert into
employee
VALUES (
3,
'张晓',
'武汉理工大',
'430072 ',
'87596985',
Null,
1,
'1972-12-9',
'男'
);
insert into
employee
VALUES (
4,
'王小燕',
'武汉交大',
'430071',
'85743261',
'lili@sina.com',
1,
'1950-7-30',
'女'
);
insert into
employee
VALUES (
5,
'李华',
' 华中农大',
'430070',
'87569865',
Null,
5,
'1962-10-18',
'男'
);
insert into
employee
VALUES (
6,
'李明',
'华中师大',
'430075',
'85362143',
'zhujun@sina.com ',
5,
'1955-09-28',
'男'
);
insert into
employee
VALUES (
7,
'田丽',
'中南财大',
'430076',
'85693265',
'zgming@sohu.com',
3,
'1968-08-10',
'女'
);
insert into
employee
VALUES (
8,
'吴天',
'武汉电力',
'430077',
'36985612 ',
'zjamg@china.com',
5,
'1964-10-01',
'男'
);
insert into
employee
VALUES (
9,
'刘备',
' 武汉邮科院',
'430078',
'69865231',
Null,
3,
'1967-04-02',
'男'
);
insert into
employee
VALUES (
10,
'赵云',
'学府家园',
'430071',
'68592312 ',
Null,
4,
'1968-11-18',
'男'
);
insert into
employee
VALUES (
11,
'貂禅',
'湖北工大',
' 430074',
'65987654',
null,
4,
'1959-09-03',
'女'
);
#--182,查询雇员表的全部信息
select * from employee;#--183，查询雇员所属的部门
select e.id, e.name, e.addr, e.zip, e.tel, e.email, e.birth, e.sex, d.depName, d.remark, s.inCome, s.outCome
from employee e, department d, salay s
where
e.depno = d.depno
and e.id = s.id;# 184. 查询前5个雇员的所有记录;
select e.id, e.name, e.addr, e.zip, e.tel, e.email, e.birth, e.sex, d.depName, d.remark, s.inCome, s.outCome
from employee e, department d, salay s
WHERE
e.depno = d.depno
AND e.id = s.id
LIMIT 0, 5;# 185， 查询每个雇员的地址和电话;
select name, addr, tel from employee# 186. 查询id为1的雇员地址和电话;
select name, addr, tel from employee where id = 1;# 187. 查询表Employee表中女雇员的地址和电话，使用AS子句将结果列中各列的标题分别指定为地址、电话;
select name, addr as 地址, tel as 地址, sex from employee where sex = '女';# 188. 计算每个雇员的实际收入;
--实际收入 等于 总工资-罚款
select e.name, s.inCome - s.outCome as 实际收入
from salay s, employee e
where
s.id = e.id
GROUP BY
e.name;
# 189. 找出所有性王的雇员的部门号（部门号不能重复显示）;
select DISTINCT
d.depName
from employee e, department d
where
e.depno = d.depno
and e.name like '王%';# 190. 找出所有收入在2000－3000元之间的雇员编号
select e.id from employee e JOIN salay s on e.id = s.id
WHERE s.inCome between 2000 on 3000;# 191. 查找在财务部工作的雇员情况;
--解法一
select depno from department WHERE depName = '财务部';
select *
from employee
where
depno = (
select depno
from department
WHERE
depName = '财务部'
);
--解法二 内连接
select *
FROM employee e, department d
where
e.depno = d.depno
and d.depName = '财务部';# 192. 查找在财务部且年龄不低于研发部任一个雇员年龄的雇员的姓名;
--财务部的年龄
select depno from department where depName = '财务部';
select *
from employee
where
depno = (
select depno
from department
where
depName = '财务部'
);
--研发部的年龄
select depno from department where depName = '研发部';
select birth
from employee
where
depno = (
select depno
from department
where
depName = '研发部'
);
--题目
---研发部最大的年龄
select *
from employee e
JOIN department d ON e.depno = d.depno
WHERE
d.depName = '财务部'
and e.birth <= (
select min(birth)
from employee
where
depno = (
select depno
from department
where
depName = '研发部'
)
)
# 193. 查找比所有财务部雇员收入都高的雇员的姓名;
--财务部的最高收入
select max(s.inCome)
from department d, salay s, employee e
WHERE
d.depno = e.depno
and s.id = e.id
and d.depName = '财务部';
--题目
select e.name, s.inCome
from department d, salay s, employee e
WHERE
d.depno = e.depno
and s.id = e.id
and s.inCome > (
select max(s.inCome)
from department d, salay s, employee e
WHERE
d.depno = e.depno
and s.id = e.id
and d.depName = '财务部'
);
# 194. 查找每个雇员的情况及薪水情况;
select e.name, s.inCome - s.outCome
from department d, salay s, employee e
WHERE
d.depno = e.depno
and s.id = e.id# 195. 查找财务部收入在2200元以上的雇员姓名及其薪水详细情况;
select *
from department d, salay s, employee e
WHERE
d.depno = e.depno
and s.id = e.id
and d.depName = '财务部'
and s.inCome > 2200;
# 196. 求财务部雇员的平均实际收入;
select avg(s.inCome - s.outCome)
from department d, salay s, employee e
WHERE
d.depno = e.depno
and s.id = e.id
and d.depName = '财务部';# 197. 求财务部雇员的总人数;
select count(e.depno)
from department d, salay s, employee e
WHERE
d.depno = e.depno
and s.id = e.id
and d.depName = '财务部';# 198. 求各部门的雇员数（要求显示，部门号、部门名称和部门雇员数）;
select d.depno, d.depName, count(d.depName) as 部门雇员数
from department d, salay s, employee e
WHERE
d.depno = e.depno
and s.id = e.id
group by
d.depName;# 199. 求部门的平均薪水大于2500的部门信息（要求显示，部门号、部门名称和平均工资）
select e.depno, d.depName, avg(inCome - outCome)
from department d, salay s, employee e
WHERE
d.depno = e.depno
and s.id = e.id
GROUP BY
d.depName
HAVING
avg(inCome - outCome) > 2500;
;# 200， 求部门的平均薪水大于3500的部门信息（要求显示，部门号、部门名称和平均工资）
select e.depno, d.depName, avg(inCome - outCome)
from department d, salay s, employee e
WHERE
d.depno = e.depno
and s.id = e.id
GROUP BY
d.depName
HAVING
avg(inCome - outCome) > 3500;
;
第六批，13道sql查询练习题
表数据,数据纯属杜撰，仅做查询练习使用
create database db_hm2 char set utf8;
use db_hm2;
-- 部门表
CREATE TABLE dept (
deptno INT PRIMARY KEY auto_increment,-- 部门编号
dname VARCHAR ( 14 ), -- 部门名字
loc VARCHAR ( 13 ) -- 地址
);
--审题 部门表 有编号 有名字 有地址
-- 员工表
create table emp(
empno int primary key auto_increment,-- 员工编号
ename varchar(10), -- 员工姓名 -
job varchar(9), -- 岗位
mgr int, -- 直接领导编号
hiredate date, -- 雇佣日期，入职日期
sal int, -- 薪水
comm int, -- 提成
deptno int not null, -- 部门编号
foreign key (deptno) references dept(deptno)
);
--雇员表 有编号 有姓名 有岗位 有直接领导 有入职时间 有薪资 有提成 有编号
--- 部门表和雇员表 是一对多关系 一个部门有多个雇员；
-- 工资等级表
create table salgrade(
grade INT, -- 等级
losal DECIMAL(10, 2), -- 最低工资
hisal DECIMAL(10, 2) -- 最高工资
);
-- 单表
-- 公司利润表
create table tbyear (
year INT,-- 年份
zz DECIMAL(10, 2) -- 利润
);
INSERT INTO dept VALUES ('10', '教研部', '北京');
INSERT INTO dept VALUES ('20', '学工部', '上海');
INSERT INTO dept VALUES ('30', '销售部', '深圳');
INSERT INTO dept VALUES ('40', '财务部', '广州');
INSERT INTO dept VALUES ('50', '董事会', '太原');
INSERT INTO emp VALUES ('1001', '甘宁', '文员', '1013', '2000-12-17', '8000.00', null, '20');
INSERT INTO emp VALUES ('1002', '黛绮丝', '销售员', '1006', '2001-02-20', '16000.00', '3000.00', '30');
INSERT INTO emp VALUES ('1003', '殷天正', '销售员', '1006', '2001-02-22', '12500.00', '5000.00', '30');
INSERT INTO emp VALUES ('1004', '刘备', '经理', '1009', '2001-04-02', '29750.00', null, '20');
INSERT INTO `emp` VALUES ('1005', '谢逊', '销售员', '1006', '2001-09-28', '12500.00', '14000.00', '30');
INSERT INTO emp VALUES ('1006', '关羽', '经理', '1009', '2001-05-01', '28500.00', null, '30');
INSERT INTO `emp` VALUES ('1007', '张飞', '经理', '1009', '2001-09-01', '24500.00', null, '10');
INSERT INTO emp VALUES ('1008', '诸葛亮', '分析师', '1004', '2007-09-01', '30000.00', null, '20');
INSERT INTO emp VALUES ('1009', '曾阿牛', '董事长', null, '2001-11-17', '50000.00', null, '10');
INSERT INTO emp VALUES ('1010', '韦一笑', '销售员', '1006', '2001-09-08', '15000.00', '0.00', '30');
INSERT INTO emp VALUES ('1011', '周泰', '文员', '1008', '2007-05-28', '11000.00', null, '20');
INSERT INTO emp VALUES ('1012', '程普', '文员', '1006', '2001-12-03', '9500.00', null, '30');
INSERT INTO emp VALUES ('1013', '庞统', '分析师', '1004', '2001-12-09', '30000.00', null, '20');
INSERT INTO emp VALUES ('1014', '黄盖', '文员', '1007', '2002-01-23', '13000.00', null, '10');
INSERT INTO emp VALUES ('1015', '张三', '保洁员', '1009', '1999-09-09', '80000.00', '90000.00', '50');
INSERT INTO salgrade VALUES ('1', '7000', '12000');
INSERT INTO salgrade VALUES ('2', '12000', '14000');
INSERT INTO salgrade VALUES ('3', '14000', '20000');
INSERT INTO salgrade VALUES ('4', '20000', '30000');
INSERT INTO salgrade VALUES ('5', '30000', '90000');
INSERT INTO tbyear VALUES ('2010', '100');
INSERT INTO tbyear VALUES ('2011', '150');
INSERT INTO tbyear VALUES ('2012', '250');
INSERT INTO tbyear VALUES ('2013', '800');
INSERT INTO tbyear VALUES ('2014', '1000');
---201. 查出至少有一个员工的部门，显示部门编号、部门名称、部门位置、部门人数。
-- 根据部门分组 查询 部门表
select * from
emp e,dept d
where e.deptno = d.deptno;
select e.deptno 部门编号,d.dname 部门名称,d.loc 部门位置,count(e.deptno) as 部门人数
from emp e, dept d
where e.deptno = d.deptno
GROUP BY e.deptno,d.dname
HAVING count(e.deptno) >=1;
SELECT d.deptno, d.dname, d.loc, e1.`count(*)`
FROM dept d
INNER JOIN (
SELECT deptno, COUNT(*)
FROM emp
GROUP BY
deptno
) e1 ON d.deptno = e1.deptno;-- 202. 列出薪金比关羽高的所有员工。
-- --查询关二哥的薪资 薪水+薪资 如果薪资是空 则赋值0
-- select sal+IFNULL(comm,0) from emp where ename = '关羽';
-- --查询其他人的薪资 和关二哥的薪资对比
-- select * from emp ORDER BY sal,comm desc;
--解答：
select * from emp
where (sal + IFNULL(comm, 0)) > (select sal + IFNULL(comm, 0) from emp where ename = '关羽');
;
SELECT *
FROM emp e
WHERE e.sal>(SELECT sal FROM emp WHERE ename='关羽');-- 203. 列出所有员工的姓名及其直接上级的姓名。
select * from emp;
---这是一个自连接的表 mgr 连接的是主键 部门编号
select e1.ename,e2.ename from emp e1,emp e2
where e1.mgr = e2.empno;
SELECT e1.ename, e2.ename 上级
FROM emp e1
LEFT OUTER JOIN emp e2 ON e1.mgr = e2.empno;-- 204. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
select e1.empno,e1.ename,d.dname from emp e1, emp e2, dept d
where e1.mgr = e2.empno
and d.deptno = e1.deptno
and e1.hiredate < e2.hiredate;
---验证
-- select *
-- from emp e1, emp e2, dept d
-- where
-- e1.mgr = e2.empno
-- and d.deptno = e1.deptno
-- and e1.hiredate < e2.hiredate;
SELECT e1.empno, e1.ename, d.dname
FROM emp e1
LEFT OUTER JOIN emp e2 ON e1.mgr = e2.empno
LEFT OUTER JOIN dept d ON e1.deptno = d.deptno
WHERE
e1.hiredate < e2.hiredate;
-- 205. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
-- select if( count(d.dname) >= 1, d.dname, null ) as 有员工部门,
-- e1.ename ,e1.sal,e1.job,e1.hiredate,e1.comm,
-- if(count(d.dname)>=1,null,d.dname) as 没有员工的部门
-- from emp e1,dept d
-- where e1.deptno = d.deptno
-- GROUP BY d.dname,e1.ename
-- ;
select d.dname,e.* from dept d LEFT JOIN emp e on d.deptno = e.deptno;
SELECT d.dname, e.*
FROM dept d
LEFT OUTER JOIN emp e ON d.deptno = e.deptno;-- 206. 列出所有文员的姓名及其部门名称，部门的人数。
select e.ename,d.dname, COUNT(d.dname) as 部门人数 ,e.ename
from emp e ,dept d
where e.deptno = d.deptno
and e.job = '文员'
GROUP BY e.ename ;
--求部门人数
select deptno,COUNT(*) from emp GROUP BY deptno;
select e.ename,d.deptno,z.zz
from
emp e,dept d,(select deptno, COUNT(*) as zz from emp GROUP BY deptno) z
where e.deptno = d.deptno
and z.deptno = d.deptno
and e.job='文员';
SELECT e.ename, d.dname, z.`count(*)`
FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno
INNER JOIN (
SELECT deptno, COUNT(*)
FROM emp
GROUP BY
deptno
) z ON z.deptno = d.deptno
WHERE
e.job = '文员';-- 207. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。
select * from emp where sal>15000 ORDER BY sal desc;
select job,COUNT(job) from emp GROUP BY job HAVING min(sal) >15000;
SELECT job, COUNT(*)
FROM emp
GROUP BY
job
HAVING
MIN(sal) > 15000;-- 208. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。
select * from emp e,dept d
where e.deptno = d.deptno
and d.dname='销售部';
---不知道不部门编号 那么销售部总得给我们吧
select deptno from dept where dname LIKE '%销售部%';
select ename from emp where deptno = (select deptno from dept where dname LIKE '%销售部%');
SELECT e1.ename
FROM emp e1
INNER JOIN dept d ON e1.deptno = d.deptno
WHERE
d.dname = '销售部';-- 209. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。
---求平均薪资 部门的 24683.3333
select avg(sal) from emp;
-- --这是比他们大的人
select * from emp where sal > (select avg(sal) from emp);
-- ---上级领导 得 还得上自关联查询
-- select * from emp e1,emp e2
-- where e1.mgr =e2.empno
-- and e1.sal>( select avg(sal) from emp )
-- --投影列要部门名称
-- select e1.ename,d.dname,e2.ename,e1.sal
-- from emp e1, emp e2,dept d
-- where
-- e1.mgr = e2.empno
-- and d.deptno =e1.deptno
-- and e1.sal > (
-- select avg(sal)
-- from emp
-- )
-- ;
-- --- 投影列 还要工资等级 得还得找工资等级表
-- select * from salgrade s2,
-- (select e1.sal
-- from emp e1, emp e2, dept d
-- where
-- e1.mgr = e2.empno
-- and d.deptno = e1.deptno
-- and e1.sal > (
-- select avg(sal)
-- from emp
-- )) as s1
-- where s1.sal between s2.losal and s2.hisal
-- GROUP BY s1.sal
-- ;
---回到题目 题目是查询啥来着 24683
----------------列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。
select s1.*,s2.grade
from salgrade s2, (
select e1.ename,e2.ename as 上级, d.dname, e1.sal
from emp e1
left JOIN emp e2 on e1.mgr=e2.empno
left JOIN dept d on d.deptno = e2 .deptno
where e1.sal > (select avg(sal) from emp)
) as s1
where
s1.sal > s2.losal and s1.sal <= s2.hisal;
--非等值连接
select e1.*
CASE avg(e1.sal)
WHEN THEN
ELSE
END
from emp e1 LEFT JOIN emp e2 on e1.mgr =e2.empno;
SELECT e1.*, e2.ename 上级, d.dname 部门名称, sal.`grade`
FROM
emp e1
LEFT OUTER JOIN emp e2 ON e1.mgr = e2.empno
LEFT OUTER JOIN dept d ON e1.deptno = d.deptno
LEFT OUTER JOIN salgrade sal ON e1.sal BETWEEN losal AND hisal
WHERE
e1.`sal` > (
SELECT AVG(sal)
FROM emp
) ;
SELECT t.*, b.ename AS mgr_ename, -- 给mgr对应的ename一个别名，以便区分
c.grade
FROM (
SELECT ep.*, dt.dname AS dept_name
FROM emp ep
JOIN dept dt ON ep.deptno = dt.deptno
WHERE
ep.sal > (
SELECT AVG(sal)
FROM emp
)
) t
JOIN emp b ON t.mgr = b.empno
JOIN salgrade c ON t.sal BETWEEN c.losal AND c.hisal;
-- 210.列出与庞统从事相同工作的所有员工及部门名称。
--庞统的工作 是 分析师
select job from emp where ename = '庞统';
--- 找到和分析师相同的员工 和部门
select d.dname,e.*
from emp e,dept d
where e.deptno =d.deptno
and e.job = (select job from emp where ename = '庞统')
and e.ename !='庞统';
SELECT e.ename, d.dname
FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno
WHERE
e.job = (
SELECT job
FROM emp
WHERE
ename = '庞统'
);-- 211.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。
---1，查看雇员表薪资
select * from emp;
---2，再部门的值是30 里面 最高的薪资 是多少 28500
select max(sal) from emp where deptno = '30';
--- 条件： 薪资 > 28500 投影列：薪资，员工的姓名，部门名称
select e.sal,e.ename,d.dname
from emp e,dept d
where e.deptno = d.deptno
and sal > (select max(sal) from emp where deptno = '30');
SELECT e.ename, e.sal, d.dname
FROM emp e
LEFT OUTER JOIN dept d ON e.deptno = d.deptno
WHERE
e.sal > ALL (
SELECT sal
FROM emp
WHERE
deptno = 30
);-- 212.列出每个部门的员工数量、平均工资(保留2位小数)。
select COUNT(deptno) as 每个部门的员工数量 ,ROUND(avg(sal),2) as 平均工资 from emp GROUP BY deptno;
SELECT d.dname, e1.*
FROM (
SELECT e.deptno, COUNT(*), AVG(sal)
FROM emp e
GROUP BY
e.deptno
) e1
INNER JOIN dept d ON e1.deptno = d.deptno;-- 213.查出年份、利润、年度利润增长比
select t1.year,t1.zz, CONCAT(round(((t2.zz-t1.zz) / t1.zz) *100,2),'%') AS 利润增长比
from tbyear t1
left join
tbyear t2
on t1.year = t2.year - 1
where
t1.zz != 0;
SELECT tb1.*, IFNULL(
CONCAT(
(tb1.zz - tb2.zz) / tb2.zz * 100, '%'
), 0
) '年度增长比'
FROM tbyear tb1
LEFT OUTER JOIN tbyear tb2 ON tb1.`year` = tb2.`year` + 1;
select 150/100;
--- 自关联 自己左连接自己 有共有的值 也有我左表的自己的值
-- 连接条件 今年 = 去年
---用今年和去年去 比对
---左表
-- 投影列 年份 利润
---净利润 =去年的利润-今年的利润
-- 解法提示: 使用tbyear表进行自连接. 年度利润增长比 = (净利润 / 销售额 ) * 100 % 第七批 sql查询综合练习题
表数据
create database kettle_demo char set utf8;
create database test char set utf8;
use test;
---部门表 主表 一的一方
create table department (
id int primary key auto_increment,
dname varchar(20),
info varchar(50)
);
--雇员表 从表 多的一方
create table emp(
id int primary key auto_increment,
ename varchar(20),
age int,
sex varchar(10),
sal double,
job varchar(10),
work_year int,
boos_name varchar(20),
d_id int,
foreign key(d_id) references department(id)
)engine=InnoDB char set utf8;
---部门表
insert into department (dname, info) values ('研发部', '搞研发的');
insert into department (dname, info) values ('人事部', '搞人事的');
insert into department (dname, info) values ('财务部', '搞工资的');
insert into department (dname, info) values ('销售部', '搞销售的');
insert into department (dname, info) values ('宣发部', '搞宣传的');
insert into department (dname, info) values ('网络部', '搞网络的');
---雇员表
insert into emp values ( null, '荀彧', 50, '男', 80000, '人事部', 20, '曹操', 2 );
insert into emp values ( null, '曹操', 50, '男', 80000, null, 50, '曹操', null );
insert into emp values ( null, '墨子', 50, '男', 90000, '研发部', 50, '鲁班', 1 );
insert into emp values ( null, '沈万三', 50, '男', 40000, '财务部', 20, '朱元璋', 3 );
insert into emp values ( null, '诸葛亮', 50, '男', 80000, '销售部', 30, '刘备', 4 );
insert into emp values ( null, '马云', 50, '男', 100, '网络部', 50, 'null', 6 );
select * from department d, emp e where d.id = e.d_id;
use db6;
### 查询数据准备###
# 创建部门表
create table department(
did int primary key auto_increment,
dname varchar(20),
description varchar(255)
) engine = InnoDB default charset utf8;
# 创建员工表
create table employee(
eid int primary key auto_increment,
eno varchar(20),
ename varchar(20),
eage int,
egender varchar(10),
esalary double,
estation varchar(20),
eworked_year int,
eleader varchar(20),
depart_id int,
foreign key(depart_id) references department(did)
) engine = InnoDB default charset utf8;
# 添加数据
insert into department(dname,description) values ('研发部','致力于研发公司自研新产品');
insert into department(dname,description) values ('人事部','提升员工凝聚力, 招聘优秀人才');
insert into department(dname,description) values ('财务部','核算公司员工薪酬、奖金等财务相关工作');
insert into department(dname,description) values ('销售部','销售公司数据平台产品, 引导商家入驻公司平台');
insert into department(dname,description) values ('市场部','洞察公司产品市场,做好宣传工作');
insert into department(dname,description) values ('网络部','插网线');
insert into employee(eno, ename, eage, egender, esalary, estation, eworked_year, eleader, depart_id)
values
('U34210','东方薇薇',25,'女',13500,'后台开发工程师',2,'金隆泽',1);
insert into employee(eno, ename, eage, egender, esalary, estation, eworked_year, eleader, depart_id)
values
('U34211','西门子舟',28,'男',19620,'数据开发工程师',5,'金隆泽',1);
insert into employee(eno, ename, eage, egender, esalary, estation, eworked_year, eleader, depart_id)
values
('U34219','金隆泽',34,'男',27000,'数据分析师',6,'金隆泽',1);
insert into employee(eno, ename, eage, egender, esalary, estation, eworked_year, eleader, depart_id)
values
('U34234','付小春',22,'女',7500,'UI工程师',1,'金隆泽',1);
insert into employee(eno, ename, eage, egender, esalary, estation, eworked_year, eleader, depart_id)
values
('U34256','王展燕',24,'女',9000,'数据开发工程师',2,'金隆泽',1);
insert into employee(eno, ename, eage, egender, esalary, estation, eworked_year, eleader, depart_id)
values
('U34223','谢佰',29,'男',25500,'大数据开发工程师',3,'金隆泽',1);
insert into employee(eno, ename, eage, egender, esalary, estation, eworked_year, eleader, depart_id)
values
('U34303','周佳佳',19,'女',4800,'人事专员',1,'张小敏',2);
insert into employee(eno, ename, eage, egender, esalary, estation, eworked_year, eleader, depart_id)
values
('U34314','张小敏',28,'女',6000,'人事主管',4,'张小敏',2);
insert into employee(eno, ename, eage, egender, esalary, estation, eworked_year, eleader, depart_id)
values
('U34326','文宾',24,'男',5300,'行政专员',2,'张小敏',2);
insert into employee(eno, ename, eage, egender, esalary, estation, eworked_year, eleader, depart_id)
values
('U34412','马莲莲',27,'女',6500,'财务主管',4,'马莲莲',3);
insert into employee(eno, ename, eage, egender, esalary, estation, eworked_year, eleader, depart_id)
values
('U34409','谢莹莹',26,'女',6000,'财务专员',3,'马莲莲',3);
insert into employee(eno, ename, eage, egender, esalary, estation, eworked_year, eleader, depart_id)
values
('U34511','黄惠',21,'女',8000,'销售专员',2,'牛芳',4);
insert into employee(eno, ename, eage, egender, esalary, estation, eworked_year, eleader, depart_id)
values
('U34517','牛芳',25,'女',8500,'销售经理',5,'牛芳',4);
insert into employee(eno, ename, eage, egender, esalary, estation, eworked_year, eleader, depart_id)
values
('U34600','县香梅',27,'女',9000,'渠道经理',4,'县香梅',5);
insert into employee(eno, ename, eage, egender, esalary, estation, eworked_year, eleader, depart_id)
values
('U34604','王祥',23,'男',6000,'渠道专员',2,'县香梅',5);
insert into employee(eno, ename, eage, egender, esalary, estation, eworked_year, eleader, depart_id)
values
('U34604','王鸿翔',33,'男',60000,'渠道专员',2,null,null);
--214，查询每一个员工的姓名及关联的部门名称；[内连接]
----foreign key (depart_id) references department (did)
-- （1）查询每一个员工的姓名及关联的部门名称；[内连接]
select e.ename,d.dname from department d,employee e where e.depart_id = d.did;
--215，查询员工表的所有数据和对应的部门信息；[左外连接]
select e.*,d.dname,d.description from employee e LEFT JOIN department d on e.depart_id = d.did ;
--216，查询部门表的所有数据和对应的员工信息；[右外连接]
-- 要求用右连接 且要查询部门表的所有数据 那么部门表放右边 也就是后面
select d.*,e.* from employee e RIGHT JOIN department d on e.depart_id = d.did;
--217，查询员工及其所属领导的名字；
select ename,eleader from employee;
--218，查询所有员工及其领导的名字 , 如果员工没有领导, 也需要查询出来；
--- 员工没有领导也要查询出来
--- 查询出了领导的字段 本身就无论是不是null都可以显示
select ename, eleader from employee;
--219，将薪资低于5000和工龄大于2年的员工全部查询出来；
select * from employee;
select * from employee where esalary <5000 and eworked_year >2;
--- 这个公司还行 老员工基本薪资不会太低 不是黑厂--220，查询销售部的所有员工信息；
select * from department d, employee e
where e.depart_id = d.did
and d.dname = '销售部';
--221，查询比【张小敏】入职之后的员工信息；
--张小敏的 工龄 4
select eworked_year from employee where ename='张小敏';
--全部员工工龄 <4 即比他晚的员工
select * from employee where eworked_year <(select eworked_year from employee where ename = '张小敏');--222，查询销售部和市场部的所有员工信息；
select * from department d, employee e
where e.depart_id = d.did
and d.dname in ('销售部','市场部');
-- （10）查询比财务部所有人工资都高的员工信息；
--财务部最高薪资 6500
select max(esalary) from department d, employee e where e.depart_id = d.did and d.dname='财务部';
---所有人薪资字段 > 财务部最高薪资 即题目 比财务部所有人都高的员工信息
select * from department d, employee e
where e.depart_id = d.did
and e.esalary >(
select max(esalary) from department d, employee e where e.depart_id = d.did and d.dname='财务部'
);
--223，查询比财务部所有人工资都高的员工信息；
--财务部最高薪资 6500
select max(esalary) from department d, employee e where e.depart_id = d.did and d.dname='财务部';
---所有人薪资字段 > 财务部最高薪资 即题目 比财务部所有人都高的员工信息
select * from department d, employee e
where e.depart_id = d.did
and e.esalary >(
select max(esalary) from department d, employee e where e.depart_id = d.did and d.dname='财务部'
); --224，查询与【谢莹莹】的职位和薪资相同的员工信息。
-- 谢莹莹 的职位
select estation from employee where ename = '谢莹莹';
和薪资
select esalary from employee where ename = '谢莹莹';
select * from employee e
WHERE
estation = (select estation from employee where ename = '谢莹莹')
and esalary =(select esalary from employee where ename = '谢莹莹')
--验证看看 果然财务部没有人和它职位相同 也没有人薪资和她相同
select * from employee
第八批，窗口函数sql综合练习题
十四,MYSQL之窗口函数，详细篇，附大量窗口函数查询sql练习题-CSDN博客
冲鸭，224道sql查询练习题，量大管饱。
sql查询无他，熟能生巧。
数据纯属杜撰，仅做查询练习使用。
554
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
