테이블 생성
create table sawon
(
sa_no int not null,
sa_irum nvarchar(10),
dept_no int not null,
jik nvarchar(10) default '사원',
pay int,
ibsail datetime default getdate(), /* getdate() 오늘날짜 */
sa_sex varchar(4),
mgr int
)
-------------------------------------------------------------------
데이터 추가
insert into tb_member values
('wonbin','123456','원빈',810000,1000000,'wonbin@naver.com','135-902','서울시 강남구 압구정','010-333-444',getdate(),1)
-------------------------------------------------------------------
데이터 업데이트
update com_man set m_name='장동권', m_h='영화' where m_name='가'
update com_man set m_h=null where num=4 /* num=4를 null로 바꾼다 */
update com_man set m_h='작업' where (m_h is null) or (m_h='')
update com_man set m_name='원빈' , c_id=3, m_h=null where num=6
-------------------------------------------------------------------
조건 검색 정렬
select sa_irum as '이름',pay as '급여',floor(pay*0.7) as '보너스', floor((pay*12)+(pay*0.7)*6 ) as '연봉' from sawon
--오늘 날짜에 3년을 더하라
select dateadd(year, 3, getdate()) as '3년후'
-- TB_member 에서 이름과 나이를 출력
select s_name as '이름',datediff(year,'19'+left(zumin1,2),getdate()) as '나이' from tb_member
-- 입사일로 부터 30년을 더한값을 정년일로 하여 정년일 출력
select dateadd(year, 30, ibsail) as '정년일' from sawon
select id as '코드번호' from tb_com where com_name='삼성전자'
select com_name from tb_com where id <= 4 order by com_name desc /* 내림차순 */
select com_name, tel, id from tb_com where com_name like '한%'
select tel from tb_com where tel like ('%1111') or (tel is null)
select * from sawon where jik=(select jik from sawon where sa_irum='최진실')
select * from sawon where pay > (select avg(pay) from sawon)
select
dept_no as '부서', sa_irum as '이름' from sawon where dept_no=(select
dept_no from sawon where sa_irum='최진실') order by dept_no, sa_irum
select * from sawon where pay=(select max(pay) from sawon where jik='과장') and jik='과장'
select count(distinct p_id) as '상품 수' from tb_order -- 상품 갯수 확인(중복 제거 distinct)
select sum(su) as '판매된 상품수' from tb_order -- 상품수
select
jik as '직책', avg(pay) as '직책별 급여 평균' , count(jik) as '직책별 사원 수' ,
sum(pay) as '직책별 급여 합' from sawon where jik<> '사장' group by jik
having count(jik) > 4
select p_id as '주문량 10개 이상 상품' , sum(su) as '수량' from tb_order group by p_id having sum(su) >= 10
select count(ibsail) as '사원수' , year(ibsail) as '입사년도' from sawon group by year(ibsail)
select
dept_no as '부서번호',sum(pay) as '급여총합' from sawon where dept_no<30
group by dept_no select dept_no as '부서번호',sum(pay) as '급여총합' from sawon
group by dept_no having dept_no<30
select tb_order.p_id as '제품번호', p_name as '제품명',go_no as '고객번호',sa_irum as '사원이름', dept_name as '부서명' from tb_order, products, sawon, dept where (tb_order.p_id=products.p_id) and (tb_order.go_no=sawon.sa_no) and (sawon.dept_no=dept.dept_no)
select p_name as '제품명' , p_price as '금액', sa_irum as '주문자' , go_no as '고객번호' from products, tb_order, sawon where (products.p_id=tb_order.p_id) and (sawon.sa_no=tb_order.go_no) and (p_price > 100000)
-------------------------------------------------------------------
데이터 삭제
delete tb_member where cur is null
-------------------------------------------------------------------
프로시져
create proc a_dd @add nvarchar(50) as
select * from ziptable where addr like '%'+@add+'%'
a_dd '청파동2가'
-----------------------
create proc input @input int, @input2 nvarchar(20), @input3 nvarchar(50), @input4 nvarchar(30) as
insert into tb_com values(@input,@input2,@input3,@input4)
input 7,'인텔','미국','001'
-----------------------
create proc p_hpsu
@hp nvarchar(20) as
select @hp as '통신번호',count(*) as '회원수' from tb_member where hp like @hp+'%'
p_hpsu '010'
-------------------------------------------------------------------
begin tran -- 복구 시작 위치를 지정
rollback tran -- 복구
