Iii. Simple Oracle Query

Oracle Table query

Premise: understand the meaning of several Scott tables.

EMP: employee table

Field	Type	Note
Empno		Employee ID
Ename		Employee name
Job		Employee position
Mgr		Superior ID
Hiredate		Employment date
Sal		Salary
Comm		Bonus
Deptno		Department ID

 

 

 

Demp: department table

Field	Type	Note
Deptno	Number (2)	Department ID
Dname	Varchar2 (14)	Department name
Loc	Varchar2 (13)	Location

 

Salgrade: wage level table

Field	Type	Note
Deptno	Number (2)	Department ID
Dname	Varchar2 (14)	Department name
Loc	Varchar2 (13)	Location

 

 

1. View table structure

DescDept;

1. Query all columns

Select * from Dept;

1. Query a specified Column

Select ename, Sal, job, depno from EMP;

1. Cancel duplicate rows

Select distinct deptno from EMP;

Example: Query Smith's salary, job, and department.

Select Sal, job, deptno from EMP where ename = 'Smith ';

 

Example: Create a table: Create Table users ( Userid varchar2 (20 ), Username varchar2 (30 ), Userpass varchar2 (20) ); Insert data: Insert into users values ('a0001 ', 'wang erxiao hahaha', 'fanyong @ gmail.com '); Value assignment data: add your data to your table: Insert into users (userid, username, userpass) Select * from users; The data size after each insert is doubled from the previous one, Query the number of records: Select count (*) from users;

 

1. Use the arithmetic expression and column alias

Displays the annual salary of each employee

Select ename "name", Sal * 12 + comm * 12 "annual income" from EMP;

However, the calculation result is incorrect. For all records whose salary comm field is null, the annual income is null.

1. How do I calculate the null value?

Use the nvl function to determine whether the value is null.

Select ename "employee", Sal * 12 +Nvl (Comm, 0)* 12 "annual income" from EMP;

1. Use where clause

How to display employees with salaries higher than 3000:

Select ename, Sal from EMP where SAL> 3000;

How to find employees who have joined the company since January 1, January 2, 1982

Select ename, hiredate from EMP whereHiredate> '2-January 1, January-1982 ';

How to show employees with salaries between 2000 and 2500

Select ename, Sal from E where SAL> 2000AndSal <1, 2500;

1. Use in where

How to display employees whose employee numbers are 101, 220, or 340

Select * from EMP empnoIn(101,220,340 );

1. Use the like Operator

%: Represents any 0 to multiple characters

_: Represents any 1 Character

How to display employee names and salaries whose first letter is S

Select ename, Sal from EMP where enameLike'S % ';

How to display the name and salary of all employees whose third character is uppercase o

Select ename, Sal from EMP where enameLike'_ O % ';

1. Use the is null, is not null operator

How to display information without a superior employee

Select ename from EMP where MGR is null;

1. Use logical operation symbols

Query employees whose salaries are higher than 500 or whose positions are manager, and set their names to uppercase letters J.

Select * from EMP where (SAL> 500 or job = 'manager') and ename like 'J % ';

1. Sort by order by clause

How to display employee information in ascending order of salary

Select * from EMP order by SalASC;

How to sort employees in descending order by Department number

Select * from EMPOrderDeptno ASC, SalDesc;

1. Sort by column alias

Select * from EMP where (SAL + nvl (Comm, 0) * 12 "annual salary" from EMP order by "annual salary ";

1. Paging Query

Query students first!