Learning SQL on SQL Server 2005

Learning SQL on SQL Server 2005

by Sikha Saha Bagui, Richard Walsh Earp
Released April 2006
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781449390891

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Anyone who interacts with today's modern databases needs to know SQL (Structured Query Language), the standard language for generating, manipulating, and retrieving database information. In recent years, the dramatic rise in the popularity of relational databases and multi-user databases has fueled a healthy demand for application developers and others who can write SQL code efficiently and correctly.

If you're new to databases, or need a SQL refresher, Learning SQL on SQL Server 2005 is an ideal step-by-step introduction to this database query tool, with everything you need for programming SQL using Microsoft's SQL Server 2005-one of the most powerful and popular database engines used today. Plenty of books explain database theory. This guide lets you apply the theory as you learn SQL. You don't need prior database knowledge, or even prior computer knowledge.

Based on a popular university-level course designed by authors Sikha Saha Bagui and Richard Walsh Earp, Learning SQL on SQL Server 2005 starts with very simple SQL concepts, and slowly builds into more complex query development. Every topic, concept, and idea comes with examples of code and output, along with exercises to help you gain proficiency in SQL and SQL Server 2005. With this book, you'll learn:

  • Beginning SQL commands, such as how and where to type an SQL query, and how to create, populate, alter and delete tables
  • How to customize SQL Server 2005's settings and about SQL Server 2005's functions
  • About joins, a common database mechanism for combining tables
  • Query development, the use of views and other derived structures, and simple set operations
  • Subqueries, aggregate functions and correlated subqueries, as well as indexes and constraints that can be added to tables in SQL Server 2005

Whether you're an undergraduate computer science or MIS student, a self-learner who has access to the new Microsoft database, or work for your company's IT department, Learning SQL on SQL Server 2005 will get you up to speed on SQL in no time.

Publisher resources

View/Submit Errata

Table of contents

  1. Learning SQL on SQL Server 2005
  2. Dedication
  3. SPECIAL OFFER: Upgrade this ebook with O’Reilly
  4. A Note Regarding Supplemental Files
  5. Preface
    1. Why This Book?
    2. SQL and SQL Server
    3. Audience and Coverage
    4. A Few Notes About SQL Server 2005 Installation
    5. Conventions Used in This Book
    6. Using Code Examples
    7. How to Contact Us
    8. Acknowledgments
  6. 1. Starting Microsoft SQL Server 2005
    1. Starting Microsoft SQL Server 2005 and SQL Server 2005’s Management Studio
    2. Creating a Database in Microsoft SQL Server 2005
      1. Objects in the Student_course Database
      2. Default Tables in the Student_course Database
      3. Default System Databases
    3. The Query Editor
      1. Opening the Query Editor by Right-Clicking
      2. Opening the Query Editor Using the New Query Button
      3. Opening or Activating the Database Using USE
    4. Creating Tables Using the Load Script
    5. Viewing Table Definitions
    6. Modifying Table Definitions
      1. Modifying Column Definitions
      2. Modifying the Table Definition Directly
    7. Viewing Table Data
    8. Deleting a Table
    9. Deleting a Database
    10. Entering a SQL Query or Statement
    11. Parsing a Query
    12. Executing a Query
      1. Color Coding
    13. Saving a Query
    14. Displaying the Results
      1. Viewing Results in Grid Form
      2. Viewing Results in Text Form
      3. Saving Results to File
    15. Stopping Execution of a Long Query
      1. Viewing Error Messages
    16. Printing the Query and Results
    17. Customizing SQL Server 2005
      1. The Environment tab
      2. The Source Control Tab
      3. The Text Editor Tab
      4. The Query Execution Tab
      5. The Query Results Tab
      6. The Designer Tab
    18. Summary
    19. Review Questions
    20. Exercises
  7. 2. Beginning SQL Commands in SQL Server
    1. Displaying Data with the SELECT Statement
      1. SELECT without the FROM
      2. Displaying or Selecting Columns from a Table
      3. Displaying or SELECTing One Column from a Table
      4. Displaying or SELECTing More than One Column from a Table
      5. Displaying or SELECTing All Columns of a Table
      6. ORDER BY
        1. ORDER BY and NULLs
      7. Ascending and Descending Order
      8. Ordering Within an Order
    2. Displaying or SELECTing Rows or Tuples from a Table
      1. Filtering with WHERE
      2. The AND Operator
      3. The OR Operator
      4. The BETWEEN Operator
      5. Negating the BETWEEN Operator
    3. The COUNT Function
      1. IS NULL
      2. IS NOT NULL
    4. The ROWCOUNT Function
    5. Using Aliases
      1. Column Aliases
      2. Table Aliases
      3. Table Aliases Used as Table Qualifiers
    6. Synonyms
    7. Adding Comments to SQL Statements
    8. Some Conventions for Writing SQL Statements
    9. A Few Notes About SQL Server 2005 Syntax
    10. Summary
    11. Review Questions
    12. Exercises
  8. 3. Creating, Populating, Altering, and Deleting Tables
    1. Data Types in SQL Server 2005
      1. Numeric Data Types
        1. Integer data types
        2. Decimal data types
      2. Character Data Types
        1. The CHAR data type
        2. The VARCHAR data type
        3. The TEXT data type
        4. The NCHAR data type
        5. The NVARCHAR data type
        6. Unicode character strings
        7. Selecting the character data types
      3. Date and Time Data Types
      4. Miscellaneous Data Types
        1. The BINARY data type
        2. The IMAGE data type
        3. The BIT data type
        4. The monetary data types
        5. The TABLE data type
        6. The SQL_VARIANT data type
        7. The UNIQUEIDENTIFIER data type
        8. The XML data type
      5. Selecting Data Types
    2. Creating a Table
    3. Inserting Values into a Table
      1. Using INSERT INTO .. VALUES
      2. Using INSERT INTO .. SELECT
    4. The UPDATE Command
    5. The ALTER TABLE Command
      1. Adding a Column to a Table
      2. Changing a Column’s Data Type in a Table
        1. Changing a column’s length in a table
      3. Deleting a Column from a Table
    6. The DELETE Command
    7. Deleting a Table
    8. Summary
    9. Review Questions
    10. Exercises
    11. References
  9. 4. Joins
    1. The JOIN
      1. Example 1
      2. Example 2
      3. The INNER JOIN
      4. Using a WHERE Clause Instead of a JOIN
      5. Associative Property of the JOIN
      6. Column Types in Joins
      7. Performance Hint for Efficient Joins
    2. The Cartesian Product
      1. Uses of the Cartesian Product
      2. CROSS JOIN Used to Generate a Cartesian Product
    3. Equi-Joins and Non-Equi-Joins
      1. Equi-Joins
      2. Non-Equi-Joins
    4. Self Joins
    5. Using ORDER BY with a Join
    6. Joining More Than Two Tables
      1. Joining Multiple Tables Using a Nested JOIN
    7. The OUTER JOIN
      1. The LEFT OUTER JOIN
      2. The RIGHT OUTER JOIN
      3. The FULL OUTER JOIN
    8. Summary
    9. Review Questions
    10. Exercises
  10. 5. Functions
    1. Aggregate Functions
      1. The COUNT Function
      2. The SUM Function
      3. The AVG Function
      4. The MIN and MAX Functions
    2. Row-Level Functions
      1. Arithmetic Operations on a Column
      2. The ROUND Function
      3. Other Common Numeric Functions
      4. The ISNULL Function
        1. Example 1
        2. Example 2
      5. The NULLIF Function
      6. Other Row-Level Functions
    3. Other Functions
      1. The TOP Function
        1. Handling the “BOTTOM”
        2. Handling a tie
      2. The TOP Function with PERCENT
      3. The DISTINCT Function
        1. Using DISTINCT with other aggregate functions
    4. String Functions
      1. String Concatenation
      2. String Extractors
        1. The SUBSTRING function
        2. The LEFT and RIGHT functions
        3. The LTRIM and RTRIM functions
        4. The CHARINDEX function
      3. The UPPER and LOWER Functions
      4. The LEN Function
      5. Matching Substrings Using LIKE
        1. Using the wildcard character with LIKE
        2. Finding a range of characters
        3. Finding a particular character
        4. Finding a single character or single digit—the underscore wildcard character
        5. Using NOT LIKE
    5. CONVERSION Functions
      1. The CAST Function
      2. The STR Function
      3. The CONVERT Function
    6. DATE Functions
      1. Creating a Table with the DATETIME Data Type
      2. Default Date Formats and Changing Date Formats
      3. Date Functions
        1. The DATEADD function
        2. The DATEDIFF function
        3. The DATEPART function
        4. The YEAR function
        5. The MONTH function
        6. The DAY function
        7. The GETDATE function
        8. Inserting the current date and time
    7. Summary
      1. Table of Functions
    8. Review Questions
    9. Exercises
  11. 6. Query Development and Derived Structures
    1. Query Development
    2. Parentheses in SQL Expressions
      1. Operator Precedence
      2. Data Type Precedence
    3. Derived Structures
      1. Views
        1. Creating views
        2. Using views
        3. ORDER BY in views
        4. SELECT INTO in views
        5. Column aliases in views
        6. Data in views
          1. Changing data in views
          2. Changing data in tables
        7. Deleting views
      2. Temporary Tables
        1. Creating temporary tables
          1. Creating local temporary tables
          2. Creating global temporary tables
          3. Deleting temporary tables
    4. Query Development with Derived Structures
      1. Step 1: Develop a Query Step by Step
      2. Step 2: Using a Derived Structure
        1. Option 1: Turning your query into a view
        2. Option 2: Using an inline view
        3. Option 3: Using a global temporary table
    5. Summary
    6. Review Questions
    7. Exercises
  12. 7. Set Operations
    1. Introducing Set Operations
      1. Union Compatibility
    2. The UNION Operation
      1. Similar Columns in Unions
      2. Unioning Constants or Variables
    3. The UNION ALL Operation
    4. Handling UNION and UNION ALL Situations with an Unequal Number of Columns
    5. The IN and NOT..IN Predicates
      1. Using IN
        1. Using IN as a subquery
      2. The INTERSECT Operator
      3. Using NOT..IN
        1. Using NOT..IN in a subquery
    6. The Difference Operation
      1. Example 1
      2. Example 2
    7. The Union and the Join
      1. When a JOIN May Be Used Versus When a UNION May Be Used
        1. Example 1: A straightforward join operation
        2. Example 2: A not-so-straightforward query
      2. A Summary of the Other Differences Between the UNION and the JOIN
    8. A UNION Used to Implement a Full Outer Join
    9. Summary
    10. Review Questions
    11. Exercises
    12. Optional Exercise
  13. 8. Joins Versus Subqueries
    1. Subquery with an IN Predicate
    2. The Subquery as a Join
    3. When the Join Cannot Be Turned into a Subquery
    4. More Examples Involving Joins and IN
      1. Example 1
      2. Example 2
      3. Example 3
    5. Using Subqueries with Operators
    6. Summary
    7. Review Questions
    8. Exercises
  14. 9. Aggregation and GROUP BY
    1. A SELECT in Modified BNF
    2. The GROUP BY Clause
      1. GROUP BY and ORDER BY
      2. GROUP BY and DISTINCT
    3. The HAVING Clause
      1. HAVING and WHERE
    4. GROUP BY and HAVING: Aggregates of Aggregates
      1. Aggregation and Grouping in SQL Server 2005
        1. Aggregation and grouping handled with a global temporary table
        2. Aggregation and grouping handled with an inline view
    5. Auditing in Subqueries
    6. Nulls Revisited
    7. Summary
    8. Review Questions
    9. Exercises
  15. 10. Correlated Subqueries
    1. Noncorrelated Subqueries
    2. Correlated Subqueries
    3. Existence Queries and Correlation
      1. Using EXISTS
      2. From IN to EXISTS
      3. NOT EXISTS
    4. SQL Universal and Existential Qualifiers
      1. Example 1
        1. The way the query works
      2. Example 2
        1. The way this query works
      3. Example 3
    5. Summary
    6. Review Questions
    7. Exercises
  16. 11. Indexes and Constraints on Tables
    1. The “Simple” CREATE TABLE
    2. Indexes
      1. The “Simple” CREATE INDEX
      2. Deleting Indexes Using SQL
    3. Constraints
      1. The NOT NULL Constraint
      2. The PRIMARY KEY Constraint
        1. Option 1
        2. Option 2
        3. Option 3
        4. Concatenated primary keys
      3. The UNIQUE Constraint
      4. The CHECK Constraint
      5. Deleting a Constraint
      6. Referential Integrity Constraints
        1. Defining the referential integrity constraint
        2. Adding the foreign key after tables are created
        3. DELETE and the referential CONSTRAINT
        4. UPDATE and the referential CONSTRAINT
        5. Using the ON DELETE and ON UPDATE together
    4. Summary
    5. Review Questions
    6. Exercises
  17. A. The Student Database and Other Tables Used in This Book
      1. Brief English Description of the ER Diagram
  18. B. Script Used to Create the Student_course Database
  19. Glossary of Terms
  20. Important Commands and Functions
  21. Index
  22. About the Authors
  23. Colophon
  24. SPECIAL OFFER: Upgrade this ebook with O’Reilly
  25. Copyright

Product information

  • Title: Learning SQL on SQL Server 2005
  • Author(s): Sikha Saha Bagui, Richard Walsh Earp
  • Release date: April 2006
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781449390891