一 使用CREATE TABLE語(yǔ)句和INSERT INTO語(yǔ)句創(chuàng)建表和插入數(shù)據(jù)。

student表

course表

sc表

二 操作數(shù)據(jù)庫(kù)

1.分別查詢學(xué)生表和學(xué)生修課表中的全部數(shù)據(jù)。

SELECT * FROM student;

SELECT * FROM course;

2.查詢成績(jī)?cè)?0到80分之間的學(xué)生的學(xué)號(hào)、課程號(hào)和成績(jī)。

SELECT sno,cno,grade FROM sc WHERE grade BETWEEN 70 AND 80;

3．查詢100號(hào)課程成績(jī)最高的分?jǐn)?shù)。

SELECT MAX(grade) FROM sc WHERE cno = 100;

4.查詢學(xué)生都選修了哪些課程,要求列出課程號(hào)。

SELECT DISTINCT c.cno,cname FROM course c,sc WHERE c.cno = sc.cno;

SELECT DISTINCT c.cno,cname FROM course c INNER JOIN sc on c.cno = sc.cno;

5.查詢修了200號(hào)課程的所有學(xué)生的平均成績(jī)、最高成績(jī)和最低成績(jī)。

SELECT AVG(grade),MAX(grade),MIN(grade) FROM sc WHERE cno = 200;

6.統(tǒng)計(jì)每個(gè)系的學(xué)生人數(shù)。

SELECT sdept,count(*) FROM student GROUP BY sdept;

7.統(tǒng)計(jì)每門(mén)課程的修課人數(shù)和考試最高分。

SELECT cno,count(*),MAX(grade) FROM sc GROUP BY cno ORDER BY cno;

8.統(tǒng)計(jì)每個(gè)學(xué)生的選課門(mén)數(shù),并按選課門(mén)數(shù)的遞增順序顯示結(jié)果。

SELECT sno,count(*) FROM sc GROUP BY sno ORDER BY count(*) ASC;

9.統(tǒng)計(jì)選修課的學(xué)生總數(shù)和考試的平均成績(jī)。

SELECT COUNT(DISTINCT sno) stu_count,AVG(grade) FROM sc;

10.查詢選課門(mén)數(shù)超過(guò)2門(mén)的學(xué)生的平均成績(jī)和選課門(mén)數(shù)。

SELECT AVG(grade),COUNT(*) FROM sc GROUP BY cno HAVING count(*)>2;

11.列出總成績(jī)超過(guò)200分的學(xué)生,要求列出學(xué)號(hào)、總成績(jī)。

SELECT sno,SUM(grade) FROM sc GROUP BY sno HAVING SUM(grade) > 200;

12.查詢選修了c02號(hào)課程的學(xué)生的姓名和所在系。

SELECT sname,sdept FROM student WHERE sno IN(SELECT sno FROM sc WHERE cno = 200);

13.查詢成績(jī)80分以上的學(xué)生的姓名、課程號(hào)和成績(jī),并按成績(jī)的降序排列結(jié)果。

SELECT sname,cno,grade FROM student,(SELECT * FROM sc WHERE grade > 80) scc

WHERE student.sno = scc.sno ORDER BY scc.grade DESC;

SELECT sname,cno,grade FROM student INNER JOIN(SELECT * FROM sc WHERE grade > 80) scc

ON student.sno = scc.sno ORDER BY scc.grade DESC;

14.查詢計(jì)算機(jī)系男生修了"數(shù)據(jù)庫(kù)基礎(chǔ)"的學(xué)生的姓名、性別、成績(jī)。

SELECT sname,ssex,grade FROM student,(SELECT sno,grade FROM sc WHERE cno IN(SELECT cno FROM course WHERE cname = '高等數(shù)學(xué)')) newsc

WHERE student.sno = newsc.sno AND sdept = '計(jì)算機(jī)系' AND ssex = '男';

SELECT sname,ssex,grade FROM (SELECT sno,sname,ssex FROM student WHERE sdept = '計(jì)算機(jī)系' AND ssex = '男') newstu,

(SELECT sno,grade FROM sc WHERE cno IN(SELECT cno FROM course WHERE cname = '高等數(shù)學(xué)')) newsc

WHERE newstu.sno = newsc.sno;

15.查詢哪些課程沒(méi)有人選,要求列出課程號(hào)和課程名。

SELECT cname,cno FROM course WHERE cno NOT IN(SELECT cno FROM sc GROUP BY cno);

16.查詢有考試成績(jī)的所有學(xué)生的姓名、修課名稱及考試成績(jī)，要求將查詢結(jié)果放在一張新的永久表(假設(shè)新表名為new-sc)中。

CREATE TABLE new_sc AS

SELECT sname,cname,grade FROM course,student,(SELECT * FROM sc WHERE grade is not NULL) nsc WHERE

course.cno = nsc.cno AND student.sno = nsc.sno;

17.分別查詢信息系和計(jì)算機(jī)系的學(xué)生的姓名、性別、修課名稱、修課成績(jī),并要求將這兩個(gè)查詢結(jié)果合并成一個(gè)結(jié)果集,并以系名、姓名、性別、修課名稱、修課成績(jī)的順序顯示各列。

SELECT sdept,sname,ssex,cname,grade FROM course,(SELECT * FROM sc,(SELECT * FROM student WHERE sdept = '計(jì)算機(jī)系') nsc WHERE

sc.sno = nsc.sno) sc_stu WHERE course.cno = sc_stu.cno

UNION

SELECT sdept,sname,ssex,cname,grade FROM course,(SELECT * FROM sc,(SELECT * FROM student WHERE sdept = '情報(bào)系') nsc WHERE

sc.sno = nsc.sno) sc_stu WHERE course.cno = sc_stu.cno;

18.用子查詢實(shí)現(xiàn)如下查詢:

(1) 查詢選修了100號(hào)課程的學(xué)生的姓名和所在系。

SELECT sname,sdept FROM student WHERE sno IN(SELECT sno FROM sc WHERE cno = 100);

(2) 查詢數(shù)學(xué)系成績(jī)80分以上的學(xué)生的學(xué)號(hào)、姓名。

SELECT sno,sname FROM student WHERE sno IN(SELECT sno FROM sc WHERE grade >80) AND sdept = '數(shù)學(xué)系';

(3) 查詢計(jì)算機(jī)系學(xué)生所選的課程名。

SELECT cname FROM course WHERE cno IN(SELECT cno FROM sc WHERE sno IN(SELECT sno FROM student WHERE sdept = '計(jì)算機(jī)系'));

19.將計(jì)算機(jī)系成績(jī)高于80分的學(xué)生的修課情況插入到另一張表中,分兩種情況實(shí)現(xiàn)：

(1) 在插入數(shù)據(jù)過(guò)程中建表。

CREATE TABLE sc_info1 AS

SELECT * FROM sc WHERE sno IN(SELECT sno FROM student WHERE sdept = '計(jì)算機(jī)系') AND grade > 80;

(2) 先建一個(gè)新表,然后再插入數(shù)據(jù)。 創(chuàng)建表SC_Info1 往表SC_info2插入查詢得到的結(jié)果

CREATE TABLE sc_info2(

sno NUMBER,

cno NUMBER,

grade NUMBER

);

INSERT INTO sc_info2(

SELECT * FROM sc WHERE sno IN(SELECT sno FROM student WHERE sdept = '計(jì)算機(jī)系') AND grade > 80

);

--下面這種方法更容易理解一些

INSERT INTO sc_info2(

sno,

cno,

grade

)

SELECT * FROM sc WHERE sno IN(SELECT sno FROM student WHERE sdept = '計(jì)算機(jī)系') AND grade > 80

;

20.刪除修課成績(jī)小于50分的學(xué)生的修課記錄。

DELETE FROM sc WHERE grade < 50;

21.將所有選修了'100'課程的學(xué)生的成績(jī)加10分。

UPDATE sc SET grade = grade+10 WHERE cno = 100;

三 附加題 1.

SELECT s1.storeno,s1.store_name

FROM store s1,

(SELECT storeno,MAX(start_date) AS start_date

FROM store

WHERE start_date <= TO_DATE('2016011','YYYYMMDD')

AND over_date >= TO_DATE('2016011','YYYYMMDD')

GROUP BY storeno) s2

WHERE s1.storeno = s2.storeno

AND s1.start_date = s2.start_date;

總結(jié)

以上是生活随笔為你收集整理的Oracle课表查询系统,Oracle SQL基础练习(一)（学生表-课程表-选课表）的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。