Oracle Tutorial-Query (ii)

EMP Employee Table
Whether the field name data type is empty remarks
--------   -----------   --------   --------
EMPNO Number (4) Employee ID
Ename VARCHAR2 (Ten) Y employee name
Job VARCHAR2 (9) Y Job
MGR Number (4) Y of the Superior
HireDate Date Y Entry dates
SAL number (7,2) Y monthly Salary
COMM number (7,2) Y bonus
DEPTNO Number (2) Y belongs to the department
-------------------------------------------
Job field:
Clerk Staff
Salesman Sales
Manager managers
Analyst analysts
President President

Dept Department table
Whether the field name data type is empty remarks
--------    -----------      --------   --------
DEPTNO Number (2) Department No.
Dname VARCHAR2 (Y) department name
LOC VARCHAR2 (Y) Department location
-------------------------------------------
Dname field:
Accounting Finance Dept.
Research and Development Department
Operations Business Dept.

Salgrade Salary scale
Whether the field name data type is empty remarks
--------  ---------  --------  --------
GRADE number Y level
Losal Number Y Minimum wage
Hisal number Y Maximum wage

1. View table structure
DESC EMP;

2. Query all Columns
SELECT * FROM dept;
Note: Avoid the use of a select *, using * low efficiency, especially in the large table to pay attention to.

3, set timing on/off;
Turn on the switch showing the operating time, and show the operating time at the bottom.
eg, sql> insert into tb_stu values (' 0001 ', ' Zhangsan ', 24);
1 row inserted
Executed in 0.015 seconds

4. Insert Into...select ... Table Copy Statement
Syntax: INSERT into table2 (field1,field2,...) Select Value1,value2,... from table1

--Create a tb_dept table
CREATE TABLE Tb_dept
(
Deptno Number (4) is not NULL,
Dname VARCHAR2 (14),
Loc VARCHAR2 (13)
)
--Add a PRIMARY KEY constraint
ALTER TABLE TB_DEPT ADD constraint tb_dept primary key (DEPTNO);

--insert Into...select ... Usage
Insert into Tb_dept (Deptno, Dname, loc) Select A.deptno, A.dname, a.loc from dept A;

5. Statistics
Select COUNT (*) from EMP;

6. Query the specified column
Select Ename, Sal, Job, deptno from EMP;

7. How to cancel duplicate line distinct
Select distinct deptno, job from EMP;

8. Find Smith's department, job, salary
Select Deptno, Job, Sal from emp where ename = ' Smith ';
Note: Oracle is sensitive to the case of the content, so Ename= ' Smith ' and Ename= ' Smith ' are different

9. Nvl function
Format: NVL (string1, Replace_with)
Function: If string1 is null, the NVL function returns the value of Replace_with, otherwise the value of string1 is returned.
Note: string1 and replace_with must be of the same data type unless shown using the To_char function.
eg, how to show the annual salary of each employee?
Select SAL*13+NVL (comm, 0) *13 "annual salary", ename, comm from EMP;

10. Use the alias of the column
Select Ename "Name", sal*12 as "annual income" from EMP;

11. How to handle null values
Use the NVL function to handle

12. How to connect a string (| |)
Select Ename | | ' is a ' | | Job from EMP;

13. Using the WHERE clause
Question: How do i show employees with a salary above 3000?
SELECT * from emp where sal > 3000;
Question: How do I find employees who have been in employment since 1982.1.1?
Select Ename,hiredate from emp where HireDate > ' January-January-1982 ';
Question: How do i show employees with a salary of 2000 to 3000?
Select Ename,sal from emp where sal>=2000 and sal<=3000;

14. How to use the LIKE operator
%: represents 0 to more characters _: denotes any single character
Question: How do I display the employee name and salary for the first character s?
Select Ename,sal from emp where ename like ' s% ';
How do i show the names and wages of all employees with a third character in uppercase O?
Select Ename,sal from emp where ename like ' __o% ';

15. Use in in the Where condition
Question: How do i show an employee empno to 7844,7839,123,456?
SELECT * from EMP where empno in (7844, 7839, 123, 456);

16. Operator using is null
Question: How do i show an employee without a superior?
Error notation: SELECT * from emp where mgr = ';
Correct wording: SELECT * from emp where Mgr is null;

Source: >  

From for notes (Wiz)

Oracle Tutorial-Query (ii)