sql语句 面试题

首先创造三个表：
学生表
create table student
{
student_id varchar2(10) not null,
student_name varchar2(50) null
};
课程表
create table course
{
course_id varchar(10) not null,
course_name varchar(100) null
};
分数表
create table score
{
student_id varchar2(10) not null,
course_id varchar2(10) not null,
course_score number(3,1) null
};
答案1:
SELECT st.student_id,st.student_name,re.course_num
FROM student st,
(SELECT sc.student_id,count(sc.student_id) course_num
FROM score sc
WHERE sc.course_score > 60 or sc.course_score = 60
GROUP BY sc.student_id ) re
WHERE st.student_id = re.student_id;
答案2：
SELECT st.student_id,st.student_name
FROM student st,
(SELECT sc.student_id
FROM course co,score sc
WHERE co.course_id = sc.course_id AND course_name = '语文'
GROUP BY sc.student_id) re
WHERE st.student_id = re.student_id AND st.student_name LIKE '张%';
答案3:
SELECT st.student_id,st.student_name
FROM student st,
(SELECT sc.student_id
FROM course co,score sc
WHERE co.course_id = sc.course_id AND co.course_name = '语文' AND co.course_name = '音乐'
GROUP BY sc.student_id) re
WHERE st.student_id = re.student_id;
后三道题有点麻烦，有时间再给你解答，以上都是用Oracle专用的SQL语句查询结果集的。

A.创建表格CODE省略

注明：学生表PK stu_id 课程表pk cos_id 分数表PK enrollment_id FK stu_id,cos_id

B.插入数据code省略

C.Query

1. select s.stu_id,stu_name,count(cos_id) from student s,enrollments e where s.stu_id = e.stu_id and e.grade>60 group by s.stu_id,stu_name;

2. select e.stu_id,s.stu_name,c.cos_name from student s,enrollments e,course c

   where s.stu_id = e.stu_id

   and e.cos_id = c.cos_id

   and c.cos_name = 'CHINESE'

   and s.stu_name like 'W%';

3. select stu_id,stu_name from (select e.stu_id,stu_name,cos_name  from enrollments e,student s,course c 

   where s.stu_id = e.stu_id

   and e.cos_id = c.cos_id

   and c.cos_name IN ('CHINESE','MUSIC'))

   group by stu_id,stu_name

   having count(cos_name) = 2

4. select distinct e.cos_id,c.cos_name,count(e.stu_id) stu_count,count(e.stu_id)-NVL(A.FAIL,0) upscore,(count(e.stu_id)-NVL(A.FAIL,0))/count(e.stu_id) rate from 

   (select cos_id,count(stu_id) fail from enrollments where grade<60 group by cos_id) a,enrollments e,course c

   where e.cos_id = a.cos_id(+)

   and e.cos_id = c.cos_id

   group by e.cos_id,NVL(a.fail,0),c.cos_name;

5. update student

   set avg_grade =(select avg(grade) X from enrollments group by stu_id

   having student.stu_id = enrollments.stu_id);

6. select stu_id,avg(grade) from

   (select stu_id,cos_id,grade,row_number() over(partition by stu_id order by grade ) X from enrollments)

   group by stu_id

   having count(*)<=2

   UNION

   select A.stu_id,avg(A.grade)from

   (select stu_id,cos_id,grade,row_number() over(partition by stu_id order by grade ) X from enrollments) A,

   (select stu_id,count(*) c from

   (select stu_id,cos_id,grade,row_number() over(partition by stu_id order by grade ) X from enrollments)

   group by stu_id) B

   where A.stu_id = B.stu_id

   and A.x>1 and x

   group by A.stu_id,b.c

_________________________________________________

环境：oracle 10g/TOAD 以上代码均通过测试，如有问题，请联系，谢谢