Learning SQL on SQL Server 2005 (e-book)

Learning SQL on SQL Server 2005 (e-book) - Opis

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

Brief English Description of the ER Diagram
B. Script Used to Create the Student_course Database
Glossary of Terms
Important Commands and Functions
Index
About the Authors
Colophon
SPECIAL OFFER: Upgrade this ebook with OReilly
Copyright mniej