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 Microsoft SQL Server, data analysis can be performed using a query window in Microsoft SQL Server Management Studio, at the Command Prompt, or in PowerSheel.

1. Start the computer and log in
2. Launch Microsoft SQL Server and click Connect
3. On the main menu, click File -> New -> Query With Current Connection
4. In the Query window, type the following code:

   -- ===============================================================
   -- Database:	 LambdaSquare1
   -- Author:	 FunctionX
   -- Date Created: Sunday, January 16, 2011
   -- Description:	 This database represents an apartment building.
   --		 This part of the datbase is used to present the
   --		 apartments to a customer and select the right one
   -- ===============================================================
   USE master
   GO
   
   -- Drop the database if it already exists
   IF  EXISTS (
   	SELECT name 
   		FROM sys.databases 
   		WHERE name = N'LambdaSquare1'
   )
   DROP DATABASE LambdaSquare1;
   GO
   
   CREATE DATABASE LambdaSquare1
   ON PRIMARY
   ( NAME = DataRepository,
       FILENAME = 'C:\Microsoft SQL Server Database Development\LambdaSquare1.mdf')
   LOG ON
   ( NAME = DataLog,
       FILENAME = 'C:\Microsoft SQL Server Database Development\LambdaSquare1.ldf');
   GO
   
   USE LambdaSquare1;
   GO
   CREATE SCHEMA Presentation;
   GO
   CREATE TABLE Presentation.Units
   (
   	UnitNumber nchar(10),
   	Bedrooms int,
   	Bathrooms real,
   	Price money,
   	Deposit money,
   	Available bit
   );
   
   INSERT Presentation.Units
   VALUES('101', 0, 1.00, 845.00,  200.00, 0),
         ('102', 1, 1.00, 895.00,  250.00, 0),
         ('103', 1, 1.00, 925.00,  275.00, 1),
         ('104', 2, 1.00, 1050.00, 300.00, 0),
         ('105', 1, 1.00, 885.00,  250.00, 1),
         ('106', 3, 2.00, 1350.00, 425.00, 1),
         ('107', 2, 2.00, 1185.00, 400.00, 0),
         ('108', 0, 1.00, 865.00,  225.00, 1),
         ('109', 2, 1.00, 1050.00, 350.00, 1),
         ('110', 1, 1.00, 895.00,  250.00, 0),
         ('111', 1, 1.00, 895.00,  250.00, 0),
         ('112', 0, 1.00, 805.00,  200.00, 1),
         ('201', 0, 1.00, 825.00,  200.00, 1),
         ('202', 1, 1.00, 950.00,  325.00, 0),
         ('203', 1, 1.00, 885.00,  250.00, 1),
         ('204', 2, 2.00, 1125.00, 425.00, 1),
         ('205', 1, 1.00, 1055.00, 350.00, 0),
         ('206', 2, 1.00, 1165.00, 400.00, 1),
         ('207', 1, 1.00, 895.00,  250.00, 0),
         ('208', 0, 1.00, 815.00,  200.00, 1),
         ('210', 1, 1.00, 895.00,  350.00, 1),
         ('211', 2, 2.00, 925.00,  400.00, 1),
         ('212', 3, 2.00, 1280.00, 500.00, 0),
         ('301', 0, 1.00, 845.00,  200.00, 0),
         ('302', 1, 1.00, 925.00,  250.00, 0),
         ('303', 2, 1.00, 985.00,  275.00, 1),
         ('304', 2, 2.00, 1250.00, 300.00, 0),
         ('305', 1, 1.00, 945.00,  250.00, 1),
         ('306', 3, 2.00, 1350.00, 425.00, 1),
         ('307', 2, 2.00, 1285.00, 400.00, 0),
         ('308', 0, 1.00, 875.00,  225.00, 1),
         ('309', 2, 1.00, 1150.00, 350.00, 1),
         ('310', 1, 1.00, 955.00,  250.00, 0),
         ('311', 3, 2.00, 1325.00, 500.00, 0),
         ('312', 0, 1.00, 825.00,  200.00, 1);
   GO

5. To execute, on the main menu, click Query -> Execute
6. In the Object Explorer, right-click the Databases node and click Refresh
7. Expand the Databases node
8. Expand LambdaSquare1 and expand its Tables node

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.

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

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: and the name of the table. 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:

1. On the main menu, click Query Designer, position the mouse on Pane, and click Diagram
2. Click the check boxes of all fields to remove the check marks

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.

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

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.

1. Right-click an empty area in the Diagram pane, position the mouse on Pane, click SQL
2. In the SQL section, delete TOP (200)

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.

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:

As an alternative to the Table window, you can use the Query window for data selection. To prepare a Query window 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 window. Right-click inside the Query window 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.

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

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 window
  • On the main menu, click Query and click Execute
  • On the SQL Editor toolbar, click the Execute button
  • Right-click anywhere in the Query window 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.

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.

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.

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 QL statement in double-quotes. Then type Invoke-Sqlcmd followed by a space and the statement in double-quotes. The formula would be:

Invoke-Sqlcmd "Statement"

Then press Enter. In this case, the statement would executed against a table in the database you had selected. If you want the statement to apply to a different instance of, after the above formula, type -ServerInstance, followed by a space, and followed by the server instance in double-quotes.

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

After entering the SQL statement in a Table window or a Query window, 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 window, 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. Here is an example:
   
  
• 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. Here is an example:
   
  

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

Remember that the lower section of the Query window contains two tabs. When a statement is executed, by default, the Messages tab shows a message. For a SELECT statement, it shows the number of records that were selected. 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 window, 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.

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).

It appears that data selection is the most fundamental and the most common operation performed on a database. This is because it allows a user to at least see the records of a table. This is also the most basic right you can give to a user. As it happens, many permissions may depend on, or need, the ability to open a table. That's why the right to select records must sometimes be added to other permissions.

As seen for data entry, to visually control data selection, open the Properties dialog box for a table. As an alternative, you can perform security using code.

To visually grant or deny data selection, access the Tables Properties of a table and click the Permissions node. After selecting the user in the Users or Roles list, in the Permission column of the Permissions section, locate the Select row.

The formula to programmatically grant data selection to a user is:

GRANT SELECT [, Permission1,Permission2, Permission_n]
ON [ OBJECT :: ][ schema_name ].object_name [ (Column1, Column2, Column_n ] ) ]
TO Login1, Login2, Login_n ]

The formula to programmatically deny data selection is:

DENY SELECT [, Permission1,Permission2, Permission_n]
ON [ OBJECT :: ][ schema_name ].object_name [ (Column1, Column2, Column_n ] ) ]
TO Login1, Login2, Login_n ]

The SELECT permission can be used by itself but some other rights must be combined with it:

• SELECT: In Microsoft SQL Server 2008 (unlike previous versions), you can separately open a table to view its records but not use the table for data entry. The SELECT permission allows a user to open a table and see the records in it. Here is an example:

  USE Exercise;
  GO
  
  GRANT SELECT 
  ON OBJECT::Employees
  TO [Peter Mukoko];
  GO

  If you grant only the SELECT permission, the user can neither add new records nor change anything about the existing records

• INSERT: If the INSERT permission is used by itself, because it doesn't imply the SELECT permission, the user cannot open the table in the SQL Server Management Studio.
  If the user is working from the SQL Server Management Studio, you should (must) combine the INSERT with the SELECT permissions. Here is an example:

  USE Exercise;
  GO
  
  GRANT SELECT, INSERT
  ON OBJECT::Employees
  TO [Peter Mukoko];
  GO

• UPDATE: On the surface, the UPDATE permission allows a user to change one or more records on the table. If used alone, the UPDATE permission has no effect: the user cannot even open the table. This means that this right should (must) be combined with (an)other(s).
  If the UPDATE permission is combined with the SELECT right, the user becomes able to open the table and change its record(s). Among the other ways you can use this right, if you grant it and grant SELECT but deny INSERT, the user would be able to change existing records but cannot add new records

As mentioned during data entry, if you want to give a user the ability to grant or deny data selection to other accounts, add the WITH GRANT OPTION expression.