MYSQL join 예제&문제 10

-- 학적 테이블에 존재하는 학생들 중에서 아직 등록을 못한 학생이 있다. 각 학생에 대하여 학번, 이름, 등록년도, 학기, 이름, 등록년도, 학기, 학생이 등록한 납입금 총액을 출력하라

-- (x ???)

select student.stu_no, stu_name, fee_year, fee_term, fee_pay

from student, fee

where not EXISTS

(select student.stu_no, stu_name, fee_year, fee_term, fee_pay

from student, fee

where student.stu_no = fee.stu_no);

 

 

 

-- 

-- 학적 테이블과 성적테이블을 크로스 조인하여 학번 , 이름, 성적년도, 학기를 출력하라 

select student.stu_no, stu_name, sco_term

from student , score;

 

-- 학적 테이블과 성적테이블을 크로스 조인하여 학번, 이름 , 성적년도, 학기를 출력하라 

-- (단, 학적테이블에 존재하는 20061011 학생만을 출력하라 )

select student.stu_no, stu_name, sco_term

from student , score

where student.stu_no = 20061011;

 

-- 학적테이블에 존재하는 학생들 중에서 등록한 학생의 학번, 이름, 반, 등록년도, 학기, 등록금 총액을 출력하라 

 

 

 (select student.stu_no, stu_name, fee_year, fee_term, fee_pay

from student, fee

where student.stu_no = fee.stu_no);

-- 

--   where 절의 조건 연산자가 = 인경우에 from절에 나오는 좌측에 있는 student 테이블의 모집단이 

-- 우그의 fee테이블의 모집단을 전부 포함하고 있다면 이너 조인 이라고 한다. 

-- 만약, 우측의 모집단이 좌측의 모집단을 포함하고 있다면 아웃터 조인이라고 한다. 

-- 

-- 등록 테이블은 학적테이블에 존재하는 학번을 전체적으로 가지고 있지 않기 때문에 아웃터 조인은 

-- 동일한 결과를 반환한다. 

-- 

-- 아웃터 조인 예)

  

 (select student.stu_no, stu_name, fee_year, fee_term, fee_pay

from fee,student

where student.stu_no = fee.stu_no);

 

 

 

-- 수강테이블에 존재하는 학생의 수강신청년도, 학기, 과목코드, 과목명을 출력하라 

select att_year, att_term, subject.sub_code, sub_name

from attend,subject

where subject.sub_code = attend.sub_code;

 

-- 

-- 재학생이면서 수강신청테이블에 존재하는 학생의 학번, 이름, 수강신청년도, 학기, 과목코드, 과목명, 

-- 교수코드, 교수명을 출력하라 (단, 출력순서는 학번, 수강년도, 수강학기, 수강코드순이고, 과목명과 

-- 교수명을 표제어로 사용하여라 )

select s.stu_no, stu_name, att_year, att_term, sj.sub_code, sub_name, p.prof_code, prof_name

from student s, attend a, professor p, subject sj

where s.stu_no=a.stu_no

and a.sub_code = sj.sub_code

and a.prof_code=p.prof_code

order by s.stu_no, att_year,att_term, sj.sub_code;

 

-- 학적 테이블에 존재하는 모든 학생이 출력될 수 있도록 레프트 아웃 이퀄 조인으로 출력하라 

-- (단, 미등록자는 등록년도를 "미납" , 학기는 0 , 납입금총액도 0으로 출력하라 )

select student.stu_no, stu_name, fee_year, fee_term, fee_pay

from student, fee

where student.stu_no = fee.stu_no

UNION

select stu_no, stu_name, 'minab' , 0,0 

from student 

where stu_no not in

(select stu_no from fee);

-- 등록 테이블에 존재하는 모든 학생이 출력될 수 있도록 학생의 학번, 이름, 등록년도, 학기, 

-- 등록금총액을 출력하고, 단 성명이 없는 학생은 이름을 '무명'으로 출력하라 (라이트 아웃 이퀄 조인 으로 출력하라)

-- 

-- (????????)

select fee.stu_no, stu_name, fee_year, fee_term, fee_pay

from fee, student

where  student.stu_no = fee.stu_no

union

select stu_no, "moomyeng", fee_year, fee_term, fee_pay

from fee

where stu_no not in 

(select stu_no from student);

-- 학적테이블에 존재하는 학생들옥한 학생의 학번, 이름, 반, 등록년도, 학기, 등록금 총액을 출력하라 

-- (풀 아웃터 조인을 출력하라 ) 단, 학적테이블에 존재하나 드옭테이블에 존재하지 않은 학생의 등록년도는 '미납', 학기와 등록금 총액은 0으로 표현하고, 등록테이블에 존재하는 학생이 학적테이블에 존재하지 않은 경우 학번은 등록에 포함된학번, 이름은 '무명'으로 반은 0으로 표현한다. 

-- ????

select f.stu_no, stu_name, fee_year, fee_term, fee_pay

from student s, fee f

where s.stu_no = f.stu_no

union

select stu_no, stu_name, class, 'minab' , 0,0

from student

where stu_no not in

(select stu_no from fee)

UNION

select stu_no, 'moomyeng' , 0, fee_year, fee_term, fee_total

from fee

where stu_no not in

(select stu_no from student);

-- 학적테이블의 학생에 대하여 학번, 이름, 연도, 학기, 취득학점, 평점 평균을 출력하라 

-- ????

--  

-- 동아리에 가입한 학생 중 회장을 제외한 동아리 회원의 동아리 번호, 동아리명, 학번, 이름을 셀프조인으로 출력하라 

 

select cir_num, cir_name, stu_no, stu_name

from circle

where president != 1;

select a.cir_num, a.cir_name, a.stu_no, a.stu_name, a.manager_num

from circle a, circle b

where a.manager_num = b.cir_num

-- 동아리 이름이 "java길라잡이" 인 동아리 회원의 동아리번호, 동아리명, 학번, 이름을 출력하라 

-- (셀프 조인 대신에 부속질의어를 이용하여 출력하라 )

select a.cir_num, a.cir_name, a.stu_no, a.stu_name, a.manager_num

from circle a

where a.manager_num in 

(select manager_num from circle where cir_name = "java길라잡이");

update circle set manager_num = 1 where cir_num = 2;

update circle set manager_num = 1 where cir_num = 3;

update circle set manager_num = 6 where cir_num = 4;

update circle set manager_num = 6 where cir_num = 5;

-- (x)

select cir_num, cir_name, stu_no, stu_name

from circle

where cir_name = "java길라잡이";

select a.cir_num, a.cir_name, a.stu_no, a.stu_name , a.manager_num

from circle a,circle b

where b.cir_num = a.manager_num

and b.cir_name = "java길라잡이";

-- 학적테이블에 존재하는 학생들 중에서 등록한 학생의 학번, 반, 등록금 총액을 출력하라 

-- (풀 아웃터 조인을 출력하라)

-- 

-- 등록테이블에서 각 장학금에 대하여 모든 장학금의 액수를 누적시킨 총액을 출력하라 

-- (단, 출력은 학번, 연도, 학기, 등록일, 장학금액, 누적합계를 정렬순서는 누적합계, 장학금액, 학번, 

-- 등록년도, 학기 순으로 출력하라)

-- +++

-- 등록한 학생에 대하여 학번, 이름, 납입한 등록금의 총액, 수강 신청여부를 출력하라 

-- 

-- (x)

select DISTINCT s.stu_no, s.stu_name, sum(f.fee_total), a.att_div

from student s, fee f, attend a

where s.stu_no = f.stu_no

and s.stu_no = a.stu_no

group by s.stu_no;

-- 수강신청을 한 학생에 대하여 학번과 등록한 수를 출력하라 

-- (x)

select a.stu_no, count(*)

from attend a, fee f

where a.stu_no = f.stu_no

group by a.stu_no;

use haksa;
 
-- 학적 테이블에 존재하는 학생들 중에서 아직 등록을 못한 학생이 있다. 각 학생에 대하여 학번, 이름, 등록년도, 학기, 이름, 등록년도, 학기, 학생이 등록한 납입금 총액을 출력하라
 
-- (x ???)
 
 
select student.stu_no, stu_name, fee_year, fee_term, fee_pay
from student, fee
where not EXISTS
 
(select student.stu_no, stu_name, fee_year, fee_term, fee_pay
from student, fee
where student.stu_no = fee.stu_no);
 
 
 
 
-- 
-- 학적 테이블과 성적테이블을 크로스 조인하여 학번 , 이름, 성적년도, 학기를 출력하라 
 
select student.stu_no, stu_name, sco_term
from student , score;
 
 
 
-- 학적 테이블과 성적테이블을 크로스 조인하여 학번, 이름 , 성적년도, 학기를 출력하라 
-- (단, 학적테이블에 존재하는 20061011 학생만을 출력하라 )
 
select student.stu_no, stu_name, sco_term
from student , score
where student.stu_no = 20061011;
 
 
 
-- 학적테이블에 존재하는 학생들 중에서 등록한 학생의 학번, 이름, 반, 등록년도, 학기, 등록금 총액을 출력하라 
 
 
 (select student.stu_no, stu_name, fee_year, fee_term, fee_pay
from student, fee
where student.stu_no = fee.stu_no);
 
-- 
--   where 절의 조건 연산자가 = 인경우에 from절에 나오는 좌측에 있는 student 테이블의 모집단이 
-- 우그의 fee테이블의 모집단을 전부 포함하고 있다면 이너 조인 이라고 한다. 
-- 만약, 우측의 모집단이 좌측의 모집단을 포함하고 있다면 아웃터 조인이라고 한다. 
-- 
-- 등록 테이블은 학적테이블에 존재하는 학번을 전체적으로 가지고 있지 않기 때문에 아웃터 조인은 
-- 동일한 결과를 반환한다. 
-- 
-- 아웃터 조인 예)
  
 (select student.stu_no, stu_name, fee_year, fee_term, fee_pay
from fee,student
where student.stu_no = fee.stu_no);
 
 
 
 
-- 수강테이블에 존재하는 학생의 수강신청년도, 학기, 과목코드, 과목명을 출력하라 
 
 
select att_year, att_term, subject.sub_code, sub_name
from attend,subject
where subject.sub_code = attend.sub_code;
 
 
-- 
-- 재학생이면서 수강신청테이블에 존재하는 학생의 학번, 이름, 수강신청년도, 학기, 과목코드, 과목명, 
-- 교수코드, 교수명을 출력하라 (단, 출력순서는 학번, 수강년도, 수강학기, 수강코드순이고, 과목명과 
-- 교수명을 표제어로 사용하여라 )
 
 
select s.stu_no, stu_name, att_year, att_term, sj.sub_code, sub_name, p.prof_code, prof_name
from student s, attend a, professor p, subject sj
where s.stu_no=a.stu_no
and a.sub_code = sj.sub_code
and a.prof_code=p.prof_code
order by s.stu_no, att_year,att_term, sj.sub_code;
 
 
-- 학적 테이블에 존재하는 모든 학생이 출력될 수 있도록 레프트 아웃 이퀄 조인으로 출력하라 
-- (단, 미등록자는 등록년도를 "미납" , 학기는 0 , 납입금총액도 0으로 출력하라 )
 
select student.stu_no, stu_name, fee_year, fee_term, fee_pay
from student, fee
where student.stu_no = fee.stu_no
UNION
select stu_no, stu_name, 'minab' , 0,0 
from student 
where stu_no not in
(select stu_no from fee);
 
-- 등록 테이블에 존재하는 모든 학생이 출력될 수 있도록 학생의 학번, 이름, 등록년도, 학기, 
-- 등록금총액을 출력하고, 단 성명이 없는 학생은 이름을 '무명'으로 출력하라 (라이트 아웃 이퀄 조인 으로 출력하라)
-- 
 
-- (????????)
select fee.stu_no, stu_name, fee_year, fee_term, fee_pay
from fee, student
where  student.stu_no = fee.stu_no
union
select stu_no, "moomyeng", fee_year, fee_term, fee_pay
from fee
where stu_no not in 
(select stu_no from student);
 
 
 
-- 학적테이블에 존재하는 학생들옥한 학생의 학번, 이름, 반, 등록년도, 학기, 등록금 총액을 출력하라 
-- (풀 아웃터 조인을 출력하라 ) 단, 학적테이블에 존재하나 드옭테이블에 존재하지 않은 학생의 등록년도는 '미납', 학기와 등록금 총액은 0으로 표현하고, 등록테이블에 존재하는 학생이 학적테이블에 존재하지 않은 경우 학번은 등록에 포함된학번, 이름은 '무명'으로 반은 0으로 표현한다. 
-- ????
 
select f.stu_no, stu_name, fee_year, fee_term, fee_pay
from student s, fee f
where s.stu_no = f.stu_no
union
 
select stu_no, stu_name, class, 'minab' , 0,0
from student
where stu_no not in
(select stu_no from fee)
 
UNION
 
select stu_no, 'moomyeng' , 0, fee_year, fee_term, fee_total
from fee
where stu_no not in
(select stu_no from student);
 
 
-- 학적테이블의 학생에 대하여 학번, 이름, 연도, 학기, 취득학점, 평점 평균을 출력하라 
-- ????
--  
 
-- 동아리에 가입한 학생 중 회장을 제외한 동아리 회원의 동아리 번호, 동아리명, 학번, 이름을 셀프조인으로 출력하라 
 
select cir_num, cir_name, stu_no, stu_name
from circle
where president != 1;
 
 
select a.cir_num, a.cir_name, a.stu_no, a.stu_name, a.manager_num
from circle a, circle b
where a.manager_num = b.cir_num
 
 
-- 동아리 이름이 "java길라잡이" 인 동아리 회원의 동아리번호, 동아리명, 학번, 이름을 출력하라 
-- (셀프 조인 대신에 부속질의어를 이용하여 출력하라 )
 
select a.cir_num, a.cir_name, a.stu_no, a.stu_name, a.manager_num
from circle a
where a.manager_num in 
 
(select manager_num from circle where cir_name = "java길라잡이");
 
 
 
 
update circle set manager_num = 1 where cir_num = 2;
 
update circle set manager_num = 1 where cir_num = 3;
 
update circle set manager_num = 6 where cir_num = 4;
 
update circle set manager_num = 6 where cir_num = 5;
 
 
-- (x)
select cir_num, cir_name, stu_no, stu_name
from circle
where cir_name = "java길라잡이";
 
 
 
select a.cir_num, a.cir_name, a.stu_no, a.stu_name , a.manager_num
from circle a,circle b
where b.cir_num = a.manager_num
and b.cir_name = "java길라잡이";
 
 
-- 학적테이블에 존재하는 학생들 중에서 등록한 학생의 학번, 반, 등록금 총액을 출력하라 
-- (풀 아웃터 조인을 출력하라)
-- 
 
-- 등록테이블에서 각 장학금에 대하여 모든 장학금의 액수를 누적시킨 총액을 출력하라 
-- (단, 출력은 학번, 연도, 학기, 등록일, 장학금액, 누적합계를 정렬순서는 누적합계, 장학금액, 학번, 
-- 등록년도, 학기 순으로 출력하라)
-- +++
 
 
-- 등록한 학생에 대하여 학번, 이름, 납입한 등록금의 총액, 수강 신청여부를 출력하라 
-- 
-- (x)
select DISTINCT s.stu_no, s.stu_name, sum(f.fee_total), a.att_div
from student s, fee f, attend a
where s.stu_no = f.stu_no
and s.stu_no = a.stu_no
group by s.stu_no;
 
-- 수강신청을 한 학생에 대하여 학번과 등록한 수를 출력하라 
 
-- (x)
 
select a.stu_no, count(*)
from attend a, fee f
where a.stu_no = f.stu_no
group by a.stu_no;