New SQL:2023 Standard now published

Yesterday ISO published the new SQL:2023 Standard, making that day a special day for me as this is the first version of the SQL Standard I’ve actively participated in. The new Standard comes with an entirely new Part 16 SQL/PGQ for standardizing Property Graph Queries, the JSON data type, SQL/JSON simplified syntax, and many smaller things like some new string manipulation functions, aggregate functions, simplification of literal definitions, etc.

Continue reading “New SQL:2023 Standard now published”

How to write an anonymous PL/SQL function and use it in your SQL statement

Have you ever encountered a situation when you were in the middle of writing a SQL query and thought, “if only I could write a quick PL/SQL function for this, it would make this quick and easy?” But, unfortunately, you don’t have any privileges to create any functions in the schema. Luckily, since Oracle Database 12c, there is an answer for you.

Continue reading “How to write an anonymous PL/SQL function and use it in your SQL statement”

Percentage calculations using SQL Window Functions

Sometimes you have the requirement to calculate percentages on some values of your data. There are multiple ways of doing it, of course, but often people are not aware that you do not have to calculate these percentages in the application itself or via a SQL statement that queries the same table multiple times to first calculate the denominator and then calculate the actual percentage. Instead, you can use SQL window functions to run a variety of complex calculations over different groups of data in a single pass! Window functions were introduced in the SQL:2003 standard back in 2003 and although the SQL standard calls these Window Functions, Oracle Database has them documented as Analytic Functions.

Let’s take a look.

Continue reading “Percentage calculations using SQL Window Functions”