SQL实验第一部分

--1．分别查询学生表和学生修课表中的全部数据。
select * from student 
select * from sc 
--2．查询计算机系及数学系的学生的姓名、年龄。
select sname, sage from student 
where sdept = '计算机系' or sdept = '数学系'
--3．查询选修了C01号课程的学生的学号和成绩。
select sno, grade from sc 
where cno = 'C01'
--4．查询成绩在70到80分之间的学生的学号、课程号和成绩。
select sno, cno, grade from sc
where grade between 70 and 80
--5．查询计算机系年龄在18到20之间且性别为“男”的学生的姓名、年龄。
select sname, sage from student
where sdept = '计算机系'
and sage between 18 and 20
and ssex = '男'
--6．查询1812101号学生的修课情况。
select * from sc
where sno = '1812101'
--7．查询C01号课程成绩最高的分数。
select top 1 with ties grade from sc
where cno = 'C01' order by grade desc
--8．查询学生都选修了哪些课程，要求列出课程号。
select s.sno, cno from student s left join sc
on s.sno = sc.sno 
--*9．查询Northwind数据库中orders表的OrderID、CustomerID和OrderDate，并将最新的定购日期(OrderDate)列在前边。
select OrderID, CustomerID, orderDate from orders
order by OrderDate desc
--*10．查询Northwind数据库中orders表的ShipCountry列以B，C，D，F开始且第三个字符为“a”的OrderID、CustomerID和ShipCountry的信息。
select OrderID, CustomerID, ShipCountry from Orders
where ShipCountry like '[BCDF]_a%'
--*11．查询Northwind数据库中orders表的ShipCountry列不以A，B，C，D，E，F开始且最后一个字母是“d”的OrderID、CustomerlD和ShipCountry的信息。
select OrderID, CustomerID, ShipCountry from Orders
where ShipCountry like '[^ABCDEF]%d'
--*12.  查询Northwind数据库中Employees（雇员）表中Region（区域）未确定的员工信息
select * from Employees 
where Region is null
--13．查询学生数据库中学生的最大年龄和最小年龄。
select MAX(Sage), MIN(Sage) FROM student
--14．查询修了c02号课程的所有学生的平均成绩、最高成绩和最低成绩。
select AVG(grade), MAX(grade), MIN(grade) FROM sc 
WHERE cno = 'C02'
--15．查询SC表的选课人数。
select COUNT(DISTINCT sno) from sc 
--16．统计每个系的学生人数。
select sdept, COUNT(sno) from student
group by sdept
--18．统计每个学生的选课门数，并按选课门数的递增顺序显示结果。
select sno, COUNT(cno) from sc
group by sno order by COUNT(cno) 
--19. 查询哪些课程尚未考试。
select cno from sc
group by cno 
having COUNT(grade)=0
--20．查询姓名中含有“小”字的学生信息。
select * from student
where sname like '%[小]%'
--21. 查询总成绩超过200分的学生信息，要求列出学号、总成绩。
select sno, SUM(grade) from sc 
group by sno
HAVING SUM(grade) > 200
--22. 查询选课在三门(含三门)以上且各门课程成绩均及格的学生信息，按平均成绩从高到低的顺序输出学号及平均成绩。
SELECT sno, AVG(grade) from sc
group by sno
having COUNT(cno) >= 3
and sno not in (select sno from sc where grade < 60)

--23．统计各系的修课的学生总数和考试的平均成绩。*/
   Select sdept,count(distinct sc.sno) as xszs, avg(grade) as pjcj from sc join student 
   On sc.sno=student.sno group by sdept
--24．查询选课门数超过2门（不含缺考和未开考课程）的学生的姓名、平均成绩和选课门数。
   Select sname, avg(grade) as pjcj , count(cno) as xkms  from sc join student 
   On sc.sno=student.sno  where grade is not null group by sname having count(cno)>2
--25·查询pubs数据库的titles表中每类图书的平均价格超过12．O元的书的类型(Type)、平均价格和最高价格，要求只计算有确定价格的图书的情况。
   select type as 类型,avg(price) as   平均价格,max(price) as 最高价格 from titles where price is not null
   Group by type having avg(price)>12
--26·查询pubs数据库的titles表中版税(royalty)为10的每类书的平均价格。
   select type,avg(price) as pjje  from titles where royalty=10 group by type
--27·查询pubs数据库的titles表中每类图书中图书的数目超过3本的图书的总价格。
   select type as 类型,sum(price) as 总价格 from titles group  by type having count(*)>3
--28．分别用连接和子查询两种方式查询选修了C02号课程的学生的姓名和所在系。
   select sname,sdept from student join sc on student.sno=sc.sno Where  cno='C02'
--或:
   select sname,sdept from student where sno in(select sno from sc where cno='C02')
--29.查询数学系成绩80分以上的学生的姓名、课程号和成绩，并按成绩的降序排列结果。
   select sname,cno,grade from student join sc on student.sno=sc.sno 
   Where sdept='数学系' and  grade>80 order by grade desc
--30．查询计算机系男生修了“数据库原理及应用”的学生的姓名、性别、成绩。
   select sname,ssex,grade from student join sc on student.sno=sc.sno
   Where sdept='计算机系' and ssex='男'and cno=(select cno from course where cname='数据库原理及应用')
--或:
   select sname,ssex,grade from student join sc on student.sno=sc.sno join course on sc.cno=course.cno
   Where sdept='计算机系' and ssex='男'and  cname='数据库原理及应用'
--31·查询学生的选课情况，要求列出每位学生的选课情况(包括未选课的学生)，并列出学生的学号、姓名、修课号、修课成绩。
   select student.sno,sname,cno,grade from student left join sc on student.sno=sc.sno 
--32．列出“数据库原理及应用”课程考试成绩前三名的学生的学号、姓名、所在系和考试成绩。
   select top 3 with ties student.sno,sname,sdept,grade from student,sc,course
   Where student.sno=sc.sno and sc.cno=course.cno and cname='数据库原理及应用'  
   Order by grade desc
--33．查询有考试成绩的所有学生的姓名、修课名称及考试成绩，要求将查询结果放在一张新的永久表(假设新表名为new sc)中，新表的列名分别为：姓名，课程名，成绩。
   Select sname as 姓名,cname as 课程名,grade as 成绩 into new_sc 
   from student,sc,course
   where student.sno=sc.sno and sc.cno=course.cno and grade is not null
--34．查询哪些学生的年龄相同，要求列出年龄相同的学生的姓名和年龄。
   select  distinct s1.sname,s1.sage from student s1 join student s2 on s1.sage=s2.sage and s1.sname<>s2.sname
--35．查询哪些课程没有人选，要求列出课程号和课程名。
   select cno,cname from course where cno not in (select cno from sc)
--或：
   select course.cno,cname from course left join Sc on course.cno=sc.cno where Sno is null
--36. 查询计算机系学生所选的课程名（用子查询实现）。
    select cname from course where cno in(select cno from sc where sno in
   (select sno from student where sdept='计算机系'))
--37．查询"VB”课程考试成绩前三名的学生的学号、姓名、所在系。
    select  top 3 student.sno,sname,sdept from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and cname='VB' order by grade desc
/*38．查询选了VB课程的学生的学号、姓名、所在系和成绩，并对所在系进行如下处理：
      “计算机系”：显示“CS”；  “信息系”：显示“IM”；“    数学系”：显示“MS”。*/
    Select student.sno as 学号,sname as 姓名,
    case sdept
         When '计算机系'then 'CS'
         When '信息系' then 'IM'
         When '数学系'then 'MS'
    End as 所在系 from student join sc on student.sno=sc.sno 
    Join course on sc.cno=course.cno where cname='VB'
--39． 查询同时选修了“VB”和“计算机应用”的学生姓名。
    select sname from student where sno in(select sno from sc join course on  sc.cno=course.cno where cname='VB') 
    and sno in(select sno from sc join course on sc.cno=course.cno where cname='计算机应用')
--40．查询只选“VB”而没选“数据库原理及应用”的学生姓名。
    select sname from student where sno in(select sno from sc join course on sc.cno=course.cno  where cname='VB')  
    and  sno not in(select sno from sc join course on sc.cno=course.cno  where  cname='数据库原理及应用') 
--41．查询计算机系和信息系选课门数超过3门（含3门）的学生中，考试平均成绩最高的前2名（包括并列的情况）学生的学号、选课门数和平均成绩。
    Select   top 2  with ties student.sno as 学号,count(cno) as 选课门数,avg(grade) as 平均成绩
    From student join sc on student.sno=sc.sno
    Where sdept='计算机系' or  sdept='信息系'
    Group by student.sno having count(cno)>=3
    Order by avg(grade) desc
/*42. 统计计算机系每个学生的选课门数，包括没有选课的学生。列出学号、选课门数和选课情况，对选课情况的处理为：
        如果选课门数超过4门，则为“多”；
        如果选课门数在2~4范围内，则为“一般”；
        如果选课门数少于2门，则为“少”；
        如果学生没有选课，则“未选”。
     并将查询结果按选课门数降序排序。*/
    Select S.Sno 学号,Count(SC.Cno) 选课门数,
    CASE
      When  Count(SC.Cno)>4 Then '多'
      When  Count(SC.Cno) Between 2 And 4 Then '一般'
      When  Count(SC.Cno) =1 Then '少'
      When  Count(SC.Cno)=0 Then '未选'
    End AS 选课情况
    From student as s Left join Sc On S.Sno=SC.Sno
    Where Sdept='计算机系'
    Group by S.Sno
    Order By count(SC.cno) DESC

--43．统计每个系的学生人数和平均年龄，并将查询结果及各系系主任名保存到Dept表中
--（假设：计算机系主任张三，信息系主任李四，数学系主任王五）。
select sdept as 系名, COUNT(sno) as 学生人数, AVG(sage)as 平均年龄 into Dept from student
group by sdept
alter table Dept
add depttc char(10)
update Dept set depttc = '张三'
where 系名 = '计算机系'
update Dept set depttc='李四'
where 系名 = '信息系'
update Dept set depttc='王五'
where 系名 = '数学系'
--44．查询计算机系每个学生的选课门数，包括没有选课的学生，并将结果保存到一个局部临时表#CS_Sno中，
--并查看查询结果。
select sname as 姓名, COUNT(cno) as 选课门数
into #CS_Sno
from student left join sc on student.sno = sc.sno
where sdept = '计算机系'
group by sname
--45．创建一个新表test，其结构为：(COLl，COL2，COL3)，其中：
 -- COLl：整型，允许空值，
 -- COL2：字符型，长度为10，不允许空值，
 -- COL3：字符型，长度为10，允许空值，
 -- 试写出按行插入如下数据的语句(空白处表示不提供值)，并观察插入后表中的记录值。
--    序号        COLl        COL2        COL3
  --  (1)、            B1    
  --  (2)        1        BZ        C2
  --  (3)        2        B3    
  --  (4)            B4        C4
create table test(
COL1 int,
COL2 char(10) not null,
COL3 char(10)
)
insert into test(COL2) values('B1')
insert into test values(1, 'BZ', 'C2')
insert into test values(2, 'B3', null)
insert into test(COL2, COL3) values('B4', 'C4')
--46．将计算机系成绩高于80分的学生的修课情况插入到另一张表（表名任意）中，分两种情况实现：
  --(1)在插入数据过程中建表。
  --(2)先建一个新表，然后再插入数据。
select sname as 学生姓名, cno as 课程号, grade as 成绩 into resert1
from student join sc on student.sno = sc.sno
where sdept = '计算机系' and grade > 80

create table resert2(
sname char(10),
cno char(10),
grade int
)
insert into resert2
    select sname, cno, grade
from student join sc on student.sno = sc.sno
where sdept = '计算机系' and grade > 80

--47．删除course表中学分低于2分(含2分)的课程记录。
delete from course
where ccredit <= 2
--48．删除student表中没有修课的学生记录，分别用子查询和连接查询实现。
delete from student
where sno not in(select sno from sc)

delete from student
from student left join sc on student.sno = sc.sno
where cno is null

--49．将所有选修了"C01”课程的学生的成绩加2分。
update sc set grade = grade + 2
where cno = 'C01'
--50．将计算机系所有选修了“计算机应用”课程的学生的成绩加2分，分别用子查询和连接查询实现。
update sc set grade = grade + 2
where cno in(select cno from course where cname = '计算机应用')
and sno in (select sno from student where sdept = '计算机系')

update sc set grade = grade + 2
from sc join student on sc.sno = student.sno join course on sc.cno = course.cno
where sdept='计算机系'
and cname='计算机应用'
--51. 查询哪些学生选修了所有课程，列出学生的学号，姓名。
select sno, sname from student
where sno in (select sno from sc 
group by sno
having COUNT(cno) = (select COUNT(*) from course)
)

--52．修改全体学生的VB考试成绩，修改规则如下(要求执行一次SQL命令即实现修改操作)：
--    信息系学生，成绩加3分。
--    数学系学生，成绩加5分。
--    其他系学生，成绩不变。
update sc set grade = grade +
case Sdept
when'信息系' then 3
when'数学系' then 5
else 0
end
from student s join sc on s.sno = sc.sno
join course c on sc.cno = c.cno
where cname = 'VB'
--53. 查询哪些课程被所有的学生选修，列出课程号，课程名。
select cno, cname from course
where cno in(
select cno from sc 
group by cno 
having COUNT(sno) = (select COUNT(*) from student))
--54. 查询至少选了“1812101”学生所选的全部课程的学生的学号和姓名。
select sno, sname from student where sno in(
SELECT DISTINCT X.Sno
FROM SC X 
WHERE NOT EXISTS( 
    SELECT *
    FROM SC Y 
    WHERE Y.Sno = '1812101' AND NOT EXISTS( 
        SELECT *
        FROM SC Z
        WHERE Z.Cno = Y.Cno AND Z.Sno = X.Sno
        )
    )
)