Instructions
Write 10 SQL statements against the STUDENT schema. Your statements should run error-free and should be valid. Submit two separate files: one plain text file (.txt or .sql file) with your statements only; and the other document (doc/docx/pdf) include both your statements and your query results (copy and paste text or screen shots). The instructor and TA should be able to run your plain text source file as script and generate the same output as shown in your result document.
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, DATEBottom of Form
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)
Write 10 SQL statements against the STUDENT schema. Your statements should run error-free and should be valid. Submit two separate files: one plain text file (.txt or .sql file) with your statements only; and the other document (doc/docx/pdf) include both your statements and your query results (copy and paste text or screen shots). The instructor and TA should be able to run your plain text source file as script and generate the same output as shown in your result document.
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)
