SQL Developer Database Certification

Working with Query Fundamentals

• Querying Data with the SELECT Statement
• Sorting and Filtering Data with the WHERE clause
• Use the DISTINCT statement
• Work with NULL values
• Use the LIKE clause
• Work with Wildcard characters
• Use the BETWEEN clause
• Use the ORDER BY clause
• back to top

Querying Multiple Tables

• Understand Joins
• Work with INNER JOINS
• Work with OUTER JOINS
• Understand types of OUTER JOINS
• Work with CROSS JOINS
• Retrieve data from multiple tables
• Work with Self Joins
• back to top

Implementing Aggregate Queries

• Understand Aggregate functions
• Use the GROUP BY clause
• Modify data in tables
• Work with the WITH ROLLUP Operator
• Work with the WITH CUBE Operator
• Work with Grouping Sets
• Understand the HAVING clause
• back to top

Combine and Compare Datasets

• Understand the Fundamentals of Combining Datasets
• Use the UNION operator
• Use the EXCEPT operator
• Use the INTERSECT operator
• Use the APPLY operator
• Understand Built-in Scalar Functions
• Work with the DATE and TIME functions
• Use the SYSTEM functions
• Use the STRING functions
• back to top

Working with Transactions and Locking

• Understand the fundamentals of Transactions
• List the properties and types of Transactions
• Work with Implicit Transactions
• Use the COMMIT and ROLLBACK statements
• Work with Explicit Transactions
• Understand different database locking approaches
• Understand the Deadlock scenario
• Set Transaction Isolation Levels
• back to top

Modifying Data

• Use the INSERT statement
• Use the UPDATE statement
• Use the DELETE statement
• Use the TRUNCATE TABLE statement
• Modify Data using the MERGE Statement
• Use the OUTPUT clause
• back to top

Working with Data Types and Creating Tables

• Understand Data types
• Understand Schemas
• Work with Identifiers
• Choose the right Data Types
• Work with NULL or NOT NULL values
• Understand the IDENTITY property
• Create a Table
• Compress Rows and Pages to reduce storage space
• back to top

Implementing Data Integrity

• Understand the fundamentals of Data Integrity
• Define Declarative Data Integrity
• Implement Data Integrity
• Use the PRIMARY KEY constraint
• Use the UNIQUE constraint
• Use the FOREIGN KEY constraint
• Use the CHECK constraint
• Use the DEFAULT constraint
• back to top

Using Advanced Query Techniques

• Define Common Table Expressions
• Work with Common Table Expressions
• Understand how Sub-queries work
• Work with Sub-queries
• Understand how to rank rows with functions.
• Use the ROW_NUMBER function
• Use the RANK function
• Use the DENSE RANK function
• Use the NTILE function
• back to top

Using Programming Objects

• Understand how Variables are used
• Work with Variables
• Understand how to use Stored Procedures
• Work with Stored Procedures
• Work with the following Control Flow statements
  • BEGIN…END
  • IF…ELSE
  • WHILE…BREAK/CONTINUE
  • RETURN
  • WAITFOR
  • GOTO
• Understand the use of Cursors
• Work with Cursors
• Understand and work with User-Defined Functions
• back to top

Working with Triggers and Views

• Understand Triggers
• Work with DML Triggers
• Work with DDL Triggers
• Work with Logon Triggers
• Understand Views
• Create Regular Views
• Create Indexed Views
• Create Partitioned Views
• Modify Views
• Rename Views
• Modify Data in Views
• back to top

Improving Query Performance

• Understand the principles of Query Performance
• Measure Query Performance
• Understand what is the Query Cost
• Understand what is the Query Execution Time
• Optimize Query Performance
• Tune Query Performance
• Understand Effects of Query Components on Query Cost
• back to top

Working with Indexes and Partitions

• Understand the Index Structure
• Work with Indexes
• Use the DISTINCT statement
• Work with NULL values
• Use the LIKE clause
• Work with Wildcard characters
• Use the BETWEEN clause
• Use the ORDER BY clause
• back to top

Working with XML in the database

• Understand XML
• Understand the use of XML in SQL Server
• Retrieve Table Data as XML
• Work with XML Data
• Use the XML Data Type
• Work with XML Stored in an XML Variable or Column
• Retrieve data stored in an XML column by using:
  • Exist
  • Value
  • Nodes
  • Query
• back to top

Working with SQLCLR and FILESTREAM

• Understand SQLCLR
• Create Objects using SQLCLR
• Compile a Common Language Runtime (CLR) Object
• Load and Use the compiled assembly in SQL Server
• Describe the Permission Sets in SQLCLR
• Understand the fundamentals of FILESTREAM
• Use the FILESTREAM feature
• back to top

Using Additional Functionality

• Understand what are Spatial Data Types
• Work with Geometry Data Types
• Work with Geography Data Types
• Understand what are Full Text Indexes
• Work with the Full Text Index Thesaurus File and Stop List
• Work with CONTAINS and FREETEXT
• Use Full-Text Search
• Troubleshoot Full-Text Search
• back to top

Implementing the Service Broker

• Understand the Service Broker
• Understand the Service Broker Components
• Work with the Service Broker
• Understand the Service Broker Applications
• Enable the Service Broker
• Configure the Service Broker Components
• Send and Receive Messages
• back to top

Using SQL Server Manageability Features

• Understand Database Mail
• Configure Database Mail
• Send Database Mail messages
• Manage Database Mail
• Understand Windows PowerShell
• Execute SQL Server PowerShell cmdlets
• back to top

Tracking Data Changes

• Understand how to Manage Changes to Data
• Work with Change Tracking
• Enable Change Tracking at the Database Level
• Enable Change Tracking at the Table Level
• Use Change Tracking Functions
• Understand the fundamentals of Change Data Capture
• Work with Change Data Capture
• Microsoft Test Preparation
• back to top