(데이터베이스) 서브쿼리 생성하기

* my_contact 테이블과 job_current 테이블이 있을 때,

--->" 내 리스트에 있는 사람들 중에 누가 연봉이 가장 많을까?"를 구하여라


SELECT mc.first_name, mc.last_name, jc.salary
FROM my_contacts AS mc NATURAL JOIN job_current AS jc
WHERE jc.salary=(SELECT MAX(salary) FROM job_current jc);



** 서브쿼리를  SELECT열에서 사용.
zip_code 테이블과 my_contacts테이블이 있을 때

my_contacts 테이블의 모든 행에 대해 이름(first_name)과 성(last_name)그리고 주(state)
(우편번호를 가져다 zip_code테이블에서 해당 주 이름을 가져와 사용)를 가져옵니다.

예) SELECT mc.first_name, mc.last_name,
(SELECT state
 FROM zip_code
 WHERE mc.zip_code=zip_code)AS state
 FROM my_contacts mc;



**자연조인을 포함한 서브쿼리

앤디의 연봉을 가져오고, 연봉을 비교하고, 그리고 앤디보다 더 많이 버는 사람의 이름가 성을 연봉과 함께 반환한다.

예) SELECT mc.first_name, mc.last_name,jc.salary
     FROM my_contacts AS mc NATURAL JOIN job_current AS jc
 WHERE jc.salary>(앤디의 연봉 쿼리)

-->(SELECT jc.salary FROM my_contacts mc NATURAL JOIN job_current jc
     WHERE email='andy@weatheroram.com');

문제)
1. job_current 테이블에서 웹 개발자의 평균 연봉을 구하는 쿼리를 쓰시오.
 ----> SELECT AVG(salary) FROM job_current WHERE title='Web Developer';

2. job_current 테이블에서 모든 웹 개발자의 이름,성,연봉을 구하는 쿼리를쓰시소
----> SELECT mc.first_name, mc.last_name, jc.salary
        FROM my_contacts mc NATURAL JOIN job_current jc
        WHERE jc.title='Web Developer';
3. 평균연봉을 서브쿼리로 사용하여 각 웹 개발자에게 평균보다 얼마나 많이 또는 적게버는지 보이자.
--->
SELECT mc.first_name,mc.last_name, jc.salary,
jc.salary-(SELECT AVG(salary) FROM job_current WHERE title='Web Developer')
FROM my_contacts mc NATURAL JOIN job_current jc
WHERE jc.title='Web Developer';

** 만약 서브쿼리가 외부쿼리의 어떤 것도 참조하지 않고 단독으로 사용되면 그것은 비상관 서브쿼리입니다.


<상관서브쿼리>

예) my_contacts테이블의 각 사람에 대해 interest테이블에서 그 사람에 대한 관심사(interest)의 수를 세고 관심사를 세 개 가진 사람의 이름과 성을 반환하는 것

SELECT mc.first_name, mc.last_name
FROM my_contacts AS mc
WHERE 3=(SELECT COUNT(*) FROM contact_interest
              WHERE contact_id=mc.contact_id);


**NOT EXISTS를 포함한 상관 서브쿼리
예) my_contacts 사람들 중 job_current 테이블에 없는 모든 사람에게 이메일을 보내는 쿼리

SELECT mc.first_name firstname ,mc.last_name lastname, mc.email email
FROM my_contacts mc
WHERE NOT EXISTS
(SELECT * FROM job_current jc
WHERE mc.contact_id=jc.contact_id);


WHERE EXISTS를 한 경우는 my_contacts 테이블에서 contact_id값이 contact_interest테이블에서 한 번 이상 나오는 사람드르이 이름,성 이메일 주소를 찾는다.