book

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

Name: SQL: A Beginner's Guide, Fourth Edition, 4th Edition
Author: Andy Oppel
ISBN: 9780071842570

by Andy Oppel

November 2015

Beginner

544 pages

16h 31m

English

McGraw-Hill

Read now

Unlock full access

Cover
Title Page
Copyright Page
Dedication
Contents at a Glance
Contents
Acknowledgments
Introduction
PART I Relational Databases and SQL
1 Introduction to Relational Databases and SQL
Understand Relational DatabasesThe Relational ModelTry This 1-1: Normalizing Data and Identifying RelationshipsLearn About SQLThe SQL EvolutionTypes of SQL StatementsTypes of ExecutionSQL Standard versus Product ImplementationsTry This 1-2: Connecting to a DatabaseChapter 1 Self Test

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 DatabaseTry This 2-1: Creating a Database and a SchemaChapter 2 Self Test
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 ValuesSpecify Identity Column and Generation OptionsMaintaining Independent Sequence GeneratorsTry This 3-1: Creating SQL TablesAlter SQL TablesDrop SQL TablesTruncate SQL TablesTry This 3-2: Altering and Deleting SQL TablesChapter 3 Self Test
4 Enforcing Data Integrity
Understand Integrity ConstraintsUse NOT NULL ConstraintsAdd UNIQUE ConstraintsAdd PRIMARY KEY ConstraintsAdd FOREIGN KEY ConstraintsThe MATCH ClauseThe <referential triggered action> ClauseTry This 4-1: Adding NOT NULL, Unique, and Referential ConstraintsDefine CHECK ConstraintsDefining AssertionsCreating Domains and Domain ConstraintsTry This 4-2: Adding a CHECK ConstraintChapter 4 Self Test
5 Creating SQL Views
Add Views to the DatabaseDefining SQL ViewsCreate Updateable ViewsUsing the WITH CHECK OPTION ClauseDrop Views from the DatabaseTry This 5-1: Adding Views to Your DatabaseChapter 5 Self Test
6 Managing Database Security
Understand the SQL Security ModelSQL SessionsAccessing Database ObjectsCreate and Delete RolesGrant and Revoke PrivilegesRevoking PrivilegesGrant and Revoke RolesRevoking RolesTry This 6-1: Managing Roles and PrivilegesChapter 6 Self Test
PART II Data Access and Modification
7 Querying SQL Data
Use 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 ResultsTry This 7-1: Querying the Inventory DatabaseChapter 7 Self Test
8 Modifying SQL Data
Insert SQL DataInserting Values from a SELECT StatementUpdate SQL DataUpdating Values from a SELECT StatementDelete SQL DataMerge SQL DataTry This 8-1: Modifying SQL DataChapter 8 Self Test
9 Using Predicates
Compare SQL DataUsing the BETWEEN PredicateReturn Null ValuesReturn Similar ValuesTry This 9-1: Using Predicates in SQL StatementsReference Additional Sources of DataUsing the IN PredicateUsing the EXISTS PredicateQuantify Comparison PredicatesUsing the SOME and ANY PredicatesUsing the ALL PredicateTry This 9-2: Using Subqueries in PredicatesChapter 9 Self Test
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 ValuesTry This 10-1: Using Functions and Value ExpressionsChapter 10 Self Test
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 OperationsTry This 11-1: Querying Multiple TablesChapter 11 Self Test
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 DataTry This 12-1: Working with SubqueriesChapter 12 Self Test
13 Working with Temporal Data
Create and Use System-Versioned TablesChanging Data in a System-Versioned TableQuerying a System-Versioned TableCreate and Use Application-Time Period TablesInserting Data into an Application-Time Period TableUpdating Data in an Application-Time Period TableDeleting Data from an Application-Time Period TableTry This 13-1: Working with Application-Time Period TablesCreate and Use System-Versioned Application-Time Period TablesInserting Data into a System-Versioned Application-Time Period TableUpdating Data in a System-Versioned Application-Time Period TableDeleting Data from a System-Versioned Application-Time Period TableChapter 13 Self Test
PART III Advanced Data Access
14 Creating SQL-Invoked Routines
Understand 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 StatementsTry This 14-1: Creating SQL-Invoked ProceduresAdd Output Parameters to Your ProceduresCreate SQL-Invoked FunctionsTry This 14-2: Creating SQL-Invoked FunctionsChapter 14 Self Test
15 Creating SQL Triggers
Understand SQL TriggersTrigger Execution ContextCreate SQL TriggersReferencing Old and New ValuesDropping SQL TriggersCreate Insert TriggersCreate Update TriggersCreate Delete TriggersCreate Instead Of TriggersTry This 15-1: Creating SQL TriggersChapter 15 Self Test
16 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 StatementTry This 16-1: Working with SQL CursorsChapter 16 Self Test
17 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 TransactionTry This 17-1: Working with TransactionsChapter 17 Self Test
18 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 HandlingTry This 18-1: Embedding SQL StatementsCreate SQL Client ModulesDefining SQL Client ModulesUse an SQL Call-Level InterfaceAllocating HandlesExecuting SQL StatementsWorking with Host VariablesRetrieving SQL DataTry This 18-2: Using the SQL Call-Level InterfaceChapter 18 Self Test
19 Working with XML Data
Learn the Basics of XMLLearn About SQL/XMLThe XML Data TypeSQL/XML FunctionsSQL/XML Mapping RuleTry This 19-1: Using SQL/XML FunctionsChapter 19 Self Test
PART IV Appendices
A SQL Keywords
SQL Reserved KeywordsSQL Nonreserved Keywords
B Answers to Self Tests
C SQL Code Used in Try This Exercises
Index

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

Chapter 10 Working 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 values and expressions are used in examples and Try This ...

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, Third Edition, 3rd Edition

Publisher Resources

ISBN: 9780071842570

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

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

by Andy Oppel

Chapter 10