Ubuntu中文论坛

This is a very good practice for you to assess your knowledge of writing SQL queries.

Problem Description
You were identified as the SQL expert in the company. Your manager has given you the
following relational schema

(下面粗体的是primary keys of the relation):


STUDENT (sid, sname, sex, age, year, averagegrade)
SCHOOL (dname, numhonours)
PROF (pname, dname)
COURSE (dname, cno, cname)
MAJOR (dname, sid)
CLASS (dname, cno, classno, pname)
ENROLL (sid, dname, cno, classno, grade)

Design SQL queries that answer the questions given below (one query per question). The answer
to each query should be duplicate free, but you should use DISTINCT only when necessary.

Questions

1. Show the names of courses offered by schools that have more than 50 honours students.

2. For every class offered by the business school, display the cno, classno, and the average age of
the students enrolled in the class.

3. Show the course names and the class numbers of all classes with fewer than 10 students
enrolled in.

4. List the course names and class numbers in which all its students are older than twenty-five
years old.

5. List the professors in which every class they teach has at least one student who is younger
than twenty.

6. Find the names and majors of the students who are taking at least two database related
courses, i.e, courses containing the word "database".

7. List the name of the schools and its number of honours students if the school has no major
containing “database” courses.

8. For each class from the SIT school, display the course number, class number, and the lowest
average grade of the students enrolled in that class. Sort the results in ascending order of
lowest average grade.

9. List the student names with the second highest average grade in their major schools.

10. List the student names who are taking courses from either the SIT school or the business
school, but not from both.




我刚开始接触SQL不久.第一个题目的话看起来好像是要显示有大于50个honours students 的course name.但是在COURSE表里没有numhonours,那该怎么写才是正确的呢?
我尝试写的是
SELECT numhonours
FROM SCHOOL
WHERE numhounours>50;

但感觉应该是错的...因为要求的是显示course name吧....=. =;;

求达人解答.下面几道题可以的话也请麻烦给出你的解答好让我学习下.谢谢

1. Show the names of courses offered by schools that have more than 50 honours students.

select a.cname
from course a,school b
where a.dname=b.dname and b.numhonours>50;

2. For every class offered by the business school, display the cno, classno,
and the average age of the students enrolled in the class.

select a.cno,a.classno,avg(b.age)
from enroll a,student b
where a.sid=b.sid and a.dname='business school'
group by 1,2;

3. Show the course names and the class numbers of all classes with fewer than 10 students
enrolled in.

select b.cname,a.classno
from enroll a,course b
where a.dname=b.dname and a.cno=b.cno
group by 1,2
having count (distinct a.sid)<10;

4. List the course names and class numbers in which all its students are older than twenty-five
years old.

select distinct c.cname,b.classno
from enrol a,class b,course c,student d
where d.sid=a.sid and d.age>25
and a.dname=b.dname and a.cno=b.cno and a.classno=b.classno
and b.dname=c.dname and b.cno=c.cno

5. List the professors in which every class they teach has at least one student who is younger
than twenty.

select distinct a.pname
from prof a,major b,student c
where a.dname=b.dname and b.sid=c.sid and c.age<20

or real time records (heavier transation):

select distinct a.pname
from prof a,enroll b,student c
where a.dname=b.dname and b.sid=c.sid and c.age<20

6. Find the names and majors of the students who are taking at least two database related
courses, i.e, courses containing the word "database".

谢谢9楼：这个是错了，题目没看清。呵呵
select distinct c.sname,a.dname
from major a,course b,student c
where a.dname=b.dname and a.sid=c.sid
and b.cname like '%database%'

select c.sname,a.dname,c.sid
from enrol a,course b,student c
where a.cno=b.cno and a.sid=c.sid
and b.cname like '%database%'
group by 2,3 ## Mysql 老版本--> group by 1,2,3
having count(distinct a.cno)>1

7. List the name of the schools and its number of honours students if the school has no major
containing “database” courses.

select distinct a.*
from school a,course b
where a.dname=b.dname and b.cname not like '%database%'

8. For each class from the SIT school, display the course number, class number, and the lowest
average grade of the students enrolled in that class. Sort the results in ascending order of
lowest average grade.

select cno,classno,min(grade)
from enrol
where dname='SIT school'
group by 1,2
order by 3

9. List the student names with the second highest average grade in their major schools.

create table 0_a
select dname,max(grade) max_grade from enrol group by 1

create table 0_b
select a.sid from enrol a,0_a b
where a.dname=b.dname and a.grade=b.max_grade

drop table 0_a;
create table 0_a
select dname,max(grade) sec_grade from enrol
where sid not in (select sid from 0_b)
group by 1

select distinct b.sname
from 0_a a,student b,enrol c
where a.dname=c.dname and a.sec_grade=c.grade
and c.sid=b.sid

10. List the student names who are taking courses from either the SIT school or the business
school, but not from both.

select a.sname
from student a,major b
where a.sid=b.sid
and (b.dname='SIT school' or b.dname='business school')
minus
select a.sname
from student a,major b
where a.sid=b.sid and b.dname='SIT school'
and a.sid in (
select sid from major where dname='business school'
)
//can be improved -- try the logic your self

还是这里的同志最亲切啊.

请问一下那些primary keys of the relation和写这些SQL的时候有什么联系么?

还有请问你写的里面的group by 1,2以及后面的3是分别代表什么?

另外第9题不能用one statement完成么?

PK 是可用来 join table 用的。

select a,b,count(*) from tab group by 1,2 order by 3 与
select a,b,count(*) from tab group by a,b order by c 相同 (可能 MySQL 老版本只能用 a,b,c 而非1,2,3)

有些题目是考试用的，又如两个 not exists 语句，在实际工作中几乎没人用。第9题可以用1个SQL 表达，你自己写啊。

那请问你对ERD了解么?

有个问题
1

select a.cname
from course a,school b
where a.dname=b.dname and b.numhonours>50;

2

select a.cno,a.classno,avg(b.age)
from enroll a,student b
where a.sid=b.sid and a.dname='business school'
group by 1,2;

a应该是course的简写,b应该是school的简写.那在第二个解答里出现了a.cno, a.classno.但是classno是在CLASS表里.并不是在course表里额.不是很懂这里,请教一下,谢谢

natrs 写了：有个问题
2

select a.cno,a.classno,avg(b.age)
from enroll a,student b
where a.sid=b.sid and a.dname='business school'
group by 1,2;

a应该是course的简写,b应该是school的简写.那在第二个解答里出现了a.cno, a.classno.但是classno是在CLASS表里.并不是在course表里额.不是很懂这里,请教一下,谢谢

这个SQL里无有 class | course 表噢？

a.cno, a.classno 是表 enroll a 里的啊。

哦哦..对对,是我没看清楚.谢谢你的解答.非常感谢.

6. Find the names and majors of the students who are taking at least two database related
courses, i.e, courses containing the word "database".

select distinct c.sname,a.dname
from major a,course b,student c
where a.dname=b.dname and a.sid=c.sid
and b.cname like '%database%'

这个不是至少2门课吗？
select a.dname
from enroll a left outer join course b on a.cno = b.cno and a.dname = b.dname
where a.cno = b.cno and b.cname like '%database%'
group by a.dname,a.sid
having count(*) > 1

leesir 写了：这个不是至少2门课吗？
select a.dname
from enroll a left outer join course b on a.cno = b.cno and a.dname = b.dname
where a.cno = b.cno and b.cname like '%database%'
group by a.dname,a.sid
having count(*) > 1

是