Basics of Databases, Task set 2
 
19. Which courses are not in schedule for year 2000. Give the name and the
    number. 

20. Find out the names and numbers for courses that have or have had
    students that live in Vantaa. Sort the result based on the name of the
    course.

21. Find out the names and numbers for courses that do not have or have
    not had students that live in Vantaa. Sort the result based on the name
    of the course. 

22. Find out the names and numbers for courses that have or have had only
    such students that live in Helsinki. 

23. Find out the other specialities that teachers of Software
    engineering (SWENG) are able to teach. Include the identifier and name
    of the speciality in your answer. 

24. How many teachers have been registed into the system. Give the
    answer as column CNT. 

25. Teaching task are categorized by task type (tctask).How many types
    are in use. Give the answer as column TASKTYPE. 

26. How many students studied the course Network management that started
    on 10.1.2000. Give the answer as column STUDENTS. 

27. How many students stydied somethig in spring 2000? Give the answer as
    column STUDENTS. 

28. Find out the minimum, maximun and average free for courses in
    columns LOW, HIGH and MID. Give the average as rounded to an integer. 

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. 

30. Find out the total number of credits achieved by each student. Give
    the name and number of the students. 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. 

31. Find out which course has the highest fee. Give the name of the
    course, its starting date and its fee. 

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. 

33. Which speciality has the biggest amount of major students. Give the
    name of the speciality and the number of students. Five the number of
    students in column STUDENTS. 

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. 

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. 

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