CSCI A114 / INFO I111 Lecture Notes Twelve First semester 2001-2002

In class today we will solve the problem posed last time.

We will also work on the following problem.

Assume the following database:

Justify it.

Think of some examples of data that could go in it.

Then write the following queries:

1. List the names of all courses, their department, and the number of credits for each.

2. List the names, home towns, and home states of all students.

3. List all values in the COURSES table.

4. List all values in the TEACHERS table.

5. List the course name, department, and number of credits for all three credit courses.

6. List the names and salaries of teachers earning more than $30,000.

7. Who are all the male students.

8. List the names and addresses of all female students from Chicago.

9. Which Math courses have 3 or more credits?

10. List the name, sex, city, and state for all students who are either from Connecticut or from a city called Erie.

11. List the names, cities, states and zip codes of all students whose zip codes are between 20000 and 29999 or who live in a city called Erie.

12. List the names and states of all students who are not from Illinois.

13. List the names and salaries of teachers who make between $30,000 and $35,000.

14. List the names, cities, states and zip codes of students whose zip codes are NOT between 20000 and 29999.

15. List the name and sex of every student whose name begins with 'Jo'.

16. List the names and home states of all students from states that begin with the letter 'C';

17. List the names and phone numbers of all teachers whose phone numbers do NOT begin with 257.

18. List the names and departments of all Math and English courses.

19. List the names, cities, and states of all students who are NOT from California or Illinois.

20. List the entire contents of any records in the ENROLLS table with a NULL value for grade.

21. List the names, cities, states, and zip codes for all students whose zip codes are not NULL.

22. Give an alphabetical list of teachers and their phone numbers.

23. Give an alphabetical list of teachers and their phone numbers.

24. Give a list of teachers and their phone numbers in reverse alphabetical order.

25. List the names, cities, and states of all students ordered first by state, then by city within that state.

26. List all three credit courses alphabetically by department.

27. What would each teacher's salary be if he or she received a 5.5% cost of living increase and a $1,500 merit increase?

28. What is the largest salary paid to a teacher?

29. What is the lowest grade earned by a student?

30. What is the total of all teacher salaries?

31. What is the total salary for all teachers tearning over $30,000?

32. What is the average of all teacher salaries?

33. How many students are there?

34. How many teachers earn over $30,000?

35. How many different states do students come from?

36. List the number of students enrolled in each course?

37. List the number of courses taken by each student.

38. List the average grade and number of courses taken by each student.

39. Assuming a fee of $450.00 per course, determine each student's total tuition bill.

40. List the average grade and number of students for each section of each course.

41. List the average grade and number of courses taken by students with an average grade of more than 2.5.

42. List the average grade and number of students for each section with more than four students.

43. List all teacher names along with all the values for teacher number and course number contained in the SECTIONS table.

44. Which teachers teach which course? (List the teachers by name).

45. What is the maximum enrollment in each section of each course?

46. List the grades received by each student.

47. Which students from California or Illinois got 'A's or 'B's?

48. Assuming that tuition is $450.00 per course, what is Larry Bird's total tuition bill? (Larry Bird is a student).

49. List the teacher for and number of students in each section. Order this list of courses by number of students, from largest to smallest.

50. List the names and grades of all students enrolled in section 1333 of A114.

51. For each department, list its courses and sections together with the number of students in each.

52. List the names of all teachers, along with the names and numbers of the courses taught by each.

53. In which courses did any female student get an A? (List both the courses and the students by name).

54. List the names of all teachers and their departments, ordered alphabetically by department.

55. List each student's grade for each course. Order the list alphabetically by course name, then, for each course, by student name.

56. Which courses are the students from Pennsylvania, California, and Connecticut taking?

57. List alphabetically the names and grades of all male students enrolled in M119.

58. How many students are in Dr. Engle's English class?

59. Produce a list of the names and addresses of Dr. Horn's students. Order the list alphabetically by student name.

60. Produce a roster for each course by teacher, listing the courses and sections they teach along with the names of all students enrolled in each.

61. What is the total number of students taught by each teacher?

62. List in descending order the total number of credits awarded for each course.

63. How many students of each sex are enrolled in A201?

We'll cover them slowly, and completely, and the final will contain SQL queries like these.