SB.sql
2021/5/17 2:56:10
本文主要是介绍SB.sql,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
--查询课有一门或以上课程的课时超过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 )
这篇关于SB.sql的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-06-19《2023版Java工程师》课程升级公告
- 2024-06-15matplotlib作图不显示3D图,怎么办?
- 2024-06-1503-Loki 日志监控
- 2024-06-1504-让LLM理解知识 -Prompt
- 2024-06-05做软件测试需要懂代码吗?
- 2024-06-0514-ShardingSphere的分布式主键实现
- 2024-06-03为什么以及如何要进行架构设计权衡?
- 2024-05-31全网首发第二弹!软考2024年5月《软件设计师》真题+解析+答案!(11-20题)
- 2024-05-31全网首发!软考2024年5月《软件设计师》真题+解析+答案!(21-30题)
- 2024-05-30【Java】百万数据excel导出功能如何实现