MS SQL

Retrieving Data Using the SQL SELECT Statement

• List the capabilities of SQL SELECT statements

• Execute a basic SELECT statement

• Describe how schema objects work

Restricting and Sorting Data

• Limit the rows that are retrieved by a query

• Sort the rows that are retrieved by a query

Using Single-Row Functions to Customize Output

• Describe various types of functions that are available in SQL

• Use character, number, and date functions in SELECT statements

• Describe the use of conversion functions

Reporting Aggregated Data Using the Group Functions

• Identify the available group functions

• Describe the use of group functions

• Group data by using the GROUP BY clause

• Include or exclude grouped rows by using the HAVING clause

Displaying Data from Multiple Tables

• Write SELECT statements to access data from more than one table using equijoins and nonequijoins

• Join a table to itself by using a self-join

• View data that generally does not meet a join condition by using outer joins

• Generate a Cartesian product of all rows from two or more tables

Using Subqueries to Solve Queries

• Define subqueries

• Describe the types of problems that subqueries can solve

• List the types of subqueries

• Write single-row and multiple-row subqueries

Using the Set Operators

• Describe set operators

• Use a set operator to combine multiple a single query

• Control the order of rows returned

Manipulating Data

• Describe each data manipulation language (DML) statement

• Insert rows into a table

• Update rows in a table

• Delete rows from a table

• Control transactions

Using DDL Statements to Create and Manage Tables

• Categorize the main database objects

• Review the table structure

• List the data types that are available for columns

• Create a simple table

• Explain how constraints are created at the time of table creation

Creating Other Schema Objects

• Create simple and complex views

• Retrieve data from views

• Create, maintain, and use sequences

• Create and maintain indexes

• Create private and public synonyms

Managing Objects with Data Dictionary Views

• Use the data dictionary views to research data on your objects

• Query various data dictionary views

Controlling User Access

• Differentiate system privileges from object privileges

• Grant privileges on tables

• View privileges in the data dictionary

• Grant roles

• Distinguish between privileges and roles

Managing Schema Objects

• Add constraints

• Create indexes

• Create indexes using the CREATE TABLE statement

• Creating function-based indexes

• Drop columns and set column UNUSED

• Perform FLASHBACK operations

• Create and use external tables

Manipulating Large Data Sets

• Manipulate data using subqueries

• Describe the features of multitable INSERTs

• Use the following types of multitable INSERTs (Unconditional, Conditional and Pivot)

• Merge rows in a table

• Track the changes to data over a period of time

Generating Reports by Grouping Related Data

• Use the ROLLUP operation to produce subtotal values

• Use the CUBE operation to produce crosstabulation values

• Use the GROUPING function to identify the row values created by ROLLUP or CUBE

• Use GROUPING SETS to produce a single result set

Managing Data in Different Time Zones

• Use Various datetime functions

Retrieving Data Using Subqueries

• Write a multiple-column subquery

• Use scalar subqueries in SQL

• Solve problems with correlated subqueries

• Update and delete rows using correlated subqueries

• Use the EXISTS and NOT EXISTS operators

• Use the WITH clause

Hierarchical Retrieval

• Interpret the concept of a hierarchical query

• Create a tree-structured report

• Format hierarchical data

• Exclude branches from the tree structure

Regular Expression Support

• Using Meta Characters

• Regular Expression Functions

• Replacing Patterns

• Regular Expressions and Check Constraints