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

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.

Step by Step Solution

★★★★★

3.48 Rating (151 Votes )

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock

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 full answer

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Databases Questions!

Newtons law of cooling is given by theta =theta.o e-^kt, where the excess of temperature at zero time is theta.o oC and at timer t seconds is theta. oC. Determine the rate of change of temperature...

Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...

There is an XYZ Company, which purchases some parts from vendors to produce some products. It has several departments, marketing sites, and parts supply vendors in the company. 1) For each...

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

Scenario and Summary The lab for this week addresses taking a logical database design (data model) and transforming it into a physical model (tables, constraints, and relationships). As part of the...

These statements are not working in Oracle SQL Developer mySQL Write 1 0 SQL statements against the STUDENT schema. Your statements should run error - free and should be valid. Submit two separate...

CIS-336: Lab 3: Building the Physical Model LAB OVERVIEW Scenario and Summary The lab for this week addresses taking a logical database design (data model) and transforming it into a physical model...

The relational schema for the Academics database is as follows: DEPARTMENT(deptnum, descrip, instname, deptname, state, postcode) ACADEMIC(acnum, deptnum*, famname, givename, initials, title)...

A 25-foot string of lights will be attached to the top of a 20-foot pole for a holiday display. How far from the base of the pole should the end of the string of lights be anchored?

What is municipal solid waste (MSW)? List some of the components of MSW. Are industrial, hazardous, and construction waste considered as MSW?

SCalcET 9 1 6 . 6 . 0 3 3 . Find a n equation o f the tangent plane t o the given parametric surface a t the specified point. x = u + v , y = 9 u 2 , z = u - v ; , ( 2 , 9 , 0 )

A ball was kicked with an initial speed of 25 m/s with an initial angle of 60 relative to the horizontal plane. The distance between the place where the ball was initially kicked to the foot of the...

Dr Cr $'000 $'000 Buildings at cost 740 Buildings, accumulated depreciation, 1 November 20X6 60 Plant at cost 220 Plant, accumulated depreciation, 1 November 20X6 110 Land at cost 235 Bank balance 50...

1. Three different chemicals have the following LD 50 for killing mosquito larvae and minnows in a freshwater ecosystem. Chemical LD 50 for mosquito larvae (mg/L) LD 50 for minnows (mg/L) A 0.200...

Identify external and internal environmental factors which impact on an entity and explain their relevance for the entity's external audit

1. -----------------------------advertising is when a company compares its brand indirectly with other brands. a comparative b informative c patronage d persuasive e competitive...

In capital rationing decisions, which of the following would be used first in the screening process?Group of answer choicesRanking of proposalsCash paybackInternal rate of returnNet present value

What information is lost by the use of the information-reducing grammar?

Consider the instance of the relation SHIRT (Shirt#, Color, Size) where Shirt# is equivalent to a style number (e.g., style number 341 might be a shirt with a button-down collar while style number...

What is the difference between a Cross Join, an Inner Join, and an Outer Join?

What is loading?

One approach that can be effective in reducing the impact on production bottlenecks is to use smaller transfer lot sizes as in tire Theory of Constraints. Explain how small transfer lot sizes can...

The fol1owing table contains information concerning four jobs that are waiting for processing at a machine. a. Sequence the jobs using priority rules (I) SPT, and (2) EDD. b. For each of the priority...