Programando SQL con SQL 2005 Express

1. Starting Microsoft SQL Server 2005

1.1. Starting Microsoft SQL Server 2005 and SQL Server 2005's Management Studio

1.2. Registering a Server

1.2.1. Types of Authentication

1.2.2. Server Properties

1.2.3. Server Folders

1.2.4. Attach – Detach a Database

1.3. Windows and Views on Management Studio

1.3.1. Object Explorer

1.3.1.1. Hierarchy Tree of Objects

1.3.1.2. Explain

1.3.2. Summary

1.3.3. Registered Servers

1.3.4. Template Explorer

1.3.5. Solution Explorer

1.3.6. Properties Window

1.3.7. Bookmark Window

1.3.8. Toolbox

1.3.9. Output

1.4. Manage a Database in Microsoft SQL Server 2005

1.4.1. Create Database

1.4.2. Delete Database

1.4.3. Attach-Detach

1.4.4. Backup and Restore

1.5. Creating Tables

1.5.1. Fields and Types

1.5.2. Properties

1.5.3. Identity

1.5.4. Indexes

1.5.5. Constraints

1.5.6. Primary Keys and Foreing Keys

1.6. Viewing Table Definitions

1.7. Diagrams

1.7.1. Relations

1.8. Modifying Table Definitions

1.9. Viewing Table Data

1.9.1. Modifying Table Data

1.10.Deleting a Table

1.11.Entering a SQL Query or Statement

1.12.Parsing a Query

1.13.Executing a Query

1.13.1. Create Drop Database

1.14.Data Types in SQL Server 2005

1.15.Creating a Table

1.15.1. Inserting Values into a Table

1.15.2. The UPDATE Command

1.15.3. The ALTER TABLE Command

1.15.4. The DELETE Command

1.16.Deleting a Table Saving a Query

1.17.Displaying the Results

1.18.Stopping Execution of a Long Query

1.19.Printing the Query and Results


2. Beginning SQL Commands in SQL Server

2.1. Displaying Data with the SELECT Statement

2.2. Displaying or SELECTing Rows or Tuples from a Table

2.3. Parentheses in SQL Expressions

2.4. The COUNT Function

2.5. The ROWCOUNT Function

2.6. Using Aliases

2.7. Synonyms

2.8. Adding Comments to SQL Statements

2.9. The DISTINCT and ALL Predicates

2.10.The INSERT INTO . . . SELECT Statement

3. Joins

3.1. The JOIN

3.2. The Cartesian Product

3.3. Equi-Joins and Non-Equi-Joins

3.4. Self Joins

3.5. Using ORDER BY with a Join

3.6. Joining More Than Two Tables

3.7. The OUTER JOIN

4. Functions

4.1. Aggregate Functions

4.2. Row-Level Functions

4.3. Other Functions

4.4. String Functions

4.5. CONVERSION Functions

4.6. Mixing Datatypes: CAST and CONVERT

4.7. DATE Functions

5. Set Operations

5.1. Introducing Set Operations

5.2. The UNION Operation

5.3. The UNION ALL Operation

5.4. Handling UNION and UNION ALL Situations with an Unequal Number of Columns

5.5. The IN and NOT..IN Predicates

5.6. The Difference Operation

5.7. The Union and the Join

5.8. A UNION Used to Implement a Full Outer Join

6. Joins Versus Subqueries

6.1. Subquery with an IN Predicate

6.2. The Subquery as a Join

6.3. When the Join Cannot Be Turned into a Subquery

6.4. More Examples Involving Joins and IN

6.5. Using Subqueries with Operators

7. Aggregation and GROUP BY

7.1. The GROUP BY Clause

7.2. The HAVING Clause

7.3. GROUP BY and HAVING: Aggregates of Aggregates

7.4. Auditing in Subqueries

7.5. Nulls Revisited

8. Correlated Subqueries

8.1. Noncorrelated Subqueries

8.2. Correlated Subqueries

8.3. Existence Queries and Correlation

8.4. EXISTS

9. Indexes and Constraints on Tables

9.1. The "Simple" CREATE TABLE

9.2. Indexes

9.3. Constraints

10. Views

10.1.Simple Views

10.2.Views as Filters

10.3.More Complex Views

10.4.Using a View to Change Data — Before INSTEAD OF Triggers

10.5.Editing Views with T-SQL

10.6.Dropping Views

10.7.Creating and Editing Views in the Management Studio

10.8.Editing Views in the Management Studio

10.9.Auditing: Displaying Existing Code

10.10. Protecting Code: Encrypting Views

10.11. About Schema Binding 309

10.12. Making Your View Look Like a Table with VIEW_METADATA

10.13. Indexed (Materialized) Views


11. Writing Scripts and Batches

11.1.Script Basics

11.2.The USE Statement

11.3.Declaring Variables

11.4.Temporary Tables

11.5.Using IDENTITY

11.6.Using ROWCOUNT

11.7.Batches

11.8.Errors in Batches

11.9.When to Use Batches

11.10. Dynamic SQL: Generating Your Code On-the-Fly with the EXEC Command

11.11. The Gotchas of EXEC

12. Stored Procedures

12.1.Creating the Sproc: Basic Syntax

12.2.An Example of a Basic Sproc

12.3.Changing Stored Procedures with ALTER

12.4.Dropping Sprocs

12.5.Parameterization

12.6.Declaring Parameters

12.7.Control-of-Flow Statements

12.8.The IF . . . ELSE Statement

12.9.The CASE Statement

12.10. Looping with the WHILE Statement

12.11. The WAITFOR Statement

12.12. TRY/CATCH Blocks

12.13. Confirming Success or Failure with Return Values

12.14. How to Use RETURN

12.15. Dealing with Errors

12.16. The Way We Were . . .

12.17. Handling Errors Before They Happen

12.18. Manually Raising Errors

12.19. Adding Your Own Custom Error Messages

12.20. What a Sproc Offers

12.21. Creating Callable Processes

12.22. Using Sprocs for Security

12.23. Sprocs and Performance

12.24. Extended Stored Procedures (XPs)