Oracle Learning Notes (ii)

1. Clear the screen

Windows:host CLS;

Linux:host Clear;

2. Processing of dates

1) system default type:

Select Sysdate from dual;--> Note: Sysdate is the system's current time system default format: Day-month-year

2) usage of to_char ()

Syntax: To_char (date,'format_model')

Format:

elements of a date format

Format	Description	Example
YYYY	Full year in numbers	2011
Year	Year spelled out (English full name)	Twenty Eleven
Mm	Two-digit value of month (two digits)	04
MONTH	Full name of the month (full name)	April
Dy	Three-letter abbreviation of the Day of the week (Days of the week)	Monday
Day	Full name of the day of the week	Monday
Dd	Numeric Day of the month	02

Select To_char (sysdate, ' Yyyy-mm-dd ') from dual;

3) Modify the system's default date type:

View system Default Parameters: SELECT * from V$nls_parameters;

Modifies the default format for date types in the current session (only valid for the current session)

Alter session set nls_date_format= ' YYYY-MM-DD '; Valid only for the current session

4) Use the Date type function

Sql> SELECT * from emp where hiredate>to_date (' 1981-12-31 ', ' yyyy-mm-dd ');
Or
Sql> SELECT * from emp where TO_CHAR (hiredate, ' yyyy-mm-dd ') > ' 1981-12-31 ';

Syntax: to_char (date, ' Format_model ')
Syntax: to_date (str, ' Format_model ')

3. Sorting

Syntax: ORDER BY column name ASC/DESC Note: column names here can only be numbers, strings, date types

ASC is ascending by default

Desc Descending

Example: Sort by Employee's name

Example: Sort by employee's salary from low to high

When the order by column contains NULL, it will:
In ascending order, NULL is below.
In descending order, NULL is above.
We hope that, regardless of ascending or descending, the null value is always below

Mode one: Select Empno,ename,sal,comm from emp ORDER by comm nulls last;--> using nulls last allows null to be placed on the final side

Mode two:select empno, ename, Job, HireDate, Sal, NVL (comm, 0) from emp ORDER BY 6 DESC

4. Group functions

Select
Max (SAL) Wages,
Min (sal) minimum wage,
AVG (SAL) Average salary,
SUM (SAL) The wages of all employees and,
Number of employees with Count (SAL) payroll
from EMP;

Example: Querying the average wage in an EMP table

Select SUM (COMM)/count (*) from EMP; COUNT (*) Here queries the number of records that include Comm as null

Select AVG (comm) from EMP; the AVG here will automatically consider NULL, so the comm-null line doesn't count as 3 of the records here.

Thus the use of AVG should read: Select AVG (NVL (comm,0)) from EMP; Note: Functions can be nested using

Attention:

GROUP by a column
Group BY multiple columns
There is a different group of multiple columns that participate in grouping.

The difference between having and where:

Having, after splitting the group and then filtering, shows only the results that match the conditions.
Aliases cannot be used in both group by and have.
The difference from where
Having is the separation of the group after the filter.
Where is the first filter, then the grouping operation.
If possible, write where conditions, do not write having.

5. Sub-query

When a step cannot be solved, the subquery can be used

Divided into: single-line subquery/multirow sub-query

Example: Querying employee information for wages higher than Scott

Select ename from emp where sal> (select Sal from emp where ename= ' SCOTT ');

Note the problem:
1. Sub-query to right indent relative to main query
2. Enclose the subquery in parentheses
3. Line break
4. Can be in the main query of the Select, from, where, have can be placed sub-query
5. You can not query the group by in the main query
6. The main query and subquery can be used instead of a single table, as long as the subquery returns the result that the main query can use to
7. Sub-query from the back (* * * * *)
Select Back query: The subquery must be a single-line subquery
From back sub-query: Query employee's number and name
The main query and subquery can be used without a single table, as long as the subquery returns the result that the main query can use to
Example: Query employee information for Department name Accounting

Multi-row subqueries
In: Within the collection
Example: Querying employee information for department name accounting and sales

SELECT * from EMP where deptno in (select Deptno from dept where dname = ' ACCOUNTING ' or dname= ' SALES ')
Any: compare with any (random) value in a subquery
Inquire about employees with a salary lower than any one employee in department 10th

SELECT * from emp where Sal<any (select Sal from EMP where deptno=10);

SELECT * from emp where sal< (select min (sal) from EMP where deptno=10);
All: Comparison of all values in the subquery
Inquire about employees who pay less than all employees in department 10th

SELECT * from emp where Sal<all (select Sal from EMP where deptno=10);
**
Any: Less than the maximum value in the collection
All: Less than the minimum value in the collection
Any: greater than the minimum value in the collection
All: greater than the maximum value in the collection

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Oracle Learning Notes (ii)