Home Page Icon
Home Page
Table of Contents for
SQL for Microsoft® Access, 2nd EditionSQL for Microsoft® Access, 2nd Edition
Close
SQL for Microsoft® Access, 2nd EditionSQL for Microsoft® Access, 2nd Edition
by Neal A. Berkowitz, Cecelia L. Allison
SQL for Microsoft Access, 2nd Edition
Contents
Acknowledgments
Introduction
The Importance of SQL in Microsoft Access
Code Interpretations
Companion Files
Chapter 1 The Relational Database Structure
Introduction
Definitions
Early Forms of Data Storage
The Relational Database Structure
Tables
Keys
The Planning Stage
Data Modeling
Entities and Relationships
Normalization
Client/Server Databases
Database Management Systems
Summary
Quiz 1
Project 1
Chapter 2 Structured Query Language and Microsoft Access
Introduction
Definitions
Structured Query Language
SQL Versions
Switching to Version SQL-92 in Microsoft Access 2007
Switching to Version SQL-92 in Microsoft Access 2003
SQL Components
SQL Syntax
The Power of SQL in Microsoft Access
The Query Wizard
The Query Design Tool and SQL View
Opening Microsoft Access and Switching to SQL View
Opening Microsoft Access
Switching to SQL View in Microsoft Access 2007
Switching to SQL View in Microsoft Access 2003
Summary
Quiz 2
Project 2
Chapter 3 Creating Tables and Inserting Records
Introduction
Keywords
Definitions
The Data Definition Language Component
CREATE TABLE Syntax
Data Types
Example 1
Constraints
Example 2
NULL/NOT NULL Constraint
PRIMARY KEY Constraint
Example 3
FOREIGN KEY Constraint
UNIQUE Constraint
Adding Constraints to Existing Tables
Example 4
Example 5
Constraint Syntax
Inserting Records
Example 6
Inserting Data without Specifying Column Names
Example 7
Inserting NULL Values
Example 8
Copying Records from One Table to an Existing Table
Example 9
Copying Records from One Table to a New Table Simultaneously
Example 10
Updating Records
Update a Record with a Text Value
Example 11
Update a Record with a New Calculated Value
Example 12
Update Multiple Columns
Example 13
Update a Column that Contains a Date
Example 14
Deleting Records
Example 15
Summary
Quiz 3
Project 3
Chapter 4 Retrieving Records
Introduction
Keywords
Definitions
The SELECT Statement
Example 1
Example 2
The ORDER BY Clause
Sorting in Descending Order
Example 3
Sorting in Ascending Order
Example 4
Sorting Multiple Columns
Example 5
Example 6
Sorting Using Numbers
Example 7
Example 8
Handling Duplicate Values
The DISTINCT Keyword
Example 9
The DISTINCTROW Keyword
Handling Duplicate Subsets of the Entire Result Collection
The TOP Keyword
Example 10
Example 11
The TOP PERCENT Keywords
Example 12
Example 13
Creating an Alias
Example 14
Create an Alias that Contains a Space
Example 15
Concatenation
Concatenate Multiple Fields and Characters
Example 16
Concatenate Multiple Fields from Multiple Tables
Example 17
Summary
Quiz 4
Project 4
Chapter 5 Filtering Retrieved Records
Introduction
Keywords
Definitions
The WHERE Clause
Comparison Operators
Logical Operators
Operator Precedence
The AND, OR, =, and < Operators
Example 1
The LIKE Operator
Example 2
Example 3
Example 4
Example 5
Example 6
Example 7
Example 8
Example 9
The BETWEEN Operator
Example 10
The IN and NOT Operators
Example 11
Example 12
The IS NULL and IS NOT NULL Operators
Example 13
Summary
Quiz 5
Project 5
Chapter 6 Creating Calculated Fields
Introduction
Keywords
Definitions
Operators and Functions
Arithmetic Operators
Use an Arithmetic Operator with SELECT
Example 1
Use an Arithmetic Operator in the WHERE clause
Example 2
Aggregate Functions
Using the AVG (), FIRST (), LAST (), SUM (), MAX (), and MIN () Functions
Example 3
Using the COUNT () Function
Example 4
String Functions and Operations
Use of the + and &
Example 5
Using the LEFT (), UCASE (), LEN (), and TRIM () Functions
Example 6
Using the MID () and INSTR () Functions
Example 7
Date and Time Functions
Inserting Dates into a Table
Example 8
Using the FORMAT () Function
Example 9
Using the DATE (), TIME (), MONTH (), DAY (), and YEAR () Functions
Example 10
Miscellaneous Functions
Using the CCUR () Function
Example 11
Summary
Quiz 6
Project 6
Chapter 7 Grouping Data
Introduction
Keywords
Definitions
The GROUP BY Clause
Example 1
Using the GROUP BY Clause with the ORDER BY Clause
Example 2
The HAVING Clause
Example 3
Using the HAVING Clause with the WHERE Clause
Example 4
Summary
Quiz 7
Project 7
Chapter 8 Creating Table Joins and Unions
Introduction
Keywords
Definitions
Table Joins — An Overview
Qualification
Inner Join
Example 1
Using the DISTINCTROW Keyword
Example 2
Self Join
Example 3
Nested Join
Example 4
Outer Joins
Right Outer Join
Example 5
Left Outer Join
Example 6
Create a Join that Contains an Aggregate Function
Example 7
UNION and UNION ALL Keywords
UNION
Example 8
UNION ALL
Example 9
Summary
Quiz 8
Project 8
Chapter 9 Creating Subqueries
Introduction
Keywords
Definitions
Subqueries
Correlated and Non-Correlated Subqueries
The IN Subquery
Example 1
The EXISTS Subquery
Example 2
The ANY and SOME Subqueries
Example 3
The ALL Subquery
Example 4
Nested Subqueries
Example 5
Using a Subquery to Find the Second Highest Value
Example 6
Summary
Quiz 9
Project 9
Chapter 10 Creating Views
Introduction
Keywords
Definitions
Creating a View
Example 1
Creating a View Using the CREATE VIEW Keywords in SQL-92
Create a View that Contains a Complex Join
Example 2
Query the CustomersTotalTransactions View
Example 3
Filtering a Record through a View
Example 4
Example 5
Updating a Record through a View
Example 6
Deleting a View
Example 7
Summary
Quiz 10
Project 10
Chapter 11 Table Management and Indexes
Introduction
Keywords
Definitions
Adding a Column to an Existing Table
Example 1
Changing a Column
Example 2
Setting a Default Value for a Column
Example 3
Removing a Column from a Table
Example 4
Removing a Table
Improving Data Retrieval Time Using Indexes
Index Options
Creating an Index
Example 5
Indexing in Descending Order
Example 6
Viewing and Editing Indexes
Deleting an Index
Example 7
Summary
Quiz 11
Project 11
Chapter 12 Temporary Tables vs. Views
Introduction
Definitions
Creating a View
Example 1
Creating a Temporary Table
Example 2
Accessing the Temporary Table
Querying a Temporary Table
Example 3
Indexing a Temporary Table
Example 4
Updating a Temporary Table
Example 5
Copying Records from One Temporary Table to a New Temporary Table Simultaneously
Example 6
Deleting a Temporary Table
Example 7
Why Do We Need Temporary Tables?
Summary
Quiz 12
Project 12
Chapter 13 Parameter Queries
Introduction
Definitions
Parameter Queries
Creating a Simple Query
Example 1
Creating a Parameter Query
Example 2
Customizing Your Dialog Box
Example 3
Creating Multiple Prompts
Example 4
Example 5
Using the LIKE Keyword to Prompt the User
Example 6
Prompting the User for Dates
Example 7
Creating a Button to Prompt the User
Example 8
View the New Button on the Form
SQL Syntax for a Parameter Query in SQL View
Example 9
Example 10
Non-parameter Parameter Queries
Summary
Quiz 13
Project 13
Chapter 14 Integrating SQL Script in VBA Code
Introduction
Definitions
Fixed Queries vs. “On-the-Fly ” Queries
Filtered Recordsets for Forms
Filtered Recordsets for Combo Boxes
Recordsets for Subforms
Report Filters
Summary
Chapter 15 Integrating SQL Script into ASP Code
Introduction
Definitions
Basics
Building the Components
ODBC Connection
Code
Building SQL Statements
Summary
Chapter 16 Access Projects
Introduction
Definitions
Overview
Differences between Access Projects and Access Databases
Project Window
Tables
Notes
Database Diagrams
Queries
Views
Stored Procedures
Functions
Summary
Quiz 16
Project 16
Chapter 17 Concluding Thoughts
Introduction
Common Rules
Summary
Appendix A Answers to Quizzes and Projects
Quiz 1
Project 1
Quiz 2
Project 2
Quiz 3
Project 3
Quiz 4
Project 4
Quiz 5
Project 5
Quiz 6
Project 6
Quiz 7
Project 7
Quiz 8
Project 8
Quiz 9
Project 9
Quiz 10
Project 10
Quiz 11
Project 11
Quiz 12
Project 12
Quiz 13
Project 13
Quiz 16
Appendix B Frequently Used SQL Keywords in Microsoft Access
Appendix C Terms and Definitions
Appendix D Microsoft Access Data Types
Appendix E SQL Script to Create the Tables in This Book
Create and Populate the Activities Table
Create and Populate the Committee1 Table
Create and Populate the Committee2 Table
Create and Populate the Computers Table
Create and Populate the Customers Table
Create and Populate the Customers2 Table
Create and Populate the Departments Table
Create and Populate the Employees Table
Create and Populate the Friends Table
Create and Populate the Manufacturers Table
Create and Populate the Numbers Table
Create and Populate the Products Table
Create and Populate the Sales Table
Create and Populate the Tools Table
Create and Populate the Toys Table
Create and Populate the Transactions Table
Index
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Next
Next Chapter
SQL for Microsoft® Access, 2nd Edition
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset