SQL Server basics

Useful Settings Changes in Management Studio

To make our query writing experience easier we first adjust some of the settings in SQL Server Management Studio. 

Options  dialogue.

Options dialogue.

Open the Options dialogue by clicking on Tools from the menu bar and selecting Options.  Add line numbers which appear at the left side of the query window.  It helps to identify where errors occur your query.  Expand Text Editor from the list of options then choose All Languages, check the Line numbers checkbox.  Then click OK to save the changes. 

Add Comments to Queries

To begin writing queries there are two ways.  Click on New Query button on the top menu bar.  Alternatively you can hold down the Ctrl+N keys on your keyboard.  Both will start a new query window as shown below.

New Query window showing how to create single and multiple line comments

New Query window showing how to create single and multiple line comments

Add two dashes -- to create a single line comment.  Multiple lines of comments can also be written once they fall between the following opening and closing comment blocks: /*  comment */

Creating our Sample Database

Using SQL Server Management Studio, open the sample database script you downloaded from the Installation Guide.  You can do this by going to File in the menu bar then Open then click on File... Alternatively you can hold down the Ctrl+O on your keyboard.  Navigate to where you saved the downloaded sample database SQL file (SQLTW_Sample_DB) and click on Open in the Open File dialogue.

Open the SQLTW_Sample_DB script in SQL Server Management Studio

Open the SQLTW_Sample_DB script in SQL Server Management Studio

After the script opens click on Execute in the menu bar to run the script and create our SQL Server sample database.

Click on  Execute  to run the script and create the SQLTW_Sample_DB. 

Click on Execute to run the script and create the SQLTW_Sample_DB. 

Your  Results Pane  will display the messages above after the script runs successfully.

Your Results Pane will display the messages above after the script runs successfully.

To view the newly created sample database navigate to the Object Explorer and expand the Databases folder.  In the Databases folder expand the SQLTW_Sample_DB database.  In the SQLTW_Sample_DB expand the Tables folder.  Here you can observe all of the Tables that we will be working on in the database for the duration of this tutorial.

The  Object Explore r shows the Tables in our SQLTW_Sample_DB that we will be working with.

The Object Explorer shows the Tables in our SQLTW_Sample_DB that we will be working with.

The Structure of a Basic Query

To begin writing our first query open a new query window (Ctrl +N).  

Enter the following comment block:
"Created by Your Name"
"Show a list of people"

Associate your query with the SQLTW_Sample_DB

Associating your query with the database you will be working in takes advantage of Intellisense, which is an Auto Complete feature within SQL Server.  Intellisense saves you from typing out every character as you compose your query.  Associate your query with the SQLTW_Sample_DB  in one of two ways:  

  1. Select the database name from the dropdown in the menu bar.
  2. Write "USE SQLTW_Sample_DB" as the first thing in your new query window
Associate your query with the SQLTW_Sample_DB

Associate your query with the SQLTW_Sample_DB

Start writing your Query

SELECT
    name,
    residence,
    age
 
FROM person

Start by typing FROM person.  This tells your query what table to look in for it's data.  After composing this SQL query, run the statement by clicking on the Execute button in the menu bar.  The results of your query is displayed in the Results pane below.

Execute your query and view its results in the Results pane

Execute your query and view its results in the Results pane

Next ABOVE where you typed your FROM clause, type SELECT name, residence, age.  You will end up with a SQL statement composed like the one shown at the beginning of this section.

Add an alias to your fields.  You return an alias for your column names by adding the AS keyword in front of the original column name specified in your query followed by the alias name:

SELECT
    name AS FirstName,
    residence AS [Home City],
    age
 
FROM person

As seen above the AS keyword has aliased the name column as FirstName.  When an alias has a space, it is surrounded by square brackets [ ] as shown.

1.) Return a list of residences only from the person table.  Create an alias for the residence column called "First City Lived".

2.) Return a list of account numbers from the account table?  Alias this column as "Bank Account Numbers".