头歌的数据库的第二次作业的答案
目录
MySQL-视图
第1关：创建所有保险资产的详细记录视图
第2关：基于视图的查询
MySQL数据库 - 连接查询
第1关：内连接查询
第2关：外连接查询
第3关：复合条件连接查询
MySQL数据库 - 子查询
第1关：带比较运算符的子查询
第2关：关键字子查询
MySQL数据库 - 使用聚合函数查询
第1关：COUNT( )函数
第2关：SUM( )函数
第3关：AVG( )函数
第4关：MAX( )函数
第5关：MIN( )函数
MySQL数据库 - 分组选择数据
第1关：GROUP BY 与 聚合函数
第2关：使用 HAVING 与 ORDER BY
MySQL-视图
第1关：创建所有保险资产的详细记录视图
任务描述
本关任务：创建所有保险资产的详细记录视图。
use finance1;
-- 创建包含所有保险资产记录的详细信息的视图v_insurance_detail，包括购买客户的名称、客户的身份证号、保险名称、保障项目、商品状态、商品数量、保险金额、保险年限、商品收益和购买时间。
-- 请用1条SQL语句完成上述任务：create view v_insurance_detail
as
select c_name,c_id_card,i_name,i_project,pro_status,pro_quantity,i_amount,i_year,pro_income,pro_purchase_time
from (select * from (select * from (select * from client union select * from client new_client) a join property b on a.c_id = b.pro_c_id) c join insurance d on c.pro_pif_id = d.i_id) e where e.pro_type = '2';/* end of your code */第2关：基于视图的查询
任务描述
本关任务：基于视图v_insurance_detail查询每位客户保险资产的总额和保险总收益。
-- 基于上一关创建的视图v_insurance_detail进行分组统计查询，列出每位客户的姓名，身份证号，保险投资总额(insurance_total_amount)和保险投资总收益(insurance_total_revenue),结果依保险投资总额降序排列。-- 请用一条SQL语句实现该查询：select c_name,c_id_card,sum(i_amount*pro_quantity) as insurance_total_amount,sum(pro_income) as insurance_total_revenuefrom v_insurance_detailgroup by c_id_cardorder by insurance_total_amount desc/* end of your code */MySQL数据库 - 连接查询
第1关：内连接查询
任务描述
本关任务：使用内连接查询数据表中学生姓名和对应的班级。
USE School;########## 查询数据表中学生姓名和对应的班级 ##########
#请在此处添加实现代码
########## Begin ##########select s.name studentName,c.name className
from
tb_student s
join
tb_class c
on(s.class_id=c.id);########## End ##########第2关：外连接查询
任务描述
本关任务：使用外连接查询数据表中所有班级和对应班级里学生的姓名。
USE School;########## 使用左外连接查询所有学生姓名和对应的班级 ###########请在此处添加实现代码
########## Begin ##########select s.name studentName,c.name className
from
tb_student s
left join
tb_class c
on(s.class_id=c.id);########## End #################### 使用右外连接查询所有学生姓名和对应的班级 ###########请在此处添加实现代码
########## Begin ##########select s.name studentName,c.name className
from
tb_student s
right join
tb_class c
on(s.class_id=c.id);########## End ##########第3关：复合条件连接查询
任务描述
本关任务：使用连接查询，查询所有班级里分数在90分以上的学生的姓名和学生的成绩以及学生所在的班级。
USE School;########## 查询所有班级里分数在90分以上的学生的姓名和学生的成绩以及学生所在的班级 ##########
#请在此处添加实现代码
########## Begin ##########select s.name studentName,score,c.name className
from
tb_student s
join
tb_class c
on(s.class_id=c.id)
where s.score>90;########## End ##########MySQL数据库 - 子查询
第1关：带比较运算符的子查询
任务描述
本关任务：查询大于所有平均年龄的员工姓名与年龄。
USE Company;#请在此处添加实现代码
########## Begin ##########
#1.查询大于所有平均年龄的员工姓名与年龄select name,age
from tb_emp
where age>all(select avg(age) from tb_emp
);########## End ##########第2关：关键字子查询
任务描述
本关任务：根据要求使用关键字进行查询。
USE Company;
#请在此处添加实现代码
########## Begin ###########1.使用 ALL 关键字进行查询
select position,salary
from tb_salary
where salary>all(select salary from tb_salary where position='Java'
);
#2.使用 ANY 关键字进行查询
select position,salary
from tb_salary
where salary>any(select salary from tb_salary where position='Java'
);
#3.使用 IN 关键字进行查询
select position,salary
from tb_salary
where position in('Java');
########## End ##########MySQL数据库 - 使用聚合函数查询
第1关：COUNT( )函数
任务描述
本关任务： 1.使用COUNT()函数查询数据表中总数据量， 2.使用COUNT()函数统计班级总人数。
USE School;#请在此处添加实现代码
########## Begin #################### 查询该表中一共有多少条数据 ##########select count(*) from tb_class;########## 查询此表中367班有多少位学生 ##########select classid,count(*) from tb_class where classid=367;########## End ##########第2关：SUM( )函数
任务描述
本关任务： 1.使用SUM()函数查询数据表中学生的总成绩； 2.使用SUM()函数查询语文课程中学生的总成绩。
USE School;#请在此处添加实现代码
########## Begin #################### 查询所有学生总分数 ##########select sum(score) from tb_class;########## 查询学生语文科目的总分数 ##########select course,sum(score) from tb_class where course='语文';########## End ##########第3关：AVG( )函数
任务描述
本关任务：使用AVG()函数查询学生各科目的平均分数。
USE School;#请在此处添加实现代码
########## Begin #################### 查询学生语文科目的平均分数 ##########select course,avg(score) from tb_class where course='语文';########## 查询学生英语科目的平均分数 ##########select course,avg(score) from tb_class where course='英语';########## End ##########第4关：MAX( )函数
任务描述
本关任务：使用MAX()函数查询各科中的最高分数。
USE School;#请在此处添加实现代码
########## Begin #################### 查询语文课程中的最高分数 ##########select course,max(score) from tb_class where course='语文';########## 查询英语课程中的最高分数 ##########select course,max(score) from tb_class where course='英语';########## End ##########第5关：MIN( )函数
任务描述
本关任务：使用MIN()函数查询学生在各科中的最低分数。
USE School;#请在此处添加实现代码
########## Begin #################### 查询语文课程中的最低分数 ##########select course,min(score) from tb_class where course='语文';########## 查询英语课程中的最低分数 ##########select course,min(score) from tb_class where course='英语';########## End ##########MySQL数据库 - 分组选择数据
第1关：GROUP BY 与 聚合函数
任务描述
本关任务：使用GROUP BY关键字结合聚合函数将数据进行分组。
USE School;#请在此处添加实现代码
########## Begin ###########1.查询表中2,3,4年级中分别男女的总人数select gradeId,sex,count(*)
from student
where gradeId in(2,3,4)
group by gradeId,sex;########## End ##########第2关：使用 HAVING 与 ORDER BY
任务描述
本关任务：按照要求编写sql查询语句。
USE School;#请在此处添加实现代码
########## Begin ###########1.查询表中至少有两门课程在90分以上的学生信息select sno,count(*) from tb_grade
where score>=90
group by sno having count(1)>=2;#2.查询表中平均成绩大于90分且语文课在95分以上的学生信息select sno,avg(score)
from tb_grade
where sno in(select sno from tb_grade where pno='语文' and score>=95
)
group by sno
having avg(score)>=90;########## End ##########