Question

Write Ten SQL SELECT statements to query the STUDENT schema you created for practice lab.

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.

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.

STUDENT DATABASE
SCHEMA
ENR_SECT_FK
ENR_STU_FK
GR_ENR_FK
GR_GRTW_FK
GRTW_GRTYP_FK
GRTW_SECT_FK
INST_ZIP_FK
SECT_CRSE_FK
SECT_INST_FK
STU_ZIP_FK
CRSE_CRSE_FK
ENROLLMENT
STUDENT_ID (PK)(FK) NUMBER(8,0) NOT NULL
SECTION_ID (PK)(FK) NUMBER(8,0) NOT NULL
ENROLL_DATE DATE NOT NULL
FINAL_GRADE NUMBER(3,0) NULL
CREATED_BY VARCHAR2(30) NOT NULL
CREATED_DATE DATE NOT NULL
MODIFIED_BY VARCHAR2(30) NOT NULL
MODIFIED_DATE DATE NOT NULL
GRADE
GRADE_CODE_OCCURRENCE (PK) NUMBER(38,0) NOT NULL
STUDENT_ID (PK)(FK) NUMBER(8,0) NOT NULL
SECTION_ID (PK)(FK) NUMBER(8,0) NOT NULL
GRADE_TYPE_CODE (PK)(FK) CHAR(2) NOT NULL
NUMERIC_GRADE NUMBER(3,0) NOT NULL
COMMENTS VARCHAR2(2000) NULL
CREATED_BY VARCHAR2(30) NOT NULL
CREATED_DATE DATE NOT NULL
MODIFIED_BY VARCHAR2(30) NOT NULL
MODIFIED_DATE DATE NOT NULL
GRADE_CONVERSION
LETTER_GRADE (PK) VARCHAR2(2) NOT NULL
GRADE_POINT NUMBER(3,2) NOT NULL
MAX_GRADE NUMBER(3,0) NOT NULL
MIN_GRADE NUMBER(3,0) NOT NULL
CREATED_BY VARCHAR2(30) NOT NULL
CREATED_DATE DATE NOT NULL
MODIFIED_BY VARCHAR2(30) NOT NULL
MODIFIED_DATE DATE NOT NULL
GRADE_TYPE
GRADE_TYPE_CODE (PK) CHAR(2) NOT NULL
DESCRIPTION VARCHAR2(50) NOT NULL
CREATED_BY VARCHAR2(30) NOT NULL
CREATED_DATE DATE NOT NULL
MODIFIED_BY VARCHAR2(30) NOT NULL
MODIFIED_DATE DATE NOT NULL
GRADE_TYPE_WEIGHT
SECTION_ID (PK)(FK) NUMBER(8,0) NOT NULL
GRADE_TYPE_CODE (PK)(FK) CHAR(2) NOT NULL
NUMBER_PER_SECTION NUMBER(3,0) NOT NULL
PERCENT_OF_FINAL_GRADE NUMBER(3,0) NOT NULL
DROP_LOWEST CHAR(1) NOT NULL
CREATED_BY VARCHAR2(30) NOT NULL
CREATED_DATE DATE NOT NULL
MODIFIED_BY VARCHAR2(30) NOT NULL
MODIFIED_DATE DATE NOT NULL
INSTRUCTOR
INSTRUCTOR_ID (PK) NUMBER(8,0) NOT NULL
SALUTATION VARCHAR2(5) NULL
FIRST_NAME VARCHAR2(25) NULL
LAST_NAME VARCHAR2(25) NULL
STREET_ADDRESS VARCHAR2(50) NULL
PHONE VARCHAR2(15) NULL
CREATED_BY VARCHAR2(30) NOT NULL
CREATED_DATE DATE NOT NULL
MODIFIED_BY VARCHAR2(30) NOT NULL
MODIFIED_DATE DATE NOT NULL
ZIP (FK) VARCHAR2(5) NULL
SECTION
SECTION_ID (PK) NUMBER(8,0) NOT NULL
SECTION_NO NUMBER(3,0) NOT NULL
START_DATE_TIME DATE NULL
LOCATION VARCHAR2(50) NULL
CAPACITY NUMBER(3,0) NULL
CREATED_BY VARCHAR2(30) NOT NULL
CREATED_DATE DATE NOT NULL
MODIFIED_BY VARCHAR2(30) NOT NULL
MODIFIED_DATE DATE NOT NULL
COURSE_NO (FK) NUMBER(8,0) NOT NULL
INSTRUCTOR_ID (FK) NUMBER(8,0) NOT NULL
STUDENT
STUDENT_ID (PK) NUMBER(8,0) NOT NULL
SALUTATION VARCHAR2(5) NULL
FIRST_NAME VARCHAR2(25) NULL
LAST_NAME VARCHAR2(25) NOT NULL
STREET_ADDRESS VARCHAR2(50) NULL
PHONE VARCHAR2(15) NULL
EMPLOYER VARCHAR2(50) NULL
REGISTRATION_DATE DATE NOT NULL
CREATED_BY VARCHAR2(30) NOT NULL
CREATED_DATE DATE NOT NULL
MODIFIED_BY VARCHAR2(30) NOT NULL
MODIFIED_DATE DATE NOT NULL
ZIP (FK) VARCHAR2(5) NOT NULL
ZIPCODE
ZIP (PK) VARCHAR2(5) NOT NULL
CITY VARCHAR2(25) NULL
STATE VARCHAR2(2) NULL
CREATED_BY VARCHAR2(30) NOT NULL
CREATED_DATE DATE NOT NULL
MODIFIED_BY VARCHAR2(30) NOT NULL
MODIFIED_DATE DATE NOT NULL
COURSE
COURSE_NO (PK) NUMBER(8,0) NOT NULL
DESCRIPTION VARCHAR2(50) NOT NULL
COST NUMBER(9,2) NULL
CREATED_BY VARCHAR2(30) NOT NULL
CREATED_DATE DATE NOT NULL
MODIFIED_BY VARCHAR2(30) NOT NULL
MODIFIED_DATE DATE NOT NULL
PREREQUISITE (FK) NUMBER(8,0) NULL

Answer 1

4 Select ZIP.city,ZIP.State,count(STUDENT.ZIP) "Number of Student",count(INSTRUCTOR.ZIP) "Number of Instructor"(STUDENT.ZIP+INSTRUCTOR.ZIP)"Total Student/instructor" from ZIPCODE inner join STUDENT on ZIPCODE.ZIP=STUDENT.ZIP inner join INSTRUCTOR on INSTRUCTOR.ZIP =ZIPCODE.ZIP group by ZIPCODE.ZIP having count(INSTRUCTOR.ZIP)>10 and count(STUDENT.ZIP)>10 order by count(*) desc;
5) Select First_name,Last_Name from Instrucor where Instructor_ID in(Select INSTRUCTOR_ID from Section group by INSTRUCTOR_ID having count(*)<10);
7)Select (*) "Number of Student" from Enrollment STUDENT_ID in(Select ENROLLMENT.STUDENT_ID from ENROLLMENT inner join on SECTION.SECTION_NO=ENROLLMENT.SECTION_NO inner join INSTRUCTOR on INSTRUCTOR.INSTRUCTOR_ID=SECTION.INSTRUCTOR_ID where INSTRUCTOR.FIRST_NAME='Todd' and INSTRUCTOR.LAST_NAME ='Smythe');

i am working on remaining one please wait for sometimes i will upload it as soon as it completed.