🍜基本表的定义与删除
- 用SQL语句创建如下三张表:学生(Student),课程表(Course),和学生选课表(SC),这三张表的结构如表1-1到表1-3所示。
1、表1-1 Student表结构
列名 说明 数据类型 约束
Sno 学号 字符串,长度为7 主键
Sname 姓名 字符串,长度为15 非空
Ssex 性别 字符串,长度为3 取‘男’或‘女’
Sage 年龄 整数 取值15~45
Sdept 所在系 字符串,长度为30 默认为‘计算机系’
2、表1-2Course表结构
列名 说明 数据类型 约束
Cno 课程号 字符串,长度为15 主键
Cname 课程名 字符串,长度为20 非空
Ccredit 学分 整数 取值大于0
Semster 学期 整数 取值大于0
3、表1-3 SC表结构
列名 说明 数据类型 约束
Sno 学号 字符串,长度为7 关联Student表主键Sno
Cno 课程名 字符串,长度为10 关联Course表主键Cno
Grade 成绩 整数 取值0~100
XKLB 课程类别(选修|必修) 字符串 长度6
练习题(含答案)
--student_table创建
create table Student_table(
Sno varchar2(7) primary key,
Sname varchar2(15) not null,
Ssex varchar2(3) check(Ssex='男' or Ssex='女'),
Sage number(3) check(Sage>15 and Sage<45),
Sdept varchar(30) default('计算机系')
)
select * from Student_table;
-- Course创建
create table Course_table(
Cno varchar2(15) primary key,
Cname varchar2(20) not null,
Ccredit number(3) check(Ccredit>=0),
Semster number(3) check(Semster>=0)
)
select * from Course_table;
--Sc表创建
create table SC_table(
Sno varchar2(7) references Student_table(Sno),
Cno varchar2(10) references Course_table(Cno),
Grade number(3) check(Grade>0 and Grade<100),
XKLB varchar2(6)
)
select * from SC_table;
select * from Student_table;
-- 插入数据Student
insert into Student_table values
(9512101,'李勇','男',19,default);
insert into Student_table values
(9512102,'刘晨','男',19,default);
insert into Student_table values
(9512103,'王敏','女',20,default);
insert into Student_table values
(9521101,'张立','男',22,'信息系');
insert into Student_table values
(9521102,'吴兵','女',21,'信息系');
insert into Student_table values
(9521103,'张海','男',20,'信息系');
insert into Student_table values
(9531101,'钱小平','女',18,'数学系');
insert into Student_table values
(9531102,'王大力','男',19,'数学系');
-- 插入Course
INSERT INTO Course_table VALUES('C01','计算机文化学',3,1);
INSERT INTO Course_table VALUES('C02','VB',2,3);
INSERT INTO Course_table VALUES('C03','计算机网络',4,7);
INSERT INTO Course_table VALUES('C04','数据库基础',6,6);
INSERT INTO Course_table VALUES('C05','高等数学',8,2);
INSERT INTO Course_table VALUES('C06','数据结构',5,4);
commit;
select * from Course_table;
--插入sc
INSERT INTO SC_table VALUES(9512101,'C01',90,'必修');
INSERT INTO SC_table VALUES(9512101,'C02',86,'选修');
INSERT INTO SC_table VALUES(9512101,'C06',NULL,'必修');
INSERT INTO SC_table VALUES(9512102,'C02',78,'选修');
INSERT INTO SC_table VALUES(9512102,'C04',66,'必修');
INSERT INTO SC_table VALUES(9521102,'C01',82,'选修');
INSERT INTO SC_table VALUES(9521102,'C02',75,'选修');
INSERT INTO SC_table VALUES(9521102,'C04',92,'必修');
INSERT INTO SC_table VALUES(9521102,'C05',50,'必修');
INSERT INTO SC_table VALUES(9521103,'C02',68,'选修');
INSERT INTO SC_table VALUES(9521103,'C06',NULL,'必修');
INSERT INTO SC_table VALUES(9531101,'C01',80,'选修');
INSERT INTO SC_table VALUES(9531101,'C05',95,'必修');
INSERT INTO SC_table VALUES(9531102,'C05',85,'必修');
COMMIT;
select * from sc_table;
--1查询全体学生的学号与姓名。
select sno,sname from Student_table;
--2查询全体学生的姓名,学号和所在系。
select sno,sname,sdept from Student_table;
select * from Student_table;
--**5.查询全体学生的姓名及其出生年份。**
select to_char(sysdate,'yyyy')from dual;
select sname, to_number(to_char(sysdate, 'yyyy')) - sage "出生年份"
from Student_table;
--6.查询全体学生的姓名和出生年份,并在出生年份列前加入一个列,此列的每行数据均为“Year of Birth”常量值
select * from student_table;
select 'Year of Birth',sname,to_number(to_char(sysdate,'yyyy'))-sage "出生年龄" from Student_table;
--7.在选课表(SC)中查询有哪些学生选修了课程,并列出学生的学号
select * from sc_table;
select sno from sc_table where xklb='选修';
--**8.查询计算机系全体学生的姓名。
select sname from student_table where sdept='计算机系';
--9.查询所有年龄在20岁以下的学生的姓名及年龄
select sname,sage from student_table where sage<20;
--10.查询考试成绩不及格的学生的学号
select sno from sc_table where nvl(grade,0)<60;
--**11.查询年龄在20~23岁之间的学生的姓名,所在系和年龄。**
select sname,sdept,sage from student_table where sage>20 and sage<23;
--**12.查询年龄不在20~23之间的学生的姓名,所在系和年龄。**
select sname,sdept,sage from student_table where not(sage>20 and sage<23);
--**13.查询信息系,数学系和计算机系学生的姓名和性别。**
select sname,ssex from student_table where sdept='计算机系' or sdept='数学系' or sdept='信息系';
--14.查询既不属于信息系,数学系,也不属于计算机系的学生的姓名和性别
select sname,ssex from student_table where not(sdept='计算机系' or sdept='数学系' or sdept='信息系');
--15.查询姓“张”的学生的详细信息
select * from student_table where sname like '张%';
--16.查询学生表中姓“张”,姓“李”和姓“刘”的学生的情况
select * from student_table where sname like '张%' or sname like '李%' or sname like'刘%';
--**17.查询名字中第2个字为“小”或“大”字的学生的姓名和学号。**
select sname ,sno from student_table where sname like '_小%' or sname like '_大%';
--**18.查询所有不姓“刘”的学生。**
select * from student_table where not(sname like '刘%');
--**19.从学生表中查询学号的最后一位不是2,3,5的学生的情况。**
select * from student_table where sno not like '%3' or sno not like '%2' or sno not like '%5';
--**20.查询无考试成绩的学生的学号和相应的课程号。**
select * from sc_table;
select sno,cno from sc_table where nvl(grade,0)=0;
select sno,cno from sc_table where grade is null;
--**21.查询所有有考试成绩的学生的学号和课程号。**
select sno,cno from sc_table;
--22.查询计算机系年龄在20岁以下的学生的姓名
select sname from student_table where sdept='计算机系' and sage<20;
--**23.将学生按年龄升序排序。**
select sname,sage from student_table order by sage;
--**24.查询选修了课程“c02”的学生的学号及其成绩,查询结果按成绩降序排列。**
select sno,grade from sc_table where cno='C02' order by grade desc;
--**25.查询全体学生的信息,查询结果按所在系的系名升序排列,同一系的学生按年龄降序排列。**
select * from student_table order by sdept,sage desc;
--**26.统计学生总人数。**
select count(1) from student_table;
--**27.统计选修了课程的学生的人数。**
select * from course_table;
select * from sc_table;
select count(1) from sc_table where xklb='选修';
--**28.计算学号为9512101的学生的考试总成绩之和。**
select sum(grade) from sc_table where sno=9512101;
--**29.计算课程“c01”的学生的考试平均成绩。**
select avg(grade) from sc_table where cno='C01';
--**30.查询选修了课程“c01”的学生的最高分和最低分。**
select max(grade),min(grade) from sc_table where cno='C01';
--**31.统计每门课程的选课人数,列出课程号和人数。**
select count(1),min(cno) from sc_table group by cno;
--**32.查询每名学生的选课们数和平均成绩。**
select count(1),avg(grade) from sc_table group by sno;
--**33.查询选修了3门以上课程的学生的学号。**
select sno from sc_table group by sno having count(1)>3;
--**34.查询选课门数等于或大于4门的学生的平均成绩和选课门数。**
select count(1),avg(grade) from sc_table group by sno having count(1)>=4;
--**35.查询每个学生的情况及其选课的情况。**
select * from student_table;
select * from sc_table;
select * from student_table s1 join sc_table s2 on s1.sno=s2.sno;
select * from student_table s1,sc_table s2 where s1.sno=s2.sno;
--**37.查询计算机系学生的选课情况,要求列出学生的名字,所修课的课程号和成绩。**
select s1.sname, s2.cno, s2.grade,s2.xklb
from student_table s1, sc_table s2
where s1.sno = s2.sno
and xklb = '选修';
--**38.查询信息系选修VB课程的学生的成绩,要求列出学生姓名,课程名和成绩。**
select s1.sname,c.cname,s2.grade from sc_table s2
join student_table s1
on s1.sno = s2.sno
join course_table c
on s2.cno = c.cno
where c.cname = 'VB'
and s1.sdept = '信息系';
--39.查询所有选修了VB课程的学生的情况,要求列出学生姓名和所在的系。\
select s1.sname,s1.sdept,c.cname from sc_table s2
join student_table s1
on s1.sno = s2.sno
join course_table c
on s2.cno = c.cno
where c.cname = 'VB';
--40.查询与刘晨在同一个系学习的学生的姓名和所在系
select sdept from student_table where sname='刘晨';
select sname,sdept from student_table where sdept=(select sdept from student_table where sname='刘晨') and sname!='刘晨';
--41.查询学生的选课情况,包括选修课程的学生和没有修课的学生
select * from sc_table s1 join student_table s2 on s1.sno=s2.sno;
--**43.查询成绩大于90分的学生的学号和姓名。**
select a1.sno,a1.sname from student_table a1 join sc_table a2 on a1.sno=a2.sno where a2.grade>90;
--**44.查询选修了“数据库基础”课程的学生的学号和姓名。**
select cno from course_table where cname='数据库基础';
select a1.sno, a1.sname
from student_table a1
join sc_table a2
on a1.sno = a2.sno
where cno = (select cno from course_table where cname = '数据库基础');
--**45.查询选修了课程“c02”且成绩高于次课程的平均成绩的学生的学号和成绩。**
select * from sc_table;
select avg(grade) from sc_table where cno='C02' group by cno;
select sno, grade
from sc_table
where grade >
(select avg(grade) from sc_table where cno = 'C02' group by cno)
and cno = 'C02';
--**46.查询选修了课程“c01”的学生姓名。**
select s1.sname
from student_table s1
join sc_table s2
on s1.sno = s2.sno
where cno = 'C02';
--**47.查询没有选修课程“c01”的学生姓名和所在系。**
select s1.sname,s1.sdept,s2.cno
from student_table s1
join sc_table s2
on s1.sno = s2.sno
where cno != 'C01';
--**48.查询选修了课程“c01”的学生的姓名和所在系。**
select s1.sname,s1.sdept,s2.cno
from student_table s1
join sc_table s2
on s1.sno = s2.sno
where cno = 'C01';
--**49.查询数学系成绩在80分以上的学生的学号,姓名。**]
select * from course;
select s1.sno, s1.sname,s1.sdept
from student_table s1
join sc_table s2
on s1.sno = s2.sno
where s2.grade>80 and s1.sdept='数学系';
--**50.查询计算机系考试成绩最高的学生的姓名。**
select max(s2.grade) from student_table s1 join sc_table s2 on s1.sno=s2.sno where sdept='计算机系' ;
select *
from student_table s1
join sc_table s2
on s1.sno = s2.sno
where s2.grade = (select max(s2.grade)
from student_table s1
join sc_table s2
on s1.sno = s2.sno
where sdept = '计算机系');
--**51.将新生纪录(9521105,陈冬,男,信息系,18岁)插入到Student表中。**
select * from student_table;
insert into student_table values(9521105,'陈东','男',18,'信息系');
commit;
--**52.在SC表中插入一新记录(9521105,c01),成绩暂缺。**
insert into sc_table(sno,cno) values (9521105,'C01');
commit;
select * from sc_table;
--**53.将所有学生的年龄加1。**
update student_table set sage=sage+1;
commit;
--54.将“9512101”学生的年龄改为21岁。
update student_table set sage=21 where sno='9512101';
select * from student_table;
--**55.将计算机系学生的成绩加5分。**
select sno from student_table where sdept='计算机系';
update sc_table set grade=grade+5 where sno in(select sno from student_table where sdept='计算机系');
select * from sc_table;
--**56.删除所有学生的选课记录。**\
delete sc_table;
--**57.删除所有不及格学生的选课记录。**
delete sc_table where grade<60;
--**58.删除计算机系不及格学生的选课记录。**
select sno from student_table where sdept='计算机系';
delete sc_table where sno in(select sno from student_table where sdept='计算机系') and grade<60;
完结~~~~,网图————>