Oracle scott table structure and simple query instance analysis, oraclescott

Oracle scott table structure and simple query instance analysis, oraclescott

This article describes the scott table structure and simple query methods in Oracle. Share it with you for your reference. The specific analysis is as follows:

1. scott's table structure

View table structure

Desc table name; // desc emp;
Emp table:
SQL> desc emp;
Is the name empty? Type
-------------------------------------
Empno not null number (4) employee ID
ENAME VARCHAR2 (10) employee name
JOB VARCHAR2 (9) Employee position
Mgr number (4) corresponds to the lead NUMBER
Hiredate date employment DATE
Sal number (7, 2) Basic Salary
Comm number (7, 2) bonus, Commission
Deptno number (2) department no.

Dept table:
SQL> desc dept;
Is the name empty? Type
-------------------------------------
Deptno not null number (2) department ID
DNAME VARCHAR2 (14) department name
Location of LOC VARCHAR2 (13) Department

Salgrade table:
SQL> desc salgrade;
Is the name empty? Type
-----------------------------------
GRADE NUMBER
Losal number highest salary of this level
Hisal number highest salary of this level

Bonus table:
SQL> desc bonus;
Is the name empty? Type
-------------------------------------
ENAME VARCHAR2 (10) employee name
JOB VARCHAR2 (9) Employee position
Sal number employee's salary
Comm number employee bonus

2. Simple Query

1. query non-repeated positions

select distinct job from emp;

2. query the annual salary and alias. Do not enclose the alias in single quotation marks.

select sal*12 [as] income from emp;

3. You can use "|" to connect to the queried fields in a simple query.

select empno ||','|| ename from emp;

Strings in SQL statements are enclosed in single quotes.
Select 'employee ID: '| empno |' name: '| ename | 'salary:' | sal | 'position: '| job | '! 'Employee information from emp;

4. Data time zones in oracle are case-sensitive

select * from emp where job ='CLERK';

Query employee information that is not a clerk

select * from emp where job!='CLERK'; select * from emp where job<>'CLERK';select * from emp where NOT job='CLERK';

5. ...... AND greater than or equal to less than or equal

select * from emp where sal between 1500 and 3000; 

You can also reverse

select * from emp where sal not between 1500 and 3000;

It can also be a date.

Select * from emp where hiredate between '01-February 1, January-1981 'and '31-February 1, December-81 ';

6. Determine whether it is null. IS (NOT) NULL

select * from emp where comm Is not null; select * from emp where not comm Is null;

7. IN Operator

select * from emp where empno in (7521,7844,5555);select * from emp where empno not in (7521,7844,5555);

About NOT IN
If the IN operator is used, null exists IN the query range, and the query is not affected.

select * from emp where empno in (7521,7844,null);

If the not in operator is used, if null exists IN the query range, no results are returned.

select * from emp where not empno in (7521,7844,null);select * from emp where empno not in (7521,7844,null);

8. LIKE clause

Matching symbol:
Match a single character: _ 0
Match any number of characters: % 0, one, or more
Query all employees whose names start with ""

select * from emp where ename like 'A%';

Query employees whose second letter is ""

select * from emp where ename like '_A%'; 

Query employees with the letter ""

select * from emp where ename like '%A%'; 

Query employees without the letter ""

select * from emp where not ename like '%A%'; select * from emp where ename not like '%A%';

LIKE '%' indicates querying all data

select * from emp where empno like '%%'

9. Sort data

Order by field [ASC | DESC] [, field [ASC | DESC]…];

The order by clause must be followed by the where clause and end with all SQL statements.
Multiple sorting fields can be specified during sorting.
There are two sorting methods: the default (ASC) Ascending, DESC (descending)
Sort by salary

select * from emp order by sal desc;

Sort by salary first, and then by employment date

select * from emp order by sal desc,hiredate;

I hope this article will help you with Oracle programming.