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.
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: 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:
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 window for data selection. To prepare a Query window 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:
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 QL statement in double-quotes. Then type Invoke-Sqlcmd followed by a space and the statement in double-quotes. The formula would be:
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:
In the Query window, you are expected to write appropriate code that would constitute a SQL statement.
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;
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:
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.
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 window, you must execute the SQL statement to see its result. Here is an example:
Alternatively, you can precede the asterisk with the ALL keyword (but don't omit the asterisk). Here is an example:
SELECT ALL * FROM Registration.Students; GO
Qualifying the name(s) of (a) column(s) consists of indicating what table it (they) belong to. 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.
Here is an example:
USE VideoCollection GO CREATE SCHEMA Experiment; GO CREATE TABLE Experiment.Videos ( Title nvarchar(50), Director nvarchar(50), Rating nchar(10), YearReleased int ) GO INSERT INTO Experiment.Videos VALUES(N'Her Alibi', N'Bruce Beresford', N'PG-13', 1998), (N'Two for the Money', N'D.J. Caruso', N'R', 2008), (N'Memoirs of a Geisha', N'Rob Marshall', N'PG-13', 2006); GO SELECT Experiment.Videos.* FROM Experiment.Videos; GO
As opposed to selecting all records, you may be interested in only one particular column whose fields you want to view. To do this, 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.
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 of 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.
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:
SELECT FirstName, LastName, Gender, EmailAddress, HomePhone FROM Registration.Students;
Once again, you can qualify each column by preceding it with the name of the table followed by the period operator. The above statement is equivalent to:
SELECT Students.FirstName, Students.LastName, Students.Gender, Students.EmailAddress, Students.HomePhone FROM Registration.Students;
You don't have to qualify all columns, you can qualify some and not qualify some others. The above statement is equivalent to:
SELECT Students.FirstName, LastName, Students.Gender, EmailAddress, HomePhone FROM Registration.Students;
When executed in a Query window, this expression would produce:
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:
SELECT std.FirstName, std.LastName, std.Gender, std.EmailAddress, std.HomePhone FROM Registration.Students std;
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:
CREATE DATABASE Exercise5; GO USE Exercise5; GO CREATE TABLE Employees ( [Empl #] nchar(7), [First Name] nvarchar(20), [Last Name] nvarchar(20), [Hourly Salary] money ); GO CREATE TABLE Products ( Number int, Name nvarchar(50), UnitPrice money, ); GO INSERT INTO Employees VALUES(N'207-025', N'Julie', N'Flanell', 36.55), (N'926-705', N'Paulette', N'Simms', 26.65), (N'240-002', N'Alexandra', N'Ulm', 12.85), (N'847-295', N'Ellie', N'Tchenko', 11.95); GO INSERT INTO Products VALUES(217409, N'Short Black Skirt', 55.85), (284001, N'Pencil Skirt', 49.00); GO
Here is an example of selecting columns from those tables:
SELECT [Empl #], [First Name], [Last Name], Name, UnitPrice FROM Employees, Products; GO
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:
Imagine your tables have each a column with the same name:
DROP TABLE Employees; GO DROP TABLE Products; GO CREATE TABLE Employees ( [Empl #] nchar(7), Name nvarchar(50), [Hourly Salary] money ); GO CREATE TABLE Products ( Number int, Name nvarchar(50), UnitPrice money, ); GO INSERT INTO Employees VALUES(N'207-025', N'Julie Flanell', 36.55), (N'926-705', N'Paulette Simms', 26.65), (N'240-002', N'Alexandra Ulm', 12.85), (N'847-295', N'Ellie Tchenko', 11.95); GO INSERT INTO Products VALUES(217409, N'Short Black Skirt', 55.85), (284001, N'Pencil Skirt', 49.00); GO
When selecting the columns, you must qualify at least the column(s) with the same name. Otherwise you would receive an error. Therefore, the above Name columns can be accessed as follows:
SELECT [Empl #], empl.Name, prod.Name, UnitPrice FROM Employees empl, Products prod; GO
This would produce:
Of course, you can qualify all columns of the tables. Here are examples:
DROP TABLE Employees; GO DROP TABLE Products; GO CREATE TABLE Employees ( [Empl #] nchar(7), [First Name] nvarchar(20), [Last Name] nvarchar(20), [Hourly Salary] money ); GO CREATE TABLE Products ( Number int, Name nvarchar(50), UnitPrice money, ); GO INSERT INTO Employees VALUES(N'207-025', N'Julie', N'Flanell', 36.55), (N'926-705', N'Paulette', N'Simms', 26.65), (N'240-002', N'Alexandra', N'Ulm', 12.85), (N'847-295', N'Ellie', N'Tchenko', 11.95); GO INSERT INTO Products VALUES(217409, N'Short Black Skirt', 55.85), (284001, N'Pencil Skirt', 49.00); GO SELECT empl.[Empl #], empl.[First Name], empl.[Last Name], prod.Name, prod.UnitPrice FROM Employees empl, Products prod; GO
Just as we have used only two tables, you can select records from three or more tables, following the same formula.