Home > Store

Sams Teach Yourself Transact-SQL in 21 Days, 2nd Edition

Register your product to gain access to bonus material or receive a coupon.

Sams Teach Yourself Transact-SQL in 21 Days, 2nd Edition

Book

  • Your Price: $35.99
  • List Price: $44.99
  • Usually ships in 24 hours.

About

Features

  • NEW -

Description

  • Copyright 2001
  • Dimensions: 7-3/8" x 9-1/8"
  • Pages: 640
  • Edition: 2nd
  • Book
  • ISBN-10: 0-672-31967-5
  • ISBN-13: 978-0-672-31967-9

Sams Teach Yourself Transact-SQL in 21 Days, 2E will teach programmers how to develop Transact-SQL queries. There will be a focus on providing methods for improving productivity without a reducing performance. Specifically, the reader will:Learn Transact-SQL syntax Learn how to add, delete, and modifying data using Transact-SQL Understand coding standards Review variations from ANSI-standard SQL Be presented with basic server operations. Recognize performance issues with queries. This book will also include:Constructs such as CUBE, ROLLUP, CASE, and JOIN. Techniques to solve complex problems How the server uses indexes Methods to write (correctly) stored procedures and triggers. Templates of procedures and triggers (reference) Advanced Topics such as: Outer and self joins Temporary tables Sub-queries.

Downloads

Downloads


Transact-SQL code Listings (49.7 kb) -- sourcecode.exe

Sample Content

Table of Contents

INTRODUCTION.

WEEK 1. WORKING WITH ROWS AND COLUMNS.

Day 1. Introduction to SQL and the Query Analyzer.

SQL and SQL Server. What Is T-SQL? Retrieving Data with Query Analyzer.

Installing the Query Analyzer. Logging In to and Changing the Database. The Query Toolbar. Executing Your First Query. Understanding SELECT and FROM. Using the Query Analyzer Editor. Using Query Analyzer to Learn about Tables.

Introducing SQL Server 2000 Enterprise Manager.

Registering a Server. Seeing How Enterprise Manager Works.

Summary. Q&A. Workshop.

Quiz. Exercise.

Day 2. Filtering and Sorting Data.

Filtering Data with WHERE Clauses.

Matching Column Data with Equality Statements. Matching Rows Using Not Equals (!= or <>). Searching for Numeric Values.

Combining Conditions with OR and AND.

The AND Condition. Combining AND and.

Searching for Ranges of Values with Inequalities (>, <, BETWEEN).

Combining Inequality Conditions. Searching for Ranges of Values with BETWEEN. Using Inequalities with Strings.

Getting Correct Results from Date Comparisons.

Dates in Other Languages and Formats.

Looking for Values in a List: The IN Operator.

Using the NOT IN Operator.

Wildcard Searches with LIKE.

The NOT LIKE Operator. Searching for Wildcard Characters.

Summary of Search Conditions. Sorting Data (ORDER BY).

Sorting by One Column. Sorting by Multiple Columns. Sorting by Column Number.

Retrieving Unique Rows.

Using DISTINCT to Find Unique Rows. Avoiding DISTINCT.

Summary. Q&A. Workshop.

Quiz. Exercises.

Day 3. Working with Columns.

Column Characteristics.

Data Types. Data Type Precedence. Using Null Data.

Changing Result Sets.

Using Column Aliases. Using String Constants. Using Operators. Using Functions. Using String Functions. Using Arithmetic Functions. Using Date Functions. Using the CASE Statement.

Summary. Q&A. Workshop.

Quiz. Exercises.

Day 4. Working with Row Aggregates and Grouping.

Working with Aggregate Functions.

Using SUM() and AVG(). Using MIN() and MAX(). Using Aggregates in a WHERE Clause. Using COUNT(). Implementing Error Handling for Aggregates. Understanding Aggregates and Null. Computing Aggregates with DISTINCT. Reviewing Aggregates.

Grouping Data.

GROUP BY Clauses. Grouping with all. Using Multi-Level Grouping. Grouping by Complex Expressions. Using HAVING to Remove Grouped Rows. Combining Subtotals and Totals with ROLLUP and CUBE.

Creating Pivot Tables. Summary. Q&A. Workshop.

Quiz. Exercises.

Day 5. Joining Tables.

Understanding Joins.

Creating and Using Entity Relationship Diagrams (ERDs). Writing Your First Join Using an Inner Join.

Understanding More about Joins.

Introducing Cross Joins. Understanding More about Joins. Using Table Aliases. Working with Multi-Table Joins. Joining Tables with Multi-Part Keys.

Writing Outer Joins. Joining a Table to Itself.

Using Parent-Child Relationships. Naming Standards in Self-Joins.

Using Union to Merge Result Sets.

SQL Keywords and UNION.

Summary. Q&A. Workshop.

Quiz. Exercises.

Day 6. Using Subqueries.

Understanding Subqueries.

A Basic Subquery. Benefits of Using Subqueries. The Rules of Using Subqueries.

Matching a Value with Subqueries.

Creating Subqueries with Comparison Operators. Using ALL and ANY.

Checking for Membership.

Writing Your First Subquery with IN. Using the NOT IN Condition. Creating Correlated Subqueries. Things to Remember When Using IN. Writing Subqueries with EXIST. Comparing Joins and Subqueries.

Summary. Q&A. Workshop.

Quiz. Exercises.

Day 7. Adding, Changing, and Deleting Rows.

Creating Tables. Deleting Tables. Inserting Rows.

Inserting Rows with INSERT…VALUES. Inserting Rows with INSERT…SELECT. Inserting Rows with SELECT INTO.

Updating Rows.

Updating Data Using Data in Other Tables. Reviewing the Limits on the UPDATE Statement.

Deleting Rows.

Ensuring a Successful Deletion. Reviewing the Limits on the DELETE Statement. Truncating Tables. Understanding Identity Columns and the TRUNCATE Statement.

Summary. Q&A. Workshop.

Quiz. Exercises.

WEEK 1. IN REVIEW.

WEEK 2. PROGRAMMING WITH T-SQL.

Day 8. Defining Data.

What Is Data Definition Language (DDL)?

Creating a New Database. Altering a Database. Dropping or Deleting a Database. DDL Versus the Enterprise Manager.

Working with Tables.

Rules about Table and Column Names. Understanding Column Data Types. User-Defined Data Types. Choosing Nullability. Default Columns. Computed Columns.

Altering Tables.

Renaming Tables and Columns. Changing Data Types and Nullability.

Defining Constraints.

CHECK Constraints.

Defining Table Relationships.

Primary and Foreign Keys. Using Declarative Referential Integrity (DRI).

Summary. Q&A. Workshop.

Quiz. Exercises.

Day 9. Indexes and Performance.

How SQL Server Uses Indexes.

Why Use Indexes? Understanding Optimization. Understanding Clustered and Nonclustered Indexes. Understanding Unique Indexes. Indexing Computed Columns. Forcing Index Selection.

Defining and Managing Indexes.

Creating an Index. Using Full-Text Indexing.

Performing Index Maintenance and Using Performance Tools.

Full-Text Indexing Scheduling. Using Server Statistics. Using the Query Analyzer Tools.

Summary. Q&A /li>. Workshop.

Quiz. Exercises.

Day 10. Views and Temporary Tables.

Using Views to Access Data.

Creating a Basic View. Using Enterprise Manager Query Builder to Create Views. Using the Enterprise Manager Create View Wizard. Editing Views in Query Analyzer. Using Views for Security. Creating Views with Multiple Tables. Modifying Data in a View. Modifying and Renaming Views. Deleting a View.

Using Temporary Tables for Storing Data.

Creating a Temporary Table. Creating a Temporary Table on System Startup. Determining When to Use a Temporary Table.

Temporary Tables Versus Views. Summary. Q&A. Workshop.

Quiz. Exercises.

Day 11. T-SQL Programming Fundamentals.

Handling Variables.

Declaring and Setting Local Variables. Using Global Variables.

Reviewing Programming Structures.

Understanding Batches. Controlling Program Flow. Using the PRINT Statement. Using Comments.

Trapping Errors and Implementing Error Handling.

Communicating Error Messages. Raising an Error Message. Creating an Error Message. Coding Standard Error Handling Methods.

Summary. Q&A. Workshop.

Quiz. Exercise.

Day 12. Understanding Database Locking.

Lock Fundamentals.

Understanding Lock Modes. Examining Lock Scope. Examining Locks.

Testing Lock Interaction.

Setting the Lock Timeout. Monitoring Blocking Locks.

Understanding Lock Persistence.

Working with Transaction Isolation Levels. Using Hints to Control Lock Types. Deadlocks.

Summary. Q&A. Workshop.

Quiz. Exercises.

Day 13. Programming with Transactions.

Understanding Transaction Basics.

Understanding Automatic Transactions and the Transaction Log. Understanding the Write-Ahead Log. Rolling Back Changes.

Controlling Transactions in Your Programs.

Transaction Control Statements. Implicit, Explicit, and Autocommit Transactions. Transaction Error Handling.

Managing Concurrency.

Optimistic Locking. Application Resource Locking.

Handling Long-Running Transactions. Summary. Q&A. Workshop.

Quiz. Exercise.

Day 14. Using Cursors.

Defining Cursors.

Creating a Cursor. Cursor Types. Cursor Scope.

Working with Cursors.

Declaring the Cursor. Opening the Cursor. Declaring the Variables. Fetching the Rows. Closing the Cursor. Destroying the Cursor. Other Cursor Functions.

Cursor Locking. Summary. Q&A. Workshop.

Quiz. Exercise.

WEEK 2. IN REVIEW.

WEEK 3. PROCEDURES AND TRIGGERS.

Day 15. Writing and Executing Stored Procedures.

Benefits of Stored Procedures. Writing Your First Stored Procedure.

The INSERT Statement. The insert Procedure. Getting Information about Procedures. Executing Procedures.

Working with Parameters.

Execution Using a Parameter. Default Parameters.

Returning Procedure Status. Using Output Parameters.

Putting Output Parameters to Work. Communicating Between Stored Procedures. Using Output Parameters with Remote Procedure Calls.

Five Ways to Communicate Between Stored Procedures and Client Programs. Summary. Q&A. Workshop.

Quiz. Exercises.

Day 16. Optimizing Stored Procedures. Understanding Stored Procedure Optimization.

Using Good Procedure Performance Practices. Understanding the Compilation Process. Using the RECOMPILE Option in Stored Procedures. Using Table Variables and Temporary Tables with Procedures.

Using Table Variables in a Stored Procedure. Understanding the Scope of a Temporary Table. Indexing Temporary Tables. Handling Transactions in Stored Procedures.

Solving the Transactional Problem. Summary. Q&A. Workshop.

Quiz. Exercises.

Day 17. Debugging Stored Procedures.

Debugging Explained.

Finding the Problems. Creating a Good Work Environment. Displaying Debugging Messages. Using the PRINT Statement. Tracking Time. Tracking Elapsed Time Using Temporary Tables. Performing Error Checking in a Stored Procedure.

Using the T-SQL Debugger.

Starting the Debugger. Using the T-SQL Debugger Interface. Setting Breakpoints. Single-Stepping in a Procedure. Getting Output.

Summary. Q&A. Workshop.

Quiz. Exercises.

Day 18. Writing and Testing Triggers.

Understanding Triggers.

Writing Your First Trigger. Getting Information on Triggers. Maintaining Triggers. Naming Triggers.

Executing Triggers.

When Do Triggers Fire? How Does a Trigger Fire? INSTEAD OF Triggers.

Using Trigger Resources.

Accessing the INSERTED and DELETED Tables. Using the UPDATE() Function. Using Rollback in a Trigger.

Determining When to Use Triggers. Summary. Q&A. Workshop.

Quiz. Exercise.

Day 19. Solving Special Trigger Problems.

Managing Referential Integrity with Triggers.

Enforcing Referential Integrity with Triggers. Enforcing Business Rules with Triggers. Cascading Update and Delete Triggers.

Managing Special Database Actions with Triggers.

Managing Derived Columns. Synchronizing Real-Time Updates Between Databases. Maintaining an Audit Trail.

Summary. Q&A. Workshop.

Quiz. Exercise.

Day 20. User-Defined Functions in SQL.

Understanding User-Defined Functions.

Creating a Function. Creating a Scalar Function. Creating a Single Table Value Function. Creating a Multi-Table Function.

Reviewing Advanced UDF Topics.

Rewriting Stored Procedures as Functions. UDF Performance and Recommendations. Using Functions Instead of Views. Error Trapping and Functions.

Summary. Q&A. Workshop.

Quiz. Exercise.

Day 21. Handling BLOBs in T-SQL.

Understanding Large Data Types.

How Large Data Is Stored. Data Storage Options.

Managing BLOB Data.

Using the Large Object System Functions. Using the READTEXT, WRITETEXT, and UPDATETEXT Statements.

Summary. Q&A. Workshop.

Quiz. Exercises.

WEEK 3. IN REVIEW.

APPENDIX.

Appendix A. Answers to Quizzes and Exercises.
Index.

Updates

Submit Errata

More Information

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020