数据更新和简单查询----实验二 SQL语句应用

一. 实验目的

1. 掌握运用Transact-SQL语句实现表的更新操作。
2. 掌握运用Transact-SQL语句实现表的简单查询操作。

包括：

（1）掌握SELECT子句以及WHERE子句的使用方法。

（2）学会应用ORDER BY子句。

（3）掌握5种基本的聚合函数。

（4）学会分组统计。

二. 实验内容

1. 熟悉SQL Server 查询分析器环境。
2. 运用T-SQL语句对表中的数据进行插入、修改和删除操作。
3. 运用Transact-SQL语句实现：

（1）SELECT子句的应用。

（2）WHERE子句的应用。

（3）ORDER BY子句的应用。

（4）聚合函数的应用。

（5）聚合操作与分组统计的应用。

三.实验步骤

根据实验一创建的数据库及基本表，运用SQL语句实现以下查询要求：

1. 查询所有同学的基本信息，包括：学号s_no、班级号class_no、姓名s_name、性别s_sex、出生日期s_birthday。

   select *
   from Student

    

2. 查询所有同学，要求显示其学号s_no、姓名s_name。

   select s_no,s_name
   from Student

    

3. 查询所有男同学，要求显示其学号s_no、姓名s_name、出生日期s_birthday。

   select s_no,s_name,s_birthday
   from Student
   where s_sex='男'

    

4. 查询所有出生日期在“1980－01－01”前的女同学，要求显示其学号s_no、姓名s_name、性别s_sex、出生日期s_birthday。

   select s_no,s_name,s_sex,s_birthday
   from Student
   where (s_sex='女') and (s_birthday<'1980-01-01')

    

5. 查询所有姓“李”的男同学，要求显示其学号s_no、姓名s_name、性别s_sex、出生日期s_birthday。

   select s_no,s_name,s_sex,s_birthday
   from Student 
   where (s_sex='男') and (s_name like '李%')

    

6. 查询所有姓名中含有“一”字的同学，要求显示其学号s_no、姓名s_name。

   select s_no,s_name
   from Student 
   where s_name like '%一%'

    

7. 查询所有职称不是“讲师”的教师，要求显示其教师号t_no、姓名t_name、职称t_title。

   select t_no,t_name,t_title 
   from Teacher
   where  t_title!='讲师'

    

8. 查询虽选修了课程，但未参加考试的所有同学，要求显示出这些同学的学号s_no。

   select s_no
   from Choice 
   where (course_no is not null) and (score is null)

    

9. 查询所有考试不及格的同学，要求显示出这些同学的学号s_no、成绩score，并按成绩降序排列。

   select s_no,score
   from Choice 
   where(score<'60')
   order by score desc

    

10. 查询出课程号为01001、02001、02003的所有课程，要求显示出课程号course_no、课程名称course_name。（要求用in运算符）。

    select course_no,course_name
    from Course
    where course_no in ('01001','02001','02003')

     

11. 查询所有在1970年出生的教师，要求显示其教师号t_no、姓名t_name、出生日期t_birthday。

    select t_no,t_name,t_birthday
    from Teacher
    where t_birthday between '1970-01-01' and '1970-12-31'

     

12. 查询出各个课程号course_no及相应的选课人数。

    select course_no,count(*) as '选课人数'
    from Course
    group by course_no

     

13. 查询出教授两门以上课程的教师号t_no。

    select t_no,count(*) as '授课数'
    from Teacher
    group by t_no
    having count(*)>=2

     

14. 查询出选修了01001课程的学生平均分数、最低分数及最高分数。

    select avg(score) as '平均分数',min(score) as '最低分数',max(score) as '最高分数'
    from Choice
    where course_no='01001'

     

  15.查询1960年以后出生的，职称为讲师的教师的姓名t_name、出生日期t_birthday，并按出生日期升序排列。

select t_name,t_birthday
from Teacher
where (t_title='讲师') and (t_birthday>'1960-01-01')
order by t_birthday asc

 

四.根据上课讲的例题，自己给出5个查询语句，包括实验内容中要求的知识点。

   注意：每个均要写出查询要求和相应的SQL语句。

1.查询所有姓名中含有“方”字的男同学，要求显示其学号s_no，s_name姓名，出生日期s_birthday。

select s_no,s_name,s_birthday
from Student 
where (s_name like '%方%') and (s_sex='男')

2.查询所有考试及格的同学，要求显示这些同学的学号s_no,姓名s_name，成绩score，并按成绩升序排列。

select student.s_no,s_name,score
from Student,Choice
where (Student.s_no=Choice.s_no) and (score>='60')

3.查询所有职称是“教授”的教师，要求显示其教师号t_no,教师姓名t_name,职称t_title。

select t_no,t_name,t_title
from Teacher
where t_title='教授'

4.查询“李桂菁”老师所讲授的所有课程，要求显示其教师号t_no,教师姓名t_name,职称t_title,所教授的课程号course_no。

select Teacher.t_no,t_name,t_title,course_no
from Teacher,Teaching
where (Teacher.t_no=Teaching.t_no) and (t_name='李桂菁')

5.查询所有女同学，要求显示其学号s_no,姓名s_name，出生日期s_birthday，以及所选修的课程号course_no。

select Student.s_no,s_name,Student.s_birthday,course_no
from Student,Choice
where (Student.s_no=Choice.s_no) and (s_sex='女')