/*
SQL BASIC
*/
-- 1.학과 이름과 계열
-- 학과명, 계열
SELECT department_name, category
FROM tb_department;
-- 2.학과의 정원을 출력
-- 000의 정원은 00명 입니다.
SELECT department_name||'의 정원은 '||capacity||'명 입니다.'
FROM tb_department;
-- 3.국어국문학과에 다니는 여학생 중 휴학중인 여학생 조회
SELECT *
FROM tb_student
JOIN tb_department USING(department_no)
WHERE absence_yn = 'Y'
AND (SUBSTR(student_ssn,8,1)=2 OR SUBSTR(student_ssn,8,1)=4)
AND department_name = '국어국문학과';
-- 4.이름순서대로 조회
SELECT student_name
FROM tb_student
WHERE student_no IN('A513079', 'A513090', 'A513091', 'A513110', 'A513119')
ORDER BY student_name;
-- 5.총장의 이름
-- 총장을 제외한 모든 교수들은 소속 학과를 가지고 있음
SELECT *
FROM tb_professor
WHERE department_no IS NULL;
-- 6.학과가 지정되지 않은 학생 조회
SELECT *
FROM tb_student
WHERE department_no IS NULL;
-- 7.선수과목이 존재하는 과목 조회
SELECT *
FROM tb_class
WHERE preattending_class_no IS NOT NULL;
-- 8.입학정원이 20이상 30이하인 학과들의 이름과 계열
SELECT department_name, category, capacity
FROM tb_department
WHERE capacity BETWEEN 20 AND 30;
-- 9.학과 계열을 중복제거하고 조회
SELECT DISTINCT category
FROM tb_department;
SELECT category
FROM tb_department
GROUP BY category;
-- 10.02학번 전주거주자들의 모임
SELECT student_no, student_name, student_ssn
FROM tb_student
WHERE EXTRACT(YEAR FROM entrance_date) = '2002'
AND student_address LIKE '%전주%'
AND absence_yn = 'N';
/*
SELECT_FUCTOIN
*/
-- 1.영어영문학과 학생들의 학번, 이름, 입학년도 조회
-- 입학년도가 빠른순으로 정렬
SELECT student_no 학번, student_name 이름, TO_CHAR(entrance_date,'YYYY-MM-DD') 입학년도
FROM tb_student
JOIN tb_department USING(department_no)
WHERE department_name = '영어영문학과'
ORDER BY entrance_date;
-- 2.교수중 이름이 3글자가 아닌 사람을 조회
SELECT *
FROM tb_professor
--WHERE professor_name NOT LIKE '___';
WHERE LENGTH(professor_name)!=3;
-- 3.남자교수들의 이름과 나이 조회 나이 순서로 정렬하되 나이가 같다면 이름순으로 정렬
SELECT professor_name 이름,
TO_CHAR(SYSDATE, 'YYYY')- (CASE
WHEN SUBSTR(professor_ssn,8,1) IN (1,2) THEN 19
WHEN SUBSTR(professor_ssn,8,1) IN (3,4) THEN 19
ELSE 0
END||SUBSTR(professor_ssn,1,2)) 나이
FROM tb_professor
WHERE SUBSTR(professor_ssn,8,1) IN(1,3)
ORDER BY 나이 DESC, 이름;
-- DECODE(SUBSTR(professor_ssn,8,1),1,19,2,19,3,20,4,20,0)
-- 4.교수의 성(무조건1글자라고 가정)을 제외한 이름을 출력
SELECT SUBSTR(professor_name, 2)
FROM tb_professor;
-- 5.2023년 크리스마는 무슨 요일
-- DAY : 월요일 D: 2(일요일이 1)
SELECT TO_CHAR(TO_DATE('2023-12-25'), 'day')
FROM DUAL;
-- 6.2000이후 입학자들의 학번은 A로 시작
-- 2000년 이전 학번을 받은 학생들의 학번과 이름을 조회
SELECT student_no, student_name
FROM tb_student
WHERE student_no NOT LIKE 'A%';
-- 7.학번이 A517178인 한아름 학생의 학점 총 평균점수 조회
-- 헤더 : 학번, 이름, 평점
-- 점수는 반올림해서 소수점 이하 한자리까지 표시
-- 학번과 이름을 함께 조회하기 위해 그룹으로 묶어준다.
SELECT student_no 학번,
(SELECT student_name FROM tb_student WHERE tb_student.student_no = tb_grade.student_no) 이름,
ROUND(AVG(point),1) 평점
FROM tb_grade
WHERE student_no = 'A517178'
GROUP BY student_no;
SELECT student_no 학번,
student_name 이름,
ROUND(AVG(point),1) 평점
FROM tb_grade
JOIN tb_student USING(student_no)
WHERE student_no = 'A517178'
GROUP BY student_no, student_name;
-- 8.학과별 학생 수 조회
SELECT (SELECT department_name FROM tb_department WHERE tb_department.department_no = tb_student.department_no) 학과,
COUNT(*) 학생수
FROM tb_student
GROUP BY department_no
ORDER BY 학생수 DESC;
-- 10.지도교수를 배정받지 못한 학생의 수를 조회\
SELECT COUNT(*) 지도교수없음
FROM tb_student
WHERE COACH_PROFESSOR_NO IS NULL;
-- 11.학번이 A112113인 김고운 학생의 년도별 평점 *****
SELECT student_no 학번,
(SELECT student_name FROM tb_student WHERE tb_student.student_no = tb_grade.student_no) 이름,
SUBSTR(term_no,1,4) 년도, FLOOR(AVG(point)) 평점
FROM tb_grade
WHERE STUDENT_NO = 'A112113'
GROUP BY student_no, SUBSTR(term_no,1,4)
ORDER BY 1;
/*
SELECT(OPTION)
*/
-- 1.휴학중인 학생들의 이름과 주민번호를 나이가 적은 순서로 화면에 출력
SELECT student_name, student_ssn
FROM tb_student
WHERE absence_yn = 'Y'
ORDER BY student_ssn DESC;
-- 2. 주소지가 강원도나 경기도인 학생들 중 1900년대 학번을 가진 학생들의 이름과 학번, 주소를 이름의 오름차순으로 화면에 출력하시오.
-- 단, 출력헤더에는 "학생이름", "학번", "거주지 주소"가 출력되도록 한다.
SELECT student_name AS 학생이름, student_no AS 학번, student_address AS "거주지 주소"
FROM tb_student
WHERE (student_address LIKE '%강원도%' OR student_address LIKE '%경기도%')
AND student_no LIKE '9%'
ORDER BY student_name;
-- 3.현재 법학과 교수 중 가장 나이가 많은 사람부터 이름을 확인할 수 있는 SQL 문장을 작성하시오.
-- (법학과의 '학과 코드'는 학과 테이블을 조회해서 찾아 내도록 하자)
SELECT professor_name, professor_ssn, department_name
FROM tb_professor
JOIN tb_department USING(department_no)
WHERE department_name = '법학과'
ORDER BY professor_ssn;
SELECT professor_name, professor_ssn, department_name
FROM tb_professor, tb_department
WHERE tb_professor.department_no = tb_department.department_no
AND department_name = '법학과'
ORDER BY professor_ssn;
-- 4.2004년 2학기에 'C3118100' 과목을 수강한 학생들의 학점을 조회하려고 한다.
-- 학점이 높은 학생부터 표시하고,
-- 학점이 같으면 학번이 낮은 학생부터 표시하는 구문을 작성해 보시오.
-- 워크북 결과와 동일하게 소수점 아래 2자리까지 0으로 표현
SELECT student_no, TO_CHAR(point,'0.00')
FROM tb_grade
WHERE term_no = '200402' AND class_no = 'C3118100'
ORDER BY 2 DESC, 1;
-- 5.과목별 교수 이름을 찾으려고 한다. 과목 이름과 교수 이름을 출력하는 SQL문을 작성하시오
SELECT class_name, professor_name
FROM tb_class_professor
JOIN tb_professor USING(professor_no)
JOIN tb_class USING(class_no);
SELECT class_name, professor_name
FROM tb_class
JOIN tb_professor USING(department_no)
WHERE professor_name = '박철우';
-- 6.결과 중 '인문 사회' 계열에 속한 과목의 교수 이름을 찾으려고 한다.
-- 이에 해당하는 과목 이름과 교수 이름을 출력하는 SQL문을 작성하시오. 디파트먼트 인문
SELECT class_name, professor_name
FROM tb_class_professor
JOIN tb_professor USING(professor_no)
JOIN tb_department USING(department_no)
JOIN tb_class USING(class_no)
WHERE category = '인문사회';
-- 7.'음악학과' 학생들의 평점을 구하려고 한다.
-- 음악학과 학생들의 "학번", "학생 이름", "전체 평점"을 출력하는 SQL 문장을 작성하시오.
-- (단, 평점은 소수점 1자리까지만 반올림하여 표시한다.)
SELECT student_no AS 학번, student_name AS "학생 이름", ROUND(AVG(point),1)
FROM tb_student
JOIN tb_grade USING(student_no)
JOIN tb_department USING(department_no)
WHERE department_name = '음악학과'
GROUP BY student_no, student_name;
-- 8.학번이 A313047인 학생이 학교에 나오고 있지 않다. 지도 교수에게 내용을 전달하기 위한
-- 학과 이름, 학생 이름과 지도 교수 이름이 필요하다.
SELECT department_name, student_name, professor_name
FROM tb_student
JOIN tb_department USING(department_no)
JOIN tb_professor ON(coach_professor_no=professor_no)
WHERE student_no = 'A313047';
-- 9.2007년도에 '인간관계론' 과목을 수강한 학생을 찾아
-- 학생이름과 수강학기를 표시하는 SQL 문장을 작성하시오. <- 문제 이상함
SELECT student_name, term_no, class_name
FROM tb_student
JOIN tb_grade USING(student_no)
JOIN tb_class USING(class_no)
WHERE class_name='인간관계론'
AND term_no LIKE '2007%';
SELECT term_no FROM tb_grade WHERE term_no LIKE '2007%';
--> tb_grade
SELECT class_name FROM tb_class WHERE class_name='인간관계론';
--> tb_grade -> tb_class
SELECT student_no FROM tb_student WHERE ;
--> class - department
SELECT student_name, term_no, class_name
FROM tb_class
JOIN tb_grade USING(class_no)
JOIN tb_student USING(student_no)
WHERE class_name='인간관계론'
AND term_no LIKE '2007%';
SELECT class_name FROM tb_class WHERE class_name='인간관계론';
--> tb_class
SELECT term_no FROM tb_grade WHERE term_no LIKE '2007%';
--> tb_class -> tb_grade (class_no)
SELECT student_no FROM tb_student WHERE ;
--> tb_grade->tb_student student_no
SELECT student_name, term_no, class_name
FROM tb_grade
JOIN tb_class USING(class_no)
JOIN tb_student USING(student_no)
WHERE class_name='인간관계론'
AND term_no LIKE '2007%';
-- 10.예체능 계열 과목 중 과목 담당교수를 한 명도 배정받지 못한 과목을 찾아
-- 그 과목 이름과 학과 이름을 출력하는 SQL 문장을 작성하시오.
SELECT class_name, department_name
FROM tb_class
LEFT JOIN tb_class_professor USING(class_no)
JOIN tb_department USING(department_no)
WHERE category = '예체능' AND professor_no IS NULL;
-- 11.서반아어학과 학생들의 지도교수를 게시하고자 한다.
-- 학생이름과 지도교수 이름을 찾고 만일 지도 교수가 없는 학생일 경우
-- "지도교수 미지정"으로 표시하도록 하는 SQL 문을 작성하시오.
-- 단 출력헤더는 "학생이름", "지도교수"로 표시하며 고학번 학생이 먼저 표시되도록 한다.
SELECT student_name AS 학생이름, NVL((SELECT professor_name FROM tb_professor WHERE tb_student.coach_professor_no = tb_professor.professor_no),'지도교수 미지정') AS 지도교수
FROM tb_student
JOIN tb_department USING(department_no)
WHERE department_name='서반아어학과'
ORDER BY student_ssn;
SELECT * FROM tb_grade WHERE student_no = 'A431348';
-- 12.휴학생이 아닌 학생 중 평점이 4.0 이상인 학생을 찾아
-- 그 학생의 학번, 이름, 학과 이름, 평점을 출력하는 SQL문을 작성하시오.
SELECT student_no, student_name, department_name, ROUND(AVG(point),1)
FROM tb_student
JOIN tb_department USING(department_no)
JOIN tb_grade USING(student_no)
WHERE absence_yn='N' AND point>=4.0
GROUP BY student_no, student_name, department_name;
-- 13.환경조경학과 전공과목들의 과목 별 평점을 파악할 수 있는 SQL 문을 작성하시오.
SELECT * FROM tb_department WHERE department_name = '환경조경학과';
--> deaprtment_no
SELECT * FROM tb_class WHERE class_type LIKE '전공%';
--> department_no class_no
SELECT * FROM tb_grade;
--> class_no
-- 조인할때 테이블 관계 잘 정해야함
SELECT department_name, class_name, ROUND(AVG(point),1)
FROM tb_department
JOIN tb_class USING(department_no)
JOIN tb_grade USING(class_no)
WHERE department_name = '환경조경학과' AND class_type LIKE '전공%'
GROUP BY class_name, department_name
ORDER BY 3;
SELECT department_name, class_name, ROUND(AVG(point),1)
FROM tb_class
JOIN tb_department USING(department_no)
JOIN tb_grade USING(class_no)
WHERE department_name = '환경조경학과' AND class_type LIKE '전공%'
GROUP BY class_name, department_name
ORDER BY 3;
-- 14.최경희 학생과 같은 과 학생들의 이름과 주소를 출력하는 SQL 문을 작성하시오.
SELECT student_name, student_address
FROM tb_student
WHERE department_no = (SELECT department_no FROM tb_student WHERE student_name = '최경희');
-- 15.국어국문학과에서 총점수가 가장 높은 학생의 이름과 학번을 표시하는 SQL문을 작성하시오
-- 국어국문학과
SELECT department_no
FROM tb_department
WHERE department_name = '국어국문학과';
-- tb_department
-- 학생
SELECT *
FROM tb_student
WHERE department_no='001';
-- tb_department -> tb_student department_no
-- 점수
SELECT *
FROM tb_grade;
-- tb_student-> tb_grade student_no
SELECT student_name, student_no, AVG(point)
FROM tb_department
JOIN tb_student USING(department_no)
JOIN tb_grade USING(student_no)
WHERE department_name = '국어국문학과'
GROUP BY student_no, student_name;
SELECT student_name, student_no, AVG(point) AS 총점
FROM tb_department
JOIN tb_student USING(department_no)
JOIN tb_grade USING(student_no)
WHERE department_name = '국어국문학과'
GROUP BY student_no, student_name
HAVING AVG(point) = (
SELECT MAX(AVG(point))
FROM tb_department
JOIN tb_student USING(department_no)
JOIN tb_grade USING(student_no)
WHERE department_name = '국어국문학과'
GROUP BY student_no
);
SELECT *
FROM (
SELECT student_name, student_no, department_name, TRUNC(AVG(point),2) AS 평균점수,
-- 그룹으로 묶어서 순위를 구함
-- partition by 그룹으로 묶을 컬럼이름
RANK() OVER(PARTITION BY department_no ORDER BY TRUNC(AVG(point),2) DESC) AS RK
FROM tb_department
JOIN tb_student USING(department_no)
JOIN tb_grade USING(student_no)
--WHERE department_name = '국어국문학과'
GROUP BY student_no, student_name, department_name, department_no)
WHERE RK=1;
SELECT *
FROM (
SELECT student_name, student_no, AVG(point) AS 총점
FROM tb_department
JOIN tb_student USING(department_no)
JOIN tb_grade USING(student_no)
WHERE department_name = '국어국문학과'
GROUP BY student_no, student_name
ORDER BY AVG(point) DESC
)
WHERE ROWNUM = 1;
-- 16."환경조경학과"가 속한 같은 계열 학과들의
-- 학과 별 전공과목 평점을 파악하기 위한 적절한 SQL문을 찾아내시오.
-- 단, 출력헤더는 "계열 학과명", "전공평점"으로 표시되도록 하고,
-- 평점은 소수점 한자리까지만 반올림하여 표시되도록 한다.
SELECT * FROWM tb_department WHERE department_name = '환경조경학과';
-- department_no
SELECT * FROM tb_class;
-- department_no class_no
SELECT * FROM tb_grade;
-- class_no
SELECT *
FROM (
SELECT department_name AS "계열 학과명", ROUND(AVG(point),2) AS 전공평점,
RANK() OVER(ORDER BY ROUND(AVG(point),2) DESC) AS RK
FROM tb_department
JOIN tb_class USING(department_no)
JOIN tb_grade USING(class_no)
WHERE category = (SELECT category FROM tb_department WHERE department_name = '환경조경학과')
AND class_type LIKE '전공%'
GROUP BY department_no, department_name)
WHERE RK <=5;
--WHERE ROWNUM<=5; -- 조건이 1보다 클때는 조회가 안됨
댓글