SQL Essentials For Dummies (For Dummies (Computer/Tech))

A right-to-the-point guide on all the key topics of SQL programming SQL Essentials For Dummies is your quick reference to all the core concepts of SQL―a valuable common standard language used in relational databases. This useful guide is straightforward―with no excess review, wordy explanations, or fluff―so you get what you need, fast. Great for a brush-up on the basics or as an everyday desk reference, this book is one you can rely on. Strengthen your understanding of the basics of SQL Review what you've already learned or pick up key skills Use SQL to create, manipulate, and control relational databases Jog your memory on the essentials as you work and get clear answers to your questions Perfect for supplementing classroom learning, reviewing for a certification, and staying knowledgeable on the job, SQL Essentials For Dummies is the convenient, direct, and digestible reference you've been looking for. Title Page Copyright Page Table of Contents Introduction About This Book Foolish Assumptions Icons Used in This Book Where to Go from Here Chapter 1 Getting to Know SQL Knowing What SQL Does Knowing What SQL Does Not Do Choosing and Using an Available RDBMS Implementation Microsoft Access Microsoft SQL Server IBM DB2 Oracle Database Sybase SQL Anywhere MySQL PostgreSQL Chapter 2 Creating a Database with SQL First Things First: Planning Your Database Building Tables Locating table rows with keys Using the CREATE TABLE statement Setting Constraints Column constraints Table constraints Working with Keys and Indexes Ensuring Data Validity with Domains Establishing Relationships between Tables Altering Table Structure Deleting Tables Chapter 3 Drilling Down to the SQL Nitty-Gritty SQL’s Data Types Exact numerics INTEGER SMALLINT BIGINT NUMERIC DECIMAL DECFLOAT Approximate numerics REAL DOUBLE PRECISION FLOAT Character strings CHARACTER CHARACTER VARYING CHARACTER LARGE OBJECT NATIONAL CHARACTER, NATIONAL CHARACTER VARYING, and NATIONAL CHARACTER LARGE OBJECT Binary strings BINARY BINARY VARYING BINARY LARGE OBJECT Booleans Datetimes DATE TIME WITHOUT TIME ZONE TIME WITH TIME ZONE TIMESTAMP WITHOUT TIME ZONE TIMESTAMP WITH TIME ZONE Intervals XML ROW Collection ARRAY Multiset REF JSON User-defined Distinct types Structured types Handling Null Values Applying Constraints Column constraints NOT NULL UNIQUE CHECK Table constraints Foreign key constraints Assertions Operating on Data with the Data Manipulation Language Retrieving data from a database Adding data to a table Manually adding data Adding incomplete records Updating data in a table Deleting data from a table Chapter 4 Values, Variables, Functions, and Expressions Entering Data Values Row values Column references Literal values Variables Special variables Working with Functions Summarizing data with set functions COUNT AVG MAX MIN SUM LISTAGG ANY_VALUE Dissecting data with value functions String value functions Numeric value functions Datetime value functions Polymorphic table functions Using Expressions Numeric value expressions String value expressions Datetime value expressions Interval value expressions Boolean value expressions Array value expressions Conditional value expressions Handling different cases The NULLIF special CASE Bypassing null values with COALESCE Converting data types with a CAST expression Row value expressions Chapter 5 SELECT Statements and Modifying Clauses Finding Needles in Haystacks with the SELECT Statement Modifying Clauses FROM clauses WHERE clauses Comparison predicates BETWEEN IN and NOT IN LIKE and NOT LIKE NULL ALL, SOME, and ANY EXISTS UNIQUE DISTINCT OVERLAPS MATCH The MATCH predicate and referential integrity Logical connectives GROUP BY clauses HAVING clauses ORDER BY clauses Chapter 6 Querying Multiple Tables with Subqueries Introducing Subqueries Subqueries that return multiple values Subqueries that retrieve rows satisfying a condition Subqueries that retrieve rows that don’t satisfy a condition Subqueries that return a single value Quantified subqueries: Returning a single value Correlated subqueries Using a subquery as an existence test Introducing a correlated subquery with the IN keyword Introducing a correlated subquery with a comparison operator Using correlated subqueries in a HAVING clause Using Subqueries in INSERT, DELETE, and UPDATE Statements Chapter 7 Querying Multiple Tables with Relational Operators UNION UNION ALL UNION CORRESPONDING INTERSECT EXCEPT JOINS Cartesian product or cross join Equi-join Natural join Condition join Column-name join Inner join Outer join Left outer join Right outer join Full outer join ON versus WHERE Join Conditions and Clustering Indexes Chapter 8 Cursors Declaring a Cursor The query expression Ordering the query result set Updating table rows Sensitive versus insensitive cursors Scrolling a cursor Holding a cursor Declaring a result set cursor Opening a Cursor Operating on a Single Row FETCH syntax Absolute versus relative fetches Deleting a row Updating a row Closing a Cursor Chapter 9 Assigning Access Privileges Working with the SQL Data Control Language Identifying Authorized Users Understanding user identifiers Getting familiar with roles Creating roles Destroying roles Classifying Users Granting Privileges Looking at data Deleting data Adding data Changing data Using certain database facilities Responding to an event Defining new data types Executing an SQL statement Doing it all Passing on the power Revoking Privileges Granting Roles Revoking Roles Chapter 10 Ten Retrieval Tips Verify the Database Structure Try Queries on a Test Database Double-Check Queries That Include Joins Triple-Check Queries with Subselects Summarize Data with GROUP BY Watch GROUP BY Clause Restrictions Use Parentheses with AND, OR, and NOT Control Retrieval Privileges Back Up Your Databases Regularly Handle Error Conditions Gracefully Index EULA