book

SQL: Visual Quickstart Guide, Second Edition

Name: SQL: Visual Quickstart Guide, Second Edition
Author: Chris Fehily
ISBN: 9780321334176

by Chris Fehily

May 2005

Beginner

480 pages

11h 1m

English

Peachpit Press

Read now

Unlock full access

Copyright
DedicationSpecial thanks to…
Introduction
About SQLAbout This BookAudienceTypographic conventionsSyntax conventionsUsing SQL with a specific DBMSWhat You'll Need
1. DBMS Specifics
Running SQL ProgramsMicrosoft AccessTo turn on ANSI-92 SQL query mode for a databaseTo run an SQL statementMicrosoft SQL ServerTo use SQL Query AnalyzerTo use the osql command-line utility interactivelyTo use the osql command-line utility in script modeTo exit the osql command-line utilityTo show osql command-line optionsOracleTo use SQL*PlusTo use the sqlplus command-line utility interactivelyTo use the sqlplus command-line utility in script modeTo exit the sqlplus command-line utilityTo show sqlplus command-line optionsIBM DB2To use Command CenterTo start the db2 command-line processor in WindowsTo use the db2 command-line processor interactivelyTo use the db2 command-line processor in script modeTo exit the db2 command-line utilityTo show db2 command-line optionsMySQLTo use the mysql command-line utility interactivelyTo use the mysql command-line utility in script modeTo exit the mysql command-line utilityTo show mysql command-line optionsPostgreSQLTo use the psql command-line utility interactivelyTo use the psql command-line utility in script modeTo exit the psql command-line utilityTo show psql command-line options
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 royalties
3. SQL Basics
SQL SyntaxSQL Standards and ConformanceIdentifiersData TypesCharacter String TypesBinary Large Object TypeExact Numeric TypesApproximate Numeric TypesBoolean TypeDatetime TypesInterval TypesOther Data TypesNulls
4. Retrieving Data From a Table
Retrieving Columns with SELECT and FROMTo retrieve a column from a tableTo retrieve multiple columns from a tableTo retrieve all columns from a tableCreating Column Aliases with ASTo create column aliasesEliminating Duplicate Rows with DISTINCTTo eliminate duplicate rowsSorting Rows with ORDER BYTo sort by a columnTo sort by multiple columnsTo sort by relative column positionsFiltering Rows with WHERETo filter rows by making a comparisonCombining and Negating Conditions with AND, OR, and NOTThe AND operatorThe OR operatorThe NOT operatorUsing AND, OR, and NOT togetherMatching Patterns with LIKETo filter rows by matching a patternTo match a wildcard characterRange Filtering with BETWEENTo filter rows by using a rangeList Filtering with INTo filter rows by using a listTesting for Nulls with IS NULLTo retrieve rows with nulls or non-null values
5. Operators and Functions
Creating Derived ColumnsPerforming Arithmetic OperationsTo change the sign of a numberTo add, subtract, multiply, or divideDetermining the Order of EvaluationConcatenating Strings with ||To concatenate stringsExtracting a Substring with SUBSTRING()To extract a substringChanging String Case with UPPER() and LOWER()To convert a string to uppercase or lowercaseTrimming Characters with TRIM()To trim spaces from a stringTo trim characters from a stringFinding the Length of a String with CHARACTER_LENGTH()To find the length of a stringFinding Substrings with POSITION()To find a substringPerforming Datetime and Interval ArithmeticTo extract part of a datetime or intervalGetting the Current Date and TimeTo get the current date and timeGetting User InformationTo get the current userConverting Data Types with CAST()To convert one data type to anotherEvaluating Conditional Values with CASETo use a simple CASE expressionTo use a searched CASE expressionChecking for Nulls with COALESCE()To return the first non-null valueComparing Expressions with NULLIF()To return a null if two expressions are equivalent
6. Summarizing and Grouping Data
Using Aggregate FunctionsCreating Aggregate ExpressionsFinding a Minimum with MIN()To find the minimum of a set of valuesFinding a Maximum with MAX()To find the maximum of a set of valuesCalculating a Sum with SUM()To calculate the sum of a set of valuesCalculating an Average with AVG()To calculate the average of a set of valuesCounting Rows with COUNT()To count non-null rowsTo count all rows, including nullsAggregating Distinct Values with DISTINCTTo calculate the sum of a set of distinct valuesTo calculate the average of a set of distinct valuesTo count distinct non-null rowsGrouping Rows with GROUP BYTo group rowsFiltering Groups with HAVINGTo filter groups
7. Joins
Qualifying Column NamesTo qualify a column nameCreating Table Aliases with ASTo create a table aliasUsing JoinsCreating Joins with JOIN or WHERETo create a join by using JOINTo create a join by using WHERECreating a Cross Join with CROSS JOINTo create a cross joinCreating a Natural Join with NATURAL JOINTo create a natural joinCreating an Inner Join with INNER JOINTo create an inner joinCreating Outer Joins with OUTER JOINTo create a left outer joinTo create a right outer joinTo create a full outer joinCreating a Self-JoinTo create 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 OperatorTo compare a subquery valueTesting Set Membership with INTo test set membershipComparing All Subquery Values with ALLTo compare all subquery valuesComparing Some Subquery Values with ANYTo compare some subquery valuesTesting Existence with EXISTSTo test existenceComparing Equivalent Queries

9. Set Operations
Combining Rows with UNIONTo combine rowsFinding Common Rows with INTERSECTTo find common rowsFinding Different Rows with EXCEPTTo find different rowsCalculating Running StatisticsGenerating SequencesTo define a sequence generatorFinding Sequences, Runs, and RegionsLimiting the Number of Rows ReturnedMicrosoft AccessMicrosoft SQL ServerOracleDB2MySQLPostgreSQLAssigning RanksCalculating a Trimmed MeanPicking Random RowsHandling Duplicates
10. Inserting, Updating, and Deleting Rows
Displaying Table DefinitionsTo display table definitions in Microsoft AccessTo display table definitions in Microsoft SQL ServerTo display table definitions in OracleTo display table definitions in IBM DB2To display table definitions in MySQLTo display table definitions in PostgreSQLInserting Rows with INSERTTo insert a row by using column positionsTo insert a row by using column namesTo insert rows from one table into another tableUpdating Rows with UPDATETo update rowsDeleting Rows with DELETETo delete rows
11. Creating, Altering, and Dropping Tables
Creating TablesUnderstanding ConstraintsTo name a constraintCreating a New Table with CREATE TABLETo create a new tableForbidding Nulls with NOT NULLTo specify a column's nullabilitySpecifying a Default Value with DEFAULTTo specify a column's default valueSpecifying a Primary Key with PRIMARY KEYTo specify a simple primary keyTo specify a composite primary keySpecifying a Foreign Key with FOREIGN KEYTo specify a simple foreign keyTo specify a composite foreign keyForcing Unique Values with UNIQUETo specify a simple unique constraintTo specify a composite unique constraintAdding a Check Constraint with CHECKTo add a check constraintCreating a Temporary Table with CREATE TEMPORARY TABLETo create a temporary tableCreating a New Table from an Existing One with CREATE TABLE ASTo create a new table from an existing tableAltering a Table with ALTER TABLETo alter a tableTo re-create and repopulate a tableDropping a Table with DROP TABLETo drop a table
12. Indexes
Creating an Index with CREATE INDEXTo create an indexDropping an Index with DROP INDEXTo drop an index in Microsoft Access or MySQLTo drop an index in Microsoft SQL ServerTo drop an index in Oracle, DB2, or PostgreSQL
13. Views
Creating a View with CREATE VIEWTo create a viewRetrieving Data Through a ViewTo retrieve 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 VIEWTo drop a view
14. Transactions
Executing a TransactionTo start a transaction explicitlyTo commit a transactionTo roll back a transaction
A. Creating the Sample Database
Creating the Sample DatabaseTo open books in Microsoft AccessTo create books in Microsoft SQL ServerTo create books in OracleTo create books in IBM DB2To create books in MySQLTo create books in PostgreSQL
B. SQL Keywords

Content preview from SQL: Visual Quickstart Guide, Second Edition

Chapter 12. Indexes

Recall from “Tables, Columns, and Rows” in Chapter 2 that rows stored in a table are unordered, as required by the relational model. This lack of order makes it easy for the DBMS to INSERT, UPDATE, and DELETE rows quickly, but its unfortunate side effect is that it makes searching and sorting inefficient. Suppose that you run this query:

SELECT *
  FROM authors
  WHERE au_lname = 'Hull';

To execute this query, the DBMS must search the entire table authors sequentially, comparing the value in each row's au_lname column to the string Hull. Searching an entire table in a small database is trivial, but production database tables can have millions of rows.

DBMSes provide a mechanism called an index that has the same purpose as its book ...

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

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

SQL: Visual Quickstart Guide, Second Edition

by Chris Fehily

Chapter 12. Indexes

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.