SQLserver查询练习

这是在练习中使用的表:

--1.select dno from dept where dname=‘数学系‘

select sno,sname from student where dno=(select dno from dept where dname=‘数学系‘);

--2.求选修了课程的学生的学号

select distinct sno from sc ;

--3.求选修了01号课程学生的学号和成绩 ,查询结果按成绩升序排序,成绩相同按学号的降序排序
select sno,grade from sc where cno=‘01 ‘order by grade,sno desc

--4.求选修了01号课程且成绩在80-90之间的学生的学号和成绩,并将成绩乘以0.8输出.

select sno,grade=grade*0.8 from sc where cno=‘01‘ and grade between 80 and 90

--5.查询数学系或计算机系姓张的学生的信息
select * from student where dno in (select dno
from dept where dname in (‘数学系‘,‘计算机系‘));

--6.查看选修了课程,但没有成绩学生的信息.
select * from student where sno in (select sno
from sc where grade is null)

--7.查询学生的学号,成绩,姓名,课程名
select sc.sno,sname,cname,grade from sc,student,course
where sc.sno=student.sno and sc.cno=course.cno

--8.分别实现学生和系的交叉连接,内连接,外连接
--内连接:
select * from student ,dept where student.dno=dept.dno;
--外连接:
select * from student left outer join dept on (student.dno=dept.dno);

--9.选修了高等数学学生的学号和姓名
select sname ,sno from student where sno in (select sno from sc
where cno =(select cno from course
where cname=‘数学‘));

--10.求01号课程的成绩和高于周立波的学生的成绩和学号

select sno,grade from sc where grade > (select grade from
sc where sno=(select sno from student
where sname=‘周立波‘));

--11.求其他系的学生年龄小于计算机系最大年龄的学生

select max(sage) from student where dno=(select dno from dept
where dname=‘计算机系‘);

select sage from student where dno=(select dno from dept
where dname=‘计算机系‘);

select * from student where sage < (select max(sage) from student
where dno=(select dno from dept
where dname=‘计算机系‘)) and dno !=(select dno from dept
where dname=‘计算机系‘);

--12.求其他系中比计算机系中年龄都小的学生

select * from student where sage < all(select sage from student
where dno=(select dno from dept
where dname=‘计算机系‘)) and dno !=(select dno from dept
where dname=‘计算机系‘);

--13.求选修了02号课程学生的姓名
select sname from student where sno in(select sno from sc where cno=‘02‘)
--14.求没有选修01号课程学生的姓名
select sname from student where sno not in (select sno from sc where cno=‘02‘)

--15.查询选修了全部课程的学生的姓名
select sname from student where not exists(select * from course
where not exists (select * from sc
where sc.sno=student.sno and course.cno=sc.cno));

--16.求选修了学号为‘2014005‘学生所选修全部课程的学生的学号和姓名.
select sname ,sno from student where not exists(select * from sc
where not exists (select * from
(select * from sc where sno=‘2014005‘) as newtb
where
newtb.sno=student.sno and sc.cno=newtb.cno));

select distinct sno from sc s1
where not exists
(select * from sc s2 where s2.sno=‘2014005‘and not exists
(select * from sc s3 where s1.sno=s3.sno and s2.cno=s3.cno)
)

select sname,sno from student where sno in(select distinct sno from sc s1
where not exists
(select * from sc s2 where s2.sno=‘2014005‘and not exists
(select * from sc s3 where s1.sno=s3.sno and s2.cno=s3.cno)
))

--注意表之间的顺序
select sname ,sno from student where not exists(select * from
(select cno from sc where sno=‘2014005‘) as newtb
where not exists (select * from sc
where sc.sno=student.sno and sc.cno=newtb.cno));