Hearing the Oracle

Home » Featured

Category Archives: Featured

Categories

What is SQL-Injection?

June 18, 2015 2:07 PM / 3 Comments on What is SQL-Injection?

Warning: Toxic SQL

Warning: Toxic SQL!

I wasn’t especially surprised to read not long ago that Russian hackers had succeeded in stealing an estimated 1.2 billion login/password credentials for online websites. It seems to have become accepted that internet security is hopelessly more porous than originally envisioned, and that more large-scale breaches involving well known companies exist than are publicized. What piqued my curiosity, however, was a press report that the chief technique used for this record-shattering theft was SQL Injection: a technique which I had supposed to be long ago defeated.     Continue…

GGPLOT Graphics for NFL Stats

May 2, 2015 8:41 PM / Leave a comment

GGPLOT2 is a package developed for producing graphics within the R statistical tool. It utilizes a layering metaphor for gradually adding visual details to the desired output. R can support datasets with millions of rows for various aggregation and analysis operations, but it can be slow, unwieldy to code in, and has memory limitations.     Continue…

Exploring Analytic Functions : an Iterative Approach

April 23, 2015 11:24 AM / Leave a comment

Lake Malawi

Lake Malawi

Suppose some quixotic HR evangelist fresh off a stint of social aid work in Malawi is pushing the bombastically non-Libertarian idea of normalizing compensation patterns within departments as follows: employees who have high tenure seniority contrasted with low salary ‘seniority’ will receive pay adjustments. This is the sort of question that can be explored with Oracle’s analytic SQL functions.     Continue…

De-duplicating Rows

March 26, 2015 5:19 PM / Leave a comment

dedup them!

dedup these hay-bogarting heffers for me!

Often the need arises to locate and remove duplicated rows, or partially duplicated rows from a table. Queries using the DISTINCT keyword (or it’s synonym UNIQUE) will retrieve and display rows without duplicates. But actually identifying and then removing the unwanted duplicates within a table represents a different level of work, and is also likely to involve greater resource consumption.     Continue…

Unpivoting Spreadsheets into Oracle

March 4, 2015 4:01 PM / Leave a comment

phingeThe premise is as follows: You’ve got useful data lying in an Excel or Google spreadsheet which you’d like translated into relational table(s). This is likely going to involve normalizing the denormalized spreadsheet data on the way into the database; you are going to end up with (and want) more rows in your table than the corresponding quantity within the spreadsheet. The SQL tool for this is the UNPIVOT keyword.     Continue…

Set Operators in SQL

February 18, 2015 10:23 AM / 1 Comment on Set Operators in SQL

150px-Edgar_F_Codd

E.F. ‘Ted’ Codd

Relational database design has strong roots within Set Theory, as can be seen in the seminal work of E.F. Codd more than 40 years ago. Most people recall encountering Venn diagrams during their school days, a pictorial excursion into this realm. Codd codified his relational ideals into a series of well-known rules, which represent an extreme case, not taking account of the often necessary pragmatic or performance reasons for selective denormalization. But his principles are still instructive and serve as a starting point for designing business schemas.     Continue…

More About PIVOT Queries

January 11, 2015 11:19 AM / 2 Comments on More About PIVOT Queries

complex pivot query with both multiple aggregates and multiple pivots

complex pivot query with both multiple aggregate functions and multiple pivot columns

An earlier post introduced the PIVOT feature in Oracle’s 11g2 SQL, and described the motivation for pivoting. This one explores a few more complex ways to use pivot queries. We’ll look at:

         • Multiple Pivot Columns
         • Multiple Aggregate Functions

within the same SQL statement and we’ll see a few errors which commonly arise when building more complex pivot queries like these. Finally, we’ll consider: why would one have a need to UNPIVOT?     Continue…

Twisted Strings : Enforcing Password Strength

November 6, 2014 6:54 PM / Leave a comment

password TVgameIn this post, I aim to depict some practical utilizations of assorted Oracle string functions as an antidote to bland, often pointless examples given in SQL documentation. Security and reliable user authentication is ever in the news these days, and many studies confirm that most users continue to choose facile and predictable userids and/or passwords. This article summarizes recent user habits and hacker trends regarding passwords with some basic recommendations, while these two illustrate some of the most common (thus insecure) passwords.     Continue…

R with Oracle

May 4, 2014 12:10 PM / Leave a comment

r1The data presentation graphic at right, and many of those sprinkled throughout this article (click them for better resolutions), are highlights from an enthusiast’s gallery which have been produced by the R statistical software package. R is an open-source programming language and environment which has gained much popularity among academics who want to apply statistical methods     Continue…

NFL Nerds 2013 : Week 2

October 27, 2013 12:05 PM / 2 Comments on NFL Nerds 2013 : Week 2

inverted td catch - credit: anthony souffle / chicago tribune

inverted td catch! check out the right hand of #15.          credit: anthony souffle / chicago tribune

My imagination as to how a weekly tracking of the course of the 2013 NFL (American football) season, in graphics, analytics, and R code, might look — in my imaginary copious spare time. The data is stored in one or more simple Oracle database tables. New visualizations will be added as the season progresses.     Continue…

Post navigation

← Older posts