Technology Driven IT Training Longstanding IT Technologists Computer Education Techniques
Distance-learning - MS SQL Server: Database Design and Implementation

Home

distance-learning center

Computer-Device_Tethered-Distance-learning Distance-learning-Building

Professional Distance-learning

IT Training Services



PCECS08A

MS SQL Server: Database Design and Implementation
DURATION: 4 days

FEE: $1,580

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.

AUDIENCE:

  • Support Personnel
  • Database Administrator
  • System Designer
  • System Analyst
  • IT Professional
 
   

DATES:

CONTENT AND OBJECTIVES:
    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.

 
 
    Web-based Training   Blended Learning    
Synchronous
Instruction
Asynchronous
Instruction
 

 

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.