실습8

(SELECT * FROM a_team UNION SELECT * FROM b_team UNION SELECT * FROM c_team UNION SELECT * FROM d_team UNION SELECT * FROM e_team) AS test

 

1. 전체 테이블에서 여성만 출력

SELECT * FROM (SELECT * FROM a_team UNION SELECT * FROM b_team UNION SELECT * FROM c_team UNION SELECT * FROM d_team UNION SELECT * FROM e_team) AS test WHERE sex = 'W';

2. 전체 테이블에서 나이가 20대인 남성을 출력

SELECT * FROM (SELECT * FROM a_team UNION SELECT * FROM b_team UNION SELECT * FROM c_team UNION SELECT * FROM d_team UNION SELECT * FROM e_team) AS test WHERE sex = 'M' AND age BETWEEN 20 AND 29;

3. 전체 테이블에서 가장 나이가 많은 사람의 이름과 주소 출력

SELECT name,address FROM (SELECT * FROM a_team UNION SELECT * FROM b_team UNION SELECT * FROM c_team UNION SELECT * FROM d_team UNION SELECT * FROM e_team) AS test ORDER BY age DESC LIMIT 1;

SELECT name,address FROM (SELECT * FROM a_team UNION SELECT * FROM b_team UNION SELECT * FROM c_team UNION SELECT * FROM d_team UNION SELECT * FROM e_team) AS test WHERE age = (SELECT MAX(age) FROM (SELECT * FROM a_team UNION SELECT * FROM b_team UNION SELECT * FROM c_team UNION SELECT * FROM d_team UNION SELECT * FROM e_team) AS test);

4. 전체 테이블에서 서울에 살면서 성이 김씨인 사람의 이름과 나이 출력

SELECT name,age FROM (SELECT * FROM a_team UNION SELECT * FROM b_team UNION SELECT * FROM c_team UNION SELECT * FROM d_team UNION SELECT * FROM e_team) AS test WHERE address like '서울시%' AND name like '김%';

5. a team에서 가장 많은 나이와 같은 사람을 다른 모든테이블에서 출력

SELECT * FROM (SELECT * FROM b_team UNION SELECT * FROM c_team UNION SELECT * FROM d_team UNION SELECT * FROM e_team) AS test WHERE age = (SELECT MAX(age) FROM a_team);

6. 전체 테이블에서 여성이 몇명인지 출력

SELECT sex,COUNT(*) AS '인원' FROM (SELECT * FROM a_team UNION SELECT * FROM b_team UNION SELECT * FROM c_team UNION SELECT * FROM d_team UNION SELECT * FROM e_team) AS test GROUP BY sex HAVING sex = 'W';

7. 전체 테이블에서 남성의 가장 어린 나이가 몇인지 출력

SELECT MIN(age) AS '나이' FROM (SELECT * FROM a_team UNION SELECT * FROM b_team UNION SELECT * FROM c_team UNION SELECT * FROM d_team UNION SELECT * FROM e_team) AS test GROUP BY sex HAVING sex = 'M';

8. 나이를 입력시 해당 나이와 같은 사람의 모든 정보를 출력하는 프로시저 생성(age_pro1)

DELIMITER //
CREATE PROCEDURE age_pro1(num1 int)
    BEGIN
    SELECT * FROM (SELECT * FROM a_team UNION SELECT * FROM b_team UNION SELECT * FROM c_team UNION SELECT * FROM d_team UNION SELECT * FROM e_team) AS test WHERE num1 = age;
    END //
DELIMITER ;

9. 1을 입력하면 a_team, 2를 입력하면 b_team, 3을 입력하면 c_team이 출력되는 방식으로 1~5까지 입력시 해당 테이블이 출력되는 프로시저 생성(잘못 입력한 경우 '잘못 입력했습니다.'가 출력되도록 설정(team_pro1)

DELIMITER //
CREATE PROCEDURE team_pro1(num1 int)
    BEGIN
    IF num1 = 1 THEN SELECT * FROM a_team;
    ELSEIF num1 =2 THEN SELECT * FROM b_team;
    ELSEIF num1 =3 THEN SELECT * FROM c_team;
    ELSEIF num1 =4 THEN SELECT * FROM d_team;
    ELSEIF num1 =5 THEN SELECT * FROM e_team;
    ELSE SELECT '잘못 입력했습니다.';
    END IF;
    END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE team_pro1(num1 int)
    BEGIN
    CASE num1
    WHEN 1 THEN SELECT * FROM a_team;
    WHEN 2 THEN SELECT * FROM b_team;
    WHEN 3 THEN SELECT * FROM c_team;
    WHEN 4 THEN SELECT * FROM d_team;
    WHEN 5 THEN SELECT * FROM e_team;
    ELSE SELECT '잘못 입력했습니다.';
END CASE;
END //
DELIMITER ;

10. 평균 이라고 입력하면 전체테이블의 평균나이 출력, 최고 라고 입력하면 최고나이 출력, 최저라고 입력시 최저나이 출력, 인원이라고 입력시 전체테이블의 전체인원수 출력(cou_pro1)

DELIMITER //
	CREATE PROCEDURE cou_pro1(text varchar(20))
	BEGIN
	IF text = '평균' THEN SELECT AVG(age) FROM (SELECT * FROM a_team UNION SELECT * FROM b_team UNION SELECT * FROM c_team UNION SELECT * FROM d_team UNION SELECT * FROM e_team) AS test;
	ELSEIF text = '최고' THEN SELECT MAX(age) FROM (SELECT * FROM a_team UNION SELECT * FROM b_team UNION SELECT * FROM c_team UNION SELECT * FROM d_team UNION SELECT * FROM e_team) AS test;
	ELSEIF text = '최저' THEN SELECT MIN(age) FROM (SELECT * FROM a_team UNION SELECT * FROM b_team UNION SELECT * FROM c_team UNION SELECT * FROM d_team UNION SELECT * FROM e_team) AS test;
	ELSEIF text = '인원' THEN SELECT COUNT(*) FROM (SELECT * FROM a_team UNION SELECT * FROM b_team UNION SELECT * FROM c_team UNION SELECT * FROM d_team UNION SELECT * FROM e_team) AS test;
	END IF;
	END//
DELIMITER ;

DELIMITER //
CREATE PROCEDURE cou_pro1(text varchar(20))
BEGIN
CASE text
    WHEN '평균' THEN SELECT AVG(age) FROM (SELECT * FROM a_team UNION SELECT * FROM b_team UNION SELECT * FROM c_team UNION SELECT * FROM d_team UNION SELECT * FROM e_team) AS test;
    WHEN '최고' THEN SELECT MAX(age) FROM (SELECT * FROM a_team UNION SELECT * FROM b_team UNION SELECT * FROM c_team UNION SELECT * FROM d_team UNION SELECT * FROM e_team) AS test;
    WHEN '최저' THEN SELECT MIN(age) FROM (SELECT * FROM a_team UNION SELECT * FROM b_team UNION SELECT * FROM c_team UNION SELECT * FROM d_team UNION SELECT * FROM e_team) AS test;
    WHEN '인원' THEN SELECT COUNT(*) FROM (SELECT * FROM a_team UNION SELECT * FROM b_team UNION SELECT * FROM c_team UNION SELECT * FROM d_team UNION SELECT * FROM e_team) AS test;
    ELSE SELECT '잘못 입력했습니다.';
END CASE;
END //
DELIMITER ;

11. 프로시저 실행 시 남성 이라고 입력하면 남성의 인원수, 평균나이, 최고나이, 최저나이, 나이의 합을 출력, 여성 이라고 입력 시 여성의 인원 수, 평균 나이, 최고 나이, 최저 나이, 나이의 합을 출력되도록 생성(team_pro2)

DELIMITER //
CREATE PROCEDURE team_pro2(text varchar(30))
	BEGIN
	IF text = '남성' THEN SELECT sex,COUNT(*),AVG(age),MAX(age),MIN(age),SUM(age) FROM (SELECT * FROM a_team UNION SELECT * FROM b_team UNION SELECT * FROM c_team UNION SELECT * FROM d_team UNION SELECT * FROM e_team) AS test WHERE sex = 'M';
	ELSEIF text = '여성' THEN SELECT sex,COUNT(*),AVG(age),MAX(age),MIN(age),SUM(age) FROM (SELECT * FROM a_team UNION SELECT * FROM b_team UNION SELECT * FROM c_team UNION SELECT * FROM d_team UNION SELECT * FROM e_team) AS test WHERE sex = 'W';
	END IF;
	END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE team_pro2(text varchar(30))
    BEGIN
    CASE text
    WHEN '남성' THEN SELECT COUNT(*),AVG(age),MAX(age),MIN(age),SUM(age) FROM (SELECT * FROM a_team UNION SELECT * FROM b_team UNION SELECT * FROM c_team UNION SELECT * FROM d_team UNION SELECT * FROM e_team) AS test WHERE sex = 'M';
    WHEN '여성' THEN SELECT COUNT(*),AVG(age),MAX(age),MIN(age),SUM(age) FROM (SELECT * FROM a_team UNION SELECT * FROM b_team UNION SELECT * FROM c_team UNION SELECT * FROM d_team UNION SELECT * FROM e_team) AS test WHERE sex = 'W';
    ELSE SELECT '잘못 입력했습니다.';
END CASE;
END //
DELIMITER ;

12. 숫자를 입력시 자동으로 0부터 숫자 사이의 숫자를 전부 더하는 반복문 프로시저 생성(4입력시 1+2+3+4)

DELIMITER //
CREATE PROCEDURE test1(num1 int)
BEGIN
    DECLARE sum int DEFAULT 0;
    DECLARE tmp int DEFAULT 1;
    WHILE tmp <= num1 DO
    	SET sum = sum + tmp;
        SET tmp = tmp + 1;
    END WHILE;
SELECT sum as '합계';
END //
DELIMITER ;

13. 구구단 2x1부터 2x9까지의 결과가 출력되는 반복문 프로시저 생성(2,4,6,8..)

DELIMITER //
CREATE PROCEDURE test2()
BEGIN
    DECLARE num1 int DEFAULT 1;
    DECLARE num2 int DEFAULT 0;
    WHILE num1 <= 9 DO
        SET num2 = 2*num1;
    SELECT num2 as '2단';
        SET num1 = num1+1;
    END WHILE;
END //
DELIMITER ;

 

다 한 사람은 13번 문제 식도 출력 되도록 프로시저 생성(2x1=2, 2x2=4...)

DELIMITER //
CREATE PROCEDURE test3()
BEGIN
    DECLARE num1 int DEFAULT 1;
    DECLARE num2 int DEFAULT 0;
    WHILE num1 <= 9 DO
        SET num2 = 2*num1;
    SELECT concat(2,'x',num1,'=',num2) AS '구구단2단';
        SET num1 = num1+1;
    END WHILE;
END //
DELIMITER ;

다 한 사람은 13번 문제 식도 출력하면서 숫자 입력시 해당 구구단 출력 되도록 프로시저 생성

DELIMITER //
CREATE PROCEDURE test4(num3 int)
BEGIN
    DECLARE num1 int DEFAULT 1;
    DECLARE num2 int DEFAULT 0;
    WHILE num1 <= 9 DO
        SET num2 = num3*num1;
    SELECT concat(num3,'x',num1,'=',num2) AS '구구단';
        SET num1 = num1+1;
    END WHILE;
END //
DELIMITER ;

---

delimiter //
create function sum_func(num1 int, num2 int) returns int
begin
declare tmp int;
if num1 > num2 then
set tmp = num1;
set num1 = num2;
set num2 = tmp;
end if;
set tmp = 0;
while num1 <= num2 do
set tmp = tmp + num1;
set num1 = num1 + 1;
end while;
return tmp;
end //
delimiter ;