oracle sql analytic functions

modify information, and DML uses statements such as INSERT, select, Update, and delete to manipulate the data contained by database objects. (1). Create a table with an existing table Syntax: CREATE TABLE Select Column_names from eg 1), CREATE TABLE tb_dept as SELECT * from dept; 2), CREATE table tb_dept as select A.deptno, A.dname from dept A; 3), CREATE TABLE tb_dept as SELECT * FROM dept a where a.deptno=10; (2), select a row without duplicates, use the DISTINCT keyword eg, select distinc

; Parameter description: 1.substr (str, intercept start position, len) //Returns the intercepted Word, right (Str,len) returns the Len string starting from the rightmost 2.to_char (sysdate, ' YYYYMMDD ') //Return to current month day 3.NVL () // NVL (STR1,STR2) If the first parameter of Oracle is empty then the value of the second parameter is displayed, and if the value of the first parameter is not NULL, the first parameter is displayed. Summari

(SAL),Count (*), COUNT (Job), COUNT (Distinct (job)),Sum (SAL) from the EMP group by DEPTNO;2) Department 30 maximum wage, minimum wage, total number, number of jobs, number of jobs and sum of wagesSelect Deptno, Max (ename), Max (SAL),Min (ename), Min (Sal),AVG (SAL),Count (*), COUNT (Job), COUNT (Distinct (job)),Sum (SAL) from the EMP group by DEPTNO have deptno=30;3.stddev returns the standard deviation of a set of valuesSelect Deptno,stddev (SAL) from the EMP group by DEPTNO;Variance return

: INSERT into Tb_stu (ID, Birthday) VALUES (1, to_date (' 20111130 ', ' YYYYMMDD '));6, clear screen: clear screens;7. Two single quotes in Oracle represent a single quotation markeg, update tb_stu set pwd= ' 1 ' 2 ' where id=1;8. Oracle MatchEg, select * from Tb_stu where pwd like ' A% '--matches multipleSELECT * from Tb_stu where pwd like ' a_ '--matches a9, drop table tb_dept purge; --Permanently deleted

Label:Use SQL statements to export stored procedures and functions in Oracle: SETEchoif ; SETHeadingoff ; SETFeedbackoff ; SPOOL'C:/prc.sql' Replace SELECT Case whenLine= 1 Then 'CREATE OR REPLACE' || TEXT whenLine=Max_line Then TEXT ||CHR (Ten)|| '/' ELSE TEXT END fromUser_source A Left JOIN(

the database column name as key, and the record value is returned as value. This time, however, you need to use Resultmap, which allows you to freely combine the return value of the form to handle more complex queries. Or the first code: Sql: MyBatis Configuration: Blog entity class public class Bolg { private Integer Authorid; Private String AuthorName; Private list Post entity classes public class

numberS:select rand () valueO:select Sys.dbms_random.value (0,1) value from dual;13. Take the symbolS:select sign ( -8) value-1O:select sign ( -8) value from Dual-1======= Mathematical Function =======14. PiS:select PI () value 3.1415926535897931O: I don't know15.sin,cos,tan parameters are measured in radiansExample: Select Sin (PI ()/2) value gets 1 (SQL Server)16.asin,acos,atan,atan2 return radians17. Radian Angle Interchange (sqlserver,

Tags: Enter use statement function style to establish a return return statement specificThe tables used in the following test cases are derived from the Scott scenario, before use, make sure that the user is unlocked1. Introductionfunction is used to return a specific data, when a function is established, the function header must contain a return clause, and the function body must contain the data returned by the return statement , we can use the CREATE function to establish the

--Create a functionCreate or Replace function Add_sal (ssal number) return numberIsBegin if (Ssal >) Then return ssal + 51; elsif (Ssal >) Then return ssal + 111; Else return ssal + 222; End If;EndSelect Sal, Add_sal (SAL) from EMP;--TriggerCREATE TABLE Deptlog ( UName varchar2 (20), Action Varchar2 (20), DTime Date);--Create a trigger--for each row can trigger multiple entries, and how many times your language affects how many records will be triggeredCreate

Single-record functions in SQL1. ASCIIReturns the decimal number corresponding to the specified character;SQL> select ascii ('A') A, ascii ('A') A, ascii ('0') zero, ascii ('') space from dual;A ZERO SPACE------------------------------------65 97 48 322. CHRReturns the corresponding characters;SQL> select chr (54740) zhao, chr (65) chr65 from dual;ZH C---Zhao3. C

. Enter and execute the query: SQL code SELECTename, round (sysdate-hiredate) days fromEmp WHEREDeptno = 10; SELECT ename, round (sysdate-hiredate) days from emp WHERE deptno = 10; The result returned is: Ename days --------------- --------------------- CLARK 7913 KING 7752 MILLER 7685 Note: This query uses the subtraction of dates to find the number of days between two dates. The number of days is rounded with the round function. Conversio

Oracle date functions/character functions/numeric functions/conversion functions/Aggregate functions Oracle date functions: [