SQL练习题简单篇

SQL语句练习题附建表语句，学不会我把我手剁下来寄给你

实验一<数据定义>

（1）创建学生管理数据库

create database xsgl
on (
name=xsgl,
filename='E:\SQL\xsgl.mdf',
size=5mb,
maxsize=50mb,
filegrowth=1mb);

–（2）分别创建3个表并插入数据

use xsgl;

create table student
(Sno char(10) primary key,
Sname char(8),
Ssex char(2),
Sage smallint,
Sdept char(20),);

create table course
(Cno char(2) primary key,
Cname char(20),
Cpno char(2),
Ccredit tinyint);

create table sc
(Sno char(10),
Cno char(2),
Grade tinyint,
primary key (sno,cno),
foreign key (sno) references student(sno) on update cascade,
foreign key (cno) references course(cno) on update cascade);

insert into student values ('201215121','李勇','男',20,'CS');
insert into student values ('201215122','刘晨','女',19,'CS');
insert into student values ('201215123','王敏','女',18,'MA');
insert into student values ('201215125','张立','男',19,'IS');
insert into student values ('201215124','张晨','女',23,'IS');
insert into student values ('201215126','李国平','男',22,'MA');
insert into student values ('201215128','张一山','男',25,'CS');

insert into course values ('1','数据库','5',4);
insert into course values ('2','数学',null,2); 
insert into course values ('3','信息系统','1',4);
insert into course values ('4','操作系统','6',3);
insert into course values ('5','数据结构','7',4); 
insert into course values ('6','数据处理',null,2);
insert into course values ('7','PASCAL语言','6',4);

insert into sc values ('201215121','1',92);
insert into sc values ('201215121','2',85); 
insert into sc values ('201215121','3',88); 
insert into sc values ('201215122','2',90);
insert into sc values ('201215122','3',80); 

–（3）把创建表的SQL命令一文件的形式保存到磁盘上

select * from student;

select * from course;

select * from sc;

–（4）在student表中增加“brithday"属性列，数据类型为DATeTIM

ALTER TABLE student ADD brithday DATETIME; 

–（5）删除student表中“brithday"属性列

ALTER TABLE student DROP COLUMN brithday;

–（6）在course表中的cname属性列创建唯一索引

create unique index icname on course(cname);

–（7）删除course表中XM索引

drop index icname on course;

实验二<数据查询>

1． 查询学生的基本信息；

select *from Student;

– 2． 查询“CS”系学生的基本信息；

select *from Student where Sdept='CS';

– 3． 查询“CS”系学生年龄在19到21之间的学生的学号、姓名；

select Sno,Sname,Sage from Student
where Sdept='CS' and Sage between 19 and 21 ;

– 4． 找出最大年龄；

select max(Sage) 最大年龄 from Student ;

– 5． 找出“CS”系年龄最大的学生，显示其学号、姓名；

select Sno,Sname from Student
where Sdept='CS' and Sage= (
    select max(Sage) from Student where sdept='CS');

– 6． 查询各系年龄最大的学生，显示其学号、姓名；************

select sno,sname,sdept
from student a
where sage = (
    select max(sage) from student b
    where a.sdept=b.sdept );

– 7． 统计“CS”系学生的人数；

select count(*) 计算机系人数 from Student
where Sdept='CS';

– 8． 统计各系学生的人数，结果按升序排列；

select Sdept,count(*) cnt
from Student
group by Sdept
order by cnt;

– 9．按系统计各系学生的平均年龄，结果按降序排列；

select Sdept,avg(Sage) age
from Student
group by Sdept
order by age desc;

– 10． 查询每门课程的课程名；

select Cname from Course;

– 11． 查询无先修课的课程的课程名和学分数；
–先将无字符的换成NULL

update course set cpno=null where cpno='';

select Cname,Ccredit
from Course
where Cpno is null;

– 12． 统计无先修课的课程的学分总数；

select sum(Ccredit) 
from Course
where Cpno is null ;

– 13． 统计每位学生选修课程的门数、学分及其平均成绩；

select SC.sno,count(SC.Cno),sum(Ccredit),avg(Grade)
from SC,Course
where SC.cno=Course.cno
group by Sno;

– 14． 查询选修了1号课程和2号课程的学生学号和姓名；

– 方法一

select distinct Student.Sno,Sname
from Student,SC
where SC.Sno=Student.Sno and (Cno='1' or Cno='2' );

– 方法二

select distinct Student.Sno,Sname
from Student,SC
where SC.Sno=Student.Sno and Cno in('1','2');

– 15． 查询选修了课程名为“数据库”且成绩在60分以下的学生的学号、姓名和成绩；
–增加一行低于60分的。

insert into SC values('200215125','1',58);

select Student.Sno,Sname,Grade
from SC,Course,Student
where SC.Sno=Student.Sno and SC.Cno=Course.Cno and Cname='数据库' and grade<60 ;

–删除增加的行

delete from SC 
where Sno='200215125' and Cno='1' ;

– 16． 查询每位学生选修了课程的学生信息（显示：学号，姓名，课程号，课程名，成绩）；

select Student.Sno,Sname,Course.Cno,Cname,grade
from Student,Course,SC
where Student.Sno=SC.Sno and Course.Cno=SC.Cno;

– 17. 查询没有选修课程的学生的基本信息；*************
– 方法一

select Student.* from Student
where Sno not in (
    select Sno from SC );

– 方法二

select Student.* from student
where not exists (
    select * from SC
    where sno=Student.sno );

– 18． 查询选修了3门以上课程的学生学号；

select Sno from SC
group by Sno
having count(Sno)> 2;

– 19． 查询选修课程成绩至少有一门在90分以上的学生学号；

select Sno from SC
group by Sno
having max(Grade)>90 ;

– 20． 查询选修课程成绩均在80分以上的学生学号；

select Sno from SC
group by Sno
having min(Grade)>80 ;

– 21． 查询选修课程平均成绩在85分以上的学生学号；

select Sno from SC
group by Sno
having avg(Grade)>85 ;

– 22． 找出平均成绩在85分以上的学生，结果按系分组，并按平均成绩的升序排列；

select Student.Sno,avg(grade) avg_grade,Sdept
from SC,Student
where SC.Sno=Student.Sno
group by Student.Sno,Sdept
having avg(grade)>85
order by avg(grade);  -- 注意在order by 后面可以用聚集函数

实验三

– (5)将sc表中插入一个学生的选课信息，学号‘201215123‘，课程号’5‘，成绩待定。

insert into sc(Sno,Cno) values ('201215128','5');

– (6） 将数学系全体学生的成绩置零。

update sc set Grade=0 
	where 'ma'=(select sdept from student 
		where sc.Sno=student.sno);

– (7） 删除数学系所有学生的选课记录。

delete from sc
	where 'ma'=(select sdept from student
		where student.sno=sc.Sno);

– (8) 将学号为201215128的学生的学号修改为201215188

alter table sc drop FK__sc__Sno__08EA5793 ;

drop table sc;

update  student set Sno='201215188'
	where Sno='201215123' ;

– (9) 把平均成绩大于80分的男同学的学号和平均成绩存入另一个表“s_grade(sno,avg_grade)"中；

create table s_grade
(sno char(10),
avg_grade smallint);

insert into s_grade(sno,avg_grade) 
	(select sc.sno,AVG(grade) from sc,student 
		WHERE Ssex='男' AND SC.SNO=STUDENT.SNO
		Group by sc.sno having AVG(grade)>80);

–（10） 把选修了课程名为“数据结构”的学生的各门课成绩提高10%；

UPDATE sc SET grade=grade*1.1
	WHERE sno IN (SELECT sno FROM sc b where b.cno=(select cno from course where Cname='数据结构' ));

–(11) 把选修了“2”号课程，且成绩低于该门课程的平均成绩的学生的成绩提高5%；

UPDATE sc SET grade= grade * 1.05
	WHERE cno= '2' AND grade < ( SELECT AVG(grade) FROM sc WHERE cno ='2' ) ;

select * from sc;

–（12）把选修了“2”号课程，且成绩低于该门课程的平均成绩的学生成绩删除掉；

DELETE sc 
	WHERE cno='2' AND grade < ( SELECT AVG(grade) FROM sc WHERE cno ='2' ) ;

use xsgl;

实验四：<视图>

–（1）建立信息系学生的视图v_is

create view v_is  
	as select * from student where sdept='is';

select * from v_is;

–（2）将student,course,sc表中的学生的学号、姓名、课程号、课程名、成绩定义为视图v_s_c_g

create view v_s_c_g(Sno,Sname,Cno,Cname,grade) 
	as select student.Sno,Sname,sc.Cno,Cname,grade from student,course,sc
		where student.Sno=sc.Sno and sc.Cno=course.Cno;

select * from v_s_c_g;

– (3)将各系学生人数，平均年龄定义为视图"V NUM AVG"I

create view v_num_svg as
	select sdept,COUNT(*) rs,avg(sage) avg_g from student group by sdept;

 select * from v_num_svg;

 select * from student;

–(4)定义一个反映学生出生年份的视图"V YEAR ,

create view V_YEAR as
	select sno,sname,2019-sage csny from student;

select * from V_YEAR;

– (5)将各位学生选修课程的门数及平均成绩定义为视图“V AVG s G"

create view v_avg_D_G as
	select sno,count(*) ms,avg(grade) pjcj from sc
		group by sno;

select * from v_avg_D_G;

– (6)将各门课程的选修人数及平均或绩定义为视围"V AVGC G";

create view v_avgc_g as
	select cno,count(*) rs,avg(grade) pjcj from sc
		group by cno;

select * from v_avgc_g;

– (7)创建平均成绩为90分以上的学生学号.姓名和成绩的视图;

create view st_1 as
	select sno,sname,grade from v_s_c_g
		where sno in (select sno from v_s_c_g
	 group by sno having avg(grade)>90);

 select * from st_1;

– (8)更新视图"V 1S",将学号为“20161513*的学生姓名更改为"S1 MMM"

update v_is set Sname='s1_mmm'where Sno='201215125' ;

select * from v_is;

– (9)通过视图v_is,将增加一个学生记录（’s12’,‘YAN XI’,‘男’,19,‘is’),并查询结果。

insert into v_is values( 's12','YAN XI','男',19,'is');

select * from student;

– （10）通过视图’v_is’，删除学号为‘S12’和’s13’的学生信息，并查询结果。

delete from v_is where Sno='s12';

select * from v_is;