Oracle Exercises and Answers (i) ____oracle

--1, select employees in Department 30

SELECT * from EMP where deptno=30;

--2, listing the names, numbers and departments of all clerks

Select Ename,empno,dname from EMP e INNER JOIN dept D on e.deptno = D.deptno where Job=upper (' clerk ');

--3, finding employees with a higher commission than salary

SELECT * FROM EMP where comm>sal;

--4, find employees with a commission above 60% of salary

SELECT * FROM EMP where comm>sal*0.6

--5, find out the details of all the managers in department 10 and all the clerks in department 20

SELECT * from EMP where (deptno=10 and Job=upper (' manager ')) or (Deptno=20 and Job=upper (' Clerk '));

--6, find out all the managers in department 10, all the clerks in department 20, and the details of all employees who are neither manager nor clerk but their salary >=2000

SELECT * from EMP where (deptno=10 and Job=upper (' manager ')) or (Deptno=20 and Job=upper (' Clerk ')) or Job<>upper (' Manager ') and Job<>upper (' clerk ') and sal>=2000)

--7, find out the different jobs of employees who collect commissions

SELECT DISTINCT job from EMP where comm>0;

--8, find employees who do not charge commissions or receive less than 100 commission

SELECT * from emp where NVL (comm,0) <100;

--9, find all employees employed on the last day of each month
SELECT * from emp where hiredate= last_day (hiredate);

--10, identify employees who were employed prior to 25

SELECT * from emp where Months_between (sysdate,hiredate)/12>25;

SELECT * from emp where hiredate<add_months (SYSDATE,-12*25);

--11, displaying the names of all employees with only the first letter capitalized

Select ename from emp where ENAME=INITCAP (ename);

--12, displaying employee names that are exactly 6 characters

Select ename from emp where length (ename) =6

--13, showing employee name without ' R '

Select ename from EMP where ename not like '%r% ';

Select ename from EMP where InStr (ename, ' R ') = 0;

--14, displays the first three characters of all employees ' names

Select substr (ename,1,3) from EMP

--15, display the names of all employees, replace all ' a ' with a

Select replace (ename, ' a ', ' a ') from EMP

--16, showing the names of all employees and the dates of 10 years of service

Select ename,add_months (hiredate,12*10) ' Service life Date ' from emp

--17, displaying details of the employee, sorted by name

Select * from emp ORDER by ename

--18, display employee name, according to its service life, the oldest employees in the front

Select ename from EMP hiredate

--19, display the names, jobs, and salaries of all employees, sorted by work in descending order, and work in ascending salary

Select ename,job,sal from emp-Job desc, sal ASC

--20, display the names of all employees and the year and month of incorporation, sorted by the month on which the employee was hired, and the first year of the item in the front

Select Ename,to_char (hiredate, ' yyyy '), To_char (hiredate, ' mm ') from EMP ORDER by hiredate ASC

--21, showing daily salary of all employees in the case of one months for 30 days

Select Ename,sal/30 from EMP;

--22, find all employees employed in February (in any year)

SELECT * from emp where TO_CHAR (hiredate, ' mm ') = ' 02 ';

--23, for each employee, show the number of days they joined the company

Select Ename,sysdate-hiredate from emp

--24, displaying the names of all the employees of "A" at any location in the Last Name field

Select ename from emp where ename like '%a% ';

Select Ename from emp where InStr (ename, ' A ', 1) >0;

--25, showing the service life of all employees by year, month and day

Select Months_between (sysdate,hiredate)/12 as "year", Months_between (sysdate,hiredate) as "month", Sysdate-hiredate as "Day" from Emp