내용

글번호 1168
작성자 heojk
작성일 2022-12-17 16:59:55
제목 2022.12.17 SQL 1일차
내용 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;