id做課程表

① 三個表 student Sid（學生ID） Sname（姓名） Sage（年齡） Ssex（性別）

select student.Sid,student.Sname
from student,課程自表,成績表
where student.Sid=成績表.SCSid and 成績表.Cid=課程表.CourseCid and 課程表.CourseCid="001" or 課程表.CourseCid="002"
group by student.Sid,student.Sname
having count(*)=2;

② 3張表，課程表(課程id,課程名，分類) 學生表(學生id,學生姓名) 成績表(學生id,課程id

select a.課程名,b.學生姓名,max(c.分數) from 課程表 a ,學生表 b ,成績表 c where a.課程id=c.課程id and b.學生id=c.學生id

③ 三張表，學生表有id.name課程表有課程id課程name選課表有學生id課程id求顯示選了兩門哥的學生信息

(C，10）年齡（N，2），性別（C，2） 課程表：課程編號（C，回3），答課程名稱（C，10），課時（N，4），任課教師（C，10） 選課表：學號（C，6），課程編號（C，3），成績（N，2） 是寫出完成以下操作的SQL命令：1.查詢所有學生的信息 2.查詢所有課程的課程名稱和任課教師 3.查詢出姓「李」的學生的基本信息 4.查詢選課表中分數介於80分至90分之間的記錄 5。求學生總數 6.查詢年齡比唐潔小的所有學生的基本信息 8.在學生表中插入一條記錄：學號：9000101，姓名；李麗，年齡：20 9。查詢出200807號學生的姓名及其所選課程的名稱 10.在學生表中增加一個「電話號碼」欄位（文本型（8字元））

④ 如何設置外鍵，有三個表，學生表（ID，學號，課程號，成績），課程表（ID，課程號，課程名）

學生表中的學號是否唯一且必須？如果是的話，建議將ID及學號合並
課程表中的課程號是否唯一且必須？如果是的話，建議將ID及課程號合並
學生表中課程號是課程表中ID的外鍵，成績表中的課程號是課程表中ID的外鍵，成績表中的學號是學生表中ID的外鍵

⑤ 我寫了一個學生表（學生id，學生名字），寫了一個課程表（課程id，課程名字，學生id）

外鍵關聯的是另一張表的主鍵，將本表的這個欄位的值約束在關聯的主鍵范圍內。

你這個需求可以寫觸發器實現的

⑥ 學生表、課程表,學生課程關系表中怎麼修改學生id信息

問題1:原學生表中沒有學號是1的??,如果沒有可以改
update student set Stuid=1 where Stuid=(select stuid from card where score=100 and cid=(select cid from course where cmane='物理')) and sname='張三'

這個表改後card 表裡相專應的張三的數據就與student表裡不相符屬了.

delete from student where Stuid in (select stuid from card where score<60 and cid=(select cid from course where cname='英語'))
同樣的刪除數據後,找不到對應的student表裡的

這種結構的表之間應該還有外鍵關聯

像你這么改要亂套的

⑦ 資料庫查詢，有三張表學生表STU，課程表Course,SC學生課程關系表（s_id,c_id）

SELECTSTU.s_nameFROMSTU,(SELECTSC.s_idFROMSCGROUPBYs_id
HAVINGCOUNT(*)=(SELECTCOUNT(*)FROMCourse)AStmpWHERESTU.s_id=tmp.s_id

⑧ 設在學生資料庫中有三張表，表結構如下所示： Student(學生表): Student表的主鍵：sno(學號) Course(課程

(1) create table S_C
(sno char(10) not null
,cno char(8) not null
,score int null
,constraint PK_SC primary key (sno,cno)
)
(2)insert into Student (sno,sname,ssex)
values('1010','李小麗','女')
(3)create index IND_CName on Course (cname)
(4)update student set sage=23 where sno='1005'
(5)delete from course where cname='管理信息系統'
(6)select cno,cname,ctime
from course
where teacher='李元'
order by cno ASC
(7)select sno,sum(score) as score
from S_C
group by sno
(8)create view V_Student
as
select *
from student
where ssex='男' and sage>=18 and sage<=24
(9)select sno,sname
from student
where sno in (select sno from S_C where cno='001')
(10)select A.sno,A.sname
from student A
left join S_C B on A.sno=B.sno
left join Course C on B.cno=C.cno
where C.cname='關系資料庫'
(11)select sno,sname from
(select A.sno,A.sname,count(1) as count_
from student A
left join S_C B on A.sno=B.sno
group by A.sno,A.sname) A
where count_>3
(12)select C.sname
from
(select * from S_C where cno='002') A
inner join
(select * from S_C where cno='004' on) B on A.sno=B.sno
left join student C on A.sno=C.sno

⑨ 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. 不會

⑩ 現有2個表： 學生表：Student表（StudentId，StudentName） 課程表(課程ID，課程名稱)

select UserID,student.Name UserName,課程表名.Name Cousename
from student,課程表名