Company Assessment Questions-(Oracle article I)

Today bored to churn just graduated from the computer, found in 11 in the unit internship some exercises and examination questions and answers, now record here, convenient for later inspection.

Assessment One:

--1, List all employees who pay more than "SMITH"      SELECT * from emp where sal > (select sal from emp where Ename= ' Smith ');--2, find Employees with a commission higher than 60% of the salary      SELECT * from emp where comm> (sal*0.6);      --3, find out the different jobs for employees who receive commissions      Select job from EMP where Comm is not null;--4, list all employees whose entry dates are earlier than their direct superiors Clerk      Select E.empno,e.ename,e.mgr, e.hiredate,m.hiredate from emp e,emp m where M.empno=e.mgr and E.hireda Te < m.hiredate;--5, lists the names of all "clerk" (clerks) and their department names      select T.ename,d.dname from emp t,dept D where t.job= ' Clerk ' and t.deptno=d.deptno;--6, lists minimum salaries for various job categories, shows records with minimum salary greater than 1500      select Job,min (SAL) from EMP Group By Job has min (sal) >1500;--7, lists the names of employees engaged in "sales" work, assuming you do not know the department number        Select job of the sales department, Ename from EMP where substr (job,1,5) = ' SALES ';     Select E.job, E.ename, e.deptno from emp e where E.deptno = (select D.deptno from dept D where D.dname= ' SALES ');--8, lists all employees with a salary above the company average  &nbsp   SELECT * from emp where sal> (select AVG (SAL) from EMP),--9, list all employees who work in the same job as "SCOTT"      SELECT * FR Om emp WHERE job = (select Job from emp where ename= ' SCOTT ');--10, lists minimum wages for various categories of work      Select Job,min (SAL) fro M EMP Group by JOB;--11, lists the minimum salary      select Deptno,min (SAL) from each department manager (SELECT * from EMP where Jo b= ' MANAGER ') group by Deptno;--12, List all employees ' employee names, department names, and salaries      Select Emp.ename,dept.dname,sal from emp,dept where Emp.deptno=dept.deptno;
Examination Two:1. If the "library name" is "GB" or "HB" or "WB", then the access path is: http://10.122.1210.203:2022/classification encoding per two bits of a split, middle with "/" separated/file/file nameExample: Http://10.122.1210.203:2022/B3/JH/I0/file/123.doc
2. If "Library name" is "QY" The Access path is: http://10.122.1210.203:2022/file/QY/file nameExample: Http://10.122.1210.203:2022/file/QY/123.doc
3. "Library name" other than the above two cases, the access path is: http://10.122.1210.203:2022/file/file nameExample: Http://10.122.1210.203:2022/file/123.doc
The stored procedures are as follows:Create or Replace function myfunc (filename varchar2,truclen number) return VARCHAR2 isI number (a);Fpath varchar (+);len Number (a);beginlen:=length (filename);i:=0;if Len>truclen thenWhile i<len loop<= "" div= "" >fpath:=fpath| | substr (Filename,i,truclen) | | ' /';I:=i+truclen;end Loop;Elsefpath:=fpath| | ' /';End If;return (Fpath);end MyFunc;
Create or Replace procedure MyProc isFpath varchar2 (+);cursor Db_cursor is a select * from QY;classid varchar2 ($);begin
For R in Db_cursor Loopif r.dbname= ' GB ' or r.dbname= ' HB ' or r.dbname= ' WB ' ThenClassid:=myfunc (r.classid,2);fpath:= ' http://10.122.1210.203:2022/' | | classid| | ' /file ' | | ' /' | | R.filename;elsif r.dbname= ' QY ' Thenfpath:= ' http://10.122.1210.203:2022/' | | ' qy/' | | R.filename;Elsefpath:= ' http://10.122.1210.203:2022/file/' | | R.filename;     End If;INSERT into qy_new values (R.dbname,r.classid,r.filename,fpath);end Loop;
end;

Company Assessment Questions-(Oracle article I)