SQLserver查詢練習

標籤：des   blog   http   使用   sp   on   2014   log   bs   

這是在練習中使用的表:

--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));

SQLserver查詢練習