Oracle Query function Exercise 2

/*
The following code is the display width setting for the EMP Table/dept table/salgrade table
*/
Col empno for 9999;
Col ename for A10;
Col job for A10;
Col Mgr for 9999;
Col hiredate for A12;
Col Sal for 9999;
Col Comm for 9999;
Col deptno for 99;
Col dname for A14;
Col Loc for A14;
Col grade for 9999;
Col tname for A12;
Set PageSize 30;

//------------------------------------------------------------------------------------------------------

Query the current date
Select Sysdate from dual;
Select To_char (sysdate, ' Yyyy-mm-dd Day ') from dual;
Select To_char (sysdate, ' Yyyy-mm-dd Day ') from dual;
' Format ' in string, case insensitive

Date implicit conversion, read string, determine whether the date format requirements, re-implicit conversion, low efficiency
Select Ename,hiredate from emp where hiredate= ' 1 July-December-80 ';

Date explicit conversion, high efficiency, the promotion of the use of the project
Select Ename,hiredate from emp where hiredate=to_date (' 1980-12-17 ', ' yyyy-mm-dd ');

Use the To_char (date, ' format ') function to convert the date to a string that displays the following format: 2013-01-25 today is Friday 20:18:24
Select To_char (sysdate, ' yyyy-mm-dd ' Today is "Day Hh24:mi:ss") from dual;

Use the To_char (value, ' format ') function to display employee pay, plus $ or ￥ symbols and thousand characters
Select To_char (1234, ' l9,999 ') from dual;
Select Ename,to_char (Sal, ' $9,999 ') "USD", To_char (Sal, ' l9,999 ') "RMB" from EMP;

Use the To_number (string) function to turn the string "123" into a number
Select To_number (' 123 ') +321 from dual;
Select To_number (' A123 ') +321 from dual;//error

Use the To_date (string, ' format ') function to turn the string "1980-12-17" into a date
SELECT * from emp where hiredate = to_date (' 1980-12-17 ', ' yyyy-mm-dd ');

Use the NVL (A, B) function to count employee annual income
Select ENAME,JOB,NVL (comm,0) from EMP;

Use the NUL2 (a,b,c) function to count employee annual income
Select ENAME,JOB,NVL2 (comm,comm,0) from EMP;

Use the Nullif (A, B) function to compare whether 10 and 10.0 are the same
Select Nullif (10,10) from dual; same return null
Select Nullif (10,11) from dual; Returns the first value in a different way
Select Nullif (' Ten ') from dual; the two types must be the same when compared

Use case expression, position is analyst, salary +1000, position is manager, salary +800, position is other, salary +400
Select Ename "Name", Job "job", Sal "pre-pay",
Case Job
When ' ANALYST ' then sal+1000
When the ' MANAGER ' then sal+800
else sal+400
End "salary after Rise"
from EMP;

Use the Decode function, the position is the analyst's, the salary +1000, the position is the manager's, the salary +800, the position is other, the salary +400 "Oracle dedicated"
Select Ename "Name", Job "job", Sal "pre-pay",
Decode (Job, ' ANALYST ', sal+1000, ' MANAGER ', sal+800,sal+400) ' salary rise '
from EMP;

Starting today, what's the number of the next one weeks? "Chinese platform/Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday"
Select Next_day (Next_day (sysdate, ' Wednesday '), ' Wednesday ') from dual;
Functions can be nested and executed from inside Out

//------------------------------------------------------------------------------------------------------

Total employee salary, average wage, rounded, reserved 2 digits after decimal point
Select sum (SAL) "total wage", round (avg (SAL), 2) "average wage"
from EMP;

What is the maximum employee wage and the minimum wage, respectively?
Select Max (SAL) "Maximum wage", min (sal) "minimum wage"
from EMP;

Total number of employees
Select COUNT (*) "total number" from EMP;//11
Select COUNT (comm) "Total number" from EMP;//4
None of these functions will count null values

Entry to the earliest staff, the latest staff to enter the job
Select Max (hiredate) "entry to the latest employee", Min (hiredate) "entry to the earliest employee"
from EMP;
These functions are used for numeric types, and Max and Min are also available for date types

Number of employees counting commissions
Select COUNT (comm) from EMP;

Statistics company How many departments, departments can not repeat
SELECT DISTINCT deptno from EMP;

The average wage of the department is calculated by department, and the average wage takes an integer
Select Deptno "department number", Trunc (avg (SAL), 0) "Departmental average salary"
From EMP
Group BY Deptno;

Find departments with average salary greater than 2000 yuan
Select Deptno "department number", Trunc (avg (SAL), 0) "Departmental average salary"
From EMP
GROUP BY Deptno
Having trunc (avg (SAL), 0) > 2000;
Use having the prerequisite is that it must be grouped and only appear after GROUP by

In addition to department 30th, the Department of Inquiry department with average salary greater than 1500 yuan, the way a "have deptno!=30"
Select Deptno,trunc (avg (SAL), 0) "Departmental average salary"
From EMP
GROUP BY Deptno
Having trunc (avg (SAL), 0) >1500 and deptno<>30;
Analysis: Group Filters--Group filters--"low efficiency"

In addition to department 30th, the department with the average salary is more than 1500 yuan, the way two "where deptno!=30"
Select Deptno,trunc (avg (SAL), 0) "Departmental average salary"
From EMP
where deptno<>30
GROUP BY Deptno
Having trunc (avg (SAL), 0) >1500;
Analysis: Group filters, filter, line filters, "high efficiency"
Where row filter "first"
Having group filter

Show the maximum of departmental average wages
Select Max (SAL) "Department maximum average wage value"
From EMP
Group BY Deptno;

//------------------------------------------------------------------------------------------------------

Cartesian set of employee and departmental tables (Cartesian set table = sum of columns, number of rows, data in Cartesian set table is meaningless)
SELECT * FROM
emp,dept;

(1) Use equivalent connection, display employee's number, name, department name, use table alias to simplify "= number"
Select E.empno "Number", E.ename "name", d.dname "department name"
From EMP e,dept D
where E.deptno=d.deptno;

(2) using a non-equivalent connection (that is, you cannot use the = number), display the employee's number, name, salary, wage scale
Select E.empno "Number", E.ename "name", e.sal "monthly salary", S.grade "level"
From EMP E,salgrade s
where e.sal between S.losal and S.hisal;

(3) using the external connection, according to the department 10,20,30,40 number, statistics of the number of departments, display department number, department name, number of people
Select E.deptno "department number", COUNT (*) "Number of employees"
From EMP e,dept D
where E.deptno=d.deptno
Group BY E.deptno;
The above code only queries the records that satisfy the condition in the EMP table, but does not meet the criteria ' records but does not query them.
Objective: To query the "record of satisfying condition" and "record of not satisfying the condition".
Solution: Use an outer join
Types of outer joins:
A) LEFT outer connection: (+) symbol to the right of the = number, called Left outer connection
B) Right outer connection: (+) symbol to the left of the = number, called Right outer connection
(+) can only appear in the Where

Select D.deptno "department number", d.dname "department name", Count (e.empno) "Number of employees"
From EMP e,dept D
where E.deptno (+) =d.deptno
Group BY D.deptno,d.dname;
Right outer connection

Select D.deptno "department number", d.dname "department name", Count (e.empno) "Number of employees"
From EMP e,dept D
where D.deptno=e.deptno (+)
Group BY D.deptno,d.dname;
Left outer connection

(4) Using self-connect, display "Smith's old fine is Ford" This format, representing the hierarchical Association of data, with self-connected
Select Emp.ename | | ' The Boss is ' | | Boss.ename
From EMP EMP, EMP boss
where emp.mgr = Boss.empno;

Summarize:
Equivalence: The most widely used, the basis for connecting tables.
* Non-equivalent: Just do not use the = sign.
Outside connection: The main solution satisfies the condition and does not satisfy the condition the record, in the few side plus (+) can
* * Self-connection: mainly to solve the need of hierarchical relationship, a table as multiple sheets,
Connect multiple tables together with an equivalent connection.
Note: This is the Oracle-specific syntax

Non-group functions that appear in select must appear in GROUP by
The group functions here are: count (), Max (), Min (), AVG (), SUM ()

//------------------------------------------------------------------------------------------------------

The subquery resolves the query "condition unknown".

Find employee information on salary higher than ward
Step one: Check Ward's salary
Select Sal from emp where ename= ' WARD ';
Step Two: Query employee information for wages greater than 1250
SELECT * FROM EMP where sal>1250;
Sub-query:
SELECT *
From EMP
Where Sal> (
Select Sal
From EMP
where Ename= ' WARD '
);
Both the primary and the subquery are competing against the same table

Query employee information for department named ' SALES ' (Method one: sub-query)
(Inside/Sub) Step one: Query the department number of "SALES"
Select Deptno from dept where Dname= ' SALES ';
(Outside/main query) Step two: Query staff information in department 30th
SELECT * from EMP where deptno=30;

Sub-query:
SELECT * from emp where deptno= (
Select Deptno from dept where Dname= ' SALES '
);
The main and sub-queries are all competing against different sheets
The connection point type and number of the primary and sub-query are the same (if you use the = sign)

Query employee information for department named ' SALES ' (Way two: multi-table query)
Select emp.*
From Emp,dept
WHERE (Emp.deptno = Dept.deptno) and (dept.dname = ' SALES ');

After the oracle11g internal optimization, from the table count, the preferred choice
"Multi-Table query"--"subquery"
When in advance:
A) Two ways to accomplish tasks
B) Oracle optimizes the Cartesian set

Query employee information with lowest wage (single-line subquery, = number)
Step one: The employee who inquires the minimum wage
Select min (sal) from EMP;
Step Two: Query salary is 880 of employee information
SELECT * from emp where sal = 880;
Sub-query:
SELECT * from emp where Sal = (
Select min (sal) from EMP
);

Query employee with department named ' ACCOUNTING ' or ' SALES ' (multiline subquery, in keyword)
Step One: Query department name is "ACCOUNTING" or "SALES" department number
Select Deptno from dept where Dname in (' ACCOUNTING ', ' SALES ');//10,30
Step Two: Find employee information for department number 10th or number 30th
SELECT * from EMP where deptno in (10,30);
Sub-query:
SELECT * from EMP where deptno in (
Select Deptno from dept where Dname in (' ACCOUNTING ', ' SALES ')
);

Query Payroll 20th Employee information for "any" employee "low <" (multi-row subquery, any keyword)
Step one: Inquire about the salary of department number 20th
Select Sal from EMP where deptno=20;
Step two: Inquire about salary below "880/2975/3000/1100/3000" employee information of any one salary
SELECT * from emp where Sal < any (array);

Sub-query:
SELECT * from emp where Sal < any (
Select Sal from EMP where deptno=20
);
<any equivalent to less than the maximum wage

Query Payroll 30th Employee information for "All" Employees "low <" (Multi-line subquery, all keyword)
Step one: Inquire about the salary of department number 30th
Select Sal from EMP where deptno=30;
Step two: Inquire about salary below "1600/1250/1250/2850/1500/950" employee information of any one salary
SELECT * from emp where Sal < All (array);

Sub-query:
SELECT * from emp where Sal < All (
Select Sal from EMP where deptno=30
);
<all equivalent to less than the minimum wage

Thinking:
1) >any?
2) >all?

//------------------------------------------------------------------------------------------------------

Query Employee information for department 20th and Department 30th using the set operation
Step One: Check the staff of department number 20th
SELECT * from EMP where deptno=20;

Step Two: Query staff of Department 30th
SELECT * from EMP where deptno=30;

The set operation:
SELECT * FROM EMP where deptno=20
Union
SELECT * from EMP where deptno=30;

Turn on the time switch
Set time on;//displays the current times in the prompt
Set time off;//off

Switch to turn off time
Set timing on;//At the end, displays the time the SQL statement was executed
Set timing off;//time to close SQL execution

Use the intersection operation to query employee information between 1000-2000 and 1500-2500 (mode one)

SELECT * from EMP where Sal between and 2000
Intersect
SELECT * from EMP where Sal between and 2500;

Use where row filtering to query employee information between 1000-2000 and 1500-2500 (mode two)

SELECT *
From EMP
where (Sal between) and (Sal between and 2500);

Use difference set operation to query employee information (mode one) with payroll at 1000-2000, but not between 1500-2500

SELECT * from EMP where Sal between and 2000
Minus
SELECT * from EMP where Sal between and 2500;
When doing a set operation,
The number of columns in the two collection must be the same
Two sets if the column name is different, the result depends on the first collection

Use the Where row filter to query employee information (way two) with payroll at 1000-2000, but not between 1500-2500
SELECT *
From EMP
where (Sal between) and (Sal not between and 2500);

When multi-table query, subquery, collection query can complete the task, according to the following scenario selection

Query for queries with subqueries, such as multi-table query

Premise:......
The less you look, the faster you get.
The fewer fields you look for, the faster.
The fewer records, the sooner

//------------------------------------------------------------------------------------------------------

ROWID: The column is not visible through DESC, but exists, and is related to the file system reference

"Focus" RowNum: The column is not visible through DESC, but it exists, and the value has the following characteristics
A) Forever Continuous
B) RowNum can still participate in the operation
C) RowNum can participate in < or <= operations
D) rownum value cannot complete =,>,>= operation
E) rownum value <> The same result as <

Thinking:
1) Get the top five records
SELECT * FROM EMP where rownum<=5
2) Get sixth record to tenth record

Start a sqldeveloper graphical tool with ORACLE11, using JDK5/6

//------------------------------------------------------------------------------------------------------

Hands-on exercises:

Oraclesql Today, delete the answer, and practice it once again.

//------------------------------------------------------------------------------------------------------

Single quotes:
A) To_char (A, ' format ');

Double quotes:
B) To_char (A, ' format ') when there is a fixed text in the format, you can use the double quotation mark "" Bound