book

SQL: A Beginner's Guide, Third Edition, 3rd Edition

by Andy Oppel, Robert Sheldon

August 2008

Beginner

575 pages

14h 47m

English

McGraw-Hill

Read now

Unlock full access

Cover Page
SQL A Beginner's Guide
About the Auther
Copyright Page
Contents
Acknowledgments
Introduction
PART I Relational Databases and SQL
1 Introduction to Relational Databases and SQLUnderstand Relational DatabasesThe Relational ModelLearn About SQLThe SQL EvolutionTypes of SQL StatementsTypes of ExecutionSQL Standard versus Product Implementations
2 Working with the SQL Environment
Understand the SQL EnvironmentUnderstand SQL CatalogsSchemasSchema ObjectsThen What Is a Database?Name Objects in an SQL EnvironmentQualified NamesCreate a SchemaCreate a Database
3 Creating and Altering Tables
Create SQL TablesSpecify Column Data TypesString Data TypesNumeric Data TypesDatetime Data TypesInterval Data TypeBoolean Data TypeUsing SQL Data TypesCreate User-Defined TypesSpecify Column Default ValuesDelete SQL Tables
4 Enforcing Data Integrity
Understand Integrity ConstraintsUse NOT NULL ConstraintsAdd UNIQUE ConstraintsAdd PRIMARY KEY ConstraintsAdd FOREIGN KEY ConstraintsThe MATCH ClauseThe <referential triggered action> ClauseDefine CHECK ConstraintsDefining AssertionsCreating Domains and Domain Constraints

5 Creating SQL Views
Add Views to the DatabaseDefining SQL ViewsCreate Updateable ViewsUsing the WITH CHECK OPTION ClauseDrop Views from the Database
6 Managing Database Security
Understand the SQL Security ModelSQL SessionsAccessing Database ObjectsCreate and Delete RolesGrant and Revoke PrivilegesRevoking PrivilegesGrant and Revoke RolesRevoking Roles
PART II Data Access and Modification
7 Querying SQL DataUse a SELECT Statement to Retrieve DataThe SELECT Clause and FROM ClauseUse the WHERE Clause to Define Search ConditionsDefining the WHERE ClauseUse the GROUP BY Clause to Group Query ResultsUse the HAVING Clause to Specify Group Search ConditionsUse the ORDER BY Clause to Sort Query Results
8 Modifying SQL Data
Insert SQL DataInserting Values from a SELECT StatementUpdate SQL DataUpdating Values from a SELECT StatementDelete SQL Data
9 Using Predicates
Compare SQL DataUsing the BETWEEN PredicateReturn Null ValuesReturn Similar ValuesReference Additional Sources of DataUsing the IN PredicateUsing the EXISTS PredicateQuantify Comparison PredicatesUsing the SOME and ANY PredicatesUsing the ALL Predicate
10 Working with Functions and Value Expressions
Use Set FunctionsUsing the COUNT FunctionUsing the MAX and MIN FunctionsUsing the SUM FunctionUsing the AVG FunctionUse Value FunctionsWorking with String Value FunctionsWorking with Datetime Value FunctionsUse Value ExpressionsWorking with Numeric Value ExpressionsUsing the CASE Value ExpressionUsing the CAST Value ExpressionUse Special Values
11 Accessing Multiple Tables
Perform Basic Join OperationsUsing Correlation NamesCreating Joins with More than Two TablesCreating the Cross JoinCreating the Self-JoinJoin Tables with Shared Column NamesCreating the Natural JoinCreating the Named Column JoinUse the Condition JoinCreating the Inner JoinCreating the Outer JoinPerform Union Operations
12 Using Subqueries to Access and Modify Data
Create Subqueries That Return Multiple RowsUsing the IN PredicateUsing the EXISTS PredicateUsing Quantified Comparison PredicatesCreate Subqueries That Return One ValueWork with Correlated SubqueriesUse Nested SubqueriesUse Subqueries to Modify DataUsing Subqueries to Insert DataUsing Subqueries to Update DataUsing Subqueries to Delete Data
PART III Advanced Data Access
13 Creating SQL-Invoked RoutinesUnderstand SQL-Invoked RoutinesSQL-Invoked Procedures and FunctionsWorking with the Basic SyntaxCreate SQL-Invoked ProceduresInvoking SQL-Invoked ProceduresAdd Input Parameters to Your ProceduresUsing Procedures to Modify DataAdd Local Variables to Your ProceduresWork with Control StatementsCreate Compound StatementsCreate Conditional StatementsCreate Looping StatementsAdd Output Parameters to Your ProceduresCreate SQL-Invoked Functions
14 Creating SQL Triggers
Understand SQL TriggersTrigger Execution ContextCreate SQL TriggersReferencing Old and New ValuesDropping SQL TriggersCreate Insert TriggersCreate Update TriggersCreate Delete Triggers
15 Using SQL Cursors
Understand SQL CursorsDeclaring and Opening SQL CursorsDeclare a CursorWorking with Optional Syntax ElementsCreating a Cursor DeclarationOpen and Close a CursorRetrieve Data from a CursorUse Positioned UPDATE and DELETE StatementsUsing the Positioned UPDATE StatementUsing the Positioned DELETE Statement
16 Managing SQL Transactions
Understand SQL TransactionsSet Transaction PropertiesSpecifying an Isolation LevelSpecifying a Diagnostics SizeCreating a SET TRANSACTION StatementStart a TransactionSet Constraint DeferabilityCreate Savepoints in a TransactionReleasing a SavepointTerminate a TransactionCommitting a TransactionRolling Back a Transaction
17 Accessing SQL Data from Your Host Program
Invoke SQL DirectlyEmbed SQL Statements in Your ProgramCreating an Embedded SQL StatementUsing Host Variables in Your SQL StatementsRetrieving SQL DataError HandlingCreate SQL Client ModulesDefining SQL Client ModulesUse an SQL Call-Level InterfaceAllocating HandlesExecuting SQL StatementsWorking with Host VariablesRetrieving SQL Data
18 Working with XML Data
Learn the Basics of XMLLearn About SQL/XMLThe XML Data TypeSQL/XML FunctionsSQL/XML Mapping Rule
PART IV Appendices
A Answers to Self Test
B SQL:2006 Keywords
SQL Reserved KeywordsSQL Nonreserved Keywords
C SQL Code Used in Try This Exercises
SQL Code by Try This ExerciseThe INVENTORY Database
Index

Content preview from SQL: A Beginner's Guide, Third Edition, 3rd Edition

Chapter 10Working with Functions and Value Expressions

Key Skills & Concepts

Use Set Functions

Use Value Functions

Use Value Expressions

Use Special Values

In earlier parts of the book, you have been briefly introduced to various value-related functions and expressions. These ...

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

SQL: A Beginner's Guide, Fourth Edition, 4th Edition

Publisher Resources

ISBN: 9780071548649

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

SQL: A Beginner's Guide, Third Edition, 3rd Edition

by Andy Oppel, Robert Sheldon

Chapter 10Working with Functions and Value Expressions

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.