.NET Code Catto Blogger Blog: SQL 2008 DB Dev 70-433 Query Fundamentals

Hey Now Everybody,

This is a review of the second objective on the Microsoft exam 70-433 ‘Query Fundamentals’

While reviewing for the Microsoft exams it’s enjoyable to combine the three sections:
1. Self Paces Book’s chapters content
2. Code, SQL Scripts
3. Concepts from Questions of the Microsoft Training Kit

3 Working with Query Fundamentals (21 percent)
3.`1 Query Data by using Select statements c1.2
3.2 Modify Data by using Insert, Update & Delete statements c2.1
3.3 Return data by using the OUTPUT clause c2.2
3.4 Modify data by using MERGE statement c2.2
3.5 Implement aggregate queries (LINQ) c1.3
3.6 Combine datasets c1.4
3.7 Apply built in scalar functions c1.5

3.`1 Query Data by using Select statements c1.2

Select Statement can be used to retrieve data from tables & views.
Select results sets can be filtered by adding a WHERE clause
Select results sets can be sorted by usinge the ORDER BY
Aliases & string literals can be used to manipulate & format result sets.

3.2 Modify Data by using Insert, Update & Delete statements c2.1
The Join clause allows us to retrieve columns from related tables
JOIN types include INNER< LEFT OUTER, RIGHT OUTER, FULL OUTER & CROSS
Join operators can combine 2 or more tables
Tables can be joined to itself by defining different aliases for each table
3.3 Return data by using the OUTPUT clause c2.2
The OUTPUT clause allows us to redirect info to the calling app, or to an object such as a table, about the insert, update or delete statement
3.4 Modify data by using MERGE statement c2.2
MERGE statement allows us to perform DML actions on a table based on matches found on a source table
3.5 Implement aggregate queries (LINQ) c1.3
Aggregate functions perform calculations on expressions
Use the GROUP BY clause when aggregates should be applied based on the data in rows not the whole table
Include all columns listed in a SELECT WHERE or ORDER by clause in the GROUP By clause
ROLLUP & CUBE to provide summary info
Use the GROUPING function to show which rows holds summary data provided by the rollup & cube operators
Grouping Sets to provide improvements to our Group by queries
3.6 Combine datasets c1.4
UNION operator combines result sets from 2 or more Select statements
EXCEPT operator returns rows that are in the left Select statements
Intersect operator returns only rows that are shared by the two select operators
The APPLY operator uses the results from a query as input to apply a function to each row in the results.
Outer apply returns all rows from the outer table along with the results returned by the function when rows match.
CROSS Apply returns only the rows from the outer tables which matches the function results.
3.7 Apply built in scalar functions c1.5
Built in functions to provide more meaningful results sets
date & time functions to return date info
String functions to format or return info about string expressions.

Code:

 
/****** Script for SelectTopNRows command from SSMS ******/   
SELECT TOP 000 [ApplicationId]     
    ,[UserId]    
    ,[UserName]    
    ,[LoweredUserName]    
    ,[MobileAlias]    
    ,[IsAnonymous]    
    ,[LastActivityDate]    
FROM [ccatto_aspnetdb].[dbo].[aspnet_Users]

INSERT


INSERT  INTO dbo.aspnet_Membership    
 ( ApplicationId
    , UserId 
    , Password
    , PasswordSalt
    , Email
     )    
VALUES ( @ApplicationId 
    , @UserId
    , @Password 
    , @PasswordSalt 
    , @Email 
    )

Update

UPDATE dbo.aspnet_Users
SET LastActivityDate = @CreateDate
WHERE @UserId = UserId

Order by

SELECT TOP (200) ApplicationId, UserId, UserName, LoweredUserName, MobileAlias, IsAnonymous, LastActivityDate
FROM dbo.aspnet_Users
ORDER BY UserName

Question Concepts
LEFT JOIN specifies that all rows from the left table are returned whether they meet the join criteria or not. If the join criteria are not met, output columns that correspond to the right table are set to NULL.

The OUTPUT INTO clause is a simple way to insert deleted rows into another new table. A temporary table can be populated
CONVERT(char, GETDATE(), 102) retrieves the current date with the GETDATE() function & converts it to ANSI format.
The MERGE statement performs INSERT, UPDATE, or DELETE operations on a target table based on the results of a JOIN with a source table.
The SELECT INTO statement creates a new table & populates it with the result set of the SELECT statement.

AVG is a built-in aggregate function that calculates the average value for a column. Tyou
The LEN() function returns the number of characters in a string expression

Here are the skills measured in detail:

Working with Query Fundamentals (21 percent)

Query data by using SELECT statements.
LIKE
, WHERE
, ORDER BY
, INTO
Modify data by using INSERT, UPDATE UPDATE, and DELETE statements
. row constructors
, DELETE FROM FROM
, UPDATE FROM
, TRUNCATE TABLE
Return data by using the OUTPUT clause.
INSERTED
, DELETED
, INTO
Modify data by using MERGE statements.
INSERTED
, DELETED
, OUTPUT
Implement aggregate queries.
built-in aggregate functions
, GROUPING SETS
, GROUP BY
, HAVING
Combine datasets.
CROSS APPLY
, OUTER APPLY
, all join types
; UNION
, UNION ALL
, INTERSECT
, EXCEPT
Apply built-in scalar functions.
CAST and CONVERT
; REPLACE
; LEN and DATALENGTH
; PATINDEX and CHARINDEX