실습6

1. 나이를 기준으로 내림차순으로 출력

(SELECT * FROM one ORDER BY age DESC limit 20) UNION (SELECT * FROM two ORDER BY age DESC limit 16);

SELECT * FROM (SELECT * FROM one UNION SELECT * FROM two) AS test ORDER BY age DESC;

2. 서울시의 남자만 출력

SELECT * FROM one WHERE sex ='M' AND address like '서울시%' UNION SELECT * FROM two WHERE sex = 'M' AND address like '서울시%';

SELECT * FROM (SELECT * FROM one UNION SELECT * FROM two) AS test WHERE address like '서울시%' AND sex = 'M';

3. 성별로 인원수 및 나이의 평균, 가장 많은 나이, 가장 어린 나이 출력

SELECT sex,COUNT(*),AVG(age),MAX(age),MIN(age) FROM (SELECT * FROM one UNION SELECT * FROM two) AS test GROUP BY sex;

4. 김씨 성을 가진 남자의 이름, 나이, 주소 출력

SELECT name, age, address  FROM (SELECT * FROM one UNION SELECT * FROM two) AS test WHERE name like '김%' AND sex = 'M';

5. 전체 평균 나이 출력

SELECT AVG(age) FROM one UNION SELECT AVG(age) FROM two;

SELECT ACG(age) FROM (SELECT * FROM one UNION SELECT * FROM two) AS test;

6. 이름이 '재'로 끝나는 사람의 이름,주소 출력

SELECT name,address FROM (SELECT * FROM one UNION SELECT * FROM two) AS test WHERE name like '%재';

7. 30대 남성의 이름, 주소를 출력(두번째 사람부터 3명만 출력)

SELECT name,address FROM (SELECT * FROM one UNION SELECT * FROM two) AS test WHERE age BETWEEN 30 AND 39 AND sex ='M' limit 1,3;

8. 25이상 30세 미만의 이름, 주소 출력

SELECT name,address FROM (SELECT * FROM one UNION SELECT * FROM two) AS test WHERE age BETWEEN 25 AND 29;

9. 이름이 '민'이 들어가는 사람의 모든 정보 출력

SELECT * FROM (SELECT * FROM one UNION SELECT * FROM two) AS test WHERE name like '%민%';

10. 서울에 사는 여성 중 회원번호가 10번대인 사람의 모든 정보 출력

SELECT * FROM (SELECT * FROM one UNION SELECT * FROM two) AS test WHERE address like '서울시%' AND no BETWEEN 10 AND 19 AND sex = 'W';

11. no가 같은 회원들의 모든 정보 출력(6번째 사람부터 5명 출력)

SELECT * FROM one INNER JOIN two ON one.no = two.no limit 5,5;

SELECT * FROM (SELECT * FROM one UNION SELECT * FROM two) AS test WHERE no IN (SELECT one.no FROM one INNER JOIN two ON one.no = two.no) limit 5,5;

12. 동갑인 나이와 동갑 나이별 인원수를 출력(동갑 없는 나이는 제외)

SELECT age,COUNT(*) FROM (SELECT * FROM one UNION SELECT * FROM two) AS test GROUP BY age HAVING COUNT(*) > 1;