Queries 1. Display Display details details of jobs jobs where where the minimum minimum salary salary is greater greater than than 10000. 10000. SELECT * FROM JOBS WHERE MIN_SALARY > 10000
2. Display Display the first first name and join join date of the employees employees who who joined between between 2002 2002 and 2005. SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE, 'YYYY') BETWEEN 2002 AND 2005 ORDER BY HIRE_DATE
3. Display Display first first name and join date date of the employees employees who is either either IT Program Programmer mer or ales !an. SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES WHERE JOB_ID IN ('IT_PRO', 'SA_MAN')
". Display Display employe employees es who joined joined after after 1st 1st #anuary #anuary 200$. 200$. SELECT * FROM EMPLOYEES
!"#$# "%$#_&# > '01+200'
5. Display Display detail details s of emplo employee yee with ID 150 or 1%0. 1%0. SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID %+ (150,1-0)
%. Display first first name& salary& salary& 'ommission p't& and hire date for employees employees with salary less than 10000. SELECT FIRST_NAME, SALARY, COMMISSION_PCT, HIRE_DATE FROM EMPLOYEES WHERE SALARY . 10000
(. Display Display job Title& Title& the differen'e differen'e between between minimum minimum and ma)imum ma)imum salarie salaries s for jobs with ma) salary in the range 10000 to 20000. SELECT JOB_TITLE, MA/_SALARYMIN_SALARY DIFFERENCE FROM JOBS WHERE MA/_SALARY BETWEEN 10000 AND 20000
$. Display Display first first name& salary salary&& and round round the salary salary to thousand thousands. s. SELECT FIRST_NAME, SALARY, ROND(SALARY, ) FROM EMPLOYEES
*. Display Display details details of jobs jobs in the the des'endin des'ending g order order of the title. title. SELECT * FROM JOBS ORDER BY JOB_TITLE
10. Display employees where where the first name or last name starts with .
SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE OR LAST_NAME LIE 'S3'
FIRST_NAME
LIE 'S3'
11. Display employees who joined in the month of !ay. SELECT * FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE, 'MON')4 'MAY'
12. Display details of the employees where 'ommission per'entage is null and salary in the range 5000 to 10000 and department is 3 0. SELECT * FROM EMPLOYEES WHERE COMMISSION_PCT IS NLL AND SALARY BETWEEN 5000 AND 10000 AND DEPARTMENT_ID40
13. Display first name and date of first salary of the employees. SELECT FIRST_NAME, HIRE_DATE, LAST_DAY(HIRE_DATE)1 FROM EMPLOYEES
1". Display first name and e)perien'e of the employees. SELECT FIRST_NAME, HIRE_DATE, FLOOR((SYSDATEHIRE_DATE)6-5)FROM EMPLOYEES
15. Display first name of employees who joined in 2001. SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE, 'YYYY')42001
1%. Display first name and last name after 'on+erting the first letter of ea'h name to upper 'ase and the rest to lower 'ase. SELECT INITCAP(FIRST_NAME), INITCAP(LAST_NAME) FROM EMPLOYEES
1(. Display the first word in job title. SELECT JOB_TITLE, JOBS
SBSTR(JOB_TITLE,1, INSTR(JOB_TITLE, ' ')1) FROM
1$. Display the length of first name for employees where last name 'ontain 'hara'ter ,bafter 3rd position. SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE INSTR(LAST_NAME,'B') >
1*. Display first name in upper 'ase and email address in lower 'ase for employees where the first name and email address are same irrespe'ti+e of the 'ase. SELECT PPER(FIRST_NAME), LOWER(EMAIL) FROM EMPLOYEES WHERE PPER(FIRST_NAME)4 PPER(EMAIL)
20. Display employees who joined in the 'urrent year.
SELECT * FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'YYYY')4TO_CHAR(SYSDATE, 'YYYY')
21. Display the number of days between system date and 1st #anuary 2011. SELECT SYSDATE 7_&#('01+2011') FROM DAL
22. Display how many employees joined in ea'h month of the 'urrent year. SELECT TO_CHAR(HIRE_DATE,'MM'), CONT (*) FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'YYYY')4 TO_CHAR(SYSDATE,'YYYY') ROP BY TO_CHAR(HIRE_DATE,'MM')
23. Display manager ID and number of employees managed by the manager. SELECT MANAER_ID, CONT(*) FROM EMPLOYEES ROP BY MANAER_ID
2". Display employee ID and the date on whi'h he ended his pre+ious job. SELECT EMPLOYEE_ID, MA/(END_DATE) FROM JOB_HISTORY ROP BY EMPLOYEE_ID
25. Display number of employees joined after 15th of the month. SELECT CONT(*) FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'DD') > 15
2%. Display the 'ountry ID and number of 'ities we ha+e in the 'ountry. SELECT CONTRY_ID,
CONT(*)
FROM LOCATIONS ROP BY CONTRY_ID
2(. Display a+erage salary of employees in ea'h department who ha+e 'ommission per'entage. SELECT DEPARTMENT_ID, A8(SALARY) FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NLL ROP BY DEPARTMENT_ID
2$. Display job ID& number of employees& sum of salary& and differen'e between highest salary and lowest salary of the employees of the job. SELECT JOB_ID, CONT(*), SM(SALARY), MA/(SALARY)MIN(SALARY) SALARY FROM EMPLOYEES ROP BY JOB_ID
2*. Display job ID for jobs with a+erage salary more than 10000. SELECT JOB_ID, A8(SALARY) FROM EMPLOYEES ROP BY JOB_ID HA8IN A8(SALARY)>10000
30. Display years in whi'h more than 10 employees joined. SELECT TO_CHAR(HIRE_DATE,'YYYY') FROM EMPLOYEES ROP BY TO_CHAR(HIRE_DATE,'YYYY')
HA8IN CONT(EMPLOYEE_ID) > 10
31. Display departments in whi'h more than fi+e employees ha+e 'ommission per'entage. SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NLL ROP BY DEPARTMENT_ID HA8IN CONT(COMMISSION_PCT)>5
32. Display employee ID for employees who did more than one job in the past. SELECT EMPLOYEE_ID FROM JOB_HISTORY ROP BY EMPLOYEE_ID HA8IN CONT(*) > 1
33. Display job ID of jobs that were done by more than 3 employees for more than 100 days. SELECT JOB_ID FROM JOB_HISTORY WHERE END_DATESTART_DATE > 100 ROP BY JOB_ID HA8IN CONT(*)>
3". Display department ID& year& and umber of employees joined. SELECT DEPARTMENT_ID, TO_CHAR(HIRE_DATE,'YYYY'), CONT(EMPLOYEE_ID) FROM EMPLOYEES ROP BY DEPARTMENT_ID, TO_CHAR(HIRE_DATE, 'YYYY') ORDER BY DEPARTMENT_ID
35. Display departments where any manager is managing more than 5 employees. SELECT DISTINCT DEPARTMENT_ID FROM EMPLOYEES ROP BY DEPARTMENT_ID, MANAER_ID HA8IN CONT(EMPLOYEE_ID) > 5
3%. /hange salary of employee 115 to $000 if the e)isting salary is less than %000. PDATE EMPLOYEES SET SALARY 4 000 WHERE EMPLOYEE_ID 4 115 AND SALARY . -000
3(. Insert a new employee into employees with all the reuired details. INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NMBER, HIRE_DATE,JOB_ID, SALARY, DEPARTMENT_ID) 8ALES (209, 'ANELA', 'SNYDER','ANELA',' 215 25 :99', SYSDATE, 'SA_MAN', 12000, 0)
3$. Delete department 20.
DELETE FROM DEPARTMENTS WHERE DEPARTMENT_ID420
3*. /hange job ID of employee 110 to ITP4 if the employee belongs to department 10 and the e)isting job ID does not start with IT. PDATE EMPLOYEES SET JOB_ID4 'IT_PRO' WHERE EMPLOYEE_ID4110 AND DEPARTMENT_ID410 AND NOT JOB_ID LIE 'IT3'
"0. Insert a row into departments table with manager ID 120 and lo'ation ID in any lo'ation ID for 'ity Toyo. INSERT INTO DEPARTMENTS (150,'SPORTS',120,1200)
"1. Display department name and number of employees in the department. SELECT DEPARTMENT_NAME, CONT(*) FROM EMPLOYEES NATRAL JOIN DEPARTMENTS ROP BY DEPARTMENT_NAME
"2. Display job title& employee ID& number of days between ending date and starting date for all jobs in department 30 from job history. SELECT EMPLOYEE_ID, JOB_TITLE, END_DATESTART_DATE DAYS FROM JOB_HISTORY NATRAL JOIN JOBS WHERE DEPARTMENT_ID40
"3. Display department name and manager first name. SELECT DEPARTMENT_NAME, FIRST_NAME FROM DEPARTMENTS D JOIN EMPLOYEES E ON (D;MANAER_ID4E;EMPLOYEE_ID)
"". Display department name& manager name& and 'ity. SELECT DEPARTMENT_NAME, FIRST_NAME, CITY FROM DEPARTMENTS D JOIN EMPLOYEES E ON (D;MANAER_ID4E;EMPLOYEE_ID) JOIN LOCATIONS L SIN (LOCATION_ID)
"5. Display 'ountry name& 'ity& and department name. SELECT CONTRY_NAME, CITY, DEPARTMENT_NAME FROM CONTRIES JOIN LOCATIONS SIN (CONTRY_ID) JOIN DEPARTMENTS SIN (LOCATION_ID)
"%. Display job title& department name& employee last name& starting date for all jobs from 2000 to 2005. SELECT JOB_TITLE, DEPARTMENT_NAME, LAST_NAME, START_DATE FROM JOB_HISTORY JOIN JOBS SIN (JOB_ID) JOIN DEPARTMENTS SIN (DEPARTMENT_ID) JOIN EMPLOYEES SIN (EMPLOYEE_ID) WHERE TO_CHAR(START_DATE,'YYYY') BETWEEN 2000 AND 2005
"(. Display job title and a+erage salary of employees
SELECT JOB_TITLE, A8(SALARY) FROM EMPLOYEES NATRAL JOIN JOBS ROP BY JOB_TITLE
"$. Display job title& employee name& and the differen'e between ma)imum salary for the job and salary of the employee. SELECT JOB_TITLE, FIRST_NAME, MA/_SALARYSALARY DIFFERENCE FROM EMPLOYEES NATRAL JOIN JOBS
"*. Display last name& job title of employees who ha+e 'ommission per'entage and belongs to department 30. SELECT JOB_TITLE, FIRST_NAME, MA/_SALARYSALARY DIFFERENCE FROM EMPLOYEES NATRAL JOIN JOBS WHERE DEPARTMENT_ID 4 0
50. Display details of jobs that were done by any employee who is 'urrently drawing more than 15000 of salary. SELECT JH;* FROM JOB_HISTORY JH JOIN EMPLOYEES E ON (JH;EMPLOYEE_ID 4 E;EMPLOYEE_ID) WHERE SALARY > 15000
51. Display department name& manager name& and salary of the manager for all managers whose e)perien'e is more than 5 years. SELECT DEPARTMENT_NAME, FIRST_NAME, SALARY FROM DEPARTMENTS D JOIN EMPLOYEES E ON (D;MANAER_ID4E;MANAER_ID) WHERE (SYSDATEHIRE_DATE) 6 -5 > 5
52. Display employee name if the employee joined before his manager. SELECT FIRST_NAME FROM EMPLOYEES E1 JOIN EMPLOYEES E2 ON (E1;MANAER_ID4E2;EMPLOYEE_ID) WHERE E1;HIRE_DATE . E2;HIRE_DATE
53. Display employee name& job title for the jobs employee did in the past where the job was done less than si) months. SELECT FIRST_NAME, JOB_TITLE FROM EMPLOYEES E JOIN JOB_HISTORY JH ON (JH;EMPLOYEE_ID 4 E;EMPLOYEE_ID) JOIN JOBS J ON( JH;JOB_ID 4 J;JOB_ID) WHERE MONTHS_BETWEEN(END_DATE,START_DATE) . -
5". Display employee name and 'ountry in whi'h he is woring. SELECT FIRST_NAME, CONTRY_NAME FROM EMPLOYEES JOIN DEPARTMENTS SIN(DEPARTMENT_ID) JOIN LOCATIONS SIN( LOCATION_ID) JOIN CONTRIES SIN ( CONTRY_ID)
55. Display department name& a+erage salary and number of employees with 'ommission within the department.
SELECT DEPARTMENT_NAME, A8(SALARY), CONT(COMMISSION_PCT) FROM DEPARTMENTS JOIN EMPLOYEES SIN (DEPARTMENT_ID) ROP BY DEPARTMENT_NAME
5%. Display the month in whi'h more than 5 employees joined in any department lo'ated in ydney. SELECT TO_CHAR(HIRE_DATE,'MONYY') FROM EMPLOYEES JOIN DEPARTMENTS SIN (DEPARTMENT_ID) JOIN SIN (LOCATION_ID) WHERE CITY 4 'S#<#' ROP BY TO_CHAR(HIRE_DATE,'MONYY') HA8IN CONT(*) > 5
LOCATIONS
5(. Display details of departments in whi'h the ma)imum salary is more than 10000. SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_ID IN ( SELECT DEPARTMENT_ID FROM EMPLOYEES ROP BY DEPARTMENT_ID HA8IN MA/(SALARY)>10000)
5$. Display details of departments managed by ,mith-. SELECT * FROM DEPARTMENTS WHERE MANAER_ID IN (SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE FIRST_NAME4'SMITH')
5*. Display jobs into whi'h employees joined in the 'urrent year. SELECT * FROM JOBS WHERE JOB_ID IN (SELECT JOB_ID FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'YYYY')4TO_CHAR(SYSDATE,'YYYY'))
%0. Display employees who did not do any job in the past. SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID NOT IN (SELECT EMPLOYEE_ID FROM JOB_HISTORY)
%1. Display job title and a+erage salary for employees who did a job in the past. SELECT JOB_TITLE, A8(SALARY) FROM JOBS NATRAL JOIN EMPLOYEES ROP BY JOB_TITLE WHERE EMPLOYEE_ID IN (SELECT EMPLOYEE_ID FROM JOB_HISTORY)
%2. Display 'ountry name& 'ity& and number of departments where department has more than 5 employees. SELECT CONTRY_NAME, CITY, CONT(DEPARTMENT_ID) FROM CONTRIES JOIN LOCATIONS SIN (CONTRY_ID) JOIN DEPARTMENTS SIN (LOCATION_ID) WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES
ROP BY DEPARTMENT_ID HA8IN CONT(DEPARTMENT_ID)>5) ROP BY CONTRY_NAME, CITY=
%3. Display details of manager who manages more than 5 employees. SELECT FIRST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID IN (SELECT MANAER_ID FROM EMPLOYEES ROP BY MANAER_ID HA8IN CONT(*)>5)
%". Display employee name& job title& start date& and end date of past jobs of all employees with 'ommission per'entage null. SELECT FIRST_NAME, JOB_TITLE, START_DATE, END_DATE FROM JOB_HISTORY JH JOIN JOBS J SIN (JOB_ID) JOIN EMPLOYEES E ( JH;EMPLOYEE_ID 4 E;EMPLOYEE_ID) WHERE COMMISSION_PCT IS NLL
ON
%5. Display the departments into whi'h no employee joined in last two years. SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_ID NOT IN ( SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE FLOOR((SYSDATE HIRE_DATE)6-5) . 2)
%%. Display the details of departments in whi'h the ma) salary is greater than 10000 for employees who did a job in the past. SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID IN (SELECT EMPLOYEE_ID FROM JOB_HISTORY) ROP BY DEPARTMENT_ID HA8IN MA/(SALARY) >10000)
%(. Display details of 'urrent job for employees who wored as IT Programmers in the past. SELECT * FROM JOBS WHERE JOB_ID IN (SELECT JOB_ID FROM EMPLOYEES WHERE EMPLOYEE_ID IN (SELECT EMPLOYEE_ID FROM JOB_HISTORY WHERE JOB_ID4'IT_PRO'))
%$. Display the details of employees drawing the highest salary in the department. SELECT DEPARTMENT_ID,FIRST_NAME, SALARY FROM EMPLOYEES OTER WHERE SALARY 4 (SELECT MA/(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID 4 OTER;DEPARTMENT_ID)
%*. Display the 'ity of employee whose employee ID is 105. SELECT CITY FROM LOCATIONS WHERE LOCATION_ID 4 (SELECT LOCATION_ID FROM DEPARTMENTS WHERE DEPARTMENT_ID 4 (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID4105) )
(0. Display third highest salary of all employees #<#? <$@ $7 #<7@## %+ !"#$# 2 4 (#<#? ?7+( &%%+? <$@ ) $7 #<7@## !"#$# <$@ > %+;<$@)
