Oracle's SQL statement on-machine exercises and Answers (i)

--1, selecting employees in Department 30

SELECT * from Empwhere deptno=30;

　　--2, List all clerks ' names, numbers and departments

　　Selectename,empno,dname from EMP Einner joins dept d on e.deptno = D.deptno wherejob=upper (' clerk ');

　　--3, finding employees with higher commissions than salaries

　　select* from EMP where comm>sal;

　　--4, finding employees with commissions above 60% of salary

　　select* from EMP where comm>sal*0.6

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

　　select* from EMP where (deptno=10 andjob=upper (' manager ')) or (deptno=20 andjob=upper (' clerk '));

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

　　select* from EMP where (deptno=10 andjob=upper (' manager ')) or (deptno=20 andjob=upper (' clerk ')) or (Job<>upper (' C9>manager ') andjob<>upper ('clerk ') and sal>=2000)

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

　　SelectDistinct Job from EMP wherecomm>0;

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

　　select* from EMP wherenvl (comm,0) <100;

　　--9, find out all the employees who were hired on the last day of each month

　　select* from EMP where Hiredate=last_day (HireDate);

　　--10, identifying employees employed before 25

　　select* from EMP wheremonths_between (sysdate,hiredate)/12>25;

　　select* from EMP wherehiredate<add_months (SYSDATE,-12*25);

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

　　Selectename from EMP whereename=initcap (ename);

　　--12, displaying an employee name that is exactly 6 characters

　　Selectename from EMP wherelength (ename) =6

　　--13, show employee names without ' R '

　　Selectename from EMP where ename notlike '%r% ';

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

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

　　Selectsubstr (ename,1,3) from EMP

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

　　Selectreplace (ename, 'a ', 'a ') from emp

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

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

　　--17, show employee details, sort by name

　　Select* from emp ORDER by ename

　　--18, showing the employee's name, according to their service life, the oldest employees in the front

　　Selectename from emp ORDER by HireDate

　　--19, displays the names, jobs, and salaries of all employees, sorted in descending order of work, while working in payroll ascending

　　Selectename,job,sal from emp order byjob DESC, sal ASC

　　--20, showing the names of all employees and the year and month of incorporation, sorted by the month on which the employee was employed and the first year of the project

　　Selectename,to_char (hiredate, ' yyyy '), To_char (hiredate, ' mm ') from EMP order byhiredate ASC

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

　　Selectename,sal/30 from EMP;

　　--22, identifyall employees employed in February (in any year)

　　select* from EMP Whereto_char (hiredate, ' mm ') = ' 02 ';

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

　　Selectename,sysdate-hiredate from EMP

　　--24, displays the names of all employees that contain "A" in the Name field

　　Selectename from EMP where ename like '%a% ';

　　Selectename from EMP where InStr (ename, 'A ', 1) >0;

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

　　Selectmonths_between (sysdate,hiredate)/12as "year", Months_between (sysdate,hiredate) as "month", Sysdate-hiredateas "Day" from EMP

Oracle's SQL statement on-machine exercises and Answers (i)