[oracle]서브쿼리

/*서브 쿼리
개념 : 부속질의어로 서 sql 명령문 내에서 사용되는 select 문을 말함.

주의사항 : () 안에 작성한다.
          반환되는 행 즉 컬럼의 수가 연산자가 기대하는 칼럼의 수와 일치해야한다.
          order by  절을 사용하지 않는다.
          외부 쿼리 즉 , 메인 쿼리 에서 사용되는 칼럼명 을 내부 서브쿼리에서 사용할수 있           으나 내부에 선언된 칼럼에 대해서 외부에서 사용할수는 없다.
          최대 255개 까지 사용할수 있다.
         
구분 : 하나의 컬럼을 반환하는 단일 서브쿼리와 여러개의 행을 반환하는 다중 서브쿼리로 구분된다.

연산자 : 하나의 값을 비교 하므로 <> = != is null 등으로 하나에 값만 비교한다.
*/


/*1 단일 서브 쿼리*/
/*예*/
describe sg_scores;

select * from sg_scores
where course_id = 'SQL';

select * from sg_scores
where course_id in ('L1031','SQL');

select *
from sg_scores as t1
where course_id ='L1031'
and score > (
                  select avg(score)
                  from t1
                  where course_id = 'L1031'
                  );
                 
select * from sg_scores
where course_id  = 'L1031'
and score = (
          select max(score) from sg_scores where course_id = 'L1031'
          );

/* 다중 서브쿼리
서브쿼리의 결과로 여러가지 행이 반환되는 쿼리
연산자 : in , any all, exsts

in : 서브쿼리의 결과중 하나라도 메인쿼리의 비교조건과 일치하면 참

any,some  : 서브쿼리의 결과중 하나 이상이 메인쿼리의 비교조건과 일치하면 참

all : 서브쿼리의 결과가 모두 일치하면 참

extsts : 메인 쿼리의 비교조건이 서브쿼리의 결과중에서 하나라도 반환하면 참이 되는 연산자

*/

/* in 연산자 예 */


/* */
select * from sg_scores;

select grade , course_id , score, score_assigned
from sg_scores
where (score_assigned , score) in (
                                  select score_assigned, max(score)
                                  from sg_scores
                                  GROUP by score_assigned
                                  );
                                 
/*1.기간별 점수를 먼저 출력*/
 select score_assigned, score
                                  from sg_scores;
/*2. group by 연산자를 통해 중복된 일자를 제거한다
    중복된 일자가 제거되면 일자별로 하나의 스코어 값만을 갖게끔 score 값을 행당 하나만 출력되게끔 마춰줘야 한다.
   
*/

/*
  3. max 함수  : 그룹함수로써 group by 절과 함께 쓰이며 중복된값을 가지는 지정된 칼럼의 최대값 1개만을 뽑아낸다.
*/
select score_assigned, max(score)
from sg_scores
GROUP by score_assigned;

/*
  4. 서브쿼리 작성 : 최고성적을 받은 일자와 점수를 뽑아내고 그점수를 가진 학생정보를 출력함.
     주의 : where 절에 비교할 내용이 () 안에 뽑아오는 컬럼과 같은 데이터타입을 가진 순서대로 정렬이 되어야한다.
     주의2 : 단일행 서브쿼리에서 in 연산자를 사용할때에는 서브쿼리절에서 가져오는 값들이 행하나에 대한 여러값을 가져오므로 비교가 가능하지만
            다중엔 서브쿼리에서는 in 연산자를 통해 하나의 컬럼값을 다중으로 여러컬럼값을 가져온것과 in 연산자 사용을 할수가 없다
            칼럼 개수와 순서가 일치해야만 정상적으로 값에 비교가 가능하다.
*/
select student_id "학번", course_id "과목코드" , score "성적", score_assigned "성적취득일자"
from sg_scores
where (score_assigned , score) in (select score_assigned, max(score)
                                  from sg_scores
                                  GROUP by score_assigned  );
                                 
                                 
select student_id "학번", course_id "과목코드" , score "성적", score_assigned "성적취득일자"
from sg_scores
where (score_assigned , score) in (select score_assigned, max(score)
                                  from sg_scores
                                  GROUP by score_assigned  );
                                 
                                 
select student_id "학번", course_id "과목코드" , score "성적", score_assigned "성적취득일자"
from sg_scores
where score in (select score from sg_scores where score_assigned = '10/12/27');


select student_id "학번", course_id "과목코드" , score "성적", score_assigned "성적취득일자"
from sg_scores
where score in (select score from sg_scores where score_assigned = '10/12/27');

/*93 87 105 98 68 98 */
select * from sg_scores  where score_assigned = '10/12/27';


/*
 any, some 연산자 : 방식은 비교할 컬럼을 서브쿼리에서 가져온 값에 참조하는 방식으로 in 과 비슷 하지만
 값이 일치해야하는 in 의 정적 조건이 아니라 수식계산이 가능하다.


*/
select * from sg_scores;

/*
특정 과목에 최하점에 보다 높은 학점을 받은 학생 점수 정보 출력
*/

/*
1.특정과목에 모든 점수를 구한다.
*/
select score from sg_scores where course_id = 'L1031';

/*
2. 96 , 96 , 85 , 77 네개의 행이 검색이 되었다 메인 쿼리 절에서 score 값은 네가지 값들 보다 클 경우 식을 만족하고 열을 출력하게된다.
*/
select * from sg_scores where score > any (select score from sg_scores where course_id = 'L1031')
and  course_id = 'L1031';

/*
동일한 결과
*/
select * from sg_scores where score > some (select score from sg_scores where course_id = 'L1031')
and  course_id = 'L1031';

/*
all은 검색된 네가지 점수보다 모두 커야만이 로우를 출력할수 있으므로 같은 테이블이기 떄문에 아무것도 출력이 되지 않는다.
*/
select * from sg_scores where score > all (select score from sg_scores where course_id = 'L1031')
and  course_id = 'L1031';