use sqldb2b
create table emptbl(emp nchar(3), manager nchar(3), department nchar(3))
insert into emptbl values('나사장',null,null)
insert into emptbl values('김재무','나사장','재무부')
insert into emptbl values('이영업','나사장','영업부')
insert into emptbl values('최정보','나사장','정보부')
insert into emptbl values('김부장','김재무','재무부')
insert into emptbl values('이부장','김재무','재무부')
select * from emptbl
문제1각 직속상사별로 직속상사이름, 직속상사부서, 부하직원이름, 부하직원부서를 출력
select emp1.manager, emp2.department, emp1.emp, emp1.department
from emptbl emp1 join emptbl emp2 on emp1.manager=emp2.emp
문제2 같은 부서의 직원을 쌍으로 부서명, 직원명, 직원명으로 출력하시오
select emp1.department, emp1.emp, emp2.emp
from emptbl emp1 join emptbl emp2 on emp1.department=emp2.department
where emp1.emp < emp2.emp
use pubs
select * from sales
select * from stores
2-1
select s.*, st.stor_name from sales s join stores st on s.stor_id=st.stor_id
order by s.stor_id
2-2
select s.*, stor_name, title
from sales s join stores st on s.stor_id=st.stor_id
join titles l on s.title_id = l.title_id
where qty >= 20
order by s.stor_id
2-3
select * from titles
select * from sales
안팔린 책도 나와야 함으로 titles 테이블을 기준으로 해야 NULL 도 보인다.
그리고 t.title_id 해야 제대로 나오겠지 기준이 그거니까
select t.title_id, title, price, stor_id, qty, ord_date
from sales s right outer join titles t on t.title_id=s.title_id
order by t.title_id
select t.title_id, title, price, stor_id, qty, ord_date
from titles t left outer join sales s on t.title_id=s.title_id
order by t.title_id
select t.title_id, title, price, stor_id, qty, ord_date
from titles t full join sales s on t.title_id=s.title_id
order by t.title_id
2-4
select t.title_id, title, price, stor_id, qty, ord_date
from sales s right outer join titles t on t.title_id=s.title_id
where qty is NULL
order by t.title_id
2-5
insert stores(stor_id, stor_name) values('1000','aaa')
select i.title_id, title, qty, t.stor_id, stor_name
from titles i left outer join sales a on i.title_id=a.title_id
full outer join stores t on a.stor_id=t.stor_id
order by qty desc
2-6
select s1.state, s1.stor_id, s2.stor_id
from stores s1 join stores s2 on s1.state=s2.state
where s1.stor_id < s2.stor_id
select * from titles
select * from sales
p183
*휴대폰이 019인 사람의 키보다 크거나 같은 사람을 출력
use sqldb2b
select * from usertbl
where height >= any (select height from usertbl where mobile1='019')
select * from usertbl where mobile1='019'
- any연산자는 서브쿼리의 여러개의 결과중 한 가지만 만족해도 되며,
= any는 in연산자와 같고 <, >, <=, >=등 연산자와도 함께 쓸 수 있다.
(some 과 같다)
all연산자는 여러개의 결과를 모두 만족시켜야한다
p197
* 가장 큰 키와 가장 작은 티의 회원이름과 키를 출력
select name, height
from usertbl
where height = (select max(height)
from usertbl) or
height = (select min(height)
from usertbl)
--group by name
= 대신 in으로 해도 됨 in이 안전빵이다