book

SQL: Visual QuickStart Guide, Third Edition

Name: SQL: Visual QuickStart Guide, Third Edition
Author: Chris Fehily
ISBN: 9780321584069

by Chris Fehily

June 2008

Beginner

496 pages

10h 41m

English

Peachpit Press

Read now

Unlock full access

Copyright
Dedication
Special thanks to...
i. Introduction
About SQLAbout This BookAudienceTypographic conventionsSyntax conventionsUsing SQL with a specific DBMSWhat You’ll Need
1. DBMS Specifics
Running SQL ProgramsMicrosoft AccessMicrosoft SQL ServerSQL Server 2000SQL Server 2005/2008OracleIBM DB2MySQLPostgreSQL
2. The Relational Model
Tables, Columns, and RowsTablesColumnsRowsPrimary KeysForeign KeysRelationshipsOne-to-oneOne-to-manyMany-to-manyNormalizationFirst normal formSecond normal formThird normal formOther normal formsThe Sample DatabaseThe table authorsThe table publishersThe table titlesThe table title_authorsThe table royaltiesCreating the Sample Database
3. SQL Basics
SQL SyntaxSQL Standards and ConformanceIdentifiersData TypesCharacter String TypesBinary Large Object TypeExact Numeric TypesApproximate Numeric TypesBoolean TypeDatetime TypesInterval TypesUnique IdentifiersOther Data TypesNulls
4. Retrieving Data from a Table
Retrieving Columns with SELECT and FROMCreating Column Aliases with ASEliminating Duplicate Rows with DISTINCTSorting Rows with ORDER BYFiltering Rows with WHERECombining and Negating Conditions with AND, OR, and NOTThe AND operatorThe OR operatorThe NOT operatorUsing AND, OR, and NOT togetherMatching Patterns with LIKERange Filtering with BETWEENList Filtering with INTesting for Nulls with IS NULL
5. Operators and Functions
Creating Derived ColumnsPerforming Arithmetic OperationsDetermining the Order of EvaluationConcatenating Strings with ||Extracting a Substring with SUBSTRING()Changing String Case with UPPER() and LOWER()Trimming Characters with TRIM()Finding the Length of a String with CHARACTER_LENGTH()Finding Substrings with POSITION()Performing Datetime and Interval ArithmeticGetting the Current Date and TimeGetting User InformationConverting Data Types with CAST()Evaluating Conditional Values with CASEChecking for Nulls with COALESCE()Comparing Expressions with NULLIF()
6. Summarizing and Grouping Data
Using Aggregate FunctionsCreating Aggregate ExpressionsFinding a Minimum with MIN()Finding a Maximum with MAX()Calculating a Sum with SUM()Calculating an Average with AVG()Counting Rows with COUNT()Aggregating Distinct Values with DISTINCTGrouping Rows with GROUP BYFiltering Groups with HAVING
7. Joins
Qualifying Column NamesCreating Table Aliases with ASUsing JoinsCreating Joins with JOIN or WHERECreating a Cross Join with CROSS JOINCreating a Natural Join with NATURAL JOINCreating an Inner Join with INNER JOINCreating Outer Joins with OUTER JOINCreating a Self-Join

8. Subqueries
Understanding SubqueriesSubquery SyntaxSubqueries vs. JoinsSimple and Correlated SubqueriesSimple subqueriesCorrelated subqueriesQualifying Column Names in SubqueriesNulls in SubqueriesUsing Subqueries as Column ExpressionsComparing a Subquery Value by Using a Comparison OperatorTesting Set Membership with INComparing All Subquery Values with ALLComparing Some Subquery Values with ANYTesting Existence with EXISTSComparing Equivalent Queries
9. Set Operations
Combining Rows with UNIONFinding Common Rows with INTERSECTFinding Different Rows with EXCEPT
10. Inserting, Updating, and Deleting Rows
Displaying Table DefinitionsInserting Rows with INSERTUpdating Rows with UPDATEDeleting Rows with DELETE
11. Creating, Altering, and Dropping Tables
Creating TablesUnderstanding ConstraintsCreating a New Table with CREATE TABLEForbidding Nulls with NOT NULLSpecifying a Default Value with DEFAULTSpecifying a Primary Key with PRIMARY KEYSpecifying a Foreign Key with FOREIGN KEYForcing Unique Values with UNIQUEAdding a Check Constraint with CHECKCreating a Temporary Table with CREATE TEMPORARY TABLECreating a New Table from an Existing One with CREATE TABLE ASAltering a Table with ALTER TABLEDropping a Table with DROP TABLE
12. Indexes
Creating an Index with CREATE INDEXDropping an Index with DROP INDEX
13. Views
Creating a View with CREATE VIEWRetrieving Data Through a ViewUpdating Data Through a ViewInserting a row through a viewUpdating a row through a viewDeleting a row through a viewDropping a View with DROP VIEW
14. Transactions
Executing a Transaction
15. SQL Tricks
Calculating Running StatisticsGenerating SequencesFinding Sequences, Runs, and RegionsLimiting the Number of Rows ReturnedMicrosoft AccessMicrosoft SQL ServerOracleIBM DB2MySQLPostgreSQLAssigning RanksCalculating a Trimmed MeanPicking Random RowsHandling DuplicatesCreating a Telephone ListRetrieving MetadataMicrosoft AccessMicrosoft SQL ServerOracleIBM DB2MySQLPostgreSQLWorking with DatesMicrosoft AccessMicrosoft SQL ServerOracleIBM DB2MySQLPostgreSQLCalculating a MedianFinding Extreme ValuesChanging Running Statistics MidstreamPivoting ResultsWorking with Hierarchies

Content preview from SQL: Visual QuickStart Guide, Third Edition

Chapter 9. Set Operations

Recall from Chapter 2 that set theory is fundamental to the relational model. But whereas mathematical sets are unchanging, database sets are dynamic—they grow, shrink, and otherwise change over time. This chapter covers the following SQL set operators, which combine the results of two SELECT statements into one result:

UNION returns all the rows returned by both queries, with duplicates removed.
INTERSECT returns all rows common to both queries (that is, all distinct rows retrieved by both queries).
EXCEPT returns all rows from the first query without the rows that appear in the second query, with duplicates removed.

These set operations aren’t joins, but you can mix and chain them to combine two or more tables.

Combining ...

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: 9780321584069Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

SQL: Visual QuickStart Guide, Third Edition

by Chris Fehily

Chapter 9. Set Operations

Combining ...

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.