Details on Data Analysis

The lists of records we get with a SELECT statement are presented in the order they have in the table. 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 know what field would be used as reference.

To specify the order, if you are using a Table window:

• 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 (datetime or smalldatetime), 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 (datetime or smalldatetime), 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. Start Microsoft SQL Server and the SQL Server Management Studio.
   If you didn't yet, create the RealEstate1 database
2. In the Object Explorer, right-click Databases and click New Query

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 Resords

1. To show the list of properties based on values, starting the unknown followed by the least expensive of them, type the following statement:
    

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

2. Press F5 to execute
3. To show the list of properties chronologically starting with the newest, change the statement as follows:
    

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

4. Press F5 to execute

In the previous lesson, we learned that we could analyze data using the Table window. Here is an example:

We also learned how to analyze data by creating and executing a SQL statement in a query window. Instead of selecting all data as we have done so far using the SELECT keyword, you can present a condition that the database would follow to isolate specific records.

When analyzing data or if you are creating a query using the Table window, you can type an expression that uses one or more logical operators we reviewed in Lesson 5. Here is an example of an expression

> '12/31/1993'

This means that the dates that occur after 1993 would be selected.

If you are writing your SELECT statement, to formulate a condition, you use the WHERE keyword with a basic formula as follows:

SELECT What FROM WhatObject WHERE Expression;

The expressions used in conditions are built using algebraic, logical, and string operators. The Expression factor is called a criterion. Although a group of expressions, making it plural is called criteria, the word criteria is sometimes used for a singular expression also. The expression is written using the formula:

ColumnName=Value

The ColumnName factor must be an existing column of a table. It is followed by the assignment operator. The Value factor is the value that would set the condition. If the value is a word or a group of words (also called a string), you must include it in single-quotes. If it is a number, you can type its numeric value.

Here is an example from a database of students, from a table named Students, to get a list of female students:

SELECT DateOfBirth, LastName, FirstName, 
       Gender, State, ParentsNames
FROM Students
WHERE Gender='Female';
GO

This would produce:

SELECT DateOfBirth, LastName, FirstName, 
       Gender, State, ParentsNames
FROM Students
WHERE State='MD'
ORDER BY LastName;
GO

SELECT DateOfBirth, LastName,
       FirstName, State, ParentsNames
FROM Students
WHERE Gender='Female';
GO

SELECT DateOfBirth, LastName, FirstName, 
       State, ParentsNames
FROM Students
WHERE Gender = 'Female';
GO

SELECT DateOfBirth, LastName, FirstName, 
       Gender, State, ParentsNames
FROM Students
WHERE NOT Gender = 'Female';
GO

SELECT DateOfBirth, LastName, FirstName, 
       Gender, State, ParentsNames
FROM Students
WHERE NOT (Gender = 'Female');
GO

SELECT DateOfBirth, LastName, FirstName, 
       State, ParentsNames
FROM   Students
WHERE  State IS NOT NULL;
GO