Content |
select count(*) from employees; --컨트롤 엔터
select * from employees;
SELECT *-- first_name, last_name, salary
FROM employees
WHERE department_id=50;
select last_name, first_name, salary, salary + salary*0.1 as 새연봉, hire_date
from employees;
select first_name as 이름, department_id 부서번호, commission_pct "commPct"
from employees;
select first_name || ' ' || last_name || '''s salary is ' || salary
from employees;
select distinct department_id, job_id from employees;
select rowid, rownum from employees;
-- Neena의 급여를 10%인상한 금액은?
select salary*1.1 from employees where first_name='Neena';
select first_name, last_name from employees
where hire_date between '03/01/01' and '03/12/31';
select systimestamp from dual; --날짜 시간
select sysdate from dual; -- 날짜
select * from employees
where hire_date like '%/03/%'; -- long 타입에는 적용 안됨
select first_name, to_char(hire_date, 'YYYY') from employees;
-- 부서 번호가 20, 30, 40에 속하는 사원의 이름을 출력하세요.
select first_name from employees
where department_id in (20, 30, 40);
-- 직무 아이디가 IT_PROG이거나 FI_MGR인 사원 중 급여가 6000이상인 사원의 이름을 출력하세요.
SELECT first_name FROM employees
WHERE (job_id='IT_PROG' or job_id='FI_MGR') and salary>=6000;
select initcap('qwertyuiop') from dual;
select ltrim('javaspecialist', 'vaj') from dual;
-- 이메일에 lee를 포함하는 사원의 이름과 이메일 주소를 출력(대/소문자 구분 안함)
select first_name, email, salary from employees
where lower(email) like '%lee%';
-- Lex사원이 입사한지 1000일째 되는 날짜를 출력하세요.
select hire_date+1000 from employees
where first_name='Lex';
-- Nancy Greenberg 사원이 입사한지 500일째 되는 날짜를 출력하세요.
select hire_date + 500
from employees
where first_name='Nancy' and last_name='Greenberg';
select * from employees where department_id='10';
select to_number('5,000', '99,999')-4000 from dual;
select first_name, salary + salary*commission_pct
from employees;
select first_name, salary + salary*nvl(commission_pct, 0)
from employees;
select first_name, salary + salary*nvl2(commission_pct, commission_pct, 0)
from employees;
select employee_id, first_name
from employees
where hire_date like '04%'
union
select employee_id, department_id || ''
from employees
where department_id=20;
select employee_id, first_name, null as deptid
from employees
where hire_date like '04%'
union
select employee_id, null, department_id
from employees
where department_id=20;
select department_id, count(first_name)
from employees
group by department_id; |