Write Ten SQL SELECT statements to query the STUDENT schema you created for practice lab2. Your Select Statements should run error-free and should be valid. Submit two separate files: one plain text file (.txt or .sql file) with your SELECT statements only; and the other document (doc/docx/pdf) include both your SELECT 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. 1. Show the number of instructors who have a zip of 10025 and a street number of 518. 2. Display all students who live in the state of MA. List first name, last name, zip, and city. Sort by city (descending), zip (ascending), and last name (ascending). 3. List all zipcodes where at least three students AND at least four instructors reside. Show zip, state and city. 4. List all cities that have 10 or more students and instructors 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. 5. List the instructor id and name of the instructors that teach fewer than 10 sections. 6. List all students (display student_id, first name, last name, and registration date) who registered on or after 2/23/2007 but have not enrolled in any course. 7. Find how many students are enrolled in sections taught by Todd Smythe. 8. 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. 9. List the instructors who teach Advanced Java Programming, without duplication. Show instructor name and course name. 10. Find the minimum, maximum, and average grade of final exams in sections taught by Todd Smythe. the following are the tables in the database: Name Null Type -------------- -------- ------------Instructor table INSTRUCTOR_ID NOT NULL NUMBER(8) SALUTATION VARCHAR2(5) FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) STREET_ADDRESS VARCHAR2(50) ZIP VARCHAR2(5) PHONE VARCHAR2(15) CREATED_BY NOT NULL VARCHAR2(30) CREATED_DATE NOT NULL DATE MODIFIED_BY NOT NULL VARCHAR2(30) MODIFIED_DATE NOT NULL DATE Name Null Type ----------------- -------- ------------STUDENT TABLE STUDENT_ID NOT NULL NUMBER(8) SALUTATION VARCHAR2(5) FIRST_NAME VARCHAR2(25) LAST_NAME NOT NULL VARCHAR2(25) STREET_ADDRESS VARCHAR2(50) ZIP NOT NULL VARCHAR2(5) PHONE VARCHAR2(15) EMPLOYER VARCHAR2(50) REGISTRATION_DATE NOT NULL DATE CREATED_BY NOT NULL VARCHAR2(30) CREATED_DATE NOT NULL DATE MODIFIED_BY NOT NULL VARCHAR2(30) MODIFIED_DATE NOT NULL DATE Name Null Type ------------- -------- ------------ZIPCODE TABLE ZIP NOT NULL VARCHAR2(5) CITY VARCHAR2(25) STATE VARCHAR2(2) CREATED_BY NOT NULL VARCHAR2(30) CREATED_DATE NOT NULL DATE MODIFIED_BY NOT NULL VARCHAR2(30) MODIFIED_DATE NOT NULL DATE Name Null Type ------------- -------- ------------ENROLLMENT TABLE STUDENT_ID NOT NULL NUMBER(8) SECTION_ID NOT NULL NUMBER(8) ENROLL_DATE NOT NULL DATE FINAL_GRADE NUMBER(3) CREATED_BY NOT NULL VARCHAR2(30) CREATED_DATE NOT NULL DATE MODIFIED_BY NOT NULL VARCHAR2(30) MODIFIED_DATE NOT NULL DATE Name Null Type --------------------- -------- --------------GRADE TABLE STUDENT_ID NOT NULL NUMBER(8) SECTION_ID NOT NULL NUMBER(8) GRADE_TYPE_CODE NOT NULL CHAR(2) GRADE_CODE_OCCURRENCE NOT NULL NUMBER(38) NUMERIC_GRADE NOT NULL NUMBER(3) COMMENTS VARCHAR2(2000) CREATED_BY NOT NULL VARCHAR2(30) CREATED_DATE NOT NULL DATE MODIFIED_BY NOT NULL VARCHAR2(30) MODIFIED_DATE NOT NULL DATE Name Null Type ------------- -------- ------------GRADE_CONVERSION TABLE LETTER_GRADE NOT NULL VARCHAR2(2) GRADE_POINT NOT NULL NUMBER(3,2) MAX_GRADE NOT NULL NUMBER(3) MIN_GRADE NOT NULL NUMBER(3) CREATED_BY NOT NULL VARCHAR2(30) CREATED_DATE NOT NULL DATE MODIFIED_BY NOT NULL VARCHAR2(30) MODIFIED_DATE NOT NULL DATE Name Null Type --------------- -------- ------------GRADE_TYPE TABLE GRADE_TYPE_CODE NOT NULL CHAR(2) DESCRIPTION NOT NULL VARCHAR2(50) CREATED_BY NOT NULL VARCHAR2(30) CREATED_DATE NOT NULL DATE MODIFIED_BY NOT NULL VARCHAR2(30) MODIFIED_DATE NOT NULL DATE Name Null Type ---------------------- -------- ------------GRADE_TYPE_WEIGHT SECTION_ID NOT NULL NUMBER(8) GRADE_TYPE_CODE NOT NULL CHAR(2) NUMBER_PER_SECTION NOT NULL NUMBER(3) PERCENT_OF_FINAL_GRADE NOT NULL NUMBER(3) DROP_LOWEST NOT NULL CHAR(1) CREATED_BY NOT NULL VARCHAR2(30) CREATED_DATE NOT NULL DATE MODIFIED_BY NOT NULL VARCHAR2(30) MODIFIED_DATE NOT NULL DATE Name Null Type --------------- -------- ------------SECTION TABLE SECTION_ID NOT NULL NUMBER(8) COURSE_NO NOT NULL NUMBER(8) SECTION_NO NOT NULL NUMBER(3) START_DATE_TIME DATE LOCATION VARCHAR2(50) INSTRUCTOR_ID NOT NULL NUMBER(8) CAPACITY NUMBER(3) CREATED_BY NOT NULL VARCHAR2(30) CREATED_DATE NOT NULL DATE MODIFIED_BY NOT NULL VARCHAR2(30) MODIFIED_DATE NOT NULL DATE Name Null Type ------------- -------- ------------COURSE TABLE COURSE_NO NOT NULL NUMBER(8) DESCRIPTION NOT NULL VARCHAR2(50) COST NUMBER(9,2) PREREQUISITE NUMBER(8) CREATED_BY NOT NULL VARCHAR2(30) CREATED_DATE NOT NULL DATE MODIFIED_BY NOT NULL VARCHAR2(30) MODIFIED_DATE NOT NULL DATE Name Null Type -------------- -------- ------------ INSTRUCTOR_ID NOT NULL NUMBER(8) SALUTATION VARCHAR2(5) FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) STREET_ADDRESS VARCHAR2(50) ZIP VARCHAR2(5) PHONE VARCHAR2(15) CREATED_BY NOT NULL VARCHAR2(30) CREATED_DATE NOT NULL DATE MODIFIED_BY NOT NULL VARCHAR2(30) MODIFIED_DATE NOT NULL DATE Name Null Type ----------------- -------- ------------ STUDENT_ID NOT NULL NUMBER(8) SALUTATION VARCHAR2(5) FIRST_NAME VARCHAR2(25) LAST_NAME NOT NULL VARCHAR2(25) STREET_ADDRESS VARCHAR2(50) ZIP NOT NULL VARCHAR2(5) PHONE VARCHAR2(15) EMPLOYER VARCHAR2(50) REGISTRATION_DATE NOT NULL DATE CREATED_BY NOT NULL VARCHAR2(30) CREATED_DATE NOT NULL DATE MODIFIED_BY NOT NULL VARCHAR2(30) MODIFIED_DATE NOT NULL DATE Name Null Type ------------- -------- ------------ ZIP NOT NULL VARCHAR2(5) CITY VARCHAR2(25) STATE VARCHAR2(2) CREATED_BY NOT NULL VARCHAR2(30) CREATED_DATE NOT NULL DATE MODIFIED_BY NOT NULL VARCHAR2(30) MODIFIED_DATE NOT NULL DATE Name Null Type ------------- -------- ------------ STUDENT_ID NOT NULL NUMBER(8) SECTION_ID NOT NULL NUMBER(8) ENROLL_DATE NOT NULL DATE FINAL_GRADE NUMBER(3) CREATED_BY NOT NULL VARCHAR2(30) CREATED_DATE NOT NULL DATE MODIFIED_BY NOT NULL VARCHAR2(30) MODIFIED_DATE NOT NULL DATE Name Null Type --------------------- -------- -------------- STUDENT_ID NOT NULL NUMBER(8) SECTION_ID NOT NULL NUMBER(8) GRADE_TYPE_CODE NOT NULL CHAR(2) GRADE_CODE_OCCURRENCE NOT NULL NUMBER(38) NUMERIC_GRADE NOT NULL NUMBER(3) COMMENTS VARCHAR2(2000) CREATED_BY NOT NULL VARCHAR2(30) CREATED_DATE NOT NULL DATE MODIFIED_BY NOT NULL VARCHAR2(30) MODIFIED_DATE NOT NULL DATE Name Null Type ------------- -------- ------------ LETTER_GRADE NOT NULL VARCHAR2(2) GRADE_POINT NOT NULL NUMBER(3,2) MAX_GRADE NOT NULL NUMBER(3) MIN_GRADE NOT NULL NUMBER(3) CREATED_BY NOT NULL VARCHAR2(30) CREATED_DATE NOT NULL DATE MODIFIED_BY NOT NULL VARCHAR2(30) MODIFIED_DATE NOT NULL DATE Name Null Type --------------- -------- ------------ GRADE_TYPE_CODE NOT NULL CHAR(2) DESCRIPTION NOT NULL VARCHAR2(50) CREATED_BY NOT NULL VARCHAR2(30) CREATED_DATE NOT NULL DATE MODIFIED_BY NOT NULL VARCHAR2(30) MODIFIED_DATE NOT NULL DATE Name Null Type ---------------------- -------- ------------ SECTION_ID NOT NULL NUMBER(8) GRADE_TYPE_CODE NOT NULL CHAR(2) NUMBER_PER_SECTION NOT NULL NUMBER(3) PERCENT_OF_FINAL_GRADE NOT NULL NUMBER(3) DROP_LOWEST NOT NULL CHAR(1) CREATED_BY NOT NULL VARCHAR2(30) CREATED_DATE NOT NULL DATE MODIFIED_BY NOT NULL VARCHAR2(30) MODIFIED_DATE NOT NULL DATE Name Null Type --------------- -------- ------------ SECTION_ID NOT NULL NUMBER(8) COURSE_NO NOT NULL NUMBER(8) SECTION_NO NOT NULL NUMBER(3) START_DATE_TIME DATE LOCATION VARCHAR2(50) INSTRUCTOR_ID NOT NULL NUMBER(8) CAPACITY NUMBER(3) CREATED_BY NOT NULL VARCHAR2(30) CREATED_DATE NOT NULL DATE MODIFIED_BY NOT NULL VARCHAR2(30) MODIFIED_DATE NOT NULL DATE Name Null Type ------------- -------- ------------ COURSE_NO NOT NULL NUMBER(8) DESCRIPTION NOT NULL VARCHAR2(50) COST NUMBER(9,2) PREREQUISITE NUMBER(8) CREATED_BY NOT NULL VARCHAR2(30) CREATED_DATE NOT NULL DATE MODIFIED_BY NOT NULL VARCHAR2(30) MODIFIED_DATE NOT NULL DATE