学生表和课程表是一对多的关系类型

A. 数据库中包含学生表、课程表、学生选课表3个表,分别是什么

1、SELECT 课程号, 课程名, 课程学分

FROM 课程 WHERE 课程号 IN(SELECT 课程号

FROM 选课

GROUP BY 课程号

HAVING COUNT(学生号) BETWEEN 2 AND 4 )

2、SELECT MAX(成绩) - MIN(成绩) AS 分数之差

FROM 选课

倘若要每门课程相差

SELECT 课程, MAX(成绩) - MIN(成绩) AS 分数之差

FROM 选课

GROUP BY 课程

B. 用SQL对学生表、课程表、成绩表进行多表查询。

CREATE TABLE 选课表

(学号 CHAR(8) REFERENCES 学生表内(学号),
课程编号 CHAR(6) REFERENCES 课程表(课程编号),
成绩 smallint)
GO
INSERT INTO 学生表 VALUES('20100101','李丹','女','1993-6-6','管理系容')

GO
SELECT 学号,姓名,所在系

FROM 学生表
GO
SELECT 学生表.学号,姓名
FROM 学生表 JOIN 选课表 ON 学生表.学号=选课表.学号
WHERE 课程编号='A01-02'
GO
SELECT *
FROM 学生表
WHERE 姓名 LIKE '[赵李张]%'
GO
SELECT 课程名称,COUNT(*) AS 选修总人数

INTO 修课统计
FROM 课程表 JOIN 选课表 ON 课程表.课程编号=选课表.课程编号
GROUP BY 课程名称

C. 学生和课程是多对多，有学生表，课程表，关系表。问写出SQL找出选课大...

select * from 学生表 where studentid in (select studentid from 关系表 group by studentid having count(*)>5)
子查询：select studentid from 关系表 group by studentid having count(*)>5 找出符合的ID

D. 1．在学生数据库中有三张数据表，即学生表、课程表和选课表，三张表的表数据如下，写出创建学生表、课程

create table student(S_no char(9), S_name char(20), S_sex char(2), S_age int, S_department char(20))
create table course (C_no char(10), C_name char(20), C_teacher char(20) )
create table choice(S_no char(9), C_no char(10), S_score int)
insert into student(S_no, S_name, S_sex, S_age, S_department)
values('001', '李志强', '男', 20, '计算机系' )
insert into student(S_no, S_name, S_sex, S_age, S_department)
values('002', '张 亮', '男', 21 '建筑工程系' )
insert into student(S_no, S_name, S_sex, S_age, S_department)
values('003', '李 平', '女', 19 '计算机系' )
insert into course(C_no, C_name, C_teacher )
values('C001', 'C语言', '王雅新' )
insert into course(C_no, C_name, C_teacher )
values('C002', '数据结构', '和海莲' )
insert into course(C_no, C_name, C_teacher )
values('C003', 'SQL Server2000', '陈 红' )
insert into choice(S_no, C_no, S_score)
values('001', 'C001', 83)
insert into choice(S_no, C_no, S_score)
values('001', 'C002', 80 )
insert into choice(S_no, C_no, S_score)
values('002', 'C001', 75)
insert into choice(S_no, C_no, S_score)
values('002', 'C004', 85)
insert into choice(S_no, C_no, S_score)
values('003', 'C002', 88)
insert into choice(S_no, C_no, S_score)
values('003', 'C003', 86)

E. 学生—课程"数据库中包含学生表,课程表,学生选课表3个表,分别是:

您好，您这样：
--1
select Sname,Sage from Student where Sage<(select Sage from Student where Sno='某一学生') and Sdept='数学系'
--2
select Sname from Student where Sno in (select Sno from SC)
--3
select Sname from Student where Sno in (select Sno from SC group by Sno having count(*)=(select count(*) from Course ))

F. 要设计一个学生课程管理数据库,包含3个关系分别是:学生表Student、课程表Cors

1.SELECT COUNT(DISTINCT(学号）） FROM SC 2.SELECT 姓名 FROM S,SC WHERE S.学号=SC.学号 AND S.学号=1号 3.SELECT S.学号，姓名，课程名，成绩 FROM S,SC,C WHERE S.学号=SC.学号 AND SC.课程号=C.课程 AND 课程号=1号 4.SELECT S.学号，姓名 FROM S,SC WHERE S.学号=SC.学号 AND SC.课程=‘人工智能’ 5.SELECT MAX(成绩） FROM S,SC WHERE S.学号=SC.学号 AND S.学号=1号 6.SELECT 学号，姓名 FROM S WHERE SUBSTRING(姓名，3,2）=’月’ 7.SELECT 课程号，COUNT(*） FROM C,SC WHERE C.课程号=SC.课程号 8.SELECT S.学号，姓名 FROM S,SC,C WHERE S.学号=SC.学号 AND SC.课程号=C.课程号 AND C.成绩>80 9.SELECT DISTINCT（省区） FROM S WHERE S.系别=‘物理’ 10.SELECT * FROM S ORDER BY 系别 ASC，年龄 DSC 11.SELECT AVG(成绩 ） FROM C WHERE 课程号=2号 12.SELECT 学号，姓名 FROM S WHERE SUBSTRING(姓名，3,2）=’阳’

G. 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. 不会

H. 教学管理”数据库中有学生表、课程表和选课表，如何创建数据库反应三张表之间的联系

关于反映这三张表中数据之间的联系，提供三方面信息以供参考：版
1、表关系分析：学生权表与课程表是多对多的关系，建立一张关联表也就是选课表来将学生表与程表产生关联。
2、数据库表分析：学生表包括学生相关信息，课程表包括课程相关信息，选课表放其他两张表的各一个字段(可以维护唯一性，比如说主键)。
3、实体分析：包含学生和课程两个实体，在每个实体中加入另外一个实体的集合。

I. 对于给定的数据表（教师表，学生表，课程表，成绩表），如何设置他们

1. 在数据库工具选项来卡的源关系窗口中创建关系。

2. 在设计选项卡中出现显示表窗口，双击教师表，学生表，课程表和成绩表，将它们添加到关系窗口中；

3. 选择任意两个字段，出现编辑关系窗口，即可设置他们之间的关系。

• 关系类型有一对一，一对多和多对多。

• 学生表和成绩表是一对多

• 教师表和学生表是多对多

• 课程表和学生表是一对多

以上答案仅供参考

J. 学生表、课程表,学生课程关系表问题

create trigger stu_trig
on card
for insert
as
declare @e int
begin
if exists(select @e = s.stuidfrom card as c,student as s,course as u where u.cname = '物理' and c.score = 100 and s.sname = '张三')
update student set stuid = 1 where stuid = @e
end
写了1个,不知道正确与否.另1个须加分!