Nested Query - Individual Assignment #2

Assignment

 

Make a total of three retrieval queries to get an answer for the same problem definition, by referring the directions below. Your problem definition and its three different query statements will be evaluated. (9 points: 3-3-3)

 

Directions:

-  Define an interesting problem situation. Design it carefully (evaluation point).

-  Make a query that implements Type I nested query for this problem situation. You can add some explanation for the query logic and result.

-  Make a query that implements Type II nested query for this problem situation. You can add some explanation for the query logic and result.

-  Make a query without using nested queries; for example, you can use outer join, ordinary join, or set operator. You can add some explanation for the query logic and result.

 

 

 

 

 

Submission

 

Define an interesting problem situation.

 

Professor Yoon is recruiting intern researchers among undergraduates. In order to be an outstanding researcher, it is necessary to have a deep understanding of a particular subject. He decided to select among the students who have received the top 30% grade in enrollment relation regardless offering.

 

First, we have to know low limit of top 30% grade in enrollment relation.

 

i. Use ‘percent_rank()’ function.

ii. Use that result relation in ‘from’ clause. It is ‘3.4’ that low limit of top 30% grade. It can be nested in the queries in below directions instead of using the number ‘3.4’ because the number ‘3.4’ can change.

 

 

1. Make a query that implements Type I nested query.

 

2. Make a query that implements Type II nested query.

 

 

3. Make a query without using nested queries.