SQL 문제 _ A

1. 80 년 생인 여학생 중 성이 '김' 씨인 학생의 주민번호, 학생이름을 조회하시오. 단 학생이름으로 오름차순 정렬하시오

select    '[' || substr(STUDENT_SSN, 1, 8) 
       || ‘******]' [주민번호], STUDENT_NAME 이름
from     TB_STUDENT
where    STUDENT_SSN like '80%' and 
substr(STUDENT_SSN, 8, 1)='2' and 
STUDENT_NAME like ‘김%’
order by 2;

2. 계열이 ‘예체능’인 학과의 정원을 기준으로 40 이상이면 ‘대강의실’ 30이상이면 ‘중강의실’ 30나머지는 ‘소강의실’로 출력한다. 단, 정원이 많은 순으로 정렬 한다.

• 현재정원 -> 정원으로 표시

select    DEPARTMENT_NAME 학과이름, CAPACITY 현재정원, 
case 
when CAPACITY >= 40 then '대강의실'
when CAPACITY >=30 then '중강의실'
else '소강의실' 
end as 강의실크기
from     TB_DEPARTMENT
where    CATEGORY = '예체능'
order by 2 desc, 1;

3. 2005년1월1일부터 2006년12월31일까지의 기간에 입학한 학생 중 주소가 등록되지 않은 남학생의 학과번호, 학생이름, 지도교수번호, 입학년도를 조회하시오. 입학년도를 기준으로 오름차순 정렬한다.

select     DEPARTMENT_NO 학과번호, STUDENT_NAME 학생이름, 
COACH_PROFESSOR_NO 지도교수번호, to_char(ENTRANCE_DATE, 'YYYY')
|| '년' as 입학년도
from     TB_STUDENT
where     STUDENT_ADDRESS is null and 
to_char(ENTRANCE_DATE, 'YYYY') between 2005 and 2007 and
substr(STUDENT_SSN, 8, 1) = 1
order by 4,2;

4.춘 대학교애 다니는 동명이인 학생들의 이름을 찾고자 한다. 어떤 SQL 문장을 사용하면 가능하겠는가?

동일이름	동명인수
김경민	2
김명철	2
조기현	2
최효정	2
...	...

20 rows selected

SELECT STUDENT_NAME AS 동일이름,
       COUNT(*)     AS "동명인 수"
FROM   TB_STUDENT
GROUP BY STUDENT_NAME
HAVING COUNT(*) > 1;
17.
SELECT STUDENT_NAME AS "학생이름",
       STUDENT_NO AS "학번",
             STUDENT_ADDRESS AS "거주지 주소" 
FROM   TB_STUDENT
WHERE  ( STUDENT_ADDRESS LIKE '%강원도%'
        OR STUDENT_ADDRESS LIKE '%경기도%' )
AND   TO_CHAR(ENTRANCE_DATE, 'RRRR') LIKE '19%' 
ORDER BY 1 ;

 

 

5. 주소지가 강원도나 경이도인 학생들 중 1900년대 학번을 가진 학생들의 이름과 학번, 주소를 이름의 오름차순으로 화면에 출력하시오.

단, 출력헤더에는 "학생이름","학번", "거주지 주소" 가 출력되도록 한다.

 

학생이름	학번	거주지주소
김계영	9919024	경기도 용인시 수지구 풍덕천2동 신정마을 임광 305-1703
박규상	9931111	경기도 서암시 분당구 탑마을 경남아파트 710-1302
송근우	9931165	경기도 부천시 원미구 중2동 386-1186 복사골아파트 1701-405
송점삼	9995148	경기도 남양주시 도농도 부영그린타운 306-2304
이기범	9931248	경기도 근포시 산본동 한양목련아파트 1224-2002
이춘평	9811176	경기도 고양시 일산구 1107 후곡마을 1209-705
전기성	9931277	경기도 용인시 수지구 풍덕천동 239-3 중앙빌딩 3층
정기웅	9931285	경기도 성남시 분당구 야탑동 기산아파트 307-801
조기환	9931312	경기도 시흥시 매화동 194-1 동진아파트 1-305

 

SELECT PROFESSOR_NAME,
                 PROFESSOR_SSN
    FROM   TB_PROFESSOR
    JOIN   TB_DEPARTMENT USING (DEPARTMENT_NO)
    WHERE  DEPARTMENT_NAME = '법학과'
    ORDER BY 2;

 

 

6. 현재 법학과 교수 중 가장 나이가 많은 사람부터 이름을 확인할 수 있는 SQL 문장을 작성하시오.

(법학과의 '학과 코드'는 학과 테이블 (TB_DEPARTMENT)을 조회해서 찾아 내도록 하자 단, Join사용하기 USING 절로 처리할것

 

PROFESSOR_NAME	PROFESSOR_SSN
홍남수	540304-1112251
김선희	551030-2159000
임진숙	640125-1143548
이미경	741016-2103506

 

 

SELECT CLASS_NAME, 
       DEPARTMENT_NAME 
FROM   TB_CLASS
JOIN   TB_DEPARTMENT USING (DEPARTMENT_NO)
ORDER BY 2;
20.
SELECT CLASS_NAME, 
       PROFESSOR_NAME
FROM   TB_CLASS
JOIN   TB_CLASS_PROFESSOR USING(CLASS_NO)
JOIN   TB_PROFESSOR USING(PROFESSOR_NO)