After creating a table and populating it with records,
you may want to see what you can do with data stored in it. One of the most
commonly performed operations by the users of a database is to look for data
or to isolate records that respond to a particular criterion.

Looking for data that is conform to a
criterion is referred to as querying. The result of retrieving
data based on a criterion is called a query.

As a database developer, you perform queries
by passing instructions to the database engine. This is done using
some specially reserved words.

In this and other lessons, we will use
a database named LambdaSquare1. The name itself means
nothing.

The database represents a fictitious
apartment complex. The building has three levels: First,
second, and third.

The apartments are referred to as
units. These are what customers, named tenants, come to
rent.

Each unit is represented with:

A unit number: Normally, this is the number on the
door of the apartment. One of the characteristics of
this number is the first digit that specifies the
floor or level. For example, if a unit has number 104,
this means the apartment is on the first level

Bedrooms: This is an integer from 0 to 3. The
number 0 means the unit is an efficiency: A one-room
apartment (the kitchen and the living room share a
unique room; there is a separate bathroom)

Bathroom: This is a nnumber that indicates the
number of bathrooms in the unit

Price: This is how much a tenant would pay every
month

Deposit: When a customer starts renting a room, he
is usually asked to put some money down, to be
optionnally reimbursed when the customer leaves

Available: This is a Boolean value that indicates
whether the unit can be rented

In the Object Explorer, right-click the Databases node and click
Refresh

Expand the Databases node

Expand LambdaSquare1 and expand its Tables node

Opening a Table for Data Selection

Microsoft SQL Server provides many ways and tools to
perform data analysis, using a Query Editor, the PowerShell, or else:

If you are using Microsoft SQL Server Management Studio:

To open a window in which you can write the necessary
statment(s):

On the main menu, click File -> New -> Query With Current
Connection

On the Standard toolbar, click the New Query button

To open a table to view its data and perform data alalysis, in
the Object Explorer, right-click the table and click Select Top
1000 Rows

To open a table and have many options, including data
analysis, in the Object Explorer, right-click the desired table
and click Select Edit Top 200 rows

To use a Command Prompt:

In the SQL Server Management Studio, to open the PowerShell,
right-click anything and click Start PowerSheel. Write the
statement(s) as we will see

The Query
Designer Window

Introduction

As one of the ways to get a window for data selection,
in the Object Explorer, right-click the desired table and click Select
Edit Top 200 rows. When you do this, the interface becomes equipped with
the Query Designer toolbar.

The Query Designer window can be made of four
sections.

Practical
Learning: Introducing the Table Window

In the Object Explorer, right-click Presentation.Units and click
Edit Top 200 Rows

The Diagram Pane

The top section is referred to as the Diagram window
or the Diagram pane. To get it:

On the main menu, click Query Designer -> Pane -> Diagram

Right-click an area of the window -> Pane -> Diagram

On the Query Designer toolbar, click the Show Diagram Pane button

The Diagram pane displays the table you had
right-clicked in the Object Explorer. In future lessons, we will see how
and when to use more than one table, in which case the Diagram pane would
show the tables you are using. A table, or each table, in the Diagram pane
contains the columns of the table(s) you want to query.

There are various ways you can manipulate a table. For
example, if you position the mouse on its title bar, a tool tip would
appear, made of TABLE:, the name of the table, and its schema between
parentheses. If you click the title bar of the table, the Properties
window would display some characteristics of the table. Here is an
example:

You can move the table window around and position it
wherever you want inside the Diagram pane. To move it, click and drag its
title bar. You can also resize a table by clicking and dragging one of its
borders.

Each column displays a check box on its left and the
name of the column on the right. The first item of the list has a name
made of an asterisk and the (All Columns) expression. If you click a field
in the table, the Properties window would display its characteristics:

Obviously, the characteristics shown by the Properties
window depend on the type of column. If the list of fields is too long in
a table for the allocated rectangle to display, the table would be
equipped with a vertical scroll bar.

Data selection consists of selecting one or more
columns whose data you want to view. To select a column in the Diagram
pane, click the check box on the left side of the name:

Practical
Learning: Introducing the Diagram pane

On the main menu, click Query Designer, position the mouse on
Pane, and click Diagram

Click the check boxes of all fields to remove the check marks

The Criteria Pane

Under the Diagram pane, you can use the Criteria pane.
To get it:

On the main menu, click Query Designer -> Pane -> Criteria

Right-click an area of the window -> Pane -> Criteria

On the Query Designer toolbar, click the Show Criteria Pane button

The Criteria pane displays a list of columns used to
visually build the SQL statement

The Criteria pane is made of columns and rows. The
most left column is made of gray boxes. The columns are divided by
vertical bars. This allows you to resize them. To resize a column,
position the mouse between two column header:

Then click, hold the mouse down, and drag in the
direction of your choice.

The Criteria section can also be used for data
selection, for which you select columns. To select a column, click a box
under the Column header. It would become a combo box. Then click the arrow
of that combo box and select the desired field name:

If you click the check box of a column in the Diagram
pane, the same field becomes selected under the Column column of the
Criteria pane.

In the Criteria pane, if you click a combo box that
already contains a column but select another, the previous one would be
replaced by the new one.

Practical
Learning: Introducing the Criteria Pane

On the Query Designer toolbar, click the Show Criteria Pane button

The SQL Pane

Under the Criteria pane, you should see the SQL pane.
To get it:

On the main menu, click Query Designer -> Pane -> SQL

Right-click an area of the window -> Pane -> SQL

On the Query Designer toolbar, click the Show SQL Pane button

The SQL pane displays the SQL statement that results
from selections in the Diagram or the Criteria panes. After clicking the
check box of a column, it becomes selected in the Criteria pane and its
name appears in the SQL section.

Practical
Learning: Introducing the SQL Section

Right-click an empty area in the Diagram pane, position the mouse
on Pane, click SQL

In the SQL section, delete TOP (200)

The Results Pane

The bottom section of the window may display the
Results pane. To get it:

On the main menu, click Query Designer -> Pane -> Results

Right-click an area of the window -> Pane -> Results

On the Query Designer toolbar, click the Show Results Pane button

If you know the name of a column that you want to add,
which you can see in the Diagram pane, you can directly enter it in the
SQL statement.

Managing the Sections

If you don't want a particular section or you want to
hide some sections, you can right-click anywhere in the table, position
the mouse on Pane and click the name of the section:

The Query Editor for Data Selection

As an alternative to the Table window, you can use the
Query Editor for data selection. To prepare a Query Editor for data
selection:

In the Object Explorer, right-click the desired table and click
Select Top 1000 rows

In the Object Explorer, position the mouse on Script Table As ->
SELECT To -> New Query Editor Window

Open a Query Editor. Right-click inside the Query Editor and click
Design Query in Editor...

In the Add Table dialog box, click the name of the table, click Add,
and click Close. This would display a window made of three sections
that are the same as the Diagram pane, the Criteria pane, and the
Results section.

Column Selection

Any of the above three techniques allows you to select
one or more columns to build the desired SQL statement.

After making your selections in the Query Designer,
click OK. This would display a Query with a SQL statement. Also, the SQL
Editor toolbar would be added under the Standard toolbar. To display the
SQL Editor toolbar:

On the main menu, you can click View -> Toolbars -> SQL Editor

You can right-click any toolbar and click SQL Editor

Referring to a Column

We will write many expressions that include the names
of columns. In such expressions, you will need to indicate the particular
column you are referring. There are various ways you can do this. To refer
to, or to indicate, a table:

You must type the name of the table to which the column belongs,
followed by the period operator, followed by the name of the column.
An example would be Employee.LastName

If you didn't create the table in a particular schema, you can
type dbo, followed by the period operator, followed by the name
of the table to which the column belongs, followed by the period
operator, and followed by the name of the column. An example would be
dbo.Employee.LastName

If the table belongs to a particular schema, type the name of the
schema, followed by a period, followed by the name of the table to
which the column belongs, followed by a period, and followed by the
name of the column. An example would be
HumanResources.Employee.LastName

You can type the name of the database that owns the table's
column, followed by the period operator, followed by the name of the
schema, followed by a period, followed by the name of the table to
which the column belongs, followed by a period, and followed by the
name of the column. An example would be
RedOakHighSchool.HumanResources.Employee.LastName

SQL Statement Execution

After creating a SQL statement, you can view its
result, which you can get by executing the statement. To do this:

If you are working in a Table window:

On the main menu, click Query Designer and click Execute SQL

On the Query Designer toolbar, click the Execute SQL button

Right-click anywhere in the Table window and click Execute SQL

If you are working in a Query Editor

On the main menu, click Query and click Execute

On the SQL Editor toolbar, click the Execute button

Right-click anywhere in the Query Editor and click Execute

After executing the statement, the bottom section gets
filled with data from only the selected column(s) of the table. We will
see examples in the next sections and future lessons.

Other Utilities for Data Selection

Introduction

While the SQL Server Management Studio provides a
graphical interface for data selection, it is not the only application you
can use. Both the SQLCMD utility from the Command Prompt and the Windows
PowerShell are other utilities you can use.

To start, open either the Windows PowerShell or the
Command Prompt.

Using SQLCMD

If you want to use the Command Prompt, after opening
it, type SQLCMD -q followed by a space and the appropriate
expression. If you were already working in the SQLCMD utility, simply type
the desired statement. In the next sections and lessons, we will study the
statements used to analyze data.

Using PowerShell

If you are using the Windows PowerShell, if you want
to automatically specify the database to use, in the Object Explorer,
right-click that database and click Start PowerShell. At the prompt, type
the SQL statement and press Enter. In this case, the statement would
execute against a table in the database you had selected. If you want the
statement to apply to a different instance of the server, after the above
formula, type -ServerInstance, followed by a space, and
followed by the server instance in double-quotes.

Practical
Learning: Starting PowerShell

In the Object Explorer, right-click the name of the server and
click Start PowerShell (if you receive an error, skip this step)

Type SQLCMD (or sqlcmd) and press Enter

Type USE LambdaSquare1; and press Enter

Type GO and press Enter

Transact-SQL and Data Selection

Introduction

Data selection is actually performed using SQL code
that contains one or more columns.

Showing the Results of SQL Data Analysis

After entering the SQL statement in a Table window or
a Query Editor, you can execute it to see the result. The result would be
displayed in the bottom section. There are two ways you can display the
result(s).

To specify how you want to show the results of your
SQL statement, if you are using the Query Editor, you have two options:

To show the result as text:

On the SQL Editor toolbar, click the Results To Text button

Right-click somewhere in the table, position the mouse on
Results To, and click Results To Text.

The results would appear in columns of text

To show the result as a spreadsheet:

On the SQL Editor toolbar, click the Result To Grid button

Right-click somewhere in the table, position the mouse on
Results To, and click Results To Grid

The results would appear as a spreadsheet of one or various columns

In the Query Editor, you are expected to write
appropriate code that would constitute a SQL statement.

Getting the Number of Rows Selected

Remember that the lower section of the Query
Editor contains two tabs. When a statement executes, by default,
the Messages tab shows a message. It may show the number of
records that were produced. In the same way, if you work from a
Command Prompt, the last line would display the number of columns.
In the Properties window, the Rows Returned field shows the number
of rows that a SQL statement produced.

If you don't want to show the message, use the
NOCOUNT flag in the following formula:

SET NOCOUNT { ON | OFF }

If you set the flag to ON,
the message would not appear. If you are working in the Query
Editor, a different message would come up, only to let you know
that the "Command(s) completed successfully".

.

If you are working
from a Command Prompt, no message would display. If you want to
display the number of rows again, call the NOCOUNT
flag and set its value to OFF

Introduction to Field Selection

The Formula to SELECT

The most fundamental keyword used by SQL is SELECT.
In order to process a request, you must specify what to select. To perform
data analysis, the SELECT keyword uses the following syntax:

SELECT WhatField(s) FROM WhatObject;

As stated already, SQL is not case-sensitive.
This means that SELECT, Select, and select
represent the same word.

The SELECT and the FROM
keywords are required. The WhatField(s) factor represents one or
more columns from a table. WhatObject represents the name of the
table that has the WhatField(s) column(s).

SELECTing ALL Fields of a Table

To visually select all records of a table, click the
button at the intersection of the columns and the rows:

Using code, to select everything from a table, you can
use the asterisk in the WhatField(s) placeholder of our formula.
For example, to select all records from a table named Students, you can
type:

SELECT * FROM Registration.Students;

After writing the expression in a Query Editor, you
must execute the SQL statement to see its result. Here is an example:

Qualifying the name(s) of (a) column(s) consists of
indicating what table to which it (they) belongs. The way you do this
depends on some issues.

The primary way to qualify the name of a column is to
precede it with the name of the table followed by a period.

If you are using the asterisk * to select all records,
precede it with the name of the table followed by a period. Here is an
example:

SELECT Students.* FROM Registration.Students;

You can also qualify the name of a column using a
schema. To do this, use the following formula:

SchemaName.TableName.ColumnName

Practical
Learning: Qualifying the Names of Columns

In the PowerShell window, type

SELECT ALL Presentation.Units.*

Press Enter and type

FROM Presentation.Units;

Press Enter and type:

GO

Press Enter(If PowerShell is not working, in the SQL
Server Management Studio, change the statement as follows:

SELECT ALL Presentation.Units.*
FROM Presentation.Units;
GO

To execute, press F5)

The Alias Name of a Table

An alias is another name for an object. You can create
an alias name for a table to use in an expression that involves a column.

To visually create an alias for a table, after opening
to table to Edit Top 200 Rows, click the table inthe Diagram pane. In the
Properties window, click Alias and type a letter or word(s) of your
choice. Here is an example:

To create an alias of a table using code, use a letter
or a word that will represent the table. First type the name of the table,
followed by space, and followed by the letter or the word. An example
would be Employee empl. If the table belongs to a schema, precede
the name of the table with it. Here is an example:

SELECT * FROM Registration.Students pupils;
GO

We mentioned that you could qualify the name of a
column or * with the name of the table. If the table has an alias, you can
qualify the column using the alias. This:

SELECT Registration.Students.* FROM Registration.Students;
GO

Is the same as

SELECT pupils.* FROM Registration.Students pupils;
GO

You can also use an alias that is made or more than
one word. If you are visually creating the alias, select the table and
type the words in the Properties window. As soon as you press Enter,
square brackets would be added to the left and the right of the name. If
you are using SQL code, you can include the alias name in double-quotes.
Here is an example:

SELECT FirstName
FROM Registration.Students "Little Angels";
GO

Then, to qualify a column, use the quoted alias name.
Here are examples:

Instead of using double-quotes, an alterntive is to
include the alias in square brackets []. When qualifying the name(s) of
the column(s), you can use the alias either in double-quotes or in square
brackets.

When creating an alias, you can include the AS
keyword between the name of the table and its alias. Here is an example:

SELECT * FROM Registration.Students AS pupils;
GO

This is the same as:

SELECT pupils.* FROM Registration.Students AS pupils;
GO

Practical
Learning: Using the Alias Name of a Table

In the PowerShell window, type:

SELECT ALL aparts.*

Press Enter and type:

FROM Presentation.Units aparts;

Press Enter and type:

GO

Press Enter(If PowerShell is not working, in the SQL Server
Management Studio, change the statement as follows:

SELECT ALL aparts.*
FROM Presentation.Units aparts;
GO

To execute, press F5)

In the SQL Server Management Studio, click the Presentation.Units
tab

Click the check box of * (All Columns). If a message box comes up,
read it

Then click OK and click the * check box again

On the main menu, click Query Designer -> Execute SQL to see the
result:

In the Diagram pane, click the check box of * (All Columns) to
remove it

Introduction to Synonyms

A synonym is another name for an object that already
has a name. That is, a synonym is a pseudo-name for an existing object. A
synonym can be valuable if you want to provide a shorter name for an
object. At first glance, a synonym for a table is like an alias. The
difference is that an alias is created locally where you want to use it
and it can be used only in the code where you create or define it. A
synonym is a name you create globally as an object and you can use that
name throughout the database.

You can create a synonym visually or programmatically.
To visually create a synonym, in the Object Explorer, expand the database.
Right-click Synonyms and click New Synonym:

In the Synonym Name text box or placeholder, type the
desired name. It can be anything to follows the rules of name in
Transact-SQL. If the object belongs to a schema and if you want to
represent that schema with a synonym, specify it in the Synonym Schema
text box or in its placehoder. This is optional. As another option, if you
want to indicate the server, type it in the Server Name text box of
placeholder. By default, the synonym is meant to be created in the current
database. Therefore, the New Synonym dialog box would have selected it in
the Database Name text box. Otherwise, you can specify it. If the object
belongs to a schema other than dbo, you can specify it in the Schema text
box or its placeholder. Specify the object type in the indicated text box
or placeholder. After doing this, if you are using the New Synonym dialog
box, the Object Name combo box would hold the list of objects that belong
to that schema, and you can select the desired one. If you are writing
code, type the original object name after the schema. Here is an example:

If you are using the New Synonym dialog box, click OK
and the synonym would be created. Here is an example of creating a synonym
with code:

CREATE SYNONYM Staff
FOR Personnel.Employees;
GO

If you are programmatically creating the schema,
execute your code.

To use a synonym, in the place where you would have
used the name of the object, use the synonym. Here is an example that uses
the synonym created visually:

SELECT FirstName FROM Pupils;
GO

Here is an example that uses the synonym created with
code:

SELECT FirstName FROM Staff;
GO

You can also use the synonym to qualify the names of
the columns. Here is an example:

SELECT Staff.FirstName FROM Staff;
GO

You can also create a local alias for the synonym and
use it. Here is an example:

SELECT kids.FirstName
FROM Pupils kids;
GO

Practical
Learning: Using a Synonym

In the PowerShell window, type:

CREATE SYNONYM Apartments

Press Enter and type:

FOR Presentation.Units;

Press Enter

Type GO and press Enter

To use the synonym, type

SELECT * FROM Apartments;

Press Enter

Type GO and press Enter

To use an alias of a synonym, type

SELECT apts.* FROM Apartments apts;

Type GO and press Enter

Return to SQL Server Managerment Studio

&

Specifying What to Select

SELECTing One Column

As opposed to selecting all records, you may be
interested in only one particular column whose fields you want to view. To
visually select one record of a table, you must use a Query Editor, not the
Query Designer. Click the column header:

To select a column, you can replace the WhatField(s)
placeholder of our formula with the name of the desired column. For example,
to get a list of last names of students, you would execute the following
statement:

SELECT LastName FROM Registration.Students;
GO

You can also qualify a column by preceding it with the
name of the table followed by the period operator. The above statement is
equivalent to:

SELECT Students.LastName FROM Registration.Students;

When you execute the statement, it would display only
the column that contains the last names.

Practical
Learning: Selecting One Field

In the Diagram pane, click the check box of Price

On the Query Designer toolbar, click the Execute SQL button
to see the result:

Notice that it produces 35 records and some prices appear more than
once, which indicate that some appartments have the same price

In the PowerShell window, type:

SELECT aparts.UnitNumber FROM Presentation.Units aparts;

Press Enter

Type GO and press Enter

To use the synonym, type

SELECT Bedrooms FROM Apartments;

Press Enter

Type GO and press Enter

To use an alias of a synonym, type

SELECT apts.Bathrooms FROM Apartments apts;

Type GO and press Enter

Distinct Field Selection

If you specify a column to select from a table, every
record would come up. This can cause the same value to repeat over and over.
Here is an example:

Notice that PG-13 and R are repeated. Sometimes you want
to show each value only once. To visually get such a result, if you are
working in the Query Designer, click the check box of the desired column in
the Diagram pane or select the field in the Column of the Criteria pane.
Then, in the Properties window, set the Distinct Values field to Yes:

In the SQL, to get a list of distinct values, put the
DISTINCT keyword between SELECT and the name of the column. Here
is an example:

In most cases, you would get a better result if you
select only one column. Still, you can use as many columns as you want.

Practical
Learning: Producing Distinct Values

In the PowerShell window, type:

SELECT DISTINCT aparts.Bedrooms FROM Presentation.Units aparts;

Press Enter

Type GO and press Enter

This indicates that we have efficiencies (apartments with no real
bedrooms) or apartments with 1, 2, or 3 bedrooms

To close PowerShell, type exit and press Enter

Type exit again and press Enter

In SQL Server Managerment Studio, in the SQL section, between
SELECT and Price, type DISTINCT

On the Query Designer toolbar, click the Execute SQL button
to see the result:

Notice that, this time, each price appears only once and, as a
result, you get (only) 24 records.In the Diagram pane, remove the
check box on price. In the SQL pane, delete DISTINCT

SELECTing Many Fields of the Same Table

To consider more than one column in a statement, you can
list them in the WhatField(s) placeholder of our formula, separating
them with a comma except for the last column. The syntax you would use is:

SELECT Column1, Column2, Column_n FROM WhatObject;

For example, to display a list that includes only the
names, gender, email address and home phone of records from a table called
Students, you would type:

Once again, remember that you can use an alias name for
a table by preceding each column with a letter or a word and a period
operator, and then entering the name of the table followed by that letter or
word. Here is an example:

To get a list of the unit numbers and their prices, in the Diagram
pane, click the check boxes of UnitNumber and Price (if you receive an
error message box, read it and click OK, then click those check boxes
again)

Right-click somewhere in the window and click Execute SQL to execute

In the Diagram pane, remove all check boxes

To get a list of the units, their number of bedrooms, their prices,
and their availability, click the check boxes of UnitNumber, Bedrooms,
Price, and Available

Right-click somewhere in the window and click Execute SQL

SELECTing Fields From Different Tables

If you have more than one table in your database, you
can use a statement that selects any field(s) you want from those tables.
Neither the tables nor the columns need to have anything in common. The
formula to follow is:

SELECT WhatField(s) FROM Table_1, Table_2, Table_n

You start with SELECT followed by the
list of fields from the tables. If the tables have columns with different
names, you can simply list the name of each column. Consider the following
tables:

When you select fields from different tables, in the
result, each of the records of the first table would display, each showing
the first record (combination of the selected columns) of the second table.
Then each of the records of the first table would show again, followed by
the second record (combination of the selected columns) of the second table.
This will continue until all records of the second table have displayed.
Consequently, the resulting query would contain (Number of Records of First
Table) x (Number of Records of Second Table). For example, if the first
table contains 4 records and the second table contains 2 records, the
statement would produce 4 x 2 = 8 records. Therefore, the above statement
would produce:

Just as we have used only two tables, you can select
records from three or more tables, following the same formula.

Selecting Many Tables

In the Query Editor or the Query Designer, you can show
the records of as many tables as you want and those tables don't need to
have anything in common. They don't even have to belong to the same
database. Consider the following database named Cruise equipped with a table
named Cabins:

To show the records of more than one table, in the Query
Editor, write a SELECT statement for each table and execute it. If
the tables belong to different databases, make sure you indicate this. Here
is an example: