分數的課程表

『壹』 將學生表、成績表、課程表三張表中的數據作為基礎數據，寫出相關SQL語句，謝謝

--查詢所有學生的成績信息（無成績的學生也需顯示）
SELECTSname,Cname,Grade
.Sno=SC.Sno
LEFTJOINCourseONCourse.Cno=SC.Cno
--查詢8002課程的平均分、最高分以及課程名稱，且平均分保留2位小數
SELECTCAST(AVG(Grade)ASNUMERIC(5,2))平均分,MAX(Grade)最高分,Cname課程名稱
FROMCoruseJOINSCONCourse.Cno=SC.Cno
WHERECourse.Cno='8002'

『貳』 資料庫多表查詢,學生表,課程表,成績表

直接將三表關聯查詢就可以了
select b.姓名,c.課程,a.成績
from 成績表 a,學生表 b,課程表 c
where a.學生學號 = b.學號 and a.課程編號 = c.編號 and a.成績 < 60

『叄』 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. 不會

『肆』 學生表(學號，姓名)、課程表(課程編號，課程名)、成績表(自動編號、學號，課程編號，成績) 查詢各個學生學

select A.姓名,
sum(case B.課程名 when '語文' then C.成績 else 0 end) as 語文,
sum(case B.課程名 when '數學' then C.成績 else 0 end) as 數學,
sum(case B.課程名 when '英語' then C.成績 else 0 end) as 英語,
sum(case B.課程名 when '歷史' then C.成績 else 0 end) as 歷史,
from 學生表 A left join 成績表 C on 學生表.學號=成績表.學號
left join 課程表 B on 成績表.課程編號=課程表.課程編號
group by A.姓名
剛剛不是在那邊回答你了么~~~

『伍』 學生表、成績表、課程表作為數據基礎表，寫出如下SQL語句，謝謝

查詢所有學生的成績信息（無成績的學生也需顯示)
SELECT ST.SNO, ST.SNAME, AVG(GRD.GRADE) AS AVG_GRADE FROM STUDENT ST LEFT JOIN GRADE GRD
ON (ST.SNO = GRD.SNO) GROUP BY ST.SNO, ST.NAME

查詢8002課程的平均分、最高分以及課程名稱，且平均分保留2位小數
SELECT C.CNAME, ROUND(AVG(GRD.GRADE),2) AS AVERAGE_GRADE, ROUND(MAX(GRD.GRADE),2) AS MAX_GRADE FROM COURSE C INNER JOIN GRADE GRD ON (C.CNO = GRD.CNO)
WHERE C.CNO = 8002

『陸』 如何查詢成績以及查看課表

查看雲南省昭通市鎮雄縣塘房鎮塘中吉茂騫初二考試成績

『柒』 學生表、成績表、課程表中隨機插入20條數據

你的問題沒描述清楚，你說的隨機是不是以下問題:
第一，你說的學生表，成績表，課程表是不是excel數據表。如果是就是從已做好的表中隨機選擇20條數據手動輸入相應的軟體中就可以了。
第二，如果是開發程序，那麼就是從這三個表中隨機讀取20條數據插入到資料庫中。
所謂隨機插入數據就是不指定要插入哪條，而是隨機來選擇插入的數據。比如，從編號1至20號的20個球中選出1-5編號的5個球這是指定選擇法;從編號1-20號的20個球中隨機選擇5個球，就是從這20個球中用手隨機抓取5個出來，這就是隨機法。

『捌』 大家幫助哈，有兩個表：一個是成績表（學號、課程號、成績），一個是課程表（課程號、課程名、學時、學分)

SELECT*FROM

(selectc.學號,sum(c.學分)sumcfrom

(selects.學號,t.學分from成績表s,課程表t

wheret.課程名=s.課程名)c

groupbyc.學號)a,

(selectt.學號,count(*)CCfrom成績表tGROUPBYT.學號)bWHEREA.學號=B.學號

『玖』 SQL 學生表 課程表 分數表 查詢問題

select t1.學生id from (select * from 分數 as fs where fs.課程id=10001) as t1
inner join (select * from 分數 as fs where fs.課程id=10002) as t2 on t1.學生id=t2.學生id where t1.分數>t2.分數
有問題可以追問或Hi我。

『拾』 有沒有可以查看成績,查看課程表的軟體可以

掌上課表可以，手機APP