SELECT: Pick What You Want

When we’re writing a query, we first have to tell SQL Server what we want.

We’re going to start by getting data out of thin air – not even hitting the database, really, but just asking the SQL Server to do some work for us.

StackOverflow Example of SELECT

Go to the home page of StackOverflow.com:

Down the right hand side, you’ll see things like “1 min ago” and “2 mins ago.” Just like Chicago, SQL Server knows what time it is. We just have to ask.

Head over to Data.StackExchange.com and click Compose Query at the top:

This is a web front end for SQL Server. (It’s open source if you’re into that kind of thing.) You write your database query in the big white box, and then click Run Query to make the magic happen.

Start by copying or writing this into your query window, or click on my saved query:

Then click the Run Query button, and presto, the results:

When we put stuff after the SELECT statement, we’re telling SQL Server what we want. GETDATE() is a special built-in function – obviously, you’ve got better sources for time than a database server, but we had to start somewhere.

The semicolon tells SQL Server we’re done with our query. It’s not technically required in every case, but as we get fancier later with more complex queries, the semicolon will be required, so let’s go ahead and be good about always including it now.

We can ask for strings by framing them with single quotes:

Try that in your own query window, and then try asking for numbers and basic math:

Well, most of those will work – but the last one won’t. You’ll get an error because SQL Server can’t add a string to a number. SQL Server takes a guess at what it can combine, but sometimes it’s not sure if you want a number or a letter as a result.

Instead, let’s change that last one a little:

Run that and you get:

That would come in handy if we needed to get someone’s full name as a single column, but they were stored separately as first and last names.

Notice that right now, the header is blank – we’re not getting names for our columns. We have to tell SQL Server what to call the column, like this:

Run that and you get:

Notice how now we’ve got a header called FullName – that’s useful.

The data coming back is a little less useful – we kinda want a space in there between fields. We would have to pad a space in there ourselves, but let’s step back for a second – SQL Server isn’t really a presentation layer. In a perfect world, you want SQL Server to just give you the raw data, and then you do presentation work like HTML construction on the app side, not in the database.

See, the database is really expensive – typical SQL Server licensing is around $25k-$50k USD for Enterprise Edition. We want to avoid making that server do any more work than it has to.

So we can get each of our names back separately. We get multiple fields by putting a comma between them:

Run that and you get:

You can get as many columns back as you want. (Of course, we’re not getting it from database tables yet, but hold your horses.)

Now, you try.

Write a query that returns your FirstName, LastName, City, and Age as separate columns.

When you’re done with that, move on to the next lesson.