SQL 예제 - 해당되는 글 22건

-- 커서 수행시에 커서 정의구문부터 커서 삭제구문까지 일괄로 블록을 잡아서 한번에 수행하는
    것이 좋다. 개별로 수행 시킬 수도 있지만 오류가 날 가능성이 있으므로 한번에 수행하자.

-- 실습용 테이블 생성 및 Temp 데이터 추가
CREATE TABLE 우편물(
번호 int identity(1,1)
, 우편번호 varchar(3)
, 우편주소 varchar(6)
)
INSERT INTO 우편물(우편번호, 우편주소)
VALUES('001', '코난동')
INSERT INTO 우편물(우편번호, 우편주소)
VALUES('002', '악마동')
INSERT INTO 우편물(우편번호, 우편주소)
VALUES('003', '악어동')
INSERT INTO 우편물(우편번호, 우편주소)
VALUES('004', '태오동')
INSERT INTO 우편물(우편번호, 우편주소)
VALUES('', '코난동')
INSERT INTO 우편물(우편번호, 우편주소)
VALUES('', '악마동')

CREATE TABLE 우편번호(
우편번호 varchar(3)
, 우편주소 varchar(6)
)
INSERT INTO 우편번호 VALUES('001', '코난동')
INSERT INTO 우편번호 VALUES('002', '악마동')
INSERT INTO 우편번호 VALUES('003', '거북동')
INSERT INTO 우편번호 VALUES('004', '태오동')
INSERT INTO 우편번호 VALUES('005', '악어동')

SELECT * FROM 우편물
SELECT * FROM 우편번호

--커서 선언
DECLARE cur_konan_Test CURSOR
FOR
SELECT 번호, 우편번호, 우편주소 FROM 우편물
--커서 오픈
OPEN cur_konan_Test
--변수 선언
DECLARE @v_번호 INT
DECLARE @v_우편번호 VARCHAR(3)
DECLARE @v_우편주소 VARCHAR(6)
--첫 로우 FETCH
FETCH NEXT FROM cur_konan_Test INTO @v_번호, @v_우편번호, @v_우편주소
WHILE @@FETCH_STATUS = 0
BEGIN
--FETCH된 데이터를 tempdb에 삽입
UPDATE 우편물
SET 우편번호 = (SELECT 우편번호 FROM 우편번호 WHERE 우편주소 = @v_우편주소)
WHERE 번호 = @v_번호
--다음 로우 FEETCH - 루프
FETCH NEXT FROM cur_konan_Test INTO @v_번호, @v_우편번호, @v_우편주소
END
--커서 CLOSE
CLOSE cur_konan_Test
--커서 DEALLOCATE
DEALLOCATE cur_konan_Test
GO

-----------------------------------------------------------
-- 1. 학생 테이블에서 각 학생의 시험 점수를 가지고,
-- ‘수,우,미,양,가’ 형식의 값을 지정하는 커서 작성 예
create table student
(
 id int primary key,
 name varchar(20) not null,
 value tinyint not null,  -- 점수
 grade nchar(1)  -- '수,우,미,양,가'
)

insert into student(id, name, value)
values(1, '철수', 60)
insert into student(id, name, value)
values(2, '영희', 80)
insert into student(id, name, value)
values(3, '인호', 50)

select * from student

-- 커서 선언
DECLARE CUR_STUDENT_GRADE CURSOR
FOR
SELECT ID, NAME, VALUE, GRADE FROM STUDENT
-- 커서 오픈
OPEN CUR_STUDENT_GRADE
-- 변수 선언
DECLARE @id int,
@name varchar(20),
@value tinyint,
@grade nchar(2)
-- 첫 행 패치
FETCH NEXT FROM CUR_STUDENT_GRADE INTO @ID, @NAME, @VALUE, @GRADE
-- 업데이트 구문 (WHILE~END), 다음 행 패치
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@VALUE >= 90)
 BEGIN SET @GRADE = '수' END
ELSE IF (@VALUE >= 80)
 BEGIN  SET @GRADE = '우' END
ELSE IF (@VALUE >= 70)
 BEGIN  SET @GRADE = '미'  END
ELSE IF (@VALUE >= 60)
 BEGIN  SET @GRADE = '양' END
ELSE
 BEGIN  SET @GRADE = '가'  END
UPDATE STUDENT SET GRADE = @GRADE WHERE ID = @ID
FETCH NEXT FROM CUR_STUDENT_GRADE INTO @ID, @NAME, @VALUE, @GRADE
END
-- 커서 클로즈
CLOSE CUR_STUDENT_GRADE
-- 커서 삭제
DEALLOCATE CUR_STUDENT_GRADE
-- 결과 확인
SELECT * FROM STUDENT

------------------------------------------
-- 실습 테이블 재정의 --
drop table student
create table student
(
 id int primary key,
 name varchar(20) not null,
 value tinyint not null,  -- 점수
 grade nchar(1)  -- '수,우,미,양,가'
)

insert into student(id, name, value)
values(1, '철수', 60)
insert into student(id, name, value)
values(2, '영희', 80)
insert into student(id, name, value)
values(3, '인호', 50)

select * from student

-- 2. 위에서 작성한 커서를 if … else 문 대신에
-- case … when 문을 사용하여 작성하라.

-- 커서 정의
DECLARE CUR_STUDENT_GRADE2 CURSOR
FOR
SELECT * FROM STUDENT
-- 커서 오픈
OPEN CUR_STUDENT_GRADE2
-- 변수 선언
DECLARE @id int,
@name varchar(20),
@value tinyint,
@grade nchar(2)
-- 첫 행 패치
FETCH NEXT FROM CUR_STUDENT_GRADE2 INTO @ID, @NAME, @VALUE, @GRADE
-- 업데이트 (WHILE~END), 다음 행 패치
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE STUDENT
SET GRADE = (CASE WHEN @VALUE >= 90 THEN '수'
WHEN @VALUE >= 80 THEN '우'
WHEN @VALUE >= 70 THEN '미'
WHEN @VALUE >= 60 THEN '양'
ELSE '가' END)
WHERE ID = @ID
FETCH NEXT FROM CUR_STUDENT_GRADE2 INTO @ID, @NAME, @VALUE, @GRADE
END
-- 커서 클로즈
CLOSE CUR_STUDENT_GRADE2
-- 커서 삭제
DEALLOCATE CUR_STUDENT_GRADE2
-- 확인
SELECT * FROM STUDENT

Trackbacks 0 | Comments 0

--예제 1) 인구 사회학적 구성비: 성별(Q9 문항)
--1. 총 응답자의 수를 구하는 VIEW
CREATE VIEW VIEW_0001
AS
SELECT COUNT(*) AS '총(명)' FROM RES

SELECT * FROM VIEW_0001

--2. 응답자들 중 남자의 수를 구하는 VIEW
CREATE VIEW VIEW_0002
AS
SELECT COUNT(*) AS '남자(명)' FROM RES WHERE Q9 = 1

SELECT * FROM VIEW_0002

--3. 응답자들 중 여자의 수를 구하는 VIEW
CREATE VIEW VIEW_0003
AS
SELECT COUNT(*) AS '여자(명)' FROM RES WHERE Q9=2

SELECT * FROM VIEW_0003

--4. 응답자들 중 남자 비율을 구하는 VIEW (남자의 비율 = 남자 응답자수/전체 응답자수 * 100)
CREATE VIEW VIEW_0004
AS
SELECT (CONVERT(FLOAT,(SELECT COUNT(*) FROM RES WHERE Q9=1))
/ CONVERT(FLOAT,(SELECT COUNT(*) FROM RES)) * 100) AS '남자(%)'

SELECT * FROM VIEW_0004

--5. 응답자들 중 여자의 비율을 구하는 view
CREATE VIEW VIEW_0005
AS
SELECT (CONVERT(FLOAT,(SELECT COUNT(*) FROM RES WHERE Q9=2))
/ CONVERT(FLOAT,(SELECT COUNT(*) FROM RES)) * 100) AS '여자(%)'

SELECT * FROM VIEW_0005

--6. 위의 view를 Full Outer Join을 통한 결과 출력
SELECT * FROM VIEW_0001, VIEW_0002, VIEW_0004, VIEW_0003, VIEW_0005

--7. 위의 결과를 프로시저로 작성
CREATE PROC UP_RES_FVIEW
AS
PRINT '성별분포도(단위:명/%)'
PRINT '-------------------------------------------'
SELECT * FROM VIEW_0001, VIEW_0002, VIEW_0004, VIEW_0003, VIEW_0005

--8. 프로시저의 실행했을때의 결과 화면
EXEC UP_RES_FVIEW

-------------------------------------------------------------------
--예제 2) 자사 브랜드 인지도: 성별(Q7+Q9 문항)

--쿼리문 작성 1) 자사 브랜드 인지도(성별: 남)
--1. 프로시저 작성
CREATE PROC 브랜드인지도_남
AS
DECLARE @REQ FLOAT, @Q1 FLOAT, @Q2 FLOAT, @Q3 FLOAT, @Q4 FLOAT
SET @REQ = (SELECT COUNT(*) AS '남자(명)' FROM RES WHERE Q9 = 1)
SET @Q1 = (SELECT COUNT(*) FROM RES WHERE Q9=1 AND Q7=1)
SET @Q2 = (SELECT COUNT(*) FROM RES WHERE Q9=1 AND Q7=2)
SET @Q3 = (SELECT COUNT(*) FROM RES WHERE Q9=1 AND Q7=3)
SET @Q4 = (SELECT COUNT(*) FROM RES WHERE Q9=1 AND Q7=4)
PRINT '브랜드 인지도(남/단위:명)'
PRINT '---------------------------------------------'
SELECT (@REQ) AS '응답자', (@Q1) AS '답변1', (@Q2) AS '답변2',
(@Q3) AS '답변3', (@Q4) AS '답변4'
PRINT '브랜드 인지도(남/단위:%)'
PRINT '---------------------------------------------'
SELECT (@REQ/@REQ*100) AS '응답자', ROUND((@Q1/@REQ*100), 2) AS '답변1',
ROUND((@Q2/@REQ*100), 2) AS '답변2', ROUND((@Q3/@REQ*100), 2) AS '답변3',
ROUND((@Q4/@REQ*100), 2) AS '답변4'

EXEC 브랜드인지도_남

--쿼리문 작성 2) 자사 브랜드 인지도(성별: 여)
--1. 프로시저 작성
CREATE PROC 브랜드인지도_여
AS
DECLARE @REQ FLOAT, @Q1 FLOAT, @Q2 FLOAT, @Q3 FLOAT, @Q4 FLOAT
SET @REQ = (SELECT COUNT(*) AS '여자(명)' FROM RES WHERE Q9 = 2)
SET @Q1 = (SELECT COUNT(*) FROM RES WHERE Q9=2 AND Q7=1)
SET @Q2 = (SELECT COUNT(*) FROM RES WHERE Q9=2 AND Q7=2)
SET @Q3 = (SELECT COUNT(*) FROM RES WHERE Q9=2 AND Q7=3)
SET @Q4 = (SELECT COUNT(*) FROM RES WHERE Q9=2 AND Q7=4)
PRINT '브랜드 인지도(여/단위:명)'
PRINT '---------------------------------------------'
SELECT (@REQ) AS '응답자', (@Q1) AS '답변1', (@Q2) AS '답변2',
(@Q3) AS '답변3', (@Q4) AS '답변4'
PRINT '브랜드 인지도(여/단위:%)'
PRINT '---------------------------------------------'
SELECT (@REQ/@REQ*100) AS '응답자', ROUND((@Q1/@REQ*100), 2) AS '답변1',
ROUND((@Q2/@REQ*100), 2) AS '답변2', ROUND((@Q3/@REQ*100), 2) AS '답변3',
ROUND((@Q4/@REQ*100), 2) AS '답변4'

EXEC 브랜드인지도_여

Trackbacks 0 | Comments 0

-- ★ 트리거(TRIGGER)란?
-- 총의 방아쇠를 당기면 총알을 발사하듯이
-- 테이블에 특정 쿼리문(INSERT, UPDATE, DELETE)을
-- 수행할 경우에 자동으로 어떤일을 하도록 만드는 것
-- (주의 : SELECT 일 경우에는 TRIGGER 적용 안됨)


-- ★ TRIGGER 형식
CREATE TRIGGER 트리거명
ON 대상이 되는 테이블
FOR (INSERT, UPDATE, DELETE)
AS
-- 수행해야 하는 일들

CREATE TABLE T1
(
 NUM INT,
 NAME VARCHAR(10)
)

SELECT * FROM T1


-- 등록하고자 하는 트리거가 존재한다면 해당 트리거 삭제
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE TYPE = 'TR' AND NAME = 'TRG_01')
DROP TRIGGER TRG_01

-- INSERT 문이 실행될때 특정 문장 출력해주는 트리거
CREATE TRIGGER TRG_01
ON T1
FOR INSERT
AS
PRINT 'T1 테이블에 1개의 데이터가 등록되었습니다'

INSERT T1 VALUES(2, '나나나')

-- INSERT 문이 실행되면 테이블 셀렉트 해오는 트리거
CREATE TRIGGER TRG_011
ON T1
FOR INSERT, UPDATE
AS
SELECT * FROM T1

INSERT T1 VALUES(3, '다다다')


-- 트리거 등록 확인
SELECT * FROM SYSOBJECTS WHERE TYPE = 'TR'
--> TYPE = TR -> 트리거
--> TYPE = P -> 프로시저

SELECT * FROM T1
SELECT * FROM T2

CREATE TABLE T2
(
 NUM INT,
 NAME VARCHAR(10)
)


-- ★ INSERTED, DELETED 테이블 : 트리거가 실행될때 메모리상에만 임시로 저장되는 테이블
-- ★ UPDATED 테이블은 있는가? 없다. 업데이트시에도 INSERTED나 DELETED 테이블!!

-- T1 테이블 데이터 삽입시 T2 테이블에도 삽입해주는 트리거
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE TYPE = 'TR' AND NAME ='TRG_02_1')
DROP TRIGGER TRG_02_1
-- 1번째 방법 (비추 : 컬럼수가 늘어나거나 여러번 삽입시)
CREATE TRIGGER TRG_02_1
ON T1
FOR INSERT
AS
DECLARE @NUM INT, @NAME VARCHAR(10)
SET @NUM = (SELECT NUM FROM INSERTED)
SET @NAME = (SELECT NAME FROM INSERTED)
INSERT T2 VALUES (@NUM, @NAME)

-- ★ 2번째 방법(강추)
CREATE TRIGGER TRG_02_2
ON T1
FOR INSERT
AS
INSERT INTO T2 SELECT * FROM INSERTED

-- 삽입후 결과확인
INSERT T1 VALUES (12, 'AAA')

SELECT * FROM T1
SELECT * FROM T2



Trackbacks 0 | Comments 0
DML 실습 예제 2 보러 가기

-- ★ SQL 종합 문제 (DML)

--1> 부서테이블의 모든 데이터를 출력하라.
SELECT * FROM EMP

--2> EMP테이블에서 각 사원의 직업, 사원번호, 이름, 입사일을 출력하라.
SELECT JOB, EMPNO, ENAME, HIREDATE FROM EMP

--3> EMP테이블에서 직업을 출력하되, 각 항목(ROW)가 중복되지 않게 출력하라.
SELECT DISTINCT JOB FROM EMP

--4> 급여가 2850 이상인 사원의 이름 및 급여를 표시하는 출력하라.
SELECT ENAME, SAL FROM EMP WHERE SAL >= 2850

--5> 사원번호가 7566인 사원의 이름 및 부서번호를 표시하는 출력하라.
SELECT ENAME, DEPTNO FROM EMP WHERE EMPNO = '7566'

--6> 급여가 1500이상 ~ 2850이하의 범위에 속하지 않는 모든 사원의 이름 및 급여를 출력하라.
SELECT ENAME, SAL FROM EMP WHERE SAL NOT BETWEEN 1500 AND 2850
-- SELECT ENAME, SAL FROM EMP WHERE SAL < 1500 OR SAL > 2850

--7> 1981년 2월 20일 ~ 1981년 5월 1일에 입사한 사원의 이름,직업 및 입사일을 출력하라.
--    입사일을 기준으로 해서 오름차순으로 정렬하라.
SELECT ENAME, JOB, HIREDATE FROM EMP
WHERE HIREDATE BETWEEN '1981-02-20' AND '1981-05-01'
ORDER BY HIREDATE ASC

--8> 10번 및 30번 부서에 속하는 모든 사원의 이름과 부서 번호를 출력하되,
--     이름을 알파벳순으로 정렬하여 출력하라.
SELECT ENAME, DEPTNO FROM EMP WHERE DEPTNO IN(10, 30) ORDER BY ENAME ASC

--9> 10번 및 30번 부서에 속하는 모든 사원 중 급여가 1500을 넘는 사원이름 및 급여를 출력하라.
--(단 컬럼명을 각각 employee 및 Monthly Salary로 지정하시오)
SELECT ENAME AS 'employee', SAL AS 'Monthly Salary' FROM EMP WHERE DEPTNO IN(10, 30) AND SAL >= 1500

--10> 관리자가 없는 모든 사원의 이름 및 직위를 출력하라.
SELECT ENAME, JOB FROM EMP WHERE MGR IS NULL

--11> 커미션을 받는 모든 사원의 이름, 급여 및 커미션을 출력하되,
--      급여를 기준으로 내림차순으로 정렬하여 출력하라.
SELECT ENAME, SAL, COMM FROM EMP WHERE COMM != 0 AND COMM IS NOT NULL ORDER BY SAL DESC

--12> 이름의 세 번째 문자가 A인 모든 사원의 이름을 출력하라.
SELECT ENAME FROM EMP WHERE ENAME LIKE '__A%'

--13> 이름에 L이 두 번 들어가며 부서 30에 속해있는 사원의 이름을 출력하라.
SELECT DEPTNO, ENAME FROM EMP WHERE ENAME LIKE '%L%L%' AND DEPTNO = 30

--14> 직업이 Clerk 또는 Analyst 이면서 급여가 1000,3000,5000 이 아닌 모든 사원의 이름, 직업 및 급여를 출력하라.
SELECT ENAME, JOB, SAL FROM EMP WHERE JOB IN('CLERK','ANALYST') AND SAL NOT IN(1000, 3000, 5000)

--15> 사원번호, 이름, 급여 그리고 15%인상된 급여를 정수로 표시하되 컬럼명을 New Salary로 지정하여 출력하라.
SELECT EMPNO, ENAME, SAL, SAL+CAST(SAL*0.15 AS INT) AS 'New Salary' FROM EMP
--SELECT EMPNO, ENAME, SAL, SAL+CONVERT(INT, SAL*0.15) AS 'New Salary' FROM EMP

--16> 15번 문제와 동일한 데이타에서 급여 인상분(새 급여에서
--      이전 급여를 뺀 값)을 추가해서 출력하라.(컬럼명은 Increase로 하라).
SELECT EMPNO, ENAME, SAL, SAL+CONVERT(INT, SAL*0.15) AS 'New Salary',
(SAL+CONVERT(INT, SAL*0.15)) - SAL AS 'Increase' FROM EMP

--17> 각 사원의 이름을 표시하고 근무 달 수를 계산하여 컬럼명을 Months_Works로 지정하고,
--      근무 달수를 기준으로 오래된 사람부터 정렬하여 출력하라.
SELECT ENAME, DATEDIFF(MM, HIREDATE, GETDATE()) AS 'Months_Works' FROM EMP
ORDER BY 'Months_Works' DESC

--18> 이름이 J,A 또는 M으로 시작하는 모든 사원의 이름(첫 글자는 대문자로, 나머지 글자는
--소문자로 표시) 및 이름 길이를 표시하는 쿼리를 작성하고 컬럼 별칭은 적당히 넣어서 출력하라.
SELECT UPPER(SUBSTRING(ENAME, 1 ,1)) + LOWER(SUBSTRING(ENAME, 2, 8)) AS '사원이름',
LEN(ENAME) AS '이름길이'
FROM EMP WHERE ENAME LIKE '[J,A,M]%'

--19> 사원의 이름과 커미션을 출력하되, 커미션이 책정되지 않은 사원의 커미션은 'no commission'으로 출력하라.
SELECT ENAME, ISNULL(CONVERT(VARCHAR(20),COMM), 'no commission') AS 'COMM' FROM EMP

--20> 모든 사원의 이름,부서번호,부서이름을 표시하는 질의를 작성하라.
SELECT ENAME, EMPNO, DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO
-- SELECT ENAME, EMPNO, (SELECT DNAME FROM DEPT WHERE DEPTNO = EMP.DEPTNO) AS 'DNAME' FROM EMP

--21> 30번 부서에 속한 사원의 이름과 부서번호 그리고 부서이름을 출력하라.
SELECT ENAME, E.DEPTNO, DNAME FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO WHERE E.DEPTNO IN(30)
--SELECT ENAME, EMP.DEPTNO, DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.DEPTNO = '30'

--22> 30번 부서에 속한 사원들의 모든 직업과 부서위치를 출력하라.
--(단, 직업 목록이 중복되지 않게 하라.)
SELECT JOB, MAX(LOC) AS 'LOC' FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.DEPTNO = 30 GROUP BY JOB
--SELECT DISTINCT E.JOB, D.LOC FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.DEPTNO = 30

--23> 커미션이 책정되어 있는 모든 사원의 이름, 부서이름 및 위치를 출력하라.
SELECT E.ENAME, D.DNAME, D.LOC FROM EMP E LEFT OUTER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO WHERE E.COMM IS NOT NULL

--24> 이름에 A가 들어가는 모든 사원의 이름과 부서 이름을 출력하라.
SELECT E.ENAME, D.DNAME FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO AND E.ENAME LIKE '%A%'

--25> Dallas에서 근무하는 모든 사원의 이름, 직업, 부서번호 및 부서이름을 출력하라.
SELECT E.ENAME, E.JOB, E.DEPTNO, D.DNAME FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO AND D.LOC = 'DALLAS'

--26> 사원이름 및 사원번호, 해당 관리자이름 및 관리자 번호를 출력하되,
--      각 컬럼명을 employee,emp#,manager,mgr#으로 표시하여 출력하라.
SELECT E1.ENAME AS 'employee', E1.EMPNO AS 'emp#', EM.ENAME AS 'manager', EM.EMPNO AS 'mgr#'
FROM EMP E1, EMP EM WHERE E1.MGR = EM.EMPNO

--27> 모든 사원의 이름,직업,부서이름,급여 및 등급을 출력하라.
SELECT E.ENAME, E.JOB, D.DNAME, E.SAL, S.GRADE FROM EMP E, DEPT D, SALGRADE S
WHERE E.DEPTNO = D.DEPTNO AND E.SAL BETWEEN S.LOSAL AND S.HISAL

--28> Smith보다 늦게 입사한 사원의 이름 및 입사일을 출력하라.
SELECT ENAME, HIREDATE FROM EMP
WHERE HIREDATE > (SELECT HIREDATE FROM EMP WHERE ENAME = 'SMITH')
ORDER BY HIREDATE ASC

--29> 자신의 관리자보다 먼저 입사한 모든 사원의 이름, 입사일, 관리자의 이름, 관리자의
--      입사일을 출력하되 각각 컬럼명을 Employee,EmpHiredate, Manager,MgrHiredate로 표시하여 출력하라.
SELECT E1.ENAME AS 'EMPLOYEE', E1.HIREDATE AS 'EMPHIREDATE',
EM.ENAME AS 'MANAGER', EM.HIREDATE AS 'MGRHIREDATE'
FROM EMP E1, EMP EM WHERE E1.MGR = EM.EMPNO AND E1.HIREDATE < EM.HIREDATE

--30> 모든 사원의 급여 최고액,최저액,총액 및 평균액을 출력하되 각 컬럼명을
--      Maximum, Minimum, Sum, Average로 지정하여 출력하라.
SELECT MAX(SAL) AS 'MAXIMUM', MIN(SAL) AS 'MINIMUM', SUM(SAL) AS 'SUM', AVG(SAL) AS 'AVERAGE'  FROM EMP

--31> 각 직업별로 급여 최저액.최고액,총액 및 평균액을 출력하라.
SELECT JOB, MIN(SAL) AS '최저액', MAX(SAL) AS '최고액', SUM(SAL) AS '총액', AVG(SAL) AS '평균액'
FROM EMP GROUP BY JOB

--32> 직업이 동일한 사람 수를 직업과 같이 출력하라.
SELECT JOB + ' / ' + CONVERT(VARCHAR(2), COUNT(*)) AS '직업 / 사람수' FROM EMP GROUP BY JOB

--33> 관리자의 수를 출력하되, 관리자 번호가 중복되지 않게하라.
--      그리고, 컬럼명을 Number of Manager로 지정하여 출력하라.
SELECT COUNT(DISTINCT ISNULL(MGR, 0)) AS 'NUMBER OF MANAGER' FROM EMP

--34> 최고 급여와 최저 급여의 차액을 출력하라.
SELECT MAX(SAL) - MIN(SAL) AS '차액' FROM EMP

--35> 관리자 번호 및 해당 관리자에 속한 사원들의 최저 급여를 출력하라.
--      단, 관리자가 없는 사원 및 최저 급여가 1000 미만인 그룹은 제외시키고
--      급여를 기준으로 출력 결과를 내림차순으로 정렬하라.
SELECT MGR, MIN(DISTINCT SAL) AS '최저급여' FROM EMP
WHERE MGR IS NOT NULL GROUP BY MGR HAVING MIN(SAL) > 1000
ORDER BY MIN(SAL)

--36> 부서별로 부서이름, 부서위치, 사원 수 및 평균 급여를 출력하라.
--       그리고 각각의 컬럼명을 부서명,위치,사원의 수,평균급여로 표시하라.
SELECT MAX(DNAME) AS '부서명', MAX(LOC) AS '위치', COUNT(*) AS '사원수', AVG(SAL) AS '평균급여'
FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO GROUP BY D.DEPTNO

--37> Smith와 동일한 부서에 속한 모든 사원의 이름 및 입사일을 출력하라. 단, Smith는 제외하고 출력하시오
SELECT ENAME, HIREDATE FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = 'SMITH') AND ENAME !='SMITH'

--38> 자신의 급여가 평균 급여보다 많은 모든 사원의 사원 번호, 이름, 급여를 표시하는
--      질의를 작성하고 급여를 기준으로 결과를 내림차순으로 정렬하라.
SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL > (SELECT AVG(SAL) FROM EMP) ORDER BY SAL DESC

-- 39> 이름에 T가 들어가는 사원의 속한 부서에서 근무하는 모든 사원의 사원번호 및 이름을 출력하라.
SELECT EMPNO, ENAME FROM EMP WHERE DEPTNO IN(SELECT DISTINCT DEPTNO FROM EMP WHERE ENAME LIKE '%T%')

--40> 부서위치가 Dallas인 모든 사원의 이름,부서번호 및 직위를 출력하라.
SELECT ENAME, E.DEPTNO, JOB FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO WHERE LOC = 'DALLAS'

--41> KING에게 보고하는 모든 사원의 이름과 급여를 출력하라.
SELECT ENAME, SAL FROM EMP WHERE MGR = (SELECT EMPNO FROM EMP WHERE ENAME = 'KING')

--42> Sales 부서의 모든 사원에 대한 부서번호, 이름 및 직위를 출력하라.
SELECT DEPTNO, ENAME, JOB FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES')

--43> 자신의 급여가 평균 급여보다 많고 이름에 T가 들어가는 사원과 동일한 부서에
--      근무하는 모든 사원의 사원 번호, 이름 및 급여를 출력하라.

SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL > (SELECT AVG(SAL) FROM EMP)
AND DEPTNO IN (SELECT DISTINCT DEPTNO FROM EMP WHERE ENAME LIKE '%T%')

--44> 커미션을 받는 사원과 급여가 일치하는 사원의 이름,부서번호,급여를 출력하라.
SELECT ENAME, DEPTNO, SAL FROM EMP WHERE SAL IN (SELECT DISTINCT SAL FROM EMP WHERE COMM IS NOT NULL)

--45> Dallas에서 근무하는 사원과 직업이 일치하는 사원의 이름,부서이름, 및 급여를 출력하시오
SELECT ENAME, DNAME, SAL FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE JOB IN (SELECT DISTINCT JOB FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO WHERE LOC = 'DALLAS')

--46> Scott과 동일한 급여 및 커미션을 받는 모든 사원의 이름, 입사일 및 급여를 출력하시오
SELECT ENAME, HIREDATE, SAL, COMM FROM EMP
WHERE SAL = (SELECT SAL FROM EMP WHERE ENAME = 'SCOTT')
AND ISNULL(COMM,0) = (SELECT ISNULL(COMM,0) FROM EMP WHERE ENAME = 'SCOTT')
-- AND ENAME != 'SCOTT'

--47> 직업이 Clerk 인 사원들보다 더 많은 급여를 받는 사원의 사원번호, 이름, 급여를 출력하되,
--      결과를 급여가 높은 순으로 정렬하라.

SELECT EMPNO, ENAME, SAL, JOB FROM EMP
WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE JOB = 'CLERK') ORDER BY SAL DESC
 
--48> 이름에 A가 들어가는 사원과 같은 직업을 가진 사원의 이름과 월급, 부서번호를 출력하라.
SELECT ENAME, SAL, DEPTNO FROM EMP WHERE JOB IN (SELECT DISTINCT JOB FROM EMP WHERE ENAME LIKE '%A%')

--49> New  York 에서 근무하는 사원과 급여 및 커미션이 같은 사원의 사원이름과 부서명을 출력하라.
SELECT E.ENAME, D.DNAME FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE SAL IN (SELECT DISTINCT E.SAL FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO WHERE D.LOC = 'NEW YORK')
AND ISNULL(COMM, 0) IN (SELECT DISTINCT ISNULL(E.COMM, 0) FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO WHERE D.LOC = 'NEW YORK')

--50> Dallas에서 근무하는 사원과 직업 및 관리자가 같은 사원의 사원번호,사원이름,
--      직업,월급,부서명,커미션을 출력하되 커미션이 책정되지 않은 사원은 NoCommission으로 표시하고,
--      커미션의 컬럼명은 Comm으로 나오게 출력하시오. (단, 최고월급부터 출력되게 하시오)
SELECT E.EMPNO, E.ENAME, E.JOB, E.SAL, D.DNAME,
ISNULL(CONVERT(VARCHAR(20), E.COMM), 'NoCommission') AS 'Comm'
FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE JOB IN (SELECT DISTINCT E.JOB FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO WHERE D.LOC = 'DALLAS')
AND MGR IN (SELECT DISTINCT E.MGR FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO WHERE D.LOC = 'DALLAS')
ORDER BY E.SAL DESC

< 위 예제를 실습하려면 아래의 DB를 만들어야 합니다!! >
 

Trackbacks 0 | Comments 0
DML 실습 예제 1 보러가기

-- ★ 연습문제 3 (DML)

--1. 사원테이블에서 부서별 최대 월급을 출력하라.
SELECT (SELECT DNAME FROM DEPT WHERE DEPTNO=E.DEPTNO) AS '부서명',
MAX(SAL) AS '최대월급' FROM EMP E GROUP BY DEPTNO

--2. 사원테이블에서 직위별 최소 월급을 구하되 직위가 CLERK인 것만 출력하라.
SELECT JOB, MIN(SAL) FROM EMP WHERE JOB = 'CLERK' GROUP BY JOB

--3. 커미션이 책정된 사원은 모두 몇 명인가?
SELECT COUNT(*) FROM EMP WHERE COMM IS NOT NULL AND COMM != '0'

--4. 직위가 'SALESMAN'이고 월급이 1000이상인 사원의 이름과 월급을 출력하라.
SELECT ENAME, SAL FROM EMP WHERE JOB = 'SALESMAN' AND SAL >= 1000

--5. 부서별 평균월급을 출력하되, 평균월급이 2000보다
-- 큰 부서의 부서번호와 평균월급을 출력하라.
SELECT DEPTNO, AVG(SAL) AS '평균월급' FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) >= 2000

--6. 사원테이블에서 커미션을 가장 많이 받는 사원 2명을
-- 출력하되 랭킹이 중복될 경우 동률처리를 하여 출력하라.

SELECT TOP 2 WITH TIES ENAME, COMM FROM EMP ORDER BY COMM DESC

--7. 직위가 MANAGER인 사원을 뽑는데 월급이 높은 사람
-- 순으로 이름, 직위, 월급을 출력하라.
SELECT ENAME, JOB, SAL FROM EMP WHERE JOB = 'MANAGER' ORDER BY SAL DESC

--8. 각 직위별로 총월급을 출력하되 월급이 낮은 순으로 출력하라.
SELECT JOB, SUM(SAL) AS '총 월급' FROM EMP GROUP BY JOB ORDER BY '총 월급' ASC

--9. 직위별 총월급을 출력하되, 직위가 'MANAGER'인 사원들은 제외하라.
-- 그리고 그 총월급이 5000보다 큰 직위와 총월급만 출력하라.
SELECT JOB, SUM(SAL) AS '총 월급' FROM EMP
GROUP BY JOB HAVING JOB != 'MANAGER' AND SUM(SAL) >= 5000
ORDER BY '총 월급' ASC

--10. 직위별 최대월급을 출력하되, 직위가 'CLERK'인 사원들은 제외하라.
-- 그리고 그 최대월급이 2000 이상인 직위와 최대월급을 최대 월급이
-- 높은 순으로 정렬하여 출력하라.
SELECT JOB, MAX(SAL) AS '최대월급' FROM EMP
GROUP BY JOB HAVING JOB !='CLERK' AND MAX(SAL) >= 2000
ORDER BY '최대월급' DESC

--11. COMPUTE BY 구문을 이용하여 직위별 사원의 이름과
-- 월급, 그리고 직위별 월급의 합을 구하라.
SELECT JOB, ENAME, SAL FROM EMP ORDER BY JOB COMPUTE SUM(SAL) BY JOB

--12. 위 문제를 WITH ROLLUP 구문을 이용하여 집계하여라.
SELECT JOB, (CASE WHEN ENAME IS NULL THEN '[합계]' ELSE ENAME END) AS ENAME,
SUM(SAL) AS SAL FROM EMP GROUP BY JOB, ENAME WITH ROLLUP


-- ★ 연습문제 (JOIN)

-- 1. 사원들의 이름, 부서번호, 부서이름을 출력하라.
SELECT ENAME, EMP.DEPTNO, DNAME
-- FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO
FROM EMP INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO
ORDER BY DEPT.DNAME ASC

-- 2. DALLAS에서 근무하는 사원의 이름, 직위, 부서번호, 부서이름을 출력하라.
SELECT ENAME, JOB, EMP.DEPTNO, DNAME, LOC FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO WHERE DEPT.LOC = 'DALLAS'

-- 3. 이름에 'A'가 들어가는 사원들의 이름과 부서이름을 출력하라.
SELECT ENAME, DNAME FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO WHERE ENAME LIKE '%A%'

-- 4. 사원이름과 그 사원이 속한 부서의 부서명, 그리고 월급을
--출력하는데 월급이 3000이상인 사원을 출력하라.
SELECT ENAME, DNAME, SAL FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO WHERE SAL >= 3000

-- 5. 직위가 'SALESMAN'인 사원들의 직위와 그 사원이름, 그리고
-- 그 사원이 속한 부서 이름을 출력하라.
SELECT JOB, ENAME, DNAME FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO WHERE JOB = 'SALESMAN'

-- 6. 커미션이 책정된 사원들의 사원번호, 이름, 연봉, 연봉+커미션,
-- 급여등급을 출력하되, 각각의 컬럼명을 '사원번호', '사원이름',
-- '연봉','실급여', '급여등급'으로 하여 출력하라.
SELECT EMPNO AS '사원번호', ENAME AS '이름', (SAL*12) AS '연봉',
((SAL*12)+ISNULL(COMM, 0)) AS '실급여', S.GRADE AS '급여등급'
FROM EMP E,SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL

-- 7. 부서번호가 10번인 사원들의 부서번호, 부서이름, 사원이름,
-- 월급, 급여등급을 출력하라.
SELECT E.DEPTNO AS '부서번호', D.DNAME AS '부서이름', ENAME AS '사원이름',
SAL AS '월급', S.GRADE AS '급여등급'
FROM EMP E, DEPT D, SALGRADE S WHERE E.DEPTNO = D.DEPTNO
AND E.SAL BETWEEN S.LOSAL AND S.HISAL AND E.DEPTNO = 10

-- 8. 부서번호가 10번, 20번인 사원들의 부서번호, 부서이름, 사원이름,
-- 월급, 급여등급을 출력하라. 그리고 그 출력된 결과물을
-- 부서번호가 낮은 순으로, 월급이 높은 순으로 정렬하라.
SELECT E.DEPTNO AS '부서번호', D.DNAME AS '부서이름',
E.ENAME AS '사원이름', E.SAL AS '월급', S.GRADE AS '급여등급'
FROM EMP E, DEPT D, SALGRADE S WHERE E.DEPTNO = D.DEPTNO
AND E.SAL BETWEEN S.LOSAL AND S.HISAL AND E.DEPTNO IN (10, 20)
ORDER BY E.DEPTNO ASC, E.SAL DESC

-- 9. 사원번호와 사원이름, 그리고 그 사원을 관리하는 관리자의
-- 사원번호와 사원이름을 출력하되 각각의 컬럼명을 '사원번호',
-- '사원이름', '관리자번호', '관리자이름'으로 하여 출력하라.
SELECT E1.EMPNO AS '사원번호', E1.ENAME AS '사원이름',
EM.EMPNO AS '관리자번호', EM.ENAME AS '관리자이름'
FROM EMP E1, EMP EM WHERE E1.MGR = EM.EMPNO

< 위 예제를 실습하려면 아래의 DB를 만들어야 합니다!! >
 

Trackbacks 0 | Comments 3

-- ★ 사용 테이블
-- 1) EMP TABLE: 사원 테이블
--EMPNO : 사원번호
--ENAME : 사원이름
--JOB : 직위(직업)
--MGR : 관리자
--HIREDATE : 입사일
--SAL : 봉급(월급)
--COMM : 커미션
--DEPTNO : 부서번호

-- 2) DEPT TABLE: 부서 테이블
--DEPTNO : 부서번호
--DNAME : 부서명
--LOC : 부서위치


-- ★ 연습문제 1 (DML)
--1. 사원테이블에서 모든 데이터를 출력하라

SELECT * FROM EMP

--2. 사원테이블에서 사원번호, 사원이름, 월급을 출력하라
SELECT EMPNO, ENAME, SAL FROM EMP

--3. 사원테이블에서 월급을 뽑는데 중복된 데이터가 없게 출력하라
SELECT DISTINCT SAL FROM EMP

--4. 사원테이블에서 사원이름과 월급을 출력하는데 각각의 컬럼명을
-- '이 름','월 급'으로 바꿔서 출력하라. 단, ALIAS에 공백추가
SELECT ENAME AS '이 름', SAL AS '월 급' FROM EMP

--5. 사원테이블에서 사원이름, 월급을 뽑고, 월급과 커미션을  더한 값을
-- 출력하는데 컬럼명을 '실급여'이라고 해서 출력하라.
-- 단, NULL값은 나타나지 않게 하라.
SELECT ENAME, SAL, (SAL + ISNULL(COMM, 0)) AS '실급여' FROM EMP

--6. 사원테이블에서 'SCOTT'이라는 사원의 사원번호, 이름, 월급을 출력하라
SELECT EMPNO, ENAME, SAL FROM EMP WHERE ENAME = 'SCOTT'

--7. 사원테이블에서 직위가 'SALESMAN'인 사원의 사원번호, 이름, 직위를 출력하라
SELECT EMPNO, ENAME, JOB FROM EMP WHERE JOB = 'SALESMAN'

--8. 사원테이블에서 사원번호가 7499, 7521, 7654인 사원의 사원번호, 이름, 월급을 출력하라
SELECT EMPNO, ENAME, SAL FROM EMP WHERE EMPNO IN(7499, 7521, 7654)

--9. 사원테이블에서 월급이 1500에서 3000사이인 사원의 사원번호, 이름, 월급을 출력하라.
SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL BETWEEN 1500 AND 3000
-- SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL >= 1500 AND SAL <= 3000

--10. 사원테이블에서 이름의 첫글자가 A이고 마지막 글자가 N이 아닌 사원의 이름을 출력하라
SELECT ENAME FROM EMP WHERE ENAME LIKE 'A%[^N]'
-- SELECT ENAME FROM EMP WHERE ENAME LIKE 'A%' AND ENAME LIKE '%[^N]'


-- ★ 연습문제 2 (DML)
--1. 사원 테이블에서 사원이름을 첫글자는 대문자로, 나머지는 소문자로 출력하라
SELECT UPPER(SUBSTRING(ENAME,1,1))+LOWER(SUBSTRING(ENAME,2,8)) FROM EMP

--2. 사원테이블에서 사원이름을 뽑고 또 이름의 두번째 글자부터 네번째 글자까지 출력하라.
SELECT ENAME, SUBSTRING(ENAME, 2, 4) AS 'NAME 2~4' FROM EMP

--3. 사원테이블의 사원 이름의 철자 개수를 출력하라.
SELECT ENAME, LEN(ENAME) AS '철자개수' FROM EMP

--4. 사원테이블에서 사원 이름의 앞 글자 하나와 마지막 글자 하나만 출력하되
-- 모두 소문자로 각각 출력하라.
SELECT LOWER(LEFT(ENAME, 1)) AS 'FIRST ONE', LOWER(RIGHT(ENAME,1)) AS 'LAST ONE' FROM EMP

--5. 3456.78의 소수점 첫번째 자리에서 반올림하라.
SELECT ROUND(3456.78, 0) AS '소수 첫째자리 반올림'

--6. 3의 4제곱을 구하고, 64의 제곱근을 구하라.
SELECT POWER(3, 4) AS '3의 4제곱', SQRT(64) AS '64의 제곱근'

--7. 오늘날짜와 오늘날짜에서 10일을 더한 날짜를 출력하라.
SELECT GETDATE() AS '오늘날짜', DATEADD(DD, 10, GETDATE()) AS '오늘+10일'

--8. 국제 표준으로 현재 날짜를 출력하라.
SELECT CONVERT(VARCHAR(10),GETDATE(),112) AS '국제 표준날짜', GETDATE() AS '현재 날짜'
-- GETUTCDATE() : 국제 표준시

--9. 사원테이블에서 사원이름과 사원들의 오늘 날짜까지의 근무일수를 구하라.
SELECT ENAME AS '사원이름', HIREDATE, DATEDIFF(DD, HIREDATE, GETDATE()) AS '근무일수' FROM EMP

--10. 위 문제에서 근무일수를 00년 00개월 00일 근무하였는지
--확인할 수 있도록 변환하라.(단, 한 달을 30일로 계산하라)
-- 예)
--  | ENAME | 근무일수  |
--  | KING  | 00년 00개월 00일 |
SELECT ENAME, 
(CONVERT(CHAR(4), DATEDIFF(DD, HIREDATE, GETDATE())/365)+'년 ' +
CONVERT(CHAR(2), DATEDIFF(DD, HIREDATE, GETDATE())%365/30)+'개월 ' +
CONVERT(CHAR(2), DATEDIFF(DD, HIREDATE, GETDATE())%365%30)+'일')
AS '근무일수'
FROM EMP

< 위 예제를 실습하려면 아래의 DB를 만들어야 합니다!! >
 

Trackbacks 0 | Comments 1

-- ★ 사용 테이블
-- 1) EMP TABLE: 사원 테이블
--EMPNO : 사원번호,
--ENAME : 사원이름
--JOB : 직위(직업)
--MGR : 관리자
--HIREDATE : 입사일
--SAL : 봉급(월급)
--COMM : 커미션
--DEPTNO : 부서번호

-- 2) DEPT TABLE: 부서 테이블
--DEPTNO : 부서번호
--DNAME : 부서명
--LOC : 부서위치

--1. 30번 부서 사원들의 직위, 이름, 월급을 담는 VIEW를 만들어라.
CREATE VIEW EX_01
AS
SELECT JOB, ENAME, SAL FROM EMP WHERE DEPTNO = 30 WITH CHECK OPTION

SELECT * FROM EX_01

--2. 30번 부서 사원들의  직위, 이름, 월급을 담는 VIEW를 만드는데,
-- 각각의 컬럼명을 직위, 사원이름, 월급으로 ALIAS를 주고 월급이
-- 300보다 많은 사원들만 추출하도록 하라.
CREATE VIEW EX_02
AS
SELECT JOB AS [직위], ENAME AS [사원이름], SAL AS [월급]
FROM EMP WHERE DEPTNO = 30 AND SAL > 300

SELECT * FROM EX_02

--3. 부서별 최대월급, 최소월급, 평균월급을 담는 VIEW를 만들어라.
CREATE VIEW EX_03
AS
SELECT (SELECT DNAME FROM DEPT WHERE DEPTNO = E.DEPTNO) AS [부서명],
MAX(SAL) AS [최대월급], MIN(SAL) AS [최소월급], AVG(SAL) AS [평균월급]
FROM EMP E GROUP BY DEPTNO

SELECT * FROM EX_03

--4. 부서별 평균월급을 담는 VIEW를 만들되, 평균월급이 2000 이상인
-- 부서만 출력하도록 하라.
CREATE VIEW EX_04
AS
SELECT (SELECT DNAME FROM DEPT WHERE DEPTNO = E.DEPTNO) AS [부서명], AVG(SAL) AS [평균월급]
FROM EMP E GROUP BY DEPTNO HAVING AVG(SAL) >= 2000

SELECT * FROM EX_04

--5. 직위별 총월급을 담는 VIEW를 만들되, 직위가 MANAGER인
-- 사원들은 제외하고 총월급이 3000이상인 직위만 출력하도록 하라.
CREATE VIEW EX_05
AS
SELECT JOB, SUM(SAL) AS [총월급] FROM EMP
WHERE JOB NOT IN ('MANAGER') GROUP BY JOB HAVING SUM(SAL) >= 3000
-- (또는) GROUP BY JOB HAVING JOB != 'MANAGER' AND SUM(SAL) >= 3000

SELECT * FROM EX_05

--6. 30번 부서 사원의 부서번호, 사원번호, 사원이름, 월급을 담는
-- VIEW를 작성하되 다른 부서에서 근무하는 사원들의 레코드는
-- 입력 및 수정하지 못하도록  WITH CHECK OPTION을 사용하라.
CREATE VIEW EX_06
AS
SELECT DEPTNO, EMPNO, ENAME, SAL
FROM EMP WHERE DEPTNO = 30 WITH CHECK OPTION

-- 부서번호가 20이고 이름이 SMITH 인 사람의 이름을 메롱으로 바꾸려 하면 0행 적용
UPDATE EX_06 SET ENAME = '메롱' WHERE DEPTNO = 20 AND ENAME='SMITH'

--7. 20번 부서에서 근무하는 사원의 이름, 월급, 커미션, 부서번호를
-- 담는 VIEW를 작성하되, 그 누구도 VIEW의 정의를 보지 못하도록 하라.
CREATE VIEW EX_07 WITH ENCRYPTION
AS
SELECT DEPTNO, ENAME, SAL, COMM  FROM EMP WHERE DEPTNO=20

-- EX_07의 정보는 볼 수 있으나
EXEC SP_HELP EX_07
-- EX_07의 정의는 볼 수 없다
EXEC SP_HELPTEXT EX_07

--8. 7번 문제에서 만든 VIEW를 20번 부서에서 근무하는 사원의 이름과
-- 월급만 담는 VIEW가 되도록 수정하고 다시 암호화하여라.
ALTER VIEW EX_07 WITH ENCRYPTION
AS
SELECT DEPTNO, ENAME, SAL FROM EMP WHERE DEPTNO=20

EXEC SP_HELP EX_07
EXEC SP_HELPTEXT EX_07

< 위 예제를 실습하려면 아래의 DB를 만들어야 합니다!! >
 
Trackbacks 0 | Comments 0

-- 01. UNIQUE 제약조건 테스트
CREATE TABLE EMP06
(
 EMPNO INT CONSTRAINT UK_EMP06_EMPNO UNIQUE,
 ENAME VARCHAR(20)
)

EXEC SP_HELPCONSTRAINT EMP06

INSERT INTO EMP06(EMPNO, ENAME) VALUES(111,'이하나')
INSERT INTO EMP06(EMPNO, ENAME) VALUES(112, '차두리')
INSERT INTO EMP06(EMPNO, ENAME) VALUES(NULL, '김삼')
-- UNIQUE 제약조건에 위배
INSERT INTO EMP06(EMPNO, ENAME) VALUES(NULL, '김삼')


-- 02. UNIQUE 제약조건 테스트 2
CREATE TABLE USER04
(
 U_ID INT CONSTRAINT PK_USER04_U_ID PRIMARY KEY,
 U_NAME VARCHAR(20) NOT NULL,
 REG_NUM1 CHAR(6) NOT NULL CONSTRAINT UK_USER04_REG_NUM1 UNIQUE,
 REG_NUM2 CHAR(7) NOT NULL CONSTRAINT UK_USER04_REG_NUM2 UNIQUE,
 U_JOB VARCHAR(20) CONSTRAINT DF_USER04_U_JOB DEFAULT '무직'
)

EXEC SP_HELPCONSTRAINT USER04


-- 03. CHECK 제약조건 테스트
CREATE TABLE EMP07
(
 EMPNO INT CONSTRAINT PK_EMP07_EMPNO PRIMARY KEY,
 ENAME VARCHAR(20) NOT NULL,
 GENDER CHAR(2) CONSTRAINT CK_EMP07_GENDER CHECK(GENDER IN('남','여'))
)

EXEC SP_HELPCONSTRAINT EMP07

INSERT INTO EMP07(EMPNO, ENAME, GENDER) VALUES(111,'신사임당', '여')
INSERT INTO EMP07(EMPNO, ENAME, GENDER) VALUES(112, '이율곡', '남')
-- CHECK 제약에 위배
INSERT INTO EMP07(EMPNO, ENAME, GENDER) VALUES(113, '하리수', '왓')


-- 04. CHECK 제약조건 테스트 2
CREATE TABLE EMP08
(
 EMPNO INT CONSTRAINT PK_EMP08_EMPNO PRIMARY KEY,
 ENAME VARCHAR(20) NOT NULL,
 DEPTNO INT NOT NULL
)
EXEC SP_HELPCONSTRAINT EMP08

INSERT INTO EMP08 VALUES(111,'유명한',10)
INSERT INTO EMP08 VALUES(112,'강인해',20)
INSERT INTO EMP08 VALUES(113,'허무한',30)

-- 1) DEPTNO 컬럼에 10, 20, 30, 40 만 들어갈 수 있도록 CHECK 제약조건을 걸어라
ALTER TABLE EMP08
ADD CONSTRAINT CK_EMP08_DEPTNO CHECK(DEPTNO IN(10,20,30,40))

-- 2) EMP 테이블에 걸려있는 CHECK 제약을 삭제하라.
ALTER TABLE EMP08
DROP CONSTRAINT CK_EMP08_DEPTNO

-- 3) EMP08 테이블에 EMPNO, ENAME, DEPTNO가 각각 114, ‘왕고집’, 50인 데이터를 삽입하라.
INSERT INTO EMP08 VALUES(114, '왕고집', 50)
SELECT * FROM EMP08

-- 4) 다시 2) 에서 설정한 CHECK 제약을 적용하라.
ALTER TABLE EMP08
ADD CONSTRAINT CK_EMP08_DEPTNO CHECK(DEPTNO IN(10,20,30,40))
-- DEPTNO 컬럼과 CHECK 제약 조건과의 충돌로 인한 에러 발생

-- 5) 아래와 같이 수정하라. (WITH NOCHECK : 체크 조건을 무시하고 제약조건 추가)
ALTER TABLE EMP08 WITH NOCHECK
ADD CONSTRAINT CK_EMP08_DEPTNO CHECK(DEPTNO IN(10,20,30,40))

-- 6) EMP08 테이블에 EMPNO, ENAME, DEPTNO가 각각 115, ‘똥고집’, 50인 데이터를 삽입하라.
INSERT INTO EMP08 VALUES(115, '똥고집', 50)  -- CHECK 제약 위배로 인한 에러발생

-- 7) NOCHECK 옵션으로 제약을 일시적으로 중지시킨 후 다시 위의 데이터를 삽입한다.
ALTER TABLE EMP08
NOCHECK CONSTRAINT CK_EMP08_DEPTNO

INSERT INTO EMP08 VALUES(115, '똥고집', 50)

SELECT * FROM EMP08

-- 8) NOCHECK 옵션으로 제약을 해제한 후 데이터 삽입
ALTER TABLE EMP08
CHECK CONSTRAINT CK_EMP08_DEPTNO
EXEC SP_HELPCONSTRAINT EMP08

-- CHECK 옵션이 다시 제대로 동작하는지 아래의 데이터를 삽입한다.
INSERT INTO EMP08 VALUES(116, '아까그놈', 50)
-- CHECK 제약 위배로 인한 에러발생
DELETE EMP08 WHERE EMPNO = 116

-- 제약조건 FOREIGN KEY, RULE, DEFAULT 도 테스트해 볼 것


-- 05. FOREIGN KEY 제약조건 테스트
CREATE TABLE 부서
(
 부서이름 VARCHAR(20) CONSTRAINT PK_DEPT_DNAME PRIMARY KEY,
 부서위치 CHAR(4)
)

-- 1) 부서 테이블에 자료 입력
INSERT INTO 부서 VALUES('기획', '5F')
INSERT INTO 부서 VALUES('마케팅', '3F')
INSERT INTO 부서 VALUES('영업', '3F')
INSERT INTO 부서 VALUES('재무관리', '1F')
INSERT INTO 부서 VALUES('전산', '2F')

CREATE TABLE 입사지원
(
 이름 VARCHAR(20),
 주민등록번호 CHAR(13),
 성별 CHAR(2),
 지원부서 VARCHAR(20) CONSTRAINT FK_RESUME_DNAME FOREIGN KEY
 REFERENCES 부서(부서이름)
)

EXEC SP_HELPCONSTRAINT 입사지원
EXEC SP_HELPCONSTRAINT 부서

-- 2) 입사지원 테이블에 자료 입력
INSERT INTO 입사지원 VALUES('홍길동','1212121313131','남','기획')
INSERT INTO 입사지원 VALUES('성춘향','1313131212121','여','전산')
-- 인사라는 부서가 없으므로 FK 제약조건에 걸림
INSERT INTO 입사지원 VALUES('왕고집','1212121313131','남','인사')

-- 3) 제약조건 삭제
-- PK 제약조건에 따라 참조하고 있는 FK 테이블이 있으므로 에러발생
ALTER TABLE 부서
DROP CONSTRAINT PK_DEPT_DNAME
-- 먼저 FK 테이블의 제약조건을 삭제하고
ALTER TABLE 입사지원
DROP CONSTRAINT FK_RESUME_DNAME
-- PK 테이블의 제약조건을 삭제할 수 있다
ALTER TABLE 부서
DROP CONSTRAINT PK_DEPT_DNAME


-- 06. RULE 제약조건 테스트
CREATE TABLE TEST01
(
 NUM1 INT CHECK(NUM1 BETWEEN 0 AND 20),
 NUM2 INT,
 NAME VARCHAR(20)
)
-- NUM1 컬럼의 CHECK 제약조건을 동일하게 NUM2 컬럼에 적용하고 싶다...
ALTER TABLE TEST01
ADD CONSTRAINT CK_TEST01_NUM2 CHECK(NUM2 BETWEEN 0 AND 20)
-- 근데 이런게 많아지면 번거롭다. 그래서...

CREATE TABLE TEST02
(
 NUM1 INT,
 NUM2 INT,
 NAME VARCHAR(20)
)
-- 1) 룰을 만든다
-- CREATE RULE RULE_NAME
-- AS VALUES

-- 0과 20 사이의 값만 들어가도록 하는 제약조건
CREATE RULE RULE01
AS @VALUE BETWEEN 0 AND 20

-- 2) 룰의 적용 : EXEC SP_BINDRULE 룰이름, '적용할테이블.컬럼'
EXEC SP_BINDRULE RULE01, 'TEST02.NUM1'
EXEC SP_BINDRULE RULE01, 'TEST02.NUM2'

-- 3) 룰의 적용여부 테스트
-- (1) RULE에 적합한 데이터 삽입시
INSERT INTO TEST02 VALUES(1, 20, '홍길동')
-- (2) RULE에 위배되는 데이터 삽입시
INSERT INTO TEST02 VALUES(21, 1, '성춘향')
INSERT INTO TEST02 VALUES(1, 21, '이몽룡')
INSERT INTO TEST02 VALUES(21, 21, '변사또')

-- 4) 룰의 삭제 : DROP RULE RULE_NAME
-- 아래의 쿼리로 RULE을 삭제해 보도록 한다.
DROP RULE RULE01
-- 바인딩이 되어 있어 삭제할 수 없다는 에러메시지가 출력된다.
-- (1) BINDING된 RULE을 UNBINDING한다.
EXEC SP_UNBINDRULE 'TEST02.NUM1'
EXEC SP_UNBINDRULE 'TEST02.NUM2'
-- (2) RULE의 삭제
DROP RULE RULE01

-- 5) 실습 예제
-- 이름에는 김씨와 이씨만 들어가게 하고,
-- 성별에는 남과 여만 들어가게 룰을 만들고 적용하라
CREATE TABLE TEST03
(
 이름 VARCHAR(20), 성별 CHAR(2)
)
-- (1) RULE의 생성
CREATE RULE NAME_RULE
AS @VALUE LIKE '%김%' OR @VALUE LIKE '%이%'

CREATE RULE GENDER_RULE
AS @VALUE LIKE '남' OR @VALUE LIKE '여'

-- (2) RULE의 BINDING
EXEC SP_BINDRULE NAME_RULE, 'TEST03.이름'
EXEC SP_BINDRULE GENDER_RULE, 'TEST03.성별'


-- 07. DEFAULT 제약조건 테스트
-- DEFAULT 생성 구문 :
-- CREATE DEFAULT RULE_NAME
-- AS VALUES
CREATE TABLE TEST04
(
 ENAME VARCHAR(20),
 HIREDATE DATETIME,
 JOB VARCHAR(20)
)
-- 1) DEFAULT의 생성
-- DFT01: ‘무명’
-- DFT02: GETDATE()
-- DFT03: ‘무직’
CREATE DEFAULT DFT01 AS '무명'
CREATE DEFAULT DFT02 AS GETDATE()
CREATE DEFAULT DFT03 AS '무직'

-- 2) DEFAULT의 적용 : NUM1, NUM2 컬럼
-- DFT01 : ENAME
-- DFT02 : HIREDATE
-- DFT03 : JOB
EXEC SP_BINDEFAULT DFT01, 'TEST04.ENAME'
EXEC SP_BINDEFAULT DFT02, 'TEST04.HIREDATE'
EXEC SP_BINDEFAULT DFT03, 'TEST04.JOB'

-- 3) DEFAULT 적용 확인
INSERT INTO TEST04(HIREDATE, JOB) VALUES('2002-03-04', '나무꾼')
INSERT INTO TEST04(ENAME, JOB) VALUES('돌쇠','짐꾼')
INSERT INTO TEST04(ENAME, HIREDATE) VALUES('마당쇠', '2002-04-04')

SELECT * FROM TEST04

-- 4) DEFAULT의 삭제
-- (1) UNBINDING
EXEC SP_UNBINDEFAULT 'TEST04.ENAME'
EXEC SP_UNBINDEFAULT 'TEST04.HIREDATE'
EXEC SP_UNBINDEFAULT 'TEST04.JOB'
-- (2) DROP
DROP DEFAULT DFT01
DROP DEFAULT DFT02
DROP DEFAULT DFT03

-- 5) 실습 예제
-- 이름, 직업, 월급을 가지는 테이블(TEST05)을 만들고 이름은 ‘그녀석’,
-- 직업은 ‘백수’, 월급은 0이 DEFAULT값이 되도록 쿼리문을 작성하여라.
CREATE TABLE TEST05
(
 이름 VARCHAR(20),
 직업 VARCHAR(20),
 월급 INT
)

CREATE DEFAULT DFT11 AS '그녀석'
CREATE DEFAULT DFT12 AS '백수'
CREATE DEFAULT DFT13 AS 0

EXEC SP_BINDEFAULT DFT11, 'TEST05.이름'
EXEC SP_BINDEFAULT DFT12, 'TEST05.직업'
EXEC SP_BINDEFAULT DFT13, 'TEST05.월급'

EXEC SP_HELPCONSTRAINT TEST05

Trackbacks 0 | Comments 0

-- 1. 출력용 매개변수(OUTPUT)과 RETURN의 차이점


-- 실습용 테이블 등록
USE ADV
CREATE TABLE T1
(
 NUM INT NOT NULL,
 NAME VARCHAR(10) NULL
)

-- ★ 1-1. 출력용 매개변수를 이용해서 적용값 받기
CREATE PROC UP_INSERT_T1
@NUM INT,
@NAME VARCHAR(10),
@RTNVAL INT OUTPUT
AS
DECLARE @EXIST INT
SET @EXIST = (SELECT COUNT(*) FROM T1 WHERE NUM = @NUM)
IF (@EXIST = 0)
 BEGIN
 INSERT T1 VALUES(@NUM, @NAME)
 END
ELSE
 BEGIN
  PRINT '같은 NUM 값이 존재합니다' RETURN
 END
SET @RTNVAL = @EXIST

DECLARE @RTNVAL INT
EXEC UP_INSERT_T1 2, '둘리', @RTNVAL OUTPUT
SELECT (CASE @RTNVAL WHEN 0 THEN '삽입완료' ELSE '삽입실패' END) AS [반환행]

INSERT T1 VALUES(1, '홍길동')
SELECT * FROM T1


-- ★ 1-2. RETURN을 사용해서 적용값 받기
CREATE PROC UP_INSERT_T1_RTN
@NUM INT,
@NAME VARCHAR(10)
AS
DECLARE @EXIST INT, @RTNVAL INT
SET @EXIST = (SELECT COUNT(*) FROM T1 WHERE NUM = @NUM)
SET @RTNVAL = @EXIST
RETURN @RTNVAL

DECLARE @RTNVAL INT
EXEC @RTNVAL = UP_INSERT_T1_RTN 1, '나나나'
SELECT @RTNVAL AS [반환행]

SELECT * FROM T1


-- 2. 매개변수에 DEFAULT 값 적용하기
-- 프로시저 실행시 아무것도 넣지 않고
-- 실행할 경우 IS_USER가 'Y' 인 값만 들고와라
-- 실행시 'N'을 넣으면 'N'인 값을 들고오고...
CREATE ALTER PROC UP_LIST_POSITION
@IS_USE CHAR(1) = NULL
AS
IF (@IS_USE IS NULL)
BEGIN
 SELECT * FROM T_ADV_POS
END
ELSE
BEGIN
 SELECT * FROM T_ADV_POS WHERE IS_USE = @IS_USE
END
------------------------------
-- 일반 사용자 일때는
EXEC UP_LIST_POSITION 'Y'
------------------------------
-- 관리자일 때는
EXEC UP_LIST_POSITION
EXEC UP_LIST_POSITION 'Y'
EXEC UP_LIST_POSITION 'N'
-- 3. SQL-SERVER의 함수 만들기
-- SQL 함수에 필요한 것은
-- 1) 인자 (있을수도 있고 없을수도 있다.)
-- 2) 반환하는 데이터 형
-- 3) 반환 되는 값
-- 형식)
CREATE FUNCTION 함수 이름
(
-- 여기에 인자가 들어 갑니다.
)
RETURNS 반환되는 데이터 형식
AS
BEGIN
-- 함수 내용이 들어 갑니다.
RETURN 돌려줄 변수 명
END
----------------------------------
SELECT * FROM T1

-- 인자가 필요없는 경우
CREATE FUNCTION FN_MAX_NUM
(
)
RETURNS
INT
AS
BEGIN
DECLARE @MAX_NUM INT
SET @MAX_NUM = (SELECT ISNULL(MAX(NUM), 0) + 1 FROM T1)
RETURN @MAX_NUM
END
-- 함수 호출
SELECT dbo.FN_MAX_NUM()
----------------------------------
-- 인자가 들어가는 함수
CREATE ALTER FUNCTION FN_DATE
(

@YY INT,
@MM INT,
@DD INT
)
RETURNS CHAR(10)
AS
BEGIN
DECLARE @FDATE CHAR(10), @CDATE DATETIME
SET @FDATE = CONVERT(CHAR(4), @YY) + '-' +
CONVERT(CHAR(2), @MM) + '-' + CONVERT(CHAR(2), @DD)
SET @CDATE = CONVERT(DATETIME, @FDATE)
SET @FDATE = CONVERT(CHAR(10), @CDATE, 121)
RETURN @FDATE
END
-------------------------------
SELECT DBO.FN_DATE(2008,4,1)
결과값 : 2008-04-01
SELECT DBO.FN_DATE(2008,11,21)
결과값 : 2008-11-21

Trackbacks 0 | Comments 1
뉴스관리를 위한 프로시저 만들기 2 (CASE문 사용) 보러가기

 -- ★★ 관리자 입장의 검색조건 가능한 목록 리스팅 프로시저
CREATE ALTER PROC UP_NEWS_LIST_BY_ADMIN
@CATEGORY1 char(8),
@CATEGORY2 char(8),
@IS_ADMIT char(1),
@STARTDATE datetime,
@ENDDATE datetime
AS
DECLARE @QUERY VARCHAR(800)
SET @QUERY = 'SELECT NEWS_IDX, ''['' + (SELECT CODE_NAME FROM T_NEWS_CATEGORY WHERE CODE_IDX = N.CATEGORY1) + '' > '' +'
SET @QUERY = @QUERY + '(SELECT CODE_NAME FROM T_NEWS_CATEGORY WHERE CODE_IDX = N.CATEGORY2) + ''] '' + TITLE AS [TITLE],'
SET @QUERY = @QUERY + 'CONTENT, (SELECT USER_NAME FROM T_NEWS_MEMBER WHERE USER_ID = N.USER_ID) AS [WRITER],'
SET @QUERY = @QUERY + '(CASE N.IS_ADMIT WHEN ''Y'' THEN ''승인'' ELSE ''미승인'' END) AS [IS_ADMIT],'
SET @QUERY = @QUERY + '(CASE DATEDIFF(DAY, N.REGDATE, GETDATE()) WHEN ''0'' THEN RIGHT(N.REGDATE, 6) ELSE CONVERT(VARCHAR(10), N.REGDATE, 121) END) AS [REGDATE],'
SET @QUERY = @QUERY + 'STARTDATE, ENDDATE, ATTACH_CNT, VIEW_CNT FROM T_NEWS_NEWS N WHERE NEWS_IDX > 10000000'
IF(@CATEGORY1 <> '' AND @CATEGORY1 IS NOT NULL)
BEGIN
 SET @QUERY = @QUERY + ' AND CATEGORY1 = ''' + @CATEGORY1 + ''''
END
IF(@CATEGORY2 <> '' AND @CATEGORY2 IS NOT NULL)
BEGIN
 SET @QUERY = @QUERY + ' AND CATEGORY2 = ''' + @CATEGORY2 + ''''
END
IF(@CATEGORY1 <> '' AND @CATEGORY1 IS NOT NULL)
BEGIN
 SET @QUERY = @QUERY + ' AND IS_ADMIT = ''' + @IS_ADMIT + ''''
END
IF((@STARTDATE <> '' AND @STARTDATE IS NOT NULL) AND (@ENDDATE <> '' AND @ENDDATE IS NOT NULL))
BEGIN
 IF(@STARTDATE >= @ENDDATE)
 BEGIN PRINT '시작일이 종료일보다 클수 없습니다' RETURN END
 ELSE
 BEGIN
  SET @QUERY = @QUERY + ' AND STARTDATE >= ''' + CONVERT(VARCHAR(10),@STARTDATE) + ''''
  SET @QUERY = @QUERY + ' AND ENDDATE <= ''' + CONVERT(VARCHAR(10),@ENDDATE) + ''''
 END
END
SET @QUERY = @QUERY + ' ORDER BY NEWS_IDX DESC'
--SELECT (@QUERY)
EXEC (@QUERY)

EXEC UP_NEWS_LIST_BY_ADMIN '','','','',''


-- ★ 출력 명령어 : PRINT와 SELECT 차이
--     PRINT '문자열' RETURN -- 이 구문이 메시지 창에 출력되고 루틴 종료됨.
--     SELECT '문자열' -- 이 구문이 결과 창에 1행 1열로 출력됨
-- ★ 테이블명 바꾸기 (주의! Relation이 없는 테이블에 할 것.)
EXEC SP_RENAME '기존 테이블명', '신규 테이블명'

BEGIN TRAN
SELECT * FROM T_NEWS_NEWS
EXEC SP_RENAME 'T_NEWS_NEWS', 'T_NEWS'
ROLLBACK

-- ★ 컬럼명 바꾸기 (주의! Relation이 없는 컬럼에 할 것.)
EXEC SP_RENAME '테이블명.기존 컬럼명', '신규 컬럼명', 'COLUMN(타입)'

BEGIN TRAN
SELECT * FROM T_NEWS_NEWS
EXEC SP_RENAME 'T_NEWS_NEWS.TITLE', 'JEMOK', 'COLUMN'
ROLLBACK
-- ★ 글쓴 날짜가 오늘이라면 시간을, 아니라면 문자를 출력
-- SELECT CONVERT(VARCHAR(10), GETDATE(), 121)

SELECT NEWS_IDX,
'[' + (SELECT CODE_NAME FROM T_NEWS_CATEGORY WHERE CODE_IDX = N.CATEGORY1) + ' > ' +
(SELECT CODE_NAME FROM T_NEWS_CATEGORY WHERE CODE_IDX = N.CATEGORY2) + '] ' + TITLE AS [TITLE],
CONTENT, (SELECT USER_NAME FROM T_NEWS_MEMBER WHERE USER_ID = N.USER_ID) AS [WRITER],
(CASE N.IS_ADMIT WHEN 'Y' THEN '승인' ELSE '미승인' END) AS [IS_ADMIT],
STARTDATE, ENDDATE, ATTACH_CNT, VIEW_CNT,
(CASE DATEDIFF(DAY, N.REGDATE, GETDATE()-1) WHEN '0' THEN CONVERT(VARCHAR(10), N.REGDATE, 121) ELSE RIGHT(N.REGDATE, 6) END) AS [REGDATE]
FROM T_NEWS_NEWS N
WHERE NEWS_IDX > 10000000
ORDER BY NEWS_IDX DESC
Trackbacks 0 | Comments 0
뉴스관리를 위한 프로시저 만들기 1 (IF 문 사용) 보러가기
-- 1. 회원 테이블
-- 아이디 PK
-- 비밀번호
-- 이름
-- 우편번호
-- 주소
-- 전화번호
-- 활동여부
-- 등록일
-------------------------------------------
CREATE TABLE T_NEWS_MEMBER
(
 USER_ID  VARCHAR(20) PRIMARY KEY
, USER_PASS VARCHAR(10) NOT NULL
, USER_NAME VARCHAR(5) NOT NULL
, ZIPCODE VARCHAR(7) NULL
,   USER_ADDR VARCHAR(100) NULL -- 기본주소↔나머지주소로 구분
,   USER_PHONE VARCHAR(15) NULL
,   IS_DELETE CHAR(1) DEFAULT 'Y'
,   REGDATE DATETIME DEFAULT GETDATE()
)
-- 2. 뉴스 테이블
-- 일련번호
-- 기사제목
-- 기사내용
-- 기사작성자
-- 승인 여부 (Y:현재 승인=보여지고 있는 기사)
-- 시작일
-- 종료일
-- 첨부파일 (1) -- 반정규화
-- 조회수
-- 등록일

CREATE TABLE T_NEWS_NEWS
(
 NEWS_IDX  CHAR(8) PRIMARY KEY
, TITLE    VARCHAR(300) -- 기사 제목
,   CONTENT  TEXT -- 기사 내용
,   CATEGORY1  CHAR(8)  -- 1차 카테고리 키값
,   CATEGORY2  CHAR(8)  -- 2차 카테고리 키값
,   USER_ID   VARCHAR(20)  -- 작성자
,   IS_ADMIT  CHAR(1)  -- Y : 승인, N : 미승인
,   STARTDATE  DATETIME  -- 기사 시작일
,   ENDDATE  DATETIME  -- 기사 종료일
,   ATTACH_CNT INT  -- 첨부파일 갯수
,   VIEW_CNT  INT  -- 조회수
,   REGDATE  DATETIME -- 등록일
)


-- 각 카테고리 별로 볼 수 있어야 한다
-- 1. 뉴스 등록하기
INSERT T_NEWS_NEWS VALUES ()

CREATE PROC UP_INSERT_NEWS
@TITLE VARCHAR(300),
@CONTENT TEXT,
@CATEGORY1 CHAR(8),
@CATEGORY2 CHAR(8),
@USER_ID VARCHAR(20),
@STARTDATE DATETIME,
@ENDDATE DATETIME,
@ATTACH_CNT INT
AS
DECLARE @MAX_IDX CHAR(8)
SET @MAX_IDX = (SELECT ISNULL(MAX(NEWS_IDX), 10000000) + 1 FROM T_NEWS_NEWS)
INSERT T_NEWS_NEWS
VALUES(@MAX_IDX, @TITLE, @CONTENT, @CATEGORY1, @CATEGORY2, @USER_ID,
'N', @STARTDATE, @ENDDATE, @ATTACH_CNT, 0, GETDATE())

EXEC UP_INSERT_NEWS '첫번째 뉴스', '첫번째 뉴스 내용', '10000001', '10000004', '홍길동이',
'2008-04-14', '2008-04-24', 1
SELECT * FROM T_NEWS_NEWS


-- 2. 뉴스 수정하기
-- 수정을 위해서 필요한 값
-- NEWS_IDX 값을 반드시 알아야 한다
UPDATE T_NEWS_NEWS SET
TITLE=@TITLE, CONTENT=@CONTENT.....
WHERE NEWS_IDX = @NEWS_IDX

-- 매개변수명과 타입을 뽑아오는 프로시저 SP_MP 를 실행해서 들고오면 편하다...
SP_MP T_NEWS_NEWS

ALTER PROC UP_UPDATE_NEWS
@NEWS_IDX CHAR(8),
@TITLE VARCHAR(300),
@CONTENT TEXT,
@CATEGORY1 CHAR(8),
@CATEGORY2 CHAR(8),
@STARTDATE DATETIME,
@ENDDATE DATETIME
AS
UPDATE T_NEWS_NEWS SET TITLE=@TITLE, CONTENT=@CONTENT,
CATEGORY1 = @CATEGORY1, CATEGORY2 = @CATEGORY2,
STARTDATE = @STARTDATE, ENDDATE = @ENDDATE
WHERE NEWS_IDX = @NEWS_IDX

EXEC UP_UPDATE_NEWS 10000001, '첫번째 뉴스', '첫번째 뉴스 내용', '10000001', '10000004', '2008-04-14', '2008-04-24'
SELECT * FROM T_NEWS_NEWS


-- 3. 뉴스 삭제하기
DELETE T_NEWS_NEWS WHERE NEWS_IDX = @NEWS_IDX
CREATE PROC UP_DELETE_NEWS
@NEWS_IDX CHAR(8)
AS
DELETE T_NEWS_NEWS WHERE NEWS_IDX = @NEWS_IDX

BEGIN TRAN
EXEC UP_DELETE_NEWS 10000001
SELECT * FROM T_NEWS_NEWS
ROLLBACK


-- 4. 뉴스 승인/미승인 하기
UPDATE T_NEWS_NEWS SET IS_ADMIT=@IS_ADMIT WHERE NEWS_IDX = @NEWS_IDX
-- CASE 컬럼명 WHEN 'Y' THEN 'N' ELSE 'Y' END

CREATE PROC UP_ADMIT_NEWS
AS
UPDATE T_NEWS_NEWS SET IS_ADMIT =
(CASE IS_ADMIT WHEN  'Y' THEN 'N' ELSE 'Y' END)

EXEC UP_ADMIT_NEWS
SELECT * FROM T_NEWS_NEWS


-- 5. 뉴스 목록 뽑아오기
-- 타이틀 앞에 CATEGORY1 >> CATEGORY2 형태로 추가해서 목록 뽑아오는 쿼리
SELECT NEWS_IDX, '('+ (SELECT (SELECT CODE_NAME FROM T_NEWS_CATEGORY WHERE CODE_IDX = CATEGORY1)
+  '>'  + CODE_NAME FROM T_NEWS_CATEGORY WHERE CODE_IDX = CATEGORY2) + ') ' +
TITLE, CONTENT, USER_ID, IS_ADMIT, STARTDATE, ENDDATE, ATTACH_CNT, VIEW_CNT, REGDATE
FROM T_NEWS_NEWS
WHERE NEWS_IDX > 0 AND IS_ADMIT = 'Y' ORDER BY NEWS_IDX DESC

CREATE PROC UP_SELECT_NEWS
@CATEGORY1  CHAR(8),
@CATEGORY2  CHAR(8),
@IS_ADMIT  CHAR(1),
@STARTDATE  VARCHAR(10),
@ENDDATE  VARCHAR(10)
AS
DECLARE @QUERY VARCHAR(800)
SET @QUERY = 'SELECT NEWS_IDX, ''(''+ (SELECT (SELECT CODE_NAME FROM T_NEWS_CATEGORY WHERE CODE_IDX = CATEGORY1) +  ''>''  +'
SET @QUERY = @QUERY + 'CODE_NAME FROM T_NEWS_CATEGORY WHERE CODE_IDX = CATEGORY2) + '') '' +'
SET @QUERY = @QUERY + 'TITLE AS [제목], CONTENT, USER_ID, IS_ADMIT, STARTDATE, ENDDATE, ATTACH_CNT, VIEW_CNT, REGDATE '
SET @QUERY = @QUERY + 'FROM T_NEWS_NEWS WHERE NEWS_IDX > 0'
-- 1차 카테고리 조건
IF @CATEGORY1 <> '' BEGIN SET @QUERY = @QUERY + ' AND CATEGORY1 = ' + @CATEGORY1 END
-- 2차 카테고리 조건
IF @CATEGORY2 <> '' BEGIN SET @QUERY = @QUERY + ' AND CATEGORY2 = ' + @CATEGORY2 END
-- 승인여부 조건 검색
IF @IS_ADMIT <> '' BEGIN SET @QUERY = @QUERY + ' AND IS_ADMIT = ''' + @IS_ADMIT + '''' END
-- 발행일 조건 검색
IF @STARTDATE <> '' BEGIN SET @QUERY = @QUERY + ' AND STARTDATE >= ''' + @STARTDATE + '''' END
-- 만료일 조건 검색
IF @ENDDATE <> '' BEGIN SET @QUERY = @QUERY + ' AND ENDDATE <= ''' + @ENDDATE + '''' END
SET @QUERY = @QUERY + ' ORDER BY NEWS_IDX DESC'
EXEC (@QUERY)

EXEC UP_SELECT_NEWS '', '', '', '', ''

Trackbacks 0 | Comments 0

USE MASTER
CREATE DATABASE NEWS
-- 새로운 데이터베이스 NEWS를 생성
USE NEWS
--1. 카테고리 관련 테이블
--2. 사용자 관련 테이블
--3. 뉴스 관리 테이블


-- 1. 카테고리 테이블 생성
CREATE TABLE T_NEWS_CATEGORY
(
 CODE_IDX  CHAR(8) PRIMARY KEY,
 CODE_NAME VARCHAR(100)  NOT NULL,
 PARENT_CODE_IDX CHAR(8)  NOT NULL
)
-- 데이터 입력시 규칙
-- 1) 1차 카테고리일 경우
-- PARENT_CODE_IDX 값이 00000000
-- 2) 2차 카테고리일 경우
-- PARENT_CODE_IDX 값 CODE_IDX

SELECT * FROM T_NEWS_CATEGORY
--1차 카테고리 입력
INSERT T_NEWS_CATEGORY VALUES('10000001', '뉴스', '00000000')
INSERT T_NEWS_CATEGORY VALUES('10000002', '금융', '00000000')
INSERT T_NEWS_CATEGORY VALUES('10000003', '부동산', '00000000')
--2차 카테고리 입력
INSERT T_NEWS_CATEGORY VALUES('10000004', '뉴스종합', '00000001')
-- 1차, 2차 카테고리 선택
SELECT CODE_IDX, CODE_NAME
FROM T_NEWS_CATEGORY WHERE PARENT_CODE_IDX = '00000000'
--10000001
SELECT CODE_IDX, CODE_NAME
FROM T_NEWS_CATEGORY WHERE PARENT_CODE_IDX = '10000001'


--★★ 카테고리 리스트 프로시저
CREATE PROC UP_LIST_CATEGORY
@_ROOT CHAR(8)
AS
IF @_ROOT = '00000000' -- 1차 카테고리를 보겠다
BEGIN
 SELECT CODE_IDX, CODE_NAME FROM T_NEWS_CATEGORY
 WHERE PARENT_CODE_IDX = @_ROOT
END
ELSE -- 2차 카테고릴 보겠다
BEGIN
 SELECT CODE_IDX, CODE_NAME FROM T_NEWS_CATEGORY
 WHERE PARENT_CODE_IDX = @_ROOT
END
--------------------------------------------
-- 목록 뽑아오기
EXEC UP_LIST_CATEGORY '00000000'
-- 구문 : @@ROWCOUNT ==> 적용을 받은 행의 개수를 반환
-- @ : 사용자 정의 변수
-- @@ : 시스템 변수
CREATE TABLE T1
(
 IDX INT PRIMARY KEY,
 NAME VARCHAR(10)
)

CREATE PROC UP_INSERT_T1
@IDX INT,
@NAME VARCHAR(10),
@RESULT INT OUTPUT
AS
INSERT T1 VALUES(@IDX, @NAME)
SET @RESULT = @@ROWCOUNT

DECLARE @RCNT INT
EXEC UP_INSERT_T1 1, '가가가', @RCNT OUTPUT
SELECT @RCNT

INSERT T1 VALUES(1, '가가가')
---------------------------------------
-- 1차 카테고리 (00000000) 안에 '뉴스' 라는 값이 있는지를 검사 (존재하면 1 반환, 존재안하면 0반환)
SELECT COUNT(*) FROM T_NEWS_CATEGORY WHERE CODE_NAME = '뉴스' AND PARENT_CODE_IDX = '00000000'
SELECT CODE_NAME FROM T_NEWS_CATEGORY WHERE CODE_NAME = '뉴스' AND PARENT_CODE_IDX = '00000000'
----------------------------------------
--★★ 카테고리 등록 (1차, 2차)
CREATE PROC UP_INSERT_CATEGORY
@CODE_NAME VARCHAR(100),
@PARENT_CODE_IDX CHAR(8),
@RESULT INT OUTPUT
AS
DECLARE @MAXROW VARCHAR(8), @ISEXIST INT
-- @MAXROW 첫행에 들어갈 CODE_IDX 값을 저장
SET @MAXROW = (SELECT ISNULL(MAX(CODE_IDX), '10000000') + 1 FROM T_NEWS_CATEGORY)
-- ★★★★ @ISEXIST 등록할려는 카테고리에 중복이 존재하면 1이, 아니면 0 저장 (COUNT 함수 이용)
SET @ISEXIST = (SELECT COUNT(*) FROM T_NEWS_CATEGORY WHERE CODE_NAME = @CODE_NAME AND PARENT_CODE_IDX = @PARENT_CODE_IDX)
IF @PARENT_CODE_IDX = '00000000'
BEGIN
 IF @ISEXIST = 0
 BEGIN
  INSERT T_NEWS_CATEGORY VALUES(@MAXROW, @CODE_NAME, @PARENT_CODE_IDX)
  SET @RESULT = @@ROWCOUNT
 END
    ELSE
 BEGIN
  SET @RESULT = 0
 END
END
ELSE -- 2차 등록을 원했다면
BEGIN
 IF @ISEXIST = 0
 BEGIN
  INSERT T_NEWS_CATEGORY VALUES(@MAXROW, @CODE_NAME, @PARENT_CODE_IDX)
  SET @RESULT = @@ROWCOUNT
 END
 BEGIN
  SET @RESULT = 0
 END
END

DECLARE @RESULT INT
EXEC UP_INSERT_CATEGORY '뉴스종합', '10000001', @RESULT OUTPUT
SELECT @RESULT
SELECT * FROM T_NEWS_CATEGORY
-- ★ 출력용매개변수로 @RESULT 값을 받는 이유
-- : 1값이 반환되면 성공, 0이면 실패. 프로그래밍 단에서 처리 하도록


--★★ 카테고리 등록 간략버전
CREATE PROC UP_INSERT_CATEGORY
@CODE_NAME VARCHAR(100),
@PARENT_CODE_IDX CHAR(8),
@RESULT INT OUTPUT
AS
DECLARE @MAXROW VARCHAR(8), @ISEXIST INT
-- @MAXROW 첫행에 들어갈 CODE_IDX 값을 저장
SET @MAXROW = (SELECT ISNULL(MAX(CODE_IDX), '10000000') + 1 FROM T_NEWS_CATEGORY)
-- @ISEXIST 등록할려는 카테고리에 중복이 존재하면 1이, 아니면 0 저장
SET @ISEXIST = (SELECT COUNT(*) FROM T_NEWS_CATEGORY WHERE CODE_NAME = @CODE_NAME AND PARENT_CODE_IDX = @PARENT_CODE_IDX)
IF @ISEXIST = 0
BEGIN
 INSERT T_NEWS_CATEGORY VALUES(@MAXROW, @CODE_NAME, @PARENT_CODE_IDX)
 SET @RESULT = @@ROWCOUNT
END
ELSE
BEGIN
 SET @RESULT = 0
END

DECLARE @RESULT INT
EXEC UP_INSERT_CATEGORY '뉴스종합2', '10000001', @RESULT OUTPUT
SELECT @RESULT
SELECT * FROM T_NEWS_CATEGORY


-- 1차 2차 카테고리 수정
CREATE PROC UP_UPDATE_CATEGORY
@CODE_IDX VARCHAR(8),
@CODE_NAME VARCHAR(100),
@RESULT INT OUTPUT
AS
-- 중복검사 필요
UPDATE T_NEWS_CATEGORY
SET CODE_NAME = @CODE_NAME WHERE CODE_IDX = @CODE_IDX
SET @RESULT = @@ROWCOUNT

DECLARE @RESULT INT
EXEC UP_UPDATE_CATEGORY '10000001', '뉴스', @RESULT OUTPUT
SELECT @RESULT
SELECT * FROM T_NEWS_CATEGORY
---------------------------------------------------------------------------------

Trackbacks 1 | Comments 0

지난번 MS-SQL 게시물에 이어서.... -_-a


-- 게시물 신규 등록 프로시저
CREATE PROC UP_INSERT_BOARD
@TITLE VARCHAR(300),
@CONTENT TEXT,
@USER_ID VARCHAR(20),
@WRITER VARCHAR(10),
@PASSWORD VARCHAR(10)
AS
-- (BOARD_MAIN_IDX의 최대값을 구해와서 새 게시물의 IDX로 쓰기 위해 변수지정)
DECLARE @BOARD_MAIN_IDX CHAR(8)
SET @BOARD_MAIN_IDX = (SELECT ISNULL(MAX(BOARD_MAIN_IDX), 10000000) + 1 FROM T_BOARD_MAIN)
INSERT T_BOARD_MAIN VALUES
(@BOARD_MAIN_IDX, @TITLE, @CONTENT, @USER_ID, @WRITER, @PASSWORD, 0, 0, 0, 'N', GETDATE(), GETDATE())

EXEC UP_INSERT_BOARD '처음이예요', '내용입니다', 'AAA', '↔', '↔'

SELECT * FROM T_BOARD_MAIN


-- 게시물 목록 보기 프로시저 (페이지당 최대 몇개 볼래?)
CREATE PROC UP_LIST_BOARD
@TOP_VAL INT
AS
SELECT TOP(@TOP_VAL) * FROM T_BOARD_MAIN
ORDER BY BOARD_MAIN_IDX DESC

DROP PROC UP_LIST_BOARD
EXEC UP_LIST_BOARD 1


-- 주의 : 프로시저를 만들때 매개변수 사용
-- 1) SELECT TOP N 구문에서 N에 해당하는 매개변수의 사용법
-- 2) 테이블명을 매개변수 값으로 받을수는 없다
CREATE TABLE TEST1
(
 IDX INT,
 NAME VARCHAR(200)
)
INSERT TEST1 VALUES (1, 'AAA')
INSERT TEST1 VALUES (2, 'BBB')
SELECT * FROM TEST1
DROP TABLE TEST1
-- 아래 프로시저는 수행 안됨
CREATE PROC UP_SEL_T1
@TBLNAME VARCHAR(100)
AS
SELECT * FROM @TBLNAME

-- ★★ 문자열 매개변수 생성후 쿼리 실행
CREATE PROC UP_SEL_T1
@TBLNAME VARCHAR(100)
AS
DECLARE @QUERY VARCHAR(500)
SET @QUERY = 'SELECT * FROM ' + @TBLNAME
EXEC(@QUERY) -- 실행시
--SELECT(@QUERY) -- 쿼리문 뽑아올 시

EXEC UP_SEL_T1 'T_BOARD_MAIN'
DROP PROC UP_SEL_T1

-- 쿼리를 문자열 변수로 저장후 변수값을 이용하여 쿼리문 실행 (페이지당 몇개 볼래?)
CREATE PROC UP_SEL_T1_ALTER
@TOPCNT INT, -- 매개변수 : 페이지당 볼 글의 수
@TBLNAME VARCHAR(100) -- 게시물이 저장된 테이블명
AS
DECLARE @QUERY VARCHAR(500)
SET @QUERY = 'SELECT TOP '+CONVERT(VARCHAR(10),
@TOPCNT)+' * FROM ' + @TBLNAME
EXEC(@QUERY)

EXEC UP_SEL_T1_ALTER 1, 'TEST1'
DROP PROC UP_SEL_T1_ALTER


-- 페이징 쿼리 NOT IN
-- 한 페이지당 1개 -> 개발자 쪽에서 결정
--> 페이지 사이즈 -> 1
-- 사용자 -< 나 ?(1) 페이지 볼래요

SELECT TOP 가져올 개수 (페이지 사이즈)
가져올 컬럼들...
FROM T_BOARD_MAIN
WHERE BOARD_MAIN_IDX > 10000000
AND NOT IN(SELECT TOP(페이지 번호 -1) * 페이지 사이즈)
BOARD_MAIN_IDX FROM T_BOARD_MAIN ORDER BY BOARD_MAIN_IDX
ORDER BY BOARD_MAIN_IDX DESC


-- 목록 뽑아 오기 (페이징 적용)
-- ★ 리스트 페이징 프로시저

CREATE PROC UP_LIST_BOARD2
@PAGE_SIZE VARCHAR(3),
@PAGE_NUM VARCHAR(3)
AS
DECLARE @QUERY VARCHAR(300)
SET @QUERY =
'SELECT TOP (' + @PAGE_SIZE + ') * FROM T_BOARD_MAIN WHERE BOARD_MAIN_IDX > 10000000
AND BOARD_MAIN_IDX NOT IN(SELECT TOP(('+ @PAGE_NUM+'-1)*'+@PAGE_SIZE+')
BOARD_MAIN_IDX FROM T_BOARD_MAIN ORDER BY BOARD_MAIN_IDX DESC) ORDER BY BOARD_MAIN_IDX DESC'
EXEC(@QUERY)
--SELECT(@QUERY)

DROP PROC UP_LIST_BOARD2
EXEC UP_LIST_BOARD2 '1', '5'


-- CM 모드 (문자열 결합 형태 A = A + B)
CREATE PROC UP_LIST_BOARD3
@PAGESIZE INT,
@PAGENUM INT
AS
DECLARE @RESULT INT, @QUERY VARCHAR(500)
SET @RESULT = ((@PAGENUM-1) * @PAGESIZE)
SET @QUERY = 'SELECT TOP ' + CONVERT(VARCHAR(10), @PAGESIZE) +' * FROM '
SET @QUERY = @QUERY + ' T_BOARD_MAIN '
SET @QUERY = @QUERY + ' WHERE BOARD_MAIN_IDX > 10000000 '
SET @QUERY = @QUERY + ' AND BOARD_MAIN_IDX  NOT IN ('
SET @QUERY = @QUERY + ' SELECT TOP ' + CONVERT(VARCHAR(10),@RESULT) + ' BOARD_MAIN_IDX'
SET @QUERY = @QUERY + ' FROM T_BOARD_MAIN ORDER BY BOARD_MAIN_IDX DESC) '
SET @QUERY = @QUERY + ' ORDER BY BOARD_MAIN_IDX DESC '
--SELECT (@QUERY)
EXEC(@QUERY)

DROP PROC UP_LIST_BOARD3
EXEC UP_LIST_BOARD3 1, 2


-- 출력용 매개변수 (C#의 out 키워드 역할 => OUTPUT)
CREATE PROC UP_TEST1
@PATH VARCHAR(100),
@FNAME VARCHAR(100) OUTPUT
AS
SET @FNAME = (@PATH)

DECLARE @FNAME VARCHAR(100)
EXEC UP_TEST1 'AAA.TXT', @FNAME OUTPUT
SELECT (@FNAME)


-- OUTPUT TEST
CREATE PROC UP_LIST_BOARD4
@PAGE_SIZE VARCHAR(3),
@PAGE_NUM VARCHAR(3),
@OUTQ VARCHAR(300) OUTPUT
AS
DECLARE @QUERY VARCHAR(300)
SET @QUERY =
'SELECT TOP (' + @PAGE_SIZE + ') * FROM T_BOARD_MAIN WHERE BOARD_MAIN_IDX > 10000000
AND BOARD_MAIN_IDX NOT IN(SELECT TOP(('+ @PAGE_NUM+'-1)*'+@PAGE_SIZE+')
BOARD_MAIN_IDX FROM T_BOARD_MAIN ORDER BY BOARD_MAIN_IDX DESC) ORDER BY BOARD_MAIN_IDX DESC'
SET @OUTQ = (@QUERY)
-- EXEC(@QUERY)
-- SELECT(@QUERY)

DROP PROC UP_LIST_BOARD4

DECLARE @OUTQUERY VARCHAR(300)
EXEC UP_LIST_BOARD4 '1', '5', @OUTQUERY OUTPUT
SELECT @OUTQUERY


사용자가 글번호가 10000001인 녀석의
상세보기를 원했다면
-- 상세보기
SELECT * FROM T_BOARD_MAIN
WHERE BOARD_MAIN_IDX = 10000001
-- 코멘트 내용 가져오기
SELECT * FROM T_BOARD_COMMENT
WHERE BOARD_MAIN_IDX = 10000001

-------댓글 페이징 : BETWEEN 으로 내림차순만들기------------
-- BETWEEN A AND B
-- A : B - (페이지 사이즈 -1)
-- B : MAX(COMMENT) - 페이지 사이즈(N) * (페이지 갯수(K) -1)

SELECT * FROM T_BOARD_COMMENT
WHERE BOARD_MAIN_IDX = 10000001--N=5, K=3
AND COMMENT_IDX
BETWEEN -- (@M_COMMENT_IDX-N*(K-1)-(N-1)) AND (@M_COMMENT_IDX-N*(K-1))
(@M_COMMENT_IDX-1*(2-1)-(1-1)) AND (@M_COMMENT_IDX-1*(2-1))
ORDER BY COMMENT_IDX DESC
------------NOT IN----------------------------------------------

SELECT TOP 10 *
FROM T_BOARD_COMMENT
WHERE  BOARD_MAIN_IDX = 10000001
 AND COMMENT_IDX 
NOT IN(SELECT TOP 20 COMMENT_IDX FROM T_BOARD_COMMENT  ORDER BY  COMMENT_IDX DESC) 
ORDER BY COMMENT_IDX DESC
----------------------------------------------------------------
SELECT COMMENT_IDX,COMMENT
FROM T_BOARD_COMMENT
WHERE BOARD_MAIN_IDX = 10000001
AND COMMENT_IDX BETWEEN (N*K-N+1) AND (N*K)

Trackbacks 0 | Comments 0

SELECT *
FROM T_BOARD_MAIN
-----------------------------------------------------------------
-- 1. 스토어드 프로시저

-- 미리 수행할 쿼리문을 만들어 놓구서 필요할때 이름을 불러서
-- 사용 한다.
-- 이점 : 일반 쿼리문에 보다 속도가 휠 빠르다.

CREATE TABLE T_TEST
(
 IDX INT
, NAME VARCHAR(100)
)

-- 프로시저 만들기
--1)
SELECT * FROM T_TEST
위의 녀석을 프로시저로 만들어 보자
문법)
--CREATE PROC UP_LIST
--AS
--수행할 SQL 쿼리문
CREATE PROC UP_LIST
AS
SELECT * FROM T_TEST
ORDER BY IDX DESC
-----------------------------------
-- EXEC 실행할 프로시저명
EXEC UP_LIST
UP_LIST

--프로시저 구문 분석시===> SP_HELPTEXT 프로시저명
EXEC SP_HELPTEXT UP_LIST
ALTER PROC UP_LIST
AS
SELECT * FROM T_TEST
ORDER BY IDX
---------------------------------------------------------
EXEC UP_LIST

INSERT T_TEST VALUES(1,'강감찬')
----------------------------------------------
위의 형태와 같이 사용자에게 번호와 이름을 얻어와서
데이터를 입력하는 프로시저를 만들자
번호 : 2
이름 : 홍길동
CREATE PROC UP_INSERT_TEST
@IDX INT,
@NAME VARCHAR(100)
AS
INSERT T_TEST VALUES(@IDX,@NAME)

EXEC UP_INSERT_TEST 2, '홍길동'
EXEC UP_LIST

CREATE PROC UP_LIST_1
@IDX INT
AS
SELECT * FROM T_TEST WHERE IDX = @IDX

EXEC UP_LIST_1 2

SELECT * FROM T_BOARD_MAIN



DECLARE @BOARD_MAIN_IDX CHAR(8)


Trackbacks 0 | Comments 0

---------------------------------------
1. 유머 게시판 스키마를 만들어 보자
--
http://www.motorsvalley.co.kr/community/humor/list.aspx
---------------------------------------
-- 유머 게시판 테이블
CREATE TABLE T_HUMOR
(
 H_NUM  INT  IDENTITY(1,1) PRIMARY KEY, -- 자동증가, PK
 H_USERNAME VARCHAR(10) NOT NULL,
 H_USERMAIL VARCHAR(20) NULL,
 H_USERPW VARCHAR(8) NOT NULL,
 H_CODETYPE CHAR(1)  NOT NULL DEFAULT '0', -- DEFAULT = 0 : 텍스트, 1 : HTML
 H_TITLE  VARCHAR(200) NOT NULL,
 H_TEXT  TEXT  NULL,
 H_REGDATE DATETIME NULL,
 H_VIEWCNT INT  NOT NULL,
 H_ISMEMBER CHAR(1)  NOT NULL DEFAULT '0', -- DEFAULT = 0 : 비회원, 1 : 회원
 H_SHOWCHK CHAR(1)  NOT NULL DEFAULT '1'  -- DEFAULT = 0 : 안보이기, 1 : 보이기
)

-- 유머 게시판 첨부 파일 테이블
CREATE TABLE T_HUMOR_ATT
(
 H_NUM  INT  NOT NULL
 FOREIGN KEY REFERENCES T_HUMOR(H_NUM) ON UPDATE CASCADE ON DELETE CASCADE,
 H_FILESEQ INT  NOT NULL,
 H_FILENAME VARCHAR(200) NOT NULL
)

--DROP TABLE T_HUMOR
--DROP TABLE T_HUMOR_ATT

-- 유머 게시판 더미 값 삽입
INSERT T_HUMOR VALUES('홍길동', 'aaa@paran.com', '1234', DEFAULT, '아하하하하', '아하하하하하하하', GETDATE(), 0, DEFAULT, DEFAULT)
INSERT T_HUMOR VALUES('이순신', 'bbb@paran.com', '1234', DEFAULT, '으하하하하', '으하하하하하하하', GETDATE(), 0, DEFAULT, DEFAULT)
INSERT T_HUMOR VALUES('강감찬', 'ccc@paran.com', '1234', DEFAULT, '음하하하하', '음하하하하하하하', GETDATE(), 0, DEFAULT, DEFAULT)

-- 유머 게시판 파일 첨부 더미 값 삽입
INSERT T_HUMOR_ATT VALUES(1, 1, 'AAA.TXT');
INSERT T_HUMOR_ATT VALUES(1, 2, 'BBB.TXT');
INSERT T_HUMOR_ATT VALUES(1, 3, 'CCC.TXT');

SELECT * FROM T_HUMOR
SELECT * FROM T_HUMOR_ATT

--★ 관리자 입장

1. 관리자는 전체 게시물(삭제 게시물 포함)의 목록을 볼 수 있어야 한다.

-- H_SHOWCHK 값이 0 (안보이기) 이상인 값을 가진 데이터를 뽑는다 혹은 그냥 전체 조회.
SELECT * FROM T_HUMOR WHERE H_SHOWCHK >= 0

2. 관리자는 삭제된 게시물의 목록만 따로 볼 수 있어야 한다.
(사용자는 실제로 삭제 되었다고 생각하지만 DB에는 남아있다.
단, 사용자에게는 보여지지 않을 뿐)

-- H_SHOWCHK 값이 0 (안보이기) 인 값을 가진 데이터를 뽑는다
SELECT * FROM T_HUMOR WHERE H_SHOWCHK = 0

3. 관리자는 언제든지 게시물의 성격과 부적합하다고 판단되면
게시물을 삭제 할 수 있어야 한다.
(역시 사용자에게는 보여지지 않을 뿐 2번항목으로 따로 보여줘야 한다.)

-- 삭제할 (사실은 숨길) 6번 데이터의 H_SHOWCHK 값을 0으로 준다.
UPDATE T_HUMOR SET H_SHOWCHK=0 WHERE H_NUM=6
SELECT * FROM T_HUMOR
-- 실제 삭제는 DELETE 구문으로 한다.
DELETE T_HUMOR WHERE H_NUM=4
SELECT * FROM T_HUMOR

--★ 사용자 입장
1. 사용자는 게시물을 언제든지 등록 할 수 있어야 한다.
(단, 비회원 역시 게시물을 쓸 수 있어야 하며, 비밀번호로 게시물을 삭제할 수 있다.
여기서 또한 사용자 입장의 삭제의 의미는 목록에 보여지지 않는다는 것이지
관리자 입장의 2번 항목과 같이 관리자는 볼 수 있어야 한다.)

-- 사용자 입장의 등록 (T_HUMOR 더미 데이터 삽입과 동일) -- 비회원 버전 (비번은 사용자 입력, 비회원 체크)
INSERT T_HUMOR VALUES('홍길동', 'hong@paran.com', '1234', DEFAULT, '아하하하하', '아하하하하하하하', GETDATE(), 0, DEFAULT, DEFAULT)

-- 사용자 입장의 등록 (T_HUMOR 더미 데이터 삽입과 동일) -- 회원 버전 (비번은 회원고유 비번 입력, 회원 체크)
--INSERT T_HUMOR VALUES('홍길동', 'hong@paran.com', '회원고유 비번', DEFAULT, '아하하하하', '아하하하하하하하', GETDATE(), 0, 1, DEFAULT)

-- 사용자 입장의 삭제 (관리자의 H_SHOWCHK 값 변경과 동일. 0 : 숨긴다.)
UPDATE T_HUMOR SET H_SHOWCHK=0 WHERE H_NUM=2
SELECT * FROM T_HUMOR

2. 상세 내용 역시 볼 수 있어야 한다. (pk 값 기준 하나의 데이터 전부 끌어오기)

-- 상세 데이터 조회 (첨부파일까지 LEFT OUTER JOIN으로 가져옴)
SELECT TH.H_NUM, TH.H_USERNAME, TH.H_TITLE, TH.H_TEXT, TH.H_VIEWCNT, TH.H_REGDATE, TA.H_FILENAME
FROM T_HUMOR TH LEFT OUTER JOIN T_HUMOR_ATT TA
ON TH.H_NUM = TA.H_NUM WHERE TH.H_NUM=1

3. 첨부 파일은 최대 3개 까지 등록 가능하다. 게시물 테이블과 첨부파일 테이블이 관계로 연결

-- 위의 CREATE TABLE 구문 참조. H_NUM으로 PK-FK 제약조건 걸어주고, 첨부파일 최대 3개는...How?

4. 목록에서 페이징? -- PASS

유머게시판 필요 컬럼 : 번호, 제목, 작성자, 작성일, 조회수, 삭제시 보여주는 여부 체크
         ( 1,  웃찾사 )
첨부파일 필요 컬럼 : 번호, 일련번호(최대3), 파일명

-- ★ 목록보기 페이지 (정리...)

--1) 관리자를 위한 목록 (삭제한 글)
-- SELECT * FROM T_HUMOR WHERE H_SHOWCHK >= 0

--2) 일반사용자는 삭제된 글 (보이지 않는다)
-- SELECT * FROM T_HUMOR WHERE H_SHOWCHK >= 1

-- 상세내용 보기
--SELECT * FROM [테이블명] WHERE NUM = [불러올 컬럼]

-- 삭제하기
--1) 관리자 삭제 - 완전 삭제 DELETE
--DELETE T_HUMOR WHERE H_NUM=4

--2) 사용자 삭제 - 안보이게 체크 UPDATE
--UPDATE T_HUMOR SET H_SHOWCHK=0 WHERE H_NUM=6

Trackbacks 0 | Comments 0

--- 컬럼 3개 카테고리 관리

CREATE TABLE T_CATEGORYS
(
 CODE_IDX INT IDENTITY(1,1) PRIMARY KEY,
 CODE_NAME VARCHAR(200) NULL,
 PARENT_CODE_IDX INT NOT NULL
)

-- 전제 : 1차 카테고리 : PARENT_CODE_IDX => 0
SELECT * FROM T_CATEGORY

-- 1차 카테고리 값 등록

INSERT T_CATEGORYS
(CODE_NAME, PARENT_CODE_IDX)
VALUES('게임', 0)

INSERT T_CATEGORYS
(CODE_NAME, PARENT_CODE_IDX)
VALUES('영화', 0)

INSERT T_CATEGORYS
(CODE_NAME, PARENT_CODE_IDX)
VALUES('만화', 0)

-- 1차 카테고리만 보여주자
SELECT CODE_IDX, CODE_NAME
FROM T_CATEGORYS
WHERE PARENT_CODE_IDX=0

-- 2차 카테고리 값 등록
INSERT T_CATEGORYS
(CODE_NAME, PARENT_CODE_IDX)
VALUES('액션', 2)

INSERT T_CATEGORYS
(CODE_NAME, PARENT_CODE_IDX)
VALUES('멜로', 2)

-- 2차 카테고리만 보여주자
SELECT CODE_IDX, CODE_NAME
FROM T_CATEGORYS
WHERE PARENT_CODE_IDX=2
----------------------------------------------------
CREATE TABLE T_CATES
(
 CODE_IDX CHAR(8)  NOT NULL PRIMARY KEY,
 CODE_NAME VARCHAR(200) NULL,
 PARENT_CODE_IDX CHAR(8)  NOT NULL
)

DROP TABLE T_CATES

-- 1차 등록 (전제 : PARENT_CODE_IDX : 00000000)
INSERT T_CATES
VALUES('10000001', '게임', '00000000')
INSERT T_CATES
VALUES('10000002', '영화', '00000000')
INSERT T_CATES
VALUES('10000003', '만화', '00000000')
-- 1차 카테고리만 뽑아오기
SELECT CODE_IDX, CODE_NAME FROM T_CATES
WHERE PARENT_CODE_IDX = '00000000'
-- 10000001
-- 2차 카테고리 삽입(게임 -> 스타)

INSERT T_CATES
VALUES('10000004', '스타', '10000001')
SELECT CODE_IDX, CODE_NAME FROM T_CATES
WHERE PARENT_CODE_IDX = '10000001'

-- 1차 카테고리의 개수

SELECT COUNT(*) FROM T_CATEGORY
WHERE PARENT_CODE_IDX = 0

SELECT CODE_IDX, CODE_NAME FROM T_CATEGORY
WHERE PARENT_CODE_IDX = 2

SELECT CODE_IDX, CODE_NAME FROM T_CATEGORY
WHERE PARENT_CODE_IDX = '10000001'

-- 4 영화 >> 액션
-- 5 영화 >> 액션
-- 10000004 게임 >> 스타

SELECT CODE_IDX, (SELECT CODE_NAME FROM T_CATEGORYS WHERE CODE_IDX=2) + ' >>' AS [1차],
CODE_NAME AS [2차] FROM T_CATEGORYS
WHERE PARENT_CODE_IDX = 2

SELECT CODE_IDX, (SELECT CODE_NAME FROM T_CATEGORYS WHERE CODE_IDX=2) + ' >> ' +
CODE_NAME AS [카테고리] FROM T_CATEGORYS
WHERE PARENT_CODE_IDX = 2

--> 하위 질의 (서브 쿼리)
-- : 단독으로도 수행 된다

--> 상관 관계 하위 질의
-- : 단독으로 수행이 안된다

--------------------------------------------------------------------------------------
-- 2008년 4월 1일
--1. 데이터의 무결성
-- 1) 엔티티
-- 2) 도메인 무결성 (컬럼자체에 제약을 둔다)
--> JUMIN1 | JUMIN2
--111111 | 1111111
-- 3) 참조 무결성
--------------------------------------------------------------------------------------
SELECT TOP 3 NUM, USER_NAME FROM MT_USERINFO
SELECT * FROM T_HOBBY

INSERT T_HOBBY VALUES(3, 1, '뒷북치기')

INSERT T_HOBBY VALUES(21, 1, '뒷북치기')

CREATE TABLE T_HOBBY
(
 NUM INT NOT NULL FOREIGN KEY REFERENCES
 MT_USERINFO(NUM) ON UPDATE CASCADE ON DELETE NO ACTION,
 SEQ INT NOT NULL,
 HOBBY VARCHAR(200)
)

DROP TABLE T_HOBBY

-- 기존 테이블에 기본키가 없을때 기본키를 추가
ALTER TABLE T_CATEGORYS ADD PRIMARY KEY (CODE_NAME)

-- 3번을 21번으로 바꾸면
UPDATE MT_USERINFO SET NUM=21 WHERE NUM=3

BEGIN TRAN
DELETE MT_USERINFO WHERE NUM=21
ROLLBACK

Trackbacks 0 | Comments 0

select * from t_hobby
-- 별칭 : 테이블명을 다른이름으로 만든다.

select *
from mt_userinfo mtu
where mtu.num>10
---------------------------------------------------
--문법
--1)ANSI 문법 <==추천
--2)T-SQL 문법
----------------------------------------------------

--테이블에 별칭을 사용하여 inner join 한 결과

select mtu.num,user_name,hobby
from mt_userinfo mtu inner join t_hobby h
on mtu.num=h.num

--별칭을 사용하지 않고 테이블명을 명시함으로써 inner join한 결과

select mt_userinfo.num,user_name,hobby
from mt_userinfo inner join t_hobby
on mt_userinfo.num=t_hobby.num
where mt_userinfo.num>1

--OUTER JOIN: 두개의 테이블의 검색한 컬럼의 전체 데이터를 추출!!
--from mt_userinfo LEFT OUTER JOIN t_hobby(LEFT:mt_userinfo , RIGHT:t_hobby)
--LEFT OUTER JOIN :검색할 테이블의 왼쪽을 기준으로 테이터 추출!!
--RIGHT OUTER JOIN:검색할 테이블의 오른쪽을 기준으로 테이터 추출!!

select mt_userinfo.num,user_name,hobby
from mt_userinfo LEFT OUTER JOIN t_hobby
on mt_userinfo.num=t_hobby.num

select mt_userinfo.num,user_name,hobby
from mt_userinfo RIGHT OUTER JOIN t_hobby
on mt_userinfo.num=t_hobby.num


-----------------------------------------------------------------------------
--1.게임:스타크래프트,비디오게임
--2.만화/애니메이션:일반,창작,주제/작품별
--1.1차 카테고리 테이블
--2.2차 카테고리 테이블
------------------------------------------------------------------------------
--테이블 생성한다.
--관리자 입장
--1.관리자는 해당 카테고리별로 등록/수정/삭제 가능
--2.1차 카테고리만 보기
----2차 카테고리보기(전제:어떤 1차 카테고리를 선택했느냐)


--사용자 입장
--1.사용자는 각각의 카테고리별로 볼 수 있어야 한다.
----1차 카테고리만 보기
----2차 카테고리보기(전제:어떤 1차 카테고리를 선택했느냐
)

Trackbacks 0 | Comments 0

-- ** 페이징 쿼리 **
-- WHERE NUM BETWEEN (페이지사이즈 *(현재페이지번호-1) + 1)
-- AND (현재 페이지 * 페이지 사이즈)
컬럼명 BETWEEN A AND B
SELECT * FROM MT_USERINFO
WHERE NUM BETWEEN 10 AND 20

SELECT * FROM MT_USERINFO
WHERE NUM >= 10 AND NUM <= 20
SELECT * FROM MT_USERINFO
WHERE NUM NOT IN(20,19)

--총페이지 개수 ==> 20
SELECT COUNT(*) FROM MT_USERINFO
1. 한페이지당 보고자 하는 페이지 개수
몇개씩 볼래요
==> 페이지 사이즈 : 5
2. 몇 페이지의 내용을 볼래요?
==> 현재 페이지 번호 : 2
SELECT TOP 5 * FROM MT_USERINFO
WHERE NUM BETWEEN (5*(3-1)+1) AND (3*5)

--(페이지사이즈 *(현재페이지번호-1) + 1)
-- AND (현재 페이지 * 페이지 사이즈)
SELECT * FROM MT_USERINFO
---------------------------------------------------------
-- INNER JOIN
--취미 테이블
컬럼
1) NUM ==> MT_USERINFO의 NUM과 동일
2) SEQ ==> 취미테이블의 내부 일련번호
4) 취미
CREATE TABLE T_HOBBY
(
 NUM INT NOT NULL
, SEQ CHAR(1) NOT NULL
, HOBBY VARCHAR(100) NULL
)
---------------------
SELECT * FROM MT_USERINFO
SELECT * FROM T_HOBBY

INSERT T_HOBBY
VALUES(1,'1', '여자꼬시기')

INSERT T_HOBBY
VALUES(20,'1', '남자꼬시기')
INSERT T_HOBBY
VALUES(20,'2', '꼬셔서뜯어먹기')

NUM =1

INNER JOIN (두개의 테이블을 결합 시킨다)
---------------------------
SELECT USER_NAME, HOBBY
FROM MT_USERINFO
INNER JOIN
T_HOBBY
ON(MT_USERINFO.NUM = T_HOBBY.NUM)
WHERE MT_USERINFO.NUM = 1
--------------------------------------------------
SELECT USER_NAME, HOBBY
FROM MT_USERINFO MT
INNER JOIN
T_HOBBY TH
ON(MT.NUM = TH.NUM)
WHERE TH.NUM = 1

Trackbacks 0 | Comments 0

/*
1. 테이블
: 컬럼과 로우(행)으로 이루어져 있다.
==> 행안에는 데이터가 들어가 있다.
====> 컬럼에 들어가는 데이터는 각각의 자료형을 가지고 있고
그 자료형에 맞는 데이터가 들어가 있다.
*/
-- 1.1 테이블 만들기
-- 컬럼을 만들자 -> 컬럼명, 테이터 타입
create table T_HOBBY
(
    NUM  int, --> 숫자형 NUM 컬럼 선언
    SEQ  int,
    HOBBY varchar(50),
)
-- 1.2 테이블 수정하기
-- 컬럼 추가하기
alter Table T_HOBBY
add seq int

alter table t_hobby
add hobby varchar(50)
-- 컬럼 수정하기
alter table t_hobby
alter column hobby varchar(100)
exec sp_mp t_hobby
-- 컬럼 삭제하기
alter table t_hobby
drop column hobby
-- 1.3 테이블 삭제하기
DROP TABLE T_HOBBY
-----------------------------------
-- 2. 테이블을 좀더 상세하게 만들기
-- 2.1 컬럼에 NULL 허용 여부
create table t_1
(
 id int not null,
 city varchar(10) null,
 name varchar(10) null
)
select * from t_1
-- 2.2 테이블에 기본키와 기본값과 자동 증가값 주기
create table t_1
(
 id int not null identity(1,1) primary key,
 city varchar(10) default '부산',
 name varchar(10) not null
)
-- 3. 테이블에 데이터 입력하기
-- 형식) insert 테이블명 values(데이터)
1. '부산', '홍길동'
insert t_1 values(2, '부산', '홍길동')
insert t_1 values(2, '부산', 'ㅂㅂㅂ')
----------------------------------------------
-- 기본키와 아이덴티티가 적용된 데이터 삽입하기
insert t_1 values(default, '홍길동')

insert t_1 (name) values('홍길동')
insert t_1 (city, name) values('서울', '홍길동')
-- C#
-- string.format("지역 : {0} 이름 {1}", '서울', '홍길동')

insert t_1 (city, name) values (5, '서울', '홍길동')

select * from t_1

-- 3. 테이블에 데이터 입력하기
-- 형식) update 수정할 테이블명 set 수정할 컬럼명 = 수정할 값
-- where 조건
update t_1
set city = '광주', name= '윽'
where id = 2

-- 4. 테이블에 데이터 삭제하기
-- 형식) delete 삭제할 테이블명 where 조건
delete t_1 where id = 2

Trackbacks 0 | Comments 0

--1. select 컬럼명~~, (*) from 테이블
-- order by
--2. 함수
--2.1 날짜 함수
--2.2 문자 함수
--2.3 수치 함수

--1) AVG : 평균을 구하는 함수.
--2) count(*) : 각 행의 총 개수를 구하는 함수.
--3) MAX :  최대값을 반환 하는 함수.
--4) MIN : 최소값을 반환하는 함수.
--5) SUM : 총합계를 반환하는 함수.
-- ==> GROUP BY, HAVING ==> 하의질의, 또는 상관관계 하의 질의

--1.AVG
SELECT AVG(USER_SCORE)
FROM MT_USERINFO
--==>기본적으로 NULL값은 제외시킨다.
SELECT USER_NAME, USER_SCORE
FROM MT_USERINFO

-->ISNULL
::: ISNULL(컬럼명OR데이터,기본값)
DECLARE @PIOZEN INT
SET @PIOZEN=NULL
SELECT ISNULL(@PIOZEN,0)

--null 값제외한 값들만 호출해보았다.
Select *
FROM MT_USERINFO
where USER_EMAIL is not null

--null을 0으로 바꾸어 호출해보았다.
SELECT AVG(ISNULL(USER_SCORE,0))
FROM MT_USERINFO


2) COUNT(*)
select count(*)
FROM MT_USERINFO
where USER_EMAIL is  null

3) MAX
select MAX(user_score)
FROM MT_USERINFO

SELECT MAX(NUM)
FROM MT_USERINFO

BEGIN TRAN

ROLLBACK

SELECT ISNULL(MAX(NUM),0)+1
FROM MT_USERINFO

4) MIN
select MIN(user_score)
FROM MT_USERINFO

5) SUM
select SUM(user_score)
FROM MT_USERINFO

SELECT COUNT(*)AS 남구
FROM MT_USERINFO
WHERE USER_ADDRESS like '%남구%'


--SELECT TOP n FROM 테이블명 : n => 숫자

SELECT TOP 10 NUM, USER_NAME
FROM MT_USERINFO

-- ORDER BY
-- 데이터의 정렬
-- 내림차순 : DESC 오름차순 : ASC

Select *
FROM MT_USERINFO

Select TOP 1 *
FROM MT_USERINFO
ORDER BY USER_AGE DESC

Select TOP 1 *
FROM MT_USERINFO
ORDER BY NEWID()
--==> NEWID()는 반드시 ORDER BY 와 함께 사용되어야 한다. 무작위로 값을 추출한다.

데이터의 형변환

CONVERT(), CAST()
1)CONVERT(바꿀 자료형, 컬럼또는 데이터, 스타일)) ==> 내가 원하는 자료형으로 바꾼다.
1.1)CONVERT(바꿀 자료형, 컬럼또는 데이터, 스타일))

DECLARE @NUM INT
SET @NUM = 1
SELECT @NUM + '점'
1점

DECLARE @NUM INT
SET @NUM = 1
SELECT CONVERT(VARCHAR(10),( @NUM))+ '점'

--스타일을 포함시킨 CONVERT
DECLARE @DATE DATETIME
SET @DATE = '2008-02-27 00:00:00'
SELECT CONVERT(VARCHAR(10), @DATE, 112)

DECLARE @DATE DATETIME
SET @DATE = '2008-02-27 00:00:00'
SELECT CONVERT(VARCHAR(10), @DATE, 120)

DECLARE @DATE DATETIME
SET @DATE = '2008-02-27 00:00:00'
SELECT CONVERT(VARCHAR(10), @DATE, 108)

SELECT *
FROM HOBBY
SELECT *
FROM MT_USERINFO

Trackbacks 0 | Comments 0

-- 2008년 3월 25일
-- 1. 함수
-- 1) 일반 함수
-- 2) 날짜 함수
-- 3) 문자 함수

-- 문자함수
--len(컬럼값 또는 변수값) ==> 해당 문자열의 길이를 돌려준다.

--변수 정의
declare @alphadog varchar(200)
--값을 할당
set @alphadog = '알파독'
-- 할당된 값을 출력
select @alphadog
select len(@alphadog) as 길이

select len(user_jumin)
from mt_userinfo

--left(컬럼값 또는 검색문자열, 이동수) ==> 해당 문자열 중 왼쪽에서 원하는 값을 돌려줌
declare @alphadog varchar(200)
set @alphadog = 'ABCDEFGH'
select left(@alphadog, 2)

select left(user_jumin, 6) as '줄인주민'
from mt_userinfo

--right(컬럼값 또는 검색문자열, 이동수) ==> 해당 문자열 중 오른쪽에서 원하는 값을 돌려줌
declare @alphadog varchar(200)
set @alphadog = 'ABCDEFGH'
select right(@alphadog, 5)

select right(user_jumin, 7) as '줄인주민', len(right(user_jumin, 7)) as '글자수'
from mt_userinfo

select left(user_jumin, 6) + ' - ' + right(user_jumin, 7) from mt_userinfo

-- ltrim() : 왼쪽 문자열의 공백을 제거
declare @alphadog varchar(200)
set @alphadog = '     AAAAAA'
select ltrim(@alphadog)

-- rtrim() : 오른쪽 문자열의 공백을 제거
declare @alphadog varchar(200)
set @alphadog = 'AAAAAAA      '
select rtrim(@alphadog)

-- replace(컬럼, 원본문자, 바꿀문자) : 문자열중에 내가 원하는 문자를 특정 문자로 바꾼다
declare @alphadog varchar(200)
set @alphadog = '백원짜리'
select replace(@alphadog, '백', '십')

-- 공백문자 제거
declare @alphadog varchar(200)
set @alphadog = '        AA A       B  B           '
--select replace(rtrim(ltrim(@alphadog)), ' ', '')
select replace(@alphadog, ' ', '')

-- substring(컬럼, 시작점, 가져올글자수) : 문자열중 원하는 위치에서 원하는 글자수를 자른다
-- 즉 어떤 글자를 어디서부터 몇글자 자를래?
-- C# => ABCDEFG : B글자의 위치 : 1
-- DB => ABCDEFG : B글자의 위치 : 2
declare @alphadog varchar(200)
set @alphadog = 'ABCDEFGH'
select substring(@alphadog, 2, 3)

-- like 문자열 검색

select *
from mt_userinfo
where user_address like '%남구%'

-- patindex(정규식, 컬럼) : 문자열 중에서 내가 원하는 글자를 찾아서
-- 그 글자를 몇번째 위치에 있는지를 알아 온다.
declare @alphadog varchar(100)
set @alphadog = 'alphadog@paran.com'
select patindex('%@%', @alphadog)

-- 문자열 자르기
select user_email, left(user_email, patindex('%@%', user_email)-1) as 닉네임,
right(user_email, len(user_email)-patindex('%@%', user_email)) as 메일서버 from mt_userinfo

select user_email, (substring(user_email, 1, patindex('%@%', user_email)-1)) as [메일 앞아이디],
(substring(user_email, patindex('%@%', user_email)+1, len(user_email))) as [메일 뒷아이디]
from mt_userinfo

-- uppper, lower : 주어진 문자열을 대/소문자로 바꾸기
declare @alphadog varchar(100)
set @alphadog = 'abcDEF'
select upper(@alphadog), lower(@alphadog)

Trackbacks 0 | Comments 0

USE PUBS
-- pubs 데이터 베이스를 사용하겠다.
-- f5 해당 구문을 실행한다.

-- :: DB에서 한줄 주석
-- :: /////////////////////////////////////////

select title_ID
from titles
-- 컬럼명 바꾸기

select title_id,title
from titles

select title_id as [책번호],title as [제목]
from titles

select '12345'

-- 함수

1) 날짜함수

--GETDATE(); 현재 날짜를 돌려주는 함수

SELECT GETDATE() as [현재날짜];

--함수이름();

-- DATEADD() : 날짜를 더하는 함수
-- 어느 날짜에
-- 년 또는 월 또는 일
select DATEADD(day, 21, GETDATE())
select DATEADD(day, 100, '2008-03-23')

--년
select DATEADD(year, 10, GETDATE())
select DATEADD(yy, 10, GETDATE())
--월
select DATEADD(month, 10, GETDATE())
select DATEADD(mm, 10, GETDATE())
--일
select DATEADD(day, 10, GETDATE())
select DATEADD(dd, 10, GETDATE())

select pubdate from titles
select DATEADD(DAY, 10, PUBDATE) as [더한날짜],PUBDATE from titles

-- DATEDIFF() : 날짜 사이의 반환 값을 돌려줌

SELECT DATEDIFF(DAY, '2008-02-27', GETDATE())
SELECT DATEDIFF(DAY, PUBDATE, GETDATE())
FROM TITLES

select pubdate from

-- DATEPART() : 날짜의 일부를 보여줌

select datepart(year, getdate())
select datepart(month, getdate())
select datepart(day, getdate())

-- dw 파라메터 : 요일을 표시해줌. 1~7 값을 반환, 1 : 일요일, 7 : 토요일
select datepart(dw, getdate())
select datepart(dw, pubdate)as DW from titles

select * from titles

select title
from titles
where YTD_SALES >=3000

select count(*)
from titles
where YTD_SALES >=3000

select *
from titles

select *
from titles
where YTD_SALES>=3000 and YTD_SALES<=4000

select count(*)
from titles
where YTD_SALES>=3000 and YTD_SALES<=4000

select * from titles
where YTD_SALES between 3000 and 4000

--컬럼명 BETWEEN A AND B
--컬럼명 A와 B사이의 데이터
-- ()는 함수형으로 변환시키므로 에러가 생긴다.(BETWEEN은 연산자다.)

select * from titles
where YTD_SALES not between 3000 and 4000

-- IN : 원하는 형태의 데이터만 직접 선택

SELECT * FROM TITLES
WHERE ROYALTY =10 OR ROYALTY=12
-- 성능에 부하가 생기므로 or은 사용금지

SELECT * FROM TITLES
WHERE ROYALTY IN (10,12)

SELECT * FROM TITLES
WHERE ROYALTY NOT IN (10,12)

SELECT * FROM TITLES WHERE TYPE IN('BUSINESS','MOD_COOK')

SELECT * FROM TITLES WHERE ROYALTY IS NULL

SELECT * FROM TITLES WHERE ROYALTY IS NOT NULL

SELECT COUNT(*) FROM TITLES WHERE ROYALTY IS NULL

SELECT COUNT(*) FROM TITLES WHERE ROYALTY IS NOT NULL

-- ISNULL 은 IS 와 NULL을 떨어뜨려서 써야한다.


Trackbacks 0 | Comments 0

microdesk's Blog is powered by Daum & tistory