PRIVATE GROUP FEE:
$ Based upon a class size of six students.
Scheduling can be adjusted to employee work hours and incorporate client assignments.
SMALL CLASS SIZE:
There is a surcharge associated with training two or fewer students.
CONTENT AND OBJECTIVES:
Instructor to present an examination of MS SQL Server architecture.
Programming SQL Server
Use the SQL Enterprise Manager to create an entity-relationship diagram; the instructor will provide the XYZ database.
1- Code T-SQL in pubs to use the ytd_sales column; which is an int column in table titles. 2- Convert it to a char(20) column to be used with the LIKE predicate for locating records that start with 15 in ytd_sales. 3- Get the average prices of business books; this needs to be done without duplicate values. 4- Use a wildcard to find the position at which the pattern “won_erful” begins in a row with title_id = ‘TC3218’of the notes column in the titles table. The underscore is a wildcard representing any character. 5- Use the PATINDEX() string function.
Creating and Managing Databases
1- Create a database using T-SQL scripts and the SQL Enterprise Manager. 2- Set the new database to the current database. 3- Get a list of all databases in SQL Server. 4- List the database states. 5- Set the database to a single user.
Creating Datatypes and Tables
Use the BCP utility to populate the tables in the XYZ database.
Use the DBCC command to determine the current value of the identity value and use it to reset the value.
Generate the DDL for the XYZ database.
Implementing Data Integrity
In MS SQL Server databases data integrity is implemented by using constraints in conjunction with triggers.
Determine which tables need indexes and which indexes need to be implemented.
Determine the type of index and then design and implement an index.
Creating and Maintaining Indexes
Code a query which retrieves all table names and limits the table list to user tables. 1- Use the USER_NAME() function and the OwnerID property to fetch the name of the user who owns the table. 2- Add the INDEXPROPERTY() function’s IsClustered property to the query; this will determine the index type. 3- The IsUnique property is used to determine whether the index is unique. 4- As part of completing the task, use the STATS_DATE() system function to provide the date of the last statistics update.
Code and test several different views. Some of the views will be updatable; other views will retrieve data from the catalog.
Code a procedure that accepts two arguments.t; the second argument has a default value.
Based on the argument(s) passed, query the database and pass back a calculated value.
Code a multi-statement table function which is similar to a stored procedure; the multi-statement table will return a table.
When a NULL value is concatenated with a string, the result will be a null value.
Modify an existing trigger to properly report NULL values.
The instructor will present an examination of multiple server configurations.
The recommended place to start tuning an individual query is at the execution plan. The execution plan describes the sequence of operations, physical and logical, that SQL Server will perform to fulfill the query and produce the required resultset.
Use Management Studio to access the Display Actual Execution Plan and Display Estimated Execution Plan features; it will present the plan graphically.
Managing Transactions and Locks
Query the database for the status of locks for a specified table.
Effective February 27, 2012, the course dates listed on the SYS-ED schedules only will be available based upon approved and authorized user ID.
Copyright Acknowledgement: The software product(s) listed in this outline are owned and copyrighted by their respective companies. SYS-ED makes no representation regarding ownership in any of the software products that we train on.
SYS-ED courses are not intended for or open to the general public. They are intended for employees of Fortune 1000 companies, government municipalities, consulting companies, software, companies, healthcare providers. and mid-sized businesses. Individuals attending such courses will be required to execute a statement acknowledging that the employer will be paying for the SYS-ED course and that the employer has a licensed version of the software.