00:00
来讲完分组函数这一章之后啊,我们看一下这章的练习题,先看这个TST。分组函数来,我们看这一章,第一个题目说查询employees,这个表中有多少个部门,有多少个部门,这个呢,我们上课的时候已经讲到了,我们就直接来看一看,大家再回忆一下啊,如果我们这里边儿这呢有多少空门,当然我们要用到count叫分组函数了。我们加了一个叫distinct的,对吧,如果我们要是不加的话,就是这样写。它实际上会列出这个这个个数很多了。我们看一下。106个对吧,不可能有这么多部门,因为你每一条记录相当于你这个表里边每一条记录,就每一个员工,他这个凡是这个有职的都算是一条记录,一共呢,有106个人是都有部门,就有一个人没有部门,所以没有部门那个人就剔除出去了,他是now就有106个,这显然是不对的,对吧,我们部门呢,说是就那么十多个部门,所以要求得加上一个叫的啊。
01:21
这样呢,他就能够去重,把重复的都给去掉,只选择不一样的这个ID,这样一算的话,就十多个11个部门。这是我们这个题,需要大家你注意distinct啥意思?好了,那么这个题目就这样了,下一个查询全工资奖金基数的平均值,没有奖金的按零来计算,这个题目练什么的,就是说我们的组函数啊,它在进行运算的时候是不计算空值在内的,所以你要想去判断这个,求出全公司的奖金的平均值,那必须把这个为no的那些人也得算进去,所以给他们拿零来计算,使用到NVL这样一个通用函数。
02:08
这需要大家注意。下一个题目查询各个部门的平均工资。这是我们说错误的做法,错在哪啊?我们说了,当你在查询当中使用到了组函数。没有函数,就是我们之前讲的哈,那就不提了,当你使用到组函数的时候,而且还存在着非组函数的这样的列的话,要求这个这些列必须都得放在格当中。否则的话会报错啊。下一个题目查询Toronto这个城市员工的平均工资,CTRLC,来,我们写一下这个题目。查询这个城市员工的平均工资,那我们就直接来select查询平均工资,就是avg salary吧。
03:04
查询平均工资,这个工资我们知道是在employees表中的。那Toronto这个量是在哪一个表啊?是不是应该在那个。哎,我们带大家看一下这个啊。这个需要大家你对这个表结构要熟悉。好了,Toronto是表示一个城市是不是在这个locations这个表里啊?好,现在我们要查询这个Toronto这个城市的平均工资,相当于我必须得是不是把这个表给连起来呀,要想连接employees表和locations表,中间我还得用到department这个表。好了,那我们这个相当于是多表连接,是我们上一节讲的内容。我们这儿呢,就直接来用了。
04:01
这写的时候我们也讲了两种方式,一种呢叫做通用的方式,还有一种就是涉及到SQ999语法join on2种方式都行,这呢我们就写其中一种啊,比如from employees,叫e departments d locations。L这三个表,然后是不是得写连接条件啊?三个表至少得有两个连接条件,不写连接条件就出现笛卡尔的错误了。Where地点department ID等于D呃点departmentd等于d.department ID and d.location location ID等于l.location ID。这连接条件也写完了,然后呢,我们看说只让你是不是输出Toronto这个城市啊,所以我们再给它加上一个连接条件叫and。
05:06
嗯,l.CD等于后。对吧,如果你防止说这个词到底跟人家那个城市城市的话,它是都小写都大写,还是说首字母大写,你要是不保险的话,你就再来一个lower。啊,后边这个,当你这个CD全部改成小写以后长这样。也行是吧。9500,这就是Toronto这个城市的平均工资,那有的同学说,哎,我想在这个前面把这个Toronto给显示出来,那在这加个CT吧。再加集体直运型,你说。有没有问题啊。别忘了哈,就是知识点学了,你得灵活运用,这你要写CT的话,是不是这又没有加在group当中又又挂了呀,是吧?嗯,那你要想这就光显示一个TOTO怎么办呀。
06:05
那你可以就直接这样呗。床头。啊,这个城市的平均工资九千五可以吧,好了,我们看下边。下边一个题目说,哎,有员工的城市,各个城市的平均工资。刚才我们写的是一个城市的平均工资,现在是各个城市的平均工资来。我们把这个题目做一下,在上一个题目的基础之上,我们做一些修改。有员工的各个城市,那前边就是C吧。然后你在这加个L点也行,不加也没事,CBG,然后呢,这就不要了。这整个使用叫group by。Goodbye city要求他必须得出现在goodbye当中,那这个题目写完没有啊?
07:05
有的同学说写完了,有的同学说这前面还有一个书叫有员工的城市的一个限制呢,这个怎么表达呀。大家回忆一下,我们上节课讲的多表查询的时候,说我们这样实现了连接,没有使用外连接的话,是不是就是。没有说什么哪个表有的不存在的,没对接的再连上,如果是需要那样的话,我们才使用到外连接。那链接就是他们真的都有数据的时候,这呢实际上表达的就是有员工的城市了,你要还想说,呃,有些城市可能没有员工,所以就没有工资,还想把那些连起来,那你在适当的位置加加号。这个题目是这样。下一个。查询平均工资高于8000的部门的ID和他的平均工资,来,我们再写一个,把这个再写一写。
08:00
平均工资高于8000的部门ID不难吧,他们的ID。然后呢,平均工资avg salary from employees。平均工资大家注意了,这是不是一个过滤条件呀?然后过滤条件当中如果用到了主函数,我们要用heavy,要求他的salary得大于8000。别忘了合伙半。判断ID就这样是吧,平均工资大于8000的部门ID以及他的平均工资。就这样,如果你要说最高工资。大于8000的,那你就把这改成叫max。
09:09
这不应该。这不用来是吧,还是ABG最高工资大于8000的部门ID和他的平均工资。就是这样啊,根据题目的要求,来下一个查询平均工资高于6000的job title有哪些?Job title这个呢?可能大家没咋接触过,我们看job title job title是不是在这儿啊?Job title有哪些?那你相当于是又涉及到跟平均工资挂钩了,就是有employees表跟jobs这两个表做连接了,好,这两个表做连接,问问这些有哪些,那我们就就来呗。
10:04
平均工资高于6000的job title select job。抬头。然后我也给你再列一下你的平均工资吧,From employees。我换一种方式叫John。Jobs表,然后呢,嗯。我们加上一个别名E。高。然后一点。你看它们的连接条件叫照白D,所以叫E点叫吧ID等于勾点照白D,这个写完以后注意是不是得go了呀,知道吧开同时有一个排过滤条件avg salary要求得大于6000。
11:00
就这样写呗。说啊I少一个I。OK,就这样一些job title啊,叫这个名字的,他们的平均工资都是大于6000的。那么这儿呢,我们这个关于多表呃,分组函数的。分组函数的这一块,这个TXT题目就做完了,然后呢,我们看一下这个word。大家下来啊,一定要自己把这些题目都给做一做,你做了以后,你熟悉了,相当于就就熟了,然后等你回头用的时候,我们后边讲为什么要讲这个数据库SQ这些语言,后边我们要讲这个JDBC,我们通过Java应用程序里边嵌套这些SQL语句,来实现Java应用程序对数据库的一个调用。对吧,就像你这个,你访问一下京东网站,京东现在后台都改成用Java来写了,那你的你的用户的信息,嗯,比如你的购物车的东西,这不都是数据库中存的吗。
12:07
相当于我们通过Java应用程序在前台,我们给他往后台去调用的时候,里边需要嵌套这个SQL语句来实现这样的一些操作的啊。第一个组函数处理多行返回一行吗?主函数返回多行处理一呃,处理多行返回一行吗?对呀,所以正因为这样,所以它才叫主函数吧,你要是处理一行返回一行,那叫单行。那有没有多行返回多行的没有啊,函数的话,因变量最后输出结果一定只有一个,要不就不叫函数了。组函数不计算空值吗?对,因为它不计算空值,所以我们才用到那个NV对吧?V2子距可否使用组函数进行过滤,这个我们要严格注意,是不行不可以的。
13:00
用什么呀?用having替代吧。如果你要是没有组函数的话,你可以用外过滤。查询好,这是这几个题目了,我们把它。搂过来。先来四个吧。先看这个查询公司员工公司的最大最小平均总和,这个不难,Select max salary。平均AG总和。这是整个公司的里边没有涉及到其他的,这都是组函数,所以没有跟勾法有有啥关系,也就这样写呗。
14:07
没问题。下一个。查询各job ID的员工工资的最大最小平均工作,这就类似了,就是用一下比如说Y不同job ID的AV max salary mean salary,然后呢,Avg salary some somelo is。大家需要注意一下,是不是得用头快了,OK?
15:05
这呢就是按不同的照白D来进行一个区分,最大,最小,平均和总和。这么多。接着。选择具有各个job ID的员工人数,选择就是select,就是查询一下各个job ID的员工人数拉的表白的。人数,Count。这这这得写了是吧?方你按什么来写,你可以写一个星也行,就是你看不同的照白地里边都有哪些。也可以,你写上一个employee ID也行,对吧,From employees到白。
16:06
各个招布ID的员工人数。这有这么多啊。第七个,查询员工最高工资和最低工资的差距,差距公司当中的,嗯,我们先看一下这个最高工资,就差最低工资呢,叫命salary,他们之间的差距不就是这个最高的,让它减去一下最低的吧,然后起了个别名叫differences。
17:07
你不好意思,这还用不用加勾一看是没有除了主函数以外的列,那就不用了。看一下这个结果。哎,它俩一减,就是这个最后的值。下边第八题第九题我们做这里啊。先看第八个,查询各个管理者手下员工的最低工资。其中最低工资不能低于6000,没有管理者的员工不计算在内,这是不是都是一些条件限制啊,或者叫过滤条件,前边是我们真正要查的,看一下他让查询各个管理者,那就是manager ID,你看这是不是就是各个管理者的ID啊,你看各个ID里边的他手下的最低工资是不是得按一下管理者进行一个。
18:22
分组吧。比如说101号,这是一个管理者,他下面有十个人,他十个人里边谁的是最低的,按他进行分组,把它放在group拜中,最低工资命salary from employees,先把它框先写出来,那该ID对吧,不同管理者手下的。这个最低工资,然后看后边这个题目。这个要求哈,说其中最低工资啊,不能低于6000,没有管理者的员工不计算在内,没有管理者的员工,那就是外要求这个manager ID的is not not吧,同时呢,是不是还有一个条件叫having,最低工资mean salary要求不能低于,那得大于等于。
19:20
有钱。是吧,我们看一下这个题这个啊。那就是这样,这是不同的卖ID,这是他们手下的最低工资,把小于6000的都给过滤掉。OK。对,回来说一下,有的同学可能会有这个疑问,我这呢,是不是既用了where又用了海是吧?那我能不能把这个给它注掉?把它给它写在一起,Ctrl c and ctrl c,就是都写在having having不是表示过滤嘛,是吧,能不能这样处理,看看是不是也行啊,两种方式都可以啊。
20:14
然后我们看第九题。查询所有部门的名字location ID,员工数量和工资的平均值,所有部门的名字,名字是不是在department表里吧,然后呢,Location ID location I也是它这个表里的员工数量、工作平均值。好了,来吧,Select。Department name。OKID员工数量,Count。我就用星吧平工资的平均值,Avg salary from employees e。
21:06
嗯。Department d e.department ID等于D点比它们还低,这有连接条件了,然后呢?是不是把他俩得放在国当中啊?所有部门的1234,有的部门是不是没有人呢?他让查所有的,那怎么办?相当于是不是这个表的数据多,这个表的。少一点,那这边给整个加号相当于是不是右外连接,也就是right out draw。
22:03
是吧,你看一下我们假设要不加的话,CTRLX啊,我先给你删掉,看一下这个结果。有11个部门都对应的有值。加上right out john27个。抗星这里边的抗德星,我们看到这个结果,它显示的是一对吧,那这个应该怎么去理解啊。那我们看一下我们刚才写的这哈。我这写看得清,它之所以显示的是一,是不是就因为我们前面放的这个是叫department name,就是相当于你往部门往这放的,他当然是有一个部门,所以这显它就是一对吧,那我们这写星的话,我不写星了,改一改写什么呀,我就写你这个employee ID,这样的话就看到假设你这个部门里边没有员工的话,那相当于都是now呗。
23:03
对吧,我们现在看一下这个结果。这个时候就对了。没有人,这就相当于是把所有部门的都给输读出来了,这个题目这样写是最完整的。好了,然后我们看第十题。这个题啊,有一个表格是吧,有个表格,那我们就。这样把它放在这边。这个题目第十题有一点难度,需要结合我们前面讲的条件表达式的。大家你下来把这个题目做一做,当然这个写法也不唯一,有多种多样,你选择一个你好理解的,当然尽量的也简洁,对吧?查询公司在九五年到九八年之间每年雇佣的人数,结果类似于下面的合适。
24:16
九九年到九五年到九八年,这是总共有20个人,这是各年分别的人数,那么这个怎么来写?这个年之间的所有人数,这肯定是用到总函数了,对吧。这个题目我们先给他分解一下,大家你看看,当然这是需要我们一次性查出来的,你不能说我查一下这个年之间的,我再查一次九五年的,再查一次九六年,那不行,他是让你通过一次查询,把这个表格的形式这样的给它列出来。那我们先分着来肢解一下大家,你先能不能通过一个查询操作把这个给搞定啊,查询一下九五年到九八年这个之间总人数是供应多少。
25:02
总人工多少?那怎么写啊?那select肯定是count了,对吧?查询一下select count。Count什么呀?看清吧,Count氢,然后起一个别名。Total from employees,这样你要是写的话,是不是一百零六一百零七了。他是说只让找九五年到九八年之间的,那得相当于是一个过滤条件,我先用where来写了,那where要求你的。Para date吧,Para date得在九五年到九八年,这是不是9598,我用字符的话开data,我得给它转成字符,所以我用一下独唱。
26:00
然后转成我只要年就可以了,YYYY,当你转成这个格式以后,这个呢,现在是一个号类型的,这个年我只能让你印这样几个哪几个呀,1995。是这个意思吧。只让在这之间的,我们先看一下这个结果是多少。没有正确接触看一下。这找一个单引号对吧。65个人,也就是说在九五年到九八年之间,一共呢,是有65个人来了,那如果说让大家你判断一下九九往往九五年的,你是不是把后边这个给他干掉就行了。
27:01
没错,那现在如果他想让你在一个当中写,怎么去处理呀。正常的话,如果让你一个表中出现,是不是就应该是刚才这是一列,然后你再是不是得整一个一列,然后这里边。仅是九五年的这些人参与运算的,然后再来一个仅仅让里边九六年的参与算,然后再再接着往后是这意思吧,好了,那我们95年96年实际上你搞定一个那个呢,就顺势的来做就行,我们先写一个九五年。方便,嗯,我就先就就先这样写吧,九五年就是当你进来一条数据的时候。肯定这边要走一下,走一下这些数据。九五年到九八年的这些数据都往这走的话,每一个都算一条,一共是65个,当你这每一条数据都往这走的话,我应该是让仅仅九五年的时候是不是加一个一,其他的这三年都应该不加吧。
28:01
咱们就能让它不假,是不是我们讲了只要你是空的就不假了。那相当于是我进来一条数据,我看了看一下,你是是不是九五年的,是不是得需要进行一个判断呀。如果你是九五年的,我就让你加一,不是的话就不加一。好,我们使用判断抵扣的吧,抵扣的谁?抵扣的应该是扣her date。Date呢,判断是不是九五年,这又是一个字符串,所以我在这还得使用一个字符串。它对,然后还是转成只要粘的这种格式。大家这个时候你要特别小心这个小括号啊。我这儿得补一个小括号。对,当这个时候变变成这个。字符以后如果它这个字符,我这里加个逗号吧。字符呢,它是1995年的,如果是这一年的话,后边是不是对应这一年的一个值啊,我给你个值,比如就叫一。
29:06
这给个直角一,如果不是九五年的呢,我就直接让你else就完了呗,全部让你当个now。全部上当个now,这个时候你看非常巧妙啊,当你从这些年过来这些数据的时候,我到这儿判断的时候,看看你这个hard data是否是九五,如果是九五给值是一,其他的都作为else都给它now,这样你在count的时候是不是就光算九五年?然后我给他再起一个别名,别名1995。这加上一个逗号呗。然后九六是不是也是这样做呀。这太长了,我给它拉到下一行CTRLX。这是九五年的,我们先看一眼。对不对。说缺失表达式,这这个逗号是不是不能要了。
30:02
好了,九五年出来了,这95年一出来,实际上这个题目基本上就已经迎刃而解了。我要算九六年的话,我只需要在你后边这补呗,9697的九八的改一下。这这是不是就行了,把这个逗号去掉。好了,这就是我们这个题就这样来解决的,你算一下这三个这这四个数加起来是不是65啊。50 50。是六十六十一六十五对吧,没错。等于负C。所以知识越学越多,有的时候为什么说后边越讲越难呀,就是因为你越往后讲的话,他需要跟你之前的那个知识给你整合在一起,所以你得先把前面这个知识给他打牢了。
31:05
大家你下来了,把这个题目再自己去实现一下,做一做,这一节呢,我们就讲到这儿。
我来说两句