Home

Details on Data Analysis

 

Sorting the Records

 

Sorting the Records in the Table Window

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:

Using the Table Window

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.

Practical Learning Practical Learning: Using Conditions With Data Analysis

  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

Sorting the Records in the SQL

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:

Using the SQL

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:

Sorting Records  

Practical Learning 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

Operators and Data Analysis

 

Introduction to Conditions

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.

WHERE is the Condition

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:

In a WHERE statement, you can also use the ORDER BY expression to sort a list of records based on a column of your choice. Here is an example:

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

This would produce:

 

Practical Learning Practical Learning: Using WHERE

  1. To see a list of only properties in DC, type the following:
     
    SELECT house.PropertyNumber AS [Prop #],
           house.Address,
           house.City,
           house.State,
           house.ZIPCode AS [Location],
           house.YearBuilt AS [Year Built],
           house.PropertyType AS [Type],
           house.MarketValue AS [Value]
    FROM Properties house
    WHERE house.ZIPCode < 20500
    GO
  2. Press F5 to execute the statement
  3. To get a list of only properties in Virginia, change the statement as follows:
     
    SELECT house.PropertyNumber AS [Prop #],
           house.Address,
           house.City,
           house.State,
           house.ZIPCode AS [Location],
           house.YearBuilt AS [Year Built],
           house.PropertyType AS [Type],
           house.MarketValue AS [Value]
    FROM Properties house
    WHERE house.State = 'va'
    GO
  4. Press F5 to execute
  5. To get a list of only the newest properties built in or after 2000, change the statement as follows:
     
    SELECT house.Address,
           house.City,
           house.State,
           house.YearBuilt AS [Year Built],
           house.PropertyType AS [Type],
           house.MarketValue AS [Value]
    FROM Properties house
    WHERE house.YearBuilt >= 2000
    GO
  6. Press F5 to execute
     
  7. To get a list of properties in Maryland arranged by ZIP Codes, change the statement as follows:
     
    SELECT house.PropertyType AS Type,
           house.YearBuilt AS [Year Built],
           house.City,
           house.ZIPCode,
           house.Bedrooms AS Beds,
           house.Bathrooms AS Baths,
           house.MarketValue AS Value
    FROM Properties house
    WHERE house.State = 'md'
    ORDER BY house.ZIPCode
    GO
  8. Press F5 to execute the statement

Hiding a Column

In our SELECT statements so far, we were selecting the columns we needed to display. When formulating such a statement, you can apply a condition to a column without including that column in the result. For example, consider the above query. It is used to display a list of female students. Since we know that the result would show only the girls, it becomes redundant to include the Gender column in the statement. In this case, you can hide that column in the result.

To hide a column from a query, omit that column in the SELECT statement but involve it in the WHERE condition. Here is an example:

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

This would produce:

Notice that the SELECT statement doesn't have the Gender column and the resulting query doesn't show the Gender column.

Practical Learning Practical Learning: Not Showing a Column

  1. To see a list of only properties in Maryland, change the statement as follows:
     
    SELECT house.PropertyType AS Type,
           house.YearBuilt AS [Year Built],
           house.City,
           house.Bedrooms AS Beds,
           house.Bathrooms AS Baths,
           house.MarketValue AS Value
    FROM Properties house
    WHERE house.State = 'md'
    GO
  2. Press F5 to execute the statement

Negating Some Records

In Lesson 5, we saw that you could use the NOT operator to negate the validity of a Boolean expression. Consider the following statement:

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

When this statement is executed, a list of female students would display. Instead of girls, to get a list of male students, you can negate this condition. To do this, type NOT before the condition. This would be done as follows:

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

To make this condition easier to read, you should include the positive expression in parentheses. This would be done as follows:

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

This clearly indicates that it is the expression in the parentheses that is being negated. In the same way, you can use the IS NOT NULL to find the records that are not null. For example, you can create a list of only records that don't have a null value on a certain column. Here is an example:

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

When this statement is executed, the table would display only the records that include a state for each student.

Practical Learning Practical Learning: Negating a Value

  1. To see a list of only properties that have been assigned a property number, change the statement as follows:
     
    SELECT dbo.Properties.PropertyNumber AS [Prop #],
           dbo.Properties.PropertyType AS Type,
           dbo.Properties.YearBuilt AS [Year Built],
           dbo.Properties.City,
           dbo.Properties.State,
           dbo.Properties.ZIPCode AS [ZIP Code],
           dbo.Properties.Bedrooms AS Beds,
           dbo.Properties.Bathrooms AS Baths,
           dbo.Properties.MarketValue AS Value
    FROM dbo.Properties
    WHERE dbo.Properties.PropertyNumber IS NOT NULL
    GO
  2. Press F5 to execute the statement
 

Previous Copyright © 2007-2012 FunctionX Next