Write  SQL statements against the STUDENT schema in Oracle. Statements should run error-free and should be valid. 

 

1. List all instructors. Show salutation in all lowercase, last name in all uppercase, and phone number in the format '(xxx) xxx-xxxx'. ( use LOWER, UPPER and SUBSTR function)

2. List all students (display student_id, first name, last name, street address, zip code, city, and state) who live in New York, NY. Sort results by last name, and then first name, in descending order.

3. Display the city in which each instructor lives. List first name, last name, zip, city, and state. Display "N/A" for zip, city, and state if an instructor does not have a zip code. (left join INSTRUCTOR and ZIPCODE; use NVL function)

4. Show the number of instructors who live in NY state and have a street number of 518. (use string functions such as SUBSTR and INSTR)

5. Display the lowest, highest, and average numeric grade of Project grade type. ( use the MIN, MAX, and AVG function; join GRADE and GRADE_TYPE)

-- LEFT JOIN, DATE

6. List all students (display student_id, first name, last name, and registration date) who registered on or before 2/15/2007 but have not enrolled in any course. (left outer join STUDENT and ENROLLMENT; use the TO_DATE function)

-- COUNT WITH 3-TABLE JOIN

 

7. Find how many students are enrolled in sections taught by Todd Smythe. 

INSTRUCTOR, SECTION, and ENROLLMENT) -- 4-TABLE JOIN

 

 

8. Show all students who are taking, or have taken the course "Advanced Java Programming." List student name, enrollment date, and section location. (HINT: join COURSE, SECTION, ENROLLMENT, and STUDENT)

-- 4-TABLE JOIN

9. List the students who have received any numeric grade score of at least 95 in an Advanced Java Programming course. Show student name, the grade type code, and the numeric grade.

-- 4-TABLE JOIN WITH DISTINCT

10. List the instructors who teach Advanced Java Programming (having a section with students enrolled), without duplication. Show instructor name and course name. (HINT: use the DISTINCT keyword)