sql课程表默认值设置

『壹』 用SQL语句实现：学生表、课程表、选课表三张表中的问题：

用子查询
select 姓名
from 学生,选课,课程
where 学生.学号=选课.学号 and 选课.课程号=课程.课程号 and 课程.课程名='数据库'
and 学生.学号 in
(select 学生.学号
from 学生,选课,课程
where 学生.学号=选课.学号 and 选课.课程号=课程.课程号 and 课程.课程名='操作系统';
}

『贰』 SQL语句的一道题 三个基本表：学生表(Student)、课程表（Course）、学生选课表（SC）

1. select * from SC
2. select Sname，Sage from Student where Sdept = '计算机'
3. select Sno,Cno,Grade from SC where Grade >= 70 and Grade <= 80
4. select Sname,Sage from Student where Sage between 18 and 20 and Ssex = '男'
5. select top 1 Grade from SC where Cno = 'C01'
6. select max(Sage),min(Sage) from Student
7. select Sdept,sum(Sno) from Student group by Sdept
8. select course.Cname,sum(sc.Sno),max(Grade) from SC
join studet on Student.Sno = SC.Sno
join Course on Course.Cno = SC.Cno
group by course.cname,max(grade)

9. select sum(Cno),avg(Grade) from SC
join Course on Course.Cno = SC.Cno
join Student on Student.Sno= SC.Sno
order by SC.Sno

10. select Stuent.Sno，Stuent.Sname，sum(Grade) A from SC
join Student on Student.Sno = SC.Sno
group by sc.Sno,student.Sname
having A > 200

11. select Student.Sname,Student.Sdept from Student
join Course on Course.Cno = SC.Cno
join SC on SC.Sno = Student.Sno
where SC.Cno = 'C02'

12. select Student.sname,course.cno,sc.grade from sc
join student on student.sno = sc.sno
join course on course.cno = sc.cno
where sc.grade >= 80
order by sc.grade desc

13. select cno,cname from
(
select course.cno,course.cname,sun(sno) from student
join course on course.cno = sc.cno
join sc on sc.sno = student.sno
group by cno,cname
having sun(sno) > 0
)

14. ① select student.sname,student.sdept from
(
select student.sname,student.sdept,course.cname from student
join sc on sc.sno = student.sno
join course on course.cno = sc.cno
where course.cname = 'C01'
)

② select student.sno,student.sname from
(
select student.sno,student.sname,student.sdept,sc.grade from sc
join student on student.sno = sc,sno
where student.sdept = '信息' and sc.grade >= 80
)

③ select top 1 student.sname from
(
select student.sname,student.sdept,sum(sc.grade) from sc
join student on student.sno = sc.sno
where student.sdept = '计算机'
group by student.sname,student.sdept
order by
)

15. delete from sc where grade < 50
16. update sc set grade += 5 from sc
join course on course.cno = sc.cn
join student on student.sno = sc.sno
where student.sno =
(
select student.sno from sc
join course on course.cno = sc.cno
where course.cname = 'c01'
)

17. update sc set grade += 10 from sc
join student on student.sno = sc.sno
where student.sno =
(
select student.sno from student
join sc on sc.sno = student.sno
join course on course.cno = sc.cno
where student.sdept = '计算机' and course.cname = '计算机文化基础'
)

18. create view [A] as
select student.sno,student.sname,student.sdept,course.cno,course.cname,sc.grade from sc
join student on student.sno = sc.sno
join course on course.cno = sc.cno

19. create view [A] as
select student.sno,avg(sc.grade) from sc
join student on student.sno = sc.sno
group by student.sno

20. create view [A] as
select student.sno,sum(sc.grade) from sc
join student on student.sno = sc.sno
group by student.sno

21. create index A on student(sname)
22. 不会

『叁』 用SQL语句实现：学生表、课程表、选课表三张表中的问题：

1.select sname,sno,sdept from student
2.select sno from sc where grade < 60
3.select sname,sdept ,sage from student where sage <20 and sage>23
4.select sno from sc group by con having count(con)>=3
5.select s.sno,s.sname,b.cno,b.cname from student s
left join
(select sc.sno,sc.cno,c.cname from sc left join course c on sc.cno=c.cno) b
on s.sno=b.sno

『肆』 建课程表的sql语句

CREATE course
(
co_id VARCHAR(5) PRIMARY KEY,
co_name VARCHAR(3) NOT NULL,
co_desc VARCHAR(60)
)

SELECT
a.sc_name,
b.co_name,
a.sc_score
FROM score a, course b
WHERE a.sc_courseid = b.co_id
AND substr(a.sc_number,1,2) = '98'
AND a.sc_sex = '0'
AND a.sc_score < 60;

仅供参考

『伍』 用SQL语句实现：学生表、课程表、选课表三张表中的问题

1
(*)>=200

2
selecta.cname课程,b.cname先修课
fromcoursealeftjoincoursebona.cpno=b.cno

3
selecta.sno,a.sname,a.sgender,a.sage,a.sdept,b.cno,isnull(b.grade,0)
fromstudentaleftjoinscbona.sno=b.sno

4
updatescsetgrade=0wheresnoin(='CS')

『陆』 用SQL语句实现：学生表、课程表、选课表三张表中的问题：

1）select 课程表.课程名,count(学号)From 课程表 inner join 选课表On 课程表.课程号=选课表.课程号Group by 学号 2）Create View AllChoosenAs select DISTINCT(课程表.课程名)
From 课程表 inner join 选课表 On 课程表.课程号=选课表.课程号 3）Select 学生表.姓名 From 学生表 Where 学号 Not In (Select 学号 From 选课表) 4）select 学生表.学号,学生表.姓名,课程表.课程名,课程学分 From 学生表 Left Join 选课表 On 学生表.学号=选课表.学号 Inner Join 课程表 On 课程表.课程号=选课表.课程号 5）select 学生表.姓名 From 学生表 where 学号 In (Select 学号 From 选课表 Group by 学号 Having(count(课程号)>2) 6）select 学生表.姓名 From 学生表 where 学号 In (Select 学号 From 选课表 Group by 学号 Having(count(课程号)=select count(*) From 课程表) 7）delete from 选课表 where 学号=(select top 1 学号 From 学生表 where 姓名='张三')

『柒』 sql语句 课程表查询

网上搜行转列，例子很多

『捌』 简单sql语句,student（学生表） course（课程表） choice（选课表）

学生表一张 s
课程表一张 c

select s.name from s
join c
on s.sid=c.sid
where c.name in ('数学',英语',语文','化学')

『玖』 怎么用SQL_UPDATE命令将课程表中课程名为计算机基础的学时修改为4。

update 课程表 set 学时=4 where 课程名='计算机基础'