Oracle Learning Series 2

SQL syntax Exercise: 1, select all employees in department 30 select * from EMP where deptno=30;2, lists the clerk's name, number and department number select ename, empno, deptno from Empwhere job=u Pper (' clerk '); 3, find the employee with a commission above the salary select * from Empwhere comm>sal; 4, find the employee of 60% of the Commission above the salary select * from Empwhere comm > Sal * 0.6;5, find out all of the department 10 Details of all clerks in department 20 select * from Empwhere (deptno=10 and job= ' manager ') or (deptno=20 and job= ' clerk ') 6, find all managers in department 10, department 20 All clerks, who are neither managers nor clerks but whose salaries are greater than or equal to 2000 of all employees ' details select * from EMP where (deptno=20 and job= ' manager ') or (deptno=20 and job= ' Clerk ') or (Job not in (' manager ', ' clerk ') and Sal >=2000); 7, find out the different jobs for employees who receive commissions select distinct job from Empwhere Comm is N OT Null;8, find an employee who does not charge a commission or is charged less than 100 of the staff select * FROM Empwhere (comm is NULL) or (comm<100); 9 to find all employees employed on the third day of the month select * from EM PWhere Last_day (HireDate)-2 =hiredate; 10, find employees hired before 12 select * FROM Empwhere (Months_between (sysdate, HireDate)/12) &G T 12; 11, display all employees ' names in the first letter University Select Initcap (ename) from emp;12, displays the name of the employee who is exactly 5 characters select ename from Empwhere length (ename) =5; 13, display the name of the employee without ' R ' selECT Enamefrom empwhere ename not like '%r% '; 14, display the first three characters of all employee names Select substr (ename,0,3) from EMP, 15, show all employee names, replace all ' a ' with ' a ' Select Replace (ename, ' a ', ' a ') from EMP; 16, showing the name and date of employment of employees over 10 years of service Select Ename, Hiredatefrom empwhere (Months_between ( Sysdate, HireDate)/12) > 10, 17, show employee details, sort by name select * from Emporder by ename;18, displays the employee's name and date of employment, according to their service life, ranked the oldest employee at the front SE Lect ename, Hiredatefrom Emporder by hiredate;19, showing the names, jobs and salaries of all employees, sorted by job in descending order, sort by salary if same job select ename, job, Sal from Empwher E job desc, Sal; 20, displays the names of all employees, the year and month of incorporation, sorted by the month of employment, and if the month is the same, the employee of the earliest year is ranked first select Ename,to_char (hiredate, ' yyyy ') _char (hiredate, ' yy ') monthsfrom emporder by months, year; 21, showing the daily salary of all employees in one months for 30 days, ignoring remainder select ename, round (SAL/30) From Emp22, find all employees employed in February in any year select * from Empwhere to_char (hiredate, ' mm ') = 2; 23, for each employee, show the number of days it joins the company select Ename, round (s ysdate-hiredate) Daysfrom emp; 24, displays the names of all employees of the name field that contain ' A ', select * from Empwhere ename like '%a% '; 25, displays the service life of all employees by month and day. Select Ename, Trunc (Months_between (Sysdate, HIredate)/12) years, trunc (mod (Months_between (sysdate,hiredate),)) months, trunc (mod ((sysdate-hiredate), 30) ) Daysfrom EMP;

Oracle Learning Series 2