Assignment 1

Wayland Holfeltz

CS2550 Spring 2013

1) List the names (first and last name) of all instructors in alphabetical order (last name then first name).

SELECT last_name, first_name

from instructor;

2) Provide a list of distinct locations that have been used to teach sections of courses.

Arrange the list in order of location.

SELECT distinct location

from section;

3) List the first and last names of Instructors with a last name starting with "W".

Sort them in alphabetical order.

SELECT last_name, first_name

from instructor

where last_name LIKE 'W%';

4) List the phone number, full name (as one column) and employer for all students with a last name of "Grant".

Sort by phone number.

SELECT phone, first_name||' '||last_name, employer

FROM student

where last_name='Grant';

5 List the course number and course description of all courses that have a prerequisite of course 20.

Arrange in order of course number.

SELECT course_no, description

from course

where prerequisite=20;

6 List the course number, description and cost for all 200 level courses (200-299) costing less than $1100.

Arrange by course number.

SELECT course_no, description, cost

from course

where course_no BETWEEN 200 and 299

and cost=1100;

7 List the course number and section id for all 100 level courses taught in room L211 or L214.

Order by course number and section id.

SELECT course_no, section_id

from section

where course_no between 100 and 199

AND (location=L211

or location=L214);

8 List the course number and section id for classes with a capacity of 10 or 12 (use the IN clause).

Order the list by course number and section id.

SELECT course_no, section_id

from section

where capacity in (10, 12);

9 List the student id and grade for all of the final exam scores (FI) in section id 147.

Arrange the list by student id and grade.

SELECT student_id,...