3.sql

sql常用查询 本人为在校生 内容粗浅 但涉及查询均为典型情况 代码自写 编译通过

use northwind;

select *
from Orders;

--1. 查询Ｎorthwind数据库中orders表的ShipCountry列不以A,B,C,D,E,F开始且最后一个字母是'a'的OrderID、CustomerID和ShipCountry的信息。
select OrderID,CustomerID,ShipCountry
from Orders
where Shipcountry like '%a' and Shipcountry not like 'A%' and Shipcountry not like 'B%' and Shipcountry not like 'c%' and Shipcountry not like 'd%' and Shipcountry not like 'e%' and Shipcountry not like 'f%';

use students;

--2. 统计每个学生的选课门数，并按选课门数的递增顺序显示结果。
select sno,count(cno)
from sc$
group by sno order by count(cno) asc;

select *
from sc$
order by grade desc

--3. 列出总成绩超过200分的学生，要求列出学号、总成绩。
select sno,sum(grade)
from sc$
group by sno having sum(grade)>200;

--4. 查询成绩80分以上的学生的姓名、课程号和成绩，并将结果按成绩的降序排列。
select sname,cno,grade
from sc$,student$
where grade>=80 and sc$.sno=student$.sno
order by grade desc;

--5. 查询学生的选课情况，要求列出每位学生的选课情况（包括未选课的学生），并列出学生的学号、姓名、课程号和考试成绩。
select sc$.sno,sname,cno,grade
from sc$,student$
where sc$.sno=student$.sno

select sc$.sno,sname,cno,grade
from sc$ inner join student$ on sc$.sno=student$.sno

select *
from sc$,student$
where sc$.sno=student$.sno

select a.*,s.*
from sc$ as a inner join student$ as s on a.sno=s.sno

--6. 列出“VB”课程考试成绩前三名的学生的学号、姓名、所在系和考试成绩。
select top 3 with ties sc$.sno,sname sdept,grade
from sc$,course$,student$
where cname='vb' and sc$.cno=course$.cno and sc$.sno=student$.sno
order by grade desc

select top 3 with ties sc$.sno,sname sdept,grade
from sc$ left outer join course$ on (cno) left outer join student$ on (sno)
where cname='vb'
order by grade

--7. 查询哪些课程没有人选，要求列出课程号和课程名。
select course$.cno,cname
from sc$ right outer join course$ on sc$.cno=course$.cno
where sc$.cno is null

select *
from sc$
order by sno

select *
from course$

select *
from student$

--8．查询计算机系学生考试成绩高于全体学生的总平均成绩的学生的姓名、考试的课程名和考试成绩。
select sname,cname,grade
from sc$,course$,student$
where  sdept='计算机系' and grade>
(select avg(grade)
from sc$)
and sc$.cno=course$.cno and sc$.sno=student$.sno
order by grade desc

--9．查询VB考试成绩最低的学生的姓名、所在系和VB成绩。
select sname,sdept,grade
from sc$,student$
where sc$.sno=student$.sno
group by sname,sdept,grade
having grade=
(select min(grade)
from sc$,course$
where cname='vb' and sc$.cno=course$.cno)

select sname,sdept,grade
from sc$ inner join student$ on sc$.sno=student$.sno
group by sname,sdept,grade
having grade=
(select min(grade)
from sc$,course$
where cname='vb' and sc$.cno=course$.cno)

select sname,sdept,grade
from sc$ inner join student$ on sc$.sno=student$.sno inner join course$ on sc$.cno=course$.cno
where cname='vb' and grade=
(select min(grade)
from sc$ join course$ on sc$.cno=course$.cno
where cname='vb')

select sname,sdept,grade
from sc$,student$,course$
where cname='vb' and grade=
(select min(grade)
from sc$ join course$ on sc$.cno=course$.cno
where cname='vb')
and sc$.cno=course$.cno and sc$.sno=student$.sno

--10. 查询VB课程考试成绩高于VB平均成绩的学生 的姓名。
select sname
from student$
where sno in
(select sno
from sc$,course$
where cname='vb' and sc$.cno=course$.cno
group by sno,grade
having grade>
(select avg(grade)
from sc$,course$
where cname='vb' and sc$.cno=course$.cno)
)

--11. 查询平均成绩大于80分的课程的名字和学分。
select cname,ccredit
from course$,sc$
where course$.cno=sc$.cno
group by course$.cno,cname,ccredit
having avg(grade)>80

--12. 统计每门课程的选课人数，包括有人选的课程和没有人选的课程，列出课程号
，选课人数及选课情况，其中选课情况为：如果此门课程的选课人数超过100人，则
显示“人多”；如果此门课程的选课人数在40～100，则显示“一般”；如果此门课
程的选课人数在1～40，则显示“人少”；如果此门课程没有人选，则显示“无人选”。
select course$.cno,count(sno) as num,case
when count(sno)>100 then 'too many'
when count(sno)>40 then 'nomal'
when count(sno)>0  then 'less'
else 'none'
end as circumstance
from course$ left outer join sc$ on course$.cno=sc$.cno
group by course$.cno order by count(sno) desc

--13．查询至少选修了“9512102”号学生选修的全部课程的学生，列出学号和所选的课程号。
select sno,cno
from sc$
where cno in
(select cno
from sc$
where sno='9512102')

--14．查询至少选修了第2学期开设的全部课程的学生的姓名和所在系。
select sname,sdept
from student$ inner join sc$ on student$.sno=sc$.sno
where cno in
(select cno
from course$
where semester=2)

--15．修改“VB”课程的考试成绩，如果是计算机系的学生，则增加4分；如果是信息系的学生则增加8分，其他系的学生增加10分。
select *
from sc$
--where sno in (9512101,9521102,9531101)
--where cno='c02'
order by cno

update sc$ set grade=case
when student$.sdept='计算机系' then grade+4
when student$.sdept='信息系' then grade+8
else grade+10
end
from sc$ inner join course$ on sc$.cno=course$.cno inner join student$ on sc$.sno=student$.sno
where cname='vb'

select *
from course$

select *
from sc$
--where sno in (9512101,9521102,9531101)
where cno='c02'
order by cno

select *
from student$

--16. 删除修课成绩小于50分的学生的修课记录。
delete from sc$
where grade<50

--17. 删除信息系修课成绩小于50分的学生的修课纪录，分别用子查询和多表连接形式实现。
delete from sc$
where grade<=50 and sno in
(select sno
from student$
where sdept='信息系')

delete from sc$ from sc$ join student$ on sc$.sno=student$.sno
where grade<50 and sdept='信息系'

--18. 将所有选修了“c01”课程的学生的成绩加10分。
update sc$ set grade=grade-10
where cno='c01'

--19. 将计算机系所有选修了‘计算机文化学’课程的学生的成绩加10分，分别用子查询和多表连接形式实现。
update sc$ set grade=grade+10
where sno in
(select sno
from sc$,course$
where cname='计算机文化学' and sc$.cno=course$.cno)
and cno=
(select cno
from course$
where cname='计算机文化学')

update sc$ set grade=grade+10
from sc$ join course$ on sc$.cno=course$.cno
where cname='计算机文化学'

update sc$ set grade=grade+10
where sno in
(select sno
from sc$,course$
where cname='计算机文化学' and sc$.cno=course$.cno)

--20．删除“VB”考试成绩最低的学生的VB修课记录。
delete from sc$ from sc$ join course$ on sc$.cno=course$.cno
where cname='vb' and grade=
(select min(grade)
from sc$ join course$ on sc$.cno=course$.cno
where cname='vb')