SQL 데이터베이스 명령어 연습 예제

참고하시고 공부하세요 ㅎ

하위그룹 커리 ->

create database 상품거래DB

--상품테이블에서 상품분류별로 판매가격의 합계, 평균, 판매건수를
 --검색하시오.
 select sum(판매가격) 판매가격합계,
        avg(판매가격) 판매가격평균,
        count(*) 판매건수
 from 상품
 
 
 select 상품분류, sum(판매가격) 판매가격합계,
        avg(판매가격) 판매가격평균,
        count(*) 판매건수
 from 상품
 group by 상품분류
 
 --상품테이블에서 상품분류별 판매건수가 3건 이상인 상품에 대하여
 --판매가격의 합계, 평균, 판매건수를 검색하시오.

 select 상품분류, sum(판매가격) 판매가격합계,
        avg(판매가격) 판매가격평균,
        count(*) 판매건수
 from 상품
 group by 상품분류
 having COUNT(*) >= 3
 
 --주문테이블에서 거래처코드별 주문건수와 상품수량 총합을 검색하시오.
 select 거래처코드, COUNT(*) 주문건수, SUM(상품수량) 상품수량총합
 from  주문
 group by 거래처코드
 
 --상품테이블에서 재고수량이 100개 이상인 상품에 대하여 상품분류별로
  --재고수량평균 및 평균판매가격을 검색하시오.
 
  select 상품분류, AVG(재고수량) 재고수량평균,
         AVG(판매가격) 평균판매가격
  from  상품
  where 재고수량 >= 100
  group by 상품분류 
 
 --상품테이블에서 상품분류별로 검색하되, 판매가격평균보다 많은
  --상품들에 대하여 판매가격의 총합과 판매건수를 검색하시오.
  select 상품분류, sum(판매가격) 판매가격총합,
         COUNT(*) 판매건수
  from  상품
  where  판매가격 >= (select AVG(판매가격)
                      from 상품)
  group by 상품분류
 
  /* 하위쿼리 작업
  1. 일반적으로 확정된 값이 아닌 통계적 작업을 보조하기 위해 사용
     => 집계함수 sum(), avg(), max(), Min(), count() 와 함께 사용
  2. 서로 다른 테이블의 내용을 연결하여 적용하기 위해 사용
     => join 쿼리로 대체 가능
  3. 단일값 지정 (where절에 =를 부여해서 값 적용
                  => 검색된 값이 하나인 경우)
  4. 목록값 지정 (where절에 in을 부여해서 값 적용
                  => 검색된 값이 여러개의 값인 경우)
  5. 문법 : select
            from
            where 검색속성명 연산자 (select 검색속성명
                                     from
                                     where   ....   )
           
            *연산자 : in,=,>=,...의 관계연산자, in 연산자 의미
 
 * 그룹질의 작업
 1. 반드시 통계작업을 하기 위해 사용
 2. 전체통계가 아닌 부분적 통계를 하기 위해 사용
 3. 어떤 특성을 지닌 항목별로 대상을 묶어서 처리
 4. 묶인 대상에 대한 별도의 조건을 부여하기 위하여 where절 외에
    having절을 따로 가지고 사용 가능
 5. having 절에는 일반적으로 집계함수에 대한 조건이 부여
 6. select 절과 having절에 사용되는 단일속성명은 반드시 group by절에
    표시된 것만 사용할 수 있다.
   
 7. 문법 : select 그룹지정할속성명, sum(),avg(),max(),min(),count()
           from
          [where ...   ]
           group by  그룹지정할속성명
          [having ...       ]
 */
 
 -- 상품테이블에서 가장 많은 재고수량을 갖는 상품에 대한 모든 내역을
   --검색하시오.
   select * from 상품
   where 재고수량 = (select MAX(재고수량) from 상품)
  
 -- 상품테이블에서 상품분류가 의류 또는 신발인 상품의 갯수는 몇개인지
   --검색하시오.
   select COUNT(*) 상품갯수
   from 상품
   where 상품분류 in ('의류','신발')
  
   select 상품분류, COUNT(*) 상품갯수
   from 상품
   where 상품분류 in ('의류','신발')  
   group by 상품분류   
  
 --주문테이블에서 상품수량이 50개 이상 주문된 상품의 내역을 검색하시오.
 select *
 from 상품
 where 상품코드 in  (select 상품코드
                    from 주문
                    where 상품수량 >=50)
 
 --주문테이블에서 상품수량이 50개 이상 주문된 상품의 주문건수를 검색
 select COUNT(*) 주문건수
 from 주문
 where 상품수량 >= 50
 
 --상품테이블에서 상품분류별로 그룹지은 후, 재고수량 총합이
   --300이상인 경우에 대하여 재고수량합계, 판매가격합계를 검색하시오.
 select 상품분류, SUM(재고수량) 재고수량합계,
        SUM(판매가격) 판매가격합계
 from 상품
 GROUP by 상품분류
 having SUM(재고수량)  >=300 
   
  

제약조건 쿼리 ->

create database sampleDB
go
use sampleDB
go
create table 샘플
( 아이디 char(10),
  품명코드 char(3),
  수량 int
)
insert into 샘플
values('fly56', 'm2', 1)
insert into 샘플
values('jung_min', 'm3', 3)
insert into 샘플
values('soo123', 'm1', 1)
insert into 샘플
values('beauty7', 'm7', 1)
insert into 샘플
values('king33', 'm1', 5)
insert into 샘플
values('bear77', 'm4', 4)
insert into 샘플
values('a1004', 'm3', 1)

-- 샘플테이블에 등록날짜 속성을 날짜유형으로
 --추가하시오.
 alter table 샘플
  add 등록날짜 datetime
-- 샘플테이블에 아이디 속성을 기본키로 추가
 --하는 "기본키제약조건"을 생성하시오.
 alter table 테이블명
  add constraint 생성할제약조건이름
    primary key (기본키속성명)

 alter table 샘플
  add constraint 기본키제약조건
    primary key(아이디) --현재상태로 실행 안됨
--[해결] 기본키로 사용할 속성이 반드시
 --not null 조건이 되어야 한다 (전제조건)
 alter table 샘플
  alter column 아이디 char(10) not null

 alter table 샘플
  add constraint 기본키제약조건
    primary key(아이디) --실행가능
   
--샘플테이블의 수량속성 값으로 1부터 100까지의
 -- 값만 입력 또는 변경 가능하도록 "수량제한"
  --제약조건을 생성하시오. (도메인제약조건)
 alter table 테이블명
  add constraint 생성할제약조건이름
    check (속성명 연산자 적용값)
   
 alter table 샘플
  add constraint 수량제한
    check (수량 between 1 and 100)       
--다음과 같이 입력해보자
insert into 샘플(아이디, 품명코드, 수량)
values('b1004', 'm3', 50)
  --수량제한 제약조건에 맞으므로 입력허용
 
--품명코드가 'm1','m2','m3','m4'로만 입력
 --되거나 변경될 수 있도록 값을 제한하는
  --"품명코드제한" 이라는 도메인 제약조건을
   --생성하시오.
 alter table 샘플
  add constraint 품명코드제한
    check (품명코드 in ('m1','m2','m3','m4'))  
--처리불가, 기존 샘플테이블에 'm7'데이터가
 --존재함으로써 제약조건 위반, 생성 안됨
 --[해결] with nocheck 옵션을 설정함.
alter table 샘플 with nocheck
  add constraint 품명코드제한
    check (품명코드 in ('m1','m2','m3','m4'))   
 
alter table 샘플
  drop constraint 품명코드제한
 
-- 데이터 입력 또는 변경시에 수량속성이 입력
 --되지 않았다면 기본값으로 1이 적용되도록
  --"수량기본값"   제약조건을 생성하시오.
 alter table 테이블명
  add constraint 생성할제약조건이름
    default 기본값 for 속성명
   
 alter table 샘플
  add constraint 수량기본값
    default 1 for 수량      
-- 다음과 같이 데이터를 입력해보자
insert into 샘플(아이디,품명코드)
values('d1004','m1')  
-- 수량제한 제약조건을 삭제하시오.
alter table 샘플
 drop constraint 수량제한

    
select * from 샘플

sp_help 샘플
sp_helpconstraint 샘플
sp_helptext 샘플뷰

create table 샘플
( 아이디 char(10) not null primary key,
  품명코드 char(3)
     check(품명코드 in ('m1','m2','m3','m4')),
  수량 int default 1
)

select a.*
from 거래처 a, 거래처 b
where a.거래처명 =b.거래처명 and
         a.사업자번호 != b.사업자번호

 

기본 예제 ->

--학과 테이블의 '학과'속성을 기본키로 설정하는
 --[학과기본키] 제약조건을 생성하시오
alter table 학과
 add constraint 학과기본키
  primary key(학과)
--성적 테이블의 '번호'속성을 기본키로 설정하는
 --[성적기본키] 제약조건을 생성하시오.
alter table 성적
 add constraint 성적기본키
  primary key(번호)

--성적테이블의 '학과'속성을 학과테이블의
 --기본키인 '학과'속성과 연결짓는 참조키로
  --선언하는 [학과참조키] 제약조건을 생성하시오.
alter table 성적
 add constraint 학과참조키
  foreign key(학과) references 학과(학과)
 
alter table 제약조건생성테이블
 add constraint 제약조건명
  foreign key(참조키)
           references 연결테이블(기본키)   

--성적테이블의 국어점수를 0점부터 100점 사이의
 --값만 입력이 허용되도록 값을 제한하는
  --[국어점수범위]제약조건을 생성하시오.
alter table 제약조건생성테이블
 add constraint 제약조건명
   check (where절 문법 적용)
  
alter table 성적
 add constraint 국어점수범위
  check (국어 between 0 and 100)
 
--성적테이블의 수학점수를 0점부터 100점 사이의
 --값만 입력이 허용되도록 값을 제한하는
  --[수학점수범위]제약조건을 생성하시오.
alter table 성적
 add constraint 수학점수범위
  check (수학 between 0 and 100) 

--성적테이블의 영어점수를 0점부터 100점 사이의
 --값만 입력이 허용되도록 값을 제한하는
  --[영어점수범위]제약조건을 생성하시오.
alter table 성적
 add constraint 영어점수범위
  check (영어 between 0 and 100)

--학과테이블의 "학과" 속성이 10,20,30,40,50의
 --값만 갖도록 제한하는 "학과코드"제약조건을
  --생성하시오.
alter table 학과
 add constraint 학과코드
  check(학과 in ('10','20','30','40','50' )) 

--성적테이블의 학과 속성을 입력하지 않을 경우
 --기본값으로 10번 학과가 저장되도록
  --[학과기본값] 제약조건을 생성하시오.
alter table 적용할테이블명
 add constraint 제약조건이름
  default 적용할기본값  for 적용할속성명
 
alter table 성적
 add constraint 학과기본값
  default '10'  for 학과 
 
--성적테이블에 나이가 입력되지 않을 경우
 --20살이 기본값으로 적용되도록 [나이기본값]
  --제약조건을 생성하시오.
alter table 성적
 add constraint 나이기본값
  default 20  for 나이 
--학과테이블에 '입교일자' 속성을 datetime유형
 --으로 새롭게 추가하시오.
alter table 학과
 add 입교일자 datetime
--학과테이블의 '입교일자'속성을 입력하지 않을
 --경우 기본값으로 현재시스템의 날짜가 입력
  --되도록 [날짜기본값] 제약조건을 생성하시오.
   --현재시스템날짜는 getdate()로 얻을수 있다.
alter table 학과
 add constraint 날짜기본값
  default  getdate() for 입교일자
        
-- 학과테이블의 구성정보를 검색하시오
sp_help 학과
-- 학과테이블에 존재하는 제약조건정보를 검색하시오
sp_helpconstraint 학과
-- 성적테이블의 구성정보를 검색하시오.  
sp_help 성적
     
     
 /* 지난번에 작업했던 퀴즈 문제 풀이 */

--1. 모든 학생들의 국어 평균 검색
select  AVG(국어) from 성적
--2. 모든 학생들을 대상으로 수학의 최대점수
 --검색
select MAX(수학) from 성적
--3. 모든 학생들을 대상으로 영어의 최정점수
 --검색
select MIN(영어) from 성적
--4. 모든 학생들의 수학, 영어점수의 합을
 --검색하시오.
select SUM(수학+영어) from 성적
/* 참고 => 각 학생별로 수학과 영어의 합 검색
  select 번호, 이름, 수학+영어 from 성적   */
--5. 다음 수행의 결과화면을 작성하시오.
select MAX(이름), MIN(이름),COUNT(학과)
from 성적
 --> sim, kang, 5 (ASCII 값으로 계산되어나온다)
--6. 성적테이블에서 모든 학생들의 중복되지
 --않은 학과 수를 검색하시오.
 select COUNT(distinct 학과)  from 성적
 /*참고: 학과테이블에서 학과수를 검색하시오
 select COUNT(*)  from 학과   */
 
--7.모든 학생들의 국어점수 평균을 검색하시오.
 --단, 표시되는 열의 이름은 '국어평균'으로 표시
 select AVG(국어) as '국어평균'
 from 성적
--8. 10번학과 학생의 국어 평균 검색
 select AVG(국어) from 성적
 where 학과 ='10'
--9. 10번학과이면서 영어점수가 75점 이상인
 --학생들의 국어평균 검색
 select AVG(국어) from 성적
 where 학과='10' and 영어 >=75
--10. 학과별로 국어,수학,영어 점수의 평균검색
 select 학과, AVG(국어), AVG(수학), AVG(영어)
 from  성적
 group by 학과
 
--11. 성적테이블에서 나이가 22살이상인 학생만
 -- 학과별로 국어, 영어 점수의 최대값 검색
 select 학과, MAX(국어), MAX(영어)
 from  성적
 where 나이>= 22
 group by 학과
--12. 평균수학점수가 82점 이상인 학생만
 --학과별로 묶어서 수학, 영어의 최대값 검색
 select 학과, MAX(수학), MAX(영어)
 from  성적
 group by 학과
 having AVG(수학) >= 82
--13. 평균나이가 22살 이상인 학생만 학과별로
 --묶어 국어의 평균 검색
select 학과, AVG(국어)
from   성적
group by 학과
having  AVG(나이) >=22
--14. 수학과목을 제일 잘한 학생의 이름, 학과,
 --수학점수를 검색하시오.
 1) 수학과목을 제일 잘한 점수 검색
    select MAX(수학) from 성적
 2) 제일 잘한 최고점수를 해당 과목에서 비교
    select 이름, 학과, 수학
    from 성적
    where 수학 =(select MAX(수학) from 성적) 
--15. 경영학과 학생들의 이름, 나이, 국어,
 --영어,수학점수를 검색하시오.
 select 이름, 나이, 국어, 영어, 수학
 from 성적
 where 학과=(select 학과
             from 학과
             where 학과명='경영')            
 select 이름, 나이, 국어, 영어, 수학
 from 성적, 학과
 where 성적.학과=학과.학과 and 학과명='경영'
 
 select 이름, 나이, 국어, 영어, 수학
 from  성적 join 학과 on 성적.학과=학과.학과
 where 학과명='경영'

--뷰(view) : 하나이상의 테이블로부터 유도된
 --가상테이블
 
--성적테이블에서 학과, 이름, 나이, 국어,영어,
 --수학점수를 검색하여 성적뷰를 생성하고자한다.
 create view 성적뷰 as
  select  학과, 이름, 나이, 국어,영어,수학
  from  성적

--성적뷰를 통하여 다음과 같은 내용을 입력하자.
insert into 성적뷰
values('10','sim',20,90,83,73)
  --> 성적뷰에 기본키가 없으므로 입력불가
--[해결] 성적뷰를 수정 
 alter view 성적뷰 as
  select 학과,번호,이름,나이,국어,영어,수학
  from  성적
--성적뷰를 통하여 다음과 같은 내용을 입력하자.
insert into 성적뷰
values('10',6,'sim',20,90,83,73)

insert into 성적뷰
values('60',7,'sim',22,90,83,73)
 -->외래키(참조키)제약조건 위배   

insert into 학과(학과,학과명,교수명)
values('60','컴과','김영희')
 -->check 제약조건에 따라 입력 거부
 
--성적뷰를 다음과 같이 수정해보자 
 alter view 성적뷰 as
  select 학과,번호,이름,나이,국어,영어,수학
  from  성적
  where 학과 ='10'
 
--성적뷰를 통하여 다음과 같은 내용을 입력하자.
insert into 성적뷰
values('20',7,'sim',22,90,83,73) 
   --> 입력허용됨
--성적뷰를 다음과 같이 수정해보자 
 alter view 성적뷰 as
  select 학과,번호,이름,나이,국어,영어,수학
  from  성적
  where 학과 ='10' with check option  
--성적뷰를 통하여 다음과 같은 내용을 입력하자.
insert into 성적뷰
values('20',8,'sim',22,90,83,73)  
  --> with check option 때문에 입력 거부
 
--성적과 학과테이블을 결합하여 학과성적뷰를
 --생성하시오.
create view 학과성적뷰 as
 select *
 from 성적 join 학과 on 성적.학과=학과.학과
  --> 성적 및 학과테이블에 모두 학과속성이
   --동일한 이름으로 존재하므로 생성거부
create view 학과성적뷰 as
 select 성적.*,학과명,교수명
 from 성적 join 학과 on 성적.학과=학과.학과   
 select * from 성적뷰
  --> select문을 나열해서 학과가 한번만
   --선언되도록 정리하여 사용하면 생성된다.
 
 --성적뷰를 통하여 국어성적뷰를 생성하시오.
 create view 국어성적뷰 as
  select * from 성적뷰
    -->하나의 테이블로 구성된 뷰는 또다른
     --뷰를 구성할 수 있다.
    
 --학과성적뷰를 통하여 새로운 샘플뷰를 생성하시오.
 create view 샘플뷰 as
  select * from 학과성적뷰   
    
insert into 학과성적뷰(학과,번호,이름,나이)
values('20',10,'sim',22)    

  sp_help 성적
--학과성적뷰를 삭제하시오.
drop view 학과성적뷰
--성적테이블에서 국어점수범위제약조건을 삭제하시오.
alter table 성적
 drop constraint 국어점수범위
   
--국어성적뷰를 만들때 적용된 검색문을 검색하라.
sp_helptext 국어성적뷰