SELECT agencyid
FROM person
WHERE name = '김봉석';
SELECT corpid AS 회사번호, corpname AS 회사명, ceo AS 대표이름
FROM corp
WHERE corpid = (SELECT agencyid FROM person WHERE name = '김봉석');
-- JOIN 문 으로 작성
SELECT c.corpid AS 회사번호, c.corpname AS 회사명, c.ceo AS 대표이름
FROM corp c join person p
on c.corpid = p.agencyid
where p.name = '김봉석';
SELECT name AS 이름, job AS 업무, followers
FROM person
WHERE followers = (SELECT MAX(followers) FROM person);
SELECT name AS 이름, birthdate AS 생년월일, nickname AS 별명
FROM person
WHERE birthdate = (SELECT MAX(birthdate) FROM person);
-- Limit 작성예
SELECT name AS 이름, job AS 업무, followers
FROM person
order by followers desc
limit 1;
SELECT name AS 이름, birthdate AS 생년월일, nickname AS 별명
FROM person
order by birthdate desc
limit 1;
-- 평균키보다 작은 인물의 이름, 업무, 키 조회
SELECT name AS 이름, job AS 업무, height AS 키
FROM person
WHERE height > (SELECT AVG(height) FROM person)
ORDER BY 3 DESC;
-- 인물들의 평균몸무게가 3번 회사의 균몸무게보가 큰 회사번호, 평균몸무게 조회
SELECT agencyid , ROUND(AVG(weight), 1) AS 평균몸무게
FROM person
GROUP BY agencyid
HAVING AVG(weight) > (SELECT AVG(weight)
FROM person
WHERE agencyid = 3);