Query data by using SELECT statements

Practice Labs Module
Time
50 minutes
Difficulty
Intermediate

The "Query Data by Using SELECT 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: Working with SELECT Statements, Build Dynamic SQL Statements, Demonstrating the Working of Primary Key, Foreign Key, Check, and Default...

Join over 3 million cybersecurity professionals advancing their career
Sign up with
or

Already have an account? Sign In »

Overview

Introduction

The Query Data by Using SELECT 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:

  • Working with SELECT Statements
  • Build Dynamic SQL Statements
  • Demonstrating the Working of Primary Key, Foreign Key, Check, and Default Constraints

Lab time: It will take approximately 1 hour to complete this lab.

Exercise 1 - Working with SELECT Statements

In this exercise, you will learn the following in SQL Server 2012:

  • Query data by using Ranking functions
  • Create synonyms and query the synonyms using Except and Intersect operators
  • Build dynamic SQL using the system meta tables
  • Query data by using Inner Join, Left Outer Join, Right Outer Join, and Full Outer Join
  • Demonstrate the use of CASE, ISNULL, and COALESCE
  • Demonstrate the functioning of Primary Key, Foreign Key, Check, and Default constraints

Exercise 2 - Build Dynamic SQL Statements

You can build SQL statements dynamically at run time when you do not know the full SQL statement at compilation time. For example, using dynamic SQL, you can create a SELECT or INSERT statement on a table whose name is not known until runtime.

In this example, you will build dynamic SQL statement and work with the system metadata tables in the AdventureWorks2012 database. For this, you will query the sys.objects table, retrieve the constraint names and the constraint types for the tables in the AdventureWorks2012 database, and input the retrieved details into a temporary table named TableConstraints. You will then create a temporary table named AllTables to hold all the user tables in the AdventureWorks2012 database by querying the sys.tables table. Next, you will dynamically build SQL statement to calculate the number of constraints in each table contained in the temporary table AllTables and insert a row in the temporary table TableConstraints to contain the total number of constraints for that table.

Exercise 3 - Demonstrating the Working of Primary Key, Foreign Key, Check, and Default Constraints

In this example, you will verify that the constraints imposed on the specified tables in the AdventureWorks2012 functions correctly. This will help you understand the behaviour of the various constraint types.

Learning Partner
Comprehensive Learning

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