Oracle Learning (ii)-Simple query

1. Main data sheet among Scott users
Dept, EMP, Salgrade, bonus. Observe four table structures (Syntax: DESC table).
|· Department Table: Dept
NO. Field type description
1, DEPTNO number (2) department numbers, up to two digits can be composed of
2, Dname VARCHAR2 (14) department name, composed of 14 bytes length
3. LOC VARCHAR2 (13) Department position
|· Employee table: EMP
NO. Field type description
1, EMPNO number (4) employee numbers, up to four digits only
2, ename VARCHAR2 (10) Employee name, consisting of 14 bytes length
3, Job VARCHAR2 (9) position, multiple employees of the position data will be repeated
4, MGR number (4) leader ID, the leader must also be the employee of the company
5, HireDate Date employment dates, date contains year, month, day, time, minute, second
6. SAL Number (7,2) base salary,represented by 2-bit decimal place and 5-bit integer digits
7, COMM number (7,2) Commission, sales staff will have commission
8. DEPTNO Number (2) the employee's department code, which corresponds to the Dept table
|· Salary scale table: Salgrade
NO. Field type description
1. GRADE Number Rating
2, Losal number this level of the minimum wage
3. Hisal number The highest wage in this class
|· Salary Table: Bonus
NO. Field type description
1. Ename VARCHAR2 (10) Employee name
2. Job VARCHAR2 (9) Job
3. SAL Number Salary
4. COMM Number Commission
* Note: The string data type in Oracle is VARCHAR2, and only the bonus table in four tables has no data
2. SQL syntax
SQL Introduction
|· DML data manipulation language. ———— refers to the implementation of the database query and follow the new operation
|· DDL data Definition Language. ———— object that defines the database
|· DCL Data Control Language. ———— Controlling user permissions for operations

• Simple Query
For the query is divided into: Simple query, limited query, multi-table query, statistical query, four types of queries.
SELECT [DISTINCT] *| column [alias], -->2, controlling the data columns to display
from table name [alias];-->1, determine the data source of the query
　　 //"|" Represents or, "distinct" means to remove duplicate rows of data, "*" means to query all columns,
Note: The order in which SQL statements are executed
Example
Query each employee's number (empno), name (ename), Position (job), base salary (SAL)
SELECT empno,ename,job,sal from EMP;
　　 various mathematical calculations can be made in the *select clause
Example
Query the number, name, and basic annual salary of each employee
SELECT empno,ename,sal*12 from EMP;
Example
Column alias not queried
SELECT empno number, ename name, sal*12 annual salary from EMP;
　　 * Note: In the actual development to avoid the use of Chinese
Example
Query each employee's number, name, position, annual salary, and each employee has a monthly allowance of 200 yuan for meals, 200 car subsidy
Summer four months have 300 yuan of high temperature subsidy, at the end of the time can have 15 months base salary.
SELECT Empno,ename,job, (sal+400) *12+300*4+sal*3 income from EMP;
Example
To eliminate repeating columns, use the DISTINCT keyword, which can only appear in the SELECT clause:
SELECT DISTINCT job from EMP;
　　 * Note: Repeat is only possible if all columns are duplicated.
A "| |" has been added to Oracle D-Connector for data connection
Example
SELECT ename | | Job from EMP;
* Example: Want to have such data to show
Item No: 7300, Name: Smith, Salary: 800
The data that is fixed in the program is called a string, and a single quotation mark "" is used for the representation of the string in the database;
SELECT ' number: ' | | empno | | ', name: ' | | ename info from EMP;

Oracle Learning (ii)-Simple query