Oracle database Query statements

Write the SQL statement for the following query, using the EMP and dept tables of the Scott user as the query data:

1. List all departments that have at least one employee.

SQL statements:

Select *  from SCOTT. DEPTwherein(Selectdistinct from SCOTT. DEPT);

Query Result:

2. Lists the names of all employees and their immediate superiors.

SQL statements:

Select E.ename Employee name, M.ename Superior name  from scott.emp e,scott.emp m where m.empno=e.mgr;

Query Result:

3. Lists the department names and employee information for those departments, and lists the departments that have no employees.

SQL statements:

Select d.dname,e.* from scott.dept d,scott.emp ewhere d.deptno= E.deptno (+);

Query Result:

4. Lists various jobs with a minimum salary of more than 1500.

SQL statements:

Select Job,min(sal) Minimum salary  from SCOTT. EMPGroup by jobhavemin(sal)>;

Query Result:

5. List all employees who pay more than the company's average salary.

SQL statements:

Select *  from SCOTT. EMPwhere  sal>(Selectavg from SCOTT. EMP);

Query Result:

6. Lists the names and salaries of all employees whose salaries are equal to the salaries of employees in department 30.

SQL statements:

Select Ename,sal,deptno  from SCOTT. EMPwhere in (selectfromwhere deptno=);

Query Result:

Oracle database Query statements