Overview

Introduction

The Working with SELECT Queries module provides you with the instructions and devices to develop your hands-on skills in the following topics.

  • Filter data based on predicates
  • Fetch unique rows using DISTINCT clause
  • Fetch rows from multiple tables using aliases
  • Use TOP and OFFSET-FETCH filters in SELECT queries
  • Sort query results using ORDER BY clause
  • Use simple CASE expression with SELECT statement

Lab time: It will take approximately 45 minutes to complete this lab.

Exam Objectives

The following exam objectives are covered in this lab:

  • Write and execute a simple SELECT statement using the WHERE clause to filter data based on predicates
  • Write and execute a SELECT statement with a WHERE clause to fetch only unique rows by using the DISTINCT clause
  • Write and execute a SELECT statement to fetch rows from two tables by giving aliases for the tables
  • Demonstrate the use of the TOP and OFFSET-FETCH filters in SELECT queries
  • Demonstrate how to sort query results using ORDER BY clause
  • Write and execute a SELECT statement with a simple CASE expression

Exercise 1 - Filter Data Based on Predicates

Predicate is an expression that returns Boolean data type values such as TRUE, FALSE or UNKNOWN. It is used with the WHERE clause to extract rows from the database that meet specified conditions. You can use logical operators such as AND, OR and NOT in predicates. In this exercise, you will learn to filter the data based on predicates.

Exercise 2 - Fetch Unique Rows Using DISTINCT Clause

The SELECT DISTINCT statement is used to extract unique rows from the database. The syntax of the SELECT DISTINCT statement is as follows:

<SELECT DISTINCT columnname1, columnname2 FROM tablename> In this exercise, you will learn to fetch distinct values from the database using the DISTINCT clause.

Exercise 3 - Fetch Rows from Multiple Tables Using Aliases

Aliases can be used to rename a specific item in the database that is a part of the query result. You can use aliases to temporarily rename tables, fields, expressions, and so on.

In this exercise, you will learn to write and execute the SELECT statement to fetch rows from two tables using aliases.

Exercise 4 - Use of TOP and OFFSET-FETCH Filters in SELECT Queries

The TOP filter fetches the specified number of rows from the database tables. It can retrieve a percentage of rows also from the tables. You can use the TOP filter to retrieve data from databases that have huge number of records.

Exercise 5 - Sort Query Results Using ORDER BY Clause

You can use the ORDER BY clause to sort the data by one or more fields. You can use the keywords ASC or DESC along with the field names in the ORDER BY clause. Ascending is the default sort order that sorts from lowest to highest value of the column/field specified. Descending order sorts from highest to lowest value of the column/field specified.

In this exercise, you will learn to use ORDER BY clause that sorts the result set specific to a particular column in ascending or descending order.

Exercise 6 - Use Simple CASE Expression with SELECT Statement

You can use the CASE expression similar to the if-then-else type of logic. There are two formats of CASE expression and they are as follows:

Simple CASE expression: In this format, you can compare the expression to static values. Searched CASE expression: In this format, you can compare the expression to one or more logical criteria.

The ELSE clause is optional in both the formats. In this exercise, you will learn to use Simple CASE expression.

Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.