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.
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.
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.
After the script opens click on Execute in the menu bar to run the script and create our SQL Server sample database.
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 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:
- Select the database name from the dropdown in the menu bar.
- Write "USE SQLTW_Sample_DB" as the first thing in your new query window
Start writing your Query
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.
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:
name AS FirstName,
residence AS [Home City],
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".