1.创建以上表,并给表中输入数据。
学生表:
create table T_STUDENT(
stuno varchar2(16) primary key,
stuname varchar2(16),
stusex varchar2(6),
stubir date
);
课程表:
create table T_COURSE(
courseno varchar2(16) primary key,
cousrsename Varchar2(16),
teano Varchar2(16),
foreign key (teano) references T_TEACHER(teano)
);
教师表:
create table T_TEACHER(
teano varchar2(16) primary key,
teaname varchar2(16),
teatitle varchar2(16)
);
成绩表:
create table T_SCORE(
stuno varchar2(16) ,
courseno varchar2(16),
type varchar2(16),
score float,
primary key(stuno,courseno,type),
foreign key(courseno) references T_COURSE(courseno)
);
创建表和输入数据可以通过SQL server Management Studio工具实现。
2.查询T_STUDENT中所有的数据
select*
from T_STUDENT
3.查询T_STUDENT中所有学生的姓名和性别
select stuname,stusex
from T_STUDENT
4.将学号和姓名显示,其中,姓名的格式为:“姓名:xxx”.
select stuno,’姓名:’=stuname
from T_STUDENT
5.为了更好地体现各个学生的考试情况,将T_SCORE中的信息显示,分数显示为与60分的差。
select stuno,courseno,type,’差值’=score-60
from T_SCORE
select stuno,courseno,type,score-60 as ‘分数’
from T_SCORE
6.将T_SCORE中的信息显示,分数显示为与60分的差值,列名为“差值”,如果第一条记录分数为空,会得出来什么结果。
(注意:空值具有特殊性,包括空值的任何算术表达式都等于空)
select stuno,courseno,type,’差值’=score-60
from T_SCORE
或者
select stuno,courseno,type,score-60 as “差值”
from T_SCORE
注:分数为空值,结果也是空值。
7.将学号和姓名显示,其中,列名分别显示为“学生”和姓名。
select ‘学号’=stuno,’姓名’=stuname
from T_STUDENT
SELECT stuno as “学号” ,stuname AS “姓名”
from T_Student;
8.将学号和姓名显示在一个列中,列名显示为:信息。
select ‘信息’=stuno
from T_STUDENT
union all
select stuname
from T_STUDENT
9.查询教师的职称种类(distinct可以去掉重复的)
select distinct teatitle
from T_TEACHER
10.查询女生的姓名
select stuname
from T_STUDENT
where stusex=’女’
11.查询课程VB的信息
select*
from T_COURSE
where coursename=’VB’
12.显示所有期中考试及格的记录
select*
from T_SCORE
where type=’期中’ and score>60
select t_student.stuno,stuname,courseno,stusex,type,score
from t_score,t_student
where t_student.stuno=t_score.stuno and type=’期中’ and score>60;
13.为了找出考试尖子,需要显示所有期末考试在90-100的考试记录(使用<,>)
select T_STUDENT.stuno,stuname,courseno,type,score
from T_SCORE,T_STUDENT
where t_student.stuno=t_score.stuno and score between’90’and ‘100’
select T_STUDENT.stuno,stuname,courseno,type,score
from T_SCORE,T_STUDENT
where t_student.stuno=t_score.stuno and score>=90 and score<=100
14.学校要举行一帮一活动,让高分学生帮助低分学生。查询90分以上的期末考试记录,以及不及格的期末考试记录
select t_student.stuno,stuname,stusex,courseno,type,score
from t_score,t_student
where t_student.stuno=t_score.stuno and type=’期末’ and score<60 and score>90;
15.利用BETWEEN谓词显示所有期末考试在65-80的考试记录
select T_STUDENT.stuno,stuname,stusex,courseno,type,score
from T_SCORE,T_STUDENT
where t_student.stuno=t_score.stuno and type=’期末’and (score between ‘65’and ‘80’)
16.使用IN谓词,显示分数是60,70,80的考试记录
select T_STUDENT.stuno,stuname,stusex,courseno,type,score
from T_SCORE,T_STUDENT
where T_STUDENT.stuno=T_SCORE.stuno and score in(‘60’,’70’,’80’)
17.查询姓李的学生资料
select*
from T_STUDENT
where stuname like ‘李%’
18.查询姓“王”,名字为一个字的学生,并将这类学生的详细信息显示出来
select*
from T_STUDENT
where stuname like ‘王_’
19.查询性别为空的学生资料
select*
from T_STUDENT
where stusex is null
20.用升序显示学生S001的所有期末考试成绩
select T_STUDENT.stuno,courseno,type,score
from T_STUDENT,T_SCORE
where T_STUDENT.stuno=T_SCORE.stuno and T_STUDENT.stuno=’s001’and type=’期末’
order by score asc
21.用降序显示课程C001的所有期末考试成绩,对于相等的成绩,则按照课程编号升序显示。
select T_STUDENT.stuno,T_course.courseno,type,score
from T_STUDENT,T_SCORE,T_COURSE
where T_STUDENT.stuno=T_SCORE.stuno and T_course.courseno=’c001’and type=’期末’
order by score desc,courseno asc
22.查询姓名为“郭丽芳”的考试成绩
select T_STUDENT.stuno,stuname,stusex,courseno,type,score
from T_STUDENT,T_SCORE
where T_STUDENT.stuno=T_SCORE.stuno and stuname=’郭丽芳’
23.显示各个教师及其讲授课程的详细情况(等值连接查询)
select T_TEACHER.teano,teaname,teatitle,courseno,coursename
from T_TEACHER,T_COURSE
where T_TEACHER.teano=T_COURSE.teano
24.查询名为“梁天”的教师没有上过的课程。(等值连接查询)
select coursename
from T_COURSE,T_TEACHER
where T_COURSE.teano=T_TEACHER.teano and teaname!=’梁天’
25.课程“VC++”,有哪些学生选过?请列出这些学生的姓名(等值连接查询)
select distinct stuname
from T_STUDENT,T_COURSE,T_SCORE
where T_STUDENT.stuno=T_SCORE.stuno and T_SCORE.courseno=T_COURSE.courseno and (coursename=’VC++’)
26.查询学号为”S001”的学生,参加课程“C001”考试的成绩,显示格式为:期中成绩 期末成绩 总评成绩 其中,总评成绩=期中成绩*0.4+期末成绩*0.6
select first.score “期中成绩”,second.score “期末成绩”,(first.score*0.4)+(second.score*0.6) “总评成绩”
from t_score first ,t_score second
where first.stuno = second.stuno
and first.courseno=’C002’
and second.courseno=’C002’
and first.stuno=’S001’
and second.stuno=’S001’
and first.type=’期中’
and second.type=’期末’;
27.查询课程“VB”是哪一位老师教的,列出其姓名
select teaname
from T_COURSE,T_TEACHER
where T_COURSE.teano=T_TEACHER.teano and coursename=’VB’
28.使用左外连接完成27)
select T_Teacher.teaname
from T_Teacher left join T_Course
on T_Teacher.Teano = T_Course.Teano
where Coursename=’VB’;
29.使用右外连接完成27)
select T_Teacher.teaname
from T_Course right join T_Teacher
on T_Course.Teano = T_Teacher.Teano
where Coursename=’VB’;
30.查询T_STUDENT内所有人的姓名和性别
select stuname,stusex
from T_STUDENT