珍藏的資料庫SQL基礎練習題答案-有解無憂

一,基本表的定義與洗掉.

題1:
用SQL陳述句創建如下三張表：學生（Student）,課程表（Course）,和學生選課表（SC）,這三張表的結構如表1-1到表1-3所示，

表1-1 Student表結構
列名說明資料型別約束
Sno 學號字串，長度為7 主碼
Sname 姓名字串，長度為10 非空
Ssex 性別字串，長度為2 取‘男’或‘女’
Sage 年齡整數取值15~45
Sdept 所在系字串，長度為20 默認為‘計算機系’

create table Student(
Sno char(7) primarykey,
Sname char(10) not null,
Ssex char(4) check(Ssex='男' or Ssex='女') ,
Sage number() check(Sage>=15 and Sage<=45),
Sdept char(20) default('計算機系'));
-------------------------------------------------------------------------------------------------------------------

表1-2Course表結構
列名說明資料型別約束
Cno 課程號字串，長度為10 主碼
Cname 課程名字串，長度為20 非空
Ccredit 學分整數取值大于0
Semster 學期整數取值大于0
Period 學時整數取值大于0

create table Course(
Cno char(10) primarykey,
Cname char(20) not null,
Ccredit nuber() check(Ccredit>0) ,
Cemster number() check(Cemster>0),
Period number() check(Period>0));
-----------------------------------------------------------------------------------------------------------------

表1-3 SC表結構
列名說明資料型別約束
Sno 學號字串，長度為7 主碼，參考Student的外碼
Cno 課程名字串，長度為10 主碼，參考Course
Grade 成績整數取值0~100

create table SC(
Sno char(7),
Cno char(10),

primary key(Sno,Cno),

foreign key (Sno) references Student(Sno),
foreign key (Cno) references.3 Course(Cno),

Grade number() check(Grade>=0 and Grade<=100));

-------------------------------------------------------------------------------------------------------------------

二，修改表結構

題2：
為SC表添加“選課類別”列，此列的定義為XKLB char(4).
alter SC set add XLB char(4) null;

題3：
將新添加的XKLB的型別改為char(6)，
alter table SC alter column XKLB char(6);

題4：
洗掉Course表的Period列，
alter from drop column Period;

三，資料查詢功能

表3-1 Student表資料
Sno Sname Ssex Sage Sdept
9512101 李勇男 19 計算機系
9512102 劉晨男 20 計算機系
9512103 王敏女 20 計算機系
9521101 張立男 22 資訊系
9521102 吳賓女 21 資訊系
9521103 張海男 20 資訊系
9531101 錢小平女 18 數學系
9531102 王大力男 19 數學系

insert into Student(Sno,Sname,Ssex,Sage,Sdept)
values(
'9512101','李勇','男',19,'計算機系';
'9512102','劉晨','男',20,'計算機系';
'9512103','王敏','女',20,'計算機系';
'9521101','張立','男',22,'資訊系';
'9521102','吳賓','女','21','資訊系';
'9521103','張海','男',20,'資訊系';
'9531101','錢小平','女',18,'數學系';
'9531102','王大力','男',19,'數學系')
-------------------------------------------------------------------------------------------------------------------

表3-2 Course表資料
Cno Cname Ccredit Cemester
C01 計算機文化學 3 1
C02 VB 2 3
C03 計算機網路 4 7
C04 資料庫基礎 6 6
C05 高等數學 8 2
C06 資料結構 5 4

insert into Course(Cno,Cname,Ccredit,Cemester) values(
'C01','計算機文化學',3,1;
'C02','VB',2,3;
'C03','計算機網路',4,7;
'資料庫基礎',6,6;
'C05','高等數學',8,2;
'C06','資料結構',5,4)
-------------------------------------------------------------------------------------------------------------------

表 3-3 SC表資料
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,'必修')

insert into SC(Sno,Cno,Grade) values('9521102','C02',75,'選修')
insert into SC values('9521102','C04',92,'必修')
insert into SC values('9521102','C05',50,'必修')
insert into SC values('9521103','C02',68,'選修')
insert into SC values('9521103','C06',NULL,'必修')
insert into SC values('9531101','C01',80,'選修')
insert into SC values('9531101','C05',95,'必修')
insert into SC values('9531102','C05',85,'必修')

-------------------------------------------------------------------------------------------------------------------

題5：
用sql陳述句填寫以上（表3-1 Student表資料、表3-2 Course表資料、表 3-3 SC表資料）資料，

題6：
查詢全體學生的學號與姓名，
select Sno,Sname from Student;

題7：
查詢全體學生的姓名，學號和所在系，
select Sno,Sname,Sdept from Student;

題8：
查詢全體學生的記錄，

select * from SC join Student on Student.Sno=SC.Sno;

題9：
查詢全體學生的姓名及其出生年份，
select Sname,2011-Sage as '出生年份';

題10：
查詢全體學生的姓名和出生年份，并在出生年份列前加入一個列，此列的每行資料均為“Year of Birth”常量值，
select Sname,'出生年份',2011-Sage from Student;

題11：
在選課表（SC）中查詢有哪些學生選修了課程，并列出學生的學號，
select distingct Sno from SC;

題12：
查詢計算機系全體學生的姓名，
select Sname from Student where Sdept='計算機系';

題13：
查詢所有年齡在20歲以下的學生的姓名及年齡，
slect Sname,Sage from Student where Sage<20;

題14：
查詢考試成績不及格的學生的學號，
select distingct Sno from SC where Grade <60;

題15：
查詢年齡在20~23歲之間的學生的姓名，所在系和年齡，
select Sname,Sdept,Sage from Student where Sage between 20 and 23;

題16：
查詢年齡不在20~23之間的學生的姓名，所在系和年齡，
select Sname,Sdept,Sage from Student where Sage not between 20 and 23;

題17：
查詢資訊系，數學系和計算機系學生的姓名和性別，
select Sname,Ssex from Student where Sdept in ('資訊系','數學系','計算機系');

題18：
查詢既不屬于資訊系，數學系，也不屬于計算機系的學生的姓名和性別，
select Sname,Ssex from Student where Sdept not in ('資訊系','數學系','計算機系');

題19：
查詢姓“張”的學生的詳細資訊，
select * from Student where Sname like '張%';

題20：
查詢學生表中姓“張”，姓“李”和姓“劉”的學生的情況，
select * from Student where Sname like '[張李劉]%';

題21：
查詢名字中第2個字為“小”或“大”字的學生的姓名和學號，
select Sname,Sno from Student where Snme like '_[小大]%';

題22：
查詢所有不姓“劉”的學生，
select Sname from Student where Sname not like '劉%';

題23：
從學生表中查詢學號的最后一位不是2，3，5的學生的情況，
select * from where Sno not like '%[235]';

題24：
查詢無考試成績的學生的學號和相應的課程號，
select Sno,Cno from SC where Grade is null;

題25：
查詢所有有考試成績的學生的學號和課程號，
select Sno,Cno from SC where Grade is not null;

題26：
查詢計算機系年齡在20歲以下的學生的姓名，
select Sname from Student where Sdept = '計算機系' and Sage < 20;

題27：
將學生按年齡升序排序，
select * from Student order by Sage;

題28：
查詢選修了課程“c02”的學生的學號及其成績，查詢結果按成績降序排列，
select Sno,Grade from SC where Cno='c02' order by Grade desc;

題29：
查詢全體學生的資訊，查詢結果按所在系的系名升序排列，同一系的學生按年齡降序排列，
select * from Student order by Sdept,Sage desc;

題30：
統計學生總人數，
select count(*) from Student;

題31：
統計選修了課程的學生的人數，
select count(distingct Sno) from SC;

題32 ：
計算學號為9512101的學生的考試總成績之和，
select sum(Grade) from SC where Sno='9512101';

題33：
計算課程“c01”的學生的考試平均成績，
select avg(Grade) from SC where Cno='c01';

題34：
查詢選修了課程“c01”的學生的最高分和最低分，
select max(Grade),min(Grade) from SC where Cno='c01';

題35：
統計每門課程的選課人數，列出課程號和人數，
select Cno as '課程號',count(Sno) as '選課人數' from SC group by Cno;

題36：
查詢每名學生的選課們數和平均成績，
select Sno as '學號',count(*) as '選課門數',avg(Grade) as '平均成績' from SC group by SNo;

題37：
查詢選修了3門以上課程的學生的學號，
select Sno from SC group by Sno having count(*) > 3;

題38：
查詢選課門數等于或大于4門的學生的平均成績和選課門數，
select Sno,avg(Grade) '平均成績',count(*) '選課門數' from SC group by Sno having count(*) >= 4;

四,多表連接查詢，

題39：
查詢每個學生的情況及其選課的情況，
select * from Student join SC on Student.Sno=SC.Sno

題40：
去掉例38中的重復列，
select Sno,avg(Grade) '平均成績',count(distingct Sno) '選課門數' from SC group by Sno having count(*) >= 4;

題41：
查詢計算機系學生的選課情況，要求列出學生的名字，所修課的課程號和成績，
select Sname,Cno,Grade from Student join SC on Student.Sno=SC.Sno where Sdept = '計算機系';

題42：
查詢資訊系選修VB課程的學生的成績，要求列出學生姓名，課程名和成績，
select Sname,Cname,Grade from Student s join SC on s.Sno=SC.Sno join Course c on c.Cno=SC.Cno
where Sdept = '資訊系' and Cname = 'VB';

題43：
查詢所有選修了VB課程的學生的情況，要求列出學生姓名和所在的系，
select Sname,Cname,Grade from Student s join SC on s.Sno=SC.Sno join Course c on c.Cno=SC.Cno
where Cname = 'VB';

題44：
查詢與劉晨在同一個系學習的學生的姓名和所在系，
select S2.Sname,S2.Sdept from Student S1 join Student S2 on S1.Sdept = S2.Sdept
where S1.Sname = '劉晨' and S2.Sname !='劉晨';

題45：
查詢學生的選課情況，包括選修課程的學生和沒有修課的學生，
select Student.Sno,Sname,Cno,Grade from Student left outer join SC on Student.Sno = SC.Sno;

五，自查詢，

題46：
查詢與劉晨在同一個系的學生，
select Sno,Sname,Sdept from Student where Sdept in (
select Sdept from Student
where Sname='劉晨') and Sname='劉晨';

題47：
查詢成績大于90分的學生的學號和姓名，
select Sno,Sname from Student
where Sno in (select Sno from SC where Grade > 90);

題48：
查詢選修了“資料庫基礎”課程的學生的學號和姓名，
select Sno,Sname from Student where Sno in
(
select Sno from SC where Cno in
(
select Cno from Course
where Cname='資料庫基礎'
)
);

題49：
查詢選修了課程“c02”且成績高于次課程的平均成績的學生的學號和成績，
select Sno,Grade from SC where Cno='c02' and
Grade >(select avg(Grade) from SC where Cno = 'c02');

題50：
查詢選修了課程“c01”的學生姓名，
select Sname from Student
where exists ( select * from SC where Sno = Student.Sno and Cno='c01');

題51：
查詢沒有選修課程“c01”的學生姓名和所在系，
select Sname,Sdept from Student
where not exists ( select * from SC where Sno = Student.Sno and Cno='c01');

六，自查詢，

題52：
查詢選修了課程“c01”的學生的姓名和所在系，
select Sname,Sdept from Student
where exists ( select * from SC where Sno = Student.Sno and Cno='c01');

題53：
查詢數學系成績在80分以上的學生的學號，姓名，
select Sname,Sno from Student
where exists ( select * from SC where Sno = Student.Sno and Grade>=80);

題54：
查詢計算機系考試成績最高的學生的姓名，
select Sname from Student
where exists ( select * from SC where Sno = Student.Sno and max(Grade));

七，插入資料
題55：
將新生紀錄（9521105，陳冬，男，資訊系，18歲）插入到Student表中，
insert into Student values('9521105','陳冬','男',18,'資訊系');

題56：
在SC表中插入一新記錄（9521105，c01），成績暫缺，
insert into SC values('9521105','c01',null,'必修');

八，更新資料，

題57：
將所有學生的年齡加1，
update Student set Sage = Sage+1;

題58：
將“9512101”學生的年齡改為21歲，
update Student Sage=21 where Sno='9512101';

題59：
將計算機系學生的成績加5分，
update SC set Grade = Grade + 5 where Sno in (select Sno from Student where Sdept='計算機系');

九，洗掉資料，

題60:
洗掉所有學生的選課記錄，
delete from SC;

題61：
洗掉所有不及格學生的選課記錄，
delete from SC where Grade < 60;

題62：
洗掉計算機系不及格學生的選課記錄，
delete from SC where Grade < 60 and Sno in (select Sno from Student where Sdept = '計算機系');

轉載自：https://www.cnblogs.com/caiyishuai/p/10648909.html

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/37874.html

標籤：MySQL

上一篇：MySQL （InnoDB）在什么情況下無法使用索引

下一篇：用了這么多年MySql，這些好習慣你用過哪些