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

Overview

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.

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

Publisher Resources

ISBN: 0596102151Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills