Show the number of instructors who have a zip of 10025 and a street number of 518. Display all students who live in the state of MA. List first...

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