sql 最基本联合查询
来源:互联网 发布:js字符串拼接换行符 编辑:程序博客网 时间:2024/04/29 15:17
use tt
select * from student
select * from score
select * from course
select sid,score from score where cid='1'
select sid ,score from score where cid='2'
--查询1课程比2课程成绩高的所有学生学号
select a.sid from (select sid,score from score where cid='1') a,(select sid ,score from score where cid='2') b where a.score>b.score and a.sid=b.sid
---查询平均成绩大于60分的学生学号
select * from score
select sid 学号,avg(score)as average from score group by sid
having avg(score)>60
----------------------
--查询学生学号,姓名,总选课数,总成绩
select student.id 学号,Sname 姓名,count(score.cid) as 选课总数,sum(score) 总成绩
from student
inner join score
on student.id=score.sid
group by
student.id,Sname
----------------
select student.id,student.sname,count(score.cid),sum(score) from student
left join score
on student.id=score.sid
group by student.id,Sname
--查询姓李的老师有多少个,,
select count(distinct(tname)) from teacher where tname like '李%' as a
--------------------
--查询没有选修过叶平老师课程的学生学号和姓名
select student.id,student.Sname from student where
student.id not in(select distinct(score.sid)
from score,Course,Teacher
where score.cid=Course.id and Teacher.id=Course.tid and Teacher.Tname='叶平')
-----------------------------------------
--查询既选修了1号课程,又选修了2号课程的同学
select Student.id,Student.Sname
from Student,Score where Student.id=score.sid and
score.cid='001'and
exists( Select * from score as SC_2 where SC_2.sid=score.sid and SC_2.cid='002');
-----------------------
--选修了叶平老师所有课程的同学学号,姓名
-----------------------
select student.id,Sname from Student
where student.id in
(select Sid from SCore ,Course ,Teacher
where SCore.Cid=Course.id and Teacher.id=Course.tid
and Teacher.Tname='叶平' group by sid
having count(SCore.cid)=
(select count(Course.id) from Course,Teacher
where Teacher.id=Course.Tid and Tname='叶平'));
------------------------------------
--查询所有课程成绩小于60分的同学的学号、姓名;
--------------------------------------
select student.id,sname from student
where student.id not in(
select student.id from student
inner join
score
on score.sid=student.id
where score>60)
------------------------------------------
--查询所有课程成绩小于60分的同学的学号、姓名;
select Student.id,Sname
from Student
where Student.id not in (select Student.id from Student,SCore
where Student.id=SCore.Sid and score>60);
-------------------------------------
--查询没有学全所有课程的学生
select student.id,sname
from student ,score
where student.id=score.sid
group by student.id,sname
having count(score.cid)<(select count(*) from course)
------------------------
--至少有一门课与学号1的学生选修的课程一样的其他同学的学号,姓名
-----------------------
select distinct score.sid,sname
from student,score
where student.id=score.sid
and score.cid in (select cid from score where sid='1' )
--查询学习两门课程的人数之差
select distinct
(select count(sid) as c from score where cid='1')-
(select count(sid) as d from score where cid='3') as betweens from score
--
--选修2课程也选修1课程的同学序号,姓名
select student.id ,sname from student where id in(
select a.sid from (select sid from score where cid='1') a,
(select sid from score where cid='2') b
where a.sid=b.sid)
--选修2课程也选修1课程的同学序号,姓名
select student.id,sname from student,score
where student.id=score.sid and score.cid='1'
and exists(select * from score as sc where sc.sid=score.sid and sc.cid='2')
--选修了叶平老师所有课程的学生学号,姓名
select student.id,sname from student
where id in (select sid from score,course,teacher
where score.cid=course.id and course.tid=teacher.id and
teacher.tname='叶平' group by score.sid having count(score.cid)
=(select count(course.id) from course ,teacher
where course.tid=teacher.id and teacher.tname='叶平'))
--选修2号课程比一号课程低的同学学号,姓名
select student.id,sname from student
where id in (select a.sid from (select * from score where cid='2') a,
(select * from score where cid='1') b
where a.sid=b.sid and a.score<b.score)
--所有课程成绩小于60分的同学
select id,sname from student
where id not in(select id from student ,score
where score.sid=student.id and score.SCORE>60)
--查询没有学全所有课程的同学
select student.id,student.sname from student,score
where student.id=score.sid group by student.id,student.sname
having count(cid)<(select count(*) from course)
--选修了至少一门1号学员课程的其他同学
select distinct student.id,sname
from student,score
where student.id=score.sid
and score.cid in (select cid from score where sid='1' )
--
select * from score
select * from course
select * from teacher
select * from score where id
------叶平老师教受的没门课程的平均成绩
select cid ,avg(score) from teacher,score ,course where teacher.id=course.tid
and course.id=score.cid and teacher.tname='叶平'
group by cid
having cid=7
--case when 用法对其中某一列进行评价或标识
select sid ,cid,score=(case when (score<60) then '不及格'
when (score <70 and score >60) then '良好'
when (score <80 and score >70) then '优越'
when (score <90 and score >80) then '棒'
else '胜利' end),
(select count(*) from score) as totals from score
---------------
--查询和2号学院学习课程完全一样的学生
----行列转换sql-----------
http://blog.csdn.net/xys_777/article/details/5685953
http://blog.sina.com.cn/s/blog_4cca663f0100abfv.html
- sql 最基本联合查询
- 关于sql联合查询
- Sql联合查询
- Sql联合查询
- Sql联合查询
- Sql联合查询
- sql 联合查询
- SQL联合查询
- sql联合查询
- sql联合查询详解
- SQL联合查询简介
- SQL 联合查询
- sql简单联合查询
- SQL中的联合查询
- sql联合查询
- SQL Server联合查询
- SQL Server联合查询
- SQL联合查询
- Java中ArrayList和LinkedList区 别
- C中static的作用
- 【整理】输入导航功能-查询与拼音首字母的结合以提高用户体验
- 整合UCenter+MediaWiki+Discuz实现单点登录
- 状态机的c语言编程
- sql 最基本联合查询
- android jni 包裹文件(jni wrapper) 以 speex 库为例
- 【转载】推荐两个界面原型设计工具--GUIDesignStudio 和 Mockups For Desktop
- BTrace系列之四:破解案例
- 总结
- 使用lamda表达式定义或非逻辑
- 中国式危机公关9加1策略(第十三章 建立系统实用的危机管理机制)
- 学习小记,window.open(url,....)中的url带参数时,=后面不要带空格,否则空格也将被当作参数传递
- 一个用 dl dt dd 做的表格