Home

Introduction to Data Analysis

 

Fundamentals of Data Selection

 

Introduction

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.

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.

Introduction to Databases
 

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.

Lambda Square

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

Each unit is represented with:

  1. 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
  2. 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)
  3. Bathroom: This is a nnumber that indicates the number of bathrooms in the unit
  4. Price: This is how much a tenant would pay every month
  5. Deposit: When a customer starts renting a room, he is usually asked to put some money down, to be optionnally re-imbursed when the customer leaves
  6. Available: This is a Boolean value that indicates whether the unit can be rented
 

Practical LearningPractical Learning: Introducing Data Selection

  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

Introduction to the Query Designer Window

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 LearningPractical 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 Show Diagram Pane

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:

The Properties of a Table

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:

Diagram

If you click a field in the table, the Properties window would display its characteristics:

The Properties of a Column of a Table

Obviously, the characteristics shown by the Properties window depend on the type of column:

The Properties of a Column of a Table

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:

Query Designer

Practical LearningPractical Learning: Introducing the Diagram pane

  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

Diagram

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 Show Criteria Pane

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

Diagram

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:

Resizing a Column

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:

Column Selection in the Criteria Section

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 LearningPractical Learning: Introducing the Criteria Pane

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

Column Selection in the Criteria Section

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 Show SQL Pane

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 LearningPractical Learning: Introducing 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)

SQL

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 Show Results Pane

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:

Query Designer

The Query Window for Data Selection

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

    Query Designer

    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

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 Execute
    • 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 Execute
    • 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.

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 the Windows 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 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.

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 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:
     
    Result To Text
  • To show the result as a spreadsheet:
    • On the SQL Editor toolbar, click the Result To Grid button Result to Grid
    • 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:
     
    Result To Grid

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

Getting the Number of Rows Selected

Identity

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.

Introduction to Field Selection

 

Introduction to SELECTing

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;
Author Note

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

Data Selection and Permissions

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.

 
 
 

SELECTing All Fields of a Table

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:

The Result of a Query

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 Names of Fields

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.

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;

As you may know already, you can create an alias for a table. When referring to a column in a SELECT statement, you can use that alias to qualify the column. To do this, precede a column with the alias name, followed by a period, and followed by the column name. If you are using the asterisk, precede it by the alias. Here is an example:

SELECT std.* FROM Registration.Students std;

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

Qualifying a Field
SchemaName.TableName.ColumnName

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

Practical LearningPractical Learning: Selecting All Columns of a Table

  1. In the Diagram pane, click the check box of * (All Columns). If a message box comes up, read it
     
    Syntax Error

    Then click OK and click the * check box again
  2. On the main menu, click Query Designer -> Execute SQL to see the result:
     
    Lambda Square
  3. In the Diagram pane, click the check box of * (All Columns) to remove it

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

Practical LearningPractical Learning: Selecting One Field

  1. In the Diagram pane, click the check box of Price
  2. On the Query Designer toolbar, click the Execute SQL button Execute SQL to see the result:
     
    Selecting One Field
  3. Notice that it produces 35 records and some price appear more than once, which indicate that some appartments have the same price

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:

Distinct Field Selection

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:

Distinct Field Selection

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:

Distinct Field Selection

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 LearningPractical Learning: Producing Distinct Values

  1. In the SQL section, between SELECT and Price, type DISTINCT 
  2. On the Query Designer toolbar, click the Execute SQL button Execute SQL to see the result:
     
    Selecting a Distinct Field
  3. 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:

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:

A SELECT Query

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;

Practical LearningPractical Learning: Selecting Data

  1. To get a list of the unit numbers and their prices, in the Diagram pane, click the check boxes of UnitNumber and Price
  2. Right-click somewhere in the window and click Execute SQL to execute
     
    Selecting Many Fields
  3. In the Diagram pane, remove all check boxes
  4. 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
  5. Right-click somewhere in the window and click Execute SQL
     
    Selecting Many Fields
  6. Close Microsoft SQL Server. If asked whether you want to save something, click No

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:

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:

Selecting Fields From Different Tables

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:

Selecting Fields From Different Tables

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.

Exercises

   

Lesson Summary Questions

  1. What is the keyword used to get records from a table or a view?
    1. GET
    2. SELECT
    3. CREATE
    4. ADD
    5. QUERY
  2. What statements can be used to get all records of a table named Employees (Choose two)?
    1. SELECT * FROM Employees
    2. SELECT RECORDS FROM Employees
    3. SELECT ALL * FROM Employees
    4. SELECT Employees
    5. SELECT VALUES FROM Employees
  3. What keyword is used to eliminate duplicate records when selecting them from a table or a view?
    1. GETONLY
    2. SELECT
    3. DISTINCT
    4. GROUPBY
    5. JOIN
  4. What is the basic formula to use for data selection in SQL?
    1. EXECUTE FROM WhatObject SELECT WhatColumn(s)
    2. GET WhatColumn(s) FROM WhatObject
    3. CREATE WhatColumn(s) FROM WhatObject SELECT ALL
    4. SELECT WhatColumn(s) FROM WhatObject
    5. ASSIGN WhatColumn(s) TO WhatObject
  5. What is the basic formula to get some records of a table?
    1. EXECUTE FROM WhatObject SELECT Column_1, Column_2, Column_n
    2. SELECT Column_1, Column_2, Column_n FROM WhatObject
    3. GET Column_1, Column_2, Column_n FROM WhatObject
    4. CREATE Column_1, Column_2, Column_n FROM WhatObject SELECT ALL
    5. ASSIGN Column_1, Column_2, Column_n TO WhatObject
  6. What is the basic formula to get some records of different tables?
    1. EXECUTE FROM Table_1, Table_2, Table_n SELECT Column_1, Column_2, Column_n
    2. GET Column_1, Column_2, Column_n FROM Table_1, Table_2, Table_n
    3. CREATE Column_1, Column_2, Column_n FROM Table_1, Table_2, Table_n SELECT
    4. SELECT Column_1, Column_2, Column_n FROM Table_1, Table_2, Table_n
    5. ASSIGN Column_1, Column_2, Column_n TO Table_1, Table_2, Table_n

Answers

  1. Answers
    1. Wrong Answer: There is no GET keyword in SQL or Transact-SQL
    2. Right Answer: SELECT is used to select columns/records from a table or a view
    3. Wrong Answer: The CREATE keyword is used to create something, like a table, not to select records
    4. Wrong Answer:
    5. Wrong Answer: There is no such a keyword as QUERY in SQL
  2. Answers
    1. Right Answer: The ALL keyword is the default keyword to select all records of a table or a view. To select all records from a table, use either the asterisk (*) or ALL * followed by FROM and the name of the table
    2. Wrong Answer
    3. Right Answer: The ALL keyword is the default keyword to select all records of a table or a view. To select all records from a table, use either the asterisk (*) or ALL * followed by FROM and the name of the table
    4. Wrong Answer
    5. Wrong Answer
  3. Answers
    1. Wrong Answer: There is no GETONLY keyword in SQL
    2. Wrong Answer: The SELECT keyword is used to select columns, not to restrict anything
    3. Right Answer: The DISTINCT keyword is used to make sure a SELECT statement produces unique values from a column
    4. Wrong Answer: There is no GROUPBY keyword in SQL
    5. Wrong Answer: The JOIN keyword is used to join two tables or views, not to produce unique records
  4. Answers
    1. Wrong Answer: The EXECUTE operator has no place here
    2. Wrong Answer: There is not such an operator named GET
    3. Wrong Answer: The names of column do not follow CREATE
    4. Right Answer: That's the correct formula
    5. Wrong Answer: There is no ASSIGN keyword in such an expression
  5. Answers
    1. Wrong Answer: The EXECUTE operator has no place here
    2. Right Answer: That's the correct formula
    3. Wrong Answer: There is not such an operator named GET
    4. Wrong Answer: The names of column do not follow CREATE
    5. Wrong Answer: There is no ASSIGN keyword in such an expression
  6. Answers
    1. Wrong Answer: The EXECUTE operator has no place here
    2. Wrong Answer: There is not such an operator named GET
    3. Wrong Answer: The names of column do not follow CREATE
    4. Right Answer: That's the correct formula
    5. Wrong Answer: There is no ASSIGN keyword in such an expression
 
 
   
 

Previous Copyright © 2007-2011 FunctionX.com Next