"Oracle Query Statement 2"

single-line function

Sql> Select Upper (first_name), Lower (last_name), Length (last_name) from employees;

Sql> Select (sysdate-hire_date)/7 from employees;

Sql> Select Trunc ((sysdate-hire_date)/30, 0) from employees;

Sql> Select Trunc (Months_between (sysdate,hire_date), 0) from employees;// Resolve cumulative Error

Sql> select sysdate+3650 from dual;

Sql> Select Add_months (sysdate) from dual;//to resolve cumulative errors

Sql> Select Next_day (' 2015-09-01 ', ' Friday ') from dual; Next Friday

Sql> Select Next_day (' 2015-10-01 ', 6) from dual;

Sql> Select Last_day (sysdate) from dual;

Sql> Select round (to_date (' 2015-10-10 ', ' yyyy-mm-dd '), ' MONTH ') from dual;

Sql> Select round (to_date (' 2015-10-16 ', ' yyyy-mm-dd '), ' MONTH ') from dual;

Sql> Select round (to_date (' 2015-10-10 ', ' yyyy-mm-dd '), ' year ') from dual;

Sql> Select round (sysdate, "Day") from dual;

Practice:

Identify all employees employed in the last three days of each month

Extract (month from hire_date+4)! = Extract (month from hire_date)

Identify employees employed prior to 25

Months_between (Sysdate, hire_date)/300>=25

Display an employee name that is exactly 6 characters

Length (last_name) =6

Displays the first three characters of all employees ' names

SUBSTR (last_name, 1, 3)

Show all employees ' names, replace all ' a ' with a

Replace (last_name, ' a ', ' a ')

type conversions and other functions

Sql> Select To_char (Salary, ' $999,999.00 ') from employees;

Sql> Select Last_Name, To_char (hire_date, ' DD-MON-RR ') from employees;

Sql> Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

Sql> Select To_char (sysdate, ' yyyy-mm-dd hh:mi:ss AM ') from dual;

Sql> Select last_name from Employees where hire_date=to_date (' 2006-05-23 ', ' yyyy-mm-dd ');

Sql> Select To_number (' $123,456.78 ', ' $999,999.00 ') from dual;

Practice:

Enquiry 2006 into the clerk:

Select Last_Name

From Employees

where hire_date between To_date (' 2006-01-01 ', ' yyyy-mm-dd ')

and To_date (' 2006-12-31 ', ' yyyy-mm-dd ');

Select Last_Name

From Employees

where To_char (hire_date, ' yyyy ') = ' 2006 ';

Select Last_Name

From Employees

Where extract (year from hire_date) = 2006;

--Not recommended

Select Last_Name

From Employees

Where hire_date like ' 2006% ';

For employees who have been in employment for the past September years:

Select Last_Name

From Employees

where To_char (hire_date, ' mm ') = ' 09 ';

Select Last_Name

From Employees

Where extract (month from hire_date) = 9;

Other functions:

Nvl

NVL (Val1, Val2)

If VAL1 is not null

Then

return val1;

Else

return val2;

Sql> Select Last_Name, salary*12* (1+NVL (commission_pct, 0)) Total_salary from employees;

Practice:

Show all Employee department numbers, no departments show "Unassigned Department"

Case and Decode:

It_prog +1000

Sa_rep +1500

St_clerk +2000

Other people's wages are the same

Select salary+1000 from Employees where job_id= ' It_prog ';

Select Last_Name, job_id, salary,

Case job_id

When ' It_prog ' and then salary+1000

When ' Sa_rep ' and then salary+1500

When ' St_clerk ' and then salary+2000

else salary

End New_salary

From employees;

Select Last_Name, job_id, salary,

Decode (job_id,

' It_prog ', salary+1000,

' Sa_rep ', salary+1500,

' St_clerk ', salary+2000,

Salary) New_salary

From employees;

Practice:

According to the employee's salary, the employee rating shows:

A 20001-25000

B 15001-20000

C 10001-15000

D 5001-10000

E 0-5000

    Grouping Functions

only Count (*): do statistics on rows, keep statistics on null values, and do statistics by removing null values .

Avg: Averaging for non-null values

NVL: Assign a value of 0to the null value, in the statistics

when a group by group does statistics, empty values are not

only grouped columns appear in Select, grouping functions

Sql> Select COUNT (*), sum (Salary), AVG (Salary), minsalary), 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; assigning a value of 0 to a null value is counted.

Sql> Select COUNT (distinct department_id) from employees; Remove duplicate values to do statistics

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

Sql> Select COUNT (Distinct job_id) from employee

Calculate the number of people in each department

Sql> Select department_id, Count (employee_id) from Employees group by DEPARTMENT_ID;

=sql> Select department_id, Count (last_name) from Employees group by DEPARTMENT_ID;

GROUP by year for the sum of employees ' wages

Sql> Select Extract (year from hire_date), sum (salary) from Employees group by extract (year from hire_date); draw the year out

Having statement: the where that is the equivalent of group by

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

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

Order Form:

CustID StoreID ProdID Channelid

S100 P100 C100

Customer table:

CustID name Creditlevel

abc

Address Table:

CustID adress

bj

Tj

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

where e.department_id (+) =d.department_id

Select COUNT (*)

From Employees e, departments D

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// renaming a table

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)// the non-conformance of the e-table is also added, in the back of D +"(+) "

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 (+);

Sql99 standard notation

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.location_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 Query Statement 2"