拿来主义：觉得不错和大家分享-----实训数据库开发应用——SQL查询

 

实训数据库开发应用——SQL查询

2008-03-01 11:00 2777人阅读 评论(0) 收藏 举报

数据库sqlvbtablec网络

 

1．分别查询学生表和学生修课表中的全部数据。

select * from student

select * from sc

2．查询计算机系的学生的姓名、年龄。

select sname, sage from student where 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 ssex='男' and sage between 18 and 20 

select sname, sage from student where sdept = '计算机系' and   sage>=18 and sage <=20  and ssex='男'

6．查询9512101号学生的修课情况。

select * from sc where sno = '9512101'

7．查询c01号课程成绩最高的分数。

select max(grade) as max_grade from sc where cno = 'c01'

8．查询学生都修了那些课程,要求列出课程号。

select distinct cno from sc 

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开始且最后一个字母是'a'的OrderID、CustomerID和ShipCountry的信息。

select OrderID, CustomerID, ShipCountry from Orders 

  where ShipCountry like '[^A-F]%a'

12. 查询学生的最大的年龄和最小的年龄

select max(ssex) as 最大年龄, min(ssex) as 最小年龄 from student

13．查询修了c02号课程的所有学生的平均成绩、最高成绩和最低成绩。

select avg(grade) as 平均成绩, max(grade) as 最高成绩, min(grade) as 最低成绩

  from sc where cno = 'c02'

select avg(grade) as 平均成绩,  最高成绩=max(grade), min(grade) 最低成绩from sc where cno = 'c02'

14. 统计每个系的学生人数。

select sdept,count(*) as 学生人数 from student group by sdept

15. 统计每门课程的修课人数和考试最高分。

select cno 课程号, count(*) 修课人数, max(grade) 最高分 from sc group by cno

16. 统计每个学生的选课门数，并按选课门数的递增顺序显示结果。

select sno, count(cno) as 选课门数 from sc group by sno order by 选课门数

17. 统计各系修课的学生总数和考试的平均成绩

select sdept, count(distinct sc.sno), avg(grade)

from student s join sc on s.sno = sc.sno

group by sdept

18. 查询选课门数超过2门的学生的平均成绩和选课门数。

select sno, sum(grade) 总成绩, avg(grade) 平均成绩, count(*) 选课门数 from sc 

  group by sno having count(*) > 2

19. 列出总成绩超过200分的学生，要求列出学号、总成绩。

select sno, sum(grade) 总成绩 from sc 

  group by sno having sum(grade) > 200

20. 查询pubs数据库的titles表中每类图书的平均价格超过12.0元的书的类型、平均价格和最高价格，要求只计算有确定价格的图书的情况。

select type 类型, avg(price) 平均价格, max(price) 最高价格 from titles

where price is not null

group by type

having avg(price) > 12.0

21. 查询pubs数据库的titles表中版税（royalty）为10的每类图书（type）的平均价格。

select type 类型, avg(price) 平均价格 

  from titles where royalty = 10 

  group by type

22. 查询pubs数据库的titles表中每类图书中图书的数目超过3本的图书的总价格。

select type 类型, sum(price) 总价格 

  from titles 

  group by type having count(*) > 3

23. 查询选修了c02号课程的学生的姓名和所在系。

select sname, sdept from Student join SC on Student.Sno = SC.Sno

  where cno = 'c02'

24. 查询成绩80分以上的学生的姓名、课程号和成绩，并按成绩的降序排列结果。

select sname, cno, grade 

  from student s join sc on s.sno= sc.sno

  where grade > 80

  order by grade desc

25. 查询计算机系修了"数据库基础"的学生的姓名、性别、成绩。

(查询计算机系没有选修"数据库基础"的学生的姓名、性别、和其选修课程的成绩。)

select sname, ssex, grade 

  from student s join sc on s.sno = sc.sno

  join course c on c.cno = sc.cno

  where sdept = '计算机系' 

and cname = '数据库基础'

select sname, ssex, grade 

  from student join sc on student.sno=sc.sno

where sdept = '计算机系' and sc.sno not in(

select sno from sc join course c on c.cno = sc.cno

where   cname = '数据库基础')

26. 查询学生的选课情况，要求列出每位学生的选课情况（包括未选课的学生），并列出学生的学号、姓名、修课号、修课成绩。

select s.sno, sname, cno, grade from Student s left join SC

  on s.Sno = SC.Sno

27. 列出"数据库基础"课程考试成绩前三名的学生的学号、姓名、所在系和考试成绩。

select top 3 with ties s.sno, sname, sdept, grade

  from Student s join SC on s.Sno = SC.Sno

  join Course c on c.Cno = SC.Cno

  where cname = '数据库基础'

  order by grade desc

28. 查询哪些学生合选了一门课程，要求列出合选课程的学生的学号和课程号。

select t1.sno, t2.sno, t1.cno

  from sc as t1 join sc as t2 

  on t1.cno = t2.cno 

  where t1.sno < t2.sno

29. 查询哪些学生的年龄相同，要求列出年龄相同的学生的姓名和年龄。

select t1.sname, t2.sname, t1.sage

  from student as t1 join student as t2

  on t1.sage = t2.sage

  where t1.sname < t2.sname

30. 查询哪些课程没有人选，要求列出课程号和课程名。

select c.cno, cname from course c left join sc 

  on c.cno= sc.cno

  where sc.cno is null

select cno, cname from course

where not exists

( select * from sc

where  cno=course.cno)

31. 查询有考试成绩的所有学生的姓名、修课名称及考试成绩，要求将查询结果放在一张新的永久表（假设新表名为new_sc）中，新表的列名分别为：Student_Name, Course_Name, Grade。

select sname Student_Name, cname Course_Name, Grade 

into new_sc

  from student s join sc on s.sno = sc.sno

  join course c on c.cno = sc.cno

  where grade is not null

32. 分别查询信息系和计算机系的学生的姓名、性别、修课名称、修课成绩，并要求将这两个查询结果合并成一个结果集，并以系名、姓名、性别、修课名称、修课成绩的顺序显示各列。

select sdept 系名, sname 姓名, ssex 性别, cname 修课名称, grade 修课成绩

  from student s join sc on s.sno=sc.sno

  join course c on c.cno=sc.cno

  where sdept = '信息系'

UNION

select sdept, sname, ssex, cname, grade

  from student s join sc on s.sno=sc.sno

  join course c on c.cno=sc.cno

  where sdept = '计算机系'

33.在Northwind数据库中，查询雇员（Employees表）和顾客（Customers表）

都来自哪些城市（City)，并降结果按城市的字母升序排序

select distinct city from Employees

union

select distinct city from Customers

order by city asc

34.在Northwind数据库的产品表（Products）中，查询库存数量（UnitsInStock）大于10的产品的编号（ProductID）、产品名（ProductName）和单价（UnitPrice），

并将单价小于等于10元的显示为“很便宜”；

单价超过10元但小于等于20元的显示为“较便宜”；

单价超过20元但小于30元的显示为“中等”；

单价超过30元但小于40元的显示为“较贵”；

单价超过40元但小于100元的显示为“很贵”；

单价超过100元的显示为“价格过高”。

select ProductID, ProductName, 

  case 

    when UnitPrice <=10 then '很便宜'

    when UnitPrice >10 and UnitPrice<=20 then '较便宜'

    when UnitPrice >20 and UnitPrice<=30 then '中等'

    when UnitPrice >30 and UnitPrice<=40 then '较贵'

    when UnitPrice >40 and UnitPrice<=100 then '很贵'

    when UnitPrice >100 then '价格过高'

  end

  from products

  where UnitsInStock>10

35. 查询选修了vb课程的学生的学号、姓名、所在系和成绩，并对所在系进行如下处理：

当所在系为“计算机系”时，显示“CS”；

当所在系为“信息系”时，显示“IS”；

当所在系为“数学系”时，显示“MA”；

对其他系，均显示“OTHER”。

select s.sno 学号,sname 姓名,

  case sdept

    when '计算机系' then 'CS'

    when '信息系' then 'IS'

    when '数学系' then 'MA'

    else 'OTHER'

  end 所在系,grade 成绩

 from student s join sc on s.sno = sc.sno

      join course c on sc.cno=c.cno

where cname='vb'

36. 用子查询实现如下查询:

（1）查询选修了c01号课程的学生的姓名和所在系。

select sname, sdept from student 

  where sno in ( 

     select sno from sc where cno = 'c01')

（2）查询数学系成绩80分以上的学生的学号、姓名。

select sno, sname from student

  where sno in (

    select sno from sc where grade > 80)

    and sdept = '数学系'

（3）查询计算机系学生所选的课程名。

select cname from course

where cno in (

  select cno from sc

    where sno in (

      select sno from student where sdept = '计算机系'))

（4）查询"VB"课程考试成绩前三名的学生的学号、姓名、所在系。

select  sno, sname, sdept from student

  where sno in (select top 3 with ties sno from sc where cno in (

                   select cno from course where cname = 'VB')

                   order by grade desc)     

37. 查询计算机系学生考试成绩高于全体学生的总平均成绩的学生的姓名、考试的课程名和考试成绩。

select sname,cname, grade 

  from student s join sc on s.sno = sc.sno

  join course c on c.cno = sc.cno

 where sdept = '计算机系'

   and grade >(

     select avg(grade) from sc)

38. 查询计算机系VB成绩最低的学生的姓名、所在系和VB成绩

方法一：

select sname,sdept,grade 

  from student s join sc on s.sno = sc.sno

  join course c on c.cno = sc.cno

      where grade = (

       select min(grade) from sc

         where cno in (

           select cno from course where cname ='vb')

       and sno in  (

         select sno from student where sdept='计算机系'))

   and cname= 'VB'  

and sdept=’计算机系’

方法二：

select sname,sdept,grade 

  from student s join sc on s.sno = sc.sno

  join course c on c.cno = sc.cno

      where grade = (

       select min(grade) from sc join course c on c.cno=sc.cno join student s on s.sno=sc.sno

         where  cname ='vb' and sdept='计算机系')

   and cname= 'VB'  

and sdept='计算机系'

38题改动--查询计算机系成绩最低的学生的姓名、所在系和此门最低课程名及成绩

select sname,sdept,cname,grade 

  from student s join sc on s.sno = sc.sno

  join course c on c.cno = sc.cno

  where 　grade in (  select min(grade) from sc

                    where sno in (

  select sno from student where sdept='计算机系'  )

)　and sdept='计算机系'

38题改动--查询计算机系成绩最低的学生的姓名、所在系和VB成绩

select sname,sdept,grade 

  from student s join sc on s.sno = sc.sno

  join course c on c.cno = sc.cno

 where s.sno in (

   select sno from sc

     where grade = (

       select min(grade) from sc

         where sno in (

         select sno from student where sdept ='计算机系')))

   and cname = 'vb'

   and sdept='计算机系'

39. 创建一个新表test_t，其结构为：（COL1, COL2, COL3），其中：

COL1：整型，允许空值；

COL2：字符型，长度为10 ，不允许空值，

COL3：字符型，长度为10 ，允许空值，

试写出按行插入如下数据的语句（空白处表示不提供值），并注意插入后表中的记录值。

序号

COL1

COL2

COL3

(1)

B1

(2)

1

B2

C2

(3)

2

B3

(4)

B4

C4

create table test_t (

COL1 int ,

COL2 char(10) not null,

COL3 char(10) )

（1）INSERT INTO test_t(COL2) values('B1')

（2）INSERT INTO test_t values(1, 'B2', 'C2')

（3）INSERT INTO test_t(COL1, COL2) values(2,'B3')

（4）INSERT INTO test_t(COL2, COL3) values('B4', 'C4')

40. 将计算机系成绩大于80分的学生的修课情况插入到另一张表中，分两种情况实现：

（1）在插入数据过程中建表

（2）先建一个新表，然后再插入数据

（1）select s.sno, cno, grade into new_table1

     from student s join sc on s.sno = sc.sno

     where sdept = '计算机系' and grade > 80

（2）create table new_table2 (

      sno char(10),

      cno char(10),

      grade tinyint)

    insert into new_table2

     select s.sno, cno, grade 

     from student s join sc on s.sno = sc.sno

     where sdept = '计算机系' and grade > 60

41. 删除修课成绩小于50分的学生的修课记录

    delete from sc where grade < 60

42. 删除信息系修课成绩小于50分的学生的修课纪录，分别用子查询和连接查询实现。

 （1） 用连接查询实现

    delete from sc from sc join student s on s.sno=sc.sno

      where sdept = '信息系' and grade < 50

 （2）用子查询实现

    delete from sc where sno in (

      select sno from student where sdept = '信息系' )

      and grade < 50 

43. 将所有选修了'c01'课程的学生的成绩加10分。

    update sc set grade = grade + 10

      where cno = 'c01'

44. 将计算机系所有选修了计算机网络课程的学生的成绩加10分，分别用子查询和连接查询实现。

 （1） 用子查询实现

    update sc set grade = grade + 10

      where sno in (

        select sno from student where sdept = '计算机系' )

      and cno in (

        select cno from course where cname = '计算机网络' )

 （2）用连接实现

    update sc set grade = grade - 10

      from student s join sc on s.sno = sc.sno

       join course c on c.cno = sc.cno 

      where sdept = '计算机系' and cname = '计算机网络' 

45. 删除VB考试成绩最低的学生的VB修课记录

方法一：

Delete from sc

Where sno in

( select sno from sc

Where grade=

(select min(grade) from sc

Join course c on c.cno=sc.cno

Where cname=’vb’)

And cno in

(select cno from course

Where cname=’vb’)

)

and cno in

(select cno from course

Where cname=’vb’)

方法二：

delete from sc 

where cno=(select cno from course where cname='VB')

And 

grade =(select min(grade) from sc 

where cno=

(select cno from course where cname='VB')

)