# Database: University Example: Exercises: SELECT 3

Name __________________________________________
These questions all apply to the University database.

1. (2 points) List all students (last name then first name) in ascending order.

       SELECT student_lname, student_fname
         FROM Student
         ORDER BY student_lname, student_fname;

2. (2 points) List all education centers (`edc_name`) that are in Italy (`edc_country` 'it').

       SELECT edc_name
         FROM Ed_Center
         WHERE edc_country = 'it';

3. (2 points) List all students (last name then first name) in ascending order, who are taking course id PSYC100.

       SELECT student_lname, student_fname
         FROM Student, Enrollment, Section
         WHERE Student.student_id = Enrollment.student_id
           AND Enrollment.sec_id  = Section.sec_id
           AND Section.crs_id = 'PSYC100'
         ORDER BY student_lname, student_fname;

4. (3 points) List all students (last name then first name) in ascending order, who are taking course id PSYC100 in Ed Center id "Landstuhl".

       SELECT student_lname, student_fname
         FROM Student, Enrollment, Section
         WHERE Student.student_id = Enrollment.student_id
           AND Enrollment.sec_id  = Section.sec_id
           AND Section.crs_id = 'PSYC100'
           AND Section.edc_id = 'Landstuhl'
         ORDER BY student_lname, student_fname;

5. (3 points) What is the average GPA of students in CMIS340 section 1 (`sec_num`) at) Rota (`edc_id`?

       SELECT AVG(student_gpa)
         FROM Student, Enrollment, Section
         WHERE Student.student_id = Enrollment.student_id
           AND Enrollment.sec_id = Section.sec_id
           AND crs_id = "CMIS340"
           AND sec_num = 1
           AND edc_id = "Rota";

6. (3 points) List the instructors (last name, first name in ascending order) and courses (`crs_id`) for all one credit (`crs_credit`) courses.

       SELECT ins_lname, ins_fname, crs_id
         FROM Instructor, Section, Course
         WHERE Instructor.ins_id = Section.ins_id
           AND Section.crs_id = Course.crs_id
           AND crs_credit = 1
         ORDER BY ins_lname, ins_fname;