ORACLE1.28 面试题

create table students(
student_id number,
student_name varchar(20),
major varchar(50)
)
insert into students values(1,'小红','法律');
insert into students values(2,'小明','机械');
insert into students values(3,'小军','it男');
commit;
select * from students;
delete students where student_id=2;

drop table student_scores;
create table student_scores(
scord_id number,
student_id number,
course_name varchar(50),
score number,
pass_or_fail varchar(16)
)
insert into student_scores values(1,1,'yuwen',80,null);
insert into student_scores values(2,1,'shuxue',85,null);
insert into student_scores values(3,1,'yingyu',79,null);

insert into student_scores values(1,2,'yuwen',81,null);
insert into student_scores values(2,2,'shuxue',98,null);
insert into student_scores values(3,2,'yingyu',97,null);

insert into student_scores values(1,3,'yuwen',82,null);
insert into student_scores values(2,3,'shuxue',56,null);
insert into student_scores values(3,3,'yingyu',90,null);
delete student_scores where student_id=2;

select student_name,score from student_scores
left join students on student_scores.student_id=students.student_id;

select student_name,min(score),max(score) from student_scores
left join students on student_scores.student_id=students.student_id
group by student_name
--写一个SQL脚本来检索所有的students_name其各课程成绩在80以上
select student_name,min(score),max(score),avg(score) from student_scores
left join students on student_scores.student_id=students.student_id
group by student_name
having min(score)>80

update student_scores set pass_or_fail=(case when score>=80 then '优'when score<80 then '及格'end)
where student_id in (select student_id from students where major='软件工程');

select * from student_scores left join students on student_scores.student_id=students.student_id;