8/9 [database]-서브 쿼리 [java]-반복문
Q. Abel이라는 사원이 받는 급여보다 더 많은 급여를 받는 사원의 이름과 급여를 출력하시오
SQL> 1 select last_name, salary
2 from employees
3* where salary>(select salary from employees where last_name = 'Abel')
LAST_NAME SALARY
-------------------- ----------
King 24000
Kochhar 17000
De Haan 17000
Greenberg 12008
Russell 14000
Partners 13500
Errazuriz 12000
Ozer 11500
Hartstein 13000
Higgins 12008
10 rows selected.[문제 해결에 서브 쿼리 사용]
특정값을 모를때 서브쿼리 개념을 많이 사용한다.
[서브 쿼리 구문]
- 서브 쿼리(내부 질의)는 기본 질의 실행 전에 한 번 실행됩니다.
- 서브 쿼리의 결과는 메인쿼리(외부 질의)에 사용 됩니다.
select select_list
from table
where expr operator(select select_list from table);서브 쿼리는 다른 select 문의 절에 삽입된 select문으로서 서브 쿼리를 사용하면 간단한 명령문으로 강력한 기능을 제공하는 명령문을
where- 행에대한 조건을 주는 절/ having-> 절에 대한 조건을 주는 절
서브쿼리는 다수의 조건도 사용가능하다
[서브쿼리사용]
Q. 'Abel'보다 급여가 많은 사람의 이름을 구하여라
SQL> select last_name
2 from employees
3 where salary>(select salary from employees where last_name = 'Abel');
LAST_NAME
--------------------
King
Kochhar
De Haan
Greenberg
Russell
Partners
Errazuriz
Ozer
Hartstein
Higgins
10 rows selected.[서브 쿼리 사용 지침]
- 서브 쿼리를 괄호로 묶습니다.
- 비교 조건의 오른쪽에 서브 쿼리를 넣습니다.
- 서브 쿼리의 order by 절은 top-n 분석을 수행하지 않을 경우에는 필요가 없습니다
- 단일 행 서브 쿼리에는 단일 행 연산자를 사용 하고 다중 행 서브 쿼리에는 다중 행 연산자를 사용 합니다.
[단일 행 서브퀴리]
1. 내가 멀 모르는지(모른 값인 먼지?? Abel의 급여)찾자
2. 모르는 값(abel의 급여)을 서브쿼리를 통해 찾자
3. 모르는 값을 서브쿼리의 select list절에 어떤 컬럼을 통해 찾을 것인지를 결정하자(select salary)
4. 서브쿼리의 결과 값을 메인 쿼리의 누가 받을것인지 결정
-> where, having절의 조건식에 사용이 되는 컬럼을 결정
-> where salary = (select salary)
5. 서브쿼리의 넘겨주는 컬럼과 받아주는 메인쿼리의 컬럼의 데이터타입이 반드시 같아야 함(컬럼이름은 틀려도 상관없음) -> 평균적으로 서브쿼리의 컬럼과 메인쿼리의 컬럼이름이 같은경우가 대부분
6.서브쿼리의 결과에 따라 사용해야하는 연산자를 결정 -> 단일값에는 단일 연산자(=,>, < 등)를 복수값일 경우에는 in과 같은 복수연산자를 사용
- 한 행만 반홥합니다
- 단일 행 비교 연산자를 사용합니다.
| 연산자 | 의미 |
| = | 같음 |
| > | 보다 큼 |
| >= | 크거나 같음 |
| < | 보다 작음 |
| <= | 작거나 같음 |
| <> | 같지 않음 |
ex)사원141과 동일한 업무 id를 가진 사원을 표시합니다.
1 select last_name,job_id
2 from employees
3* where job_id = (select job_id from employees where employee_id=141)[단일 행 서브 쿼리 실행]
ex) 사원141과 업무 id가 동일하면서 사원 143보다 급여가 많은 사원을 표시합니다.
1 select last_name, job_id, salary
2 from employees
3 where job_id =(select job_id from employees where employee_id = 141)
4* and salary > (select salary from employees where employee_id =143)- 이 예제는 세 개의 질의(외부 질의 한 개와 내부 질의 두 개)블록으로 구성됩니다. 내부 질의 블록이 먼저 실행되어 각각의 질의 결과(ST_CLEK,2600)를 생성합니다. 그런 다음 외부 질의 블록이 처리되면서 내부 질의에서 반환된 값을 사용하여 검색 조건을 완성합니다.
[서브 쿼리에서 그룹 함수 사용]
ex) 최소 급여를 받는 모든 사원의 이름, 업무ID 및 급여를 표시하며 MIN 그룹 함수는 단일 값(2500)을 외부 질의에 반환합니다.
SQL> select last_name, job_id, salary
2 from employees
3 where salary = (select min(salary) from employees);ex)IT라는 부서에 근무하는 사원의 이름과 급여와 부서번호를 출력하시오
SQL> select last_name,salary,department_id
2 from employees
3 where department_id =(select department_id from departments where department_name='IT');
LAST_NAME SALARY DEPARTMENT_ID
-------------------- ---------- -------------
Hunold 9000 60
Ernst 6000 60
Austin 4800 60
Pataballa 4800 60
Lorentz 4200 60
[having 절에 서브 쿼리 사용]
oracle server는 서브 쿼리를 먼저 실행합니다.
oracle server는 메인쿼리의 having절에 결과를 반환합니다.
Q. 최소 급여가 부서50의 최소 급여보다 많은 부서를 모두 표시하시오
SQL> 1 select department_id, min(salary)
2 from employees
3 group by department_id
4* having min(salary)>( select min(salary)
from employees
where department_id =50;)
DEPARTMENT_ID MIN(SALARY)
------------- -----------
100 6900
30 2500
7000
90 17000
20 6000
70 10000
110 8300
80 6100
40 6500
60 4200
10 4400Q. 평균 급여가 가장 적은 업무를 찾습니다.
1 select job_id, avg(salary)
2 from employees
3 group by job_id
4* having avg(salary) = (select min(avg(salary)) from employees group by job_id)
IN의 복수연산자를 사용해야 한다
[다중 행 서브 쿼리]
-여러 행을 반환합니다.
- 여러 행 비교 연산자를 사용합니다.
in - 목록에 있는 임의의 멤버와 동일합니다.
any - 값을 서브 쿼리에 의해 반환된 각 값과 비교 합니다.
all - 값을 서브 쿼리에 의해 반환된 모든 값과 비교합니다.
서브쿼리의 결과(30,40)
10
20
30
40
50
60
in(30,40) -> 30,40
>all(결과 모두 (최댓값40)보다 큰값을 찾는다 =>50,60
<all ( 결과 모두와 (최솟값이30)보다 작은 값들을 찾는다 =? 10,20
>any(최솟값(30)보다 큰 값 ->40,50,60
<any(최댓값(40)보다 작은값 -> 10,20,30
[다중 행 서브 쿼리에 in 연산자 사용]
SQL> select last_name, salary, department_id
2 from employees
3 where salary in (2500,4200,4400,6000,7000,8300,8600,17000);[다중 행 서브 쿼리에 any 연산자 사용]
SQL> select employee_id, last_name, job_id, salary
2 from employees
3 where salary < any(select salary from employees where job_id='IT_PROG')
4 and job_id <> 'IT_PROG';< any 는 최대값보다 작음을 나타내고, >any는 최소값보다 큼을 나타내며, =any 는 in과 동일합니다.
[다중 행 서브 쿼리에 all 연산자 사용]
SQL> select employee_id, last_name, job_id, salary
2 from employees
3 where salary<all(select salary from employees where job_id = 'IT_PROG')
4 and job_id <>'IT_PROG';all 연산자는 값을 서브 쿼리에서 반환하는 모든 값과 비교합니다. 슬라이드 예제는 업무 id가 IT_PROG인 모든 사원보다 급여가 적으면서 업무가 IT_PROG가 아닌 사원을 표시합니다.
>ALL은 최대값보다 큼을 나타내고, <ALL은 최소값보다 작음을 나타냅니다.
NOT 연산자는 IN,ANY및 ALL 연산자와 함께 사용할 수 있습니다
[서브 쿼리에서의 널 값]
select emp.last_name
from employee emp
where emp.employee_id not in(select mgr.manager_id from employees mgr);- 논리적으로는 이 SQL 문이 행을 12개를 반환해야 하지만 아무 행도 반환되지 않습니다. 내부 질의에서 반환한 값 중 하나가 널 값이므로 전체 질의가 행을 반환하지 않는 것입니다. 널 값을 비교하는 모든 조건에 대한 결과는 널입니다. 따라서 서브 쿼리의 결과 집합에 널 값이 포함될 가능성이 있는 경우에는 NOT IN 연산자를 사용하지 마십시오, NOT IN 연산자는 <> ALL과 동일합니다.
예제29) Zlotkey와 동일한 부서에 근무하는 다른 모든 사원들의 사번 및 고용 날짜를 출력하시오.
1 select employee_id, hire_date
2 from employees
3 where department_id in (select department_id from employees where last_name = 'Zlotkey')
4 and last_name!='Zlotkey';예제30) 회사 전체 평균 급여보다 더 급여를 많이 받는 사원들의 사번 및 이름을 출력하시오
1 select employee_id, last_name
2 from employees
3* where salary > (select avg(salary) from employees)예제31)이름에 u가 포함되는 사원들과 동일 부서/에 근무하는 사원들의 사번 및 이름을 출력하시오.
1 select employee_id, last_name
2 from employees
3 where department_id in (select department_id from employees where last_name like '%u%')예제32) Ernst와 동일한 부서에 근무하는 사원중 급여가 5000보단 큰 사원의 이름과 급여를 출력하시오
1 select last_name, salary
2 from employees
3 where department_id i= (select department_id from employees where last_name = 'Ernst')
4* and salary>5000예제33) 50번부서의 평균급여보다 더 많은 급여를 받는 사원의 이름과 급여와 부서번호를 출력하세요
1 select last_name, salary, department_id
2 from employees
3* where salary > (select avg(salary) from employees where department_id=50)예제34) 50번 부서의 최저급여보다 더 많은 최저급여를 받는 부서별 최저급여를 출력하시오
1 select department_id, min(salary)
2 from employees
3 group by department_id
4* having min(salary) > (select min(salary) from employees where department_id=50)
DEPARTMENT_ID MIN(SALARY)
------------- -----------
100 6900
30 2500
7000
90 17000
20 6000
70 10000
110 8300
80 6100
40 6500
60 4200
10 4400
11 rows selected.예제35) 부서별 최대 급여를 받는 사원의 번호, 이름과 급여를 출력하시오
SQL> 1 select employee_id, last_name, salary
2 from employees
3* where salary in (select max(salary) from employees group by department_id);36. IT_PROG 직업을 가진 사원의 최대급여보다 더 많은 급여를 받은 사원의 번호와 이름과 급여를 출력하시오
select employee_id, last_name, salary
from employees
where salary>all(select salary from employees where job_id = 'IT_PROG')
and job id <> 'IT_PROG'37.시애틀에 근무하는 사람 중 커미션을 받지않은 모든 사람들의 이름, 부서명, 지역 ID를 출력하시오
1 select e.last_name, d.department_name, d.location_id
2 from employees e, departments d
3 where e.department_id = d.department_id
4 and d.location_id = (select location_id from locations where city = 'Seattle')
5* and e.commission_pct is null38. 이름이 DAVLES 인 사람보다 후에 고용된 사원들의 이름 및 고용일자를 출력하시오. 고용일자를 역순으로 출력하시오.
1 select last_name, hire_date
2 from employees
3 where hire_date > (select hire_date from employees where last_name='Davies')
4* order by hire_date desc39. king을 매니저로 두고 있는 모든 사원들의 이름 및 급여를 출력하시오.
1 select last_name, salary, manager_id
2 from employees
3 where manager_id in (select employee_id from employees where last_name='King')40. 회사 전체 평균급여보다 더 많이 받는 사원들 중 이름에 u가 있는 사원들이 근무하는 부서에서 근무하는 사원들의 사번, 이름 및 급여를 출력하시오.
select employee_id, last_name, salary
from employees
where department_id in (select department_id from employees where last_name like '%u%' and salary> (select avg(salary)from employees));ex) 최저 급여를 받는 사원보다 더 많은 급여를 받는 사원의 이름과 급여를 출력하시오
select last_name, salary
from employees
where salary>(select min(salary) from employees)ex) 이름에 t를 포함하고 있는 사원과 같은 부서에 근무하는 사원의 이름과 사원번호 와 부서번호를 출력하시오.
select employee_id, last_name, department_id
from employees
where department_id in (select department_id from employees where last_name like '%t%')<<오후 java - 반복문>>
[반복문]
: 어떤 조건에 만족할 때까지 또는 특정 횟수만큼 같은 처리를 반복하여 실행하는 구조
[반복문의 종류]
while문/ do~while문 / for문
[while문]
:정확하게 몇 번 반복해야 할 지 정해지지 않은 경우에 사용
: 몇 번 반복해야하는지 정해져 있지 않기 때문에 반복문을 수행할 조건을 지정해줘야함
while(조건식){
실행문장
}
다음문장
package while문;
import java.util.Scanner;
public class Ex01_1 {
public static void main(String[] args) {
//사용자가 키보드로 숫자 입력
//10보다 작든 숫자를 입력할 때에만 입력
//10보다 같거나 큰 숫자 입력하는 순간 그만 입력
Scanner sc = new Scanner(System.in);
int num = sc.nextInt();
if(num<10) {
int num2 = sc.nextInt();
if(num2<10) {
int num3= sc.nextInt();
if(num3<10) {
}
}
}
//while문
while(num<10) { //반복을 할 조건
num = sc.nextInt();
}
}
}package while문;
import java.util.Scanner;
public class Ex01_2 {
public static void main(String[] args) {
//while문
Scanner sc = new Scanner(System.in);
int num = 0; //사용자가 입력한 값을 저장
while(num<10) {
num = sc.nextInt();
}
}
}package while문;
import java.util.Scanner;
public class Ex01_3 {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
int num = 0;
//break는 가장 가까운 반복문을 나간다
while(true) { //무한반복
num = sc.nextInt();
if(num>=10) {//반복을 멈출 조건
//반복문 멈추는 키워드
//break : 가장 가까운 반복문을 나감
break;
}
}
}
}package while문;
import java.util.Scanner;
public class Ex01_4 {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
int num =0;
do {
num = sc.nextInt();
}while(num<10);
}
}package while문;
import java.util.Scanner;
public class EX01_5 {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
//입력한 값을 저장할 변수
int num = 0; //초기값-1 xxxx
//이전까지 입력한 값의 합을 저장한 변수(누적합 구하는 변수 초기값 : 0)
int sum = 0;
System.out.println("");
while(num!=-1) {//반복을 할조건
sum = sum+num; // sum +=num;
num =sc.nextInt();
}
System.out.println("합 : "+sum);
while(true) {
sum = sum+num; // sum +=num;
num =sc.nextInt();
if(num==-1) {//반복을 중단할 조건
break;
}
}
}
}
Q. 정수 n을 1씩 증가시키고 정수 n이 5가 되면 값 증가를 중단하시오.
int n =1;
while(n<5){
n++;
}
[Do~While문]
do~while문
:정확하게 몇 번 반복해야 할지 정해지지 않은 경우에 사용
:몇 번 반복해야하는지 정해져 있지 않기 때문에 반복문을 수행할 조건을 지정해줘야 함
while 문: 조건 확인 시 true 경우 지정된 명령 실행 false가 되는 순간 다음 명령 실행
do~while문: 지정된 명령 실행 후 조건 확인 시 true인 경우 다시 지정된 명령 실행 false가 되는 순간 다음 명령 실행
| do{ 실행문장 }while(조건식); 다음문장 |
* 논리형(boolean:true/false)일반적으로 비교연산, 논리연산 사용
Q. 정수n을 1씩 증가시키고 정수n이 5가 되면 값 증가를 중단하시오.
| int n =1; do { n++ }while(n<5); 다음문장 |
[while/do~while문 예제]
Q. 키보드로부터 입력한 숫자를 입력받아 홀수와 짝수가 각각 몇 개 입력되었는지 출력하는 프로그램을 작성하시오(단, -1을 입력한 경우 프로그램 종료)
package while문;
import java.util.Scanner;
public class EX01_6 {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
int num = 0;
int n1 = 0;//홀수
int n2 = 0;//짝수
while(num !=-1) {
System.out.print("숫자 입력 : ");
num = sc.nextInt();
if(num%2==1) {
++n1;
}else if(num%2==0){
++n2;
}
}System.out.println("홀수 개수 : "+n1);
System.out.println("짝수 개수 : "+n2);
}
}
package while문;
import java.util.Scanner;
public class Ex01_07 {
public static void main(String[] args) {
Scanner sc =new Scanner(System.in);
//현재 몸무게, 목표 몸무게 입력
System.out.print("현재 몸무게 : ");
int cur = sc.nextInt();
System.out.print("목표 몸무게 :");
int goal = sc.nextInt();
int week = 1;// 주차 번호
while(true) {
System.out.print(week + "주차 감량 몸무게 : ");
week++; //week+week+1
int weight = sc.nextInt(); // 각 주차별 감량한 몸무게
//현재 몸무게 수정
cur = cur - weight; // cur -=weight;
if(cur <=goal){
break;
}
}
System.out.println("현재 몸무게 : "+ cur);
}
}package while문;
import java.util.Scanner;
public class Ex01_8 {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
while(true) {
System.out.print("아이디 : ");
String id = sc.next();
System.out.print("비밀번호 : ");
int pw = sc.nextInt();
if((id == "smhrd") && (pw == 1234)) {
System.out.println("로그인 성공");
}else {
System.out.println("로그인 실패");
}
}
}
}2번까지
for, 이중for문까지
목요일8/11 저녁에 보충수업