29. Find out the number of students in the courses during spring 2000.
Give the name, number and the starting date of each course. Give the number of
students in a course in column STUDENTS.
SELECT cNumber, cName, stBeginDate,
count(*) AS STUDENTS
FROM course, studying
WHERE cNumber = stCourse and
stBeginDate between '1-JAN-2000' and '30-JUN-2000'
GROUP BY cNumber, cName, stBeginDate;
30. Find out the total number of credits achieved by each student. Give
the name and number of the studtent. Give the credits in column CREDITS. It is
enough to list those students that have achieved some credits. Sort the result
by the name of student.
SELECT sNumber, sLname, sFname,
sum(cCreditUnits) AS CREDITS
FROM student, course, studying
WHERE sNumber = stStudent and stCourse = cNumber
and stDatePassed is not null
GROUP BY sNumber, sLname, sFname
ORDER BY sLname, sFname;
31. Find out which course has the highest fee. Give the name of the
course, its starting date and its fee.
SELECT cName, csBeginDate, csFee
FROM course, courseschedule
WHERE cNumber = csCourse and
csFee = (SELECT max(csFee)
FROM courseschedule);
32. What specialities offer more than 2 courses on different topics in
this spring. Give the name of the speciality and the number of course topics.
Give the number of topisc as column COURSES.
SELECT spName, count(distinct csCourse) AS COURSES
FROM speciality, courseschedule, suitability
WHERE spId = suSpeciality and suCourse = csCourse
and csBeginDate > '1.1.2000'
GROUP BY spName
HAVING count(distinct csCourse) > 2;
33. Which speciality has the biggest amount of major students. Give the
name of the speciality and the number of students. Give the number of students
in column STUDENTS.
SELECT spName, count(distinct sfStudent) as STUDENTS
FROM speciality, studyfor
WHERE spId = sfSpeciality and sfExtent = 'major'
GROUP BY spName
HAVING count(distinct sfStudent) >= all
(SELECT count(distsinct sfStudent)
FROM studyfor
WHERE sfExtent = 'major'
GROUP BY sfSpeciality);
34. Find out studets that have studied or have registered for less than
two courses. Include also students that stydy nothing. Give the last and the
first name of the students and the number of their courses. Give the number of
courses as column COURSES. Sort the result by name.
SELECT sLname, sFname,
count(distinct stCourse) AS COURSES
FROM student, studying
WHERE sNumber = stStudent
GROUP BY sLname, sFname
HAVING count(distinct stCourse) < 2
UNION
SELECT sLname, sFname, 0
FROM student
WHERE sNumber not in (SELECT stStudent
FROM studying)
ORDER BY sLname, sFname;
35. Find out what is the difference in the course fee per credit unit
between the most expensive and the least expensive one. Give the answer as
column DIFF.
SELECT round(max(csFee/cCreditUnits) -
min(csFee/cCreditUnits)) AS DIFF
FROM course, courseschedule
WHERE cNumber = csCourse;
36. What is the profit of each course assuming that a lecture hour costs
600 FIM and a practise hour costs 300 FIM. Give the name of the course, its
begin date and the profit (or loss). Give the profit as column PROFIT. Hint:
Use a subquery in the from list. In Oracle this kind of subquery may not refer
to the columns of the outer query. You need to include only those courses that
have students. SELECT cName, csBeginDate, (res.stCount * csFee -
(600 * cLectureHours + 300 * cPracticeHours)) AS PROFIT
FROM course, courseschedule,
(SELECT stCourse, stBeginDate, count(stStudent) AS stCount
FROM studying
GROUP BY stCourse, stBeginDate) res
WHERE cNumber = csCourse and cNumber = res.stCourse
and csBeginDate = res.stBeginDate;