Write 10 ORACLE SQL Developer SELECT statements to query the STUDENT schema. The Select Statements should run
Question:
Write 10 ORACLE SQL Developer SELECT statements to query the STUDENT schema. The Select Statements should run error-free and should be valid.
The below tables are a guide:
GRADE |
STUDENT_ID SECTION_ID GRADE_TYPE_CODE GRADE_CODE_OCCURENCE NUMERIC_GRADE COMMENTS CREATED_BY CREATED_DATE MODIFIED_BY MODIFIED_DATE |
COURSE |
COURSE_NO DESCRIPTION COST PREREQUISTISTE CREATED_BY CREATED_DATE MODIFIED_BY MODIFIED_DATE |
ENROLLMENT |
STUDENT_ID SECTION_ID ENROLL_DATE FINAL_GRADE CREATED_BY CREATED_DATE MODIFIED_BY MODIFIED_DATE |
GRADE_TYPE_WEIGHT |
SECTION_ID GRADE_TYPE_CODE NUMBER_PER_SECTION PERCENT_OF_FINAL_GRADE DROP_LOWEST CREATED_BY CREATED_DATE MODIFIED_BY MODIFIED_DATE |
GRADE_CONVERSION |
LETTER_GRADE GRADE_POINT MAX_GRADE MIN_GRADE CREATED_BY CREATED_DATE MODIFIED_BY MODIFIED_DATE |
GRADE_TYPE |
GRADE_TYPE_CODE DESCRIPTION CREATED_BY CREATED_DATE MODIFIED_BY MODIFIED_DATE |
INSTRUCTOR |
INSTRUCTOR_ID SALUTATION FIRST_NAME LAST_NAME STREET_ADDRESS ZIP PHONE CREATE_BY CREATED_DATE MODIFIED_BY MODIFIED_DATE |
SECTION |
SECTION_ID COURSE_NO SECTION_NO START_DATE_TIME LOCATION INSTRUCTOR_ID CAPACITY CREATED_BY CREATED_DATE MODIFIED_BY MODIFIED_DATE |
STUDENT |
STUDENT_ID SALUTATION FIRST_NAME LAST_NAME STREET_ADDRESS ZIP PHONE EMPLOYER REGISTRATION_DATE CREATED_BY CREATED_DATE MODIFIED_BY MODIFIED_DATE |
ZIPCODE |
ZIP CITY STATE CREATED_BY CREATED_DATE MODIFIED_BY MODIFIED_DATE |
Q1. List all courses that have "Intro to Information Systems" as prerequisite. (HINT: use a subquery)
Q2. List all courses with above average cost. Display course description, cost, and the average cost of all courses. (HINT: use a subquery)
Q3. For each zip that has at least one instructor, list the total number of instructors in that zipcode.
Q4. For each city in the state of CT, list the total number of students live in that city. Display city, state, number of students in descending order.
-- AGGREGATION OF FULL RESULT SET
Q5. Find the minimum, maximum, and average grade of final exams in sections taught by Todd Smythe.
Q6: For all students who took "Intro to Information Systems", calculate the highest, lowest, and average midterm exam grade for each section. Display Section No and calculation results.
-- TABLE JOIN WITH HAVING-CLAUSE
Q7. List the instructor id and name of the instructors that teach fewer than 10 sections regardless of student enrollment.
Q8. Show which city has the most students. Display city and state, and number of students.
Q9: List all zipcodes where at least three students AND at least four instructor reside. Show zip, state and city.
Q10: List all cities that have 10 or more students and instructor combined. Show city, state, number of student residents, number of instructor residents, and total student/instructor residents in that city. Sort by total in descending order.
Expert Answer:
Q1 List all courses that have Intro to Information Systems as a prerequisite using a subquery SELECT DISTINCT cCOURSENO cDESCRIPTION FROM COURSE c WHERE cCOURSENO IN SELECT c2COURSENO FROM COURSE c2 W... View the full answer
Data Modeling and Database Design
ISBN: 978-1285085258
2nd edition
Authors: Narayan S. Umanath, Richard W. Scammel
