MYSQL 부속질의어 예제&문제 9

-- 적어도 한 번 이상 등록한 학생의 학번과 이름을 출력하라 

-- (o)

select DISTINCT s.stu_no, s.stu_name

from student s, fee f

where s.stu_no = f.stu_no;

-- (o)

select stu_no,stu_name

from student

where EXISTS

(select *

 from fee 

 where student.stu_no=stu_no);

-- 학생중에 동아리의 등급이 일반 회원인 학생의 학번과 이름, 주민번호를 출력하라 

-- (o)

select s.stu_no, s.stu_name, s.id_num 

from student s, circle c

where s.stu_no= c.stu_no

and c.president=2;

-- (o)

select stu_no, stu_name, id_num

from student

where stu_no in

(select stu_no

from circle

where president =2);

-- 두번 이상 장학금을 지급 받은 학생중 장학금액이 학기별로 서로 다른 경우의 학생의 학번을 출력하라 

-- (o)

select stu_no,count(jang_code)

from fee

group by stu_no

HAVING count(jang_code)>=2;

-- (o)

select DISTINCT stu_no

from fee f

where stu_no in

(select stu_no

from fee

where jang_total <> f.jang_total);

-- 학적 테이블에서 동아리 "java길라잡이"에 가입하지 않은 학생의 학번과 이름, 주야구분을 출력하라 

-- (x) ??

select stu_no, stu_name, juya

from student 

where sut_no not in  

(select stu_no

 from circle 

 where cir_name='java길라잡이');

-- (o)

select stu_no, stu_name, juya

from student

where 'java길라잡이' <> 

all (select cir_name

from circle

where stu_no= student.stu_no);

-- 학적테이블에서 학번이 가장 큰 3명의 학번을 내림차순으로 출력하라 

-- (x)

select stu_no

from student

order by stu_no desc;

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

select stu_no

from student s1

where 3 >

(select count(*)

from student s2

where (s1.stu_no < s2.stu_no))

order by stu_no desc;

-- (x) 

select s2.stu_no, count(*)

from student s1 ,student s2

group by s1.stu_no

having s1.stu_no < s2.stu_no

;

-- 학적 테이블에서 학번이 가장 작은 학번을 가진 3명의 학생을 오름차순으로 출력하라 

 -- ???

select stu_no

from student s1

where 3 > 

(select count(*) 

from student s2

where (s1.stu_no > s2.stu_no))

order by stu_no;

-- 등록테이블에서 장학금을 지급 받은 학생 중 가장 작은 장학금액을 지급 받은 학생 8명의 학번, 

-- 장학금 총액을 출력하라 

select DISTINCT stu_no, jang_total

from fee f1

where 8 >

(select count(*)

from fee f2

where (f1.jang_total > f2.jang_total)

)order by f1.jang_total desc;

-- 

-- 등록테이블에서 등록한 학생 중에서 납부 총액이 가장 큰 학생을 포함한 3명의 학번, 납부총액을

-- 출력하라 

-- 

select stu_no, fee_pay

from fee f1

where 3 >

(select count(*)

from fee f2

where f1.fee_pay < f2.fee_pay) 

)

-- 20061011 학생이 가입한 동아리에 소속된 모든 학생의 학번과 이름을 출력하라 

-- 

-- (x)

select stu_no , stu_name

from student s 

where cir_name =

(select c.cir_name 

from circle c

where s.stu_no = c.stu_no

and c.stu_no = 20061011);

-- (o)

select s1.stu_no, s1.stu_name

from student s1  , circle c1

where s1.stu_no=c1.stu_no 

and c1.cir_name =

(select cir_name 

from circle 

where stu_no = 20061011);

-- (o)??

select stu_no , stu_name

from student s 

where not exists

(select *

from circle c

where c.stu_no = 20061011

and not EXISTS

(SELECT *

from circle c2

where c.cir_name = c2.cir_name

and s.stu_no = c2.stu_no)

);

-- 적어도 한 번 장학금을 받은 학생에 대하여 학번, 등록년도, 학기, 장학금액 중 가장 큰 장학금액, 등록일자를 출력하라 

-- 

-- (x)

select stu_no, fee_year, fee_term, jang_total, fee_date

from fee

group by stu_no

having jang_total = max(jang_total);

-- (o) ??

select stu_no, fee_year, fee_term, jang_total, fee_date

from fee f1

where jang_total=

(select max(jang_total)

from fee f2

where f1.stu_no = f2.stu_no);

-- 2007년 1학기에 등록한 학생이 같은 연도, 학기에 수강 신청한 학생의 학번과 등록년도, 등록학기,

-- 수강신청년도, 학기를 출력하라 (중복된 자료를 제거하기 위해서 distinct를 사용)

-- (x)

select DISTINCT f1.stu_no, f1.fee_year, f1.fee_term, a1.att_year, a1.att_term

from fee f1, attend a1

where f1.stu_no=a1.stu_no

and f1.fee_year = 2007

and f1.fee_term =1;

select DISTINCT f1.stu_no, f1.fee_year, f1.fee_term, a1.att_year, a1.att_term

from fee f1, attend a1

where f1.stu_no=a1.stu_no

and f1.fee_year = 2007

and f1.fee_term =1

and f1.fee_year = a1.att_year

and f1.fee_term = a1.att_term

;

 
-- 적어도 한 번 이상 등록한 학생의 학번과 이름을 출력하라 
-- (o)
select DISTINCT s.stu_no, s.stu_name
from student s, fee f
where s.stu_no = f.stu_no;
-- (o)
select stu_no,stu_name
from student
where EXISTS
(select *
 from fee 
 where student.stu_no=stu_no);
-- 학생중에 동아리의 등급이 일반 회원인 학생의 학번과 이름, 주민번호를 출력하라 
-- (o)
select s.stu_no, s.stu_name, s.id_num 
from student s, circle c
where s.stu_no= c.stu_no
and c.president=2;
-- (o)
select stu_no, stu_name, id_num
from student
where stu_no in
(select stu_no
from circle
where president =2);
-- 두번 이상 장학금을 지급 받은 학생중 장학금액이 학기별로 서로 다른 경우의 학생의 학번을 출력하라 
-- (o)
select stu_no,count(jang_code)
from fee
group by stu_no
HAVING count(jang_code)>=2;
-- (o)
select DISTINCT stu_no
from fee f
where stu_no in
(select stu_no
from fee
where jang_total <> f.jang_total);
-- 학적 테이블에서 동아리 "java길라잡이"에 가입하지 않은 학생의 학번과 이름, 주야구분을 출력하라 
-- (x) ??
select stu_no, stu_name, juya
from student 
where sut_no not in  
(select stu_no
 from circle 
 where cir_name='java길라잡이');
-- (o)
select stu_no, stu_name, juya
from student
where 'java길라잡이' <> 
all (select cir_name
from circle
where stu_no= student.stu_no);
 
 
-- 학적테이블에서 학번이 가장 큰 3명의 학번을 내림차순으로 출력하라 
-- (x)
select stu_no
from student
order by stu_no desc;
-- (o) ???????????
 
select stu_no
from student s1
where 3 >
 
(select count(*)
from student s2
where (s1.stu_no < s2.stu_no))
order by stu_no desc;
 
-- (x) 
select s2.stu_no, count(*)
from student s1 ,student s2
group by s1.stu_no
having s1.stu_no < s2.stu_no
;
 
 
 
-- 학적 테이블에서 학번이 가장 작은 학번을 가진 3명의 학생을 오름차순으로 출력하라 
 -- ???
select stu_no
from student s1
where 3 > 
 
(select count(*) 
from student s2
where (s1.stu_no > s2.stu_no))
order by stu_no;
 
 
-- 등록테이블에서 장학금을 지급 받은 학생 중 가장 작은 장학금액을 지급 받은 학생 8명의 학번, 
-- 장학금 총액을 출력하라 
 
 
select DISTINCT stu_no, jang_total
from fee f1
where 8 >
(select count(*)
from fee f2
where (f1.jang_total > f2.jang_total)
 
)order by f1.jang_total desc;
 
-- 
-- 등록테이블에서 등록한 학생 중에서 납부 총액이 가장 큰 학생을 포함한 3명의 학번, 납부총액을
-- 출력하라 
-- 
select stu_no, fee_pay
from fee f1
where 3 >
 
(select count(*)
from fee f2
where f1.fee_pay < f2.fee_pay) 
 
)
 
-- 20061011 학생이 가입한 동아리에 소속된 모든 학생의 학번과 이름을 출력하라 
-- 
-- (x)
select stu_no , stu_name
from student s 
where cir_name =
(select c.cir_name 
from circle c
where s.stu_no = c.stu_no
and c.stu_no = 20061011);
 
-- (o)
select s1.stu_no, s1.stu_name
from student s1  , circle c1
where s1.stu_no=c1.stu_no 
and c1.cir_name =
(select cir_name 
from circle 
where stu_no = 20061011);
 
 
-- (o)??
 
select stu_no , stu_name
from student s 
where not exists
 
(select *
from circle c
where c.stu_no = 20061011
and not EXISTS
 
(SELECT *
from circle c2
where c.cir_name = c2.cir_name
and s.stu_no = c2.stu_no)
);
 
 
 
 
-- 적어도 한 번 장학금을 받은 학생에 대하여 학번, 등록년도, 학기, 장학금액 중 가장 큰 장학금액, 등록일자를 출력하라 
-- 
-- (x)
select stu_no, fee_year, fee_term, jang_total, fee_date
from fee
group by stu_no
having jang_total = max(jang_total);
 
 
-- (o) ??
select stu_no, fee_year, fee_term, jang_total, fee_date
from fee f1
where jang_total=
(select max(jang_total)
from fee f2
where f1.stu_no = f2.stu_no);
 
 
 
 
 
-- 2007년 1학기에 등록한 학생이 같은 연도, 학기에 수강 신청한 학생의 학번과 등록년도, 등록학기,
-- 수강신청년도, 학기를 출력하라 (중복된 자료를 제거하기 위해서 distinct를 사용)
 
-- (x)
select DISTINCT f1.stu_no, f1.fee_year, f1.fee_term, a1.att_year, a1.att_term
from fee f1, attend a1
where f1.stu_no=a1.stu_no
and f1.fee_year = 2007
and f1.fee_term =1;
 
 
 
select DISTINCT f1.stu_no, f1.fee_year, f1.fee_term, a1.att_year, a1.att_term
from fee f1, attend a1
where f1.stu_no=a1.stu_no
and f1.fee_year = 2007
and f1.fee_term =1
and f1.fee_year = a1.att_year
and f1.fee_term = a1.att_term
;