--查询课有一门或以上课程的课时超过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 
)