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:

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   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 is an example:

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

This would produce:

By qualifying each column, the above statement can also be written as follows:

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

It can also be written as follows:

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

It can also be written as follows:

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

1. Start Microsoft SQL Server and connect.
   If you didn't yet, create the RealEstate1 database
2. From the Object Explorer, expand Databases
3. Expand RealEstate1 and expand Tables
4. Right-click dbo.Properties and click Edit Top 200 Rows
5. On the Query Designer toolbar, click the Show Diagram Pane button , the Show Criteria Pane button , and the Show SQL Pane button
6. In the Diagram section, remove the check boxes of all fields
7. In the Criteria section, click under the Column header, click the arrow of the combo box, and select PropertyNumber
8. Press Tab, type Prop # and press Enter
9. In the SQL section, change the statement as follows:
    

   SELECT PropertyNumber AS [Prop #], ZIPCode AS Location, YearBuilt AS [Year Built], PropertyType AS Type, Bedrooms AS Beds, Bathrooms AS Baths, MarketValue AS Value FROM Properties

10. Right-click somewhere in the window and click Execute SQL

Using the SELECT keyword, we have learned to create a list of isolated columns. These columns were rendered 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 + ' ' + LastName
FROM   Students;
GO

This would produce:

The addition can also be used on numeric values. All other arithmetic operators can be used. For example, you can multiply a weekly hours value 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 earlier, if the alias is in more than one word, include it in either single quotes or square brackets. Here is an example:

SELECT FirstName + ' ' + LastName AS 'Full Name',
       EmrgName + ' ' + EmrgPhone AS [Emergency Contact]
FROM   Students;
GO

This would produce:

1. Select the statement in the SQL section and replace it with:
    

   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 Properties

2. Right-click somewhere in the window and click Execute SQL

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

To change the name of a column during data analysis, 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 = EmrgName
FROM   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:

SELECT LastName,
       EmergencyName = EmrgName,
       EmergencyPhone = EmrgPhone
FROM   Students;
GO

This would produce:

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] = EmrgName,
       'Emergency Phone' = EmrgPhone
FROM   Students;
GO

This would produce:

The list of records we get with a SELECT statement is presented in the order the records appear in the table. The SQL allows you to arrange records in alphabetical order, in chronological order or in numeric incremental order. After selecting a series of columns, you may want to list the records following an alphabetical order from one specific field.

To get an alphabetical or an incremental order of records, you must let the database engine know what field would be used as reference.

To specify the order, if you are using a Query window or the Query Designer:

• In the Diagram section, you can right-click a field and select either Sort Ascending or Sort Descending
• In the Criteria section of the window, under the Sort Type column, click the corresponding box of the desired column. This would reveal that it is a combo box. Then click the arrow of that combo box and make your selection between Ascending and Descending:

If you select Ascending or Sort Ascending, the list of records would be re-arranged based on the type of the selected column:

• If the column is text-based (char, varchar, and their variants), the records would be arranged in alphabetical order
• If the column is date or time-based (date, time, or datetime2), the records would be arranged in chronological order
• If the column is number-based, the records would be arranged in incremental order
• If the column is Boolean-based (bit), the FALSE records would appear first

If you select Descending or Sort Descending, the list of records would be re-arranged based on the type of the selected column:

• If the column is text-based (char, varchar, and their variants), the records would be arranged in reverse alphabetical order
• If the column is date or time-based (date, time, or datetime2), the records would be arranged in reverse chronological order
• If the column is number-based, the records would be arranged in decremental order
• If the column is Boolean-based (bit), the TRUE records would appear first

After selecting the desired Sort Type, you can execute the SQL statement.

1. In the SQL section, delete the whole statement
2. In the Diagram section, click the check boxes of YearBuilt, PropertyType, Bedrooms, Bathrooms, and MarketValue
3. In the SQL section, change the statement as follows:
    

   SELECT YearBuilt AS [Year Built], PropertyType AS [Type], Bedrooms AS [Beds], Bathrooms AS [Baths], MarketValue AS [Value] FROM Properties

In SQL, to specify the sorting order, use the ORDER BY expression. The syntax used would be:

SELECT What FROM WhatObject ORDER BY WhatField;

The column used as the basis must be recognized as part of the selected columns. For example, to get a list of students in alphabetical order based on the LastName column, you can use the following statement:

SELECT FirstName, 
       LastName, 
       Gender, 
       ParentsNames, 
       SPHome
FROM Students
ORDER BY LastName;
GO

This would produce:

In the same way, you can get the list of girls followed by the list of boys by ordering the list in alphabetical order based on the Gender. The statement to produce this can be written as follows:

SELECT FirstName, LastName, Gender, EmailAddress
FROM Students
ORDER BY Gender;
GO

As another example, to list all students arranged in alphabetical order by their last name, you can change the statement as follows:

SELECT * FROM Students
ORDER BY LastName;
GO

By default, records are ordered in ascending order. Nevertheless, the ascending order is controlled using the ASC keyword specified after the based field. For example, to sort the last names in ascending order including the first and last names, you would use a statement as follows:

SELECT * FROM Students
ORDER BY LastName ASC;
GO

On the other hand, if you want to sort records in reverse order, you can use the DESC keyword instead. It produces the opposite result to the ASC effect. Here is an example:

SELECT FirstName,
       LastName,
       Gender,
       ParentsNames,
       SPHome
FROM Students
ORDER BY LastName DESC;
GO

This would produce:

 

Practical Learning: Sorting the Records

1. To show the list of properties based on values, starting the unknown followed by the least expensive of them, in the Criteria section, click the box under Sort Type corresponding to MarketValue and select Ascending:
    

   SELECT YearBuilt AS [Year Built], PropertyType AS [Type], Bedrooms AS [Beds], Bathrooms AS [Baths], MarketValue AS [Value] FROM Properties ORDER BY MarketValue

2. Right-click somewhere in the window and click Execute SQL
3. To show the list of properties chronologically starting with the newest, in the SQL section, change the statement as follows:
    

   SELECT YearBuilt AS [Year Built], PropertyType AS [Type], Bedrooms AS [Beds], Bathrooms AS [Baths], MarketValue AS [Value] FROM Properties ORDER BY YearBuilt DESC

4. Right-click somewhere in the window and click Execute SQL