Assignment #2
String and Aggregate Functions
1. Create a list of all students with middle initials (specifically, with a first name AND then a middle initial) AND remove the
middle initial from the first name. Take care: some first names are a single initial only. [HINT: use the INSTR function
embedded within other functions). (7 rows).
SELECT first_name Name,
SUBSTR(first_name,1,INSTR(first_name,’ ‘) – 1) “Instr Name”
2. Create a list of courses, sections and their capacity. Produce the result in the following format: (78 rows)
Course and Section CAPACITY
——————– ———- —————————————-
350 Section 3 ———- 25
10 Section 2 ———- 15
20 Section 2 ———- 15
…
3. Count the number of students with a “v” in their last name. Use INSTR to produce the answer. (16)
4. Show the average cost of a course with no prerequisites (1195)
5. Display the course number, number of sections and total capacity for courses
having more than 3 sections.( 9 rows).
6. List all instructors and how many sections they teach. (8 rows).
7. Do #6 but limit it to instructors teaching more than 9 sections. (6 rows).
SELECT instructor_id ID, COUNT(*)