Sql Server

  • An introduction to relational databases and SQL
  • An introduction to client/server systems
  • The hardware components of a client/server system
  • The software components of a client/server system
  • Other client/server system architectures
  • An introduction to the relational database model
  • How a database table is organized
  • How the tables in a relational database are related
  • How the columns in a table are defined
  • How relational databases compare to other data models
  • An introduction to SQL and SQL-based systems
  • A brief history of SQL
  • A comparison of Oracle, DB2, MySQL, and SQL Server
  • The Transact-SQL statements
  • An introduction to the SQL statements
  • Typical statements for working with database objects
  • How to query a single table
  • How to join data from two or more tables
  • How to add, update, and delete data in a table
  • SQL coding guidelines
  • How to work with other database objects
  • How to work with views
  • How to work with stored procedures, triggers, and user-defined functions
  • How to use SQL from an application program
  • Common data access models
  • How to use ADO.NET from a .NET application
  • Visual Basic code that retrieves data from a SQL Server database
  • C# code that retrieves data from a SQL Server database
  • How to use the Management Studio
  • An introduction to SQL Server 2012
  • A summary of the SQL Server 2012 tools
  • How to start and stop the database engine
  • How to enable remote connections
  • An introduction to the Management Studio
  • How to connect to a database server
  • How to navigate through the database objects
  • How to manage the database files
  • How to attach a database
  • How to detach a database
  • How to back up a database
  • How to restore a database
  • How to set the compatibility level for a database
  • How to view and modify the database
  • How to view the database diagrams
  • How to view the column definitions of a table
  • How to modify the column definitions
  • How to view the data of a table
  • How to modify the data of a table
  • How to work with queries
  • How to enter and execute a query
  • How to handle syntax errors
  • How to open and save queries
  • An introduction to the Query Designer
  • How to use Books Online
  • How to display Books Online
  • How to look up information
  • How to retrieve data from a single table
  • An introduction to the SELECT statement
  • The basic syntax of the SELECT statement
  • SELECT statement examples
  • How to code the SELECT clause
  • How to code column specifications
  • How to name the columns in a result set
  • How to code string expressions
  • How to code arithmetic expressions
  • How to use functions
  • How to use the DISTINCT keyword to eliminate duplicate rows
  • How to use the TOP clause to return a subset of selected rows
  • How to code the WHERE clause
  • How to use comparison operators
  • How to use the AND, OR, and NOT logical operators
  • How to use the IN operator
  • How to use the BETWEEN operator
  • How to use the LIKE operator
  • How to use the IS NULL clause
  • How to code the ORDER BY clause
  • How to sort a result set by a column name
  • How to sort a result set by an alias, an expression, or a column number
  • How to retrieve a range of selected rows
  • How to retrieve data from two or more tables
  • How to work with inner joins
  • How to code an inner join
  • When and how to use correlation names
  • How to work with tables from different databases
  • How to use compound join conditions
  • How to use a self-join
  • Inner joins that join more than two tables
  • How to use the implicit inner join syntax
  • How to work with outer joins
  • How to code an outer join
  • Outer join examples
  • Outer joins that join more than two tables
  • Other skills for working with joins
  • How to combine inner and outer joins
  • How to use cross joins
  • How to work with unions
  • The syntax of a union
  • Unions that combine data from different tables
  • Unions that combine data from the same table
  • How to use the EXCEPT and INTERSECT operators
  • How to code summary queries
  • How to work with aggregate functions
  • How to code aggregate functions
  • Queries that use aggregate functions
  • How to group and summarize data
  • How to code the GROUP BY and HAVING clauses
  • Queries that use the GROUP BY and HAVING clauses
  • How the HAVING clause compares to the WHERE clause
  • How to code complex search conditions
  • How to summarize data using SQL Server extensions
  • How to use the ROLLUP operator
  • How to use the CUBE operator
  • How to use the GROUPING SETS operator
  • How to use the OVER clause
  • How to code subqueries
  • An introduction to subqueries
  • How to use subqueries
  • How subqueries compare to joins
  • How to code subqueries in search conditions
  • How to use subqueries with the IN operator
  • How to compare the result of a subquery with an expression
  • How to use the ALL keyword
  • How to use the ANY and SOME keywords
  • How to code correlated subqueries
  • How to use the EXISTS operator
  • Other ways to use subqueries
  • How to code subqueries in the FROM clause
  • How to code subqueries in the SELECT clause
  • Guidelines for working with complex queries
  • A complex query that uses subqueries
  • A procedure for building complex queries
  • How to work with common table expressions
  • How to code a CTE
  • How to code a recursive CTE
  • How to insert, update, and delete data
  • How to create test tables
  • How to use the SELECT INTO statement
  • How to use a copy of the database
  • How to insert new rows
  • How to insert a single row
  • How to insert multiple rows
  • How to insert default values and null values
  • How to insert rows selected from another table
  • How to modify existing rows
  • How to perform a basic update operation
  • How to use subqueries in an update operation
  • How to use joins in an update operation
  • How to delete existing rows
  • How to perform a basic delete operation
  • How to use subqueries and joins in a delete operation
  • How to merge rows
  • How to perform a basic merge operation
  • How to code more complex merge operations
  • How to work with data types
  • A review of the SQL data types
  • Data type overview
  • The numeric data types
  • The string data types
  • The date/time data types
  • The large value data types
  • How to convert data
  • How data conversion works
  • How to convert data using the CAST function
  • How to convert data using the CONVERT function
  • How to use the TRY_CONVERT function
  • How to use other data conversion functions
  • How to use functions
  • How to work with string data
  • A summary of the string functions
  • How to solve common problems that occur with string data
  • How to work with numeric data
  • A summary of the numeric functions
  • How to solve common problems that occur with numeric data
  • How to work with date/time data
  • A summary of the date/time functions
  • How to parse dates and times
  • How to perform operations on dates and times
  • How to perform a date search
  • How to perform a time search
  • Other functions you should know about
  • How to use the CASE function
  • How to use the IIF and CHOOSE functions
  • How to use the COALESCE and ISNULL functions
  • How to use the GROUPING function
  • How to use the ranking functions
  • How to use the analytic functions
  • How to design a database
  • How to design a data structure
  • The basic steps for designing a data structure
  • How to identify the data elements
  • How to subdivide the data elements
  • How to identify the tables and assign columns
  • How to identify the primary and foreign keys
  • How to enforce the relationships between tables
  • How normalization works
  • How to identify the columns to be indexed
  • How to normalize a data structure
  • The seven normal forms
  • How to apply the first normal form
  • How to apply the second normal form
  • How to apply the third normal form
  • When and how to denormalize a data structure
  • How to create and maintain databases, tables, and sequences with SQL statements
  • An introduction to DDL
  • The SQL statements for data definition
  • Rules for coding object names
  • How to create databases, tables, and indexes
  • How to create a database
  • How to create a table
  • How to create an index
  • How to use snippets to create database objects
  • How to use constraints
  • An introduction to constraints
  • How to use check constraints
  • How to use foreign key constraints
  • How to change databases and tables
  • How to delete an index, table, or database
  • How to alter a table
  • How to work with sequences
  • How to create a sequence
  • How to use a sequence
  • How to delete a sequence
  • How to alter a sequence
  • The script used to create the AP database
  • How the script works
  • How the DDL statements work
  • How to use the Management Studio for database design
  • How to work with a database
  • How to create a database
  • How to delete a database
  • How to work with tables
  • How to create, modify, or delete a table
  • How to work with foreign key relationships
  • How to work with indexes and keys
  • How to work with check constraints
  • How to examine table dependencies
  • How to generate scripts
  • How to generate scripts for databases and tables
  • How to generate a change script when you modify a table
  • How to work with views
  • An introduction to views
  • How views work
  • Benefits of using views
  • How to create and manage views
  • How to create a view
  • Examples that create views
  • How to create an updatable view
  • How to delete or modify a view
  • How to use views
  • How to update rows through a view
  • How to insert rows through a view
  • How to delete rows through a view
  • How to use the catalog views
  • How to use the View Designer
  • How to create or modify a view
  • How to delete a view
  • How to code scripts
  • An introduction to scripts
  • How to work with scripts
  • The Transact-SQL statements for script processing
  • How to work with variables and temporary tables
  • How to work with scalar variables
  • How to work with table variables
  • How to work with temporary tables
  • A comparison of the five types of Transact-SQL table objects
  • How to control the execution of a script
  • How to perform conditional processing
  • How to test for the existence of a database object
  • How to perform repetitive processing
  • How to handle errors
  • How to use surround-with snippets
  • Advanced scripting techniques
  • How to use the system functions
  • How to change the session settings
  • How to use dynamic SQL
  • A script that summarizes the structure of a database
  • How to use the SQLCMD utility
  • How to code stored procedures, functions, and triggers
  • Procedural programming options in Transact-SQL
  • Scripts
  • Stored procedures, user-defined functions, and triggers
  • How to code stored procedures
  • An introduction to stored procedures
  • How to create a stored procedure
  • How to declare and work with parameters
  • How to call procedures with parameters
  • How to work with return values
  • How to validate data and raise errors
  • A stored procedure that manages insert operations
  • How to pass a table as a parameter
  • How to delete or change a stored procedure
  • How to work with system stored procedures
  • How to code user-defined functions
  • An introduction to user-defined functions
  • How to create a scalar-valued function
  • How to create a simple table-valued function
  • How to create a multi-statement table-valued function
  • How to delete or change a function
  • How to code triggers
  • How to create a trigger
  • How to use AFTER triggers
  • How to use INSTEAD OF triggers
  • How to use triggers to enforce data consistency
  • How to use triggers to work with DDL statements
  • How to delete or change a trigger
  • How to work with cursors
  • How to use cursors in SQL Server
  • An introduction to cursors
  • The seven types of SQL Server cursors
  • SQL statements for cursor processing
  • How to use cursors to retrieve data
  • How to declare a cursor
  • How to retrieve a row using a cursor
  • How to use the @@FETCH_STATUS system function
  • How to use the @@CURSOR_ROWS system function
  • How to modify data through a cursor
  • How to use the cursor concurrency options
  • How to update or delete data through a cursor
  • Additional cursor processing techniques
  • How to use cursors with dynamic SQL
  • How to code Transact-SQL cursors for use by an application program
  • How to manage transactions and locking
  • How to work with transactions
  • How transactions maintain data integrity
  • SQL statements for handling transactions
  • How to work with nested transactions
  • How to work with save points
  • An introduction to concurrency and locking
  • How concurrency and locking are related
  • The four concurrency problems that locks can prevent
  • How to set the transaction isolation level
  • How SQL Server manages locking
  • Lockable resources and lock escalation
  • Lock modes and lock promotion
  • Lock mode compatibility
  • How to prevent deadlocks
  • Two transactions that deadlock
  • Coding techniques that prevent deadlocks
  • How to manage database security
  • How to work with SQL Server login IDs
  • An introduction to SQL Server security
  • How to change the authentication mode
  • How to create login IDs
  • How to delete or change login IDs or passwords
  • How to work with database users
  • How to work with schemas
  • How to work with permissions
  • How to grant or revoke object permissions
  • The SQL Server object permissions
  • How to grant or revoke schema permissions
  • How to grant or revoke database permissions
  • How to grant or revoke server permissions
  • How to work with roles
  • How to work with the fixed server roles
  • How to work with user-defined server roles
  • How to display information about server roles and role members
  • How to work with the fixed database roles
  • How to work with user-defined database roles
  • How to display information about database roles and role members
  • How to deny permissions granted by role membership
  • How to work with application roles
  • How to manage security using the Management Studio
  • How to work with login IDs
  • How to work with the server roles for a login ID
  • How to assign database access and roles by login ID
  • How to assign user permissions to database objects
  • How to work with database permissions
  • How to work with XML
  • An introduction to XML
  • An XML document
  • An XML schema
  • How to work with the xml data type
  • How to store data in the xml data type
  • How to work with the XML Editor
  • How to use the methods of the xml data type
  • An example that parses the xml data type
  • Another example that parses the xml data type
  • How to work with XML schemas
  • How to add an XML schema to a database
  • How to use an XML schema to validate the xml data type
  • How to view an XML schema
  • How to drop an XML schema
  • Two more skills for working with XML
  • How to use the FOR XML clause of the SELECT statement
  • How to use the OPENXML statement
  • How to work with BLOBs
  • An introduction to BLOBs
  • Pros and cons of storing BLOBs in files
  • Pros and cons of storing BLOBs in a column
  • When to use FILESTREAM storage for BLOBs
  • How to use SQL to work with a varbinary(max) column
  • How to create a table with a varbinary(max) column
  • How to insert, update, and delete binary data
  • How to retrieve binary data
  • A .NET application that uses a varbinary(max) column
  • The user interface for the application
  • The event handlers for the form
  • A data access class that reads and writes binary data
  • How to use FILESTREAM storage
  • How to enable FILESTREAM storage on the server
  • How to create a database with FILESTREAM storage
  • How to create a table with a FILESTREAM column
  • How to insert, update, and delete FILESTREAM data
  • How to retrieve FILESTREAM data
  • A data access class that uses FILESTREAM storage
  • An introduction to CLR integration
  • An introduction to CLR integration
  • How CLR integration works
  • The five types of CLR objects
  • When to use CLR objects
  • How to enable CLR integration
  • How to use Visual Studio to work with CLR objects
  • How to start a SQL Server Database project
  • How to specify the type of CLR object
  • How to enter and edit the code for CLR objects
  • The code for a CLR object
  • How to create a database for testing
  • How to compile and deploy CLR objects
  • How to test and debug a CLR object
  • How to use SQL to work with CLR objects
  • How to deploy an assembly
  • How to deploy a CLR object
  • How to drop an assembly
  • How to code CLR stored procedures, functions, and triggers
  • How to work with CLR stored procedures
  • How to use the SqlTypes namespace to map data types
  • How to declare a stored procedure
  • How to create a connection
  • How to use the SqlPipe object to return data
  • How to use output parameters to return data
  • How to return an integer value
  • How to raise an error
  • A stored procedure that manages insert operations
  • A script that calls the stored procedure
  • How to work with CLR functions
  • How to declare a function
  • How to work with the SqlFunction attribute
  • A scalar-valued function that returns an amount due
  • A table-valued function that returns a table with two columns
  • How to work with CLR triggers
  • How to declare a trigger
  • How to work with the SqlTrigger attribute
  • A trigger that updates a column
  • How to use the SqlTriggerContext object
  • A trigger that works with DDL statements
  • How to code aggregate functions and user-defined types
  • How to work with aggregate functions
  • How to declare an aggregate
  • How to work with the SqlUserDefinedAggregate attribute
  • An aggregate that returns a trimmed average
  • An aggregate that returns a comma-delimited string
  • How to work with user-defined types
  • How to declare a user-defined type
  • How to work with the SqlUserDefinedType attribute
  • A user-defined type for an email address
  • SQL that works with a user-defined type