230718 SQL 강의

Notice

Recent Posts

Recent Comments

Link

« 2024/11 »
일	월	화	수	목	금	토
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30

Tags more

Archives

Today

Total

관리 메뉴

거니의 velog

230718 SQL 강의 본문

대덕인재개발원/대덕인재개발원_Oracle DB

230718 SQL 강의

Unlimited00 2023. 7. 18. 16:25

-- 각 테이블에 10개 이상의 예제 레코드를 삽입하는 SQL문을 작성한다. 
INSERT ALL
      INTO PROFESSOR (PRO_NO, PRO_NAME, PRO_MAJOR, PRO_DEPTNO, PRO_TELNO)
      VALUES ('18A01', '김길순', '국어국문학', 'AB1', '010-2623-4323')
      INTO PROFESSOR (PRO_NO, PRO_NAME, PRO_MAJOR, PRO_DEPTNO, PRO_TELNO)
      VALUES ('18A02', '봉종옥', '영어영문학', 'AB2', '010-9531-0469')
      INTO PROFESSOR (PRO_NO, PRO_NAME, PRO_MAJOR, PRO_DEPTNO, PRO_TELNO)
      VALUES ('19A03', '서하현', '독어독문학', 'AB3', '010-9024-9652')
      INTO PROFESSOR (PRO_NO, PRO_NAME, PRO_MAJOR, PRO_DEPTNO, PRO_TELNO)
      VALUES ('19A04', '류윤성', '일어일문학', 'AB4', '010-1326-7021')
      INTO PROFESSOR (PRO_NO, PRO_NAME, PRO_MAJOR, PRO_DEPTNO, PRO_TELNO)
      VALUES ('20A05', '노다희', '중국어문학', 'AB5', '010-0531-4945')
      INTO PROFESSOR (PRO_NO, PRO_NAME, PRO_MAJOR, PRO_DEPTNO, PRO_TELNO)
      VALUES ('20B06', '최현태', '스마트팩토리학', 'BC1', '010-9285-1886')
      INTO PROFESSOR (PRO_NO, PRO_NAME, PRO_MAJOR, PRO_DEPTNO, PRO_TELNO)
      VALUES ('20B07', '탁성하', '철도토목학', 'BC2', '010-5102-3459')
      INTO PROFESSOR (PRO_NO, PRO_NAME, PRO_MAJOR, PRO_DEPTNO, PRO_TELNO)
      VALUES ('21B08', '설문희', '컴퓨터정보학', 'BC3', '010-5230-2843')
      INTO PROFESSOR (PRO_NO, PRO_NAME, PRO_MAJOR, PRO_DEPTNO, PRO_TELNO)
      VALUES ('21B09', '하준현', '소방안전관리학', 'BC4', '010-3882-7013')
      INTO PROFESSOR (PRO_NO, PRO_NAME, PRO_MAJOR, PRO_DEPTNO, PRO_TELNO)
      VALUES ('22B10', '허현숙', '사회복지학', 'BC5', '010-2333-9570')
    SELECT *
      FROM DUAL;
      
SELECT * FROM PROFESSOR; -- 교수 테이블

DELETE FROM PROFESSOR;

------------------------------------------------------------------

ALTER TABLE STUDENT MODIFY (STD_ADD VARCHAR2(100)); 

INSERT ALL
       INTO STUDENT (STD_NO, STD_NAME, STD_ID, STD_TELNO, STD_ADD, PRO_NO)
       VALUES ('19AB100001', '남원미', '670321-2406923', '010-1234-5678', '경상북도 경주시 건천읍 단석로 1757', '18A01')
       INTO STUDENT (STD_NO, STD_NAME, STD_ID, STD_TELNO, STD_ADD, PRO_NO)
       VALUES ('18AB200002', '봉다현', '780403-1406923', '010-5678-1234', '서울특별시 광진구 동일로18가길 26)', '18A02')
       INTO STUDENT (STD_NO, STD_NAME, STD_ID, STD_TELNO, STD_ADD, PRO_NO)
       VALUES ('19AB300003', '허우준', '890513-1406923', '010-1234-5678', '인천광역시 연수구 학나래로 51', '19A03')
       INTO STUDENT (STD_NO, STD_NAME, STD_ID, STD_TELNO, STD_ADD, PRO_NO)
       VALUES ('15AB400004', '백문영', '970627-2406923', '010-5678-1234', '경상북도 봉화군 소천면 갈산로 737', '19A04')
       INTO STUDENT (STD_NO, STD_NAME, STD_ID, STD_TELNO, STD_ADD, PRO_NO)
       VALUES ('20AB500005', '황시원', '000730-3406923', '010-1234-5678', '인천광역시 강화군 강화읍 강화대로192번길 5', '20A05')
       INTO STUDENT (STD_NO, STD_NAME, STD_ID, STD_TELNO, STD_ADD, PRO_NO)
       VALUES ('20BC100006', '송세연', '020801-4406923', '010-5678-1234', '전라남도 나주시 다시면 백호로 151-8', '20B06')
       INTO STUDENT (STD_NO, STD_NAME, STD_ID, STD_TELNO, STD_ADD, PRO_NO)
       VALUES ('21BC200007', '남궁창희', '040912-3406923', '010-1234-5678', '대전광역시 동구 우암로387번길 7)', '20B07')
       INTO STUDENT (STD_NO, STD_NAME, STD_ID, STD_TELNO, STD_ADD, PRO_NO)
       VALUES ('21BC300008', '탁정철', '941021-1406923', '010-5678-1234', '경기도 용인시 처인구 신기로107번길 11-8', '21B08')
       INTO STUDENT (STD_NO, STD_NAME, STD_ID, STD_TELNO, STD_ADD, PRO_NO)
       VALUES ('22BC400009', '안세혁', '871121-2406923', '010-1234-5678', '경기도 성남시 중원구 둔촌대로127번길 22', '21B09')
       INTO STUDENT (STD_NO, STD_NAME, STD_ID, STD_TELNO, STD_ADD, PRO_NO)
       VALUES ('23BC500010', '성민선', '031221-4406923', '010-5678-1234', '경상북도 경주시 강동면 오금낙산길 26', '22B10')
    SELECT *
      FROM DUAL;
      
SELECT * FROM STUDENT; -- 학생 테이블

DELETE FROM STUDENT;

------------------------------------------------------------------

INSERT ALL
      INTO SUBJECT (SUB_CD, SUB_NAME, SUB_GRADE)
      VALUES ('211AB101', '국어학개론', 3)
      INTO SUBJECT (SUB_CD, SUB_NAME, SUB_GRADE)
      VALUES ('212AB202', '영어문법론', 3)
      INTO SUBJECT (SUB_CD, SUB_NAME, SUB_GRADE)
      VALUES ('221AB303', '독어문해1', 2)
      INTO SUBJECT (SUB_CD, SUB_NAME, SUB_GRADE)
      VALUES ('222AB404', '일어말하기2', 3)
      INTO SUBJECT (SUB_CD, SUB_NAME, SUB_GRADE)
      VALUES ('231AB505', '중국의문화1', 2)
      INTO SUBJECT (SUB_CD, SUB_NAME, SUB_GRADE)
      VALUES ('211BC106', '스마트팩토리의이해', 3)
      INTO SUBJECT (SUB_CD, SUB_NAME, SUB_GRADE)
      VALUES ('212BC207', '토목학개론', 2)
      INTO SUBJECT (SUB_CD, SUB_NAME, SUB_GRADE)
      VALUES ('221BC308', '컴퓨터OS이해', 2)
      INTO SUBJECT (SUB_CD, SUB_NAME, SUB_GRADE)
      VALUES ('222BC409', '소방안전개론', 3)
      INTO SUBJECT (SUB_CD, SUB_NAME, SUB_GRADE)
      VALUES ('231BC510', '사회복지실천론', 3)
    SELECT *
      FROM DUAL;
      
SELECT * FROM SUBJECT; -- 과목 테이블

DELETE FROM SUBJECT;

------------------------------------------------------------------

INSERT ALL
      INTO COUNSEL (CNS_DATE, STD_NO, PRO_NO, CNS_REMARK)
      VALUES (TO_DATE('20180416','YYYYMMDD'), '19AB100001', '18A01', '학점관리가 안되서 큰일입니다.')
      INTO COUNSEL (CNS_DATE, STD_NO, PRO_NO, CNS_REMARK)
      VALUES (TO_DATE('20190508','YYYYMMDD'), '18AB200002', '18A02', '영어과목 숙제 좀 줄여주세요.')
      INTO COUNSEL (CNS_DATE, STD_NO, PRO_NO, CNS_REMARK)
      VALUES (TO_DATE('20200626','YYYYMMDD'), '19AB300003', '19A03', '독어 시간에 공부에 집중이 안됩니다.')
      INTO COUNSEL (CNS_DATE, STD_NO, PRO_NO, CNS_REMARK)
      VALUES (TO_DATE('20210712','YYYYMMDD'), '15AB400004', '19A04', '일어 시간에 교수님이 수업을 안하십니다.')
      INTO COUNSEL (CNS_DATE, STD_NO, PRO_NO, CNS_REMARK)
      VALUES (TO_DATE('20180826','YYYYMMDD'), '20AB500005', '20A05', '중국어 교수님이 중국어를 모릅니다')
      INTO COUNSEL (CNS_DATE, STD_NO, PRO_NO, CNS_REMARK)
      VALUES (TO_DATE('20190215','YYYYMMDD'), '20BC100006', '20B06', '앞에 사람이 자꾸 신경쓰여서 공부에 집중할 수 없습니다.')
      INTO COUNSEL (CNS_DATE, STD_NO, PRO_NO, CNS_REMARK)
      VALUES (TO_DATE('20220416','YYYYMMDD'), '21BC200007', '20B07', '토목학 개론 만든 사람... 하...')
      INTO COUNSEL (CNS_DATE, STD_NO, PRO_NO, CNS_REMARK)
      VALUES (TO_DATE('20230602','YYYYMMDD'), '21BC300008', '21B08', '컴퓨터의 컴자도 모르는데 교수님 진도가 너무 빠릅니다.')
      INTO COUNSEL (CNS_DATE, STD_NO, PRO_NO, CNS_REMARK)
      VALUES (TO_DATE('20211225','YYYYMMDD'), '22BC400009', '21B09', '졸업하고 나면 어떻게 취업해야 할지 고민입니다.')
      INTO COUNSEL (CNS_DATE, STD_NO, PRO_NO, CNS_REMARK)
      VALUES (TO_DATE('20190106','YYYYMMDD'), '23BC500010', '22B10', '사회복지학과는 남에게 봉사하는 사람인줄 압니다.')
    SELECT *
      FROM DUAL;
      
SELECT * FROM COUNSEL; -- 상담 테이블

DELETE FROM COUNSEL;

------------------------------------------------------------------
ALTER TABLE LECTURE MODIFY (LEC_ROOM VARCHAR2(30)); 

-- LEC_WEEK(요일) 0:일요일, 1:월요일, 2:화요일, 3:수요일, 4:목요일, 5:금요일, 6:토요일

INSERT ALL
      INTO LECTURE (PRO_NO, SUB_CD, LEC_WEEK, LEC_TIME, LEC_ROOM, LEC_PERSON)
      VALUES ('18A01', '211AB101', '1', '0900-1200', '한빛관202호', 30)
      INTO LECTURE (PRO_NO, SUB_CD, LEC_WEEK, LEC_TIME, LEC_ROOM, LEC_PERSON)
      VALUES ('18A02', '212AB202', '2', '1300-1600', '다솔관301호', 30)
      INTO LECTURE (PRO_NO, SUB_CD, LEC_WEEK, LEC_TIME, LEC_ROOM, LEC_PERSON)
      VALUES ('19A03', '221AB303', '3', '1600-1800', '운암관404호', 20)
      INTO LECTURE (PRO_NO, SUB_CD, LEC_WEEK, LEC_TIME, LEC_ROOM, LEC_PERSON)
      VALUES ('19A04', '222AB404', '4', '0900-1200', '인문경영관101호', 30)
      INTO LECTURE (PRO_NO, SUB_CD, LEC_WEEK, LEC_TIME, LEC_ROOM, LEC_PERSON)
      VALUES ('20A05', '231AB505', '5', '1600-1800', '국제교육센터B-3호', 20)
      INTO LECTURE (PRO_NO, SUB_CD, LEC_WEEK, LEC_TIME, LEC_ROOM, LEC_PERSON)
      VALUES ('20B06', '211BC106', '1', '1300-1600', '스마트팩토리103동 2호', 30)
      INTO LECTURE (PRO_NO, SUB_CD, LEC_WEEK, LEC_TIME, LEC_ROOM, LEC_PERSON)
      VALUES ('20B07', '212BC207', '2', '1600-1800', '담헌실학관404호', 20)
      INTO LECTURE (PRO_NO, SUB_CD, LEC_WEEK, LEC_TIME, LEC_ROOM, LEC_PERSON)
      VALUES ('21B08', '221BC308', '3', '1600-1800', '공학4관302호', 20)
      INTO LECTURE (PRO_NO, SUB_CD, LEC_WEEK, LEC_TIME, LEC_ROOM, LEC_PERSON)
      VALUES ('21B09', '222BC409', '4', '0900-1200', '공학3관102호', 30)
      INTO LECTURE (PRO_NO, SUB_CD, LEC_WEEK, LEC_TIME, LEC_ROOM, LEC_PERSON)
      VALUES ('22B10', '231BC510', '5', '1600-1800', '공학2관302호', 30)
    SELECT *
      FROM DUAL;
      
SELECT * FROM LECTURE; -- 강의 테이블

DELETE FROM LECTURE;

------------------------------------------------------------------

INSERT ALL
      INTO CLASS (STD_NO, SUB_CD, CLS_SCORE)
      VALUES ('19AB100001', '211AB101', 100)
      INTO CLASS (STD_NO, SUB_CD, CLS_SCORE)
      VALUES ('18AB200002', '212AB202', 95)
      INTO CLASS (STD_NO, SUB_CD, CLS_SCORE)
      VALUES ('19AB300003', '221AB303', 90)
      INTO CLASS (STD_NO, SUB_CD, CLS_SCORE)
      VALUES ('15AB400004', '222AB404', 85)
      INTO CLASS (STD_NO, SUB_CD, CLS_SCORE)
      VALUES ('20AB500005', '231AB505', 80)
      INTO CLASS (STD_NO, SUB_CD, CLS_SCORE)
      VALUES ('20BC100006', '211BC106', 75)
      INTO CLASS (STD_NO, SUB_CD, CLS_SCORE)
      VALUES ('21BC200007', '212BC207', 70)
      INTO CLASS (STD_NO, SUB_CD, CLS_SCORE)
      VALUES ('21BC300008', '221BC308', 93)
      INTO CLASS (STD_NO, SUB_CD, CLS_SCORE)
      VALUES ('22BC400009', '222BC409', 86)
      INTO CLASS (STD_NO, SUB_CD, CLS_SCORE)
      VALUES ('23BC500010', '231BC510', 74)
    SELECT *
      FROM DUAL;
      
SELECT * FROM CLASS; -- 성적 테이블

DELETE FROM CLASS;

------------------------------------------------------------------
SELECT * FROM PROFESSOR; -- 교수 테이블
SELECT * FROM STUDENT; -- 학생 테이블
SELECT * FROM SUBJECT; -- 과목 테이블
SELECT * FROM COUNSEL; -- 상담 테이블
SELECT * FROM LECTURE; -- 강의 테이블
SELECT * FROM CLASS; -- 성적 테이블

-- 문 1) 2023년도 1학기 수강생들의 학번, 과목코드, 성적을 내림차순 성적순으로 모두 조회하시오.
SELECT STD_NO AS "학번",
       SUB_CD AS "과목코드",
       CLS_SCORE AS "성적"
  FROM CLASS
 WHERE 1=1
   AND SUBSTR(SUB_CD, 1,3) = '231' -- 23년도 1학기라는 의미
 ORDER BY CLS_SCORE DESC;

------------------------------------------------------------------

-- 문 2) 과목코드가 222AB404 과목의 수강현황을 학번, 학생명, 과목코드, 과목명, 성적순으로 조회하시오.
SELECT a.STD_NO AS "학번",
       a.STD_NAME AS "학생명",
       b.SUB_CD AS "과목코드",
       b.SUB_NAME AS "과목명",
       c.CLS_SCORE AS "성적"
  FROM STUDENT a, SUBJECT b, CLASS c
 WHERE a.STD_NO = c.STD_NO
   AND b.SUB_CD = c.SUB_CD
   AND b.SUB_CD = '222AB404';

------------------------------------------------------------------

-- 문 3) 2022년도 2학기 총 수강생들은 몇 명인가?
SELECT COUNT(*) AS "총수강생"
  FROM LECTURE
 WHERE SUBSTR(SUB_CD, 1, 3) = '222'; -- 22년도 2학기라는 뜻

------------------------------------------------------------------

-- 문 4) 2023년도 1학기 수강생들의 학번, 과목코드, 학점을 조회하시오
SELECT STD_NO AS "학번",
       SUB_CD AS "과목코드",
       CLS_SCORE AS "성적",
       CASE WHEN CLS_SCORE < 60 THEN 'F'
            WHEN CLS_SCORE >= 60 AND CLS_SCORE < 65 THEN 'D0'
            WHEN CLS_SCORE >= 65 AND CLS_SCORE < 70 THEN 'D+'
            WHEN CLS_SCORE >= 70 AND CLS_SCORE < 75 THEN 'C0'
            WHEN CLS_SCORE >= 75 AND CLS_SCORE < 80 THEN 'C+'
            WHEN CLS_SCORE >= 80 AND CLS_SCORE < 85 THEN 'B0'
            WHEN CLS_SCORE >= 85 AND CLS_SCORE < 90 THEN 'B+'
            WHEN CLS_SCORE >= 90 AND CLS_SCORE < 95 THEN 'A0'
            ELSE 'A+'
        END AS "학점"
  FROM CLASS
 WHERE SUBSTR(SUB_CD, 1, 3) = '231'
 ORDER BY CLS_SCORE DESC;

------------------------------------------------------------------

-- 문 5) 2021년도에 발생한 상담건수는?
SELECT COUNT(*) AS "상담건수"
  FROM COUNSEL
 WHERE SUBSTR(CNS_DATE, 1, 2) = '21';

------------------------------------------------------------------

-- 문 6) 2021년도에 개설한 과목의 교수명, 과목명, 요일, 강의시간, 강의장소, 강의인원은?
-- LEC_WEEK(요일) 0:일요일, 1:월요일, 2:화요일, 3:수요일, 4:목요일, 5:금요일, 6:토요일
SELECT b.PRO_NAME AS "교수명",
       b.PRO_MAJOR AS "과목명",
       CASE WHEN LEC_WEEK = 0 THEN '일요일'
            WHEN LEC_WEEK = 1 THEN '월요일'
            WHEN LEC_WEEK = 2 THEN '화요일'
            WHEN LEC_WEEK = 3 THEN '수요일'
            WHEN LEC_WEEK = 4 THEN '목요일'
            WHEN LEC_WEEK = 5 THEN '금요일'
            WHEN LEC_WEEK = 6 THEN '토요일'
       END AS "요일",
       a.LEC_TIME AS "강의시간",
       a.LEC_ROOM AS "강의장소",
       a.LEC_PERSON AS "강의인원"
  FROM LECTURE a, PROFESSOR b
 WHERE 1=1
   AND a.PRO_NO = b.PRO_NO
   AND SUBSTR(SUB_CD, 1, 2) = '21';

'대덕인재개발원 > 대덕인재개발원_Oracle DB' 카테고리의 다른 글

230720 SQL 강의 (0)	2023.07.20
230719 SQL 강의 (0)	2023.07.19
230717 SQL 강의 (0)	2023.07.17
230714 SQL 강의 (1)	2023.07.17
230713 SQL 강의 (0)	2023.07.17

'대덕인재개발원/대덕인재개발원_Oracle DB' Related Articles

거니의 velog

230718 SQL 강의 본문

230718 SQL 강의

'대덕인재개발원 > 대덕인재개발원_Oracle DB' 카테고리의 다른 글

티스토리툴바