Home

Topics on Data Selection
 

Data Selection and Expressions
 
Introduction to Databases

Using an Alias Name for a Column

In your SELECT statement, after specifying the column(s), when you execute the SQL statement, the name of each column appears as the column header. If you want, you can display any string of your choice for the column header.

To specify a column header other than the name of the column, if you are using the Query Designer, type the desired string in the Alias column corresponding to the column.

Here is an example:

Alias Name of a Column

If you are using a Query window or if you are writing your SELECT statement, on the right side of the column name, type AS followed by the desired name of the column header. If the desired column header is in one word, you can simply type it. Here is an example:

SELECT FirstName,
       LastName,
       HomePhone AS PhoneNumber,
       ParentsNames AS NamesOfParents
FROM   Registration.Students;
GO

If you want the column header to appear with more than one word, you can provide the words as a string in single-quotes or between the square brackets: [ and ] . Here are examples:

SELECT FirstName AS [First Name],
       LastName AS [Last Name],
       HomePhone AS [Phone Number],
       ParentsNames AS [Names of Parents]
FROM   Registration.Students;
GO

This would produce:

The Alias Name of a Column

In Transact-SQL, you can omit the AS keyword. This means that the above code can be written as:

SELECT FirstName [First Name],
       LastName [Last Name],
       HomePhone [Phone Number],
       ParentsNames [Names of Parents]
FROM   Registration.Students;
GO

You can also qualify a column using the name of the table. Here are examples:

SELECT Students.FirstName AS [First Name],
       Students.LastName AS [Last Name],
       Students.HomePhone AS [Phone Number],
       Students.ParentsNames AS [Names of Parents]
FROM   Registration.Students;
GO

By specifying a schema, the statement can also be written as follows:

SELECT Registration.Students.FirstName AS [First Name],
       Registration.Students.LastName AS [Last Name],
       Registration.Students.HomePhone AS [Phone Number],
       Registration.Students.ParentsNames AS [Names of Parents]
FROM   Registration.Students;
GO

We have already seen how to create an alias for a table. If you are working in the Query Designer and if you create an alias in the Properties window, as soon as you do this, the alias is written immediately in both the Criteria and the SQL sections. Otherwise, you can directly create an lias in your SQL statement. After doing this, you can qualify each column by preceding it with the name of the alias and a period. Here is an example: 

SELECT std.FirstName AS [First Name],
       std.LastName AS [Last Name],
       std.HomePhone AS [Phone Number],
       std.ParentsNames AS [Names of Parents]
FROM   Registration.Students std;
GO

In this and other lessons, we will use a database named FunDS. Fun is fun. The DS stands for department store.

FunDS is a fictitious company that sells clothes and beauty accessories at a nearby mall. We have been commissioned to create an application for the FunDS company. We start with a database that holds an inventory of the items sold in the store. To start (in this lesson), we have created a simple list of items and we will see how to assist the management with analyzing that inventory.

Each item sold in the store has:

  1. A unique item number: The management or the clerk performing data entry specifies that number. The number is between 100000 and 999999
  2. An item name: This is also a small description of the item. We would add a category and sub-category but at this time, we are keeping things simple because we are in the simple designing phase. For this reason, some (if not most) items include a word that identify their category. Examples include: dress, skirts, watch, pants, shoes, wallet
 Department Store
  1. The manufacturer: This is the company that makes the item sold in the store
  2. The size: Most clothes, shoes, and other items are recognized for their size. There is no standard for the size; it depends on the item. There are many other items that don't use/need a size
  3. The unit price: This is how much a customer would pay for the item
  4. The discount rate: This number specifies whether an item is discounted and, if so, by what percentage

In later lessons, we may improve the database but at this time, we are starting it a little simpler for illustration purposes and just for our lessons.
 

Practical LearningPractical Learning: Using Alias Names
  1. Start the computer and log in
  2. Launch Microsoft SQL Server and click Connect
  3. Open the Department Store 1 file (Department Store 1)
  4. To create the database, right-click in the middle window and click Execute
  5. Close the FunDS1 window
  6. In the Object Explorer, expand Databases
  7. Expand FunDS1 and expand Tables
  8. Right-click Inventory.StoreItems and click Edit Top 200 Rows
  9. On the main menu, click Query Designer -> Pane -> Diagram
  10. On the main menu, click Query Designer -> Pane -> Criteria
  11. On the main menu, click Query Designer -> Pane -> SQL
  12. In the Diagram pane, remove the check boxes of all fields
  13. In the SQL pane, delete TOP (200)
  14. In the Criteria pane, click the box under the Column header. You may receive an error message box:
     
    Syntax Error
     
    If so, read the message and click OK. Then click the arrow of the combo box and select ItemNumber:
     
    Selecting a Column in the Criteria Pane
  15. Press Tab, type Item # and press Enter
     
    Selecting a Column in the Criteria Pane
  16. In the Diagram pane, click the check box of ItemName
  17. In the Criteria pane, click the box at the intersection of ItemName and Alias
  18. Type Name/Description
  19. In the Diagram pane, click the check boxes of Size and of UnitPrice
  20. In the Criteria pane, click the box at the intersection of UnitPrice and Alias
  21. Type Unit Price and press Enter.
    Observe the resulting statement in the SQL pane:
    SELECT  ItemNumber AS [Item #],
 	ItemName AS [Name/Description], 
 	Size,
  	UnitPrice AS [Unit Price]
FROM    Inventory.StoreItems
  22. Right-click somewhere in the window and click Execute SQL
     
    Selecting a Column in the Criteria Section

A Combination or Expression of Columns

Using the SELECT keyword, we have learned to create a list of isolated columns. These columns were produced separate of each other. Instead of having separate columns, you can combine them to create a string or a value that is in fact an expression. For example, you can combine a first name and a last name to produce a full name as an expression. Another expression can use a date on the table, add a number to it to get a date on another day. An expression can also be used to perform a calculation on two or more columns such as employees weekly hours multiplied by their hourly salary to get their weekly salary.

The most common operator used is the addition. It can be used to combine two or more strings to get a new one. Here is an example:

SELECT FirstName + N' ' + LastName
FROM   Registration.Students;
GO

This would produce:

A Combination or Expression of Columns

The addition can also be used on numeric values. All other arithmetic operators can be used. For example, you can multiply an employee's weekly time to an hourly salary to get a weekly salary. The statement of such an expression can be written as follows:

SELECT WeeklyHours * HourlySalary
FROM Payroll

You can also create an alias for an expression to give it the desired name. To do this, on the right side of the expression, type AS followed by the name. As we learned already, if the alias is in more than one word, include it in either single quotes or square brackets. Here is an example: 

SELECT FirstName + N' ' + LastName AS N'Full Name',
       EmergencyName  + N' ' + EmrgPhone AS [Emergency Contact]
FROM   Registration.Students;
GO

This would produce:

Expressions

In the same way, you can create a longer and more complexe expression that contain SQL keywords, the table's columns, and regular words. Here is an example:

SELECT 	PropertyType + N' in ' + City + N', ' + State + N', in ' + Condition + 
       	N' condition. Equipped with ' + CAST(Bedrooms AS nvarchar(20)) + 
       	N' bedrooms, ' + CAST(Bathrooms AS nvarchar(20)) + 
       	N' bathrooms. Built in ' + CAST(YearBuilt AS nvarchar(20)) + 
  	N' and selling for ' + CAST(MarketValue AS nvarchar(20))
	AS [Property Description]
FROM    Listing.Properties

Here is an example of what this would produce:

Expression

Practical LearningPractical Learning: Using Expressions in Data Selection
  1. In the Diagram pane, click the check box of DiscountRate
  2. In the Criteria pane, click the first empty box under Column. Type UnitPrice * DiscountRate / 100 and press Tab
  3. In the corresponding Alias box, type Discount Amount
  4. Click the next empty box under Column. Type UnitPrice - (UnitPrice * DiscountRate / 100) and press Tab
  5. In the corresponding Alias box, type After Discount
  6. To execute, on the main menu, click Query Designer -> Execute SQL

Expressions
 
 
 

The Assignment Operator

If you just create a regular expression using arithmetic operators, the new column would not have a name. Transact-SQL allows you to specify a different name for any column during data selection or a name for an expression. This is done using the assignment operator "=".

 

To change the name of a column in data selection, on the right side of SELECT, type the desired name, followed by the assignment operator, followed by the actual name of the column. Here is an example:

SELECT EmergencyName = EmergencyName
FROM   Registration.Students;
GO

If you want to use more than one column, type each and assign it the desired name, separate them with commas. Here is an example:

 Assignment 
SELECT LastName,
       [Emergency Name] = EmergencyName,
       [Emergency Phone] = EmergencyPhone
FROM   Registration.Students;
GO

This would produce:

Assignment

You can also include the name between single-quotes or the square brackets. Here are examples:

SELECT LastName + N', ' + FirstName AS [Full Name],
       [Emergency Name] = EmergencyName,
       'Emergency Phone' = EmergencyPhone
FROM   Registration.Students;
GO

This would produce:

Assignment in an Expression

Other Topics on Data Selection
   

Selecting the Values of the Identity Column

As we know already, to get a list of values from a column, you can use a SELECT statement and give the name of the column. If a table is using an identity column, Transact-SQL provides the $IDENTITY flag that allows you to get the values of that column. Here is an example of using it:

USE Exercise;
GO
SELECT $IDENTITY FROM StoreItems;
GO

Selecting the TOP Number of Records

If you have a large group of records, you can specify that you want to see only a certain number of them.

If you are working in the Query Designer, to specify the maximum number of records you want to see, in the Properties window, set the Top Specification field to Yes (that's its default value when you have right-click a table in the Object Explorer and clicked Edit Top 200 Rows):

Expression

Then click the + button of Top Specification. In the Expression field and type the desired number, and execute the statement:

Expression

Using code, to specify the maximum number of returned records, after the SELECT operator, type TOP followed by an integral number. Continue the SELECT statement as you see fit. Here is an example:

USE Exercise;
GO

SELECT TOP 5 * FROM Employees;
GO

You can also include the number in parentheses. Here is an example:

USE Exercise;
GO

SELECT TOP(5) * FROM Employees;
GO

This statement asks the SQL interpreter to select the first 5 records from the Employees table. Just as done here, you can apply the TOP operator to any of the statements we will see for the rest of our lessons.

Practical LearningPractical Learning: Selecting the Top Records
  1. In the SQL section, on the right side of SELECT, type a space and TOP(12)
  2. Right-click somewhere in the window and click Execute SQL

Selecting the Top Records

Selecting the TOP Percent Records

Instead of selecting a specific number of records, you can ask the interpreter to produce a percentage of records.

To visually specify the percentage of records to return, in the Properties window, after setting the Top Specification field to Yes, type the desired value in the Expression field. The value must be between 1.00 and 100.00 included. After typing the value, set the Percent field to Yes. Here are examples:

Percent

Percent

The formula to specify a percentage of records using code is:

SELECT TOP Value PERCENT WhatColumns FROM WhatObject

After the TOP keword type a number. To indicate that you want a percentage of values, use the PERCENT keyword. Here is an example:

SELECT TOP 25 PERCENT FirstName, LastName, HourlySalary
FROM Employees;
GO

As an alternative, you can include the Value in parentheses:

SELECT TOP (25) PERCENT FirstName, LastName, HourlySalary
FROM Employees;
GO

This statement would produce a quarter (25%) the number of records in the table. For example, if the table has 12 records, the interpreter would produce 3 records. In reality, the database engine would divide the number of records to the number in the parentheses and convert the value to the closest integer, then produce that number of records. For example, if the table has 11 records and you ask for 25%, the interpreter would produce 11 / (100/25) = 11 / 4 = 2.75. The closest high integer is 3. So the database engine would produce 3 records.

WHEN a Field's Value Meets a Criterion

You can use a WHEN conditional statement to refine data selection. Consider the following Persons table:

USE Exercise1;
GO

CREATE TABLE Persons(FirstName nvarchar(20), LastName nvarchar(20), GenderID int);
GO

INSERT INTO Persons VALUES(N'Gertrude', N'Monay', 2),
			  (N'Horace', N'Taylor', 1),
			  (N'Marc', N'Knights', 2),
			  (N'Tiernan', N'Michael', 3),
			  (N'Paul', N'Yamo', 1),
			  (N'Mahty', N'Shaoul', 3),
			  (N'Hélène', N'Mukoko', 2);
GO

Imagine you want to select the GenderID column:

Conditional Statements

If a column has values that are difficult to identify, you can use a CASE conditional statement to customize the result(s). Here is an example:

SELECT FirstName, LastName, Gender =
    CASE GenderID
	WHEN 1 THEN N'Male'
	WHEN 2 THEN N'Female'
	ELSE N'Unknown'
    END
FROM Persons

Conditional Statements

Practical LearningPractical Learning: Ending the Lesson
  1. Close the Query Designer
  2. Close Microsoft SQL Server

Exercises
   

Lesson Summary Questions
  1. What is the difference between the TOP and the PERCENT keywords?
    1. There is no difference between TOP and PERCENT
    2. When used in a SQL statement, the TOP clause acts on the first n records only. The PERCENT keyword acts on the first n percent records
    3. The TOP clause causes a statement to select the first n records and the "PERCENT clause selects the n percent records from either the top or the bottom section of a table or view
    4. The TOP keyword can be used with either SELECT or INSERT statements while the PERCENT keyword can be used with SELECT only
    5. The TOP keyword can be used with only a SELECT statements but the PERCENT keyword can be used with either SELECT, DELETE, or UPDATE
  2. Imagine you have a list of employees as follows: 
    CREATE TABLE Students(FirstName nvarchar(22), LastName nvarchar(22),
             HomePhone nchar(12), ParentsNames nvarchar(48));
GO
    You want to get the records of the table and you write code as follows: 
    SELECT FirstName [First Name],
       LastName [Last Name],
       HomePhone [Phone Number],
       ParentsNames [Names of Parents]
FROM   Students
    What is wrong with that code?
    1. Nothing
    2. The AS keywords are missing as alias for the columns's captions
    3. The names of columns must be included in square brackets
    4. The semicolon is missing after the SELECT statement
    5. The name of the schema is missing before the name of the table
  3. Imagine you have a table named StoreItems and that has a column that gets automatically incrementing numbers from the database engine. What code allows you to get the list of values of that column? 
    1. SELECT #AUTONUMBER FROM StoreItems;
GO
    2. USING StoreItems SELECT @AUTOINCREMENT;
GO
    3. SELECT $IDENTITY FROM StoreItems;
GO
    4. EXECUTE sp_autonumber FROM StoreItems;
GO
    5. WITH StoreItems SELECT IDENTITY_COLUMN;
GO
  4. Imagine you have a table named Employees and that has many records. What code allows you to select the first 5 records from that table? 
    1. SELECT FIRST(5) * FROM Employees;
GO
    2. SELECT TOP(5) * FROM Employees;
GO
    3. SELECT HIGH(5) * FROM Employees;
GO
    4. SELECT 5 ROWS FROM Employees;
GO
    5. SELECT MAX(5) * FROM Employees;
GO
  5. Imagine you have a table named Employees that ha many records. What code allows you to select the first 20 percent records from the table? 
    1. SELECT TOP(20) PERCENT ROWS FROM Products;
GO
    2. SELECT TOP 20% * FROM Employees;
GO
    3. SELECT HIGH(20%) * FROM Employees;
GO
    4. SELECT TOP 20 PERCENT * FROM Products;
GO
    5. SELECT MAX(5) PERCENT * FROM Employees;
GO

Answers
  1. Answers
    1. Wrong Answer: Although they have similarities, there are differences between the TOP and the PERCENT keywords
    2. Right Answer: The TOP keyword is used to act on the first n, such as the first 10 records of a table or view.
      The PERCENT keyword is used to act on the n%, such as the first 10% records of a table or view
    3. Wrong Answer: The PERCENT keyword is used on the first, but not the last, n% records of a table or a view
    4. Wrong Answer: Both the TOP and the PERCENT keywords are used with the SELECT, the UPDATE, and the DELETE operations
    5. Wrong Answer: Both the TOP and the PERCENT keywords are used with the SELECT, the UPDATE, and the DELETE operations
  2. Answers
    1. Right Answer: There is nothing with that code
    2. Wrong Answer: In Transact-SQL, you can omit the AS keyword to specify the alias name of a column
    3. Wrong Answer: The names of columns can be included in square brackets but that is not a requirement
    4. Wrong Answer: The semicolon is not required
    5. Wrong Answer: If the table was created without a schema, it would use the default dbo and you can omit it anywhere you use the table
  3. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Right Answer: That's the right code
    4. Wrong Answer
    5. Wrong Answer
  4. Answers
    1. Wrong Answer
    2. Right Answer: That's the right code
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  5. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Right Answer: That's the right code
    5. Wrong Answer
 
 
   
 
Previous Copyright © 2007-2013, FunctionX, Inc. Next