book

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

by Andy Oppel

November 2015

Beginner

544 pages

16h 31m

English

McGraw-Hill

Read now

Unlock full access

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

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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
SQL Reserved KeywordsSQL Nonreserved Keywords

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

Appendix C

SQL Code Used in Try This Exercises

In the Try This exercises throughout this book, you created a number of SQL statements that allowed you to define database objects in the INVENTORY database, modify those objects, insert data into the tables that you created, retrieve that data, and update and delete the data. The statements are included here so that you can see the progression of the statements as you move through the Try This exercises and also so that you can reference them as necessary in case you need to redo certain elements of an exercise. In addition, I’ve provided the SQL statements (in a consolidated form) used to create the INVENTORY database and populate the tables with data. You might find that as you work your way through ...