Introduction to Databases (581328-9) - Fall 2000

Set 1: tasks and their solutions

1. List the contents of table Teacher.

     SELECT * 
     FROM teacher;

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 t1.tcTask = 'lecture') 
           and 
           (tId = t2.tcTeacher and t2.tcCourse = cNumber 
            and t2.tcTask = 'practice') 
           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;