Skip to main content

Case Study: Enough typing queries, DBAs

I always admired how most database administrators I worked with love typing both simple and complex queries with absolutely no help from a QBE (Query by Examples) graphical interface. Truth being said, the large majority of database administration tools offer a somehow poor user experience in this regard. And Query Xtractor is intended to improve this area. Here is just example.

Typing vs Design

Vic started as a DBA on SQL Server, and gradually acquired more experience on Oracle as well. But, like any DBA migrating from SQL Server to Oracle or from Oracle to SQL Server, Vic still finds sometimes hard to adapt to all the differences in tools, environment and SQL implementations, between these management systems.

Vic loves typing any query from scratch, because he feels like a pro. When the CTO suggested to take a look at Query Xtractor, he felt insulted and limited. This productivity tool doesn’t allow you to write queries that modify data! In fact, Query Xtractor doesn’t allow you to write SQL queries at all, you “design” them! To prove his point, Vic challenged his CTO to use himself Query Xtractor, to “generate” queries, while Vic will manually write the similar SQL queries.

Automated Joins

The CTO suggested to get the list of Albums with their Tracks, from their DVD Store database. Vic knew there was a one-to-many relationship between Albums and Tracks, double checked quickly on which keys, and came up with something like:

In parallel, the CTO dragged the Albums table into a new query diagram, expanded the dbo.Tracks relationship into a link to the new shape Tracks, and kept only Album.Title and Tracks.Name as selections:

demo-query-0

Lookup Fields

You’re fast, Vic“, he said, “but let’s get more information now on our result set. I’d like to see as well the Artist Name, the kind of Media and Genre of each Track. And I’m not interested in their numeric IDs, I want friendly descriptions“.

While Vic quickly started to look at all foreign keys and relationships between tables, the CTO continued on the same query and checked ArtistId, MediaTypeId and GenreId, then simply opened for each the Lookup list and selected the Name field:

demo-query-1

That’s a pretty complex query, with many joins to write down!” Vic said…

Conclusion: Stop Typing Joins!

I never have to remember and write a join with this tool! And, until you finish, I can tell you what my generated query is“, the CTO said.

And shown Vic the following long and complex generated query, with so little effort from the designer:

Leave a Reply

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close