Oracle's to_char function is applied correctly using the column hire_date of type DATE as argument with formats models DD for day-of-month. By the way, this function with most of the format-literals (at least DD and MON) is also supported by PostgreSQL and MySQL. Possible Issues: with the abbreviated month-nameI wonder if mon for month abbreviated name works correctly. At least the right-hand side of month is an issue because lowercase dec instead uppercase DEC. Also pay attention to the locale used to format month-names. In English locales DEC for December should work. For the month of March as stated in your question, you would use 'MAR' as right-hand side of the month-comparison (not DEC). SimplificationSimpler is to use combined format like TO_CHAR(hire_date, 'DD-MON') = '23-DEC' respectively using the month-abbreviation in your database-locale. Test using group-by with countSELECT TO_CHAR(hire_date, 'DD-MON') as hired_day, COUNT(*) FROM employees GROUP BY TO_CHAR(hire_date, 'DD-MON') WHERE TO_CHAR(hire_date, 'DD') = '23'Should return the day and month (formatted in your locale) with a count of hired employees - restricted to the 23th for all months recorded (max 12 rows in result-set). You can also omit the last line with WHERE condition to get all days, resulting in a longer list.
SELECT * FROM JOBS WHERE MIN_SALARY > 10000
SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE, 'YYYY') BETWEEN 2002 AND 2005 ORDER BY HIRE_DATE
SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES WHERE JOB_ID IN ('IT_PROG', 'SA_MAN')
SELECT * FROM EMPLOYEES where hire_date > '01-jan-2008'
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID in (150,160)
SELECT FIRST_NAME, SALARY, COMMISSION_PCT, HIRE_DATE FROM EMPLOYEES WHERE SALARY < 10000
SELECT JOB_TITLE, MAX_SALARY-MIN_SALARY DIFFERENCE FROM JOBS WHERE MAX_SALARY BETWEEN 10000 AND 20000
SELECT FIRST_NAME, SALARY, ROUND(SALARY, -3) FROM EMPLOYEES
SELECT * FROM JOBS ORDER BY JOB_TITLE
SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE FIRST_NAME LIKE 'S%' OR LAST_NAME LIKE 'S%'
SELECT * FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE, 'MON')= 'MAY'
SELECT * FROM EMPLOYEES WHERE COMMISSION_PCT IS NULL AND SALARY BETWEEN 5000 AND 10000 AND DEPARTMENT_ID=30
SELECT FIRST_NAME, HIRE_DATE, LAST_DAY(HIRE_DATE)+1 FROM EMPLOYEES
SELECT FIRST_NAME, HIRE_DATE, FLOOR((SYSDATE-HIRE_DATE)/365)FROM EMPLOYEES
SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE, 'YYYY')=2001
SELECT INITCAP(FIRST_NAME), INITCAP(LAST_NAME) FROM EMPLOYEES
SELECT JOB_TITLE, SUBSTR(JOB_TITLE,1, INSTR(JOB_TITLE, ' ')-1) FROM JOBS
SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE INSTR(LAST_NAME,'B') > 3
SELECT UPPER(FIRST_NAME), LOWER(EMAIL) FROM EMPLOYEES WHERE UPPER(FIRST_NAME)= UPPER(EMAIL)
SELECT * FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'YYYY')=TO_CHAR(SYSDATE, 'YYYY')
SELECT SYSDATE - to_date('01-jan-2011') FROM DUAL
SELECT TO_CHAR(HIRE_DATE,'MM'), COUNT (*) FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'YYYY')= TO_CHAR(SYSDATE,'YYYY') GROUP BY TO_CHAR(HIRE_DATE,'MM')
SELECT MANAGER_ID, COUNT(*) FROM EMPLOYEES GROUP BY MANAGER_ID
SELECT EMPLOYEE_ID, MAX(END_DATE) FROM JOB_HISTORY GROUP BY EMPLOYEE_ID
SELECT COUNT(*) FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'DD') > 15
SELECT COUNTRY_ID, COUNT(*) FROM LOCATIONS GROUP BY COUNTRY_ID
SELECT DEPARTMENT_ID, AVG(SALARY) FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NULL GROUP BY DEPARTMENT_ID
SELECT JOB_ID, COUNT(*), SUM(SALARY), MAX(SALARY)-MIN(SALARY) SALARY FROM EMPLOYEES GROUP BY JOB_ID
SELECT JOB_ID, AVG(SALARY) FROM EMPLOYEES GROUP BY JOB_ID HAVING AVG(SALARY)>10000
SELECT TO_CHAR(HIRE_DATE,'YYYY') FROM EMPLOYEES GROUP BY TO_CHAR(HIRE_DATE,'YYYY') HAVING COUNT(EMPLOYEE_ID) > 10
SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NULL GROUP BY DEPARTMENT_ID HAVING COUNT(COMMISSION_PCT)>5
SELECT EMPLOYEE_ID FROM JOB_HISTORY GROUP BY EMPLOYEE_ID HAVING COUNT(*) > 1
SELECT JOB_ID FROM JOB_HISTORY WHERE END_DATE-START_DATE > 100 GROUP BY JOB_ID HAVING COUNT(*)>3
SELECT DEPARTMENT_ID, TO_CHAR(HIRE_DATE,'YYYY'), COUNT(EMPLOYEE_ID) FROM EMPLOYEES GROUP BY DEPARTMENT_ID, TO_CHAR(HIRE_DATE, 'YYYY') ORDER BY DEPARTMENT_ID
SELECT DISTINCT DEPARTMENT_ID FROM EMPLOYEES GROUP BY DEPARTMENT_ID, MANAGER_ID HAVING COUNT(EMPLOYEE_ID) > 5
UPDATE EMPLOYEES SET SALARY = 8000 WHERE EMPLOYEE_ID = 115 AND SALARY < 6000
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE,JOB_ID, SALARY, DEPARTMENT_ID) VALUES (207, 'ANGELA', 'SNYDER','ANGELA','215 253 4737', SYSDATE, 'SA_MAN', 12000, 80) DELETE FROM DEPARTMENTS WHERE DEPARTMENT_ID=20
UPDATE EMPLOYEES SET JOB_ID= 'IT_PROG' WHERE EMPLOYEE_ID=110 AND DEPARTMENT_ID=10 AND NOT JOB_ID LIKE 'IT%'
INSERT INTO DEPARTMENTS VALUES(150,'SPORTS',120,1200)
SELECT DEPARTMENT_NAME, COUNT(*) FROM EMPLOYEES NATURAL JOIN DEPARTMENTS GROUP BY DEPARTMENT_NAME
SELECT EMPLOYEE_ID, JOB_TITLE, END_DATE-START_DATE DAYS FROM JOB_HISTORY NATURAL JOIN JOBS WHERE DEPARTMENT_ID=30
SELECT DEPARTMENT_NAME, FIRST_NAME FROM DEPARTMENTS D JOIN EMPLOYEES E ON (D.MANAGER_ID=E.EMPLOYEE_ID)
SELECT DEPARTMENT_NAME, FIRST_NAME, CITY FROM DEPARTMENTS D JOIN EMPLOYEES E ON (D.MANAGER_ID=E.EMPLOYEE_ID) JOIN LOCATIONS L USING (LOCATION_ID)
SELECT COUNTRY_NAME, CITY, DEPARTMENT_NAME FROM COUNTRIES JOIN LOCATIONS USING (COUNTRY_ID) JOIN DEPARTMENTS USING (LOCATION_ID)
SELECT JOB_TITLE, DEPARTMENT_NAME, LAST_NAME, START_DATE FROM JOB_HISTORY JOIN JOBS USING (JOB_ID) JOIN DEPARTMENTS USING (DEPARTMENT_ID) JOIN EMPLOYEES USING (EMPLOYEE_ID) WHERE TO_CHAR(START_DATE,'YYYY') BETWEEN 2000 AND 2005
SELECT JOB_TITLE, AVG(SALARY) FROM EMPLOYEES NATURAL JOIN JOBS GROUP BY JOB_TITLE
SELECT JOB_TITLE, FIRST_NAME, MAX_SALARY-SALARY DIFFERENCE FROM EMPLOYEES NATURAL JOIN JOBS
SELECT JOB_TITLE, FIRST_NAME, MAX_SALARY-SALARY DIFFERENCE FROM EMPLOYEES NATURAL JOIN JOBS WHERE DEPARTMENT_ID = 30
SELECT JH.* FROM JOB_HISTORY JH JOIN EMPLOYEES E ON (JH.EMPLOYEE_ID = E.EMPLOYEE_ID) WHERE SALARY > 15000
SELECT DEPARTMENT_NAME, FIRST_NAME, SALARY FROM DEPARTMENTS D JOIN EMPLOYEES E ON (D.MANAGER_ID=E.MANAGER_ID) WHERE (SYSDATE-HIRE_DATE) / 365 > 5
SELECT FIRST_NAME FROM EMPLOYEES E1 JOIN EMPLOYEES E2 ON (E1.MANAGER_ID=E2.EMPLOYEE_ID) WHERE E1.HIRE_DATE < E2.HIRE_DATE
SELECT FIRST_NAME, JOB_TITLE FROM EMPLOYEES E JOIN JOB_HISTORY JH ON (JH.EMPLOYEE_ID = E.EMPLOYEE_ID) JOIN JOBS J ON( JH.JOB_ID = J.JOB_ID) WHERE MONTHS_BETWEEN(END_DATE,START_DATE) < 6
SELECT FIRST_NAME, COUNTRY_NAME FROM EMPLOYEES JOIN DEPARTMENTS USING(DEPARTMENT_ID) JOIN LOCATIONS USING( LOCATION_ID) JOIN COUNTRIES USING ( COUNTRY_ID)
SELECT DEPARTMENT_NAME, AVG(SALARY), COUNT(COMMISSION_PCT) FROM DEPARTMENTS JOIN EMPLOYEES USING (DEPARTMENT_ID) GROUP BY DEPARTMENT_NAME
SELECT TO_CHAR(HIRE_DATE,'MON-YY') FROM EMPLOYEES JOIN DEPARTMENTS USING (DEPARTMENT_ID) JOIN LOCATIONS USING (LOCATION_ID) WHERE CITY = 'Seattle' GROUP BY TO_CHAR(HIRE_DATE,'MON-YY') HAVING COUNT(*) > 5
SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_ID IN ( SELECT DEPARTMENT_ID FROM EMPLOYEES GROUP BY DEPARTMENT_ID HAVING MAX(SALARY)>10000)
SELECT * FROM DEPARTMENTS WHERE MANAGER_ID IN (SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE FIRST_NAME='SMITH')
SELECT * FROM JOBS WHERE JOB_ID IN (SELECT JOB_ID FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY'))
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID NOT IN (SELECT EMPLOYEE_ID FROM JOB_HISTORY)
SELECT JOB_TITLE, AVG(SALARY) FROM JOBS NATURAL JOIN EMPLOYEES GROUP BY JOB_TITLE WHERE EMPLOYEE_ID IN (SELECT EMPLOYEE_ID FROM JOB_HISTORY)
SELECT COUNTRY_NAME, CITY, COUNT(DEPARTMENT_ID) FROM COUNTRIES JOIN LOCATIONS USING (COUNTRY_ID) JOIN DEPARTMENTS USING (LOCATION_ID) WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES GROUP BY DEPARTMENT_ID HAVING COUNT(DEPARTMENT_ID)>5) GROUP BY COUNTRY_NAME, CITY;
SELECT FIRST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID IN (SELECT MANAGER_ID FROM EMPLOYEES GROUP BY MANAGER_ID HAVING COUNT(*)>5)
SELECT FIRST_NAME, JOB_TITLE, START_DATE, END_DATE FROM JOB_HISTORY JH JOIN JOBS J USING (JOB_ID) JOIN EMPLOYEES E ON ( JH.EMPLOYEE_ID = E.EMPLOYEE_ID) WHERE COMMISSION_PCT IS NULL
SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_ID NOT IN ( SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE FLOOR((SYSDATE-HIRE_DATE)/365) < 2)
SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID IN (SELECT EMPLOYEE_ID FROM JOB_HISTORY) GROUP BY DEPARTMENT_ID HAVING MAX(SALARY) >10000)
SELECT * FROM JOBS WHERE JOB_ID IN (SELECT JOB_ID FROM EMPLOYEES WHERE EMPLOYEE_ID IN (SELECT EMPLOYEE_ID FROM JOB_HISTORY WHERE JOB_ID='IT_PROG'))
SELECT DEPARTMENT_ID,FIRST_NAME, SALARY FROM EMPLOYEES OUTER WHERE SALARY = (SELECT MAX(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID = OUTER.DEPARTMENT_ID)
SELECT CITY FROM LOCATIONS WHERE LOCATION_ID = (SELECT LOCATION_ID FROM DEPARTMENTS WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID=105) )
select salary from employees main where 2 = (select count( distinct salary ) from employees where salary > main.salary)
select max(salary) from hr.employees PL/SQL Programs
Declare V_salary_120 employees.salary%type; Begin Select salary into v_salary_120 From employees where employee_id = 120; Update employees set salary = ( select salary from employees where employee_id = 122) Where employee_id = 120; Update employees set salary = v_salary_120 Where employee_id = 122; Commit; End;
declare v_exp number(2); v_hike number(5,2); begin select floor((sysdate-hire_date) / 365 ) into v_exp from employees where employee_id = 115; v_hike := 1.05; case when v_exp > 10 then v_hike := 1.20; when v_exp > 5 then v_hike := 1.10; end case; update employees set salary = salary * v_hike where employee_id = 115; end;
declare v_salary employees.salary%type; v_exp number(2); v_cp number(5,2); begin select v_salary, floor ( (sysdate-hire_date)/365) into v_salary, v_exp from employees where employee_id = 150; if v_salary > 10000 then v_cp := 0.4; elsif v_exp > 10 then v_cp := 0.35; elsif v_salary < 3000 then v_cp := 0.25; else v_cp := 0.15; end if; update employees set commission_pct = v_cp where employee_id = 150; end;
declare v_name employees.first_name%type; v_deptname departments.department_name%type; begin select first_name , department_name into v_name, v_deptname from employees join departments using (department_id) where employee_id = ( select manager_id from employees where employee_id = 103); dbms_output.put_line(v_name); dbms_output.put_line(v_deptname); end;
declare v_min number(3); v_max number(3); v_c number(1); begin select min(employee_id), max(employee_id) into v_min, v_max from employees; for i in v_min + 1 .. v_max - 1 loop select count(*) into v_c from employees where employee_id = i; if v_c = 0 then dbms_output.put_line(i); end if; end loop; end;
declare v_year number(4); v_c number(2); begin select to_char(hire_date,'yyyy') into v_year from employees group by to_char(hire_date,'yyyy') having count(*) = ( select max( count(*)) from employees group by to_char(hire_date,'yyyy')); dbms_output.put_line('Year : ' || v_year); for month in 1 .. 12 loop select count(*) into v_c from employees where to_char(hire_date,'mm') = month and to_char(hire_date,'yyyy') = v_year; dbms_output.put_line('Month : ' || to_char(month) || ' Employees : ' || to_char(v_c)); end loop; end;
declare v_salary employees.salary%type; begin select salary into v_salary from employees where first_name = 'Joe'; update employees set salary = v_salary where employee_id = 130; exception when no_data_found then update employees set salary = (select avg(salary) from employees) where employee_id = 130; end;
declare cursor jobscur is select job_id, job_title from jobs; v_name employees.first_name%type; begin for jobrec in jobscur loop select first_name into v_name from employees where hire_date = ( select min(hire_date) from employees where job_id = jobrec.job_id) and job_id = jobrec.job_id; dbms_output.put_line( jobrec.job_title || '-' || v_name); end loop; end;
declare cursor empcur is select employee_id, first_name from employees; begin for emprec in empcur loop if empcur%rowcount > 4 then dbms_output.put_line( emprec.first_name); exit when empcur%rowcount > 10; end if; end loop; end;
declare cursor empcur is select employee_id, department_id, commission_pct from employees; v_hike number(2); begin for emprec in empcur loop if emprec.department_id = 40 then v_hike := 10; elsif emprec.department_id = 70 then v_hike := 15; elsif emprec.commission_pct > 0.30 then v_hike := 5; else v_hike := 10; end if; update employees set salary = salary + salary * v_hike/100 where employee_id = emprec.employee_id; end loop; end;
create or replace function get_dept_manager_name(deptid number) return varchar is v_name employees.first_name%type; begin select first_name into v_name from employees where employee_id = ( select manager_id from departments where department_id = deptid); return v_name; end;
create or replace function get_no_of_jobs_done(empid number) return number is v_count number(2); begin select count(*) into v_count from job_history where employee_id = empid; return v_count; end;
create or replace procedure change_dept_manager(deptid number) is v_empid employees.employee_id%type; begin select employee_id into v_empid from employees where salary = ( select max(salary) from employees where department_id = deptid) and department_id = deptid; update departments set manager_id = v_empid where department_id = deptid; end;
create or replace function get_employees_for_manager(manager number) return varchar2 is v_employees varchar2(1000) := ''; cursor empcur is select first_name from employees where manager_id = manager; begin for emprec in empcur loop v_employees := v_employees || ',' || emprec.first_name; end loop; -- remove extra , at the beginning return ltrim(v_employees,','); end;
create or replace trigger trg_employees_time_check before update or insert or delete on employees for each row begin if to_char(sysdate,'hh24') < 6 or to_char(sysdate,'hh24') > 10 then raise_application_error(-20111,'Sorry! No change can be made before 6 AM and after 10 PM'); end if; end;
create or replace trigger trg_employees_salary_check before update on employees for each row begin if :old.salary > :new.salary then raise_application_error(-20111,'Sorry! Salary can not be decreased!'); end if; end;
Note: This trigger need to read the row that is being modified, which causes mutating problem. The solution to mutating problem is explained at: Trigger with mutating problem Please check it out.
create or replace trigger trg_log_job_change after update of job_id on employees for each row declare v_enddate date; v_startdate date; begin -- find out whether the employee has any row in job_history table select max(end_date) into v_enddate from job_history where employee_id = :old.employee_id; if v_enddate is null then v_startdate := :old.hire_date; else v_startdate := v_enddate + 1; end if; insert into job_history values (:old.employee_id, v_startdate, sysdate, :old.job_id, :old.department_id); end; Note: Before testing the above trigger, you need to disable UPDATE_JOB_HISTORY trigger, which is already present in HR account, as it does the same. I hope you all have enjoyed reading this article. Comments are welcome....Related Posts: - Trigger with mutating problem Page 2
|