DH-SQL(學生資訊表-課程表-選課表)

問題：

建立學校資料庫，裡面有三張表

學生資訊表（學生學號，學生姓名，年齡，性別，院系）

Student（sno,sname,sage,ssex,sdept）

課程表（課程號，課程名，先行課，學分）

Course(cno,cname,cpon,credit)

選課表（學生學號，課程號，成績）

SC(sno,cno,grade)

現在進行以下操作

--生成包含計算機系全體學生的花名冊
--查詢所有年齡在25歲以下的學生姓名、年齡

--查詢考試成績有不及格的學生的學號、姓名

--查詢年齡在20歲至30歲（含）之間的學生的姓名、系別、年齡

--查詢數學系（MA）、計算機系（CS）、資訊（IS）學生的姓名、系別
--查詢未參加選修課考試的學生的學號、姓名、課號及課程名

--查詢平均成績大於70的學生的學號和平均成績（分組）
--‘C01’課程中，成績前五名的學生的學號、姓名、成績（First / Top n）

建庫建表及插入資料程式碼：

if exists (select *from sysdatabases where name='school')
drop database school
create database school
on primary
(
  name='schoolmanageDB',
  filename='D:\project\school.mdf',
  size=10mb,
  maxsize=100mb,
  filegrowth=2mb
)



use school--在這個資料庫下
--------------------------建立課程表--------------------------------------
if exists (select *from sysobjects where name='Course')--課程表
drop table Course
create table Course
(
  cno char(10)not null primary key,--定義主鍵
  cname varchar(20)not null,
  cpon char(10),
  credit smallint,
  foreign key (cpon) references Course(cno)--定義外來鍵
);
--------------------------建立課程表約束-------------------------------------------

alter table Course --增加課程名必須取唯一值的約束條件、
add constraint UK_cname unique(cname);
-------------------插入課程表資訊-----------------

select * from Course





--有錯,無法增添資料(注意這個地方Course定義的外來鍵是本身的一個屬性)
--要一行一行的插入
insert into Course(cno,cname,cpon,credit)--所以上來插入外來鍵的時候，第一個外來鍵一定是null
values ('1','資料庫',null,3)
insert into Course(cno,cname,cpon,credit)
values('2','數學',null,2)
insert into Course(cno,cname,cpon,credit)
values('3','資訊系統','1',4)
insert into Course(cno,cname,cpon,credit)
values('4','作業系統','3',3)
insert into Course(cno,cname,cpon,credit)
values('5','資料結構','2',4)
insert into Course(cno,cname,cpon,credit)
values('6','資料處理',null,2)
insert into Course(cno,cname,cpon,credit)
values('7','語言','6',4)












--------------------------建立學生表------------------------------------------
if exists (select *from sysobjects where name='Student')--學生表
drop table Student
create table Student(
  sno char(10)not null,
  sname varchar(20)not null,
  sage smallint,
  ssex char(1),
  sdept char(2),
  primary key(sno),
  check(ssex in ('M','F'))
)



----------------------插入學生資訊-----------------------
insert into Student(sno,sname,sage,ssex,sdept)
values('0000000001','魯班',18,'M','CS')
insert into Student
values('0000000002','狄仁傑',30,'M','IS')

insert into Student(sno,sname,sage,ssex,sdept)
values('201215121','李勇',20,'M','CS')
insert into Student
values('201215122','劉晨',19,'F','CS')
insert into Student
values('201215123','王敏',18,'F','MA')
insert into Student
values('201215125','張立',19,'M','IS')





--批量插入(select後面沒有括號)


select *from Student
insert into Student(sno,sname,sage,ssex,sdept)--批量插入
select '0000000003','小喬',19,'F','MA'union
select '0000000004','大喬',36,'F','MA'union
select '0000000005','亞瑟',55,'M','CS'

------------------------建立選課表---------------------------------
if exists (select *from sysobjects where name='SC')--選課表
drop table SC
create table SC
(
sno char(10)not null,
cno char(10)not null,
grade smallint ,
primary key(sno,cno),
foreign key(sno)references Student(sno),
foreign key(cno)references Course(cno),
check(grade>=0 and grade<=100)
)

------------------------向選課表中插入資訊--------------------------



-------------切記一條一條插入---------
insert into SC(sno,cno,grade)
values('201215121','1',92)
insert into SC(sno,cno,grade)
values('201215121','2',85)
insert into SC(sno,cno,grade)
values('201215121','3',88)
insert into SC(sno,cno,grade)
values('201215122','2',90)
insert into SC(sno,cno,grade)
values('201215122','3',90)

--插入考試不及格的同學
insert into SC(sno,cno,grade)
select '0000000001','1',56 union
select '0000000004','2',44 union
select '0000000003','2',45  

--插入沒有考試成績的同學
insert into SC(sno,cno,grade)
select '0000000001','2',null union
select '0000000002','2',null

操作程式碼：

select *from Student

執行結果：

操作程式碼：

select *from Course

執行結果：

操作程式碼：

select *from SC

執行結果：

操作程式碼：

--生成包含計算機系全體學生的花名冊
select *from  student 
where sdept='CS'

執行結果：

操作程式碼：

--查詢所有年齡在25歲以下的學生姓名、年齡
select sname, sage from  student 
where sage<25

--查詢考試成績有不及格的學生的學號、姓名
select student.sno,sname from Student
where sno in(select sno from SC where grade<60)

執行結果：

操作程式碼：

--查詢年齡在20歲至30歲（含）之間的學生的姓名、系別、年齡

select sname,sdept,sage from Student
where sage between 20 and 30

--查詢數學系（MA）、計算機系（CS）、資訊（IS）學生的姓名、系別
select sname,sdept from Student
where sdept in('MA','CS','IS')

執行結果：

操作程式碼：

--查詢未參加選修課考試的學生的學號、姓名、課號及課程名

select student.sno,sname,SC.cno,cname from SC join Student
on SC.sno=Student.sno 
join Course on SC.cno=Course.cno where( grade is null)

--查詢平均成績大於70的學生的學號和平均成績（分組）
select  sno,avg(grade)平均成績 from SC--條件
group by sno --以學號分組
having avg(grade)>70--用having語句

執行結果：

操作程式碼：

--‘C01’課程中，成績前五名的學生的學號、姓名、成績（First / Top n）
select  Student.sno,sname,grade
from Student join SC
on Student.sno=SC.sno
where SC.sno in
(select top 5 SC.sno from SC 
order by grade desc
)order by grade desc
--再次執行一遍，使成績從大到小顯示

執行結果：