book

Learning SQL on SQL Server 2005

by Sikha Saha Bagui, Richard Walsh Earp

April 2006

Beginner

352 pages

8h 24m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Learning SQL on SQL Server 2005
Dedication
SPECIAL OFFER: Upgrade this ebook with O’Reilly
A Note Regarding Supplemental Files
Preface
Why This Book?SQL and SQL ServerAudience and CoverageA Few Notes About SQL Server 2005 InstallationConventions Used in This BookUsing Code ExamplesHow to Contact UsAcknowledgments
1. Starting Microsoft SQL Server 2005
Starting Microsoft SQL Server 2005 and SQL Server 2005’s Management StudioCreating a Database in Microsoft SQL Server 2005Objects in the Student_course DatabaseDefault Tables in the Student_course DatabaseDefault System DatabasesThe Query EditorOpening the Query Editor by Right-ClickingOpening the Query Editor Using the New Query ButtonOpening or Activating the Database Using USECreating Tables Using the Load ScriptViewing Table DefinitionsModifying Table DefinitionsModifying Column DefinitionsModifying the Table Definition DirectlyViewing Table DataDeleting a TableDeleting a DatabaseEntering a SQL Query or StatementParsing a QueryExecuting a QueryColor CodingSaving a QueryDisplaying the ResultsViewing Results in Grid FormViewing Results in Text FormSaving Results to FileStopping Execution of a Long QueryViewing Error MessagesPrinting the Query and ResultsCustomizing SQL Server 2005The Environment tabThe Source Control TabThe Text Editor TabThe Query Execution TabThe Query Results TabThe Designer TabSummaryReview QuestionsExercises
2. Beginning SQL Commands in SQL Server
Displaying Data with the SELECT StatementSELECT without the FROMDisplaying or Selecting Columns from a TableDisplaying or SELECTing One Column from a TableDisplaying or SELECTing More than One Column from a TableDisplaying or SELECTing All Columns of a TableORDER BYORDER BY and NULLsAscending and Descending OrderOrdering Within an OrderDisplaying or SELECTing Rows or Tuples from a TableFiltering with WHEREThe AND OperatorThe OR OperatorThe BETWEEN OperatorNegating the BETWEEN OperatorThe COUNT FunctionIS NULLIS NOT NULLThe ROWCOUNT FunctionUsing AliasesColumn AliasesTable AliasesTable Aliases Used as Table QualifiersSynonymsAdding Comments to SQL StatementsSome Conventions for Writing SQL StatementsA Few Notes About SQL Server 2005 SyntaxSummaryReview QuestionsExercises
3. Creating, Populating, Altering, and Deleting Tables
Data Types in SQL Server 2005Numeric Data TypesInteger data typesDecimal data typesCharacter Data TypesThe CHAR data typeThe VARCHAR data typeThe TEXT data typeThe NCHAR data typeThe NVARCHAR data typeUnicode character stringsSelecting the character data typesDate and Time Data TypesMiscellaneous Data TypesThe BINARY data typeThe IMAGE data typeThe BIT data typeThe monetary data typesThe TABLE data typeThe SQL_VARIANT data typeThe UNIQUEIDENTIFIER data typeThe XML data typeSelecting Data TypesCreating a TableInserting Values into a TableUsing INSERT INTO .. VALUESUsing INSERT INTO .. SELECTThe UPDATE CommandThe ALTER TABLE CommandAdding a Column to a TableChanging a Column’s Data Type in a TableChanging a column’s length in a tableDeleting a Column from a TableThe DELETE CommandDeleting a TableSummaryReview QuestionsExercisesReferences
4. Joins
The JOINExample 1Example 2The INNER JOINUsing a WHERE Clause Instead of a JOINAssociative Property of the JOINColumn Types in JoinsPerformance Hint for Efficient JoinsThe Cartesian ProductUses of the Cartesian ProductCROSS JOIN Used to Generate a Cartesian ProductEqui-Joins and Non-Equi-JoinsEqui-JoinsNon-Equi-JoinsSelf JoinsUsing ORDER BY with a JoinJoining More Than Two TablesJoining Multiple Tables Using a Nested JOINThe OUTER JOINThe LEFT OUTER JOINThe RIGHT OUTER JOINThe FULL OUTER JOINSummaryReview QuestionsExercises
5. Functions
Aggregate FunctionsThe COUNT FunctionThe SUM FunctionThe AVG FunctionThe MIN and MAX FunctionsRow-Level FunctionsArithmetic Operations on a ColumnThe ROUND FunctionOther Common Numeric FunctionsThe ISNULL FunctionExample 1Example 2The NULLIF FunctionOther Row-Level FunctionsOther FunctionsThe TOP FunctionHandling the “BOTTOM”Handling a tieThe TOP Function with PERCENTThe DISTINCT FunctionUsing DISTINCT with other aggregate functionsString FunctionsString ConcatenationString ExtractorsThe SUBSTRING functionThe LEFT and RIGHT functionsThe LTRIM and RTRIM functionsThe CHARINDEX functionThe UPPER and LOWER FunctionsThe LEN FunctionMatching Substrings Using LIKEUsing the wildcard character with LIKEFinding a range of charactersFinding a particular characterFinding a single character or single digit—the underscore wildcard characterUsing NOT LIKECONVERSION FunctionsThe CAST FunctionThe STR FunctionThe CONVERT FunctionDATE FunctionsCreating a Table with the DATETIME Data TypeDefault Date Formats and Changing Date FormatsDate FunctionsThe DATEADD functionThe DATEDIFF functionThe DATEPART functionThe YEAR functionThe MONTH functionThe DAY functionThe GETDATE functionInserting the current date and timeSummaryTable of FunctionsReview QuestionsExercises

6. Query Development and Derived Structures
Query DevelopmentParentheses in SQL ExpressionsOperator PrecedenceData Type PrecedenceDerived StructuresViewsCreating viewsUsing viewsORDER BY in viewsSELECT INTO in viewsColumn aliases in viewsData in viewsChanging data in viewsChanging data in tablesDeleting viewsTemporary TablesCreating temporary tablesCreating local temporary tablesCreating global temporary tablesDeleting temporary tablesQuery Development with Derived StructuresStep 1: Develop a Query Step by StepStep 2: Using a Derived StructureOption 1: Turning your query into a viewOption 2: Using an inline viewOption 3: Using a global temporary tableSummaryReview QuestionsExercises
7. Set Operations
Introducing Set OperationsUnion CompatibilityThe UNION OperationSimilar Columns in UnionsUnioning Constants or VariablesThe UNION ALL OperationHandling UNION and UNION ALL Situations with an Unequal Number of ColumnsThe IN and NOT..IN PredicatesUsing INUsing IN as a subqueryThe INTERSECT OperatorUsing NOT..INUsing NOT..IN in a subqueryThe Difference OperationExample 1Example 2The Union and the JoinWhen a JOIN May Be Used Versus When a UNION May Be UsedExample 1: A straightforward join operationExample 2: A not-so-straightforward queryA Summary of the Other Differences Between the UNION and the JOINA UNION Used to Implement a Full Outer JoinSummaryReview QuestionsExercisesOptional Exercise
8. Joins Versus Subqueries
Subquery with an IN PredicateThe Subquery as a JoinWhen the Join Cannot Be Turned into a SubqueryMore Examples Involving Joins and INExample 1Example 2Example 3Using Subqueries with OperatorsSummaryReview QuestionsExercises
9. Aggregation and GROUP BY
A SELECT in Modified BNFThe GROUP BY ClauseGROUP BY and ORDER BYGROUP BY and DISTINCTThe HAVING ClauseHAVING and WHEREGROUP BY and HAVING: Aggregates of AggregatesAggregation and Grouping in SQL Server 2005Aggregation and grouping handled with a global temporary tableAggregation and grouping handled with an inline viewAuditing in SubqueriesNulls RevisitedSummaryReview QuestionsExercises
10. Correlated Subqueries
Noncorrelated SubqueriesCorrelated SubqueriesExistence Queries and CorrelationUsing EXISTSFrom IN to EXISTSNOT EXISTSSQL Universal and Existential QualifiersExample 1The way the query worksExample 2The way this query worksExample 3SummaryReview QuestionsExercises
11. Indexes and Constraints on Tables
The “Simple” CREATE TABLEIndexesThe “Simple” CREATE INDEXDeleting Indexes Using SQLConstraintsThe NOT NULL ConstraintThe PRIMARY KEY ConstraintOption 1Option 2Option 3Concatenated primary keysThe UNIQUE ConstraintThe CHECK ConstraintDeleting a ConstraintReferential Integrity ConstraintsDefining the referential integrity constraintAdding the foreign key after tables are createdDELETE and the referential CONSTRAINTUPDATE and the referential CONSTRAINTUsing the ON DELETE and ON UPDATE togetherSummaryReview QuestionsExercises
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 O’Reilly
Copyright

Content preview from Learning SQL on SQL Server 2005

Chapter 7. Set Operations

In Chapter 4, we looked at how data can be retrieved from multiple tables using joins. In this chapter, we discuss how data can also be retrieved from multiple tables by using set operations . We look at the set operations available in SQL Server 2005. Because not all the SQL set operations are explicitly available in SQL Server 2005, we will also look at the IN predicate and its negation, NOT..IN, which are ways around the explicit set operations. In the final section of this chapter, we look at the UNION operation in relation to the join operation, and how the UNION operation can be used to get the results of some joins.

Introducing Set Operations

A set is a collection of objects. In relational databases, a table can be regarded as a set of rows. Elements in a set do not have to be ordered. In relational databases, rows do not have to be ordered as they are entered or stored. Set operations are used in SQL to retrieve data from multiple sets, and include a binary union, binary intersection and binary set difference . A result set is obtained in SQL from the result of a SELECT.

A binary union is a set operation on two sets, the result of which contains all the elements of both sets. A binary intersection generates values in common between two sets. And, a binary set difference generates values in one set less those contained in another set.

Three explicit set operations are used in SQL: UNION, INTERSECT, and MINUS (for set difference). SQL Server 2005 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Oracle Database 11g & MySQL 5.6 Developer Handbook

Publisher Resources

ISBN: 0596102151Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Learning SQL on SQL Server 2005

by Sikha Saha Bagui, Richard Walsh Earp

Chapter 7. Set Operations

Introducing Set Operations

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.