[데이터베이스] SQL Query Report

<문제 1>

3. 아래의 문제를 SQL로 작성한다.

가. 과목명에 ‘구조’가 들어 있는 과목번호와 과목명을 찾아라.

select course_id, title
from course
where title like '%구조%';

나. 2012년도 1학기에 강의가 없는 교수의 이름을 찾아라.

select name
from professor
where position = '교수' and prof_id not in (select prof_id
                     from class
                     where year = 2012 and semester = 1);

다. 2012년도 1학기에 한 과목도 수강하지 않은 학생의 학번과 이름, 학과명을 찾아라.

select s.stu_id, s.name, d.dept_name
from student s, department d
where s.dept_id = d.dept_id and s.stu_id not in (select stu_id
                                                from takes t, class c 
                                                where t.class_id = c.class_id and year = 2012 and semester = 1);

라. 학과별 학생 수를 찾아라. (학과명, 학생수)

select d.dept_name, count(s.stu_id)
from student s, department d
where s.dept_id = d.dept_id
group by d.dept_name;

마. 학번별 수강과목 수를 찾아라. (학번, 수강과목수)

select stu_id, count(class_id)
from takes
group by stu_id;

바. 가장 최근에 임용된 교수(여기서 교수는 직급이 아닌 전체 교수를 의미함) 의 이름과 재직연수(2024년도 기준)를 찾아라.

select name, (2024-year_emp) 재직연수
from professor
where year_emp >= all (select year_emp
                       from professor);

사. 같은 학과, 같은 주소를 갖는 학생 이름의 쌍을 찾아라. (이때, 동일인이 쌍으로 나와서도 안되며, 한번 나온 쌍은 순서를 바꿔서 나와서도 안된다. 즉, (김광식, 김광식) 안되며, (김광식, 김정현)이 나왔다면 (김정현, 김광식)은 나와서는 안된다.)

select s1.name, s2.name
from student s1, student s2
where s1.address = s2.address and s1.dept_id = s2.dept_id and s1.name < s2.name;

---

<문제 2>

5. 아래는 은행에서 고객의 예금정보를 저장하기 위한 테이블 스키마이다. 이 테이블 들에 대해서 주어진 물음에 답할 수 있는 select문을 작성하라.

1) 모든 고객의 계좌번호, 이름, 그리고 예금 잔액을 검색하라.

select d.deposit_num, c.name, d.balance
from client c, deposit d
where c.ssn = d.ssn;

2) 이름이 ‘박지성’인 고객의 전화번호와 주민등록번호를 검색하라.

select phone, ssn
from client
where name = '박지성';

3) 지점 이름이 ‘성남지점’인 지점을 통해 개설된 모든 예금의 잔액을 검색하라.

select balance
from deposit
where branch_name = '성남지점';

4) 지점장 이름이 ‘고희경’인 지점의 이름과 주소를 검색하라.

select branch_name, address
from branch
where branch_head = '고희경';

5) 지점 이름이 ‘광주지점’인 지점의 지점장 이름과 주소를 검색하라.

select branch_head, address
from branch
where branch_name = '광주지점';

6) 이름이 ‘김기식’인 고객이 소유한 예금의 계좌번호, 개설지점의 이름, 잔액을 검색하라.

select deposit_num, branch_name, balance
from deposit
where ssn in (select ssn
             from client
             where name = '김기식');

7) ‘성남지점’에서 계좌를 개설한 고객의 이름과 주소, 그리고 예금 잔액을 검색하라.

select c.name, c.address, d.balance
from client c, deposit d
where c.ssn = d.ssn and d.branch_name = '성남지점';

8) ‘성남지점’에서 계좌를 개설한 고객 중 김씨 성을 가진 고객의 이름과 예금 잔 액을 검색하라.

select c.name, d.balance
from client c, deposit d
where c.ssn = d.ssn and d.branch_name = '성남지점' and c.name like '김%';

9) 예금 잔액이 10만원 이상인 계좌를 소유한 고객의 이름을 검색하라.

select name
from client
where ssn in (select ssn
             from deposit
             where balance >= 100000);

10) 예금 잔액이 10만원 이상인 계좌가 개설된 지점의 이름과 지점장 이름을 검색 하라.

select branch_name, branch_head
from branch
where branch_name in (select branch_name
                     from deposit
                     where balance >= 100000);

11) 예금을 개설한 지점의 지점장과 이름이 같은 고객이 소유한 예금의 계좌번호, 잔액, 그리고 개설지점 이름을 검색하라.

select d.deposit_num, d.balance ,d.branch_name
from deposit d, client c, branch b
where d.ssn = c.ssn and d.branch_name = b.branch_name and b.branch_head = c.name;

12) ‘서울지점’에서 계좌를 개설한 고객들 중에서 남자 고객의 이름과 예금 잔액을 검색하라.

select c.name, d.balance
from client c, deposit d
where c.ssn = d.ssn and branch_name = '서울지점' and c.ssn like '%-1%' or c.ssn like '%-3%';

13) 주민등록번호상의 생일이 3월인 모든 고객의 이름과 소유한 예금의 계좌번호를 검색하라.

select c.name, d.deposit_num
from client c, deposit d
where c.ssn = d.ssn and c.ssn like '__03%';

14) 자신의 주소와 같은 지점에 계좌를 소유하고 있는 고객의 이름과 예금 잔액을 검색하라.

select c.name, d.balance
from client c, deposit d, branch b
where c.ssn = d.ssn and d.branch_name = b.branch_name and c.address = b.address;

15) ‘성남지점’과 거래하고 있는 고객의 숫자를 검색하라.

select count(distinct c.ssn)
from client c, deposit d
where c.ssn = d.ssn and branch_name = '성남지점';

16) 각 지점별 잔액의 총합을 검색하라.

select branch_name, sum(balance)
from deposit
group by branch_name;

17) 고객 이름별 예금 잔액의 총합을 검색하라.

select c.name, sum(d.balance)
from client c, deposit d
where c.ssn = d.ssn
group by c.name;

18) 잔액의 합이 100만원 이상인 지점 이름과 잔액의 합을 검색하라.

select branch_name, sum(balance)
from deposit
group by branch_name
having sum(balance) >= 1000000;

19) 지점별로 예금 잔액이 100만원 이상인 고객의 숫자를 검색하라.

select d.branch_name, count(distinct c.ssn)
from client c, deposit d
where c.ssn = d.ssn and d.balance >= 1000000
group by d.branch_name;

20) 예금 계좌를 소유하고 있지 않은 고객의 이름과 전화번호를 검색하라.

select name, phone
from client
where ssn not in (select ssn
                 from deposit);