본문 바로가기

빅데이터과정/SQL

#43_140812_계층적질의문 # 계층적질의문 계층적질의문(SYS_CONNECT_BY_PATH) # 순방향SELECT ename, SYS_CONNECT_BY_PATH(ENAME||'('||sal||')','/') employeeFROM EMPSTART WITH ename='KING'CONNECT BY PRIOR EMPNO = MGR SELECT ename, LEVEL, LPAD(' ', 2*LEVEL)||SYS_CONNECT_BY_PATH(ENAME,'/') PATH FROM EMP START WITH ename='KING'CONNECT BY PRIOR EMPNO = MGR; # 역방향SELECT ename, LEVEL, SYS_CONNECT_BY_PATH(ENAME||'('||sal||')','/') employeeFROM EMPS.. 더보기
#8_140624_정규식 함수 # 정규식 함수 l 정규식 함수- regexp_like :- regexp_substr- regexp_instr- regexp_replace- regexp_count^ : 시작$ : 끝. 자릿수| : 또는 문제322. 사원 이름에 EN 또는 IN 을 포함하고 있는 사원들의 사원번호, 이름, 월급을 출력하시오 #튜닝 전SELECT empno, ename, salFROM EMPWHERE ename LIKE '%EN%' or ename LIKE '%IN'; #튜닝 후SELECT empno,ename, salFROM EMPWHERE regexp_like(ename,'(EN|IN)'); 문제323. 이름의 첫글자가 S로 시작하고 끝글자가 T또는 H로 끝나는 사원들의 이름을 출력하시오 SELECT enameFROM .. 더보기
#8_140624_with # WITH l with 절 : 복합 query 내에서 동일한 select 문이 반복되는 경우 쓰임.l with 절의 장점 두번이상 반복되고 있는 쿼리가 있는 SQL의 성능을 높일 수 있다 l oracle temporary tablespace(임시저장영역) 아래의 데이터를 저장한다 문제319. 직업, 직업별 최대월급을 출력하는데 직업별 최대월급이 직업별 최대월급들에 대한 평균값보다 더 큰 것만 출력하시오 #튜닝 전SELECT job, MAX(sal)FROM EMPGROUP BY jobHAVING MAX(sal) > (SELECT AVG(MAX(sal)) FROM EMP GROUP BY job); #튜닝 후WITH job_maxsal AS (SELECT job, MAX(sal) 최대값 FROM EMP GR.. 더보기
#7_140623_DCL(Data Control Language) # DCL(Data Control Language) l DCL(Data Control Language)- grant, revoke- 유저생성create user allenidentified by tiger;- 접속 권한 부여grant connect to allen;grant create session to allen;- user에게 접속한 이후에는 connect 명령어로 다른 유저에게 접속connect jack/oracle- 현재 유저의 접속상태를 알아보는 법show userUSDR is “” à 접속이 안되있다는 의미 1. 유저생성 문제273. allen 접속창에서 테이블을 생성하시오(테이블명 : emp708, 컬럼명 : empno, eanme, sal) GRANT CREATE TABLE TO alle.. 더보기
#7_140623_CORRELATED SUBQUERY # CORRELATED SUBQUERY 문제312. 이름, 월급을 출력하는데 자기의 월급이 자기가 속한 부서번호의 평균월급보다 더 큰 사원들만 출력하시오 SELECT ename, salFROM EMP mWHERE sal>(SELECT AVG(sal) FROM EMP s WHERE s.deptno=m.deptno); l main query의 컬럼이 subquery로 들어가게 되면 main query 부터 작동이 된다.l correlated subquery 문제313. 이름, 직업을 출력하는데 자기가 속한 직업의 인원수가 3명 이상인것만 출력하시오 SELECT ename, jobFROM EMP mWHERE 3 (SELECT COUNT(*) FROM EMP s WHERE s.job=m.job); 더보기
#7_140623_마지막 문제 140623# 점심과제 문제293. adams 라는 유저를 생성하고 adams 라는 유저에게 적절한 권한을 주고 adams 라는 유저로 접속해서 아래 테이블을 생성하시오. ename에 unique 제약을 걸고 sal에 0~7000 사이의 데이터만 입력되도록 check 제약을 걸고 제약에 위반된 데이터를 입력해서 입력이 안 되는 insert 문장 작성하시오테이블명 : emp500컬럼명 empno, ename, sal #scott 창에서CREATE USER adamsIDENTIFIED BY tiger;GRANT CONNECT TO adams;GRANT UNLIMITED TABLESPACE TO adams;GRANT CREATE TABLE TO adams; #adams 창에서create table emp500.. 더보기
#7_140623_EXISTS # EXISTS 문제308. 사원이름, 부서위치, 부서번호를 출력하시오 SELECT e.ename, d.loc, e.deptnoFROM EMP e, DEPT dWHERE e.deptno=d.deptno; 문제309. 부서테이블에서 부서위치를 출력하는데 사원테이블에 존재하는 부서번호에 대한 것만 출력하시오 #튜닝전SELECT DISTINCT d.locFROM EMP e, DEPT dWHERE e.deptno=d.deptno; #튜닝후SELECT locFROM DEPT dWHERE EXISTS (SELECT 'x' FROM EMP e WHERE e.deptno=d.deptno); l exits : 전부다 검색할 필요 없이 존재여부만 판단하기 때문에 해당하는 부서번호를 찾으면 존재하기 때문에 그 이하는 검색하.. 더보기
#7_140623_PAIRWISE # PAIRWISE 문제306. 직업이 SALESMAN인 사원들과 월급이 같고 커미션이 같은 사원들의 이름과 월급과 커미션을 출력하시오 SELECT *FROM EMPWHERE sal IN (SELECT sal FROM EMP WHERE job='SLAESMAN') ANDcomm IN (SELECT comm FROM EMP WHERE job='SALESMAN');l non pairwise 방식l multiple subquery SELECT ename, sal, jobFROM EMPWHERE (sal, comm) IN (SELECT sal, comm from EMP WHERE job='SALESMAN');l pairwise 방식l multiple subquery 문제307. KING 월급을 1500으로 커미.. 더보기
#7_140623_ALTER TABLE # ALTER TABLE 문제283. 사원테이블에 email 컬럼을 추가하시오 ALTER TABLE EMPADD email VARCHAR2(30); 문제284. 사원 테이블에 address 컬럼을 추가하시오 ALTER TABLE EMPADD address VARCHAR2(30); 문제285. address 컬럼의 길이를 varchar2(300)으로 늘리시오 ALTER TABLE EMPmodify address VARCHAR2(300); 문제286. 사원 테이블에 address 컬럼을 삭제하시오 alter TABLE EMPDROP COLUMN address; 문제287. 사원 테이블에 sal 컬럼을 삭제하시오 ALTER TABLE EMPDROP COLUMN sal;l CTRL+Z 를 바로 눌르면 취소가능 .. 더보기
#7_140623_DATA DICTIONARY # Data Dictionary l data dictionary- 언더바에 DB object(table, view, index, sequence, synonym)를 입력하면 확인할 수 있음- 언더바 이후에는 복수형으로 적어야함(index à indexes)1. user_ : 내가 생성한 것2. all_ : 내가 생성한 것 + 다른 유저가 권한준 것(grant)3. dba_ : DB에 있는 모든 객체를 다 볼 수 있음 문제298. 내가 생성한 테이블 리스트를 조회 SELECT table_nameFROM user_tables; l user_tables : data dictionary 문제299. 내가 생성한 인덱스 리스트를 조회하시오 SELECT * FROM user_indexes; 문제300. DB에 있는.. 더보기
#7_140623_DROP # DROP l 테이블 drop 할 떄“drop table emp;” 를 하면 휴지통으로 들어가지만 “drop table emp purge;” 휴지통을 거치지 않고 바로 삭제l drop column 은 rollback 도 안되고 flashback 도 안됨 l DCL은 바로 commit 되기 때문에 rollback은 안되고 flashback은 가능 문제286. 사원 테이블에 address 컬럼을 삭제하시오 alter TABLE EMPDROP COLUMN address; 문제287. 사원 테이블에 sal 컬럼을 삭제하시오 ALTER TABLE EMPDROP COLUMN sal;l CTRL+Z 를 바로 눌르면 취소가능 문제288. 사원 테이블의 ename 컬럼을 감추시오 alter table empset un.. 더보기
포맷 및 함수 l ‘/’ + ‘enter’ : 방금 수행했던 SQL이 수행됨 l Edit : command 창에서 방금 사용한 SQL 문을 수정할 수 있음 l Select * From nls_session_parameters; - 날짜를 조회하려면 현재 내가 접속한 세션의 날짜 포맷을 확인해야 한다. NLS_DATE_FORMAT을 확인해야 한다 l ALTER SESSION SET nls_date_format='RR/MM/DD' - 현재 세션의 날짜 포맷을 변경 l 날짜 포맷(책 4-12)- 요일 : day, d- 년도 : YYYY, YY, RRRR, RR, year- 달 : mm, mon- 일 : dd- 주 : ww, w, iw- 시간 : hh, hh24- 분 : mi- 초 : ssRRYY81 입력81 입력현재 년도에.. 더보기
#6_140621_DB OBJECT # DB OBJECT l db object 5가지1. table2. view3. sequence4. synonym5. index l view를 사용하는 이유 2가지- 데이터의 보안을 위해서(특정 컬럼을 감추기 위해서)- 복잡한 쿼리를 간단하게 하기 위해서 l view 생성CREATE VIEW emp550ASSELECT empno,ename,sal,deptno FROM EMP; 1. view 문제258. emp550 view의 king의 월급을 0로 변경하면 실제 테이블인 emp 테이블도 수정될까? CREATE VIEW emp550ASSELECT empno,ename,sal,deptnoFROM EMP; UPDATE emp550SET sal=0WHERE ename='KING'; è 가능하다 문제261. DA.. 더보기
#6_140621_일일과제 140621 #마무리 과제 마무리문제1. 지하철 요일별 성범죄 데이터를 오라클에 DB에 입력하고 쿼리를 작성해서 2013년도에 가장 지하철에서 가장 성범죄가 많이 발생한 요일을 출력하세요 CREATE TABLE crime(ssum NUMBER(10),sun NUMBER(10),mon NUMBER(10),tue NUMBER(10),wed NUMBER(10),thur NUMBER(10),fri NUMBER(10),sat NUMBER(10)); SELECT * FROM crime; INSERT INTO crime VALUES(1026,44,168,180,193,172,199,70); SELECT *FROM( SELECT week, cnt, RANK() OVER ( ORDER by cnt DESC ) rank .. 더보기
#6_140621_제약(constraint) # 제약(constraint) l 제약(constraints)- 제약을 사용해야 하는 이유 : data의 품질을 높이기 위해서 l 제약의 종류 5가지1. primary key : 중복된 data, null 입력 안됨2. unique : 중복된 data 입력 안됨3. not null : null 입력 안됨4. check : 미리 정의된 data만 입력 수정 가능5. foreign key : 참조하는 컬럼에 제약 l 테이블 생성시 제약 거는 방법 2가지1. table level CREATE TABLE emp585( empno NUMBER(10), ename VARCHAR2(10), sal NUMBER(10),CONSTRAINT emp585_ename_un UNIQUE(ename)); 2. column lev.. 더보기
#6_140621_테이블 및 컬럼 이름 지정규칙 # 테이블 및 컬럼 이름 지정규칙 - 문자로 시작 - 길이는 1~30 - A~z, a~z, 0~9, _, $_ # 몬 포함할 수 있다 - 동일한 유저가 소유한 다른 객체의 이름과 중복 안됨 - Oracle 서버 예약어는 사용할 수 없다 더보기
#5_140607_분석함수 # 분석함수 문제193. 사원이름, 월급, 사원 테이블의 최대월급, 사원 테이블의 최소월급, 사원 테이블의 평균월급을 출력하시오 SELECT ename, sal, (SELECT MAX(sal) FROM emp) 최대월급, (SELECT MIN(sal) FROM emp) 최소월급, (SELECT AVG(sal) FROM emp) 평균월급FROM EMP; - Select 절의 서브쿼리 사용 : scalar subquery # 분석함수를 이용하면 SELECT ename, sal, MAX(sal) OVER () 최대월급, MIN(sal) OVER () 최소월급, AVG(sal) OVER () 평균월급FROM EMP; 문제202. 이름, 월급, 부서위치, 자기가 속한 부서위치의 최대월급을, 자기가 속한 부서위치의.. 더보기
#5_140607_일일과제(crime) #마무리 과제 crime_cause.txtcrime_time.txt Select * from crime_time; 를 이용 1. 아침 9시부터 12시 사이에 가장 많이 발생하는 범죄는?SELECT *FROM (SELECT crime_type, RANK() OVER (ORDER BY f9t12 desc) 범죄순위FROM crime_time)WHERE 범죄순위 = 1; 2. 하루중에서 살인이 가장 많이 발생하는 시간대는? SELECT *FROM (SELECT crime_type, ttime, cnt, RANK() over(ORDER BY cnt desc) 순위FROM crime_timeunpivot(cnt FOR ttime IN (F0T3,F3T6,F6T9,F9T12,F12T15,F15T18,F18T21,F.. 더보기
#4_140531_null에 대해서 # NULL l null 값을 입력하는 방법1. 암시적 입력2. 명시적 입력 : 직접 입력해서 넣어주는 방법- null- ‘ ‘ 문제214. 이름이 null 이 아닌 사원들의 이름과 월급을 출력하시오 SELECT ename, salFROM EMP WHERE TRIM(ename) IS NOT NULL; - ename에 null값이 존재하고 공백이 존재할경우 위와 같이 쓴다 더보기
#4_140531_140621_DDL # DDL(Data Definition Language) l DDL 문 : 공공 데이터를 담을 테이블 생성과 관리방법 l DDL문 - create, alter, drop, truncate, rename 1. alter 문제226. 부서테이블에 sumsal 이란 컬럼을 추가하시오 ALTER TABLE DEPTADD sumsal NUMBER(10); 문제255. 사원테이블을 읽기 전용으로 변경하시오 ALTER TABLE EMP READ ONLY; SELECT *FROM user_tablesWHERE table_name='EMP'- 확인하는 방법 문제256. 다시 emp 테이블을 read write로 변경하시오 ALTER TABLE EMP READ write; 2. create문제228. 아래의 테이블을 생성.. 더보기
#4_140531_sysdate # SYSDATE 문제211. 오늘 입사한 사원의 이름과 입사일을 출력하시오 SELECT ename, hiredateFROM EMPWHERE hiredate = TO_DATE('14/06/07','RR/MM/DD'); l sysdate는 시분초가 모두 들어있기 때문에 where hiredate=sysdate 구문은 옳바르지 않다 문제212. 아래와 같이 데이터를 입력하고 오늘 입사한 사원의 이름과 입사일을 출력하시오 INSERT INTO emp(empno, ename, sal, deptno, hiredate)VALUES(4949, 'JONE', 4500, 30, TO_DATE('14/06/07','RR/MM/DD')); SELECT ename, hiredateFROM EMPWHERE TO_CHAR(hir.. 더보기
#4_140531_Subquery # SUBQUERY l 서브쿼리의 종류- Single row subqeury서브쿼리에서 메인쿼리로 하나의 값이 리턴연산자 : =, >, =, any, all, - Multiple column subquery : 서브쿼리에서 메인쿼리로 여러 개의 컬럼값이 리턴 l 서브쿼리를 쓸 수 있는 절- Select : scalar subquery- From : in line view- Where : subquery- Having : subquery- Order by : scalar subquery 문제142. SMITH와 같은 부서번호에서 근무하는 사원들의 이름과 월급과 부서번호를 출력하는데SMITH는 제외하고 출력하시오 SELECT ename, sal, deptnoFROM EMPWHERE deptno=(SELECT.. 더보기

티스토리툴바