ORACLE sql 실습
--비교연산자
--select * [col1, col2, ...]
--from table_name
--where condition
select * from employees
where salary>=1500; --비교연산자>=크거나 같다.
select * from employees where department_id = 10; --equal연산
--문자열 조건으로 조회하기
select * from employees
where first_name = 'Whalen'; --문자열 조건 비교시, 문자열은 ''로 묶어서 비교
--날짜 조건으로 조회하기
--select * from employees
--where hire_date <= '199'
select * from employee
where salary >= 1500;
select * from employee
where dno = 10;
select * from employee
where ename=SCOTT;
select * from employee
where ename = 'SCOTT';
select * from employee
where hiredate<='1981/01/01';
select * from employee where dno=10 and job= 'MANAGER';
select * from employee where dno=10 or job ='MANAGER';
select * from employee where not dno = 10;
select * from employee
where dno <> 10
select * from employee where salary>=1000 and salary <=1500;
select * from employee where commission = 300 or commission=500 or commission =1400;
select * from employee where salary between 1000 and 1500;
select * from employee
where salary>=1000 and salary<=1500;
select * from employee
where salary not between 1000 and 1500;
select * from employee where salary <1000 or salary>1500;
select employee_id, first_name||last_name empname, hire_date, job_id, manager_id, department_id from employees
where department_id =100 or manager is null or salary <10000;
select * from SALGRADE;
select * from employees where salary >=1000 and salary <=15000 order by employee_id;
select * from emp order by empno;
select empno,count(*) from emp group by empno;
--between연산자
select * from employees where salary between 1000 and 15000;
--not between
select * from employees where salary between 1000 and 15000;
--in 연산자
--나열형 조건
select * from employee where commission in(300,500,1400);
select * from employee where commission not in(300,500,1400);
select * from employee where ename like 'F%';
select * from employee
where ename like '%M%';
select * from employee where ename like '%N';
select * from employee
where ename like '_A%';
select * from employee
where ename like '__A%';
select * from employee where ename not like '%A%';
select ename, dno, commission from employee;
select * from employee where commission is null;
select * from employee where commission is not null;
select * from employee order by salary asc;
--오름차순
select * from employee order by salary;
--내림차순
select * from employee order by salary desc;
select * from employee order by ename;
select * from employee order by ename desc;
select * from emp where ename like '%M%';
select * from employee order by hiredate desc;
select * from employee order by salary desc, ename asc;
select ename, salary from employee where salary>2000 order by salary desc;
select ename, dno from employee where eno = '7788';
select ename, salary from employee where salary not between 2000 and 3000;
select ename, job, hiredate from employee where hiredate between '81/02/20' and '81/05/01';
select ename dno from employee where dno in(20,30) order by ename;
select ename, salary, dno from employee where salary between 2000 and 3000 and dno in(20,30) order by ename;
select ename, hiredate from employee where hiredate like '81%';
select ename, job from employee where manager is null;
select ename, salary,commission from employee where commission is not null order by salary, commission;
select ename from employee where ename like '__R%';
select ename from employee where ename like '%A%' and ename like '%E%';
select ename, job, salary from employee where job in('CLERK','salesman')
and salary not in (1600,950,1300);
select ename, salary, commission from employee where commission >=500;
댓글