Curso SQL Server 2008 for Developers

(1)

Curso SQL Server 2008 for Developers 1 Desarrollo y Capacitación en Internet S. A. de C. V. Altadena No 26 Col. Nápoles Del. Benito Juárez C.P 03810

www.DCInternet.com.mx

Curso SQL Server 2008 for Developers

Objetivos:

 Aprenderás a crear joins interiores y exteriores complejos, consultas

agrupadas, y subconsultas

 Aprenderás a manejar los diferentes tipos de datos y sabrás cómo

manejar los problemas más comunes que se ejecutan en varios tipos de

datos.

 Trabajarás con datos XML

 Aprovecharás las características back-end de base de datos, como son

las vistas, scripts, procedimientos almacenados y triggers.

 Aprenderás a lidiar con funciones de control de base de datos como

cursores, transacciones, y el bloqueo.

 Practicarás los conceptos básicos de diseño de bases de datos e

implementación utilizando el Management Studio o SQL DDL .

 Sabrás cómo utilizar la característica de integración CLR para crear

objetos de base de datos usando C # o código de Visual Basic.

 Aprenderás sobre la instrucción MERGE , que te permite usar una sola

declaración para hacer varias actualizaciones e inserciones.

 Trabajarás con fechas y horas. Los tipos de datos date, time y

datetimeoffset te darán más flexibilidad y precisión.

 Aprenderás a crear un tipo de datos tabla definido por el usuario para

pasar una tabla como un parámetro a un procedimiento almacenado o

una función.

(2)

DCInternet

Contenido

Chapter 1 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

data models

 An introduction to SQL and SQL-based systems

 A brief history of SQL

 A comparison of Oracle, DB2, and SQL Server

 Other SQL-based systems  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

 Perspective

Chapter 2 How to use the Management Studio

 An introduction to SQL Server 2008  A summary of the SQL Server 2008 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 start Books Online  How to look up information

Chapter 3 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

(3)

DCInternet

 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

Chapter 4 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  How to use the implicit outer join syntax  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

Chapter 5 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

Chapter 6 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

Chapter 7 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

(4)

DCInternet

Chapter 8 How to work with data types and functions

 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 other data conversion 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  How to use 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 COALESCE and ISNULL functions

 How to use the GROUPING function  How to use the ranking functions Chapter 9 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

Chapter 10 How to create and maintain databases and tables 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 constraints  An introduction to constraints  How to use check constraints  How to use foreign key contraints  How to change databases and tables  How to delete an index, table, or database  How to alter a table

 The script used to create the AP database  How the script works

 How the DDL statements work Chapter 11 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 foriegn 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

Chapter 12 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

(5)

DCInternet

Chapter 13 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

 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

Chapter 14 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 Chapter 15 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

Chapter 16 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 Chapter 17 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

(6)

DCInternet

 How to grant or revoke permission to use an object

 The SQL Server object permissions  How to grant or revoke permission to use

the objects in a schema

 How to grant or revoke database permissions

 How to work with roles

 How to work with the fixed server roles  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 558

 How to work with application roles  How to manage security using the

Management Studio  How to create login IDs

 How to modify or delete login IDs  How to work with server roles

 How to assign database access and roles by login ID

 How to assign database role memberships  How to assign database user permissions to

database objects  How to manage schemas

 How to manage user-defined roles  How to work with database permissions Chapter 18 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

Chapter 19 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 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 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 Chapter 20 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 proecedure  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 proecedure that manages insert operations

 A script that calls the stored proecedure  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 thtat 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

(7)

DInternet

Chapter 21 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 retuns 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

Curso SQL Server 2008 for Developers