PCECS08A

DATES:

1. Getting Started
   • Instructor to present an examination of MS SQL Server architecture.
2. 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.
3. 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.
4. 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.
5. Implementing Data Integrity
   • In MS SQL Server databases data integrity is implemented by using constraints in conjunction with triggers.
6. Planning Indexes
   • Determine which tables need indexes and which indexes need to be implemented.
   • Determine the type of index and then design and implement an index.
7. 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.
8. Implementing Views
   • Code and test several different views. Some of the views will be updatable; other views will retrieve data from the catalog.
9. Stored Procedures
   • 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.
10. User-defined Functions
    • Code a multi-statement table function which is similar to a stored procedure; the multi-statement table will return a table.
11. Triggers
    • 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.
12. Multiple Servers
    • The instructor will present an examination of multiple server configurations.
13. Query Performance
    • 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.
14. Managing Transactions and Locks
    • Query the database for the status of locks for a specified table.

↓