设为首页 加入收藏

TOP

SQLserver数据库相关练习(一)
2018-04-17 09:08:06 】 浏览:56
Tags:SQLserver 数据库 相关 练习

本篇博客用来记录自己在做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 FRO
编程开发网
首页 上一页 1 2 下一页 尾页 1/2/2
】【打印繁体】【投稿】【收藏】 【推荐】【举报】【评论】 【关闭】 【返回顶部
上一篇mysql错误:ERROR1045(28000):Acc.. 下一篇SQLserver练习题之数据库sql文件

评论

帐  号: 密码: (新用户注册)
验 证 码:
表  情:
内  容:

最新文章

热门文章

C 语言

C++基础

windows编程基础

linux编程基础

C/C++面试题目