SQL语句题
SQL语句题
Student(Sno,Sname,Sage,Ssex)注释：学生表（学号，姓名，性别年龄，性别）
Course(Cno,Cname,Tno) 注释：课程表（课程号，课程名称，教师编号）
SC(Sno,Cno,score) 注释：成绩表（学号，课程号，成绩）
Teacher(Tno,Tname)注释：教师表（教师编号，教师名）
drop sequence s_student;
create sequence s_student;
drop table student cascade constraint purge;
create table Student(
Sno number primary key,
Sname varchar(30),
Sage number(3),
Ssex char(1)
);
insert into Student values(s_student.nextval,'张三1',21,'m');
insert into Student values(s_student.nextval,'张三2',22,'m');
insert into Student values(s_student.nextval,'张三3',23,'w');
insert into Student values(s_student.nextval,'张三4',24,'m');
commit;
drop sequence s_teacher;
create sequence s_teacher;
drop table teacher cascade constraint purge;
create table Teacher(
Tno number primary key,
Tname varchar(30)
);
insert into teacher values(s_teacher.nextval,'里老师1');
insert into teacher values(s_teacher.nextval,'张三');
insert into teacher values(s_teacher.nextval,'里老师3');
insert into teacher values(s_teacher.nextval,'里老师4');
commit;
成绩
drop table sc cascade constraint purge;
create table SC(
Sno number constraint sc_sno_fk references student(sno),
Cno number constraint sc_cno_fk references course(cno),
score number,
constraint sc_sno_cno_pk primary key(sno,cno)
) ;
insert into sc values(1,2,58);
insert into sc values(2,4,21);
insert into sc values(3,3,76);
insert into sc values(4,1,50);
insert into sc values(2,1,66);
insert into sc values(1,3,20);
insert into sc values(2,2,30);
insert into sc values(3,2,50);
insert into sc values(4,4,55);
commit;
课程
drop sequence s_course;
create sequence s_course;
drop table course cascade constraint purge;
create table Course(
Cno number primary key,
Cname varchar(30),
Tno number constraint course_tno_fk references teacher(tno)
);
insert into course values(s_course.nextval,'java',1);
insert into course values(s_course.nextval,'javascript',2);
insert into course values(s_course.nextval,'jdbc',3);
insert into course values(s_course.nextval,'hibernate',4);
commit;
1、查询每个学生的学号，姓名，所修课程及成绩；
select * from student st join sc s on st.sno=s.sno join course co on co.cno=s.cno;
2、查询学过“001”或者学过编号“002”课程的同学的学号、姓名；
非关联子查询
select * from student where sno in (select sno from course where cno=2 or cno=1);
关联子查询
select * from student s where exist (select 1 from course c where s.sno=c.sno and (cno=1 or cno=2));
3、将课程号为“002”的授课老师改为“张三”；
update course set tno=(select tno from teacher where tname='张三') where cno=4;
4、向SC表中插入一条记录，学号001，课程号003，成绩80；
delete from sc where sno=2 and cno=1;
5、删除“002”同学的“001”课程的成绩；
delete from sc where cno=1 and sno=4;
6、查询不及格的课程，并按课程号从大到小排列；
select score , cno from sc where score<60 order by cno desc;
7、查询两门以上不及格课程的同学的学号及其平均成绩；
select sno , avg(score) from sc
where score<60 group by sno having count(sno)>=2;
8、查询姓“李”的老师的姓名及授课情况；
select * from teacher t join course c on t.tno=c.tno where tname like '张%';
9、统计每门课程的学生选修人数（超过10人的课程才统计），要求输出课程号和选修人数查询结果按人数降序排序，若人数相同，按课程号升序排序；
select cno,count(cno) ,max(cname)
from(
select c.cno ,c.cname from sc s
join course c on s.cno=c.cno
)
group by cno
having count(cno)>1
order by cno desc;
10、统计列印各科成绩，各分数段人数：课程ID，课程名称[100-85],[85-70],[70-60],[<60]
select cname||'[85-100]' name,score,c.cno
from sc s join course c on s.cno=c.cno where score between 85 and 100
union
select cname||'[70-85]' name,score,c.cno
from sc s join course c on s.cno=c.cno where score between 70 and 85
union
select cname||'[60-70]' name,score,c.cno
from sc s join course c on s.cno=c.cno where score between 60 and 70
union
select cname||'[<60]' name,score,c.cno
from sc s join course c on s.cno=c.cno where score<60
