Programming Homework Help

Q1. List all instructors. Show salutation in all lower cases, last name in all upper cases and phone number in the format of ‘(xxx) xxx-xxxx’. (HINT: use LOWER, UPPER and SUBSTR function)

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

Q3. 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 zipcode. (HINT: left join INSTRUCTOR and ZIPCODE; use NVL function)

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

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

— LEFT JOIN, DATE

Q6. 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. (HINT: left outer join STUDENT and ENROLLMENT; use the TO_DATE function)

— COUNT WITH 3-TABLE JOIN

Q7. Find how many students are enrolled in sections taught by Todd Smythe. (HINT: join INSTRUCTOR, SECTION, and ENROLLMENT)

— 4-TABLE JOIN

Q8. 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

Q9. 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

Q10. 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)