I am writing this update from outdoors, enjoying a glorious spring day on a beautiful terrace in Rome.
This is about the basic principles of select queries. Incidentally I will be using the “AdventureWorks” database, which is a demonstration database provided by Microsoft. You can download the install file from here
Probably the largest portion of my future updates will be using functions in select queries, so it’s worth covering that now and getting this out of the way. If you are in a hurry and just want to get on with it, then you skip to the Select Query Syntax section below…
For the totally uninitiated, I will briefly discuss database architecture. For those of you who know this already or just don’t particularly care, I’d suggest to skip this paragraph and read from the next one or use the hyperlink above to jump straight to the actual syntax…
When a SQL database is installed it runs as an instance i.e. a database instance. It’s called that because you can install the SQL database again but rather than over-writing the existing instance, the installation creates a new instance. There is no limit to how many instances you can install, other than your hardware resource constraints i.e. RAM, processors, hard-drives, etc. You’d probably want to get
a very “juicy” server if you intend to run many SQL instances. Now why you’d want to do this… well the main reason is security. Each instance can be set up with its’ own set of users (including its’ own “sa” user – the “God” user J) so if you have a group of users who should not see data from another database, then this would be one way of ensuring this. Web hosting companies and “Cloud Application” providers would use this type of setup in their data centres, especially for business accounts.
So, what are “Select Queries” again? Well, exactly what the name applies – a select group of specific fields from one or more tables of a database (or even multiple databases – but more about that later).
Great, so how do I write one of the queries? Well for starters, running a query application that’s been installed on your computer would be a good start J). If you have installed SQL Server 2008 Express then the “SQL Management Studio” would be installed too. Launch that then log in to the database when requested (you might be prompted for a password… I can’t help you there – this is obviously for a valid user that has been setup to access that SQL instance) and then highlight the database you intend to work with. Finally click the [Start Query] button (or icon) and a blank window will appear with a cursor.
There are other query tools e.g. MS Query which comes on the Microsoft Office CD (but is not installed by default), MS Access has its’ own built-in query tool too, and I’m certain that there are plenty of others out there.
Many have a query builder tool that lets you drag-and-drop fields to build up your query, automatically linking tables if the fields come from multiple tables, but honestly, it’s better to write the query from scratch. That way you control the convention and you are also forced to think through your script as you write it.
In essence the query screen is simply a text editor for writing the script and then a “launch” button to run it. Some of them have some formatting bits built in too, so that “functions” and “operators” show up in pretty colours too.
Select Query syntax:
SELECT {field1}, {field2}, {field3}, {field4}, {field5},etc. FROM {Table1}
There are many conventions as to how to write queries and which convention you choose to use is a matter of personal preference.
I personally use this convention (…using the example above):
Select
{field1}
,{field2}
,{field3}
,{field4}
,{field5}
From
{Table1}
I like this convention because it keeps things neat. Anyone looking at my script can immediately tell where a field begins and ends (handy when I’m applying functions, formats and all sorts of calculations to a field).
Most queries will (and should) have a “where clause” at the end. This is to filter data not required so that the result returned is smaller and more manageable. There are a number of reasons why this is an advantage but the primary one is speed. The less the data being sent back from the query, the faster your expected result will appear. Queries returning large quantities of data over a network will slow down the entire network.
So using our example above again:
Select
{field1}
,{field2}
,{field3}
,{field4}
,{field5}
From
{Table1}
Where
{condition}
Okay, so that’s the convention, now let’s try this for real…
Select
Title
,FirstName
,LastName
,EmailAddress
,Phone
From
Person.Contact
Where
EmailPromotion=2
…And this is what the result will look like in SQL Management Studio:
You might see some script where the fields have been surrounded by square brackets e.g.
[Title], [FirstName],[LastName], etc. This is strictly speaking not necessary. The reason is that some applications don’t handle spaces in field names e.g. [First Name]. Another reason is sometimes field names are the same as some SQL functions e.g. Date, Time, Year, Day, etc. By wrapping the field with square brackets, you are specifying to SQL that this is the field name and not a function.
So let’s recap, the basic premise of any “Select Query” is: Select… From… Where…
If you never forget that then the rest will come together. Remember, that if you do forget some scripting specifics, you can always come back to this website to look it up. I do it myself…
The next post will cover SQL operators which are needed for doing calculations to fields in order to return results not immediately available in the database. I’ll also explain why you would want to do this.
