use sqldb;

SELECT agencyid, name,  job
FROM person
WHERE personid <= 5
UNION
SELECT corpid, ceo,  '대표이사'
FROM corp
ORDER BY 1;

SELECT agencyid, name,  job
FROM person
WHERE personid <= 5
UNION ALL
SELECT corpid, ceo,  '대표이사'
FROM corp
ORDER BY 1;

SELECT agencyid, name,  job
FROM person
WHERE personid <= 5
UNION ALL
SELECT corpid, name, titlerole
FROM actors
ORDER BY 1;

SELECT agencyid, name,  job
FROM person
WHERE personid <= 5
UNION
SELECT corpid, name, titlerole
FROM actors
ORDER BY 1;

SELECT agencyid, name,  job
FROM person
WHERE personid <= 5
INTERSECT
SELECT corpid, name, titlerole
FROM actors
ORDER BY 1;

-- MySQL은 제공하지 않는 문형으로 오류 발생
SELECT agencyid, name,  job
FROM person
WHERE personid <= 5
MINUS
SELECT corpid, name, titlerole
FROM actors
ORDER BY 1;

SELECT e.empid AS 사원번호, e.ename AS 이름, e.title AS 직책, m.ename AS 상급자이름
 FROM emp e JOIN emp m
    ON e.mgrid = m.empid
UNION
SELECT empid, ename, title,'상급자 없음'
 FROM emp
WHERE mgrid IS NULL;


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);
