select name, height
from usertbl
where height >= any(select height
from usertbl
where mobile1='019')
any는 한가지만 완성되어도 괜찮다
all은 모든 것을 만족해야
in을 쓸 때는 등호를 쓸 수 없다 같은 뜻이라서
in 은 = any 와 같다
그러니까 등호가 나오면 all 이나 any 가 나온다
select name, height
from usertbl
where height in (select max(height)
from usertbl) or
height in (select min(height)
from usertbl)
--group by name
저걸 분리했다. 두 개 같이 쓰면 에러남 피연산자 관계? 때문
*두 번 이상 구매한 모든 회원의 이름, 주소가 나오도록 출력하시오
use sqldb2b
select * from buytbl
select * from usertbl
- join 이용
select distinct u.name, addr
from usertbl u left outer join buytbl b on u.userid = b.userid
where amount >= 2
수량이 2개 이상이니까
값이 없을 때가 없어서
inner join
<answer>
select name, addr
from usertbl u join buytbl b on u.userid=b.userid
group by name, addr
having count(*) >= 2
- subquery 이용
select name, addr
from usertbl
where userid in (select userid from buytbl group by name, addr having count(*) >= 2)
select userid
from buytbl
group by userid
having count(*) >=2
select name, addr
from usertbl
where userid in (select userid
from buytbl
group by userid
having count(*) >=2)
* subquery의 특징
1. 괄호안에 먼저 수행될 내용을 써준다.
2. 서브쿼리내에는 order by 를 쓸 수 없다. 출력될 내용이 아니니까
3. exists 연산자를 제외하고 서브쿼리내에
두 개 이상의 select list가 올 수 없다.
*한 번 이상(=한번이라도) 구매한 모든 회원의 이름, 주소가 나오도록 출력하시오
-join
select distinct name, addr
from usertbl u join buytbl b on (u.userid = b.userid)
-subquery
select name, addr
from usertbl
where userid in (select userid from buytbl)
--distinct를 안써도 되는 이유 서브쿼리라는 것 때문에
--서브쿼리에서 select 하는 것에서 빼오는 거라서?
- correlated subquery(상관 하위 질의문): 외부쿼리와 서브쿼리가
상호 영향을 준다. 외부쿼리가 10건이 있고 서브쿼리가 20건이
있다면 10 * 20의 비교횟수가 쇼오되므로 성능이 떨어진다.
select name, addr
from usertbl u
where userid in (select userid
from buytbl b
where u.userid=b.userid)
위의 where 다음의 userid 는 exists라고 적어도 된다
select name, addr
from usertbl u
where exists (select *
from buytbl b
where u.userid=b.userid)
이게 상관이다
* '박주영'이 산 물건의 평균price보다 더 비싸게 산 물건의
품목명(prodname)과 price를 출력하시오
select * from buytbl
select * from usertbl
select prodname, price
from buytbl
where userid='PJY' and price > (select avg(price) from buytbl where userid='PJY')
select prodname, price
from buytbl b join usertbl u on b.userid=u.userid
where name='박주영' and price > (select avg(price) from buytbl where userid='PJY')
<answer>
select prodname, price
from buytbl b join usertbl u on (b.userid=u.userid)
where name = '박주영' and price > (select avg(price)
from buytbl b join usertbl u on (b.userid=u.userid)
where name = '박주영')
<answer> - as correlated subquery
select prodname, price
from buytbl b join usertbl u on (b.userid=u.userid)
where name = '박주영' and price > (select avg(price)
from buytbl
where u.userid=userid)
* 모든 회원별로 각 회원이 산 물건의 평균price보다 더 비싸게 산 물건의
품목명(prodname)과 price를 출력하시오
select * from buytbl
select * from usertbl
select u.userid, b.prodname, b.price
from buytbl b join usertbl u on b.userid=u.userid
where exists (select userid
from buytbl
group by userid, price
having price > avg(price)
)
select userid, avg(price) from buytbl
group by userid
select userid, prodname, price
from buytbl b
where price > any ( select avg(price) from buytbl where b.userid=userid)
<answer>
select userid, prodname, price
from buytbl b
where price > (select avg(price) from buytbl where b.userid=userid)
* 모든 회원별로 각 회원이 산 물건의 수량(amount)의 누적합계를
usertbl의 sumamount의 값으로 변경하시오.
alter table usertbl
add sumamount int
select * from usertbl
select * from buytbl
update usertbl
set sumamount = (select sum(amount)
from buytbl
where usertbl.userid=userid)
use pubs
select * from titles
select * from sales
1.
select s.title_id, t.title, sum(qty)
from sales s join titles t on (s.title_id=t.title_id)
group by s.title_id, title
having sum(qty) < (select avg(qty) from sales)
2.
select * from sales
select stor_id, sum(qty) as 합계,
cast(sum(qty) as float) /(select sum(qty) from sales)* 100 as 비율
from sales
group by stor_id
select userid, prodname, price
from buytbl b
where price > (select avg(price) from buytbl where b.userid=userid)
3.
서브 쿼리
select stor_id, stor_name, city, state
from stores t
where stor_id in (select stor_id from sales group by stor_id
having count(*) >= 2)
join 이용
select s.stor_id, stor_name, city, state
from stores s join sales a on s.stor_id=a.stor_id
group by s.stor_id, stor_name, city, state
having count(*) >= 2
3-4
select * from sales
select stor_id, title_id, qty
from sales s
where qty = any (select max(qty) from sales where s.stor_id=stor_id)
0736 1389
3-5
select * from publishers
select * from titles
-subquery
select pub_name
from publishers s
where exists (select pub_id from titles t where type='business' and s.pub_id=t.pub_id)
-join
select distinct pub_name, t.pub_id
from publishers p join titles t on p.pub_id=t.pub_id
where t.type='business'
3-6
select * from stores
select * from publishers
select stor_id, stor_name, city, state
from stores
UNION
select pub_id, pub_name, city, state
from publishers