Grouping Functions
, multi-line functions
Summary of non-null data
Only Lieri the null value first.
Grouping is not required
Sql> Select COUNT (*), sum (Salary), AVG (Salary), min (Salary), Max (salary) from employees;
sql> CREATE TABLE t1 (x int);
sql> INSERT INTO T1 values (null);
sql> INSERT INTO T1 values (1);
Sql> commit;
Sql> Select COUNT (*) from T1;
Sql> select COUNT (x) from T1;
Sql> select Max (x) from T1;
sql> Select min (x) from T1;
sql> select SUM (x) from T1;
Sql> Select AVG (x) from T1;
Sql> Select AVG (Salary), AVG (NVL (commission_pct, 0)) from employees;
Sql> Select count (distinct department_id) from employees; Remove duplicate values
GROUP BY group:
Sql> Select department_id, avg (salary) from Employees group by DEPARTMENT_ID;
Multi-column grouping:
Sql> Select department_id, job_id, Max (salary) from Employees group by DEPARTMENT_ID, job_id;
Sql> Select department_id, job_id, Max (Salary), last_name from Employees GROUP by DEPARTMENT_ID, job_id; Error syntax
Practice:
Number of different positions in the company
Select (distinct job_id) from employees;
Calculate the number of people in each department
Select Department_id,count (employees_id)
From Employees
Where department_id is not null
Group by department_id
GROUP by year for the sum of employees ' wages
Select Extract(year fromhire_date), avg (Salary)
From Employees
Group by extract (year from hire_date)
Having statement:
Sql> Select department_id, avg (salary) from employees where AVG (salary) >=5000 GROUP by department_id; Error statement
Sql> Select department_id, avg (salary) from Employees GROUP by DEPARTMENT_ID have avg (salary) >=5000;
Practice:
According to the department to find all the ordinary employees have the average wage, the department's average salary of less than 5000 does not show, the final results in descending order of average wages.
Select department_id, Avg (Salary) Avg_sal
From Employees
where job_id not as '%\_mgr ' escape ' \ ' and department_id is not NULL
GROUP BY department_id
Having avg (Salary) >=5000
ORDER BY avg_sal Desc;
Multi-table connection
No
Emp:dept:
Empno ename deptno deptno dname
ABC Ten Sales
101 Def Ten Market
102 XYZ It
103 OPQ NULL
For EMP in 100.. 103
For dept in 10.. 30
Emp.deptno=dept.deptno
ABC Ten Sales
101 def Ten Sales
102 XYZ
Get the following information to prepare for the job:
Employees
Total Employees: 107
Sql> Select COUNT (*) from employees;
Number of employees with Department: 106
Sql> Select COUNT (*) from employees where department_id are not null;
Sql> Select COUNT (department_id) from employees;
Number of employees without department: 1
Sql> Select COUNT (*) from employees where department_id is null;
Departments:
Number of departments: 27
Sql> Select COUNT (*) from departments;
Number of departments with employees: 11
Sql> Select COUNT (distinct department_id) from employees;
Number of departments with no employees: 16
Sql> Select COUNT (*) from departments where department_id not in (select department_id from Employees where Department_ ID is not NULL);
For dept in 1..27
For EMP in 1..107
Dept.deptid does not appear in the EMP table
Select COUNT (*)
From Employees e, departments D
where e.department_id (+) =d.department_id
and e.employee_id is null;
Select COUNT (*)
From Departments D
Where NOT EXISTS
(select 1 from Employees where department_id=d.department_id);
Select (select COUNT (*) from Departments) – (select COUNT (distinct department_id) from employees) from dual;
Internal connections: 106 (106, 11)
Select E.last_name, D.department_name
From Employees e, departments D
where e.department_id=d.department_id;
Select E.last_name, D.department_name
From Employees e joins departments d on E.DEPARTMENT_ID=D.DEPARTMENT_ID;
Left Outer connection: 107 (106+1)
Select E.last_name, D.department_name
From Employees e, departments D
where e.department_id=d.department_id (+);
Select E.last_name, D.department_name
From Departments D, employees E
where e.department_id=d.department_id (+);
Select E.last_name, D.department_name
From employees e LEFT OUTER JOIN departments D
On e.department_id=d.department_id;
Right outer connection: 122 (106+16)
Select E.last_name, D.department_name
From Employees e, departments D
where e.department_id (+) =d.department_id;
Select E.last_name, D.department_name
From Employees e right OUTER join departments D
On e.department_id=d.department_id;
Full Outer connection: 123 (106+1+16)
Select E.last_name, D.department_name
From Employees e full OUTER JOIN departments D
On e.department_id=d.department_id;
Extensions for multi-table joins:
N-Sheet Connection:
Select E.last_name, D.department_name, l.city
From Employees e, Departments D, Locations L
where e.department_id=d.department_id
and d.location_id=l.location_id;
Select E.last_name, D.department_name, l.city
From Employees e join Departments D on e.department_id=d.department_id
Join locations L on d.locationy_id=l.location_id;
Select E.last_name, D.department_name, l.city
From Employees e, Departments D, Locations L
where e.department_id=d.department_id (+)
and d.location_id=l.location_id (+);
Select E.last_name, D.department_name, l.city
From employees e left outer joins departments D on E.DEPARTMENT_ID=D.DEPARTMENT_ID
Left outer joins locations L on d.location_id=l.location_id;
Oracle Class notes-day 12th