Oracle Learning Summary 2

1: Common functions

To_date () function to convert a string to a date format
Select To_date (' 2015-09-12 ', ' YYYY-MM-DD ') from dual; --The subsequent date format to match the previous conversion

To_number () function to convert a string to a number format
Select Ename,sal from emp where Sal>to_number (' $5000.00 ', ' $9999.99 ');

Clear Screen command: clear screens;

2: Common group function Max () min () Avg () sum () count ()

Select Max (SAL) from EMP; --What's the maximum salary?

Select min (sal) from EMP; -What is the minimum wage?

Select AVG (SAL) from EMP; --what is the average salary?

Select sum (SAL) from EMP; -What is the sum of the salary?

Select COUNT (ename) from EMP; --How many employees are in total

3:group by seeking department average salary
Select Deptno,avg (SAL) from the EMP group by DEPTNO;

4: Perform SELECT General Order
Select Deptno,avg (SAL) from EMP (table name) where sal>5000 (where qualified) group by DEPTNO (grouped by Department) having AVG (SAL) >5000
ORDER by AVG (SAL) desc;

5: Issues with Duplicate records
Select empno from EMP GROUP by Empno have Count (empno) >1; --Query out duplicate empno
SELECT * from EMP where empno in (select Empno from EMP GROUP by Empno have Count (empno) >1); --Query for duplicate records
Delete from EMP where empno in (select Empno from EMP GROUP by Empno have Count (empno) >1) and rowID not in (select Mi N
(ROWID) from the EMP group by Empno have Count (empno) >1); --delete duplicate records and keep the first one

6: Sub-query
Select Ename,sal from emp where sal= (select Max (SAL) from EMP); --Check the name of the highest-paid employee in the company
Select Ename,sal from emp where Sal in (select Max (SAL) from the EMP Group by DEPTNO);--Query the name of the employee with the highest pay per department--the wrong
(all employees will be identified if they match one of them)
Select Ename,sal from emp e join (select Max (SAL) Max_sal,deptno from EMP Group by Deptno) T on (E.sal=t.max_sal and
E.DEPTNO=T.DEPTNO);
or select Ename,sal from emp E, (select Max (SAL) max_sal, deptno from EMP Group by Deptno) T where E.sal=t.max_sal and
E.deptno=t.deptno;
Select Empno,count (1) from EMP Group by EMPNO have count (1) >1; --Querying employees for duplicate employee numbers

92 Standard:
Select Ename,sal,grade from emp e,grade g where e.sal between G.lowsal and G.higsal; --Ask all employees to pay grade
Select Ename,sal,grade from emp e join (select Max (SAL) Max_sal,deptno from EMP Group by Deptno) T on (E.sal=t.max_sal and
E.DEPTNO=T.DEPTNO) join grade G on (T.max_sal Bwtween g.lowsal and G.higsal)-------find the highest salary in the department name, grade

--The average salary level of each department
Select Deptno, Avg_sal,grade from (select AVG (SAL) Avg_sal,deptno from EMP Group by DEPTNO) T joins grade G on (t.avg_sal
Between G.lowsal and G.higsal);

--The average salary level for each department
Select Ename,deptno,sal,grade from emp e joins grade G on (E.sal between G.lowsal and G.higsal); --Salary level for each employee
Select Deptno,avg (Grade) from (select Ename,deptno,sal,grade by emp e join grade G on (E.sal between G.lowsal and
g.higsal)) T Group by Deptno;

99 SQL Standard:
Select Ename,sal,grade from emp e joins grade G on (E.sal between G.lowsal and G.higsal);

Self-connect: operate on the same table
Select E1.ename,e2.ename from EMP E1 join EMP E2 on (e1.mgr=e2.empno) or select E1.ename,e2.ename from emp e1,emp E2 where
E1.mgr=e2.empno;
Left Outer connection:
Select E.ename,d.loc from emp e leave join Dept D on (E.DEPTNO=D.DEPTNO);--the left outer connection will also query the field that is not associated with the table on the left.
Come out
Right outer connection:
Select E.ename,d.loc from the EMP e right joins Dept D on (E.DEPTNO=D.DEPTNO);---the left side connection will also query the fields that are not related to the table on the right.
Come out
Full Outer connection:
Select E.ename,d.loc from emp e full join Dept D on (E.DEPTNO=D.DEPTNO); --The full connection will query the fields that are not related to the left and right side tables.
To

7:--query all the managers
Select ename from emp where empno in (select distinct MGR from EMP);

8:--do not use group functions to query the highest value of salary (sort by salary first, then query the first article)
Select Sal from (select Sal from emp order by sal Desc) where rownum=1;
Or
Select E1.sal from emp where Sal is not in (select E1.sal from EMP E1 join, EMP E2 on (e1.sal<e2.sal)); --Self-junction table, then table E1
Only the maximum value is not associated with the

9:--The name of the department with the highest average pay
1:--for the average salary of each department
Select Deptno,avg (SAL) from the EMP group by DEPTNO;
2:--what is the highest average salary for
Select Max (avg_sal) from (select Deptno,avg (SAL) avg_sal from EMP Group by DEPTNO);
3:--Find the highest average salary number of the department
Select Deptno from (select Deptno,avg (SAL) avg_sal from EMP Group by deptno) where Avg_sal = (select M Ax (avg_sal) from
(select Deptno,avg (SAL) avg_sal from EMP Group by DEPTNO);
4:--the name of the highest department of the average salary
Select Dname from dept where deptno = (select Deptno from (select Deptno,avg (SAL) avg_sal from EMP Group by Deptno) where
Avg_sal = (select Max (avg_sal) from (select Deptno,avg (SAL) avg_sal from EMP Group by DEPTNO)) ;

10:--Find the department name with the lowest average salary level
1:--for each department average salary
Select AVG (SAL), Deptno from EMP Group by DEPTNO;
2:--average salary level for each department
Select Deptno,avg_sal,grade from (select AVG (SAL) Avg_sal,deptno from EMP Group by DEPTNO) T join grade G on (T.avg_sal
between G.lowsal and G.higsal);
3:--average Salary level minimum
Select min (grade) from (select Grade from (select AVG (SAL) Avg_sal,deptno from EMP Group by DEPTNO) T Joi N Grade G on
(T.avg_sal between G.lowsal and G.higsal));
4:--The department number with the lowest average salary level
Select Deptno from (select Deptno,avg_sal,grade from (select AVG (SAL) Avg_sal,deptno from EMP g Roup by Deptno) T joins grade
G on (T.avg_sal between G.lowsal and G.higsal)) Where grade = (select min (grade) from (Sele CT grade from (select AVG (SAL)
Avg_sal,deptno from EMP Group by DEPTNO) T joins grade G on (T.avg_sal between G.lowsal a nd g.higsal));

5:--the department number with the lowest average salary level
Select Dname from dept where Deptno in (select Deptno from (select AVG (SAL)
Avg_sal,deptno from EMP Group by DEPTNO) T joins grade G on (T.avg_sal between G.lowsal and G.higsal)) Where grade = (select
Min (grade) from (select Grade from (select AVG (SAL) Avg_sal,deptno from EMP Group by DEPTNO) T joins grade G on (t.avg_sal
Between G.lowsal and G.higsal)));

11:oracle Database User Switching
Conn Sys/root (username/password) as SYSDBA;

Authorization statement:
Grant create table,create view to System (user name);
Grant Select,update,insert on EMP to HR; -Multiple operations can be authorized at one time, but only for a single table

12:--a manager who is higher than the average employee's maximum wage
1: Maximum wage for ordinary employees
Select Max (SAL) from the EMP where empno not in (select distinct MGR from EMP where Mgr are NOT NULL)
2: The manager who asks for the high
Select ename from emp where empno in (select distinct MGR from EMP where Mgr are NOT null) and Sal > (select Max (Sal) fr Om
EMP where empno not in (select distinct MGR from EMP where Mgr are NOT null));

13:--Top 2 employees with the highest salary
Select Ename,sal from (select Ename,sal from emp order BY sal Desc) where RowNum <3;

14:rownum Sub-query, implementation of paging, first is the sort, then the query rownum, the third limit conditions
Select Ename,sal from
(Select Ename,sal,rownum r from
(select Ename,sal from emp order by sal Desc)) where r>=2 and r<=3

Contrast:
Mysql:select ename,sal from (select Ename,sal to emp order BY sal DESC) t where ID limit 2,2;--starting from 2 fetch, FETCH 2
DB2: Using Fetch

13:DML statement (data manipulation statement)
Update emp Set ename= ' Tom ' where empno=1001;
Delete from emp where empno=1002;
INSERT into EMP (EMPNO,DEPTNO) values (1005,20);

14: Transaction control (DCL statement)
A:commit commit rollback rollback transcation transaction End
B: Once the DDL statement is executed, the previous transaction is also committed
C: Normal exit connection, exit, will also automatically commit the transaction

Oracle Learning Summary 2