posted by 생존본능 2011.05.18 12:13


--allen보다 월급이 높은사람의 이름과 월급 출력

select SAL
from emp
where ENAME = 'allen'

select ENAME, SAL
from emp
where SAL >=( select SAL from emp where ENAME = 'allen' );

select ENAME, SAL
from EMP
where SAL in(( select SAL from emp where JOB = 'salesman' ))

select ENAME, SAL
from EMP
where SAL > ( select MAX(SAL)  from EMP  where DEPTNO = 20);

--all 연산자 조건문에서 안에들어있는 모든값을 포함.
select ENAME, SAL
from EMP
where SAL > all( select SAL  from EMP  where DEPTNO = 20);

--any 연산자조건 안에들어있는 값중 하나라도 만족하는경우
select ENAME, DEPTNO
from EMP
where deptno = any(select DEPTNO from EMP where ename like '%T%')

'개발 > SQL' 카테고리의 다른 글

MS-SQL 문제 12문  (0) 2011.05.18
MS - SQL 수정문법 [ insert into , insert ~ select ]  (0) 2011.05.18
MS-SQL 조건문  (0) 2011.05.18
MS-SQL 기본문제 9문  (0) 2011.05.18
MS-SQL 정렬문법 [order by , top ]  (0) 2011.05.18
MS-SQL 기초문제  (0) 2011.05.18
posted by 생존본능 2011.05.18 12:12


-- 1. 사원들의 이름, 부서번호, 부서이름을 출력하라.
select ENAME, emp.DEPTNO, DNAME
from emp, DEPT
where emp.DEPTNO = DEPT.DEPTNO


-- 2. DALLAS에서 근무하는 사원의 이름, 직위, 부서번호, 부서이름을
-- 출력하라.

select ENAME, JOB, emp.DEPTNO, DEPT.DNAME
from emp, dept
where dept.LOC = 'DALLAS'
 and emp.DEPTNO = DEPT.DEPTNO

-- 3. 이름에 'A'가 들어가는 사원들의 이름과 부서이름을 출력하라.
select ENAME, DEPT.DNAME
from emp, DEPT
where emp.DEPTNO = DEPT.DEPTNO
 and ENAME like 'A%'

-- 4. 사원이름과 그 사원이 속한 부서의 부서명, 그리고 월급을
--출력하는데 월급이 3000이상인 사원을 출력하라.

select ENAME,DNAME, SAL
from emp, DEPT
where SAL >= 3000
 and emp.DEPTNO = DEPT.DEPTNO

-- 5. 직위가 'SALESMAN'인 사원들의 직위와 그 사원이름, 그리고
-- 그 사원이 속한 부서 이름을 출력하라.

select JOB, ENAME, DNAME
from EMP, DEPT
where JOB = 'SALESMAN'
 and EMP.DEPTNO = DEPT.DEPTNO

-- 6. 커미션이 책정된 사원들의 사원번호, 이름, 연봉, 연봉+커미션,
-- 급여등급을 출력하되, 각각의 컬럼명을 '사원번호', '사원이름',
-- '연봉','실급여', '급여등급'으로 하여 출력하라.

select EMPNO     as [사원번호]
,  ENAME     as [이름]
,  SAL*12     as [연봉]
,  SAL*12+isnull(COMM,0) as [실급여]
,  GRADE     as [급여등급]
from emp,DEPT, SALGRADE
where emp.DEPTNO = DEPT.DEPTNO
 and emp.SAL >= SALGRADE.LOSAL
 and emp.SAL <= SALGRADE.HISAL

-- 7. 부서번호가 10번인 사원들의 부서번호, 부서이름, 사원이름,
-- 월급, 급여등급을 출력하라.
 
 select emp.DEPTNO, DNAME, ENAME, SAL, GRADE
 from EMP, DEPT, SALGRADE
 where emp.DEPTNO = DEPT.DEPTNO
 and SAL >= LOSAL
 and SAL <= HISAL
 and dept.DEPTNO = '10'
 
 

-- 8. 부서번호가 10번, 20번인 사원들의 부서번호, 부서이름,
-- 사원이름, 월급, 급여등급을 출력하라. 그리고 그 출력된
-- 결과물을 부서번호가 낮은 순으로, 월급이 높은 순으로
-- 정렬하라.

select dept.DEPTNO, dept.DNAME, ENAME, SAL, GRADE
from EMP, DEPT, SALGRADE
where emp.DEPTNO = DEPT.DEPTNO
 and SAL between LOSAL and HISAL
 and (emp.DEPTNO = 10 or emp.DEPTNO = 20)
order by emp.DEPTNO asc, SAL desc


-- 9. 사원번호와 사원이름, 그리고 그 사원을 관리하는 관리자의
-- 사원번호와 사원이름을 출력하되 각각의 컬럼명을 '사원번호',
-- '사원이름', '관리자번호', '관리자이름'으로 하여 출력하라.

select e.EMPNO  as [사원번호]
,  e.ENAME  as [사원이름]
,  f.DEPTNO as [관리자번호]
,  f.ENAME  as [관리자이름]
from EMP e, EMP f
where e.MGR = f.EMPNO

'개발 > SQL' 카테고리의 다른 글

MS - SQL 수정문법 [ insert into , insert ~ select ]  (0) 2011.05.18
MS-SQL 조건문  (0) 2011.05.18
MS-SQL 기본문제 9문  (0) 2011.05.18
MS-SQL 정렬문법 [order by , top ]  (0) 2011.05.18
MS-SQL 기초문제  (0) 2011.05.18
MS-SQL 문법 [ DATE ]  (0) 2011.05.18
posted by 생존본능 2011.05.18 12:10

-- order by 오름차순 desc입력시 내림차순.
select ename, sal
from emp
order by sal

select job, avg(sal) as [avgsal]
from EMP
group by JOB
order by avgsal desc

--top 상위 5개만 보여줌.
select top 5 ename, SAL
from emp
order by SAL desc

select top 30 percent ename, SAL
from EMP
order by SAL desc

--with ties 동률. 같은값이 있는경우에는 같이 표시.
select top 2 with ties ename, SAL
from EMP
order by sal desc


--group by 이후 처리가 필요할경우 having으로 처리하여줌.
select job , AVG(sal) as [평균연봉]
from emp
where job != 'clerk'
group by job
having avg(SAL) >= 3000
order by avg(sal)

--with rollup 시 이전 값에대한 전체값을 리턴.
select job, deptno, avg(sal)
from emp
group by deptno,job with rollup


select deptno, job, sal
from emp
order by DEPTNO desc
compute avg(sal) by deptno

'개발 > SQL' 카테고리의 다른 글

MS-SQL 조건문  (0) 2011.05.18
MS-SQL 기본문제 9문  (0) 2011.05.18
MS-SQL 정렬문법 [order by , top ]  (0) 2011.05.18
MS-SQL 기초문제  (0) 2011.05.18
MS-SQL 문법 [ DATE ]  (0) 2011.05.18
MS-SQL 숫자,텍스트,문법 [ in , like , Math ]  (0) 2011.05.18
posted by 생존본능 2011.05.18 12:09

--1. 사원테이블에서 부서별 최대 월급을 출력하라.

select deptno,max(sal) as [최대월급]
from emp
group by deptno

--2. 사원테이블에서 직위별 최소 월급을 구하되 직위가
-- CLERK인 것만 출력하라.

select job,min(sal) as [최소월급]
from emp
where job = 'clerk'
group by job

--3. 커미션이 책정된 사원은 모두 몇 명인가?

select count(comm)
from emp

--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 sum(sal)

--9. 직위별 총월급을 출력하되, 직위가 'MANAGER'인
-- 사원들은 제외하라. 그리고 그 총월급이 5000보다
-- 큰 직위와 총월급만 출력하라.

select job, sum(SAL) as [합계]
from emp
where job != 'manager'
group by job
having sum(SAL) > 5000

--10. 직위별 최대월급을 출력하되, 직위가 'CLERK'인
-- 사원들은 제외하라. 그리고 그 최대월급이 2000 이상인
-- 직위와 최대월급을 최대 월급이 높은 순으로 정렬하여
-- 출력하라.

select job,max(sal) as[최대월급]
from emp
where job != 'clerk'
group by job
 having max(sal) > 2000
order by max(sal) desc

--11. WITH ROLLUP  구문을 이용하여 직위별 사원의 이름과
-- 월급, 그리고 직위별 월급의 합을 구하라.

select sal, job, ename , sum(sal)
from emp
group by job,ename,sal with rollup

posted by 생존본능 2011.05.18 12:07

select GETDATE()+10 --10일후로 입력.
dateadd
/*년 yy
월 mm
일 dd
시 hh
분 mi
초 ss,
dy(day of year), dw(day of week), qq
*/
select DATEADD(yy,5,GETDATE());
select DATEDIFF(dd,'2011-12-31',GETDATE()); -- 년도끼리 연산.

select DATENAME(DW,GETDATE()); --문자형반환. /목요일
select DATEPART(DW,GETDATE()); --숫자형 반환 /5일

select YEAR(GETDATE()); --GETDATE에서 YEAR값리턴.
select MONTH(GETDATE()); --GETDATE에서 MONTH값리턴.
select DAY(GETDATE()); -- GETDATE에서 DAY값리턴.

--정수형 문자형을 합치는 방법.
select ename, empno --ename은 문자형 empno가 숫자형일때
from emp;

--CONVERT로 문자형으로 형변환.
select ename + CONVERT(varchar(10), empno)
from emp

--CAST로 문자형으로 형변환.
select ename + CAST(empno as VARCHAR(10))
from emp

--년월일 표기식 변경.
select convert(varchar(20),GETDATE(),101); -- 미국표기식 년월일.
select convert(varchar(20),GETDATE(),102); -- ansi식 년월일.
select convert(varchar(20),GETDATE(),112); -- ios식 년월일


--집계함수는 null을 배제하고 사용한다.
/*
평균 avg
최대값 max
최소값 min
합계 sum
  count
*/

--empno안에 총 몇가지 값이 있는지 갯수 체크.
select count(isnull(empno,0))
from emp

select SUM(comm) as [합계]
from emp

select AVG(isnull(comm,0)) as [평균]
from emp

--clerk를 제외
select job, AVG(sal) as [합 계]
from emp
where job != 'clerk'
group by job

select MAX(SAL)
from emp

select min(sal)
from emp

select job, deptno,avg(sal)
from emp
group by job

posted by 생존본능 2011.05.18 12:07

--distinct
중복제거

select distinct job
from emp


--exec sp_help
시스템 내장변수
테이블정보.
exec sp_help emp

비트 연산
2진수변환


--in
Select ename, sal, deptno, job
from emp
where deptno in(10,20)

10보다 크고 20보다 적다.

--like
select ename, sal
from emp
where ename like '%e%'

문자열에서 시작하는 위치

where ename like '_a%' 두번째가 a인사람
where ename like '[as]%' a나 s로 시작하는 사람.
where ename like '[^s]%' s로 시작하지 않는 사람.

 

select reverse('abcdef') as reverse; // 역방향정렬
select replace('abcdef','bc','kkk'); // bc를 kkk로 대체
select STUFF('abcdef',2,3,'kkk'); // 2~3번까지를 kkk로 대체
select round(123.456,1); // 소수점이하 1자리까지 출력
select round(123.456,-1); // 1번자리까지 출력
select power(2,3); // 제곱
select ceiling(123.456);//124 가로안에 있는 숫자보다 큰 정수 출력.
select ceiling(-123.456); //-123가로안에 있는 숫자보다 큰 정수 출력.
select floor(123.456);// 123 // 보다 작은 최대정수
select floor(-123.456);//-124 // 보다 작은 최대정수
select sqrt(9);//제곱근함수(루트)
select SQUARE(9);//81
select abs(100);//절대값
select abs(-100);//절대값


select reverse('abcdef') as reverse;
select replace('abcdef','bc','kkk') as replace;
select STUFF('abcdef',2,3,'kkk') as STUFF;
select round(123.456,1) as round;
select round(123.456,-1) as round;
select power(2,3) as power;
select ceiling(123.456) as ceiling;
select ceiling(-123.456) as ceiling;
select floor(123.456) as floor;
select floor(-123.456) as floor;
select sqrt(9) as sqrt;
select SQUARE(9) as SQUARE;
select abs(100) as abs;
select abs(-100) as abs;


//소스
--select * from emp

/*select empno, ename
from emp
go*/
/*
use master

select *
from test408.dbo.emp
*/

/*select 7+5
select 7*4
select 'tom' + 'bill'*/

select ename as [사원이름], deptno as [부서이름]
from emp

select distinct job
from emp

exec sp_help emp

Select empno, sal, sal*12 As [연봉]
From emp

Select ename, sal, comm, sal+isnull(comm,0)
From emp

Select ename, sal, deptno, job
from emp
where job = 'salesman'

Select ename, sal, deptno, job
from emp
where sal >= 1500 and sal < 3000

Select ename, sal, deptno, job
from emp
where deptno in(10,20)
--where sal between 1500 and 3000

select ename, sal
from emp
where ename like '[s]%'

posted by 생존본능 2011.05.18 12:06

--pivot
select YEAR, [1] as '1분기', [2] as '2분기', [3] as '3분기', [4] as '4분기'
From (select YEAR, quater, amount from PivotT) as t1
pivot( sum(amount)
  for quater in ([1], [2], [3], [4]) ) as pvt;

PIVOT (집계함수(값이있는 열의 이름)
  FOR 피벗하고자하는열 in (목록))


--ROW_NUMBER()
SELECT Year
,  Part
,  quater
,  amount
,  ROW_NUMBER() OVER(ORDER BY 타겟 DESC) AS 'Row Nmber'
FROM PivotT
go

모든 개체의 넘버를 빠지는 번호없이 순서를 정하는 함수.


--RANK()
SELECT Year
,  Part
,  quater
,  amount
,  RANK() OVER(ORDER BY 타겟 DESC) AS 'rank'
FROM PivotT
go

개체의 타겟을 기준으로 순위를 매김.


--DENSE_RANK()
DENSE_RANK() OVER (ORDER BY amount DESC) as 'densh'

중복된 다음의 넘버가 건너뛰는 번호없이 모두 표시됨.


--NTILE(넘버)
NTILE(5) OVER (ORDER BY amount DESC) as 'tile'

0부터 넘버에 표시되는 숫자까지의 수를 균등하게 적용하여 표시됨.


--partition by
RANK() OVER(partition by 타겟 ORDER BY amount DESC) AS 'rank'

표시된 타겟을 같은수끼리 영역으로 묶어 영역별 순위를 매김.

 

 

 

 

 

/*
INSERT PivotT (Year,Part,quater,amount)
values (2001,'g',3,2.7);
*/

SELECT *
FROM PivotT
go

SELECT Year
,  Part
,  quater
,  amount
,  ROW_NUMBER() OVER(ORDER BY amount DESC) AS 'Row Nmber'
,  RANK() OVER(partition by Year ORDER BY amount DESC) AS 'rank'
,  DENSE_RANK() OVER (ORDER BY amount DESC) as 'densh'
,  NTILE(5) OVER (ORDER BY amount DESC) as 'tile'
FROM PivotT
go

posted by 생존본능 2011.05.18 12:05

 

--WHERE 타겟 is not null
null인값은 표시하지 않음.

--group by
같은 데이터를 한데 묶음.

group by Title
Title이 같은 값을 한곳에 모음.

--having
묶인 그룹내에 검색추가.

SELECT 
  Title
,  COUNT(Title) as cnt
FROM Person.Contact
group by Title
HAVING Title = 'Ms.'
or Title = 'Mr.'


--with rollup
전체리스트의 합계라인추가.

SELECT
  CountryRegionName, StateProvinceName, city, COUNT(1) as cnt
FROM
  HumanResources.vEmployee
group by CountryRegionName, StateProvinceName, City
with rollup
order by CountryRegionName, StateProvinceName;

 

--GROUPING 그룹처리될때 원본인지 모여있는 값인지 표시.
SELECT
  CountryRegionName, StateProvinceName, city, COUNT(1) as cnt
,  GROUPING(CountryRegionName) as c1
,  GROUPING(StateProvinceName) as c2
,  GROUPING(City)    as c3
FROM
  HumanResources.vEmployee
group by CountryRegionName, StateProvinceName, City
with rollup
order by CountryRegionName, StateProvinceName;
위와 같을때는
c1에는 '0'(원본) 또는 '1'(모인값)로 표시됨.

 

when(GROUPING(CountryRegionName) = 1)then '총계'
when(GROUPING(StateProvinceName) = 1)then '국별합계'

posted by 생존본능 2011.05.18 12:04

--============== 검색 =========================

/*
SELECT A FROM B
WHERE C = 'D'
A - (어떤값을 표시할지)
B - (어디서)
C - (어떤이름을)
D - (어떤값과 같은지 검색.)
SELECT id,address FROM customer
*/


--null구문
SELECT *
 FROM customer
 where email is null
 or email = ' '


null의경우 email = null의 방식이 아닌 is null로 검색한다.

--COUNT갯수체크
SELECT COUNT(*)
 FROM Production.Product

특정값의갯수를체크


--DISTINCT
SELECT COUNT(DISTINCT ListPrice)
 FROM Production.Product

DISTINCT를 앞에사용할경우 중복된 값은 제외된 갯수가 체크된다.


--검색된값 수정
SELECT ProductID, '['+Name+']', ListPrice
 from Production.Product
위와 같이 검색할경우 검색된 값에 '[]' 가 붙어서 나오게된다.


--============ LINK ========================

--415로시작하는 phone검색
SELECT * FROM Person.Contact
WHERE Phone LIKE '415%'

--415로끝나는 phone검색
WHERE Phone LIKE '%415'

/*
 '%415%' % 로 끝에 붙어있는 값을검색할지 앞에있는값을 검색할지 설정.
*/

 

--=========== 논리연산 ======================

--부분검색 Firstname이 'kim' 인 사람검색.
SELECT * FROM Person.Contact
WHERE FirstName = 'kim'


--and == && 두가지조건이 참인경우.
SELECT *
 FROM Person.Contact
 WHERE FirstName = 'kim'
 and LastName = 'Abercrombie'
FirstName이 'kim' 이고
LastName은 'Abercrombie' 인경우


--or == || 두가지조건중 한가지가 참인경우.
SELECT *
 FROM Person.Contact
 WHERE FirstName = 'kim'
 or LastName = 'Abercrombie'
FirstName이 'kim'이거나
LastName이 'Abercrombie'인경우

 

--not == ! 조건의 반대경우

SELECT *
 FROM Person.Contact
 WHERE not( FirstName = 'kim')

또는 WHERE not FirstName != 'kim'

FirstName이 'kim'이 아닌경우


--기본 논리연산
SELECT *
 FROM Production.Product
 WHERE StandardCost >= 100
 and StandardCost <= 120

기본논리연산도 사용가능.


--=========== 논리연산구문 ======================
기본논리연산과 유사하나, 더 간편하게 사용가능하다


-- BETWEEN(범위) n보다 크고 s보다작다
SELECT *
 FROM Production.Product
 WHERE StandardCost BETWEEN 100 and 120

WHERE StandardCost >= 100
and StandardCost <= 120
BETWEEN방식은 위방식과 동일하다.


--in
SELECT *
 FROM Production.Product
 WHERE ProductID in (515, 521)

WHERE ProductID == 515
or ProductID == 521
in 방식은 위방식과 동일하다.

 

--=========== 정렬 ======================
--order by

SELECT *
 FROM Production.Product
 order by Name

값을 기준으로 리스트 정렬하여 보여준다.
기본값은 오름차순이며

내림차순으로 할경우
order by Name desc
처럼 뒤에 desc를 붙여주면 된다.

 

SELECT ProductID,Color,Name
 FROM Production.Product
 order by Color, Name desc

여러정렬을동시에 할경우 "," 를 사용하여준다.


티스토리 툴바