《数据库系统》实验报告三
(2) For the student with ID 12345 (or any other value), show all course_id and title of all courses registered for by the student.
SELECT course_id,title
FROM takes NATURAL JOIN course
WHERE id = 12345
4. As above, but show the total number of credits for such courses (taken by that student). Don't display the tot_creds value from the student table, you should use SQL aggregation on courses taken by the student.
SELECT id,SUM(credits)
FROM takes NATURAL JOIN student NATURAL JOIN course
WHERE id = 12345
GROUP BY id;
(3) As above, but display the total credits for each of the students, along with the ID of the student; don't bother about the name of the student. (Don't bother about students who have not registered for any course, they can be omitted)
SELECT id,SUM(credits)
FROM takes NATURAL JOIN student NATURAL JOIN course
GROUP BY id
(4) Find the names of all students who have taken any Comp. Sci. course ever (there should be no duplicate names)
SELECT DISTINCT id,NAME
FROM takes NATURAL JOIN student
WHERE course_id IN (SELECT course_id
FROM course
WHERE dept_name='Comp. Sci.')
(5) Display the IDs of all instructors who have never taught a course (Notesad1) Oracle uses the keyword minus in place of except; (2) interpret "taught" as
"taught or is scheduled to teach")
SELECT id
FROM instructor
WHERE id NOT IN (SELECT DISTINCT id
FROM teaches
)
(6) As above, but display the names of the instructors also, not just the IDs. SELECT id,NAME
FROM instructor
WHERE id NOT IN (SELECT DISTINCT id
FROM teaches
)
(7) Find the maximum and minimum enrollment across all sections, considering only sections that had some enrollment, don't worry about those that had no students taking that section
SELECT max(enrollment),min(enrollment)
from(
SELECT sec_id,semester,year,
COUNT(DISTINCT id) as enrollment
FROM takes
GROUP BY sec_id,semester,YEAR);
(8)As in in Q1, but now also include sections with no students taking them; the enrollment for such sections should be treated as 0. Do this in two different ways (and create require data for testing) 1). Using a scalar subquery 2). Using aggregation on a left outer join (use the SQL natural left outer join syntax) SELECT DISTINCT sec_id,semester,YEAR,IFNULL(`count`,0)
FROM section LEFT OUTER JOIN
(SELECT sec_id,semester,YEAR,COUNT(DISTINCT id,sec_id,semester,YEAR) AS 'count'
FROM takes
GROUP BY sec_id,semester,YEAR) AS T USING (sec_id,semester,YEAR) (9) Find all courses whose identifier starts with the string "CS-1"
SELECT *
FROM course
WHERE course_id LIKE 'CS-1%'
(10) Find instructors who have taught all the above courses 1). Using the "not exists ... except ..." structure 2). Using matching of counts which we covered in class (don't forget the distinct clause!)
select distinct ID,name from teaches natural join instructor
where not exists ((select course_id from course)
except (select course_id from course where course_id like 'CS-1%'));
2. The university rules allow an F grade to be overridden by any pass grade (A, B, C, D). Now, create a view that lists information about all fail grades that have
not been overridden (the view should contain all attributes from the takes relation).
CREATE VIEW F AS
SELECT *
FROM takes
WHERE grade = 'F'
3. Find all students who have 2 or more non-overridden F grades as per the takes relation, and list them along with the F
select name,'F'as final_grade
from F natural join student
group by name
having count(grade)>=2;
选择数量〉=1时有一个结果
出
现
问
题
（列出遇到的问题及其解决方法）解
决
方
案。