SQL练习题

🍜基本表的定义与删除

---

• 用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;

---

完结~~~~，网图————>

---