1일차 과제

 

0406_SQL(연습문제)_A.docx

0.01MB

0406_SQL(연습문제)_B.docx

0.01MB

 

 

----------------------------A-----------------------------

/*0406_ HOMEWORK*/
--Q1
DESC STUDENT;
DESC SUBJECT;
DESC ENROL;

--Q2
SELECT * FROM ENROL;
SELECT * FROM STUDENT;
SELECT * FROM SUBJECT;

--Q4
SELECT*FROM ENROL;

--Q6
SELECT SUB_NO, SUB_NAME FROM SUBJECT;

--Q7
SELECT STU_NO , STU_NAME, STU_GENDER FROM STUDENT;

--Q9
SELECT STU_NO, STU_NAME, STU_GRADE, STU_CLASS FROM STUDENT;

--Q11
SELECT SUB_NO, STU_NO,ENR_GRADE
FROM ENROL;

--Q14
SELECT DISTINCT STU_GENDER FROM STUDENT;

--Q16
SELECT DISTINCT STU_DEPT , STU_GRADE FROM STUDENT;

--Q18
SELECT STU_HEIGHT+5 
FROM STUDENT
WHERE STU_HEIGHT IS NOT NULL;

--Q20
SELECT STU_NO 학번, STU_NAME 이름
FROM STUDENT;

--Q21
SELECT SUB_NO 번호, STU_NO 학번, ENR_GRADE 학점
FROM ENROL;

--Q22
SELECT SUB_NAME 학과명, SUB_PROF 교수명
FROM SUBJECT;

--Q23
SELECT STU_DEPT || '학과 ' ||STU_NAME ||'입니다'
FROM STUDENT;

--Q27
SELECT *
FROM STUDENT
WHERE STU_DEPT='전기전자';

--Q28
SELECT*
FROM STUDENT
WHERE STU_HEIGHT >=170;

--Q29
SELECT *
FROM STUDENT
WHERE STU_WEIGHT >=65;

--Q31
SELECT STU_NO, STU_NAME
FROM STUDENT
WHERE STU_DEPT='컴퓨터정보';

--Q33
SELECT STU_NAME
FROM STUDENT
WHERE STU_GENDER='M';

--Q37
SELECT STU_NO
FROM ENROL
WHERE ENR_GRADE >=80;

--Q38
SELECT *
FROM STUDENT
WHERE STU_NAME='김인중';

--Q39
SELECT*
FROM STUDENT
WHERE STU_DEPT='컴퓨터정보' AND STU_GRADE=1;

--Q40
SELECT*
FROM STUDENT
WHERE STU_DEPT='기계' AND STU_GRADE=2;

--Q41
SELECT *
FROM STUDENT
WHERE STU_GENDER='F' AND STU_WEIGHT<=60;

--Q42
SELECT STU_NAME
FROM STUDENT
WHERE STU_DEPT != '컴퓨터정보' AND STU_GRADE=1;

--Q44
SELECT STU_NAME
FROM STUDENT
WHERE STU_GRADE=2 AND STU_CLASS='A';

--Q45
SELECT STU_NO, STU_NAME, STU_HEIGHT
FROM STUDENT
WHERE STU_HEIGHT BETWEEN 160 AND 170;

--Q46
SELECT STU_NO, STU_NAME, STU_DEPT
FROM STUDENT
WHERE STU_NO LIKE '2015%';

--Q47
SELECT *
FROM STUDENT
WHERE STU_GRADE IN(1,3);

--Q49
SELECT*
FROM STUDENT
WHERE STU_NO LIKE '____%20__%';

--Q51
SELECT *
FROM STUDENT
WHERE STU_NO LIKE '2006%';

--Q52
SELECT STU_NAME
FROM STUDENT
WHERE STU_HEIGHT IS NULL;

--Q53
SELECT STU_NO, STU_NAME
FROM STUDENT
WHERE STU_HEIGHT IS NOT NULL;

--Q56
SELECT STU_NO, STU_NAME
FROM STUDENT
ORDER BY STU_NO ASC;

 

 

----------------------------B-----------------------------
--Q57
SELECT STU_NO, STU_NAME
FROM STUDENT
ORDER BY STU_NAME ASC;

--Q58
SELECT STU_NO, STU_NAME, STU_DEPT
FROM STUDENT
ORDER BY STU_DEPT ASC;

--Q59
SELECT *
FROM STUDENT
ORDER BY STU_DEPT, STU_GRADE DESC;

--Q60
SELECT *
FROM STUDENT
ORDER BY STU_DEPT, STU_CLASS ASC;

--Q61
SELECT STU_NO, STU_NAME
FROM STUDENT
ORDER BY STU_NAME, STU_DEPT, STU_GENDER;

--Q62
SELECT LOWER(STU_GENDER)
FROM STUDENT;

--Q63
SELECT UPPER(STU_GENDER)
FROM STUDENT;

--Q65
SELECT STU_DEPT || '과 ' ||STU_GRADE||'학년 '||STU_NAME||' 입니다' AS 학과성명
fROM STUDENT;

--Q66
SELECT STU_NAME, SUBSTR(STU_NAME,1,2)
FROM STUDENT;

--Q67
SELECT STU_NAME, STU_DEPT, SUBSTR(STU_DEPT,2,1)
FROM STUDENT;

--Q69
SELECT STU_DEPT, LENGTH(STU_DEPT) 
FROM STUDENT;

--Q70
SELECT INSTR(STU_NAME,'김',1)
FROM STUDENT;

--Q71
SELECT INSTR(STU_DEPT,'기',1)
FROM STUDENT;

--Q73
SELECT LPAD(STU_DEPT,20,'%')
FROM STUDENT;

--Q74
SELECT STU_NO,STU_NAME, ROUND(STU_HEIGHT,-1)
FROM STUDENT;

--Q75
SELECT STU_NO,STU_NAME, TRUNC(STU_HEIGHT,-2)
FROM STUDENT;

--Q76
SELECT MOD(STU_WEIGHT,30)
FROM STUDENT;

--Q77
SELECT STU_HEIGHT, NVL(TO_CHAR(STU_HEIGHT),'미기록')
FROM STUDENT;

--Q78
SELECT STU_NO, STU_NAME, NVL(STU_HEIGHT+STU_WEIGHT,'0')
FROM STUDENT;

--Q79
SELECT STU_NO, STU_NAME, NVL(STU_HEIGHT-STU_WEIGHT,'0')
FROM STUDENT;


--Q80
SELECT STU_NO, STU_NAME,  NVL(TO_CHAR(STU_HEIGHT),'입력요망')
FROM STUDENT;

--Q82
SELECT NULLIF(STU_GENDER,'M')
FROM STUDENT;

--Q85
SELECT MAX(STU_HEIGHT)
FROM STUDENT;

--Q86
SELECT MIN(STU_HEIGHT)
FROM STUDENT;

--Q88
SELECT MAX(STU_NAME), MIN(STU_NAME)
FROM STUDENT;

--Q89
SELECT MAX(STU_WEIGHT),MAX(STU_HEIGHT), MIN(STU_WEIGHT),MIN(STU_HEIGHT)
FROM STUDENT;

--Q92
SELECT COUNT(INSTR(STU_NAME,1,1))
FROM STUDENT;

--Q93
SELECT COUNT(*) 
FROM STUDENT;

--Q96
SELECT COUNT(STU_DEPT) ,STU_DEPT 
FROM STUDENT
GROUP BY STU_DEPT
ORDER BY COUNT(STU_DEPT) DESC;

--Q97
SELECT COUNT(STU_GRADE)
FROM STUDENT
GROUP BY STU_GRADE;

--Q99
SELECT ROUND(AVG(STU_HEIGHT),-1)
FROM STUDENT
GROUP BY STU_DEPT;

--Q101
SELECT STU_DEPT,STU_GRADE, ROUND(AVG(STU_HEIGHT))
FROM STUDENT
GROUP BY STU_DEPT, STU_GRADE
HAVING ROUND(AVG(STU_HEIGHT)) IS NOT NULL;

--Q104
SELECT COUNT(STU_DEPT), STU_DEPT
FROM STUDENT
GROUP BY STU_DEPT
ORDER BY STU_DEPT DESC;


--Q105
SELECT ROUND(AVG(STU_HEIGHT)), STU_DEPT
FROM STUDENT
GROUP BY STU_DEPT
ORDER BY  ROUND(AVG(STU_HEIGHT)) DESC;

--Q106
SELECT ROUND(AVG(STU_WEIGHT)) 
FROM STUDENT
GROUP BY STU_DEPT, STU_GRADE
ORDER BY ROUND(AVG(STU_WEIGHT)) DESC;

--Q107
SELECT ROUND(AVG(STU_HEIGHT)), STU_DEPT
FROM STUDENT
GROUP BY STU_DEPT
ORDER BY ROUND(AVG(STU_HEIGHT)) DESC;

--Q108
SELECT STU_DEPT, ROUND(AVG(STU_HEIGHT))
FROM STUDENT
GROUP BY STU_DEPT
HAVING ROUND(AVG(STU_HEIGHT))>=170
ORDER BY ROUND(AVG(STU_HEIGHT)) ASC;