一、基本表的定義與刪除
T1.
用SQL語句創(chuàng)建如下三張表：學(xué)生（Student）,課程表（Course）,和學(xué)生選課表（SC）,這三張表的結(jié)構(gòu)如表1-1到表1-3所示温自。
表1-1 Student表結(jié)構(gòu)
| 列名 | 說明 | 數(shù)據(jù)類型 | 約束 |
|---|---|---|---|
| Sno | 學(xué)號(hào) | 字符串 | 長度為7拒秘，主碼 |
| Sname | 姓名 | 字符串 | 長度為10渺杉，非空 |
| Ssex | 性別 | 字符串 | 長度為2，取‘男’或‘女’ |
| Sage | 年齡 | 整數(shù) | 取值15~45 |
| Sdept | 所在系 | 字符串 | 長度為20 默認(rèn)為‘計(jì)算機(jī)系’ |
create table Student(
Sno varchar(7) primary key,
Sname varchar(10) not null,
Ssex varchar(2) check(Ssex='男' or Ssex='女'),
Sage int check(Sage>=15 and Sage <=45),
Sdept varchar(20) default('計(jì)算機(jī)系'));
表1-2Course表結(jié)構(gòu)
| 列名 | 說明 | 數(shù)據(jù)類型 | 約束 |
|---|---|---|---|
| Cno | 課程號(hào) | 字符串 | 長度為10砚偶，主碼 |
| Cname | 課程名 | 字符串 | 長度為20批销，非空 |
| Ccredit | 學(xué)分 | 整數(shù) | 取值大于0 |
| Semster | 學(xué)期 | 整數(shù) | 取值大于0 |
| Cperiod | 學(xué)時(shí) | 整數(shù) | 取值大于0 |
create table Course(
Cno varchar(10) primary key,
Cname varchar(20) not null,
Ccredit int check(Ccredit>0),
Semster int check(Semster >0),
Cperiodint check(Cperiod>0)
);
表1-3 SC表結(jié)構(gòu)
表1-2Course表結(jié)構(gòu)
| 列名 | 說明 | 數(shù)據(jù)類型 | 約束 |
|---|---|---|---|
| Sno | 學(xué)號(hào) | 字符串 | 長度為7，主碼染坯，參照Student的外碼 |
| Cno | 課程名 | 字符串 | 長度為10均芽，主碼，參照Course |
| Grade | 成績 | 整數(shù) | 取值0~100 |
create table SC(
Sno varchar(7),
Cno varchar(10),
Grade int check(Grade >=0 and Grade <=100),
primary key(Sno,Cno),
foreign key (Sno) references Student(Sno),
foreign key (Cno) references Course(Cno));
PS:
外碼(鍵): 一個(gè)關(guān)系模式(r1)可能在它的屬性中包含另一個(gè)關(guān)系模式(r2)的主碼,這個(gè)屬性在r1上稱作參照r2的外碼单鹿。關(guān)系r1稱為外碼依賴的參照關(guān)系掀宋，關(guān)系r2稱為外碼的被參照關(guān)系。FK一定來自另一個(gè)表的PK仲锄，F(xiàn)K是PK的子集劲妙。
如果外鍵或者主鍵要求命名，請(qǐng)使用以下語法
constraint pk_Student_Sno primary key (Sno)
constraint fk_Student_Sno foreign key (Sno) references Student(Sno)//SC表儒喊，參照表后邊的屬性可以省略
已有表镣奋，添加外鍵
alter table SC
add constraint fk_Student_Sno
foreign key (Sno)
references Student(Sno);
二、修改表結(jié)構(gòu)
T2.
為SC表添加“選課類別”列怀愧，此列的定義為XKLB char(4)
alter table SC add XKLB char(4);
T3.
將新添加的XKLB的類型修改為char(6)
alter table SC alter column XKLB char(6);
T4.
刪除Course表的Cperiod列
alter table Course drop column Cperiod;
T5.
重命名Student表的Ssex列為sex
EXEC sp_rename 'Student.Ssex','sex';
三侨颈、數(shù)據(jù)查詢功能
表3-1 Student表數(shù)據(jù)
| Sno | Sname | Ssex | Sage | Sdept |
|---|---|---|---|---|
| 9512101 | 李勇 | 男 | 19 | 計(jì)算機(jī)系 |
| 9512102 | 劉晨 | 男 | 20 | 計(jì)算機(jī)系 |
| 9512103 | 王敏 | 女 | 20 | 計(jì)算機(jī)系 |
| 9521101 | 張立 | 男 | 22 | 信息系 |
| 9521102 | 吳賓 | 女 | 21 | 信息系 |
| 9521103 | 張海 | 男 | 20 | 信息系 |
| 9531101 | 錢小平 | 女 | 18 | 數(shù)學(xué)系 |
| 9531102 | 王大力 | 男 | 19 | 數(shù)學(xué)系 |
insert into Student
values
('9512101','李勇','男',19,'計(jì)算機(jī)系'),
('9512102','劉晨','男',20,'計(jì)算機(jī)系'),
('9512103','王敏','女',20,'計(jì)算機(jī)系'),
('9521101','張立','男',22,'信息系'),
('9521102','吳賓','女',21,'信息系'),
('9521103','張海','男',20,'信息系'),
('9531101','錢小平','女',18,'數(shù)學(xué)系'),
('9531102','王大力','男',19,'數(shù)學(xué)系');
表3-2 Course表數(shù)據(jù)
| Cno | Cname | Ccredit | Semster |
|---|---|---|---|
| C01 | 計(jì)算機(jī)文化學(xué) | 3 | 1 |
| C02 | VB | 2 | 3 |
| C03 | 計(jì)算機(jī)網(wǎng)絡(luò) | 4 | 7 |
| C04 | 數(shù)據(jù)庫基礎(chǔ) | 6 | 6 |
| C05 | 高等數(shù)學(xué) | 8 | 2 |
| C06 | 數(shù)據(jù)結(jié)構(gòu) | 5 | 4 |
insert into Course(Cno,Cname,Ccredit,Semster)
values
('C01','計(jì)算機(jī)文化學(xué)',3,1),
('C02','VB',2,3),
('C03','計(jì)算機(jī)網(wǎng)絡(luò)',4,7),
('C04','數(shù)據(jù)庫基礎(chǔ)',6,6),
('C05','高等數(shù)學(xué)',8,2),
('C06','數(shù)據(jù)結(jié)構(gòu)',5,4);
表 3-3 SC表數(shù)據(jù)
| Sno | Cno | Grade | XKLB |
|---|---|---|---|
| 9512101 | c01 | 90 | 必修 |
| 9512101 | c02 | 86 | 選修 |
| 9512101 | c06 | <NULL> | 必修 |
| 9512102 | c02 | 78 | 選修 |
| 9512102 | c04 | 66 | 必修 |
| 9521102 | c01 | 82 | 選修 |
| 9521102 | c02 | 75 | 選修 |
| 9521102 | c04 | 92 | 必修 |
| 9521102 | c05 | 50 | 必修 |
| 9521103 | c02 | 68 | 選修 |
| 9521103 | c06 | <NULL> | 必修 |
| 9531101 | c01 | 80 | 選修 |
| 9531101 | c05 | 95 | 必修 |
| 9531102 | c05 | 85 | 必修 |
insert into SC
values
('9512101','c01',90,'必修'),
('9512101','c02',86,'選修'),
('9512101','c06',NULL,'必修'),
('9512102','c02',78,'選修'),
('9512102','c04',66,'必修'),
('9521102','c01',82,'選修'),
('9521102','c02',75,'選修'),
('9521102','c04',92,'必修'),
('9521102','c05',50,'必修'),
('9521103','c02',68,'選修'),
('9521103','c06',NULL,'必修'),
('9531101','c01',80,'選修'),
('9531101','c05',95,'必修'),
('9531102','c05',85,'必修');
T6.
查詢?nèi)w學(xué)生的學(xué)號(hào)與姓名
select Sno,Sname
from Student;
T7.
查詢?nèi)w學(xué)生的姓名，學(xué)號(hào)和所在系
select Sno,Sname,Sdept
from Student;
T8.
查詢?nèi)w學(xué)生的所有信息（三張表以上的關(guān)聯(lián)查詢）
select *
from Student left join SC on Student.Sno=SC.Sno left join Course on SC.Cno=Course.Cno
T9.
查詢?nèi)w學(xué)生的姓名及其出生年份
select Sname,2018-Sage as '出生年份'
from Student
T10.
查詢?nèi)w學(xué)生的姓名和出生年份芯义，并在出生年份列前加入一個(gè)列肛搬，此列的每行數(shù)據(jù)均為“Year of Birth”常量值
select Sname,'Year of Birth',2018-Sage as '出生年份'
from Student
T11.
在選課表（SC）中查詢有哪些學(xué)生選修了課程，并列出學(xué)生的學(xué)號(hào)
select distinct Sno
from SC
T12.
查詢計(jì)算機(jī)系全體學(xué)生的姓名
select Sname
from Student
where Sdept='計(jì)算機(jī)系'
T13.
查詢所有年齡在20歲以下的學(xué)生的姓名及年齡
select Sname,Sage
from Student
where Sage<20
T14.
查詢考試成績不及格的學(xué)生的姓名
select Sname
from Student,SC
where Student.Sno=SC.Sno and SC.Grade<60
T15.
查詢年齡在20~23歲之間的學(xué)生的姓名毕贼，所在系和年齡
select Sname,Sdept,Sage
from Student
where Sage between 20 and 23
T16.
查詢年齡不在20~23之間的學(xué)生的姓名温赔，所在系和年齡
select Sname,Sdept,Sage
from Student
where Sage not between 20 and 23
T17.
查詢信息系，數(shù)學(xué)系和計(jì)算機(jī)系學(xué)生的姓名和性別
select Sname,Ssex
from Student
where Sdept in ('信息系','數(shù)學(xué)系','計(jì)算機(jī)系')
T18.
查詢既不屬于信息系鬼癣，數(shù)學(xué)系陶贼，也不屬于計(jì)算機(jī)系的學(xué)生的姓名和性別
select Sname,Ssex
from Student
where Sdept not in ('信息系','數(shù)學(xué)系','計(jì)算機(jī)系')
T19.
查詢學(xué)生表中姓“張”的學(xué)生的詳細(xì)信息
select *
from Student
where Sname like '張%'
T20.
查詢學(xué)生表中姓“張”，姓“李”和姓“劉”的學(xué)生的情況
select *
from Student
where Sname like '張%' or Sname like '李%' or Sname like '劉%'
使用下邊的這種寫法更加簡便
select *
from Student
where Sname like '[張李劉]%'
T21.
查詢名字中第2個(gè)字為“小”或“大”字的學(xué)生的姓名和學(xué)號(hào)
select Sname,Sno
from Student
where Sname like '_[小大]%'
T22.
查詢所有不姓“劉”的學(xué)生的姓名
select Sname
from Student
where Sname not like '劉%'
T23.
從學(xué)生表中查詢學(xué)號(hào)的最后一位不是2待秃，3拜秧，5的學(xué)生的情況
select *
from Student
where Sno not like '%[235]'
T24.
查詢無考試成績的學(xué)生的學(xué)號(hào)和相應(yīng)的課程號(hào)
select Sno,Cno
from SC
where Grade is null
T25.
查詢所有有考試成績的學(xué)生的學(xué)號(hào)和課程號(hào)
select Sno,Cno
from SC
where Grade is not null
T26.
查詢計(jì)算機(jī)系年齡在20歲以下的學(xué)生的姓名
select Sname
from Student
where Sdept='計(jì)算機(jī)系' and Sage<20
T27.
將學(xué)生按年齡升序排序
select *
from Student
order by Sage asc
T28.
查詢選修了課程“c02”的學(xué)生的學(xué)號(hào)及其成績，查詢結(jié)果按成績降序排列
select Sno,Grade
from SC
where Cno='C02'
order by Grade desc
T29.
查詢?nèi)w學(xué)生的信息章郁，查詢結(jié)果按所在系的系名升序排列枉氮，同一系的學(xué)生按年齡降序排列
select *
from Student
order by Sdept,Sage desc
T30.
統(tǒng)計(jì)學(xué)生總?cè)藬?shù)
select count(*)
from Student
T31.
統(tǒng)計(jì)選修了選修課程的學(xué)生的人數(shù)
select count(distinct Sno)
from SC
where XKLB='選修'
T32.
計(jì)算學(xué)號(hào)為9512101的學(xué)生的考試總成績之和
select sum(Grade)
from SC
where Sno='9512101'
T33.
計(jì)算課程“c01”的學(xué)生的考試平均成績
select avg(Grade)
from SC
where Cno='C01'
T34.
查詢選修了課程“c01”的學(xué)生的最高分和最低分
select max(Grade),min(Grade)
from SC
where Cno='C01'
T35.
統(tǒng)計(jì)每門課程的選課人數(shù)志衍，列出課程號(hào)和人數(shù)
select Cno,count(Sno) as '選課人數(shù)'
from SC
group by Cno
T36.
查詢每名學(xué)生的選課門數(shù)和平均成績
select Sno,count(Cno) as '選課門數(shù)',avg(Grade) as '平均成績'
from SC
group by Sno
T37.
查詢選修了3門以上課程的學(xué)生的學(xué)號(hào)
select Sno
from SC
group by Sno
having count(Cno)>3
T38.
查詢選課門數(shù)等于或大于4門的學(xué)生的平均成績和選課門數(shù)
select Sno,avg(Grade) as '平均成績',count(*) as '選課門數(shù)'
from SC
group by Sno
having count(*)>=4
四、多表查詢
T39.
查詢每個(gè)學(xué)生的情況及其選課的情況
select *
from Student left outer join SC on Student.Sno=SC.Sno
T40.
查詢計(jì)算機(jī)系學(xué)生的選課情況聊替，要求列出學(xué)生的名字楼肪，所修課的課程號(hào)和成績
select Sname,Cno,Grade
from Student,SC
where Student.Sno=Sc.Sno and Sdept='計(jì)算機(jī)系'
T41.
查詢信息系選修VB課程的學(xué)生的成績，要求列出學(xué)生姓名惹悄，課程名和成績
select Sname,Cname,Grade
from Student,SC,Course
where Student.Sno=SC.Sno and SC.Cno=Course.Cno and sdept='信息系' and Cname='VB'
select Sname,Cname,Grade
from Student join SC on Student.Sno=SC.Sno join Course on Course.Cno=SC.Cno
where Sdept = '信息系' and Cname = 'VB';
T42.
查詢所有選修了VB課程的學(xué)生的情況春叫，要求列出學(xué)生姓名和所在的系
select Sname,Sdept
from Student join SC on Student.Sno=SC.Sno join Course on Course.Cno=SC.Cno
where Cname='VB'
T43.
查詢與劉晨在同一個(gè)系學(xué)習(xí)的學(xué)生的姓名和所在系
select Sname,Sdept
from Student
where Sdept=(select Sdept
from Student
where Sname='劉晨')
select s2.Sname,s2.Sdept
from Student as s1,Student as s2
where s1.Sname='劉晨' and s2.Sname !='劉晨' and s1.Sdept=s2.Sdept
T44.
查詢學(xué)生的選課情況，包括選修課程的學(xué)生和沒有修課的學(xué)生
select *
from Student left join SC on Student.Sno=SC.Sno
T45.
查詢數(shù)學(xué)系成績?cè)?0分以上的學(xué)生的學(xué)號(hào)泣港，姓名
select Student.Sno,Sname
from Student join SC on Student.Sno=SC.Sno
where Sdept='數(shù)學(xué)系' and Grade>80
T46.
查詢計(jì)算機(jī)系考試成績最高的學(xué)生的姓名
select Sname
from Student join SC on Student.Sno=SC.Sno
where Sdept='計(jì)算機(jī)系' and Grade=(select max(Grade)
from Student join SC on Student.Sno=SC.Sno
Group by Sdept
having Sdept='計(jì)算機(jī)系')
五暂殖、嵌套子查詢
(T43)
T47.
查詢成績大于90分的學(xué)生的學(xué)號(hào)和姓名
select Sno,Sname
from Student
where Sno in (select Sno
from SC
where Grade>90)
T48.
查詢選修了“數(shù)據(jù)庫基礎(chǔ)”課程的學(xué)生的學(xué)號(hào)和姓名
select Sno,Sname
from Student
where Sno in (select Sno
from SC
where Cno in (select Cno
from Course
where Cname='數(shù)據(jù)庫基礎(chǔ)'))
T49.
查詢選修了劉晨沒有選修的課程的學(xué)生的學(xué)號(hào)和所在系
select Sno,Sdept
from Student
where Sno in (select Sno
from SC
where Cno not in (select Cno
from SC
where Sno=(select Sno
from Student
where Sname='劉晨')))
T50.
查詢選修了課程“c02”且成績高于此課程的平均成績的學(xué)生的學(xué)號(hào)和成績
select Sno,Grade
from SC
where Cno='C02' and Grade>(select avg(Grade)
from SC
where Cno='C02')
T51.
查詢選修了課程“c01”的學(xué)生姓名
select Sname
from Student
where Sno in (select Sno
from SC
where Cno='C01')
select Sname
from Student
where exists (select *
from SC
where Sno=Student.Sno and Cno='C01')
PS.一般來說，在SQL中in子查詢都可以用exists代替当纱。EXISTS子查詢可以看成是一個(gè)獨(dú)立的查詢系統(tǒng)呛每，只為了獲取真假邏輯值，EXISTS子查詢與外查詢查詢的表是兩個(gè)完全獨(dú)立的毫無關(guān)系的表坡氯，當(dāng)我們?cè)谧硬樵冎刑砑恿薙no關(guān)聯(lián)之后莉给，EXISTS子查詢與外查詢查詢的表就統(tǒng)一了，是二者組合組建的虛表廉沮，是同一個(gè)表（這樣當(dāng)子查詢查詢到虛表中當(dāng)前行的Cno為C01時(shí)，則將虛表當(dāng)前行中對(duì)應(yīng)的Sname查詢到了）.
重點(diǎn)在于Sno關(guān)聯(lián)之上徐矩，添加Sno關(guān)聯(lián)滞时，數(shù)據(jù)庫會(huì)先將兩張表通過Sno關(guān)聯(lián)組合成一張?zhí)摫恚械牟樵儾僮鞫荚谶@張?zhí)摫砩贤瓿伞?/p>
六滤灯、自查詢
T52.查詢所有成績大于C01課程最高成績的學(xué)生的學(xué)號(hào)
select SC2.Sno
from SC as SC1,SC as SC2
where SC1.Sno=SC2.Sno and SC1.Cno=SC2.Cno and SC2.Grade>all(select Grade
from SC
where SC.Cno='C01')
七坪稽、更新數(shù)據(jù)
T53.
將所有學(xué)生的年齡加1
update Student
set Sage=Sage+1
T54.
將“9512101”學(xué)生的年齡改為21歲
update Student
set Sage=21
where Sno='9512101'
T55.
將計(jì)算機(jī)系學(xué)生的成績加5分
update SC
set Grade=Grade+5
where Sno in (select Sno
from Student
where Sdept='計(jì)算機(jī)系')
八、刪除數(shù)據(jù)
T56.
刪除所有學(xué)生的選課記錄
DELETE FROM SC
T57.
刪除所有不及格學(xué)生的選課記錄
delete from SC
where Grade<60
T58.
刪除計(jì)算機(jī)系不及格學(xué)生的選課記錄
delete from SC
where Grade<60 and Sno in (select Sno
from Student
where Sdept='計(jì)算機(jī)系')
數(shù)據(jù)來源于網(wǎng)絡(luò)鳞骤，整理@lihui
測試代碼：
建表代碼
題目代碼
測試環(huán)境：SQL Server 2017
