질의 1. 3학년 학생의 학번과 이름을 구하라.
SQL> select 학번,이름
2 from 학생
3 where 학년='3';
학번 이름
----- ------------
88033 이육사
86032 정소일
질의 2. 학생테이블에서 학번과 피트(ft)로 환산된 신장을 구하라.
SQL> select 학번,신장*0.0328 바보 //바보는 엘리어스....
2 from 학생;
학번 바보
----- ---------
88001 5.576
87002 5.5104
87012 5.904
88033 5.74
85014 6.0024
86032 5.412
6 개의 행이 선택되었습니다.
질의 3. 소속이 CD인 학생중 신장이 170이상되는 학생의 학년을 구하라
SQL> select 학년
2 from 학생
3 where 소속='CD' and 신장>=170;
학년
---------
2
2
질의 4. 소속이 CD인 학생을 신장 크기 순으로 구하라.
SQL> select *
2 from 학생
3 where 소속='CD'
4 order by 신장; ([desc;] // 를 사용하면 180->170->168순으로 된다.)
학번 이름 학년 신장 소속
----- ------------ --------- --------- ----------
87002 이일삼 4 168 CD
88001 홍길동 2 170 CD
87012 김이사 2 180 CD
질의 5. 소속이 같은 학생테이블과 설강테이블로부터 모든정보를 구하라.
SQL> select 학생.*,설강.*
2 from 학생, 설강
3 where 학생.소속=설강.소속;
학번 이름 학년 신장 소속 번 교수명 과 소속
----- ------------ --------- --------- ---------- --- ------------ --- ------
88001 홍길동 2 170 CD 101 김일삼 SE CD
87002 이일삼 4 168 CD 101 김일삼 SE CD
87012 김이사 2 180 CD 101 김일삼 SE CD
88001 홍길동 2 170 CD 102 권오영 DB CD
87002 이일삼 4 168 CD 102 권오영 DB CD
87012 김이사 2 180 CD 102 권오영 DB CD
88001 홍길동 2 170 CD 104 이 상 FP CD
87002 이일삼 4 168 CD 104 이 상 FP CD
87012 김이사 2 180 CD 104 이 상 FP CD
85014 김일구 4 183 ED 105 이석영 CS ED
86032 정소일 3 165 ED 105 이석영 CS ED
11 개의 행이 선택되었습니다.
질의 6.이육사 학생이 수강한 교수명과 성적을 구하시오.
SQL> select 교수명,성적 //"select 설강.교수명, 수강.성적" 과 같다
2 from 설강,수강
3 where 수강.번호=설강.번호 and 수강.학번 =
4 (select 학번
5 from 학생
6 where 학생.이름 = '이육사');
교수명 성적
------------ ---------
권오영 85
질의 7. 소속이 같은 두학생의 이름을 모두 구하라.
SQL> select 학생.이름 , 학생.소속,a.이름
2 from 학생,학생 a
3 where 학생.소속 = a.소속
4 order by 학생.이름;
이름 소속 이름
------------ ---------- ------------
김이사 CD 홍길동
김이사 CD 이일삼
김이사 CD 김이사
김일구 ED 김일구
김일구 ED 정소일
이육사 ID 이육사
이일삼 CD 홍길동
이일삼 CD 이일삼
이일삼 CD 김이사
정소일 ED 김일구
정소일 ED 정소일
홍길동 CD 홍길동
홍길동 CD 이일삼
홍길동 CD 김이사
14 개의 행이 선택되었습니다.
질의 8. 전체 학생수를 구하라.
SQL> select count(학번) 전체학생수 //엘리어스 된 상태.
2 from 학생;
전체학생수
----------
6
질의 9. 3과목 이상 수강 신청한 학생의 학번을 구하라.
SQL> select 학번
2 from 수강
3 group by 학번
4 having count(학번)>=3;
학번
-----
88001
질의 10.이씨 성을 갖는 학생의 모든 정보를 구하라
SQL> select 학생.* , 수강.*
2 from 학생,수강
3 where 학생.학번=수강.학번 and 학생.이름 like '이%';
학번 이름 학년 신장 소속 학번 번 성적
----- ------------ --------- --------- ---------- ----- --- ---------
87002 이일삼 4 168 CD 87002 101 80
87002 이일삼 4 168 CD 87002 104 95
88033 이육사 3 175 ID 88033 102 85
질의 11. 3학년 이상되는 학생의 학번과 이름을 구하라.
SQL> select 학번,이름
2 from 학생 //학생Table
3 where 학년 >= 3; //조건
학번 이름 //출력
----- --------------------
87002 이일삼
88033 이육사
85014 김일구
86032 정소일
질의 12. 설강번호 101을 수강하는 학생의 이름을 구하라
SQL> select 이름
2 from 학생,수강
3 where 학생.학번 = 수강.학번 and 수강.번호='101';
이름
--------------------
이일삼
홍길동
질의 13. 소속이 MD인 설강 과목을 수강하는 학생의 이름을 구하라
SQL> select 이름
2 from 학생,수강,설강
3 where 학생.학번=수강.학번 and 수강.번호=설강.번호 and 설강.소속='MD';
이름
--------------------
김이사
홍길동
질의 14. 학번이 88001인 학생과 소속이 같은 학생의 학번을 구하라.
SQL> select 학번
2 from 학생
3 where 소속 =
4 (select 소속
5 from 학생
6 where 학생.학번='88001') and 학생.학번 <>'88001'; //학번이 88001인 학생을 제외한다.
학번
-----
87002
87012
질의 15.번호가 102인 교수로부터 평균 성적이상을 받은 학생의 학번을 구하라
// 문제분석 : 번호가 102인 교수가 준 성적이 성적칼럼에서 평균이상인....(평균성적의 개념이 애매함)
SQL> select 학번
2 from 수강
3 where 번호='102' and 성적>=(select avg(성적) from 수강 );
학번
-----
88033
질의 16.설강번호 101을 수강하는 학생의 이름을 구하라 EXISTS를 사용하여 표현
SQL> select 이름
2 from 학생
3 where exists //다음의 조건을 만족하는것이 있다면.
4 ( select *
5 from 수강
6 where 번호='101' and 학생.학번=학번);
이름
--------------------
홍길동
이일삼
질의 17. 설강번호 101을 수강하지 않는 학생의 이름을 구하라.
SQL> select 이름
2 from 학생
3 where not exists //다음의 조건을 만족하는것이 없다면.
4 ( select 번호
5 from 수강
6 where 번호='101' and 학생.학번=학번);
이름
--------------------
김이사
이육사
김일구
정소일
질의 18. 모든 과목을 수강하는 학생의 이름을 구하라
SQL> select 이름
2 from 학생
3 where 학번 in //in을 안쓰면 에러
4 ( select 학번
5 from 수강
6 group by 학번
7 having count(번호)=
8 (select count(과목명)
9 from 설강)
10 );
이름
--------------------
홍길동
질의 19. 소속이 CD인 학생이거나 수강번호 102를 수강하는 학생의 학번을 구하라
SQL> select 학번
2 from 수강
3 where 번호 in //in을 안쓰고 "번호 = "을 하면 에러남 ->번호가 존재하는 학번을 출력!
4 (select 번호 //조건1
5 from 설강 // 설강 테이블에서
6 where 소속='CD') // 소속이 CD인 번호를 return(?)
7 or 번호='102' //조건2 또는 수강테이블안에 번호가 102인
8 group by 학번; //중복을 방지
학번
-----
85014
87002
87012
88001
88033
질의 20. 한 과목 이상을 수강한 학생의 학번을 구하라
SQL> select 학번 //수강과목에 따라 학번이 하나씩 대입되어있으므로 학번이 한번이상나오면
2 from 수강 // 한과목 이상 수강
3 group by 학번
4 having count(학번)>0 //그룹을 갖게하기위한 조건
학번
-----
85014
87002
87012
88001
88033
질의 21. 수강신청을 한 학생의 학번과 번호를 구하라
SQL> select 학번,번호 from 수강;
학번 번
----- ---
88001 101
88001 102
88001 103
88001 104
88001 105
87002 101
87002 104
87012 102
87012 103
88033 102
85014 104
학번 번
----- ---
85014 105
12 rows selected.
질의22. 학생들의 이름과 신장으로부터 표준체중을 구하라 (표준체중은 (신장-110)*1.1 로 구한다고 가정한다)
SQL> select 이름,((신장-110)*1.1) "표준체중"
2 from 학생;
이름 표준체중
-------------------- ----------
홍길동 66
이일삼 63.8
김이사 77
이육사 71.5
김일구 80.3
정소일 60.5
질의23. 설강 테이블의 모든 사항을 구하라.
SQL> select * from 설강;
번호 교수명 과목명 소속
---- -------------------- ------ ----------
101 김일삼 SE CD
102 권오영 DB CD
103 김소월 DG MD
104 이 상 FP CD
105 이석영 CS ED
질의24. 신장이 170 이상이고 180이하인 학생을 구하라.
SQL> select 이름,신장
2 from 학생
3 where 신장>=170 and 신장<=180;
이름 신장
-------------------- ----------
홍길동 170
김이사 180
이육사 175
질의25. 학번이 88001인 학생의 수강한 수강번호와 성적을 구하라
SQL> select 번호,성적
2 from 수강
3 where 학번=88001;
번호 성적
---- ----------
101 95
102 80
103 60
104 80
105 85
질의26. 소속이 CD이며,SE과목을 설강한 교수명을 구하라.
SQL> select 교수명
2 from 설강
3 where 소속='CD' and 과목명='SE';
교수명
--------------------
김일삼
질의 27. 교수번호가 102인 교수가 강의하는 과목을 수강한 학생을 성적순으로 정렬하여 학번과 성적을 구하라.
SQL> select 학번, 성적
2 from 수강
3 where 번호='102'
4 order by 성적;
학번 성적
----- ----------
87012 70
88001 80
88033 85
질의 28. 신장이 170 이상인 학생을 학년을 기준하여 오름차순으로 학번을 구하라
SQL> select 학번
2 from 학생
3 where 신장>=170
4 order by 학년 asc
--
88001
87012
88033
85014
질의 29. 학생들을 피트(Feet)로 환산된 신장과 학번에 따라 정렬하여 학번과 환산된 신장을 구하라
SQL> select 학번, 신장*0.0328 "환산된 신장" 2 from 학생
3 order by 학번;
학번 환산된 신장
----- -----------
85014 6.0024
86032 5.412
87002 5.5104
87012 5.904
88001 5.576
88033 5.74
질의 30. 학기중 설강된 과목을 강의하는 설강 고수의 번호와 이름 , 설강과목 및 소속과 그 과목을 수강한
학생의 학번,번호,성적을 구하라
SQL> select 설강.번호, 설강.교수명, 설강.과목명, 설강.소속, 수강.학번, 수강.번호, 수강.성적
2 from 설강,수강
3 where 설강.번호=수강.번호;
번호 교수명 과목명 소속 학번 번호 성적
---- -------------------- ------ ---------- ----- ---- ----------
101 김일삼 SE CD 88001 101 95
101 김일삼 SE CD 87002 101 80
102 권오영 DB CD 88001 102 80
102 권오영 DB CD 88033 102 85
102 권오영 DB CD 87012 102 70
103 김소월 DG MD 88001 103 60
103 김소월 DG MD 87012 103 80
104 이 상 FP CD 88001 104 80
104 이 상 FP CD 87002 104 95
104 이 상 FP CD 85014 104 90
105 이석영 CS ED 88001 105 85
번호 교수명 과목명 소속 학번 번호 성적
---- -------------------- ------ ---------- ----- ---- ----------
105 이석영 CS ED 85014 105 75
12 rows selected.
<31번> 설강한 교수의 소속과 학생의 소속이 같은 과인 경우의 학생과 교수의 모든 항을 구하라
SQL> select 학번,이름,학년,신장,학생.소속,교수명,과목명
2 from 학생,설강
3 where 학생.소속=설강.소속;
학번 이름 학년 신장 소속 교수명 과목
----- ---------- ---- --------- ---------- ---------- ----
88001 홍길동 2 170 CD 김일삼 SE
87002 이일삼 4 168 CD 김일삼 SE
87012 김이사 2 180 CD 김일삼 SE
88001 홍길동 2 170 CD 권오영 DB
87002 이일삼 4 168 CD 권오영 DB
87012 김이사 2 180 CD 권오영 DB
88001 홍길동 2 170 CD 이 상 FP
87002 이일삼 4 168 CD 이 상 FP
87012 김이사 2 180 CD 이 상 FP
85014 김일구 4 183 ED 이석영 CS
86032 정소일 3 165 ED 이석영 CS
11 개의 행이 선택되었습니다.
<32번> CS과목을 수강한 학생의 학번을 구하라.
SQL> select 학번
2 from 수강,설강
3 where 수강.번호=설강.번호 and 과목명='CS';
학번
-----
88001
85014
<33번> 학생의 신장이 170이상인 학생이 수강한 과목의 교수번호와 학생의 학번을 구하라
SQL> select 수강.번호,학생.학번
2 from 수강,학생
3 where 학생.학번=수강.학번 and 신장 >=170;
번호 학번
---- -----
101 88001
102 88001
103 88001
104 88001
105 88001
102 87012
103 87012
102 88033
104 85014
105 85014
10 개의 행이 선택되었습니다.
<34번> 2학년이 아닌 학생의 학번과 이름을 구하라
SQL> select 학번,이름
2 from 학생
3 where 학년<>'2'; // [!=]과 같다.
학번 이름
----- --------------------
87002 이일삼
88033 이육사
85014 김일구
86032 정소일
<35번> FP과목이나 DB과목을 설강한 교수의 번호와 교수명 그리고 과목명을 구하라
SQL> select 번호,교수명,과목명
2 from 설강
3 where 과목명='FP' OR 과목명='DB';
번호 교수명 과목명
---- -------------------- ------
102 권오영 DB
104 이 상 FP
<36번> 3학년이 아니고 소속이 CD가 아닌 학생의 소속과 같은 설강교수에 대한 학생과 교수의 모든 내용을 구하라
SQL> select 학생.*,설강.*
2 from 학생,설강
3 where 학생.소속=설강.소속 and 학생.학년 <>'3' and 학생.소속 <>'CD';
학번 이름 학년 신장 소속 번호 교수명 과목 소속
----- ---------- ---- --------- ---------- ---- ---------- ---- ----------
85014 김일구 4 183 ED 105 이석영 CS ED
<37번> 설강한 과목을 한 학생이라도 수강하는 경우에 설강한 교수가 속한 소속의 중복을 제거하여 구하라.
SQL> select *
2 from 설강,수강
3 where 설강.번호=수강.번호;
번호 교수명 과목명 소속 학번 번호 성적
---- -------------------- ------ ---------- ----- ---- ----------
101 김일삼 SE CD 88001 101 95
101 김일삼 SE CD 87002 101 80
102 권오영 DB CD 88001 102 80
102 권오영 DB CD 88033 102 85
102 권오영 DB CD 87012 102 70
103 김소월 DG MD 88001 103 60
103 김소월 DG MD 87012 103 80
104 이 상 FP CD 88001 104 80
104 이 상 FP CD 87002 104 95
104 이 상 FP CD 85014 104 90
105 이석영 CS ED 88001 105 85
105 이석영 CS ED 85014 105 75
12 rows selected.
<38번> 학기중 설강된 교수와 소속이 같은 학생의 학번과 설강한 교수의 번호를 구하라
SQL> select 학생.학번,설강.번호
2 from 학생,설강
3 where 학생.소속=설강.소속;
학번 번호
----- ----
88001 101
87002 101
87012 101
88001 102
87002 102
87012 102
88001 104
87002 104
87012 104
85014 105
86032 105
<39번> 김일삼 교수의 과목을 수강한 학생의 학번과 이름,성적을 구하라
SQL> select 학생.학번,학생.이름,수강.성적
2 from 학생,설강,수강
3 where 학생.학번=수강.학번 and 수강.번호=설강.번호 and 설강.교수명='김일삼';
학번 이름 성적
----- -------------------- ----------
87002 이일삼 80
88001 홍길동 95
<40번> 같은 소속학과에 있는 학생들의 쌍을 구하라
SQL> select a.이름, b.이름
2 from 학생 a, 학생 b
3 where a.소속=b.소속;
이름 이름
------------ ------------
홍길동 홍길동
이일삼 홍길동
김이사 홍길동
홍길동 이일삼
이일삼 이일삼
김이사 이일삼
홍길동 김이사
이일삼 김이사
김이사 김이사
김일구 김일구
정소일 김일구
김일구 정소일
정소일 정소일
이육사 이육사
14 개의 행이 선택되었습니다.
질의 41. "CD" 학과 소속의 학년의 개수는 얼만인가."
SQL> select count(학년)
2 from 학생
3 where 소속= 'CD';
COUNT(학년)
-----------
3
질의 42. "학기 중 설강된 과목수는 얼마인가?"
SQL> select count(과목명) from 설강;
COUNT(과목명)
-------------
5
질의 43. "학기 중 수강 신청을 한 학생수를 구하라."
SQL> select count(distinct(학번)) //distinct를 안쓰면 값이 틀려진다.
2 from 수강;
COUNT(DISTINCT(학번))
---------------------
5
질의 44. "교수번호 102인 교수가 설강한 과목을 수강하는 학생의 수를 계산하라."
SQL> select count(학번)
2 from 수강
3 where 번호=102;
COUNT(학번)
-----------
3
질의 45. "신장이 최소인 학생의 신장은 얼마인가?"
SQL> select min(신장)
2 from 학생;
MIN(신장)
----------
165
질의 46. "수강 신청을 한 학생중 최고 성적은 얼마인가?"
SQL> select max(성적)
2 from 수강;
MAX(성적)
----------
95
질의 47. "신장이 170이상인 학생들의 평균신장은 얼마인가?"
SQL> select avg(신장)
2 from 학생
3 where 신장>=170;
AVG(신장)
----------
177
질의 48. "번호 102인 교수의 과목을 수강하는 학생들의 평균 성적을 구하라."
SQL> select avg(성적)
2 from 수강
3 where 번호='102';
AVG(성적)
----------
78.3333333
질의 49. "수강 신청을 한 학생들의 평균성적이 80이상인 과목의 교수번호를 구하라."
SQL> select 번호
2 from 수강
3 group by 번호
4 having avg(성적)>=80;
번호
----
101
104
105
질의 50. "교수번호 103인 교수가 설강한 과목을 수강한 학생들의 평균성적을 구하라."
SQL> select avg(성적)
2 from 수강
3 where 번호='103';
AVG(성적)
----------
70
51. 학생들을 학년별로 분류하여 각각의 신장의 평균치를 구하라
SQL> select 학년, avg(신장)
2 from 학생
3 group by 학년
4 order by 학년;
학년 AVG(신장)
---- ----------
2 175
3 170
4 175.5
52."수강 과목에 대한 성적의 평균값을 구하라"
SQL> select 번호,avg(성적)
2 from 수강
3 group by 번호
4 order by 번호;
번호 AVG(성적)
---- ----------
101 87.5
102 78.3333333
103 70
104 88.3333333
105 80
53."3 과목 이상을 신청한 학생의 학번을 구하라"
SQL> select 학번,count(*)
2 from 수강
3 group by 학번
4 having count(*) >=3;
학번 COUNT(*)
----- ----------
88001 5
54. "수강을 한 학생 중, 88로 시작되는 학번을 가진 학생을 구하라.
SQL> select distinct 학번
2 from 수강
3 where 학번 like '88%';
학번
-----
88001
88033
55. " 학기 중 설강한 교수 중 이름이 김으로 시작되는 교수와 과목을 구하라.
SQL> select 교수명,과목명
2 from 설강
3 where 교수명 like '김%';
교수명 과목명
-------------------- ------
김일삼 SE
김소월 DG
56. "수강한 학생 중 한 명 이상의 학생에게 90점 이상을 준 교수이름과 과목을 구하라."
SQL> select 설강.교수명,설강.과목명
2 from 설강,수강
3 where 설강.번호 in(select 번호 from 수강 where 성적>=90)
4 group by 설강.교수명,설강.과목명;
교수명 과
-------------------- ---
김일삼 SE
이 상 EP
57. "소속이 CD인 학생이 수강하는 과목을 담당하는 교수이름과 과목이름을 구하라."
SQL> select 설강.교수명,설강.과목명
2 from 설강,수강,학생
3 where 설강.번호 in
4 ( select 수강.번호 from 수강,학생
5 where 수강.학번 in
6 ( select 학생.학번 from 학생 where 학생.소속='CD')
7 )
8 group by 설강.교수명,설강.과목명 ;
교수명 과
-------------------- ---
권오영 DB
김소월 DG
김일삼 SE
이 상 EP
이석영 CS
-----> 2번째 시도( 간단히 만들어보자)
SQL> select 교수명,과목명
2 from 설강
3 where 번호 in
4 ( select 번호 from 학생
5 where 학번 in
6 ( select 학번 from 학생
7 where 소속='CD')
8 )
9 group by 교수명,과목명;
교수명 과
-------------------- ---
권오영 DB
김소월 DG
김일삼 SE
이 상 EP
이석영 CS
58. "평균 점수 이상의 성적을 받은 학생의 이름과 학년, 교수번호를 구하라."
SQL> select 학생.이름,학생.학년,수강.번호
2 from 학생,수강
3 where 수강.학번=학생.학번
4 and 수강.성적>=(select avg(성적) from 수강);
이름 학년 번호
-------------------- ---- ----
김일구 4 104
이일삼 4 104
홍길동 2 101
홍길동 2 105
이육사 3 102
59. " 한 과목도 수강하지 않는 학생의 이름,소속을 구하라.
SQL> select 학번,이름,소속
2 from 학생
3 where not exists
4 (select 학번 from 수강 where 학생.학번=수강.학번);
학번 이름 소속
----- -------------------- ----------
86032 정소일 ED
60. "소속이 CD 이거나 성적을 90 이상을 준 교수의 번호를 구하여라.
SQL> select 번호
2 from 수강
3 where 번호 in
4 (select 번호 from 설강 where 소속='CD')
5 or 성적>=90
6 group by 번호;
번호
----
101
102
104
'IT TIP > Oracle' 카테고리의 다른 글
| Oracle dict 활용 & Compile ,Drop 쿼리 (0) | 2019.06.24 |
|---|---|
| 오라클 테스트를 위한 자료테이블 DESC (0) | 2017.04.13 |
| sqlplus - 기초실습3 (0) | 2017.04.13 |
| sqlplus - 기초실습2 (0) | 2017.04.13 |
| sqlplus - 기초실습1 (0) | 2017.04.13 |
| 사용자정의 SPLIT함수. (0) | 2017.04.06 |
| REGEXP_SUBSTR함수로.SPLIT하기 (0) | 2017.04.06 |
| 계층구조 START WITH...CONNECT BY (0) | 2017.04.06 |
| 프로시저내 다이나믹 SQL 예제1 (0) | 2017.04.05 |
| 그룹내 순위결정하는 함수 - ROW_NUMBER() ,RANK() ,DENSE_RANK() (0) | 2017.04.05 |