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:

SELECT a.Title, t.Name
FROM dbo.Album a INNER JOIN dbo.Track t
   ON a.AlbumId = t.AlbumId

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:

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:

“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:

SELECT dbo_album.[Title] AS [Title],
   l__8689.[Name] AS [ArtistId],
   dbo_track.[Name] AS [Name],
   l__8694.[Name] AS [MediaTypeId],
   l__8695.[Name] AS [GenreId]
FROM ((([dbo].[Album] AS dbo_album
   INNER JOIN [dbo].[Track] AS dbo_track
      ON dbo_album.[AlbumId] = dbo_track.[AlbumId])
   LEFT JOIN [dbo].[Artist] AS l__8689
      ON dbo_album.[ArtistId] = l__8689.[ArtistId])
   LEFT JOIN [dbo].[MediaType] AS l__8694
      ON dbo_track.[MediaTypeId] = l__8694.[MediaTypeId])
   LEFT JOIN [dbo].[Genre] AS l__8695
      ON dbo_track.[GenreId] = l__8695.[GenreId]