1.创建学生数据库
create database
studentdb
2.用SQL的有关语句定义三个
表结构
create table Student(Sno
nvarchar(6),Sname
nvarchar(20),Sage
nvarchar(3),Ssex
nvarchar(5),Sdept
nvarchar(20));
create table Course(Cno
nvarchar(20),Cname
nvarchar(20),cpno
nvarchar(20),ccredit
nvarchar(20));
create table SC(Sno
nvarchar(6),Cno
nvarchar(20),Grade
nvarchar(10));
3.修改表结构
添加列--alter table
Student add Sbrith
datetime
修改数据类型--ALTER
TABLE Student ;ALTER
COLUMN Sage int
删除列--Alter table
Student drop column
Sbrith
4.建立索引--create index
sname_index on
Student(sname)
5.删除表结构--DROP TABLE
Student;DROP TABLE
Course;DROP TABLE SC; 6.插入数据-- insert into
Student
values(101,'liyi',20,'
男','电子信息工程学院
');
7.查询年龄大于25岁的女学
生的学号和姓名-- select
sno,sname from
student where
sage>25 and ssex='女
'
8.查询选修了“数据库系统概
论”课程的学生姓名-- select
sname from
student,sc,course
9.where student.sno=sc.sno
and http://www.doczj.com/doc/aca8d223af1ffc4ffe47acdc.htmlo=http://www.doczj.com/doc/aca8d223af1ffc4ffe47acdc.htmlo and
cname='数据库'
10.统计选修2号课程的人数--
select count(*) from sc
where cno='2'
11.查询平均成绩大于80分的
学生的学号-- select sno
from sc group by sno having
avg(grade)>=80;select
sname from student where
sno in(select sno from sc
group by sno having
avg(grade)>=80 )
12.统计每个系的学生人数--
select sdept,count(sno)from
student group by sdept
13.查询选修数据库课程并且
成绩不及格的学生学号和
姓名-- select
student.sno,sname from
student,sc,course where
student.sno=sc.sno and
http://www.doczj.com/doc/aca8d223af1ffc4ffe47acdc.htmlo=http://www.doczj.com/doc/aca8d223af1ffc4ffe47acdc.htmlo and
grade<60 and cname='数据
库'
14.查询每门课程先修课的学
分-- select
http://www.doczj.com/doc/aca8d223af1ffc4ffe47acdc.htmlo,http://www.doczj.com/doc/aca8d223af1ffc4ffe47acdc.htmlredit;from
course first,course second;
where
first.cpno=http://www.doczj.com/doc/aca8d223af1ffc4ffe47acdc.htmlo;
15.查询成绩在60到80之间的
所有记录-- select * from sc
where grade between 60 and
80
16.查询成绩为85,86或88的
记录-- select * from sc
where grade in( 85,86,88)
17.查询所有不姓“王”的学生
记录-- select * from student
where sname not like'王%'
18.以系别和年龄从大到小的
顺序查询Student表中的全
部记录-- select * from
student order by sdept,sage
desc
19.统计男女生分别有多少人--
select ssex,count(sno) from
student group by ssex
20.查询姓名的第二个字为
“小”字的女生信息-- select
*from student where sname
like'_小%'and ssex='女'
21.查询成绩最高的三个学生
的学号和成绩-- select top 3
sno,grade from sc order by
grade desc
22.查询学生的成绩信息,先按
学号升序排序,再按成绩降
序排序-- select sno,grade
from sc order by sno,grade
desc
23.查询至少选修了两门课的
学生的学号,选修课程数和
选修的平均分数-- select
sno,count(sno),avg(grade)
from sc group by sno having
count(sno)>=2
24.查询所有比刘晨大的学生
的学号,姓名,年龄-- select
sno,sname,sage from student
where sage>(select sage
from student where sname='
刘晨')
25.求出总分大于150的学生的
学号、姓名及总成绩-- select
sno,sname from student
where sno in(select sno from
sc group by sno having
sum(grade)>150)