Beginner SQL section: avoiding repeated expression

Question

I'm entirely new at SQL, but let's say that on the StackExchange Data Explorer, I just want to list the top 15 users by reputation, and I wrote something like this:

SELECT TOP 15
  DisplayName, Id, Reputation, Reputation/1000 As RepInK
FROM
  Users
WHERE
  RepInK > 10
ORDER BY Reputation DESC

Currently this gives an Error: Invalid column name 'RepInK', which makes sense, I think, because RepInK is not a column in Users. I can easily fix this by saying WHERE Reputation/1000 > 10, essentially repeating the formula.

So the questions are:

• Can I actually use the RepInK "column" in the WHERE clause?
  • Do I perhaps need to create a virtual table/view with this column, and then do a SELECT/WHERE query on it?
• Can I name an expression, e.g. Reputation/1000, so I only have to repeat the names in a few places instead of the formula?
  • What do you call this? A substitution macro? A function? A stored procedure?
• Is there an SQL quicksheet, glossary of terms, language specification, anything I can use to quickly pick up the syntax and semantics of the language?
  • I understand that there are different "flavors"?

Answer 1

Can I actually use the RepInK "column" in the WHERE clause?

No, but you can rest assured that your database will evaluate (Reputation / 1000) once, even if you use it both in the SELECT fields and within the WHERE clause.

Do I perhaps need to create a virtual table/view with this column, and then do a SELECT/WHERE query on it?

Yes, a view is one option to simplify complex queries.

Can I name an expression, e.g. Reputation/1000, so I only have to repeat the names in a few places instead of the formula?

You could create a user defined function which you can call something like convertToK, which would receive the rep value as an argument and returns that argument divided by 1000. However it is often not practical for a trivial case like the one in your example.

Is there an SQL quicksheet, glossary of terms, language specification, anything I can use to quickly pick up the syntax and semantics of the language?

I suggest practice. You may want to start following the mysql tag on Stack Overflow, where many beginner questions are asked every day. Download MySQL, and when you think there's a question within your reach, try to go for the solution. I think this will help you pick up speed, as well as awareness of the languages features. There's no need to post the answer at first, because there are some pretty fast guns on the topic over here, but with some practice I'm sure you'll be able to bring home some points :)

I understand that there are different "flavors"?

The flavors are actually extensions to ANSI SQL. Database vendors usually augment the SQL language with extensions such as Transact-SQL and PL/SQL.

Answer 2

You could simply re-write the WHERE clause

where reputation > 10000

This won't always be convenient. As an alternativly, you can use an inline view:

SELECT
  a.DisplayName, a.Id, a.Reputation, a.RepInK 
FROM 
   (
        SELECT  TOP 15  
          DisplayName, Id, Reputation, Reputation/1000 As RepInK 
        FROM 
          Users 
        ORDER BY Reputation DESC 
    ) a
WHERE 
  a.RepInK > 10