Do you ever compare your work slinging SQL as a database administrator to shifting gears with a manual transmission? In a world conquered long ago by the graphical user interface (GUI), SQL is one of the few remaining ways for a database administrator to row a shifter through the compute-gears and get real work done.

As a DBA, you can be proud of that connection. Consider this, for example.

Shifting gears and writing SQL Server queries

Suppose you’re away at college and you need to find a job. So you can eat and stuff. You see an ad for a sandwich maker at a deli in town, so you decide to skip class and apply. You walk to the shop and stand in line on the sidewalk behind the 19 other people who skipped class to apply. Applicants keep going in jobless and coming out jobless.

“Must be a tough interview,” you tell yourself.

Finally, it’s your turn to go in. The owner of the sandwich shop has one priority.

“Can you drive a manual transmission?” she asks.

You weren’t expecting that, were you? But you’ve got the right answer at the right time.

“Sure. I learned on a ’99 Ford Explorer with five on the floor and a splashy clutch. Didn’t everybody?”

“No,” the owner says. “You’ll need to run deliveries for us, and our truck has a stick. One more question: What kind of meat goes in a turkey sandwich?”

You hem and haw for a minute — being a vegetarian — then take a wild guess that proves correct.

“You’ve got the job,” the owner says. “When can you start?”

It may have been touch-and-go with the meat question, but your manual-gearbox pedigree served you well at the deli and continues to serve you well as a hard-core, stick-shifting DBA. Not for you the flicking of a brain-dead PRNDL that doesn’t care whether your hand is on it or not.

It’s gratifying to you to be able to pop open a SQL Server Management Studio editor and hammer in a few T-SQL commands. In no time, you can generate a big list of valuable information pulled out of the bowels of a SQL Server database somewhere.

Glossary of SQL Server queries

And, as a self-respecting DBA, you probably have a handful of favorite SQL Server queries you keep in your back pocket. Think of them as a glossary of SQL Server queries that you pull out and draw from whenever somebody needs specific data in a hurry.

Combining the results of two SQL Server queries into one

A data analyst comes bounding into your office, hair on fire, and begs you for a list of customers, their email addresses and their recent orders for an online promotion the company is running tomorrow.

“Not to worry,” you reply calmly. “It will be in your inbox by the time you get back to your desk.”

You open a new window in your query editor and type a couple of quick statements:

You save the new table, email it to Hair-on-Fire and get back to work.

Using wildcards

The LIKE keyword, if used carefully with wildcards, is a formidable member of your glossary. Plus, it can give you so much results-bang for so little syntax-buck. With just 47 characters of SQL, you can list and examine all of the rows in a Customers table for customers whose surname begins with “Rob”, as shown here:

Your resulting list will include Robbins, Roberts, Robertson, Robinson and so forth.

Swapping values between columns in a table

Did you ever import a 100,000-record database, then realize that you had mapped fields incorrectly? Of course not. But data happens, and one of your co-workers is bound to make that mistake sooner or later.

Imagine that the fields for postal code and phone number became transposed during your import operation. Instead of scrapping the table and re-running the import, you could use UPDATE and SET to fix the problem:

Pulling columns from a table

Of course, you need a couple of SQL Server queries in your back pocket that you can whip out for the complete newbies.

“How do you do what you do?” they ask, their voice aquiver in admiration.

“Try starting with this,” you say airily, and you show them just about the most widely used SQL Server query ever:

Indeed, they try it in their own query editor and extract the surnames of all the company’s customers.

For some newbies, that may suffice. Others — the whippersnappers — may smirk and say, “Is that all you got?” They need to know with whom they’re dealing, so you add a wildcard:

You then return to your desk, having carpet-bombed them with all the attributes in the table and left them to figure out what to do with so much data.

Outputting sorted data in order

Data rarely goes into a database in a useful order, but most users need to see it listed in either alphabetical or numerical order. With this SQL Server query:

You’ll get a list of employee ID numbers and surnames in Tulsa in the order in which they occur in the table. For a list sorted numerically by ID number, run this SQL Server query:

SELECT Emp_ID, Lastname FROM Employee

WHERE City = 'Tulsa' ORDER BY Emp_ID;

If, for some reason, you want to list them in reverse numerical order, add desc:

Outputting sorted data in groups

Besides sorting, grouping is a powerful way to manipulate data. To list patient names and ages, grouped by years of age and sorted by name within those groups, structure your SQL Server query like this:

Sorting and grouping make life much easier for your data analysts. And the SQL Server queries behind those operations are relatively simple.

Don’t give up your stick shift

Those are simple SQL Server query examples to keep in your own glossary. Many DBAs can write their own favorite queries in their sleep, they use them so frequently.

Of course, stick shifts are almost an endangered species. It’s getting harder to find them (and harder to find deli employees who know how to drive them). In a world of disappearing manual transmissions, it’s important for you to hold on to the one you have in your knowledge of SQL queries. Both require skill and not a little cunning, and they set you apart from people who can’t be bothered with that whole Clutch out – Shift – Clutch in –  Gas thing.

Don’t give your stick shift and SQL Server queries up until you have to.