Question

sql查询语句，在线等，急，急！QQ：495537346

假如有如下四张表：
Teacher
id
name
age
salary
school
101
Alice
32
4000
201
102
Bernard
41
3700
202
…
…
…
…
…

School
id
name
address
201
FLS
A.B.C
202
CRS
D.C.B
…
…
…

Subject
id
name
attribute
301
English
Optional
302
Chinese
Optional
…
…
…

Class
id
name
401
A
402
B
…
…

Score
id
teacher
subject
class
score
501
101
301
401
98
…
…
…
…
…

1. 查询每个学校中工资最高的老师的名字，结果如下：
name
Alice
…

2. 假设age及salary字段上建有索引，请充分利用该索引来查询年龄在40以上且工资不足4000的所有老师的名字，结果如下：
name
Bernard
…

3. 查询各个学校中工资不足4000的老师的平均工资，结果如下：
school
avg salary
FLS
3200
…
…

4. 将CRS学校中工资不足4000的老师都涨500的工资；
5. 创建一个老师工作地点的视图，字段包括teacher name/teacher age/school name/school address；
6. 查询Alice老师和Bernard老师所教的超过10个人选修（attribute = Optional）的科目的名称；
7. 查询CRS学校的老师所教的各个班级各个科目的班级名称、科目名称以及平均分，展示结果应首先展示完一个班级的所有科目，然后再进行下一个班级的科目。结果如下：
class name
subject name
avg score
A
English
88
A
Chinese
78
…
…
…
把七个题的SQL语句写出来 ，还会加分，谢谢，急急急

Answer 1

/*====================
创建表及导入数据
====================*/
create table teacher(id int,name varchar(30),age int,salary numeric(18,2),school int)
GO
insert into teacher
select 101,'Alice',32,4000,201 union
select 102,'Bernard',41,3700,202
GO
create table School(
id int,
name varchar(100),
address varchar(200),
)
GO
insert into School
select 201,'FLS','A.B.C' union
select 202,'CRS','D.C.B'
GO
create table Subject(
id int,
name varchar(100),
attribute varchar(100)
)
GO
insert into Subject
select 301,'English','Optional' union
select 302,'Chinese','Optional'
GO
create table Class(
id int,
name varchar(100)
)
GO
insert into Class
select 401,'A' union
select 402,'B'
create table Score(
id int,
teacher int,
subject int,
class int,
score int
)
GO
insert into Score
select 501,101,301,401,98
GO
--1.
select name from teacher where salary=(select max(salary) from teacher)
GO
/*如果是每个学校的老师，应该需要有学校的资料才对*/
select School, max(salary) as salary_max
from teacher
group by School
GO
--2.
select name from teacher where age>=40 and salary<4000
GO
--3.
select name, salary_s / counta
from(
select school,count(*) counta,sum(salary) salary_s from teacher
where salary<4000
group by school
) A left join school s on A.school=s.id
--4.
update teacher
set salary=salary+500
where salary<4000 and school=(select id from school where name='CRS')
GO
--5
create view vTeacherAddr
AS
select t.name, t.age, s.name, s.address
from teacher t left join school s on t.school=s.id
GO
--6.
select subject.name,count(*)
from Score s left join teacher t on s.teacher=t.id
left join subject on s.subject=subject.id
where t.name in ('Alice','Bernard') and subject.attribute='Optional'
group by subject.name
having count(*)>10
--7.
select c.name,subject.name,sum(score)/count(*)
from Score s left join class c on s.class=c.id
left join subject on s.subject=subject.id
group by c.name,subject.name
order by c.name,subject.name
GO

Answer 2

前5个题的答案
select name,max(salary) from teacher group by school

select name from teacher where age>=40 and salary<4000
select school,avg(salary) avg_salary from (select school,salary from teacher where salary < 4000) group by school
update teacher set salary = salary + 500 where id in (select id from teacher where salary<4000 and school = (select id from school where name = 'CRS'));
create view as
select teacher.name, teacher.age, school.name, school.address from teacher join school on teacher.school = school.id

追问

前五个确定没问题吗兄弟，后两个继续，给加分 谢谢 急急急

Answer 3

1
select name from
(select school,max(salary) as salary from teacher
group by school) b
left join teacher a
on a.school=b.school and a.salary=b.salary

2
select name
from teacher
where age>40 and salary<4000

3
select b.name,avg(salary) as 'avg salary'
from teacher a
left join shcool b
on a.shcool=b.id
where salary<4000
group by b.name

4
Update teacher set salary=salary+500
Where school in
(select id from shcool where name='CRS')

5
Create View 老师工作地点
as
select a.name,age,b.name as school,addr
from teacher a,shcool b
where a.shcool=b.id

6 10个人不是很清楚，表中没有学生情况，只好用班级数
select class,count(*) from score where teacher in
(Select id from teacher where name in ('Alice','Bernard'))
and subject in
(select id from subject where attribute='Optional')
group by class
having count(*)>10

7
select c.name as class,b.name as subject,avg(score) from score a
left join subject b on a.subject=b.id
left join class c on a.class=c.id
where teacher in
(select id from teacher where school in
(select id from school where name='CRS'))
group by c.name,b.name
order by c.name,b.name