10/10/23, 9:37 AM SQL - A Practical Introduction
CONTENTS
1 INTRODUCTION
1.1 A Collection of Related Data : Databases and Database Management
about:blank 1/180
,10/10/23, 9:37 AM SQL - A Practical Introduction
CONTENTS
1 INTRODUCTION
1.1 A Collection of Related Data : Databases and Database Management
Systems.
1.2 The Database as a Collection of Tables: Relational Databases and SQL.
1.2.1 Tables, Columns and Rows.
1.2.2 The Primary Key.
1.3 Communicating to the DBMS What You Want it to do: Introduction to the
SQL language.
1.4 A Research Project Conducted by IBM: The History of SQL.
1.5 SQL Commands Build Upon Themselves: Organization of This Book.
1.5.1 Notational Conventions.
2 A ROUGH GUIDE TO SQL.
2.1 Consider the Simple Address Book: A Basic Relational Database.
2.2 SQL Commands Fall into Different Categories: Subdivisions of SQL.
2.3 Enter SQL Statements at the Command Line: Using interpretive SQL.
2.4 Use the CREATE TABLE statement: Creating Database Tables.
2.5 Use the INSERT INTO Statement: Adding Data to Tables.
2.6 Use the SELECT Statement: Extracting data from tables.
2.7 Use the UPDATE and DELETE Statements: Modifying data.
2.8 Another Kind of Table, Called a Virtual Table: Views.
2.9 Prevent Access to Sensitive Information: Database security.
3 CREATING AND MAINTAINING TABLES.
3.1 The ANSI Standard Makes Such a Distinction...: The DDL and the ANSI/ISO
standard.
about:blank 2/180
,10/10/23, 9:37 AM SQL - A Practical Introduction
3 CREATING AND MAINTAINING TABLES.
3.1 The ANSI Standard Makes Such a Distinction...: The DDL and the ANSI/ISO
standard.
3.2 Single and Multiple Database Architectures: The structure of SQL databases.
3.3 Creating a Database Table: The CREATE TABLE command.
3.4 Apply Restrictions to Groups of Columns: Column and table modifiers.
3.4.1 The NOT NULL modifier.
3.4.2 The UNIQUE modifier.
3.4.3 The INDEX modifier.
3.4.4 The PRIMARY KEY modifier.
3.4.5 The FOREIGN KEY modifier.
3.4.6 The DEFAULT modifier.
3.4.7 The CHECK modifier
3.5 Indexes are Ordered for Extremely Fast Searches: Indexes.
3.6 Changing the Structure of a Table: The ALTER TABLE Command.
3.7 Remove Redundant Tables from the Database: The DROP TABLE
Command.
4 QUERYING SQL TABLES.
4.1 The most basic query: The Simple SELECT statement.
4.1.1 Calculated columns.
4.2 Selecting rows for output: The WHERE clause.
4.2.1 Comparison Test Operators: =, <, <=, >, >=, <>.
4.2.2 Range Test Operator: BETWEEN.
4.2.3 Set Membership Test Operator: IN.
4.2.4 Pattern Matching Test Operator: LIKE.
4.2.5 NULL Value Test Operator: IS NULL.
4.2.6 Logical Operators: AND, OR and NOT.
4.3 Ordering the output of a query: The ORDER BY clause.
4.4 Summary of data in tables: The ANSI aggregate functions.
4.4.1 The number of values or rows: The COUNT() function.
4.4.2 The total of values: The SUM() function.
4.4.3 The average value: The AVG() function.
4.4.4 The minimum and maximum values: The MIN() and MAX() functions.
4.4.5 Sub-totals of grouped values: The GROUP BY clause.
4.4.6 Eliminating groups of data: The HAVING clause.
4.5 Retrieving data from multiple tables: SQL joins.
4.5.1 Classification of joins.
46 Joining a table to itself: The self-join
about:blank 3/180
, 10/10/23, 9:37 AM SQL - A Practical Introduction
4.4.1 The number of values or rows: The COUNT() function.
4.4.2 The total of values: The SUM() function.
4.4.3 The average value: The AVG() function.
4.4.4 The minimum and maximum values: The MIN() and MAX() functions.
4.4.5 Sub-totals of grouped values: The GROUP BY clause.
4.4.6 Eliminating groups of data: The HAVING clause.
4.5 Retrieving data from multiple tables: SQL joins.
4.5.1 Classification of joins.
4.6 Joining a table to itself: The self-join.
4.7 Nested SELECT statements: The subquery.
4.8 Linked SELECT statements: The correlated subquery.
4.9 Does the subquery retrieve values: The EXISTS operator.
4.10 Two more subquery operators: The ANY and ALL operators.
4.11 Combining multiple queries: The UNION clause.
5 ADDING AND UPDATING DATA.
5.1 Adding Single Rows at a Time: INSERT command.
5.2 Adding Multiple Rows at a Time: The INSERT with SELECT command.
5.3 Modifying Data in Rows: The UPDATE command.
5.4 Removing Rows Form Tables: The DELETE command.
6 DATA INTEGRITY
6.1 Keeping the Data Tidy: The Basics of Data Integrity.
6.2 Fields That Must Have Values: Non-NULL Columns.
6.3 Values Must be the Right Values: Data Validity.
6.4 Primary key values must be unique: Entity Integrity.
6.5 All Child Rows must have parents: Referential Integrity.
6.6 Integrity Requirements of the User: SQL Triggers.
7 VIEWS
7.1 Restrict the Data You Can See: What is a view?
7.2 How To Make Views: The CREATE VIEW command.
about:blank 4/180
CONTENTS
1 INTRODUCTION
1.1 A Collection of Related Data : Databases and Database Management
about:blank 1/180
,10/10/23, 9:37 AM SQL - A Practical Introduction
CONTENTS
1 INTRODUCTION
1.1 A Collection of Related Data : Databases and Database Management
Systems.
1.2 The Database as a Collection of Tables: Relational Databases and SQL.
1.2.1 Tables, Columns and Rows.
1.2.2 The Primary Key.
1.3 Communicating to the DBMS What You Want it to do: Introduction to the
SQL language.
1.4 A Research Project Conducted by IBM: The History of SQL.
1.5 SQL Commands Build Upon Themselves: Organization of This Book.
1.5.1 Notational Conventions.
2 A ROUGH GUIDE TO SQL.
2.1 Consider the Simple Address Book: A Basic Relational Database.
2.2 SQL Commands Fall into Different Categories: Subdivisions of SQL.
2.3 Enter SQL Statements at the Command Line: Using interpretive SQL.
2.4 Use the CREATE TABLE statement: Creating Database Tables.
2.5 Use the INSERT INTO Statement: Adding Data to Tables.
2.6 Use the SELECT Statement: Extracting data from tables.
2.7 Use the UPDATE and DELETE Statements: Modifying data.
2.8 Another Kind of Table, Called a Virtual Table: Views.
2.9 Prevent Access to Sensitive Information: Database security.
3 CREATING AND MAINTAINING TABLES.
3.1 The ANSI Standard Makes Such a Distinction...: The DDL and the ANSI/ISO
standard.
about:blank 2/180
,10/10/23, 9:37 AM SQL - A Practical Introduction
3 CREATING AND MAINTAINING TABLES.
3.1 The ANSI Standard Makes Such a Distinction...: The DDL and the ANSI/ISO
standard.
3.2 Single and Multiple Database Architectures: The structure of SQL databases.
3.3 Creating a Database Table: The CREATE TABLE command.
3.4 Apply Restrictions to Groups of Columns: Column and table modifiers.
3.4.1 The NOT NULL modifier.
3.4.2 The UNIQUE modifier.
3.4.3 The INDEX modifier.
3.4.4 The PRIMARY KEY modifier.
3.4.5 The FOREIGN KEY modifier.
3.4.6 The DEFAULT modifier.
3.4.7 The CHECK modifier
3.5 Indexes are Ordered for Extremely Fast Searches: Indexes.
3.6 Changing the Structure of a Table: The ALTER TABLE Command.
3.7 Remove Redundant Tables from the Database: The DROP TABLE
Command.
4 QUERYING SQL TABLES.
4.1 The most basic query: The Simple SELECT statement.
4.1.1 Calculated columns.
4.2 Selecting rows for output: The WHERE clause.
4.2.1 Comparison Test Operators: =, <, <=, >, >=, <>.
4.2.2 Range Test Operator: BETWEEN.
4.2.3 Set Membership Test Operator: IN.
4.2.4 Pattern Matching Test Operator: LIKE.
4.2.5 NULL Value Test Operator: IS NULL.
4.2.6 Logical Operators: AND, OR and NOT.
4.3 Ordering the output of a query: The ORDER BY clause.
4.4 Summary of data in tables: The ANSI aggregate functions.
4.4.1 The number of values or rows: The COUNT() function.
4.4.2 The total of values: The SUM() function.
4.4.3 The average value: The AVG() function.
4.4.4 The minimum and maximum values: The MIN() and MAX() functions.
4.4.5 Sub-totals of grouped values: The GROUP BY clause.
4.4.6 Eliminating groups of data: The HAVING clause.
4.5 Retrieving data from multiple tables: SQL joins.
4.5.1 Classification of joins.
46 Joining a table to itself: The self-join
about:blank 3/180
, 10/10/23, 9:37 AM SQL - A Practical Introduction
4.4.1 The number of values or rows: The COUNT() function.
4.4.2 The total of values: The SUM() function.
4.4.3 The average value: The AVG() function.
4.4.4 The minimum and maximum values: The MIN() and MAX() functions.
4.4.5 Sub-totals of grouped values: The GROUP BY clause.
4.4.6 Eliminating groups of data: The HAVING clause.
4.5 Retrieving data from multiple tables: SQL joins.
4.5.1 Classification of joins.
4.6 Joining a table to itself: The self-join.
4.7 Nested SELECT statements: The subquery.
4.8 Linked SELECT statements: The correlated subquery.
4.9 Does the subquery retrieve values: The EXISTS operator.
4.10 Two more subquery operators: The ANY and ALL operators.
4.11 Combining multiple queries: The UNION clause.
5 ADDING AND UPDATING DATA.
5.1 Adding Single Rows at a Time: INSERT command.
5.2 Adding Multiple Rows at a Time: The INSERT with SELECT command.
5.3 Modifying Data in Rows: The UPDATE command.
5.4 Removing Rows Form Tables: The DELETE command.
6 DATA INTEGRITY
6.1 Keeping the Data Tidy: The Basics of Data Integrity.
6.2 Fields That Must Have Values: Non-NULL Columns.
6.3 Values Must be the Right Values: Data Validity.
6.4 Primary key values must be unique: Entity Integrity.
6.5 All Child Rows must have parents: Referential Integrity.
6.6 Integrity Requirements of the User: SQL Triggers.
7 VIEWS
7.1 Restrict the Data You Can See: What is a view?
7.2 How To Make Views: The CREATE VIEW command.
about:blank 4/180