MCTS - Microsoft SQL Server 2008, Database Development - 70-433 - Objective List Part 3 (Working with Query Fundamentals)

• Query data by using SELECT statements
  
  • SELECT (Transact-SQL)
  • WITH common_table_expression
  • SELECT Clause
  • INTO Clause
  • FROM
  • WHERE
  • GROUP BY
  • HAVING
  • UNION
  • EXCEPT and INTERSECT
  • ORDER BY
  • COMPUTE
  • FOR Clause
  • OPTION Clause
  • SELECT Examples (Transact-SQL)
• Modify data by using INSERT, UPDATE, and DELETE statements
  
  • INSERT (Transact-SQL)
  • INSERT Examples (Transact-SQL)
  • BULK INSERT (Transact-SQL)
  • DELETE (Transact-SQL)
  • Deleting Data in a Table
  • UPDATE (Transact-SQL)
  • Updating Data in a Table
  • TRUNCATE TABLE (Transact-SQL)
  • Row Constructors / Table Value Constructor
• Return data by using the OUTPUT clause
  
  • OUTPUT Clause (Transact-SQL)
  • DELETE
  • INSERT
  • UPDATE
  • INTO Clause
• Modify data by using MERGE statements
  
  • MERGE (Transact-SQL)
  • OUTPUT Clause (Transact-SQL)
  • DELETE
  • INSERT
  • UPDATE
  • Using Common Table Expressions
  • WITH common_table_expression (Transact-SQL)
  • Recursive Queries Using Common Table Expressions
  • TOP (Transact-SQL)
  • Optimizing MERGE Statement Performance
  • Inserting, Updating, and Deleting Data by Using MERGE
• Implement aggregate queries
  
  • AVG
  • CHECKSUM_AGG
  • COUNT
  • COUNT_BIG
  • MAX
  • MIN
  • SUM
  • STDEV
  • STDEVP
  • VAR
  • VARP
  • Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS
  • GROUPING SETS Equivalents
  • GROUP BY (Transact-SQL)
  • HAVING
  • Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS
  • GROUPING (Transact-SQL)
  • GROUPING_ID (Transact-SQL)
  • Troubleshooting GROUP BY Errors
  • OVER Clause (Transact-SQL)
• Combine datasets
  
  • Using APPLY (CROSS APPLY and OUTER APPLY)
  • FROM (Transact-SQL)
  • Join Fundamentals
  • Using Joins
  • Using Inner Joins
  • Using Outer Joins
  • Using Cross Joins
  • Using Self-Joins
  • Joining Three or More Tables
  • Null Values and Joins
  • UNION (Transact-SQL)
  • EXCEPT and INTERSECT (Transact-SQL)
    
    • left anti semi join
    • left semi join
  • Performing Semi-Joins with EXCEPT and INTERSECT
  • Using UNION, EXCEPT and INTERSECT with Other Transact-SQL Statements
• Apply built-in scalar functions
  
  • Configuration Functions
  • Cursor Functions
  • Date and Time Data Types and Functions
  • Mathematical Functions
  • Metadata Functions
  • Security Functions
  • String Functions
  • System Functions
  • System Statistical Functions
  • Text and Image Functions
  • CAST and CONVERT (Transact-SQL)
  • REPLACE (Transact-SQL)
  • LEN (Transact-SQL)
  • DATALENGTH (Transact-SQL)
  • CHARINDEX (Transact-SQL)
  • PATINDEX (Transact-SQL)