workbook 과제

Basic

--1--
SELECT DEPARTMENT_NAME "학과 명", 텍스트CATEGORY "계열"
FROM TB_DEPARTMENT;
--2--
SELECT DEPARTMENT_NAME || '의 정원은 ' || CAPACITY || ' 명 입니다.' "학과별 정원"
FROM TB_DEPARTMENT;
--3--
SELECT STUDENT_NAME
FROM TB_STUDENT
WHERE DEPARTMENT_NO = '001' AND SUBSTR(STUDENT_SSN, 8, 1) IN ('2', '4')
AND ABSENCE_YN = 'Y';
--4--
SELECT STUDENT_NAME
FROM TB_STUDENT
WHERE STUDENT_NO IN ('A513079', 'A513090', 'A513091', 'A513110', 'A513119')
ORDER BY STUDENT_NO DESC;
--5--
SELECT DEPARTMENT_NAME, CATEGORY
FROM TB_DEPARTMENT
WHERE CAPACITY BETWEEN 20 AND 30;
--6--
SELECT PROFESSOR_NAME
FROM TB_PROFESSOR
WHERE DEPARTMENT_NO IS NULL;
--7--
SELECT *
FROM TB_STUDENT
WHERE DEPARTMENT_NO IS NULL;
--8--
SELECT CLASS_NO
FROM TB_CLASS
WHERE PREATTENDING_CLASS_NO IS NOT NULL
--9--
SELECT DISTINCT CATEGORY
FROM TB_DEPARTMENT
ORDER BY CATEGORY;
--10--
SELECT STUDENT_NO, STUDENT_NAME, STUDENT_SSN
FROM TB_STUDENT
WHERE SUBSTR(STUDENT_NO, 2, 1) = 2 AND STUDENT_ADDRESS LIKE '%전주%'
AND ABSENCE_YN = 'N'
ORDER BY STUDENT_NAME;

Fuction

--1--
SELECT STUDENT_NO AS "학번", STUDENT_NAME AS "이름", TO_CHAR(ENTRANCE_DATE,'YYYY-MM-DD') AS "입학년도"
FROM TB_STUDENT
WHERE DEPARTMENT_NO = 002
ORDER BY ENTRANCE_DATE;
--2--
.
--3--
.
--4--
SELECT SUBSTR(PROFESSOR_NAME, 2) AS "이름"
FROM TB_PROFESSOR;
--5--
SELECT STUDENT_NO, STUDENT_NAME
FROM TB_STUDENT
WHERE (TO_CHAR(ENTRANCE_DATE, 'YYYY') - TO_CHAR('19' || SUBSTR(STUDENT_SSN, 1, 2))) > '19
--6--
SELECT TO_CHAR(TO_DATE(20201225), 'DAY')
FROM DUAL;
--7--
SELECT TO_CHAR(TO_DATE('99/10/11','YY/MM/DD'), 'YYYY"년" MM"월" DD"일"') AS "2099",
TO_CHAR(TO_DATE('49/10/11','YY/MM/DD'), 'YYYY"년" MM"월" DD"일"') AS "2049",
TO_CHAR(TO_DATE('99/10/11','RR/MM/DD'), 'YYYY"년" MM"월" DD"일"') AS "1999",
TO_CHAR(TO_DATE('49/10/11','RR/MM/DD'), 'YYYY"년" MM"월" DD"일"') AS "2049"
FROM DUAL;
--8--
SELECT STUDENT_NO, STUDENT_NAME
FROM TB_STUDENT
WHERE NOT STUDENT_NO LIKE 'A%';
--9--
SELECT ROUND(AVG(POINT), 1) AS "평점"
FROM TB_GRADE
WHERE STUDENT_NO = 'A517178
--10--
.
--11--
SELECT COUNT(
FROM TB_STUDENT
WHERE COACH_PROFESSOR_NO IS NULL;
--12--
--13--
.
--14--
SELECT STUDENT_NAME AS "동일이름",
COUNT( AS "동명인 수"
FROM TB_STUDENT
GROUP BY STUDENT_NAME
HAVING COUNT(* != 1
ORDER BY 1;

option

 --1--

SELECT STUDENT_NAME AS "학생 이름", STUDENT_ADDRESS AS "주소지"
FROM TB_STUDENT
ORDER BY 1;
--2--
SELECT STUDENT_NAME, STUDENT_SSN
FROM TB_STUDENT
WHERE ABSENCE_YN = 'Y'
ORDER BY 2 DESC;
--3--
SELECT STUDENT_NAME AS "학생이름", STUDENT_NO AS "학번", STUDENT_ADDRESS AS "거주지 주소"
FROM TB_STUDENT
WHERE STUDENT_NO LIKE '9%'
AND STUDENT_ADDRESS LIKE '%경기도%' OR STUDENT_ADDRESS LIKE '%강원도%'
ORDER BY STUDENT_NAME;
--4--
SELECT PROFESSOR_NAME, PROFESSOR_SSN
FROM TB_PROFESSOR
WHERE DEPARTMENT_NO = '005'
ORDER BY PROFESSOR_SSN;
--5--
--6--
SELECT STUDENT_NO, STUDENT_NAME, DEPARTMENT_NAME
FROM TB_STUDENT
JOIN TB_DEPARTMENT USING (DEPARTMENT_NO)
ORDER BY SUBSTR(STUDENT_NAME, 2);
--7--
SELECT CLASS_NAME, DEPARTMENT_NAME
FROM TB_CLASS
JOIN TB_DEPARTMENT USING (DEPARTMENT_NO)
ORDER BY DEPARTMENT_NO;
--8--
SELECT CLASS_NAME, PROFESSOR_NAME
FROM TB_CLASS
JOIN TB_CLASS_PROFESSOR USING (CLASS_NO)
JOIN TB_PROFESSOR USING (PROFESSOR_NO);
--9--
--10--
--11--
SELECT DEPARTMENT_NAME AS "학과이름", STUDENT_NAME AS "학생이름", PROFESSOR_NAME AS "지도교수이름"
FROM TB_STUDENT
JOIN TB_DEPARTMENT USING (DEPARTMENT_NO)
JOIN TB_PROFESSOR ON (COACH_PROFESSOR_NO = PROFESSOR_NO)
WHERE STUDENT_NO = 'A313047';
--12--
SELECT STUDENT_NAME, TERM_NO
FROM TB_STUDENT
JOIN TB_GRADE USING (STUDENT_NO)
JOIN TB_CLASS USING (CLASS_NO)
WHERE CLASS_NAME = '인간관계론' AND TERM_NO LIKE '2007%';
--13--
SELECT CLASS_NAME, DEPARTMENT_NAME
FROM TB_CLASS
JOIN TB_DEPARTMENT USING (DEPARTMENT_NO)
LEFT JOIN TB_CLASS_PROFESSOR USING (CLASS_NO)
WHERE CATEGORY = '예체능' AND PROFESSOR_NO IS NULL
ORDER BY DEPARTMENT_NO, CLASS_NAME;
--14--
SELECT STUDENT_NAME AS "학생이름", NVL(PROFESSOR_NAME, '지도교수 미지정') AS "지도교수"
FROM TB_STUDENT
JOIN TB_DEPARTMENT USING (DEPARTMENT_NO)
LEFT JOIN TB_PROFESSOR ON (PROFESSOR_NO = COACH_PROFESSOR_NO)
WHERE DEPARTMENT_NAME = '서반아어학과'
ORDER BY STUDENT_NO;
--15--
--16--
--17--
--18--
--19--