太原理工大學軟體學院 資料庫實驗 實驗二(2021.4.8)

實驗內容

以下內容直接全部復制到console視窗即可

需要逐句運行

-- （1）創建Student表
CREATE TABLE Student
 ( Sno CHAR(8) PRIMARY KEY,
 Sname CHAR(8) ,
 Ssex CHAR(2) NOT NULL,
 Sage INT,
 Sdept CHAR(20)
 );
-- （2）創建Course表
CREATE TABLE Course
 ( Cno CHAR(4) PRIMARY KEY,
 Cname CHAR(40) NOT NULL,
 Cpno CHAR(4) ,
 Ccredit SMALLINT,
 );
-- （3）創建SC表
CREATE TABLE SC(
    Sno CHAR(8) FOREIGN KEY (Sno) REFERENCES Student(Sno),
    Cno CHAR(4),
    Grade SMALLINT,
);

-- （4）創建員工表Employee
CREATE TABLE Employee
(
編號 CHAR(8) PRIMARY KEY,
姓名 VARCHAR(8) not null
部門 CHR（40）,
工資 numeric(8,2),
生日 datetime,
職稱 char(20),
);
-- 指出該陳述句中的錯誤并改正后執行，
-- （5）檢查表是否創建成功
-- SELECT * FROM Student
-- SELECT * FROM Course
-- SELECT * FROM SC
-- SELECT * FROM Employee
-- （6）修改表結構及約束
-- ? 增加班級列
ALTER TABLE Student ADD Sclass char(4)
-- ? 修改年齡列
ALTER TABLE Student ALTER COLUMN Sage smallint
-- ? 增加約束
ALTER TABLE Course ADD UNIQUE(Cname)
-- （7）洗掉表
DROP TABLE Employee


-- （1）為Course表按課程名稱創建索引
CREATE INDEX iCname On Course(Cname)
-- （2）為Student表按學生姓名創建唯一索引
CREATE UNIQUE INDEX iSname ON Student(Sname)
-- （3）為SC表按學號和課程號創建聚集索引
CREATE CLUSTERED INDEX iSnoCno On SC(Sno,Cno desc)

-- （4）為Course表按課程號創建唯一索引
CREATE UNIQUE INDEX iSCno ON Course(Cno)


-- 3．創建視圖
-- 建立資訊系學生的視圖：

CREATE VIEW IS_Student
 AS
 SELECT Sno,Sname,Sage FROM Student
 WHERE Sdept= 'IS';





-- （1）插入到Student表

INSERT INTO Student VALUES('20100001','李勇','男',20,'CS','1001'),('20100002','劉晨','女',19,'CS','1001')

INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept,Sclass) VALUES('20100021','王敏','女',18,'MA','1002'),('20100031','張立','男',19,'IS','1003')

INSERT INTO Student(Sno,Sname,Ssex,sclass) VALUES('20100003','劉洋','女','1001')


-- 檢查下列陳述句中的錯誤，并改正：
-- INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept,sclass) VALUES('20100010',趙斌,'男','19','IS','1005')
-- INSERT INTO Student VALUES('20100022','張明明',19,'男','CS','1002')

INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept,sclass) VALUES('20100010','趙斌','男','19','IS','1005')

INSERT INTO Student VALUES('20100022','張明明','男',19,'CS','1002')


-- （2）插入到Course表

INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('1','資料庫系統原理', '5',4)
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('2','高等數學', null,2)
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('3','管理資訊系統','1',4)

-- 請寫出插入其余行的插入陳述句，并插入資料，
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('6','資料處理',null,'2')
INSERT INTO Course(cno, cname, cpno, ccredit) values ('7','c語言',null,'4')

-- （3）插入到SC表
INSERT INTO SC VALUES('20100001','1',92)
INSERT INTO SC VALUES('20100002','2',80)
INSERT INTO SC(Sno,Cno) VALUES('20100003','1')
INSERT INTO SC(Sno,Cno,Grade) VALUES('20100010','3',null)

-- 請寫出插入其余行的插入陳述句，并插入資料，
INSERT INTO SC VALUES('20100002','2',85)
INSERT INTO SC VALUES('20100002','3',88)
INSERT INTO SC VALUES('20100002','1',90)








-- （4）多行插入到表中
-- 創建存一個表，保存學生的學號、姓名和年齡：

CREATE TABLE cs_Student
 (
學號 char(8),
姓名 char(8),
 年齡 smallint
 );

-- 插入資料行：
INSERT INTO cs_Student
 SELECT Sno,Sname,Sage
 FROM student Where Sdept='CS';

-- （5）檢查插入到表中的資料
SELECT * FROM Student
SELECT * FROM Course
SELECT * FROM SC



-- 2． 修改資料
-- （1）將學生20100001的年齡改為22歲，
UPDATE student SET Sage = 22 WHERE Sno='20100001';

-- （2）將所有學生的年齡增加一歲，
UPDATE Student SET Sage = Sage +1

-- （3）填寫趙斌同學的管理資訊系統課程的成績
UPDATE SC SET Grade = 85
 WHERE Sno='20100010' AND Cno='3'

-- （4）將計算機科學系全體學生的成績加5分
UPDATE sc SET Grade=Grade + 5
 WHERE 'CS'=(select Sdept from student where student.Sno=sc.Sno);

-- （5）請自己完成如下操作
-- ? 將劉晨同學的2號課程成績修改為80
-- ? 將“20100021”同學的學號修改為“20100025”
update SC set Grade=80
    where Sno='20100002' and Cno=2

update Student set Sno='20100025'
    where Sno='20100021'




-- （1）洗掉學號為201000022的學生記錄
DELETE FROM Student WHERE Sno='20100022'

-- （2）洗掉學號20100001學生的1號課程選課記錄
-- 將選課資訊復制到一個臨時表tmpSC中：
SELECT * INTO tmpSC FROM SC

-- 在tmpSC中執行洗掉操作：
DELETE FROM tmpSC WHERE Sno='20100001' and Cno='1'

-- （3）洗掉臨時表中20100002學生的全部選課記錄
delete from tmpSC where Sno='20100002'

-- （4）洗掉計算機科學系所有學生的選課記錄
DELETE FROM tmpSC WHERE 'CS'=(select Sdept from student where student.Sno=tmpSC.Sno );

-- （5）洗掉全部選課記錄
DELETE FROM tmpSC




-- 3.3 資料查詢操作完成如下查詢操作：
-- 1．單表查詢
-- （1）按指定目標列查詢
-- ?查詢學生的詳細記錄：

SELECT * FROM Student;

-- ?查詢學生的學號、姓名和年齡
SELECT Sno,Sname,Sage FROM Student;

-- ?查詢全體學生的姓名、出生年份和所有系，要求用小寫字母表示所有系名，
SELECT Sname,'Year of Birth: ',2004-Sage,LOWER(Sdept) FROM Student;

-- ?查詢全體學生的姓名、出生年份和所有系，要求用小寫字母表示所有系名，
SELECT Sname, 'Year of Birth:' as BIRTH, 2000-Sage BIRTHDAY, DEPARTMENT = LOWER(Sdept)
FROM Student;

-- (4）取消重復行
-- ?查詢選修了課程的學生學號：比較ALL和DISTINCT的區別
SELECT Sno FROM SC;
SELECT DISTINCT Sno FROM SC;

-- （5）簡單條件查詢
-- ?查詢計算機科學系全體學生的名單
SELECT Sname FROM Student WHERE Sdept='CS';

-- （6）按范圍查詢
-- ?查詢年齡在20~23歲之間的學生的姓名、系別和年齡
SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23

-- （7）查詢屬性值屬于指定集合的行
-- ?查詢資訊系（IS）、數學系（MA）和計算機科學系（CS）學生的姓名和性別
SELECT Sname,Ssex FROM Student WHERE Sdept IN ('IS','MA','CS');

-- （8）模糊查詢
-- ?查詢所有姓劉學生的姓名、學號和性別
SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE '劉%'

-- （9）查詢空值
-- ?查詢缺少成績的學生的學號和相應的課程號
SELECT Sno,Cno FROM sc WHERE Grade is null;

-- （10）多重條件查詢
-- ?查詢計算機科學系年齡在歲以下的學生姓名
SELECT Sname FROM student WHERE Sdept='CS' and Sage<20;

-- （11）結果集排序
-- ?查詢全體學生情況，查詢結果按所在系的系號升序排列，同一系中的學生按年齡降序排列
SELECT * FROM Student ORDER BY Sdept,Sage DESC;




-- （12）完成下列查詢
-- ?查詢學生基本資訊，結果集屬性名使用漢字
select Sno as '學號',Sname as '姓名', Ssex as '性別', Sage as '年齡' ,Sdept as '系名', Sclass as '班級' from Student ;

-- ?查詢資訊系且年齡大于23歲同學的學號和姓名
select Sno,Sname from Student where Sage>23 and Sdept='IS';

-- ?查詢年齡是17、18、20、23歲同學的學號、姓名、年齡和所在系
select Sno,Sname,Sno,Sdept from Student where Sage=18 or Sage=17 or Sage=20 or Sage=23;

-- ?查詢年齡不在21~24歲之間的學生的姓名、系別和年齡
select Sname,Sdept,Sage from Student where Sage>24 or Sage<21;




-- 2． 分組統計 （1）聚集函式的使用
-- ?查詢學生總人數
SELECT COUNT(*) FROM Student;

-- ?查詢選修了課程的學生人數
SELECT COUNT(DISTINCT Sno) FROM SC

-- ?查詢最高分
SELECT MAX(Grade) FROM SC

-- （2）聚集函式作用于部分行
-- ?統計2號課程的總分、均分和最高分
SELECT SUM(grade) 總分,AVG(grade) 均分,MAX(grade) 最高分
FROM sc WHERE Cno='2'

-- （3）分組統計
-- ?統計各門課程的選課人數、均分和最高分
select cno 課程號,count(*) 人數,AVG(grade) 均分,MAX(grade) 最高分
from sc group by Cno

-- ?統計均分大于90的課程
select cno 課程號,count(*) 人數,AVG(grade) 均分,MAX(grade) 最高分
from sc group by Cno
having AVG(grade) > 90;



-- （4）完成下面的查詢
-- ?統計每個同學的學號、選課數、平均成績和最高成績
select Sno 學號,count(cno) 選課數,AVG(Grade) 平均成績,max(Grade) 最高成績 from SC group by Sno

-- ?統計每個班的每門課的選課人數、平均成績和最高成績
select Sclass 班級, Cno 課程號, count(cno) 選課數,AVG(Grade) 平均成績,max(Grade) 最高成績 from SC,Student where Student.Sno=SC.Sno group by Cno,Student.Sclass

-- 3.連接查詢（1）在WHERE中指定連接條件
-- ?查詢每個參加選課的學生資訊及其選修課程的情況
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno = SC.Sno

-- ?查詢每一門課的間接先修課
SELECT * FROM course first,course second
WHERE first.Cpno=second.Cno;
SELECT first.Cno,second.Cpno FROM course first,course second
WHERE first.Cpno=second.Cno;

-- （2）在FROM中指定連接條件
-- ?查詢每個參加選課的學生資訊及其選修課程的情況
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student JOIN SC ON (Student.Sno=SC.Sno)

-- （3）使用外連接查詢
-- ?查詢每個學生資訊及其選修課程的情況
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno)

-- （4）復合條件連接查詢
-- ? 查詢選修號課程且成績在90分以上的所有學生
SELECT Student.Sno, Sname
FROM Student join SC ON (Student.Sno = SC.Sno) /* 連接條件*/
WHERE SC.Cno= '2' AND SC.Grade > 90; /* 過濾條件*/

-- （5）多表查詢
-- ? 查詢每個學生的學號、姓名、選修的課程名及成績
SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;

-- （6）完成下列查詢
-- ? 查詢選修了2號課程的同學的學號和姓名

select Student.Sno,Sname from Student,SC where Student.Sno=Sc.Sno and Cno='2'

-- ?查詢各門課程的課程號、課程名稱以及選課學生的學號
select sc.Cno,Cname,Sno from Course,SC where Course.Cno=SC.Cno

-- 查詢選修了資料庫系統原理課程的同學的學號和姓名和成績
select SC.Sno ,Sname,Grade from Course,Student,SC where Sc.Cno=Course.Cno and SC.Sno=Student.Sno and Cname='資料庫系統原理'





-- 4.嵌套查詢（1）由In引出的子查詢
-- ?查詢與“劉晨”在同一個系學習的學生
SELECT Sno,Sname,Sdept FROM Student
WHERE Sdept IN ( SELECT Sdept FROM Student WHERE Sname= '劉晨'); （2）由比較運算子引出的子查詢

-- ?找出每個學生超過他選修課程平均成績的課程號，
SELECT Sno, Cno FROM SC x
WHERE Grade >= ( SELECT AVG(Grade) FROM SC y
WHERE y.Sno=x.Sno);

-- （3）帶修飾符的比較運算子引出的子查詢
-- ?查詢其他系中比計算機科學系所有學生年齡都小的學生姓名及年齡，
SELECT Sname,Sage FROM Student
WHERE Sage < ALL ( SELECT Sage FROM Student WHERE Sdept= 'CS')
AND Sdept <> 'CS' ;

-- （4）由EXISTS引出的子查詢
-- ?查詢所有選修了1號課程的學生姓名
SELECT Sname FROM Student
WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= '1')

-- 5.集合查詢（1）集合并
-- ?查詢計算機科學系的學生及年齡不大于19歲的學生
SELECT * FROM Student WHERE Sdept= 'CS'
UNION
SELECT * FROM Student WHERE Sage<=19

-- （2）集合交
-- ? 查詢計算機科學系且年齡不大于19歲的學生
SELECT * FROM Student WHERE Sdept='CS'
INTERSECT
SELECT * FROM Student WHERE Sage<=19

-- （3）集合差
-- ? 查詢計算機科學系且年齡大于19歲的學生
SELECT * FROM Student WHERE Sdept='CS'
EXCEPT
SELECT * FROM Student WHERE Sage <=19;






-- 3.4 視圖操作
-- 建立視圖并基于視圖進行查詢：
-- 1. 創建視圖
-- （1）建立學生基本資訊視圖
CREATE VIEW Student_VIEW(學號,姓名,性別,年齡,系,班級)
AS
SELECT Sno,Sname,Ssex,Sage,Sdept,Sclass
FROM Student;

-- （2）建立學生均分視圖
CREATE VIEW S_G(Sno,Gavg)
AS
SELECT Sno,avg(Grade)
FROM SC GROUP BY Sno;

-- （3）建立選課資訊視圖
CREATE VIEW XK_VIEW
AS
SELECT Student.*,Course.*,Grade
FROM Student,SC,Course
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;

-- 2. 視圖查詢
-- （1）查詢學生基本資訊
SELECT * FROM Student_VIEW

-- （2）找出每個學生超過他選修課程平均成績的課程號
SELECT SC.Sno,Cno,grade
FROM SC, S_G
WHERE SC.Sno = S_G.Sno and Grade >= S_G.Gavg

-- （3）查詢每個學生的學號、姓名、選修的課程名及成績
SELECT Sno,Sname,Cname,Grade
FROM XK_VIEW

-- （4）比較使用視圖查詢和直接從基表查詢的優點