When doing reporting or statistics with SQL, you better know your window functions. There are many of them, and few SQL developers know about them. CUME_DIST() is one such function. We've recently re-discovered it on Stack Overflow. The following query yields two times the same result for fraction1 and fraction2: SELECT ename, CUME_DIST() OVER (ORDER … Continue reading CUME_DIST(), a Lesser-Known SQL Gem
Category: sql
This category contains posts about SQL in general or some RDBMS’s SQL dialect
Java 8 Friday: Java 8 Will Revolutionize Database Access
At Data Geekery, we love Java. And as we're really into jOOQ's fluent API and query DSL, we're absolutely thrilled about what Java 8 will bring to our ecosystem. For our Java 8 series, we're honoured to host a very relevant guest post by Dr. Ming-Yee Iu. Dr. Ming-Yee Iu completed a PhD on Database … Continue reading Java 8 Friday: Java 8 Will Revolutionize Database Access
A Better Query Language than SQL
Leland Richardson, Founder of Tech.Pro has recently published a very interesting article about BQL, his visions of a better query language (than SQL). The deciding feat of his new language proposal is the fact that it is really a superset of SQL itself. SQL is a very rich and expressive language to query relational databases. … Continue reading A Better Query Language than SQL
A SQL query DSL for Scala by ScalikeJDBC
There are a tremendous amount of SQL APIs natively written in Scala. Manuel Bernhardt has summarised a nice collection in his a post. Another collection of Scala SQL APIs can be seen in this Stack Overflow question. One API that we want to focus on in particular is ScalikeJDBC (licensed ASL 2.0), which has recently … Continue reading A SQL query DSL for Scala by ScalikeJDBC
The JDBC Boolean Compatibility List
Interestingly, boolean types have been introduced only late in the SQL standard, namely in SQL:1999. Even today, not all databases natively support BOOLEAN or BIT types. Most importantly, we can still wait for them in Oracle for a while. Here's "Ask Tom"'s point of view from 2002 on the subject: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6263249199595 Oracle. Why U No … Continue reading The JDBC Boolean Compatibility List
An MS Access SQL Transformation Odyssey
Recently, we've added support for the MS Access database in jOOQ 3.3. This may well have been our hardest integration so far. The MS Access database has its ways, and many of them, too. But luckily, jOOQ's internal SQL transformation abilities are already very advanced, as we've shown previously in a blog post about the … Continue reading An MS Access SQL Transformation Odyssey
No CROSS JOIN in MS Access
For the upcoming jOOQ 3.3, we're now integrating support for the MS Access database through the JDBC-ODBC bridge, which is included in the JDK up until Java SE 7. Note that it will be removed in Java 8! Alternative access to access databases (pun intended) can be obtained through a hack involving ucanaccess, which is basically combining the … Continue reading No CROSS JOIN in MS Access
You’re Very Likely to Have Gotten SQL Date Time Arithmetic Wrong!
You're very likely to have gotten SQL date time arithmetic wrong. And why is that? Google it! You'll quickly find blog posts like these: Date / Time Arithmetic with Oracle 9 / 10 How does one add a day/hour/minute/second to a date value? Orace Date Arithmetic Tips And they're all advocating stuff like this: SYSDATE … Continue reading You’re Very Likely to Have Gotten SQL Date Time Arithmetic Wrong!
JDBC 4.0’s Lesser-known Clob.free() and Blob.free() Methods
When I talk about jOOQ at conferences, I always show this slide containing a bunch of very common JDBC mistakes that people often commit: Can you find the bugs? Some of them are obvious, such as: Line 4: Syntax errors resulting from bad concatenation on line 3 Line 7: Syntax errors and SQL injection risk … Continue reading JDBC 4.0’s Lesser-known Clob.free() and Blob.free() Methods
Introducing CQLC – a Query DSL for Cassandra’s CQL in Go, Inspired by jOOQ
This morning, we've had very nice feedback about our work on the jOOQ User Group by Ben Hood, who has been a long-time jOOQ user and ideas/bug report contributor. He has taken inspiration from our software to build CQLC, a fluent API / query DSL for Cassandra's CQL written in Go. Citing Ben: I think … Continue reading Introducing CQLC – a Query DSL for Cassandra’s CQL in Go, Inspired by jOOQ