--查询课有一门或以上课程的课时超过80的教师的id和name
--1
select t.id,t.name
from teacher t ,Timetable t2
where t.id = t2.teacher
group by t.id,t.name
having sum(iif(t2.courseHour > 80,1,0)) >= 1
--2 仅当条件为一或者更多一下语句可以实现
select distinct t.id,t.name
from teacher t ,Timetable t2
where t.id = t2.teacher and t2.courseHour > 80
select s.name,t.name,c.name,sc.grade ,tt.courseHour
from SelectCourse sc ,teacher t ,student s ,Timetable tt,course c
where tt.teacher = t.id
and tt.course = c.id
and sc.course = c.id
and s.id = sc.student
and tt.courseHour > 80
order by s.name
insert into SelectCourse(student,course,grade)
values ((select id from student where name = N'七七'),(select id from course where name = N'社会安全'),20),
((select id from student where name = N'甘雨'),(select id from course where name = N'社会安全'),100),
((select id from student where name = N'尤拉'),(select id from course where name = N'社会安全'),80),
((select id from student where name = N'甘雨'),(select id from course where name = N'格斗'),60),
((select id from student where name = N'尤拉'),(select id from course where name = N'历史'),80),
((select id from student where name = N'甘雨'),(select id from course where name = N'历史'),80),
((select id from student where name = N'工子'),(select id from course where name = N'格斗'),100),
((select id from student where name = N'甘雨'),(select id from course where name = N'经济'),80),
((select id from student where name = N'工子'),(select id from course where name = N'经济'),10),
((select id from student where name = N'可莉'),(select id from course where name = N'茶艺'),20),
((select id from student where name = N'七七'),(select id from course where name = N'茶艺'),20)
((select id from student where name = N'可莉'),(select id from course where name = N'社会安全'),30)
alter table SelectCourse
add student uniqueidentifier references student(id)
insert into Timetable (course,teacher,courseHour)
values((select id from course where name = N'社会安全'),(select id from teacher where name = N'琴'),50),
((select id from course where name = N'格斗'),(select id from teacher where name = N'迪卢克'),200),
((select id from course where name = N'调酒'),(select id from teacher where name = N'迪卢克'),50),
((select id from course where name = N'历史'),(select id from teacher where name = N'钟离'),200),
((select id from course where name = N'茶艺'),(select id from teacher where name = N'钟离'),150),
((select id from course where name = N'经济'),(select id from teacher where name = N'凝光'),150)
alter table course
drop column teacher
alter table course
drop constraint FK__course__teacher__32E0915F
insert into course(name,teacher)
values(N'社会安全',(select id from teacher where name = N'琴')),
(N'调酒',(select id from teacher where name = N'迪卢克')),
(N'格斗',(select id from teacher where name = N'迪卢克')),
(N'历史',(select id from teacher where name = N'钟离')),
(N'茶艺',(select id from teacher where name = N'钟离')),
(N'经济',(select id from teacher where name = N'凝光'))
insert into teacher(name)
values(N'琴'),(N'迪卢克'),(N'钟离'),(N'凝光')
insert into student (name)
values(N'可莉'),(N'七七'),(N'工子'),(N'甘雨'),(N'尤拉')
sp_rename 'Timetable.keshi','courseHour','column'
sp_rename 'CourseHour','Timetable'
create table CourseHour(
course uniqueidentifier references course(id),
teacher uniqueidentifier references teacher(id),
keshi int NOT NULL
)
create table SelectCourse (
id uniqueidentifier primary key default newid(),
course uniqueidentifier references course(id),
grade int NULL
)
create table course(
id uniqueidentifier primary key default newid(),
name nvarchar(10) not null,
teacher uniqueidentifier references teacher(id)
)
sp_rename 'tercher','teacher'
create table tercher (
id uniqueidentifier primary key default newid(),
name nvarchar(10) not null
)
alter table student
add constraint id_def_val default (newid()) for id
alter table student
add name nvarchar(10) not null
sp_rename 'aa','student'
create table aa(
id uniqueidentifier primary key
)
