데이터베이스 예제 3

1. DALLAS 에서 근무하는 사원의 이름, 부서명, 담당업무를 보이시오

SELECT ename, dname, job

FROM emp, deptno

WHERE emp.deptno=deptno.deptno AND loc='DALLAS';

 

2. DALLAS 에서 근무하는 사원중 연봉이 4000 이하인 사람은 몇 명인가

SELECT COUNT(*) FROM emp, dept

WHERE emp.deptno=dept.deptno AND loc='DALLAS' AND sal<=4000;

 

3. PRESIDENT (사장)이 근무하는 지역은 어디인가

SELECT loc FROM emp, dept WHERE emp.deptno=dept.deptno AND job='PRESIDENT';

 

4. 모든 매니저의 이름과 부하직원의 이름을 보이시오 (매니저이름으로 정렬하여 보 이시오)

SELECT m.ename, e.ename FROM emp e, emp m

WHERE e.mgr=m.empno 

ORDER BY m.ename;

 

5. 모든 매니저의 이름과 부하직원의 숫자를 보이시오 => 다시

SELECT m.ename, COUNT(*) FROM emp e, emp m

WHERE e.mgr=m.empno

GROUP BY m.ename;

 

 

6. 각 지역별로 근무하는 사원들의 연봉 합계를 보이시오 (지역명, 연봉합계)

SELECT loc, SUM(sal) FROM emp, dept 

WHERE emp.deptno=dept.deptno GROUP BY loc;

 

7. 매니저가 아닌 (부하직원이 없는) 사원들은 몇 명인가? -중첩사용과 미사용 둘다 =>다시 

SELECT COUNT(*) FROM emp e

WHERE e.empno NOT IN (SELECT mgr  FROM emp WHERE mgr IS NOT NULL);

 

SELECT COUNT(empno) - COUNT(distinct mgr) from emp;

(전체 직원수에서 매니저 수를 빼면 매니저가 아닌 직원 수만 남음)

 

8. 매니저가 아닌 (부하직원이 없는) 사원들의 연봉합계는 얼마인가 => 다시

SELECT SUM(sal) FROM emp

WHERE empno NOT IN (SELECT mgr FROM emp WHERE mgr IS NOT NULL);

 

 

9. 매니저이름, 부하직원이름, 부서명 을 보이시오 (매니저이름으로 정렬)

SELECT m.ename, e.ename, dept.dname FROM emp e, emp m, dept

WHERE e.mgr=m.empno AND dept.deptno=m.deptno ORDER BY m.ename;

 

 

10. SCOTT 의 매니저는 연봉이 얼마인가

SELECT m.sal FROM emp e, emp m

 WHERE e.mgr=m.empno AND e.ename='SCOTT';

 

11. Asia 지역에 있는 국가의 국가명, 도시명을 보이시오

SELECT country.name, city.name FROM country, city WHERE country.continent='Asia' AND country.code=city.counrtycode;

 

12. South Korea 에 있는 도시명, 도시 인구수(population)를 보이시오

SELECT city.name, city.population FROM country, ciry WHERE country.code=city.countrycode AND country.name = 'SOUNT KOREA';

 

13. South Korea 에 있는 도시중 인구수가 100만 이상인 도시의 도시명, 도시 인구 수(population)를 보이시오

SELECT city.name, city.population FROM city, counrty WHERE

city.popluation>=1000000 AND city.counrtycode=country.code AND country.name='south korea';

 

14. GNP 가200 미만인 국가의 국가명, 도시명, 도시 인구수를 보이시오

SELECT country.name, city.name, city.popluation FROM country, city 

WHERE country.code=city.countrycode AND country.gnp < 200;

 

15. Laos 에 있는 도시수를 보이시오

SELECT COUNT(city.name) FROM country, city WHERE country.code=city.countrycode AND country.name='laos';

 

16. 국가명, 그 국가의 도시의 수를 보이시오 => 다시

SELECT country.name, COUNT(*) FROM country, city

WHERE country.code=city.countrycode

GROUP BY country.name;

 

 

17. 국가명, 그 국가 도시의 평균 인구수를 보이시오

SELECT country.name, AVG(city.popluation) FROM country, city WHERE

country.code=city.countrycode GROUP BY country.name;

 

18. 국가 인구수가 1000만명이 안되는 국가의 국가명, 도시명을 보이시오 => 다시

SELECT country.name, city.name FROM country, city

WHERE country.code=city.countrycode AND country.popluation < 10000000;

 

19. EUROPE 에 있는 국가중 국가 인구수가 1000만명이 안되는 국가의 국가명, 도시 명을 보이시오

SELECT country.name, city.name FROM country, city WHERE

country.code=city.countrycode  AND country.continent='EUROPE' AND country.population<10000000;

 

20. Asia 지역에 있는 국가중 GNP가 20000 이상이고 인구수가 3000만이상인 국가 의 국가명, GNP, 도시명, 도시인구수를 보이시오

SELECT country.name, city.name, city.population FROM country, city

WHERE country.code=city.countrycode AND country.continent ='Asia' AND gnpe >=20000 AND couintry.population>=30000000;

 

1. 연봉을 가장 적게 받는 사원의 이름, 연봉, 부서명을 보이시오

SELECT ename, sal, dname FROM emp, dept 

WHERE emp.deptno=dept.deptno AND sal = (SELECT MIN(sal) FROM emp);

 

2. 연봉을 평균 이상 받는 사원들의 이름, 연봉, 근무지를 보이시오

SELECT ename, sal, loc FROM emp, dept

WHERE emp.deptno=dept.dpetno AND sal >= (SELECT AVG(sal) FROM emp);

 

3. 연봉을 가장 적게 받는 사원의 매니저는 누구인가?

SELECT e.mgr FROM emp e, emp m 

WHERE e.mgr=m.empno AND e.sal = (SELECT MIN(sal) FROM emp);

 

4. 연봉을 가장 많이 받는 사원과 적게 받는 사원의 이름, 연봉을 보이시오 => 다시

SELECT ename, sal FROM emp

WHERE sal = (SELECT MIN(sal) FROM emp) OR sal=(SELECT MAX(sal) FROM emp);

 

5. 연봉을 가장 많이 받는 사원과 적게 받는 사원을 제외한 나머지 사원들의 총 연봉 합계를 보이시오 => 다시

SELECT SUM(sal) FROM emp

WHERE sal != (select max(sal) from emp) and sal != (select min(sal) from emp)

 

6. SALES 부서에 속한 사원들과 동일한 담당업무를 갖는 사원들의 이름, 담당업무를 보이시오 (단 SALES 부서에 속한 사원은 제외) => 다시

SELECT ename, job FROM emp, dept

WHERE dname <> 'SALES' (SALES에 속한 사람은 제외)

AND emp.deptno=dept.deptno

AND job IN(SELECT job FROM emp, dept WHERE emp.deptno=dept.deptno AND dname = 'SALES')

(SALES 부서에 속한 사원들이 가지는 업무 )

 

 

 

7. 연봉을 평균보다 500 이상 적게 받는 사원들의 이름, 부서명, 연봉을 보이시오

SELECT ename, dname, sal FROM emp, dept

WHERE emp.deptno=dept.deptno 

AND sal + 500 < (SELECT AVG(sal) FROM emp);

 

8. JAMES 보다 입사일이 빠른 사원들의 이름, 담당업무, 입사일을 보이시오

SELECT ename, job, hiredate FROM emp

WHERE hiredate < (SELECT hiredate FROM emp WHERE ename='JAMES');

 

9. ADAMS 보다 연봉을 많이 받는 사람은 모두 몇 명인가

SELECT COUNT(*) FROM emp

WHERE sal > (SELECT sal FROM emp WHERE ename='ADAMS');

10. GNP가 가장 높은 국가의 국가명, GNP 를 보이시오

SELECT name, gnp FROM country

WHERE gnp = (SELECT MAX(gnp) FROM country);

 

11. 인구수가 가장 적은 국가의 국가명, GNP 를 보이시오

SELECT name, gnp FROM country

WHERE popluation=(SELECT MIN(popluation) FROM country);

 

12. 인구수가 평균 이상인 국가중 GNP가 50000 이상인 국가의 이름과 GNP 를 보이 시오

SELECT name, gnp FROM country 

WHERE popluation >= (SELECT AVG(popluation) FROM country) AND gnp >= 50000

 

13. 한국(South Korea)보다 GNP 가 높은 ASIA 국가들의 이름과 GNP를 보이시오

SELECT name, gnp FROM country

WHERE gnp > (SELECT gnp FROM country WHERE name='South Korea') AND continent = 'ASIA';

 

14. 일본(Japan)보다 인구수가 많은 ASIA 국가들의 이름, 인구수를 보이시오

SELECT name, population FROM country 

WHERE population > (SELECT population FROM country WHERE name='Japan');

 

 

15. 영토면적(SurfaceArea)이 가장 작은 국가의 이름, 영토면적을 보이시오

SELECT name, surfacearea FROM country

WHERE surfacearea = (SELECT MIN(surfacearea) FROM counrty);

 

16. Canada 보다 영토 면적이 넓은 국가는 모두 몇나라인지 보이시오

SELECT COUNT(*) FROM country WHERE 

surfaceaera > (select surfaceaera from country where name='Canada');

 

1. 연봉을 적게 받는 하위 5 명의 이름, 부서명, 연봉을 보이시오

SELECT ename, dname, sal FROM emp, dept

WHERE emp.deptno=dept.deptno 

ORDER BY sal LIMIT 5;

 

2. 연봉을 평균 보다 적게 받는 사람들 중에서 연봉이 많은 순서대로 3명을 보이시오 (사원번호, 이름, 연봉)

SELECT empno, ename, sal FROM emp

WHERE sal < (SELECT AVG(sal) FROM emp) 

ORDER BY sal DESC 

LIMIT 3;

 

3. SCOTT 보다 연봉을 많이 받는 사람들 중에서 SCOTT 와 연봉이 비슷한 사람 3 명의 이름, 부서명, 연봉을 보이시오

SELECT ename, dname, sal FROM emp, dept

WHERE emp.deptno=dept.deptno AND sal>(SELECT sal FROM emp WHERE ename='SCOTT')

ORDER BY sal

LIMIT 3;

 

4. BLAKE 보다 입사일이 늦은 사람들 중에서 상위 2명을 제외하고 3명의 이름, 부서 명, 입사일자를 보이시오 (입사일자가 빠른순으로)

SELECT ename, dname, hiredate FROM emp, dept

WHERE emp.deptno=dept.deptno AND hiredate >(SELECT hiredate FROM emp WHERE ename='BLAKE')

ORDER BY hiredate

LIMIT 2, 3;

 

5. 연봉금액이 SMITH 와 FORD 사이인 사원의 이름, 연봉을 보이시오. (단 SMITH 와 FORD 는 누가 더연봉을 많이 받는지 알 수 없다) => 다시

SELECT ename, sal FROM emp

WHERE sal BETWEEN (SELECT MIN(sal) FROM emp WHERE ename='SMITH' OR ename='FORD') 

AND (SELECT MAX(sal) FROM emp WHERE ename='SMITH' OR ename='FORD') ;

 

SMITH와 FORD를 포함하지 않으려면  AND ename NOT IN('SMITH', 'FORD')

 

 

6. 소속된 사원의 연봉 총액이 많은 상위 2개의 부서의 이름과, 연봉 총액을 보이시 오

SELECT dname, SUM(sal) WHERE emp, dept

WHERE emp.deptno=dept.deptno 

GROUP BY dname

ORDER BY SUM(sal) DESC

LIMIT 2;

 

 

1. SALES 부서에 근무하는 사원들의 연봉을 월급으로 환산하여 보이되 소수점 이하 는 버리고 보이시오 (사원이름, 월급)

SELECT FLOOR(sal/12);

 

2. 사원의 이름과 사원의 근무지를 하나의 문자열로 만들어 보이시오

SELECT CONTACT(ename, loc) FROM emp, dept

WHERE emp.deptno=detp.deptno;

 

3. 담당업무가 CLERK 인 사원의 이름과 사원의 매니저 이름을 보이되 매니저 이름 은 앞 세글자만 보이시오 => 다시

SELECT e.ename, LEFT(m.ename, 3) FROM emp e, emp m 

WHERE e.job='CLERK' AND e.mgr= m.empno;

 

4. 연봉 3000 이상인 사원의 이름, 담당업무를 보이되 담당업무가 MANAGER 인 경 우는 업무명을 BOSS 로 바꾸어 보이시오 => 다시

SELECT ename, REPLACE(job, 'MANAGER', 'BOSS') FROM emp 

WHERE sal >= 3000;

 

5. 현재 날짜와 시간을 출력 하시오

SELECT NOW();

 

6. 1981년과 1982년에 입사한 사원의 이름, 입사일자를 보이시오 (YEAR 함수 이용 할 것) => 다시

SELECT ename, hiredate FROM emp

WEHRE YEAR(hiredate) IN (1981, 1982);

 

 

7. SCOTT 사원의 입사일부터 오늘까지 근무한 날수를 보이시오 => 다시

TO_DAYS =>  00년 00월 00일 부터 날짜까지의 일자수

SELECT TO_DATYS(CURDATE()) - TO_DAYS(hiredate) + 1 (입사날도 근무일이다)

FROM emp WHERE ename = 'SCOTT'; 

 

8. 2019년 7월 12일은 2019년 1월 1일을 기준으로 몇번 째 날인가 => 다시

SELECT DAYOFYEAR('2019-07-12');

 

 

9. 입사한 날의 요일이 월요일~수요일인 사원의 이름, 급여를 보이시오 => 다시

SELECT ename, sal FROM emp

WHERE WEEKDAY(hiredate) IN (0, 1, 2);