Creating SQL Server Databases
Creating a new database. Using file groups. Expanding and shrinking a database. Size and place of the transaction log.
Creating tables using the Management Studio and in Script. Creating a database diagram. Data types explained. Using the XML Data Type in tables.
Indexes, Constraints and Relationships
Creating and managing constraints and relationships. Indexes – Clustered and Non-clustered. How SQL Server optimises queries using indexes.
The SELECT statement. Column aliases and computed columns. ORDER BY and WHERE clause. Inner and outer joins, Unions. DISTINCT, GROUP BY and HAVING clauses. APPLY and PIVOT operators. Using XQuery and XPath when querying XML data.
Creating, using, altering and managing views.
Using and Creating Functions
Understanding and using Built-in Functions. Scalar and table-valued functions. Using user-defined functions. Handling and Converting return types.
Creating and managing Stored Procedures. Declaring variables and parameters. Returning data and raising errors. By Value and By Reference Semantics in Stored Procedures.
Using INSERT, UPDATE and DELETE statements. Using functions to modify data. Using XML DML for changing data in XML data columns.
The Transact SQL (T-SQL) Language Operators. Comments. Functions (date and time, mathematical, aggregate string and system). Declaring variables. Conditional processing and looping. The CASE command. Comments and code structuring.
Triggers explained, and guidelines their use. Update, insert and delete triggers. AFTER and INSTEAD OF triggers. DDL triggers.
Questions and Answers
“The trainer’s knowledge of the subject matter was impeccable and this, matched with the very relevant course materials, ensured the course was a big success. We would certainly use Software Paths again for training in the future.”