Oracle Class notes-day 13th

Self-connect:

Empid ename Mgrid

abc

101 def 100

102 XYZ 100

Emp:mgr:

Empid ename Mgrid Empid mgrname

ABC (ABC)

101 def 100

102 XYZ 100

101 DEF-ABC

102 XYZ (ABC)

Select Emp.ename, Mgr.mgrname

From EMP, Mgr

where Emp.mgrid=mgr.empid

Emp:mgr:

Empid ename mgrid empid ename Mgrid

ABC (ABC)

101 DEF 101 def 100

102 XYZ 102 XYZ 100

Select E.last_name, M.last_name

From Employees e, Employees m

where e.manager_id=m.employee_id;

Number of employees with managers: 106

Sql> Select COUNT (*) from employees where manager_id are not null;

Number of employees without manager: 1

Sql> Select COUNT (*) from employees where manager_id is null;

Practice:

Displays all employee names and manager names, without the manager's display "none".

Select E.last_name, NVL (M.last_name, ' n/A ')

From Employees e, Employees m

where e.manager_id=m.employee_id (+);

No equivalent connection:

Conn Scott/tiger

Select E.ename, Sg.grade

From EMP E, Salgrade SG

where e.sal between Sg.losal and Sg.hisal;

Practice:

Identify the employee whose salary is greater than the average wage in the department.

CREATE TABLE avg_sal_dept as select department_id, avg (Salary) Avg_sal from employees where department_id are NOT null GRO Up by department_id;

Select E.last_name, E.salary, asd.avg_sal

From Employees E, avg_sal_dept ASD

where e.department_id=asd.department_id

and e.salary>asd.avg_sal;

Select E.last_name, E.salary, asd.avg_sal

From Employees e, (select department_id, avg (Salary) Avg_sal from employees where department_id are NOT null GROUP by Depar TMENT_ID) ASD assigns an alias to the department's average wage!

where e.department_id=asd.department_id

and e.salary>asd.avg_sal;

Sub-query

First written subquery second written main query

The idea of single-line subquery:

Sql> Select salary from Employees where last_name= ' Feeney ';

Sql> Select last_name from Employees where salary>3000;

Sql> Select last_name from Employees where salary> (select salary from Employees where last_name= ' Feeney ');

The idea of multi-row subqueries:

Sql> SELECT distinct department_id from employees where department_id are not null;

Sql> Select Department_name from departments where department_id in (10, 20,30);

Sql> Select Department_name from departments where department_id in (select department_id from Employees where departme NT_ID is not null);

Overwrite with a multi-table connection:

SELECT DISTINCT D.department_name

From Employees e, departments D

where e.department_id=d.department_id

For dept in 1..27

For EMP in 1..107

See if DeptID appears in the EMP

Practice:

The employee's name is more than the average salary of the company.

Sql> Select last_name from Employees where salary> (select AVG (Salary) from employees);

Name of employee who entered the same year as Feeney

Select Last_Name, Hire_date

From Employees

Where extract (year from hire_date) =

(select Extract (year from hire_date) from employees where last_name= ' Feeney ')

and last_name! = ' Feeney ';

Select Last_Name, Hire_date

From Employees

where hire_date between

(Select To_date (To_char (hire_date, ' yyyy ') | | ' 0101 ', ' YYYYMMDD ') from employees where last_name= ' Feeney ')

and

(Select To_date (To_char (hire_date, ' yyyy ') | | ' 1231 ', ' YYYYMMDD ') from employees where last_name= ' Feeney ')

Name of all employees working in Seattle

Department information in Seattle Department 1 Seattle location ID is how many 2 location ID

employees in these departments 1 of those departments 2 employees in these departments

Select Last_Name

From Employees

where department_id in

(Select department_id from departments

where location_id=

(select location_id from locations where city= ' Seattle '));

Find employee names that meet the following criteria: and Abel in the same department, wages are higher than Olson.

Select Last_Name from Employees

where department_id=

(select department_id from Employees where last_name= ' Abel ')

> Salary

(select salary from Employees where last_name= ' Olson ');

Paired subqueries:

Names of employees in the same department and in the same position as Feeney:

Select Last_Name, department_id, job_id

From Employees

where department_id=

(select department_id from Employees where last_name= ' Feeney ')

and job_id=

(select job_id from Employees where last_name= ' Feeney ')

and last_name! = ' Feeney ';

Select Last_Name, department_id, job_id

From Employees

Where (department_id, job_id) =

(select department_id, job_id from employees where last_name= ' Feeney ')

and last_name! = ' Feeney ';

In and not are affected by null values:

Department name with employee

Select Department_name from departments where department_id in (select department_id from Employees);

department name without employee Select Department_name from departments where department_id not in (select department_id from Employees Where department_id is not null);

All management, name of the person:

Sql> Select last_name from Employees where employee_id in (select manager_id from Employees);

Name of all ordinary employees:

Sql> Select last_name from Employees where employee_id isn't in (select manager_id from Employees where manager_id NULL);

To associate a subquery:

The employee's name is greater than the average wage in the department.

For I in 1..107 all employees

{

Select AVG (Salary) from employees where department_id=i.department_id

Average salary of the department if I.salary > I

Keep this record

}

Select Last_Name

From Employees outer

Where Salary >

(select AVG (Salary) from employees

where department_id = outer.department_id);

Select E.last_name, E.salary, asd.avg_sal

From Employees e, (select department_id, avg (Salary) Avg_sal from employees where department_id are NOT null GROUP by Depar TMENT_ID) ASD

where e.department_id=asd.department_id

and e.salary>asd.avg_sal;

exists/not exists query: is there

For I in 1..27 all departments

{

For j in 1..107 all employees

{

If i.department_id = j.department_id

Keep this record

Break

}

}

Select Department_name

From Departments outer

where exists

(select 1 from Employees where department_id=outer.department_id);

Select Department_name

From Departments outer

Where NOT EXISTS

(select 1 from Employees where department_id=outer.department_id);

Practice:

Name of all employees working in Seattle (two ways to use subqueries and multi-table connections)

Select Last_Name

From Employees

where department_id in

(Select department_id from departments

where location_id=

(select location_id from locations where city= ' Seattle '));

Select E.last_name

From Employees e, Departments D, Locations L

where e.department_id=d.department_id

and d.location_id=l.location_id

and l.city= ' Seattle ';

Maximum value query:

Sql> Select last_name from Employees where salary= (select Max (Salary) from employees);

Top-n query:

Sql> Select Last_Name, salary from employees where rownum<=3 order by salary desc;

Sql> SELECT * FROM (select last_name, salary from Employees order by salary DESC) where rownum<=3;

Paging query:

Sql> SELECT * FROM

(SELECT * FROM

(SELECT * FROM

(select Last_Name, salary from Employees order by salary DESC)

where rownum<=6)

Order by salary)

where rownum<=3

Order BY salary Desc;

Sql> Select Last_Name, Salary

From (select RowNum row_num, v1.*

From

(select Last_Name, salary from Employees order by salary desc) v1

) v2

where row_num between 4 and 6;

Select Last_Name, Salary

From (select RowNum row_num, v1.*

From

(select Last_Name, salary from Employees order by salary desc) v1

where rownum<=6

) v2

where Row_num >= 4;

Oracle Class notes-day 13th