Home > Database Administration, Querying, SQL Server 2008R2 > Basic Querying in SQL Server

Basic Querying in SQL Server

Today we’re going to talk about querying a database in SQL Server.  Queries can be really simple or nearly infinitely complex.  But that complexity can be deceiving, as it all breaks down to multiple combinations of the simple aspects of querying.  Today, we’ll discuss selecting items from a database and limiting those results to get results the way we want them.  You should have SQL Server 2008R2 and the AdventureWorks2008R2 database installed on your system.  If you don’t already, see the “Software”  link above.

To begin with, lets open a new query window.  In SQL Server, click “New Query” from the toolbar on the top-left of the screen.  This will open the query editor window, in which you input queries and execute by pressing “F5”.  Upon executing the first query, the query’s Results Pane will open on the bottom half of the screen.

To begin with, lets ask the database to give us the current date.  This is done with the GETDATE() system command.  Step one of any query is the SELECT  statement.  Input the following code in a query window:

SELECT GETDATE()

Press F5 to execute, and the database will return the date/time.  Excellent!

Now, let’s actually query the database.  Step one is to tell the query executor which database to use.  This is done with the USE <database> command.  In the future, as we build more complex queries, querying multiple databases will become possible.  But, hey, we’re getting ahead of ourselves.

Go ahead and execute the following query:

USE AdventureWorks2008R2 (or your database name)
GO

If you saw “Command completed successfully”, you win.  On the top left of the screen “AdventureWorks2008R2” should also be visible in the drop-down box.  Using said drop box is an alternate way to select a database to use.

Now for querying.  Every query consists of three basic parts.  It can be remembered easiest by thinking “SELECT (these columns) FROM (this database) WHERE (these conditions exist)”.

SELECT statement

The SELECT statement specifies what columns you want returned.  You can specify column names, or use * (asterisk) to specify all columns, i.e. SELECT * FROM database.  That will return all rows from said database.

Another option is aliases for column names.  This can be handy if your address column is titled “Addr” and you want the output header to be “Address”.  This is done by using “AS ‘column alias’ “.  Note the apostrophe around the alias.  AS is an optional identifier.  “SELECT Addr AS ‘Address’ ” is the same as “SELECT Addr ‘Address’ “.  The database should be specified in the SELECT statement as well, i.e. “SELECT person.FirstName”

FROM statement

The FROM statement will tell the query what database to pull the information from.  Aliases are available here too, with the same rules as the SELECT aliases.  Databases are typically given one or two letter aliases for ease of typing.  For example, you could run the query “SELECT p.FirstName FROM Person p”, as opposed to having to type the full “Person” with each column in the SELECT statement.

WHERE statement

By using an optional WHERE statement, you can limit the data by mathematical or string properties.  For example, you can use “WHERE ZipCode = 57104”, “WHERE Age > 18”, or “WHERE Birthdate IS NOT NULL”.

Sub-queries can be included in WHERE statements.  This is where you make one of the WHERE clauses dependent on another SELECT statement, for example “WHERE Age > (SELECT MAX(AGE) FROM Person)”.  You’ll notice I used an aggregate function there in the sub-query, and we’ll get into aggregates and other functions later on down the road.

Bringing it all together

Try bringing all these statements together to give you results.  Try following the instructions for queries and see what else you can make the SQL Server do!

Select all single female employees from the HumanResources.Employee table.

List all employees with more than 40 vacation hours from the HumanResources.Employee table.

Using the Sales schema area, find the territory ID for the ‘Northeast’, then use that ID to find all customer account numbers in that territory.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: