23.2.23 (목)

 [where 조건절]
   -조건을 만족하는 행들에 대해서
   -조건에 만족하는 레코드만 대상으로 조회(select), 수정(update), 삭제(delete)하기 위해서
   
   
   [between A and B] 연산자 - A부터 B까지
   --국어점수 80~89점 사이 행 조회
   
   alter table sungjuk rename column korea to kor;--어제 변경한 칼럼명 다시 kor로 변경
   
   
   select uname, kor
   from sungjuk
   where kor between 80 and 89;
   
   
   
   [in 연산자] - 목록에서 찾기
   --이름이 '무궁화', '봉선화'를 조회
   select uname
   from sungjuk
   where uname in ('무궁화','봉선화');
   
   
   select uname, kor
   from sungjuk
   where kor in (10, 20, 30);
   
   
   
   [LIKE 연산자] - 목록에서 찾기
   - 문자열 데이터에서 비슷한 유형 찾기
   - % 글자 개수와 무관
   - _ 글자 개수가 일치되어야 
   
   select uname
   from sungjuk
   where uname = '대한민국';
   
--문1) '홍'으로 시작하는 이름 조회

select uname
from sungjuk
where uname like '홍%'; --홍으로 시작하는 모든 문자
   


--문2) '화'로 끝나는 이름 조회  

select uname
from sungjuk
where uname like '%화';

--문3) '나'글자가 있는 이름 조회
select uname
from sungjuk
where uname like '%나%';

   
--문4) 두 글자 이름에서 '화'로 끝나는 것 조회
select uname
from sungjuk
where uname like '_화';--글자수만큼 (언더라인+조회글자)


--문5) 세 글자 이름에서 '나' 있는 행 조회
select uname
from sungjuk
where uname like '_나_';

select count(uname)
from sungjuk
where uname like '_나_';


--문6) 제목+내용을 선택하고 검색어가 '파스타'일때
select uname
from sungjuk
where subject like '%pasta%' or content like '%pasta%'
--제목(subject)이나 내용(content)에 파스타가 들어간 칼럼 찾기 


--문7) 국어 점수가 50점 이상인 행에 대해 총점과 평균 구하기
update sungjuk
set tot = (kor + eng + mat), aver = (kor + eng + mat)/3
where kor >= 50;

rollback

select * from sungjuk;
///////////////////////////////////////////////// 
   
   [NULL] - 비어 있는 값
   --총점의 개수 구하기
   select count(tot) from sungjuk;--null은 카운트되지 않음
   
   --총점에 null이 있는 행 개수 구하기
   
   select count(*) from sungjuk  where tot = null;--null값 확인 못함
   
   select count(*) from sungjuk  where tot is null;--null값 확인 가능
   
   --평균에 비어있지 않은 행의 개수 구하기(null이 아닌 값)
   select count(*) from sungjuk where aver is not null;
   
   
   --문8) 비어있는 총점과 평균을 모두 채우기
 update sungjuk 
 set tot = (kor + eng + mat), aver = (kor + eng + mat)/3
   where tot is null or aver is null; --총점 또는 평균이 비어있는 곳에 한정하여
   
   select * from sungjuk;
   
   commit;
   ///////////////////////////////////////////////////////////////////

 

  
[sort 정렬]
- 특정값(keyfield)을 기준으로 순서대로 재배치
  - 오름차순 Ascending Sort   ASC  생략하면 기본값  1->10  A->Z  a->z  ㄱ->ㅎ
  - 내림차순 Descending Sort  DESC
  - 형식) order by 칼럼명1, 칼럼명2, 칼럼명3, ~~ --중복된 값이 있을 경우 칼럼명을 늘려 정렬가능
  
  
  --sungjuk 테이블 조회
  select * from sungjuk;
  
  --전체 레코드를 이름순으로 정렬해 조회
 select uname  
  from sungjuk
  order by uname asc; --이름기준 오름차순 정렬( asc 생략가능 )
  
  select uname 
  from sungjuk
  order by uname desc;
  
  select uname, kor
  from sungjuk
  order by kor asc;
  
  
  --1차정렬: 국어점수순으로 정렬
  --2차정렬: 국어점수가 같다면 이름을 기준으로 내림차순 정렬
  select uname, kor
  from sungjuk
  order by kor, uname desc;--국어점수 오름차순 정렬 후 국어점수 같을 경우 이름 내림차순 정렬

 --1차(ker), 2차(eng), 3차(mat) 정렬
 select kor, eng, mat
 from sungjuk
order by kor desc, eng desc, mat desc;
  
  
  
  --문제) 평균 7 이하 행 이름순으로 정렬
 
 select uname, aver
  from sungjuk
  where aver <= 70;
  --내가 원하는 정보를 다 만든 다음에,
 
  select uname, aver
  from sungjuk
  where aver <= 70
  order by uname;
  --정렬을 해야 에러 없이 조회 가능
   
   
   select * 
   from sungjuk
   order by uname
   where aver <= 70;--ORA-00933: SQL 명령어가 올바르게 종료되지 않았습니다
   ////////////////////////////////////////////////////////////
   
   [자동으로 일련번호 부여]
   -Oracle Datanase : sequence 생성
   -M*SQL Database  : indentity 제약조건
   
   ///////////////////////////////////
   
   
   --sungjuk테이블 삭제
   drop table sungjuk;
   commit;
   
   
   --sungfuk 테이블 생성
   
  create table sungjuk (
      sno int                 not null
    , uname varchar(50)     not null
    , kor int               not null
    , eng int               not null
    , mat int               not null
    , tot int               
    , aver int              
    , addr varchar(20)
    , wdate date
    ); 
    
    
    --행추가
    insert into sungjuk(sno, uname, kor, eng, mat, addr, wdate)
    values(1, '홍길동', 89, 88, 87, 'Seoul', '2023-02-23');--날짜 데이터는 문자열 형태로 추가
    
    
    select * from sungjuk;
    delete from sungjuk;--전체 행 삭제
    commit;

 

 

    //////////////////////////////
    [시퀀스 Sequence]
    - 일련번호를 자동 생성
    -시퀀스 생성 : create sequence 시퀀스명;
    -시퀀스 삭제 : drop sequence 시퀀스명;
    
--시퀀스 생성 형식)
    create sequence 시퀀스명
    increment by 증가값 start with 시작값;
    
    --sungjuk 테이블에서 사용할 시퀀스 생성 >>한 개의 테이블에서 한 개의 시퀀스만 사용 추천
    create sequence sungjuk_seq;
    
    --시퀀스 삭제
    drop sequence sungjuk_seq;    
    
    ////////////////////////////////
    
    [sysdate 함수] - 시스템의 현재 날짜 정보
    --괄호가 붙지 않는 함수
    
    /////////////////////////////////////////////
    
    --시퀀스와 sysdate 함수를 이용한 행추가 테스트 
     insert into sungjuk(sno, uname, kor, eng, mat, addr, wdate)
    values(sungjuk_seq.nextval, '홍길동', 89, 88, 87, 'Seoul', sysdate);
    
    select * from sungjuk;
    
    //////////////////////////////////////////////////////////
    
    --문제) sno =2 행 삭제
    
    delete  from sungjuk  where sno = 2; 
    
    commit;
    
    select * from sungjuk;

 

 

o 성적테이블 조회 문제

      --sungjuk 테이블 생성
    
    create table sungjuk (
      sno int                 not null
    , uname varchar(50)       not null
    , kor int               not null
    , eng int               not null
    , mat int               not null
    , tot int               
    , aver int              
    , addr varchar(20)
    , wdate date
    ); 
    
    //////////////////////////////////////////////
    [sungjuk 테이블 입력 데이터]
    
    
insert into sungjuk(sno, uname, kor, eng, mat, addr, wdate)
values(sungjuk_seq.nextval,'아이티윌', 90, 85, 95, 'Seoul',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'무궁화',40,50,20,'Seoul',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'진달래',90,50,90,'Jeju',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'개나리',20,50,20,'Jeju',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'봉선화',90,90,90,'Seoul',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'나팔꽃',50,50,90,'Suwon',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'선인장',70,50,20,'Seoul',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'소나무',90,60,90,'Busan',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'참나무',20,20,20,'Jeju',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'홍길동',90,90,90,'Suwon',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'무궁화',80,80,90,'Suwon',sysdate);
    
    
    commit;
    
    select count(*) from sungjuk; --전체 레코드 개수
    select * from sungjuk;
    
    
-- 문1) 전체행에 대해서 총점(tot), 평균(aver)을 구하시오
    update sungjuk
    set tot = ( kor + eng + mat ), aver = ( kor + eng + mat )/3;


-- 문2) 이름에 '나' 글자가 있는 행을 조회하시오
    select uname,sno
    from sungjuk
    where uname like('%나%');


-- 문3) 주소가 서울, 제주 행을 조회하시오
    select addr,sno
    from sungjuk
    where addr = 'Seoul' or addr = 'Jeju'; --논리연산자 뒤에는 새롭게 시작한다고 보아야
    
    select addr,sno
    from sungjuk
    where addr in ('Seoul', 'Jeju');--소괄호 안의 것 조회 


-- 문4) 국영수 세과목 모두 90점 이상 행이 몇개인지 조회하시오
    select count(*) as cnt --칼럼명 변경(as 생략가능)
    from sungjuk
    where kor >= 90 and  eng >= 90 and  mat >= 90;


-- 문5) 주소별로 정렬해서 조회하시오. 주소가 같다면 일련번호 순으로 다시 정렬해서 조회하시오
    select *
    from sungjuk
    order by addr, sno;
    
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

   create table sungjuk (
    sno     int primary key --기본키(유일성), 각각의 레코드를 대표하는 유일한 컬럼은 반드시 존재해야, 접근가능성을 위해
   , uname varchar(50) not null
   , kor    int         check(kor between 0 and 100) --국어 점수는 0~100점 사이만 입력가능
    , eng    int         check(eng between 0 and 100) --영어 점수는 0~100점 사이만 입력가능
    , mat    int         check(mat between 0 and 100) --수학 점수는 0~100점 사이만 입력가능
    , addr  varchar(50)  check(addr in ('Seoul', 'Busan', 'Jeju', 'Suwon')) 
    , tot   int         default 0
    , aver int          default 0
    , wdate   date    default sysdate  --인서트 되는 시점의 날짜기 기록되는 함수
    );
   
   ////////////////////////////////////////
   
   select * from sungjuk;
   
   [테이블 제약조건 에러 메시지]
   --primary key 제약조건
   insert into sungjuk(sno, uname) values(1, '홍길동');
   --2번 실행했을 때, ORA-00001: 무결성 제약 조건(SYSTEM.SYS_C007314)에 위배됩니다
   --sno 칼럼은 기본키(pk)이므로 중복값 허용X
   
   
   --not null 조건
   --ORA-01400: NULL을 ("SYSTEM"."SUNGJUK"."UNAME") 안에 삽입할 수 없습니다
   --아래의 것은 uname칼럼에 대한 입력값이 없기 때문에 오류 발생
   insert into sungjuk(sno) values(2);
   
    insert into sungjuk(sno, uname) values(2, '1234567890');
    insert into sungjuk(sno, uname) values(3, 'asdfjklasd');
    insert into sungjuk(sno, uname) values(5, '무궁화');
    
    
    --ORA-12899: "SYSTEM"."SUNGJUK"."UNAME" 열에 대한 값이 너무 큼(실제: 11, 최대값: 10)
    insert into sungjuk(sno, uname) values(4, '12345678901');
    
    
    --ORA-12899: "SYSTEM"."SUNGJUK"."UNAME" 열에 대한 값이 너무 큼(실제: 12, 최대값: 10)
    insert into sungjuk(sno, uname) values(6, '대한민국');
    
    
    --check제약조건
    insert into sungjuk(sno, uname, kor, eng, mat)
    values(1, '홍길동', -10, 20, 300);
    --ORA-02290: 체크 제약조건(SYSTEM.SYS_C007329)이 위배되었습니다, 점수범위 체크조건에 위배
     insert into sungjuk(sno, uname, kor, eng, mat, addr)
    values(2, '장길산', 10, 20, 30, 'Incheon');
    --ORA-02290: 체크 제약조건(SYSTEM.SYS_C007330)이 위배되었습니다, 주소 체크조건에 위배
    
    select * from sungjuk;
    
    
    --default 제약조건
    --tot와 aver는 0 , wdate는 현재날짜가 조회됨
     insert into sungjuk(sno, uname, kor, eng, mat, addr)
    values(1, '홍길동', 10, 20, 30, 'Seoul');
    
        insert into sungjuk(sno, uname, kor, eng, mat, addr)
    values(1, '홍길동', 10, 20, 30, 'Seoul');

//////////////////////////////////////////////////////////////////

 [데이터그룹화]
    
    select * from sungjuk order by addr;
    
    [distinct()]
    -칼럼에 중복 내용이 있으면 대표값 1개만 출력
    -형식) distinct(칼럼명)
    
    select addr from sungjuk;
    select addr from sungjuk order by addr; --asc생략가능
    select addr from sungjuk order by addr desc;
    
    select distinct(addr) from sungjuk;
    select distinct(uname) from sungjuk;
    
    
    //////////////////////////////////////
    [group by]
    -칼럼에 동일 내용끼리 그룹화
    -형식) group by 칼럼1, 칼럼2, 칼럼3, (칼럼1, 칼럼2 그룹 칼럼3에 의한 그룹)
    
    --주소가 동일한 값을 그룹화시키고 결과값 동일
    select addr
    from sungjuk
    group by addr; --distinct와 결과값 동일
    
    
    --ORA-00979: GROUP BY 표현식이 아닙니다. "not a GROUP BY expression"
    --group화된 곳에서는 한 개 데이터만 select할 수 있으므로 group내에서 uname과 같이 여러 값이 있는 경우에는 에러
     select addr, uname 
    from sungjuk
    group by addr;