Home » Tag Archives: SQL

Tag Archives: SQL

Fixed-Point and Floating-Point: Two Things That Don’t Go Well Together

software-development-2-logo

One of the more challenging aspects of software development can be dealing with floating-point numbers. David Goldberg‘s 1991 Computing Surveys paper What Every Computer Scientist Should Know About Floating-Point Arithmetic is a recognized classic treatise on this subject. This paper not only provides an in-depth look at how floating-point arithmetic is implemented in most programming languages and computer systems, but ...

Read More »

Apache Drill SQL Queries on Parquet Data

software-development-2-logo

In this week’s Whiteboard Walkthrough Parth Chandra, Chair of PMC for Apache Drill project and member of MapR engineering team, describes how the Apache Drill SQL query engine reads data in Parquet format and some of the best practices to get maximum performance from Parquet. Additional Apache Drill resources: ”Overview Apache Drill’s Query Execution Capabilities” Whiteboard Walkthrough video “SQL Query ...

Read More »

How to remove duplicate rows from a table in SQL

software-development-2-logo

There are a couple of ways to remove duplicate rows from a table in SQL e.g. you can use a temp tables or a window function like row_number() to generate artificial ranking and remove the duplicates. By using a temp table, you can first copy all unique records into a temp table and then delete all data from the original table ...

Read More »

SQL Query on Mixed Schema Data Using Apache Drill

software-development-2-logo

You may have heard this statement before:      Apache Drill does schema discovery on-the-fly. What does that mean, and why should it matter to you? The power of SQL for business analytics is a given, but the challenge in big data settings is that SQL is normally a static language that assumes pre-defined, fixed and well-known schema. SQL also needs flat ...

Read More »

SQL for Application Developers

software-development-2-logo

“SQL? Isn’t that just something my ORM tools use?” Often application developers can and should remain at least one level removed from SQL. JPA and the Entity Framework each provide more abstract (and OO-friendly) query languages, and it’s often unnecessary to write any queries at all. If you ever diagnose production support issues, though, being proficient with SQL can greatly ...

Read More »

Correlated Subqueries are Evil and Slow. Or are They?

software-development-2-logo

A common myth in SQL is the idea that correlated subqueries are evil and slow. For example, this query here: SELECT first_name, last_name, (SELECT count(*) FROM film_actor fa WHERE fa.actor_id = a.actor_id) FROM actor a It “forces” the database engine to run a nested loop of the form (in pseudo code): for (Actor a : actor) { output( a.first_name, a.last_name, ...

Read More »

NetBeans SQL Profiler – Take it for a Spin!

netbeans-logo

The upcoming release of NetBeans, currently in active development and testing, boasts plenty of new features.  A few of them include EMCAScript 6 Support, Docker Support, JavaShell Support, and SQL Profiling.  In the latest nightly builds, the SQL Profiler is now available for use, so I took it for a test spin.  In this post, I’ll run through just a ...

Read More »

10 SQL Tricks That You Didn’t Think Were Possible

software-development-2-logo

Listicles like these do work – not only do they attract attention, if the content is also valuable (and in this case it is, trust me), the article format can be extremely entertaining. This article will bring you 10 SQL tricks that many of you might not have thought were possible. The article is a summary of my new, extremely ...

Read More »

Time for some Funky SQL: Prefix Sum Calculation

software-development-2-logo

This Stack Overflow question has yet again nerd-sniped me: [finding the] maximum element in the array that would result from performing all M operations Here’s the question by John that was looking for a Java solution: With an array of N elements which are initialized to 0. we are given a sequence of M operations of the sort (p; q; ...

Read More »

Want to take your Java skills to the next level?

Grab our programming books for FREE!

Here are some of the eBooks you will get:

  • Spring Interview QnA
  • Multithreading & Concurrency QnA
  • JPA Minibook
  • JVM Troubleshooting Guide
  • Advanced Java
  • Java Interview QnA
  • Java Design Patterns