Microsoft SQL Server provides many ways and tools to perform data analysis, using a Query Editor, the PowerShell, or else:
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.
The top section is referred to as the Diagram window or the Diagram pane. To get it:
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:
Under the Diagram pane, you can use the Criteria pane. To get it:
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.
Under the Criteria pane, you should see the SQL pane. To get it:
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.
The bottom section of the window may display the Results pane. To get it:
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 Editor for data selection. To prepare a Query Editor for data 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:
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:
After creating a SQL statement, you can view its result, which you can get by executing the statement. To do this:
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 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.
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 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:
In the Query Editor, you are expected to write appropriate code that would constitute a SQL statement.
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;
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).
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:
Alternatively, you can precede the asterisk with the ALL keyword.
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
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: SELECT "Little Angels".FirstName FROM Registration.Students "Little Angels"; GO 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
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:
This would open the New Synonym dialog box: The formula to programmatically create a synonym is: CREATE SYNONYM [ schema_name_1. ] synonym_name FOR <object>
<object> :: =
{
[ server_name.[ database_name ] . [ schema_name_2 ].| database_name . [ schema_name_2 ].| schema_name_2. ] object_name
}
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: Introducing Data Selection
