数据库原理及应用实验三参考答案

本题中所用的数据库是上次实验中所建立的Study数据库。请写出相应的查询语句。并将查询结果贴在下方。

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

use Study;

select s_no,class_no, s_name, s_sex, s_birthday from Student

go

 

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

use Study;

select s_no,s_name from Student

go

 

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

use Study;

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

go

 

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

use Study;

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

go

 

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

use Study;

select s_no,s_name,s_sex,s_birthday  from Student  where s_sex='' and s_name like '%'

go

 

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

use Study;

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

 

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

use Study;

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

go

 

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

use Study;

select s_no  from Choice  where score is null

go

 

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

use Study;

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

go

 

  1. 查询出课程号为01001、02001、02003的所有课程,要求显示出课程号course_no、课程名称course_name。(要求用in运算符)。

use Study;

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

go    

 

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

use Study;

select t_no, t_name,t_birthday

from Teacher

where t_birthday between '1970-01-01'and'1970-12-31'

go

 

  1. 查询出选了课的学生的学号

use Study;

select distinct(s_no)  from Choice

go

 

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

use Study;

select course_no,count(course_no) from Choice 

group by(course_no)

go

 

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

use Study;

select t_no  from Teaching

group by(t_no)  having count(course_no)>=2

go

 

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

use Study;

select avg(score) as 平均分,min(score) as 最低分,max(score) as 最高分

from Choice

where course_no='01001'

go

      

 

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

use Study;

select t_name,t_birthday  from Teacher  where t_title='讲师' and t_birthday>='1961-01-01'

order by(t_birthday)

go

 

补充题目:

本题中所用的数据库是上次实验中所建立的Study数据库。请写出相应的查询语句。并将查询结果贴在下方。

(1)查询所有同学的选课及成绩情况,要求显示学生的学号s_no、姓名s_name、课程号course_no和课程的成绩score。

use Study

select Student.s_no,s_name,course_no,score

from Choice,Student

where Student.s_no = Choice.s_no

go

 

(2)查询所有同学的选课及成绩情况,要求显示学生的姓名s_name、课程名称course_ name、课程的成绩score,并将查询结果存放到一个新的数据表new_table中。

use Study

select s_name,course_name,score

into new_table

from Choice,Student,Course

where Student.s_no = Choice.s_no and Choice.course_no=Course.course_no

go

 

(3)查询“计算机99-1”班的同学的选课及成绩情况,要求显示学生的学号s_no、姓名s_name、课程号course_no、课程名称course_name、课程的成绩score。

use Study

select Student.s_no,Student.s_name,Course.course_name,Course.course_no,score

from Choice,Course,Student,Class

where

Choice.course_no=Course.course_no

and Student.s_no=Choice.s_no

and Student.class_no= Class.class_no

and class_name='计算机99-1'

go

 

(4)查询所有同学的学分情况(假设课程成绩≥60分时可获得该门课程的学分),要求显示学生的学号s_no、姓名s_name、总学分(将该列定名为:total_score)。(用JOIN)

use Study

select Choice.s_no,s_name,sum(course_score) as total_score

from Choice join Student on Choice.s_no = Student.s_no join Course on Choice.course_no = Course.course_no

where score>=60

group by s_name,Choice.s_no

go

 

(5)查询所有同学的平均成绩及选课门数,要求显示学生的学号s_no、姓名s_name、平均成绩(将该列定名为average_score)、选课的门数(将该列定名为:choice_num)。

use Study

select Student.s_no,s_name,avg(score) as average_score,count(course_no) as choice_num

from  Student join Choice on Student.s_no = Choice.s_no

group by s_name,Student.s_no

go

 

(6)查询所有选修了课程但未参加考试的所有同学及相应的课程,要求显示学生的学号s_no、姓名s_name、课程号course_no、课程名称course_name。

use Study

select Student.s_no,s_name,Course.course_no,Course.course_name

from  Student, Choice , Course

where Student.s_no =Choice.s_no and Choice.course_no =Course.course_no and score is null

go

 

(7)查询所有选修了课程但考试不及格(假设<60分为不及格)的所有同学及相应的课程,要求显示学生的学号s_no、姓名s_name、课程号course_no、课程名称course_name、学分course_score。

use Study

select Student.s_no,s_name,Course.course_no,course_name,course_score

from Choice,Student,Course

where Student.s_no = Choice.s_no and Choice.course_no=Course.course_no and score<60

go

 

(8)查询选修了课程名为“程序设计语言”的所有同学及成绩情况,要求显示学生的姓名s_name、课程的成绩score。(使用ANY)

use Study

select s_name, score

from Choice,Student,Course

where Student.s_no = Choice.s_no and Choice.course_no=any(select  course_no from Course where course_name='程序设计语言')

go

 

(9)查询“计算机系”的所有同学及成绩情况,要求显示学生的学号s_no、姓名s_name、班级名称class_name、课程号course_no、课程名称course_name、课程的成绩score。

use Study

select Choice.s_no,s_name,Class.class_name,Choice.course_no,course_name,score from Choice,Student,Course,Class

where Choice.course_no = Course.course_no and Student.s_no = Choice.s_no and Class.class_no = Student.class_no and class_special = '计算机'

go

 

(10)查询所有教师的任课情况,要求显示教师姓名t_name、担任课程的名称course_name。

use Study

select t_name,course_name

from Course,Teaching,Teacher

where Teaching.course_no = Course.course_no and Teaching.t_no = Teacher.t_no

go

 

(11)查询所有教师的任课门数,要求显示教师姓名t_name、担任课程的门数(将该列定名为course_number)。

use Study

select Teacher.t_no,t_name,count(Course.course_no) as course_number

from Course,Teaching,Teacher

where Teaching.course_no = Course.course_no and Teaching.t_no = Teacher.t_no

group by Teacher.t_no, t_name

go

 

(12)查询和“李建国”是同一班级的同学的姓名。(使用子查询)

use Study

select s_name

from Student

where Student.class_no = (select class_no from Student where s_name='李建国') and s_name!= '李建国'

go

 

(13)查询没有选修“计算机基础”课程的学生姓名。(用NOT EXISTS)

use Study

select s_name

from Student

where not exists (select *

from Course,Choice

where Choice.course_no = Course.course_no and Student.s_no =Choice.s_no and course_name='计算机基础' )

go

 

(14)查询主讲“数据库原理与应用”和主讲“数据结构”的教师姓名。(用UNION)

use Study

select t_name

from Teacher,Teaching,Course

where Teacher.t_no= Teaching. t_no and  Teaching.course_no = Course.course_no and Course.course_name = '数据库原理与应用'

union

select t_name

from Teacher,Teaching,Course

where Teacher.t_no= Teaching. t_no and  Teaching.course_no = Course.course_no and Course.course_name =  '数据结构'

go

 

(15)查询讲授了所有课程的教师的姓名。

use Study

select t_name

from Teacher

where not exists (select *

from Course

where not exists (select *

from Teaching

where Course.course_no = Teaching.course_no and Teacher.t_no = Teaching.t_no)

)

go

SQL查询二 2 通过本实验使学生掌握多表查询、子查询以及基本数据操作 二、实验内容 使用实验一建立的银行贷款数据库和表,完成以下查询。 1-4是多表查询和子查询,5-11是数据操作 查询经济性质为“国营”的法人在上海的银行贷款的信息,列出法人代码、银行代码和贷款日期,分别用多表连接和子查询两种方式实现。 查询在“建设银行上海分行”贷过款的法人名称,分别用多表连接和子查询两种方式实现。 查询在“工商银行北京A支行”贷款金额三名(包括并列的情况)的法人的法人代码、法人名称和经济性质,分别用多表连接和子查询两种方式实现。 查询在“工商银行北京B支行”贷款、且贷款金额高于此银行的平均贷款金额的法人代码、贷款日期和贷款金额。 在银行表中插入如下数据:银行代码号为:B321B,银行名称为:建设银行上海B分行,电话为空值。 在法人表中插入如下数据:法人代码号为:E11,法人名称为:新法人,注册资金为:2350万元,经济性质使用默认值。 删除银行编号为“B321B”的银行信息。 删除2000年之一次贷款金额最小的贷款记录。 删除从贷款日期到当日期天数超过10年的贷款记录。 删除法人名称为“爱贝乐玩具有限公司”且贷款金额小于10万元的贷款记录,分别用子查询和多表连接两种方式实现。 将经济性质为“私营”的法人在“工商银行上海支行”贷款的所有贷款金额加5万元,分别用子查询和多表连接两种方式实现。 使用实验一建立的学生数据库和表,完成以下查询 12-15是多表查询和子查询,16-20是数据操作 查询计算机系年龄大于总平均年龄的学生的姓名和年龄。 查询计算机系年龄大于计算机系平均年龄的学生的姓名和年龄。 查询计算机系考试成绩小于总平均分的学生的学号、姓名。 将考试成绩最低的并且不及格学生的最低修课成绩改为60。 将数据库基础考试成绩最低的且成绩为不及格学生的数据库考试成绩改为60。 删除计算机系“计算机网络”课程的全部选课记录。 删除vb考试成绩最低的两个学生的vb考试记录。 对数据库考试成绩进行如下修改:如果成绩低于60分,则提高10%;如果成绩在60到80之间,则增加6%;如果成绩在80到95之间则提高4%,其他情况不提高。 对学分进行如下修改:如果是第1到第3学期开始的课程,则学分增加1分;如果是第4到第6学期开设的课程,学分增加2分,其他学期开始的课程学分增加3分。 以下查询必须用子查询完成: 查询男生年龄最大的学生的姓名和所在系。 查询选修了‘数据库基础’的学生的姓名、所在系。s 选修了第6学期开始的课程的学生的学号、姓名和所在系。 查询男生所修的课程的课程名。 查询年龄最小的学生所选的课程名。 三、实验报告 将实验结果反映在实验报告中,并对实验中遇到的问题及解决方案、进行整理、分析总结,提出实验结论或自己的看法。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值