4. Advanced Queries and functions

I. DML, functions, advanced queries

--Unlock for Scott users
sql> alter user Scott account unlock;
The user has changed.

--Set the password for Scott
Sql> alter user Scott identified by Tiger;
The user has changed.

--Switch user connections
Sql> Conn Scott;
Enter Password: Tiger
is connected.

--Delete the score column in the Student score table
ALTER TABLE stuinfo drop column score;

--Add Class column
ALTER TABLE Stuinfo Add (Classno varchar2 (12));

--View table structure
Desc Stuinfo;

--Query sequence
Select Sequence_name from User_sequences;

--INSERT statement
sql> INSERT into stuinfo values (Seq_stuinfo.nextval, ' Holly ', ' TB07 ');

1 rows have been created.

sql> INSERT into stuinfo values (Seq_stuinfo.nextval, ' Tom Shuai ', ' TB07 ');

1 rows have been created.

sql> INSERT into stuinfo values (Seq_stuinfo.nextval, ' Lu Yiwen ', ' TB07 ');

1 rows have been created.

sql> INSERT into stuinfo values (Seq_stuinfo.nextval, ' Zhang Zongchi ', ' TB07 ');

1 rows have been created.

sql> INSERT into stuinfo values (Seq_stuinfo.nextval, ' Zhou Qiang ', ' TB07 ');

1 rows have been created.

sql> INSERT into stuinfo values (seq_stuinfo.nextval, ' Dong Yan ', ' TB07 ');

1 rows have been created.

sql> INSERT into stuinfo values (seq_stuinfo.nextval, ' Shidon ', ' TB07 ');

1 rows have been created.

sql> INSERT into stuinfo values (seq_stuinfo.nextval, ' Qian Tao ', ' TB07 ');

1 rows have been created.

--Revision of Student score table structure
Sql> ALTER TABLE Stuscore Modify (中文版 number (3,1), chain number (3,1), history number (3,1));

sql> desc Stuscore;
is the name empty? Type
----------------------------------------- -------- ----------------------------
ID not NULL number
SID not NULL number
中文版 Number (3,1)
CHAIN Number (3,1)
History number (3,1)
Examtime DATE

--Insert Student score Information
sql> INSERT into Stuscore values (seq_stuscore.nextval,1,50.5,34.5,35.5,to_date (' 2014-4-4 ', ' yyyy-mm-dd '));

1 rows have been created.

sql> INSERT into Stuscore values (seq_stuscore.nextval,2,70.5,64.5,85.5,to_date (' 2014-4-8 ', ' yyyy-mm-dd '));

1 rows have been created.

sql> INSERT into Stuscore values (seq_stuscore.nextval,3,70.5,64.5,85.5,to_date (' 2014-4-8 ', ' yyyy-mm-dd '));

1 rows have been created.

sql> INSERT into Stuscore values (seq_stuscore.nextval,4,70.5,64.5,85.5,to_date (' 2014-4-8 ', ' yyyy-mm-dd '));

1 rows have been created.

sql> INSERT into Stuscore values (seq_stuscore.nextval,5,70.5,64.5,85.5,to_date (' 2014-4-8 ', ' yyyy-mm-dd '));

1 rows have been created.

sql> INSERT into Stuscore values (seq_stuscore.nextval,6,70.5,64.5,85.5,to_date (' 2014-4-8 ', ' yyyy-mm-dd '));

1 rows have been created.

sql> INSERT into Stuscore values (seq_stuscore.nextval,7,70.5,64.5,85.5,to_date (' 2014-4-8 ', ' yyyy-mm-dd '));

1 rows have been created.

sql> INSERT into Stuscore values (seq_stuscore.nextval,8,70.5,64.5,85.5,to_date (' 2014-4-8 ', ' yyyy-mm-dd '));

1 rows have been created.

Second, simple query, fuzzy query, conditional query, single-line function

--Toggle Sqlplus
>sqlplus
Enter User-name:system
Enter PASSWORD:ACCP
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-production
With the partitioning, OLAP and Data Mining options

--Unlock Scott test users
sql> alter user Scott account unlock;

User altered.

--Set a password for Scott users
Sql> alter user Scott identified by Tiger;

User altered.

--Switch Scott user Login
Sql> Conn Scott;
Enter Password:tiger;
Connected.
--1. Basic queries
--1.1 Search for Employee positions
Sql> SELECT distinct job from EMP;

--1.2 query each employee's position (the employee is unique, the position can be duplicated)
Sql> select Ename,job from emp;

--2. Arithmetic operators
--2.1 query Each employee's name, position, annual salary
Sql> select ename,job,sal*12 from emp;

--2.2 query Each employee's name, position, annual salary (300 per employee bonus)
Sql> Select Ename,job, (sal+300) *12 as income from EMP;

--2.3 company every year to give everyone a bonus 300 at the same time, the end will be more than one months base salary
Sql> Select Ename,job, (sal+300) *12+sal as income from EMP;

--2.4. Data class Table display Chinese text fields
Sql> SELECT ' Employee number: ' | | empno| | '; Employee Name: ' | | Ename from EMP;

--3: Qualifying Queries
--3.1 Query Employee Number 7839 Employees
Sql> Select Empno,ename,job, sal from EMP where empno=7839;

--4. Relational operations
--4.1 query employee information for wages above 1500
Select Empno,ename,job, sal from EMP where sal>1500;

--4.2 query employee information for salaries between 1500 and 300
Select Empno,ename,job, sal from EMP where sal>=1500
and sal<=3000;

--4.3 Job search is all information salesman the clerk clerk or sales person
Sql> Select Empno,ename,job,sal from
EMP where job= ' clerk ' or job= ' salesman ';

--4.4 query positions are information salesman by clerical clerk or salespeople and require that employees pay more than 1500
Select Empno,ename,job,sal from emp
Where (job= ' clerk ' or job= ' salesman ') and sal>1500;

--4.5 query All employee information that is not a clerk clerk
Select Empno,ename,job,sal from emp where job! = ' Clerk ';

Select Empno,ename,job,sal from emp where job <> ' clerk ';

Select Empno,ename,job,sal from emp where not job= ' clerk ';

--5. Determine if null: Is (not) null
--5.1 Find all employee information for bonus collection
Select Empno,ename,job,sal,comm from emp
Where comm is not null;

Select Empno,ename,job,sal,comm from emp
Where not comm is null;

--6. Determining the range: in operator

--6.1 all employees who do not receive bonuses
Sql> Select Empno,ename,job,sal,comm from emp
where comm is null;

--6.2 Query Employee number is 7369,7566,7788 employee information
Select Empno,ename,job,sal,comm from emp
where empno=7369 or empno=7566 or empno=7788;

Select Empno,ename,job,sal,comm from emp
where Empno in (7369, 7566, 7788);

--7. Fuzzy query like
--7.1 Query the employee name for all information that begins with the letter A
Select Empno,ename,job,sal,comm from emp
where ename like ' A% ';

--7.2 query All information about the second letter of a in the employee's name
Select Empno,ename,job,sal,comm from emp
where ename like ' _a% ';

--7.3 Query the employee name contains all the letter a information
Select Empno,ename,job,sal,comm from emp
where ename like '%a% ';

--7.4 Query the employee name does not contain all the information of the letter A
Select Empno,ename,job,sal,comm from emp
where ename not like '%a% ';

--8. Sorting data: Default ascending ASC, descending desc
--8.1 query all employee information and ask for a salary in ascending order
Select Empno,ename,job,sal,comm from emp
Order BY Sal;

Select Empno,ename,job,sal,comm from emp
ORDER BY Sal ASC;

--8.2 query All employee information, ask for salary descending order
Select Empno,ename,job,sal,comm from emp
ORDER BY Sal Desc;

--8.2 query All employee information, ask for payroll sal descending order, hire date HireDate Ascending
Select Empno,ename,job,sal,comm from emp
ORDER by Sal Desc,hiredate ASC;

--9. Single-line function
--9.1 character functions
--(1) Convert to uppercase:
Select Upper (' Hello ') from dual;

--(2) Convert to lowercase:
Select Lower (ename), Sal from EMP;

--(3) dynamic input
Sql> Select Empno,ename,sal from emp where ename= ' &str ';
Enter value for Str:smith
Old 1:select empno,ename,sal from emp where ename= ' &str '
New 1:select empno,ename,sal from emp where ename= ' SMITH '

EMPNO ename SAL
---------- ---------- ----------
7369 SMITH 800

--converted to uppercase by upper at input
Sql> Select Empno,ename,sal from emp where Ename=upper (' &str ');
Enter value for Str:smith
Old 1:select empno,ename,sal from EMP where Ename=upper (' &str ')
New 1:select empno,ename,sal from EMP where Ename=upper (' Smith ')

EMPNO ename SAL
---------- ---------- ----------
7369 SMITH 800

--(4) Capitalize the first letter of each employee, all the remaining lowercase
Select Initcap (ename) from EMP;

Initcap (EN
----------
Smith
Allen
Ward
Jones

-(5) query the length of each employee's name
Sql> Select Ename,length (ename) from EMP

Ename LENGTH (ename)
---------- -------------
SMITH 5
ALLEN 5

--(6) Replace all the A in the name with _
Sql> Select Replace (ename, ' A ', ' _ ') from EMP;

REPLACE (EN
----------
SMITH
_llen;

--(7) Intercept string
--substr (field, start point); Start point is starting from 1, which means to intercept from the start point to the last
Sql> Select Ename,substr (ename,3) from EMP;

Ename SUBSTR (ename,3)
---------- ----------------
SMITH ITH
ALLEN LEN

----substr (field, start point, length); Start point is starting at 1,
Sql> Select Ename,substr (ename,2,3) from EMP;

Ename SUBSTR
---------- ------
SMITH MIT
ALLEN LLE

--Requires the first three letters of the employee's name to be intercepted
Select Ename,substr (ename,-3) from EMP;

Select Ename,substr (Ename,length (ename)-2) from EMP;

Sql> Select Ename,substr (ename,-3,3) from EMP;

Ename SUBSTR
---------- ------
SMITH ITH

--(8) Number function
--round (number | column [, Number of decimal places reserved]: rounding
Sql> Select Round (903.53) from dual;

ROUND (903.53)
-------------
904

--Take the modulus (take the remainder)
Sql> Select mod (10,3) from dual;

MOD (10,3)
----------
1

Sql> Select mod (9,3) from dual;

MOD (9,3)
----------
0
--to the number of small parts
Sql> Select Trunc (93.6) from dual;

TRUNC (93.6)
-----------
93

--(9) Date function
--Get the number of days of employment per employee to date
Select Ename,hiredate,trunc (sysdate-hiredate) from EMP;

--Get the last day of the month
Sql> Select Last_day (sysdate) from dual;

Last_day (SYSDA
--------------
3 January-December-15

--Gets the date of the next specified week
Sql> Select Next_day (sysdate, ' Monday ') from dual;

Next_day (SYSDA
--------------
July-December-15

--Find out the date after four months
Select Add_months (sysdate,4) from dual;

--Find out the month of employment for each employee until today
--select ename, Trunc (Months_between (sysdate,hiredate)) from EMP;

(10) Conversion function
--to_char () turns into a string
Sql> Select To_char (sysdate, ' Yyyy-mm-dd ') from dual;

To_char (SY
----------
2015-12-03

--to intercept each month and day
Sql> Select To_char (sysdate, ' yyyy-mm-dd ') as date, To_char (sysdate, ' yyyy ') as Year
2 To_char (sysdate, ' mm ') as month, To_char (sysdate, ' DD ') as day
3 from dual;

Date Month Day
---------- ---- -- --
2015-12-03 2015 12 03

Sql> Select To_char (sysdate, ' yyyy-mm-dd ') date, To_char (sysdate, ' yyyy ') year
2 To_char (sysdate, ' mm ') month, To_char (sysdate, ' DD ') day
3 from dual;

Date Month Day
---------- ---- -- --
2015-12-03 2015 12 03
--Gets the date and time of the string, with a single value of 0
Sql> Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

To_char (sysdate, ' YY
-------------------
2015-12-03 11:55:35

--Gets the date and time of the string, and the single value does not have 0
Sql> Select To_char (sysdate, ' Fmyyyy-mm-dd hh24:mi:ss ') from dual;

To_char (sysdate, ' YY
-------------------
2015-12-03 11:55:35

--Query with the money symbol ￥
Sql> Select To_char (9999999999, ' l999,999,999,999 ') from dual;

To_char (9999999999, ' l999,9
--------------------------
￥9,999,999,999

--Convert the string to date
Sql> Select To_date (' 1999-12-2 ', ' YYYY-MM-DD ') from dual;

To_date (' 1999-
--------------
February-December-99

--Convert a string to a number
Sql> Select To_number (' 1 ') +to_number (' 2 ') from dual;

To_number (' 1 ') +to_number (' 2 ')
-----------------------------
3

4. Advanced Queries and functions