Analytical SQL - Running Total - Order by without Partition By
Let's Develop in Oracle
by
2y ago
Welcome to the third post on the series Analytical SQL. In the first two posts we studied  Analytical Function without Window Clause  Analytical Function Partition By In this post we will create a report showing a running total of the complete EMP table in order of HIREDATE. To create a running total for the complete dataset, PARTITION BY is not required, we just need to add ORDER BY in OVER analytic clause SQL> select d.deptno, d.dname, empno, ename, hiredate, sal, 2 sum(sal) over(order by hiredate) running_total 3 from emp e, dept d 4 where e.deptno = d.deptno; DE ..read more
Visit website
Analytical SQL - SUM with Partition By
Let's Develop in Oracle
by
2y ago
Welcome to the second post on the series Analytical SQL.  In the first post we studied Analytical Function without Window Clause. With this post, we will learn Partition By.  Analytical functions compute an aggregated value based on a group of rows defined as per Partition By clause, which determines the range of rows used to perform the calculations.  In last post, we calculated TOTAL SAL in EMP table against each row, using select d.deptno, d.dname, empno, ename, sum(sal) over() totalfrom emp e, dept dwhere e.deptno = d.deptno; Now, I want to modify TOTAL SAL to DEPARTMENT ..read more
Visit website
Analytical SQL - SUM without Window Clause
Let's Develop in Oracle
by
2y ago
This is my first post in 2022, and with this I am starting a new series on Analytical SQL.  In The first example, we are trying to list all records from EMP and DEPT tables with the SUM of SAL column with each row. As you can see here, we have used "sum(sal) over()" without WINDOW ( partition by), which made Oracle to process all rows in table. select d.deptno, d.dname, empno, ename, sum(sal) over() totalfrom emp e, dept dwhere e.deptno = d.deptno; DEPTNO DNAME EMPNO ENAME TOTAL---------- -------------- ---------- ---------- ---------- 10 ACCOUNTING ..read more
Visit website
Oracle Database 21c for on-premises is here - New Features
Let's Develop in Oracle
by
2y ago
Oracle Database 21c is the latest innovation release of world's most popular database. Oracle Database 21c is now available on Oracle cloud and on-premises for linux including Exadata.  You can directly upgrade to Oracle Database 21c from the following releases: - Oracle Database 19c - Oracle Database 18c - Oracle Database 12c Release 2 (12.2) Top New features in Oracle Database 21c: - Blockchain Tables - Immutable Tables - Oracle Machine Learning - Native JSON Datatype - SQL Macros - In database JavaScript execution - Compare explain plans / cursors We all should be aware that Inno ..read more
Visit website
Oracle: String to Date, Timestamp with Timezone
Let's Develop in Oracle
by
3y ago
I recently got a text from a friend to convert '2021-04-07T14:03:54.000000-0700' string to date. He Tried - SQL> select 2 to_date('2021-04-07T14:03:54.000000-0700','YYYY-MM-DDHH:MI:SS.FFTZH-TZM') 3 from dual;to_date('2021-04-07T14:03:54.000000-0700','YYYY-MM-DDHH:MI:SS.FFTZH-TZM') *ERROR at line 2:ORA-01821: date format not recognized but it was failing with ORA-01821: date format not recognized. There are 2 issues in above SQL.  1. The format in the SQL is itself incorrect. The correct format is 'YYYY-MM-DD"T"HH24:MI:SS.FFTZHTZM'. SQ ..read more
Visit website
Star Pattern by SQL in Oracle Database
Let's Develop in Oracle
by
3y ago
Star Patterns by SQL in Oracle - nimish@garg> select lpad('*',level,'*') from dual connect by level <= 5;LPAD('*',LEVEL,'*')---------------------------------------------------------------------------------***************nimish@garg> select lpad('*',5-level+1,'*') from dual connect by level <= 5;LPAD('*',5-LEVEL+1,'*')---------------------------------------------------------------------------------***************nimish@garg> select lpad(' ',5-level,' ') || lpad('*',level,'*') from dual connect by level <= 5;LPAD('',5-LEVEL,'')||LPAD('*',LEVEL ..read more
Visit website
LISTAGG DISTINCT - Remove Duplicates - Oracle 19c
Let's Develop in Oracle
by
3y ago
LISTAGG function was introduced in Oracle 11g. LISTAGG function aggregates the result set in multiple rows into one single column.  In Oracle 12c R2 LISTAGG function was enhanced to manage situations where the length of the concatenated string is too long by providing us ON OVERFLOW TRUNCATE clause.  With Oracle 19c Database LISTAGG function can also remove the duplicate values by using DISTINCT keyword. This feature was requested by many developers from the inception of LISTAGG function, and now we do not need to write complex SQL to remove the duplicates from the list. Following ..read more
Visit website
Redo Files, Redo Group and Redo Thread in Oracle Database
Let's Develop in Oracle
by
3y ago
Redo log files used to maintain logs of all transactions performed against the Oracle database mainly for disaster recovery. An Oracle database must have at least two redo log files. These files are written in a circular fashion by the LGWR process.  If Oracle database is running in archivelog mode, then ARCH process copies the log file to ARCHIVE_LOG_DEST directory at the time of LOG SWITCH.  Following is the SQL which provides basic information about Redo Log files on my Oracle 18c XE database. select log.thread#, log.group#, file.member, log.archived, log.status ..read more
Visit website
Oracle Autonomous Database scored highest in Gartner "Critical Capabilities for Cloud Database Management Systems for Operational Use Cases" report
Let's Develop in Oracle
by
3y ago
Oracle is named as a Leader in 2020 Gartner Magic Quadrant for Cloud Database Management Systems. Oracle Autonomous Transaction Processing (ATP) ranked highest in 2020 Gartner "Critical Capabilities for Cloud Database Management Systems for Operational Use Cases" report. Gartner "Critical Capabilities for Cloud Database Management Systems for Operational Use Cases" evaluates cloud DBMS products for their suitability to support following four operational use cases -  1) traditional transactions  2) augmented transaction processing  3) operational intelligence  4) stream/even ..read more
Visit website
Oracle Database 21c is here - Innovation Release
Let's Develop in Oracle
by
3y ago
Oracle Database 21c is the latest innovation release of world's most popular database. Oracle Database 21c is initially available on Oracle cloud with Autonomous Database Free Tier and Database Cloud Service. You can directly upgrade to Oracle Database 21c from the following releases: - Oracle Database 19c - Oracle Database 18c - Oracle Database 12c Release 2 (12.2) Oracle Database Innovation Releases include many enhancements and new capabilities which will be included in the next Long Term Release. Oracle Database Innovation Releases does not have extended support. As Oracle Database 21c is ..read more
Visit website

Follow Let's Develop in Oracle on FeedSpot

Continue with Google
Continue with Apple
OR