Introduction to Databases (581328-9) - Fall 2001

Set 1: tasks and their solutions

Note that there can also be other correct solutions to the different tasks that those presented here.

1. List all data about specialities
```     SELECT *
FROM speciality;```
2. Which cities have studests as inhabitants?
```     SELECT distinct (sCity)
FROM student;```
3. Retrieve all student data about female students that live in Helsinki.
```     SELECT *
FROM student
WHERE sCity = 'Helsinki' and sSex = 'F';```
4. Get the phone number of the teacher Susan Smith.
```     SELECT tLname, tFname, tPhone
FROM teacher
WHERE tLname = 'Smith' and tFname = 'Susan';```
5. Get all data about courses that give 3 credit units and have more than 20 lecture hours or more than 40 hours teaching alltogether.
```     SELECT *
FROM course
WHERE cCreditUnits = 3 and (cLectureHours  20 or
(cLectureHours + cPracticeHours  40));```
6. A lecture hour costs 600 FIM and a practice hour 300 FIM, make a report that shows the name, number and the total cost of each course (as column PRICE) counted based on the table course.
```     SELECT cNumber, cName, ((cLectureHours * 600) +
(cPracticeHours * 300)) AS PRICE
FROM course;```
7. Retrieve the names of teachers (as column NAME that contains lastname space and first name) that have no phone number.
```     SELECT (tLname||' '||tFname) AS NAME
FROM teacher
WHERE tPhone is null;```
8. Make a report about male students that live in Espoo. The report should contain the name columns, the birth year, and the student number. It should be ordered primarily by the age of students (the younger ones first) and secondarily by the name.
```     SELECT sLname, sFname, sYearBorn, sNumber
FROM student
WHERE sSex = 'M' and sCity = 'Espoo'
ORDER BY sYearBorn desc, sLname, sFname;```
9. Make an ordered list of courses based on the amount of teaching needed for one credit unit. Include in your report only the name of the course and the number of needed hours rounded to an integer as column CRCOST.
```     SELECT cName, round((cLectureHours + cPracticeHours) /
cCreditUnits) AS CRCOST
FROM course
ORDER BY CRCOST;```
10. Make a list of courses that start between 15.1.2000 and 15.3.2000. Include in your list the name, the number (as CNUMBER) and the date in format dd.mm.yyyy as column BEGINS. Note Oracle fuction to_char gives the date properly formatted.
```     SELECT cName, cNumber,
to_char(csBeginDate,'DD.MM.YYYY') AS BEGINS
FROM course, courseschedule
WHERE cNumber = csCourse and csBeginDate between
'15-JAN-2000' and '15-MAR-2000';```
11. List the specialities of teachers. Include the first and last name of the teacher and the name of the speciality. Order primarily by the teacher and secondarily by the speciality.
```     SELECT tLname, tFname, spName
FROM teacher, teacherskills, speciality
WHERE tId = tsTeacher and tsSpeciality = spId
ORDER BY tLname, tFname, spName;```
12. Make a report of teaching tasks of Thomas Edison on spring 2000 (upto end of May). Include the name of teacher, and the name of course and the tasks and their hours.
```     SELECT tLname, tFname, cName, tcTask, tcHours
FROM teacher, course, teaching
WHERE tLname = ´Edison´ and tFname = ´Thomas´ and
tId = tcTeacher and tcCourse = cNumber and
tcBeginDate between '1-JAN-2000' and '31-MAY-2000';```
13. What courses suit for students that study for a certain speciality?
```     SELECT spName, suPhase, cName, cNumber
FROM course, suitability, speciality
WHERE spId = suSpeciality and suCourse = cNumber
ORDER BY spName, suPhase, cName;```
14. Which students (name and number) study both courses Network management and Object Modeling?
```     SELECT sLname, sFname, sNumber
FROM student, studying s1, studying s2, course c1, course c2
WHERE (sNumber = s1.stStudent and s1.stCourse = c1.cNumber
and c1.cName = 'Network management')
and
(sNumber = s2.stStudent and s2.stCourse = c2.cNumber
and c2.cName = 'Object Modeling')
ORDER BY sLname, sFname, sNumber;```
15. Which teachers both lecture and instruct practices within the same course offering? Give the name of the teacher and the name, number and starting time of the course.
```     SELECT tLname, tFname, cName, cNumber,
to_char(t1.tcBeginDate,'DD.MM.YYYY') AS BDATE
FROM teacher, course, teaching t1, teaching t2
WHERE (tId = t1.tcTeacher and t1.tcCourse = cNumber
and
(tId = t2.tcTeacher and t2.tcCourse = cNumber
and t1.tcBeginDate = t2.tcBeginDate;```
16. Let us assume that expert level courses are too demanding for minor degree students. Find out which students have taken to demanding courses. Give the name, number, specialily and its extent for the students and the name and phase of the course. Order the result by student.
```     SELECT sLname, sFname, sNumber, spname,
sfExtent, cName, suPhase
FROM student, studying, course, speciality,
suitability, studyfor
WHERE sNumber = stStudent and stCourse = cNumber and
sNumber = sfStudent and sfExtent = 'minor' and
sfSpeciality = spId and spId = suSpeciality and
suCourse = stCourse and suPhase = 'expert'
ORDER BY sLname, sFname;```
17. Teachers are paid salaries according to the hours they teach. The sarary is paid in that month the job begings and in two succeeding months. For each teacher, find out the factors of his/her salary on March 2000. Give the name and number of the teacher, name of the course, its starting time in Finnish format as BDATE, type of task and hours allocated. Order the result by teacher and the time.
```     SELECT tLname, tFname, cName, tcTask, tcHours,
to_char(tcBeginDate,'DD.MM.YYYY') AS BDATE
FROM teacher, course, teaching
WHERE tId = tcTeacher and tcCourse = cNumber and
tcBeginDate between '1-JAN-2000' and '31-MAR-2000'
ORDER BY tLname, tFname, BDATE;```
18. Find out the name and number of students that have started their studies before year 1998 but have not passed any course.
```     SELECT sLname, sFname, sNumber
FROM student
WHERE sYearStarted <1998 and
sNumber not in (SELECT stStudent
FROM studying
WHERE stDatePassed is not null)
ORDER BY sLname, sFname;
>```