List all cities that have 10 or more students and instructors combined. Show cit

Question

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.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        

Explanation / Answer

If you get any error or doubts, please give me comment...

SELECT CITY, STATE, STU.count AS no_of_students, INST.count AS no_of_instructors, (STU.count+INST.count) AS total
FROM ZIPCODE Z, (SELECT ZIP, COUNT(*) AS count
    FROM Instructor
    GROUP BY ZIP) AS STU,
    (SELECT ZIP, COUNT(*) AS count
    FROM STUDENT
    GROUP BY ZIP) AS INST
WHERE Z.ZIP = STU.ZIP AND INST.ZIP = Z.ZIP AND STU.count+INST.count >= 10;

Related Questions

Navigate

• Browse (All)
• Browse L
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.