Oracle Query function Exercise

/*
--The following code is the display width setting for the EMP table
Col empno for 9999;
Col ename for A10;
Col job for A10;
Col Mgr for 9999;
Col hiredate for A12;
Col Sal for 9999;
Col Comm for 9999;
Col deptno for 99;
Set pagesize 20;

--Create a new table new_emp, copy the structure and data from the EMP table into the New_emp table
CREATE TABLE XXX
As
SELECT * from EMP;

--Delete xxx table
drop table xxx Purge;
*/

The Oracle database server consists of two parts:
A) database (physical concept, bottom layer is a collection of database-specific files)
B) DB instance (logical concept, can only operate database through DB instance)

Sql
B) DML (Data manipulation language, for example: Insert,update,delete,select)
B) DDL (data definition language, for example: Create Table,drop table,alter table)
C) DCL (Data Control language, for example: Grant GRANT, revoke rights)
D) TCL (transaction control language, for example: Rollback,commit, transaction start)

-------------------------------------------------------------------------------------

From the Sqlplus environment, enter the Oracle DB instance with Scott User and 123456 password
C:\>sqlplus scott/123456

Exit Sqlplus Environment
sql:\>exit;

Query all tables under the Scott user
SELECT * from tab;

Query who the current user is
Show user;

Set the displayed column width (character, date), 10 characters wide, a for the character type
Col ename for A12;
A12: Indicates a display width of 12 characters, a case insensitive

Executes the most recent SQL statement, not the Sqlplus command
/represents the most recently executed SQL statement
Col ename for A10, which is not an SQL statement, but a command in the dedicated Sqlplus tool provided by Oracle

Clear Screen
Host CLS;
Sqlplus command, can be abbreviated, semicolon can be omitted
SQL statement, not abbreviated, must end with a semicolon

Querying the structure of an EMP table
DESC EMP;
Digital Type: Number
Character Type: VARCHAR2
Date Type: Date

Querying all the contents of an EMP table
SELECT * from EMP;

Set the displayed column width (numeric), 9 for numeric, one 9 for digit, four 9 for four digits
Col empno for 999999;
Col says: Column
Empno: Column Name
For means: Format
999999: Display 6 character width, only for numbers

Set up to display up to 20 records in one page
Set pagesize 20;

Employee number, name, salary, department number for the EMP table
Select Empno,ename,job,deptno from EMP;

Querying the EMP table for non-repetitive work
SELECT distinct job from EMP;

SELECT distinct job,sal from EMP;
Ultimately determined by the Cartesian product of the job and Sal

Query employee's number, name, salary, annual salary (monthly *12)
Select empno,ename,sal,sal*12 from EMP;

To modify a recent SQL statement
Edit

Check the employee's number, name, salary, annual salary, annual income (annual salary + bonus has a null value)
Select Empno,ename,sal,sal*12,sal*12+comm from EMP;
NULL Arithmetic number =null

To solve the problem of NULL, use the NVL () function, NVL (A, B): If A is empty, replace
Select EMPNO,ENAME,SAL,SAL*12,SAL*12+NVL (comm,0) from EMP;
Null!=0
null!= Blank string

Use aliases to query employee's number, name, salary, annual salary, annual income (annual salary + bonus)
Select Empno as "number", ename as "name", Sal as "monthly salary", sal*12 as "annual salary", SAL*12+NVL (comm,0) as "annual income"
From emp;//correct

Select Empno "Number", ename "name", Sal "Monthly Salary", sal*12 "annual salary", SAL*12+NVL (comm,0) "Annual income"
From emp;//correct

Select empno ' number ', ename ' name ', sal ' monthly salary ', sal*12 ' annual salary ', SAL*12+NVL (comm,0) ' annual income '
Error from emp;//,

Select empno number, ename name, Sal monthly salary, sal*12 annual salary, SAL*12+NVL (comm,0) annual income
From emp;//correct

Select empno number, ename name, Sal monthly salary, sal*12 annual salary, SAL*12+NVL (comm,0) annual income
Error from emp;//

Select Empno "Number", ename "name", Sal "Monthly Salary", sal*12 "annual salary", SAL*12+NVL (comm,0) "Annual income"
From emp;//correct, to use whitespace in aliases, double quotation marks are used

Use the string join symbol to output "Hello World" using the dual table
The dual table is a dumb table designed by Oracle to spell some languages.
Because of the Oracle syntax, there is a SELECT clause that must have a FROM clause

Displays the current time of the system
Select Now () This is the syntax for MySQL
Select Sysdate from dual;

Use the string join symbol to display the following format information: XXXX's salary is yyyy.
Mode one: Select ' Hello World ' from dual;
Mode two: Select ' Hello ' | | "World" from dual;
Select Ename | | ' Salary ' | | Sal "Salary Situation" from EMP;

Save the SQL statement to the file and create the SQL file
Spool D:\1234.sql;

Save the SQL statement and the results of its execution
Spool off;

Executes the SQL statement in the file, which must be a *.sql file
@ d:\1234.sql;

Single-line Comment
--select * from EMP;

Multi-line comments
/**/

SQL vs SQLPLUS

Sql
1) language
2) ANSI Standard
3) Key Words cannot be abbreviated
4) must be a semicolon end
5) Update the data in the table

SQLPLUS
1) Environment, command
2) is one of the characteristics of Oracle
3) Key words can be abbreviated
4) Semicolon optional
5) The data in the table cannot be updated

html:<!---->
JSP: <%----%>
XML: <!---->
pros:#
Java:
A)//
B)/* */
C)/** */
mysql:#
Oracle:
A)--
B)/* */
Js:
A)//
B)/* */
Css:
/* */

-------------------------------------------------------------------------------------

Find employees in Department number 20th
SELECT *
From EMP
where deptno = 20;

Query name is Smith's employee, string value, case sensitive
SELECT *
From EMP
where ename= ' SMITH ';
Insensitive: Table name, field name, ' A '
Sensitive: string constant value

For December 17, 1980 employees, "1 July-December-80" satisfies the Oracle default date format (DD-MON-RR represents a 2-bit year)
SELECT *
From EMP
where hiredate = ' 1 July-December-80 ';

Query employees with a salary greater than 1500
SELECT * from emp where sal > 1500;

Find employees with salaries between 1300 and 1600, including 1300 and 1600
SELECT * from emp where sal>=1300 and sal<=1600;//correct
SELECT * from emp where sal<=1600 and sal>=1300;//correct
SELECT * from emp where Sal between 1300 and 1600;//if it is numeric: small value in front, large value behind, return to No value

Check in employees between "20月-February-81" to "2 March-January-82"
SELECT * from emp where hiredate between ' 2 March-January -82 ' and ' 20月-February-81 ';//No value
SELECT * from emp where hiredate between ' 20月-February -81 ' and ' 2 March-January-82 ';//If the date type: Small value in the back, large value in front, return to No value

Find employees in department 20th or 30th
SELECT * from emp where deptno=20 or deptno=30;
SELECT * from EMP where deptno in (20,30);

Query employees whose names begin with "S"
SELECT * from emp where ename like ' s% ';
% means: 0, 1, multiple characters

_ Means: 1 character (s)

Query employee whose name ends with "N", representing 0 or more characters
SELECT * from emp where ename like '%N ';

Query the employee whose name is 4 characters, _ The underscore can only represent 1 characters
SELECT * from emp where ename like ' w___ ';

Query employee names containing ' _ ' employees, let the character back to the original meaning "like '%\_% ' escape '"
SELECT * from emp where ename like '%\_% ' escape ' \ ';//correct
SELECT * from emp where ename like '%e_% ' escape ' E ';//correct
SELECT * from emp where ename like '%$_% ' escape ' $ ';//correct
SELECT * from emp where ename like '% #_% ' escape ' # ';//correct
SELECT * from emp where ename like '%@_% ' escape ' @ ';//correct
SELECT * from emp where ename like '%%_% ' escape '% ';//Error

Query for employees with Commission NULL
SELECT * FROM emp where comm = null;//Error
SELECT * FROM emp where comm is null;//correct
SELECT * FROM EMP where comm are not null;//correct

Find employees who have no commissions and pay more than 1500
SELECT * from EMP where (comm is null) and (SAL>1500);

Query wages are 1500 or, 3000 or 5000 of employees
SELECT * from emp where Sal in (1500,3000,5000);

Search for positions that are "MANAGER" or employees who are not "ANALYST" (Way One)
SELECT * from emp where (job = ' MANAGER ') or (Job! = ' ANALYST ');

Search for positions that are "MANAGER" or employees who are not "ANALYST" (Way Two)
SELECT * from emp where (job = ' MANAGER ') or not (Job = ' ANALYST ');

Query Employee information (number, name, salary, annual salary), sorted by monthly salary, ascending by default
Select Empno "Number", ename "name", Sal "Monthly Salary", sal*12 "annual salary" from EMP order by SAL ASC;

Query employee information in descending order by entry date
SELECT * from emp order BY hiredate Desc;

The order by can follow the column name, alias, expression, column number, starting with 1
Column name sort: SELECT * from emp order BY hiredate Desc;
Alias sort: Select Ename "Name", Sal "Monthly Salary", HireDate "entry Time" from EMP Order by entry time Desc;
Expression sort: Select Ename "Name", sal*12 "annual salary", HireDate "entry Time" from emp order by sal*12 Desc;
Sort By column number: select Ename "Name", sal*12 "annual salary", HireDate "entry Time" from emp order by 2 desc;

Query employee information, sorted by commission in ascending or descending order, null value as maximum value
SELECT * FROM emp ORDER BY comm Desc;

Query employee information in descending order of wages, employees of the same wage are then sorted in ascending order
SELECT * from emp ORDER by Sal Desc,hiredate ASC;

Query number 20th, with a salary greater than 1500, sorted in descending order of entry time
SELECT *
From EMP
where (deptno=20) and (Sal > 1500)
ORDER BY hiredate Desc;

-------------------------------------------------------------------------------------

Testing the LOWER/UPPER/INITCAP function
Select lower (' Www.itCAST.cN ') from dual;
Select Upper (' Www.itCAST.cN ') from dual;
Select Initcap (' Www.itCAST.cN ') from dual;

Test the CONCAT/SUBSTR function, starting with 1
Select concat (' Hello ', ' world ') from dual;
Select ' Hello ' | | "World" from dual;
Select substr (' www.itcast.cn ', 12,2) from dual;
12 means: Starting with the first character and 1
2 means: Take a few characters together

Test the LENGTH/LENGTHB function, provided that when you install Oracle, the encoding is UTF-8, and a Chinese account is 3 bytes long
Select Length (' China's ') from DUAL;//7
Select LENGTHB (' China's ') from dual;

Testing the Instr/lpad/rpad function
Select InStr (' Hello World ', ' W ') from DUAL;//4
Select Lpad (' Hello ', ten, ' @ ') from dual;
Select Rpad (' Hello ', ten, ' # ') from dual;

Testing the Trim/replace function
Select Trim (' W ' from ' wwwwwbookwwwwwwwwwwwwww ') from dual;
Select replace (' www.itcast.cn ', ' cn ', ' COM ') from dual;

Testing the Round/trunc/mod function
Select Round (3.1415,3) from dual;
Select Trunc (3.1415,3) from dual;
Select mod (10,3) from dual;

Test round action at current time (month)
December-March-13
Select round (sysdate, ' month ') from Dual;//01-3 month-13
Select round (Sysdate, ' year ') from Dual;//01-1 month-13

Test trunc action at current time (month)
December-March-13
Select Trunc (sysdate, ' month ') from Dual;//01-3 month-13

Test trunc action at current time (year)
Select Trunc (Sysdate, ' year ') from Dual;//01-1 month-13

Round and trunc can also be used in the date type in addition to the numerical type.

Test Sysdate, by default, displays only the date part, in the format: day-month-2-digit year
Select Sysdate from dual;

Show yesterday, today, tomorrow's date, date type only +-numeric
Select Sysdate-1 "Yesterday", sysdate "Today", sysdate+1 "tomorrow" from dual;

Show employee approximate seniority in year and month, date only-date = value
Select Ename,hiredate, (sysdate-hiredate)/30 "Approximate monthly seniority", (sysdate-hiredate)/365 "Approximate years of seniority" from EMP;

Use the Months_between function to calculate how many months until the end of the year
Select Months_between (' 3 January-December -13 ', sysdate) from dual;

Use the Months_between function to display employee seniority in a precise monthly format
Select Ename, (sysdate-hiredate)/30 "Approximate monthly seniority", Months_between (sysdate,hiredate) "accurate monthly seniority" from EMP;

Testing the Add_months function
Select Add_months (sysdate,1) from dual;

Test the Next_day function, starting today, what number is the next one weeks? "Chinese Platform"
Select Next_day (sysdate, ' Sunday ') from dual;

Test the Last_day function, what number is the last day of the month?
Select Last_day (sysdate) from dual;

Test the Last_day function, what number is the last day of the next one months?
Select Last_day (add_months (sysdate,1)) from dual;

-------------------------------------------------------------------------------------

Double lead:
1) Set the alias for the column

Single citation:
1) string Constants single citation

Hands-on exercises:

Oraclesql Today, delete the answer, and practice it once again.