Extracting Data using Statements

Introduction

The Extracting Data using Statements module provides you with the instruction and server hardware to develop your hands on skills in the defined topics. This module includes the following exercises:

Learning Outcomes

In this module, you will complete the following exercises:

• Exercise 1 - Demonstrating the Use of Select Statement
• Exercise 2 - Performing Joins
• Exercise 3 - Using UNION and INTERSECT operators

After completing this lab, you will be able to:

• Extract data from tables using simple SELECT statements
• Query data by using Inner Join, Left Outer Join, Right Outer Join, and Full Outer Join
• Combine result sets by using UNION and INTERSECT operators

Exam Objectives

The following exam objective is covered in this lab:

• 3.1 Select data

Lab Duration

It will take approximately 20 minutes to complete this lab.

Exercise 1 - Demonstrating the Use of SELECT Statement

You can use the SELECT statements to obtain one or many rows or columns from a database table. The general syntax of a simple SELECT statement is:

The select_list indicates the list of columns to be selected, the source-table in the FROM clause indicates the table from where the data to be retrieved and the search-condition in the WHERE clause indicates the search condition based on which the rows to be retrieved.

Learning Outcomes

After completing this exercise you will be able to:

• Extract data from tables using simple SELECT statements

Exercise 2 - Performing Joins

You can retrieve data from two or more logically related tables using SQL Server Joins. The two main types of joins include inner join and outer join.

Inner Join

You can specify the Inner join either in the FROM or WHERE clause of your SELECT statement. The inner join return only the records from the tables being joined that matches the join condition.

Left Outer Join

Left outer join returns all the records from the left table in the results whether or not there is a matching row in the right table. For unmatched records, the row contains a null value in the respective columns.

Right Outer Join

Right outer join returns all the records from the right table in the results whether or not there is a matching row in the left table. For unmatched records in the left table, the row contains a null value in the respective columns of the left table.

Full Outer Join

The Full Outer join returns all the rows from the joined tables whether irrespective of matching values in the columns on which the join is performed.

Learning Outcomes

• Query data by using Inner Join, Left Outer Join, Right Outer Join, and Full Outer Join

Exercise 3 - Using UNION and INTERSECT operators

You can use the UNION operator to combine the results of two or more queries into a single result set. The final result set includes all the rows including the duplicates from the result sets of all the queries specified in the union, if the UNION clause contains the option ALL. If the option ALL is not specified in the UNION, then the duplicate rows are eliminated in the final result.

Please note the difference between JOIN and UNION is that the JOIN operation combines the result set from two or more tables based on the specified columns.

Important Guidelines to follow when using UNION:

• You must provide the same number of columns in the same order in all queries in the UNION.
• The data types of respective columns in all the queries in the UNION should be the same.

The INTERSECT operator returns distinct values that are common in the result set of the queries that are to the left and to the right of the INTERSECT operand. The number and order of the columns in both the queries should be the same and their data types must be compatible.

Learning Outcomes

• Combine result sets by using UNION and INTERSECT operators