欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQLserver数据库相关练习

程序员文章站 2022-06-30 08:42:41
本篇博客用来记录自己在做sql server练习的时候,所做过的题目,遇到的问题及总结。 题目及解答: 本练习所涉及的:stumanage 1、写出下面sql语句实现的功能 (1)select co...

本篇博客用来记录自己在做sql server练习的时候,所做过的题目,遇到的问题及总结。

题目及解答:

本练习所涉及的:stumanage

1、写出下面sql语句实现的功能

(1)select count(*) as 信管专业女学生人数

from student

where mno=‘100165’ and ssex=‘女’

统计了出信管专业(100165)女学生总人数。

(2)select distinct substring(sname,1,1)

from student

查询出全部学生的姓。

(3)select sno,sname,ssex,mno

from student

where mno in (‘100165’,’201148’,’100838’)

查询出mno是‘100165’或’201148’或’100838’的学生达到学号,姓名,性别和专业号。

(4)create view v_1

as

select ccno,mark

from student_course

where sno =‘100212201’ and mark >any

(select mark from student_course

where sno =‘100212208’ )

创建一个视图v_1,该视图要求,学号为100212201的同学,

大于学号为100212208的同学所有科目的平均分的科目,

列出课程号和成绩。视图包含课程号和成绩两个字段。

(5)select distinct s.sno,sname,dname

from student as s,department as d, student_course as sc ,major as m

where s.mno=m.mno and d.dno=m.dno and s.sno=sc.sno and mark <60

找出课程成绩小于60分的同学,列出他们的学号,姓名和院系名(dname)。

(6)select sno,sname,mname

from student as s,major as m

where s.mno=m.mno and s.sno in

(select distinct sno

from student_course

where mark <60 )

找出课程成绩小于60分的同学,列出他们的学号,姓名和专业名。

2、根据下面的要求,写出相应的查询语句

(1)查询所有男同学的选课情况,要求列出学号、姓名、开课号、分数。

select student.sno,student.sname,ccno,mark 
from student_course,student 
where student_course.sno =student.sno 
and student.ssex='男';

(2)创建一个视图显示所有学生的总成绩,最高成绩,要求列出学号、总成绩和最高成绩。

create view student_mark_view 
as select sno,sum(mark) as 总成绩,max(mark) as 最高成绩 from student_course group by sno;

(3)查询出姓张的学生或者姓名中带有“秋”的学生

select * from student 
where sname like '张%' or sname like '%秋%';

(4)查询出每门课程的平均分、最低分、最高分

select ccno,avg(mark) as 平均分 ,min(mark) as 最低分, max(mark) as 最高分 from student_course group by ccno;

(5)查询出平均分大于80分,且至少选修了2门课程的学生学号。

select sno from student_course group by sno having avg(mark)> 80 
and count(ccno)>=2; 

(6)求选修课程号为’010104’且成绩在90以上的学生学号、姓名和成绩

select student.sno,sname,student_course.mark from student,student_course 
where student.sno = student_course.sno
and ccno='010104' and mark>90; 

(7)创建一个视图显示每一门课程的间接先行课(即先行课的先行课)

select a.cno,a.cname,b.cpno as '间接先行课'
from course a,course b 
where a.cpno = b.cno;

(8)求高等数学课程的成绩高于刘晨的学生学号和成绩

select student.sno,student.sname,student_course.mark 
from student,student_course,course,course_class
where student.sno=student_course.sno
and student_course.ccno = course_class.ccno
and course_class.cno = course.cno
and  course.cname='高等数学' 
and  student_course.mark>
(select student_course.mark from student,student_course 
where student.sno=student_course.sno 
and student.sname='刘晨' 
and student_course.ccno = course_class.ccno);

(9)求其他系中比计算机系某一学生年龄小的学生(即年龄小于计算机系年龄最大者的学生)

select * from student,department,major 
where student.mno=major.mno 
and major.dno = department.dno
and department.dname!='计算机系'
and datediff(yy,student.sbirth,getdate())<
(select max(datediff(yy,student.sbirth,getdate())) from student 
where student.mno = major.mno
and major.dno = department.dno
and department.dname!='计算机系'
);

(10)查询同时选修了“数据库”“金融学”“统计学”三门课程的学生姓名。(要求使用exists)

create view three_course_view 
as select  student.sname,course.cname from student,student_course,course_class,course
where student.sno = student_course.sno
and student_course.ccno = course_class.ccno 
and course_class.cno = course.cno;
select sname from three_course_view a where cname in('数据库','金融学','统计学')
and exists(select 1 from three_course_view where cname in('数据库','金融学','统计学') 
and sname=a.sname and cname<>a.cname) group by sname having count(cname)>=3 ;

(11)查询同时选修了“数据库”“金融学”“统计学”三门课程的学生姓名。(要求不能使用exists)

create view three_course_view_noexists 
as select  student.sname,course.cname from student,student_course,course_class,course
where student.sno = student_course.sno
and student_course.ccno = course_class.ccno 
and course_class.cno = course.cno;
select  c1.sname,c1.cname,c2.cname
from three_course_view_noexists c1,three_course_view_noexists c2,three_course_view_noexists c3
where c1.sname=c2.sname
and c2.sname = c3.sname 
and c1.cname ='数据库' 
and c2.cname='金融学'
and c3.cname='统计学';

3、思考题

(1)如何求出“金融学”成绩排名第5到第10之间的学生姓名。

create view  mark_rank_view as
select top 10 student.sname,mark,'金融学' as '科目' from student,student_course,course_class,course 
where student.sno = student_course.sno
and student_course.ccno = course_class.ccno 
and course_class.cno = course.cno
and course.cname='金融学' 
order by mark desc ;
select top 10 sname from mark_rank_view where sname not in (select top 4 sname from mark_rank_view);

(2)假设有两个结构完全相同的表,一个是运营数据表a,一个是历史数据表b,由于操作失误导致两个表中出现了部分完全相同的数据,请将完全相同的数据从a中删除?

根据题目意思,假设题中所指完全相等是指一行内的数据完全相同。

创建新一行数据,该行数据由两个表该行中所有字段连接成,

用where 判断两个表该新字段是否相等,如果相等,则根据表的id,删除该行。

总结

本次练习是有一定有难度,虽然自己以前有学过数据库,但是有些题目还是想了很多,说明自己对于数据库的操作还不是很熟悉,特别是多表连接查询这块内容还欠些经验。在做实验过程中,遇到过2道比较难的题目,就是2.10和2.11,因为它涉及到4张表查询,在连接起来的时候sql语句,显得很长,而且它们之间的逻辑关系变得很难理清楚,因为在最后做这两道题时我化了3,4个小时去尝试各种查询去执行,但是都不成功。最终,我先将四张表某些字段连接成一个视图,然后再用sql语句去该视图查询,才得到了题目所需要的信息。

经过这次练习,真的学到了很多,之前一直以为自己对于数据库sql语句已经很熟练了,以为这次实验应该会很简单,但是没想到却花了近乎一天的时间去完成该实验,太过于自负了,今后会好好学习sql相关知识,认真思考一下sql语句怎么变形。