DB수업

DataBase 수업 20211018

작지 2021. 10. 18. 18:02

Subquery

  • 하나의 SQL 질의문 속에 다른 SQL 질의문이 포함되어 있는 형태
  • EX) 'SCOTT' 이라는 사람의 월급보다 월급이 많은 사람의 이름 뽑기
    • SAL = 3000 (SELECT * FROM (emp) where ename='SCOTT'
    • SUBQUERY를 사용 = SELECT * FROM (칼럼) where 월급 > (select 월급 from (칼럼) where 이름 = 'SCOTT');
    •  
    • Single-Row Query
      • Subquery결과의 Row가 한 개일 경우
      • single-row operator를 사용해야함. : = , > , >= , < , <= , <>
    • Multy-Row Query
      • Subquery의 결과의 Row가 둘 이상일 떄
      • multy-Row에 대한 연산을 사용해야 함 . single-row operator는 사용하면 안됨.
      • ex) ANY , IN , ALL, EXIST 등등
      • SELECT ename,sal,deptno from emp where ename IN ( SELECT MIN(ename) from emp where group by deptno);
      • 결과값
    • Top-K Query
      • ROWNUM: 질의의 결과에 가상으로 부여되는 Oracle의 pseudo column
      • 질의가 끝난 후에 rownum 값이 부여되므로 from 절에 서브쿼리 조건을 넣으면 된다.
      • ex)

DDL

  • Create Table문 이용
    • 테이블이름, 컬럼 이름, 데이터 타입 등 정의
    •  
    • Create Table book( bookno NUMBER (5), title VARCHAR2 (50), author VARCHAR2 (10), pubdate DATE )
    • SubQuery를 이용해서 테이블 생성 가능
      • SubQuery의 결과와 동일한 테이블이 생성
      • 질의 결과 레코드들이 포함
      • NOT NULL 제약 조건 만 상속됨
      • EX)
      • Create Table empSALES
AS (select * from emp where deptno = 30)
    • Naming Rules
      • 테이블 , 칼럼  등의 이름 명명 규칙
        • 문자로 시작
        • 30자 이내
        • A-Z , a-z, _, $, #
        • 같은 유저가 소유한 다른 Object의 이름과 겹치지 않아야 함
        • 오라클 예약어는 사용할 수 없음
    • Data type
    • ALTER TABLE
      • 칼럼 추가
        • ALTER TABLE book ADD (pubs VARCHAR2(50));
      • 칼럼 수정
        • ALTER TABLE book MODIFY (title VARCHAR2(100));
      • 칼럼 삭제
        • ALTER TABLE book DROP author;
    • DELETE
      • 조건을 만족하는 레코드 삭제
      • - 이름이 SCOTT인 사원 삭제법
        • DELETE FROM EMP where ename= 'scott';
      • 조건이 없으면 모든 레코드 삭제
        • DELETE FROM EMP;
      • SUBQUERY를 이용한 DELETE
        • SALES 부서의 직원 삭제
          • DELETE FROM EMP WHERE deptno = (delete deptno FROM dept where dname='SALES');

 

VIEW

  • 보안강화
  • 데이터 복잡성을 단순화
  • 실제 테이블의 정의와 응용프로그램 분리
  • 복잡한 질의 숨김
  • CREATE[OR REPLACE] [FORCE | NO FORCE] VIEW view_name [(alias[,alias]...)] AS subquery[WITH READ ONLY] [ WITH CHECK OPTION [CONSTRAINT constraint]];;

 

 

 

 

 

 

 

 

 

 

예제 문제 2-5 :

답: select team_id 팀아이디, player_name 선수명 , position 위치, height 키 , weight 몸무게 
from player_t
where position like 'MF%' and team_id like 'K02%' or team_id like 'K07%' and position like 'MF%'

 

예제 문제 2-6

 

답:select player_name 이름, position 위치, back_no 백넘버
from player_t
where team_id like 'K02%'

 

예제 2-7

키가 170센티미터 이상인 선수들을 출력하시오.

답:select * from player_t where height >= 170

 

예제 2-8

'K02' 또는 'K07' 팀에 소속되어 있는 선수들을 출력하시오 ( IN 문을 사용할 것)

답:select *
from player_t
where team_id in ('KO2','K07')

 

예제 2-9 포지션이 'MF' 인 선수들을 출력하세요

답:

SELECT *
from player_t
where position in 'MF'

 

예제 2-10

"고"씨 성을 가진 선수들의 정보를 출력하시오

답:

SELECT *
from player_t
where player_name like '고%'

 

예제 2-11

스탭들의 정보를 출력하세요 스텝들은 포지션 코드 T

답:

SELECT *
from player_t
where position like 'T%'

 

예제 2-12

키가 170 이상 180 이하 선수들을 출력하시오 BETWEEN ~ AND 문 사용

답:

SELECT *
from player_t
where height between 170 and 180

 

예제 2-13

키 정보가 입력되지 않은 선수들을 출력하세요 null 을 사용

답:

select *
from player_t
where height is NULL

 

예제 2-14

소속이 삼성블루윙즈팀 키가 170이상인 조건을 가진 선수들 정보 조회

답:

SELECT *
from player_t
where height >= 170 and team_id in 'K02'

 

예제 2-15

삼성블루윙즈 또는 전남드레곤즈 선수들중 포지션이 미드필더이고 키가 170이상 180미만 조건을 가진 선수들

답:

SELECT *
from player_t
where height between 170 and 180 and team_id in ('K02','K07')

 

예제 2-16

K-리그 선수들의 포지션별 평균 키

답:

select position , avg(height)
from player_t
group by position

 

예제 2-17

수원 삼성블루윙즈팀의 선수들의 포지션별 인원은 어떻게 되는가

답:

select position , count(player_name)
from player_t
group by position

 

예제 2-18

k-리그의 포지션별 키가 180센티미터 이상되는 선수중에서 가장 큰 키를 구하시오

답:

select position, MAX(height)
from player_t
where height >= 180
group by position

 

예제 2-19

포지션별 평균키를 구한다. 단 평균키가 180 센티미터 이상의 값만 출력하시오

답:

select position, avg(height)
from player_t
group by position
having avg(height) >= 180

 

예제 2-20

k-리그의 선수들 중에서 팀별 포지션별 평균키를 구하시오

답:

select position,team_id, avg(height)
from player_t
group by position,team_id
having avg(height) > 0

 

예제 2-21

선수들의 이름 포지션 백넘버를 출력하는데 선수들의 백넘버 순으로 출력하시오.

답:

select player_name 이름 , position 포지션, back_no 백넘버
from player_t
order by back_no

 

예제 2-22

select player_name 이름 , position 포지션, back_no 백넘버
from player_t
where not back_no is null
order by back_no desc

 

예제 2-23 선수 이름 다음 "선수" 가 붙도록 출력하시오

답:

select player_name ||' 선수' 선수명, position 포지션, back_no 백넘버
from player_t

 

예제 2-24 소속팀이 삼성블루위즈인 수원을 연고지로 하는 선수들중 포지션이 미드필드이고 키가 175 이상 185사이에 있는 선수들의 이름, 포지션, 백넘버를 출력하시오 그런데 선수들의 키가 큰 선수부터 출력하고 키가 같다면 백넘버가 작은 선수부터 출력하게 하시오.

 

답:


select player_name, position, back_no
from player_t
where height >175 and height < 185 and position like 'MF%' and team_id ='K02'
order by height desc, back_no

 

예제 2-25 포지션별로 평균키 값을 구한다.

단 출력은 평균키 값이 180이상만 출력하고 출력시 평균키 값이 높은 순으로 출력한다.

 

답:

select position, avg(height)
from player_t
group by position
having avg(height) > 180
order by avg(height) desc

 

예제 3-1

player_t의 team_id 와 team_t 테이블의 team_id 을 이용하여 다음과 같은 결과가 나오도록 SQL문 작성

select p.player_name, p.back_no, t.region_name, t.team_name
from player_t p, team_t t
where t.team_id = 'K05'
order by back_no

 

예제 3-2

team_t 테이블의 stadium_id 와 stadium_t 테이블의 stadium_id를 이용하여

다음과 같은 결과가 나오도록 SQL문을 작성하시오

답:

select t.region_name, t.team_name, s.stadium_name, s.seat_count
from stadium_t s, team_t t
where t.stadium_id = s.stadium_id

 

예제 3-3

포지션이 GK인 선수들의 선수명, 백넘버, 연고지명, 팀명을 출력 출력시 백넘버가 작은 값부터 출력

답:

SELECT p.player_name, p.back_no, t.region_name, t.team_name
from player_t p , team_t t
where p.position = 'GK'
and t.team_id = p.team_id
order by p.back_no

 

예제 3-4

사원 테이블에서 각 사원이 받고 있는 급여가 어느 등급에 속하는 급여등급인지 출력

답:

select e.ename, e.sal , s.grade
from salgrade s , emp e
where e.sal between s.losal and s.hisal

 

예제 3-5

k-리그 소속팀에서 사용하는 전용구장의 정보를 팀별로 알고 싶다.

하지만 특정 팀에 소속되지 않는 구장에 대한 정보도 출력되어야 한다.

답:

 

select t.region_name, t.team_name, s.stadium_name, s.seat_count
from team_t t, stadium_t s
where t.stadium_id (+) = s.stadium_id
order by s.stadium_id

 

예제 3-6

사원에 대한 정보와 그 사원을 담당하고 있는 관리자의 정보를 출력

답:

select e.empno, e.ename, e.job , e.mgr
from emp e

 

예제 3-7

선수들별로 홈그라운드 경기장이 어디인지 출력하시오

답:

select p.player_name , p.position, t.region_name, s.stadium_name
from player_t p, stadium_t s, team_t t
where s.hometeam_id = t.team_id 
and t.team_id = p.team_id

 

 

예제 3-8

김남일 선수가 소속된 팀의 선수들에 대한 정보를 출력하시오

답:

select player_name, position, back_no
from player_t
where team_id = ( select team_id from player_t where player_name='김남일')

 

예제 3-9

선수들 중에 키가 평균키보다 작은 선수들의 정보를 출력하시오

답:

select player_name, position, back_no, height
from player_t
where height <= ( select avg(height) from player_t )
order by height

 

예제 3-10

소속팀 별 키가 가장 작은 사람들의 정보를 출력하시오

답:

select team_id, player_name, position, back_no, height
from player_t
where (team_id, height) in (select team_id,MIN(height) from player_t group by team_id)
order by team_id

 

예제 3-11

선수가 속해 있는 팀의 평균키보다 작은 선수들의 정보를 출력하시오

답:

select t.team_name, p.player_name, p.position, p.back_no, p.height
from player_t p, team_t t
where p.height < (select avg(s.height) from player_t s where s.team_id = p.team_id )
and t.team_id = p.team_id
order by player_name 

 

예제 3-12

삼성블루윙즈의 평균키보다 작은 팀의 이름과 평균키를 출력하시오

답:

select p.team_id, t.team_name, avg(p.height)
from player_t p, team_t t
where p.team_id = t.team_id
group by p.team_id, t.team_name
having avg(p.height) < (select avg(height) from player_t where team_id = 'K02')

 

예제 3-13

포지션이 미드필더인 선수들의 소속팀명 및 선수의 정보를 출력하는데 FROM 절에 SUBQUERY를 사용해서 SQL문을 작성하시오.

답:


SELECT t.team_name, p.player_name, p.back_no
FROM (select team_id, player_name, back_no from player_t where position = 'MF')p, team_t t
where p.team_id = t.team_id
order by player_name

 

예제 3-14

선수들 중 키가 가장 작은 사람 10명 출력

답:

select player_name,rownum, position, back_no, height
from (select * from player_t where height is not null order by height desc )
where rownum < 11

k-league.sql
0.08MB
scott.sql
0.00MB