SQL练习题
最近又教了一下SQL的写法,之前在写IDEA的时候我们用的一直都是My SQL,现在教的是SQL server其实感觉两者写法区别不大,只是有些关键字不存在,不适用,嗯...比如:LIMIT子句示例和 OFFSET子句示例,这个方法可以在My SQL 使用,但是在SQL server 却找不到该方法也给到了一些练习来做,就是一些平常的SQL 我写了一些答案,如果有不对的请告诉我1、查询“c001”
最近又教了一下SQL的写法,之前在写IDEA的时候我们用的一直都是My SQL,现在教的是SQL server
其实感觉两者写法区别不大,只是有些关键字不存在,不适用,嗯...比如: LIMIT子句示例 和 OFFSET 子句示例,这个方法可以在My SQL 使用,但是在SQL server 却找不到该方法
也给到了一些练习来做,就是一些平常的SQL 我写了一些答案,如果有不对的请告诉我
1、查询“c001”课程比“c002”课程成绩高的所有学生的学号;
select a.sno,a.sname from student a
inner join sc b on a.sno=b.sno
inner join sc c on a.sno=c.sno
Where (b.cno='c001' and c.cno='c002') and b.score>c.score
2、查询平均成绩大于60 分的同学的学号和平均成绩;
select sno,avg(score) from sc group by sno having avg(score)>60
3、查询所有同学的学号、姓名、选课数、总成绩;
select student.sno,student.sname,count(sc.cno)AS 选修课,sum(sc.score)AS 总成绩
from student left join sc on student.sno=sc.sno
group by student.sname,student.sno
4、查询姓“刘”的老师的个数;
select COUNT(tname) as 刘姓老师个数 from teacher where tname like '刘_'
select COUNT(tname) as 刘姓老师个数 from teacher where tname like '刘%'
5、查询没学过“谌燕”老师课的同学的学号、姓名;
select student.sname,student.sno from student
EXCEPT
select student.sname,student.sno from sc join student on sc.sno = student.sno inner join course on sc.cno=course.cno where course.cno in
(select course.cno from course left join teacher on course.tno=teacher.tno where teacher.tname ='谌燕')
group by student.sname,student.sno
6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名;
select a.sno,a.sname from student a
inner join sc b on a.sno=b.sno
inner join sc c on a.sno=c.sno
where (b.cno='c001' and c.cno='c002')
7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名;
select student.sno,student.sname from student where not exists
(select course.cno from course where course.tno=(select teacher.tno from teacher where teacher.tname ='谌燕')
and course.cno not in (select sc.cno from sc where sc.sno=student.sno))
8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名;
select a.sno,a.sname from student a
inner join sc b on a.sno=b.sno
inner join sc c on a.sno=c.sno
Where (b.cno='c002' and c.cno='c001') and b.score<c.score
9、查询所有课程成绩小于60 分的同学的学号、姓名;
select distinct student.sno,student.sname from student left join sc on student.sno=sc.sno where sc.score<60
select * from sc
10、查询没有学全所有课的同学的学号、姓名;
select a.sno,a.sname from student a
group by a.sno,a.sname
having (select count(1) from sc where sc.sno=a.sno)!=(select count(1) from course)
11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名;
select distinct student.sno,student.sname from student
inner join sc on student.sno=sc.sno
where sc.cno in(select sc.cno from student inner join sc on student.sno=sc.sno where student.sno='s001')
学生表 student
sno | sname | sage | ssex |
s001 | 张三 | 23 | 男 |
s002 | 李四 | 23 | 男 |
s003 | 吴鹏 | 25 | 男 |
s004 | 琴沁 | 20 | 女 |
s005 | 王丽 | 20 | 女 |
s006 | 李波 | 21 | 男 |
s007 | 刘玉 | 21 | 男 |
s008 | 萧蓉 | 21 | 女 |
s009 | 陈萧晓 | 23 | 女 |
s010 | 陈美 | 22 | 女 |
教师表 teacher
tno | tname |
t001 | 刘阳 |
t002 | 谌燕 |
t003 | 胡明星 |
课程表 course
cno | cname | tno |
c001 | J2SE | t002 |
c002 | Java Web | t002 |
c003 | SSH | t001 |
c004 | Oracle | t001 |
c005 | SQL SERVER 2005 | t003 |
c006 | C# | t003 |
c007 | JavaScript | t002 |
c008 | DIV+CSS | t001 |
c009 | PHP | t003 |
c010 | EJB3.0 | t002 |
成绩表 sc
sno | cno | score |
s001 | c001 | 78.9 |
s002 | c001 | 80.9 |
s003 | c001 | 81.9 |
s004 | c001 | 60.9 |
s001 | c002 | 82.9 |
s002 | c002 | 72.9 |
s003 | c002 | 81.9 |
s001 | c003 | 59 |
更多推荐
所有评论(0)