实训三 多表查询

多表查询-连接查询（一）

第1关：自然连接

编程要求

根据提示，在右侧编辑器补充代码，使用自然连接求选修了严敏老师的数学分析课程的学生的姓名、课程名称、教师名和成绩。

 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
  select sname,cname,tname,grade 
  from student natural join score  natural join course natural join teach
  where tname like '严敏';
  
 /**********End**********/

第2关：等值连接

编程要求

根据提示，在右侧编辑器补充代码，使用等值连接求选修了严敏老师的数学分析课程的学生的姓名、课程名称、教师名和成绩。

 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
  select sname,cname,tname,grade
  from student,score,course,teach
  where student.sno = score.sno and score.tno = teach.tno and score.cno = course.cno and
  tname like '严敏' and cname like '数学分析';
  
 /**********End**********/

第3关：JOIN连接

编程要求

根据提示，在右侧编辑器补充代码，使用 JOIN 连接求选修了严敏老师的数学分析课程的学生的姓名、课程名称、教师名和成绩。

 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
  select sname,cname,tname,grade
  from score
  inner join student on score.sno = student.sno
  inner join course on score.cno = course.cno
  inner join teach on score.tno = teach.tno
  where tname = '严敏' and cname = '数学分析';
  
 /**********End**********/

第4关：自身连接查询

编程要求

在右侧编辑器补充代码，使用自身连接查询求年龄大于'刘东明' 的所有学生的姓名与出生日期。

 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
  select b.sname,b.birthday
  from student as a,student as b
  where a.sname = '刘东明'
  and a.birthday > b.birthday

  
 /**********End**********/

第5关：外部连接查询

编程要求

在右侧编辑器补充代码，使用外部连接查询求未选修任何课程的学生的学号和姓名。

 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
  select  student.sno,sname
  from student left join score on (student.sno = score.sno)
  where score.sno is null;
  
 /**********End**********/

多表查询- 连接查询（二）

第1关：内连接查询

编程要求

在右侧编辑器补充代码，查询数据表中学生姓名以及对应的班级名称，将其对应的列名分别另命名为studentName和className。

USE School;

########## 查询数据表中学生姓名和对应的班级 ##########
#请在此处添加实现代码
########## Begin ##########
select tb_student.name as studentName, tb_class.name as className from tb_student join tb_class on tb_class.id = tb_student.class_id;



########## End ##########

第2关：外连接查询

编程要求

在右侧编辑器补充代码，分别使用左外连接和右外连接查询数据表中所有学生姓名和对应的班级名称，查询结果列分别另命名为studentName和className。

USE School;

########## 使用左外连接查询所有学生姓名和对应的班级 ##########

#请在此处添加实现代码
########## Begin ##########
select tb_student.name as studentName,tb_class.name as className from tb_class right join tb_student on tb_class.id =tb_student.class_id;



########## End ##########

########## 使用右外连接查询所有学生姓名和对应的班级 ##########

#请在此处添加实现代码
########## Begin ##########
select tb_student.name as studentName,tb_class.name as className from tb_class left join tb_student on tb_class.id =tb_student.class_id;



########## End ##########

第3关：复合条件连接查询

编程要求

在右侧编辑器补充代码，查询所有班级里分数在90分以上的学生的姓名和学生的成绩以及学生所在的班级，其中学生的姓名和学生所在班级分别另命名为studentName和className。

USE School;

########## 查询所有班级里分数在90分以上的学生的姓名和学生的成绩以及学生所在的班级 ##########
#请在此处添加实现代码
########## Begin ##########
select s1.name as studentName,score,s2.name as className
from tb_student as s1,tb_class as s2
where s1.class_id=s2.id
and s1.score>90
order by score desc;



########## End ##########

多表查询-子查询（一）

第1关：子查询

程要求

在右侧编辑器补充代码，求年龄大于'刘东明' 的所有学生的姓名与出生日期。

 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
  select sname,birthday
  from student
  where birthday<(select birthday from student where sname = '刘东明');
  
 /**********End**********/

第2关：子查询-练习

编程要求

根据提示，在右侧编辑器补充代码，求未选修任何课程的学生的学号和姓名。

 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
  select sno,sname
  from student
  where sno not in(select sno from score)
  
 /**********End**********/

第5关：子查询-练习三

编程要求

在右侧窗口补充代码，求选修了'刘东明' 同学选修的全部课程的学生的学号。

 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/

 /**********Begin**********/
  select sno
  from score
  where exists(select * from student where student.sno = score.sno and sname = '刘东明');

 /**********End**********/

第6关：子查询-练习四

编程要求

根据提示，在右侧编辑器补充代码， 求选修了全部课程的学生的学号。

 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
  select sno
  from student
  where not exists
        (select * from course where not exists 
            (select * from score where sno=student.sno and cno=course.cno));
  
 /**********End**********/

多表查询 - 子查询（二）

第1关：带比较运算符的子查询

编程要求

在右侧编辑器补充代码，查询大于所有平均年龄的员工姓名与年龄。

USE Company;

#请在此处添加实现代码
########## Begin ##########
#1.查询大于所有平均年龄的员工姓名与年龄
select name,age
from tb_emp
where age>(select avg(age) from tb_emp);


########## End ##########

第2关：关键字子查询

编程要求

我们为你提供了如下数据表： tb_salary表数据：

id	position	salary
1	Java	8000
2	Java	8400
3	Java	9000
4	Python	6500
5	Python	10000

根据提供的数据，在右侧编辑器中补充代码：

1. 查询薪资表中比Java最高工资高的所有员工职位名称和薪资；

2. 查询薪资表中比Java最低工资高的所有员工职位名称和薪资；

3. 查询薪资表中职位为Java的所有员工职位名称和薪资。

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 salary in (select salary from tb_salary where position = 'Java');
########## End ##########

大学数据库创建与查询实战

第1关：数据库表设计

编程要求

在右侧编辑器中的Begin-End之间补充代码，创建教师信息表和开课信息表（其他六张表系统已为你创建完成），使用 universityDB 数据库，创建的表命名依次为 instructor 和 section。

#请在此添加创建开课信息表的SQL语句
########## Begin ##########
use universityDB;
create table instructor(
    ID varchar(5) comment'教师号' ,primary key(ID),
    name varchar(20) comment'教师名字' not null,
    dept_name varchar(20) default null comment'教师所在的系',
    salary numeric(8,2) comment '薪资' check(salary>29000),
    foreign key (dept_name) references department(dept_name)
    on delete set null 
);
create table section(
    course_id varchar(8)  comment '课程号' ,
    sec_id varchar(8) comment '课程段标识',
    semester varchar(6) comment'学期' check(semester in('Fall','Winter','Spring','Summer')),
    year numeric(4,0) comment '年份' ,
    building varchar(15) default null comment '课程所在建筑' ,
    room_number varchar(7) default null comment '课程所在教师号',
    time_slot_id varchar(4) comment '时间档序号',
    primary key (course_id,sec_id,semester,year),
    foreign key (course_id) references course(course_id)
    on delete cascade,
    foreign key (building,room_number) references classroom(building,room_number)
    on delete set null
);

########## End ##########

第2关：查询（一）

编程要求

在右侧编辑器中的Begin-End之间补充代码，完成下列查询：

1. 查询修了‘ Biology ’所有课程的学生姓名；

2. 查询满足工资至少比‘ Biology ’系某一个教师的工资高的所有教师的姓名；

3. 查询所有教师的姓名，以及他们所在系的名称和系所在建筑的名称；

4. 查询在包含‘ Watson ’字段的大楼开课的所有系名。

#********* Begin *********#
echo "
select name
from student
where  ID in
     (  select ID
        from takes,course
        where dept_name='Biology'
            and  takes.course_id=course.course_id
     );
 
select name
from instructor
where salary >
(  
    select salary
    from  instructor
    where dept_name='Biology'
);
 
select distinct name, instructor.dept_name,building
from  instructor,department
where  instructor.dept_name= department.dept_name;
 
select dept_name
from  department
where building='Watson';
 
 


"

#********* End *********#

第3关：查询（二）

编程要求

在右侧编辑器中的Begin-End之间补充代码，完成下列查询：

1. 查询2010年春季学期教课的教师数；

2. 查询2009年秋季学期讲授2门课程的教师信息；

3. 查询每个系在2010年春季学期讲授至少一门课的教师人数。

#********* Begin *********#
echo "
select count(distinct ID)
from teaches
where year = '2010' and semester = 'Spring';

select ID,name,dept_name
from instructor
where  ID in(select ID from teaches where year = '2009' and semester = 'Fall' GROUP BY ID having count(ID)>=2);

SELECT dept_name,count(distinct instructor.ID) as instr_count
FROM instructor  join teaches on instructor.ID = teaches.ID
WHERE semester = 'spring' and year = '2010'
GROUP BY dept_name;
 
"
#********* End *********#

第4关：查询（三）

编程要求

在右侧编辑器中的Begin-End之间补充代码，完成下列查询：

1. 根据工资将教师信息表（instructor）关系按工资的降序、姓名的升序排列；

2. 查询大学里的最高工资；

3. 找出平均工资最高的系；

4. 查询教师平均工资超过50000的系，并按系名升序和平均工资降序排序。

注意：编写查询语句时，需要查询列表所有信息时，请使用表名.*（由于评测原因指定这样书写，实际自己使用可以直接*的）。

#********* Begin *********#
echo "
select instructor.*
from instructor
group by salary desc,name asc;

select max(salary)
from instructor;

select dept_name
from instructor
group by dept_name having avg(salary) >= all
(select avg(salary) from instructor group by dept_name);


select dept_name,avg(salary) as avg_salary 
from instructor group by dept_name
having avg(salary)>50000 order by dept_name asc, avg_salary desc;
"
#********* End *********#

第5关：查询（四）

编程要求

在右侧编辑器中的Begin-End之间补充代码，完成下列查询：

1. 查询在2009年秋季学期开课，但不在2010年春季学期开课的课程名称；

2. 查询2009年秋季和2010年春季都开设的所有课程号；

3. 查询2009年秋季学期和2010年春季学期开设的所有课程；

4. 对于在2009年讲授的每个课程段，如果该课程段至少有两名学生选课，查询该课程段信息和选修该课程段的所有学生的总分数（ tot_cred ）的平均值。

#********* Begin *********#
echo "
select distinct course_id 
from section 
where semester='Fall' and year=2009 and
course_id not in (select course_id from section where semester='Spring' and
year=2010);
 
select course_id 
from section as S 
where year=2009 and semester='Fall' and exists
(select section.* from section as T where year=2010 and semester='Spring' and
S.course_id=T.course_id);
 
select course_id 
from section 
where year=2009 and semester='Fall' union all select
course_id from section where year=2010 and semester='Spring';
 
select course_id,semester,year,sec_id,avg(tot_cred) 
from takes join student join department on takes.ID = student.ID 
where year=2009 group by course_id,semester,year,sec_id having count(takes.ID)>=13;
 
 
"
#********* End *********#

第6关：查询（五）

编程要求

在右侧编辑器中的Begin-End之间补充代码，完成下列查询：

1. 查询‘ Physics ’系的所有教师姓名及其课程号；

2. 查询所有在‘ Physics ’系并且工资大于80000的教师姓名；

3. 年度工资增长，请你更新各教室的工资，要求10万元以上的教师涨3%，10万元及以下的教师涨5%（注意要先更新工资大于10万的，再更新小于10万的，否则先更新小于10万的，可能更新后大于10万了）；

4. 创建视图，列出‘ Physics ’系在2009年秋季学期开设的所有课程段，以及课程段在哪栋建筑的哪个房间授课，视图名称为Physics_fall_2009。

#********* Begin *********#
echo "
select instructor.name,teaches.course_id 
from instructor,teaches 
where instructor.ID=teaches.ID and dept_name='Physics';
 
select name 
from instructor 
where dept_name='Physics' and salary>80000;
update instructor set salary = case when salary <= 100000 then salary*1.05 else salary*1.03 end;
create view Physics_fall_2009 as select course.course_id,building,room_number from course,section where course.course_id = section.course_id and course.dept_name='Physics' and section.semester='Fall' and section.year='2009';
 
 
"
#********* End *********#