␡
- Sams Teach Yourself SQL in 24 Hours, Third Edition
- Table of Contents
- Copyright
- About the Authors
- Acknowledgments
- Tell Us What You Think!
- Introduction
- Part I: A SQL Concepts Overview
- Hour 1. Welcome to the World of SQL
- SQL Definition and History
- SQL Sessions
- Types of SQL Commands
- An Introduction to the Database Used in This Book
- Summary
- Q&A
- Workshop
- Part II: Building Your Database
- Hour 2. Defining Data Structures
- What Is Data?
- Basic Data Types
- Summary
- Q&A
- Workshop
- Hour 3. Managing Database Objects
- What Are Database Objects?
- What Is a Schema?
- A Table: The Primary Storage for Data
- Integrity Constraints
- Summary
- Q&A
- Workshop
- Hour 4. The Normalization Process
- Normalizing a Database
- Summary
- Q&A
- Workshop
- Hour 5. Manipulating Data
- Overview of Data Manipulation
- Populating Tables with New Data
- Updating Existing Data
- Deleting Data from Tables
- Summary
- Q&A
- Workshop
- Hour 6. Managing Database Transactions
- What Is a Transaction?
- What Is Transactional Control?
- Transactional Control and Database Performance
- Summary
- Q&A
- Workshop
- Part III: Getting Effective Results from Queries
- Hour 7. Introduction to the Database Query
- What Is a Query?
- Introduction to the <tt>SELECT</tt> Statement
- Examples of Simple Queries
- Summary
- Q&A
- Workshop
- Hour 8. Using Operators to Categorize Data
- What Is an Operator in SQL?
- Comparison Operators
- Logical Operators
- Conjunctive Operators
- Negating Conditions with the <tt>NOT</tt> Operator
- Arithmetic Operators
- Summary
- Q&A
- Workshop
- Hour 9. Summarizing Data Results from a Query
- What Are Aggregate Functions?
- Summary
- Q&A
- Workshop
- Hour 10. Sorting and Grouping Data
- Why Group Data?
- The <tt>GROUP BY</tt> Clause
- <tt>GROUP BY</tt> Versus <tt>ORDER BY</tt>
- The <tt>HAVING</tt> Clause
- Summary
- Q&A
- Workshop
- Hour 11. Restructuring the Appearance of Data
- The Concepts of ANSI Character Functions
- Various Common Character Functions
- Miscellaneous Character Functions
- Mathematical Functions
- Conversion Functions
- The Concept of Combining Character Functions
- Summary
- Q&A
- Workshop
- Hour 12. Understanding Dates and Times
- How Is a Date Stored?
- Date Functions
- Date Conversions
- Summary
- Q&A
- Workshop
- Part IV: Building Sophisticated Database Queries
- Hour 13. Joining Tables in Queries
- Selecting Data from Multiple Tables
- Types of Joins
- Join Considerations
- Summary
- Q&A
- Workshop
- Hour 14. Using Subqueries to Define Unknown Data
- What Is a Subquery?
- Embedding a Subquery Within a Subquery
- Summary
- Q&A
- Workshop
- Hour 15. Combining Multiple Queries into One
- Single Queries Versus Compound Queries
- Why Would I Ever Want to Use a Compound Query?
- Compound Query Operators
- Using an <tt>ORDER BY</tt> with a Compound Query
- Using <tt>GROUP BY</tt> with a Compound Query
- Retrieving Accurate Data
- Summary
- Workshop
- Q&A
- Part V: SQL Performance Tuning
- Hour 16. Using Indexes to Improve Performance
- What Is an Index?
- How Do Indexes Work?
- The <tt>CREATE INDEX</tt> Command
- Types of Indexes
- When Should Indexes Be Considered?
- When Should Indexes Be Avoided?
- Summary
- Q&A
- Workshop
- Hour 17. Improving Database Performance
- What Is SQL Statement Tuning?
- Database Tuning Versus SQL Tuning
- Formatting Your SQL Statement
- Full Table Scans
- Other Performance Considerations
- Performance Tools
- Summary
- Q&A
- Workshop
- Part VI: Using SQL to Manage Users and Security
- Hour 18. Managing Database Users
- Users Are the Reason
- The Management Process
- Tools Utilized by Database Users
- Summary
- Q&A
- Workshop
- Hour 19. Managing Database Security
- What Is Database Security?
- How Does Security Differ from User Management?
- What Are Privileges?
- Controlling User Access
- Controlling Privileges Through Roles
- Summary
- Q&A
- Workshop
- Part VII: Summarized Data Structures
- Hour 20. Creating and Using Views and Synonyms
- What Is a View?
- Creating Views
- Dropping a View
- What Is a Synonym?
- Summary
- Q&A
- Workshop
- Hour 21. Working with the System Catalog
- What Is the System Catalog?
- How Is the System Catalog Created?
- What Is Contained in the System Catalog?
- Examples of System Catalog Tables by Implementation
- Querying the System Catalog
- Updating System Catalog Objects
- Summary
- Q&A
- Workshop
- Part VIII: Applying SQL Fundamentals in Today's World
- Hour 22. Advanced SQL Topics
- Advanced Topics
- Cursors
- Stored Procedures and Functions
- Triggers
- Dynamic SQL
- Call-Level Interface
- Using SQL to Generate SQL
- Direct Versus Embedded SQL
- Summary
- Q&A
- Workshop
- Hour 23. Extending SQL to the Enterprise, the Internet, and the Intranet
- SQL and the Enterprise
- Accessing a Remote Database
- Accessing a Remote Database Through a Web Interface
- SQL and the Internet
- SQL and the Intranet
- Summary
- Q&A
- Workshop
- Hour 24. Extensions to Standard SQL
- Various Implementations
- Examples of Extensions from Some Implementations
- Interactive SQL Statements
- Summary
- Q&A
- Workshop
- Part IX: Appendixes
- Appendix A. Common SQL Commands
- SQL Statements
- SQL Clauses
- Appendix B. Using MySQL for Exercises
- Windows Installation Instructions
- Linux Installation Instructions
- Appendix C. Answers to Quizzes and Exercises
- Hour 1, "Welcome to the World of SQL"
- Hour 2, "Defining Data Structures"
- Hour 3, "Managing Database Objects"
- Hour 4, "The Normalization Process"
- Hour 5, "Manipulating Data"
- Hour 6, "Managing Database Transactions"
- Hour 7, "Introduction to the Database Query"
- Hour 8, "Using Operators to Categorize Data"
- Hour 9, "Summarizing Data Results from a Query"
- Hour 10, "Sorting and Grouping Data"
- Hour 11, "Restructuring the Appearance of Data"
- Hour 12, "Understanding Dates and Time"
- Hour 13, "Joining Tables in Queries"
- Hour 14, "Using Subqueries to Define Unknown Data"
- Hour 15, "Combining Multiple Queries into One"
- Hour 16, "Using Indexes to Improve Performance"
- Hour 17, "Improving Database Performance"
- Hour 18, "Managing Database Users"
- Hour 19, "Managing Database Security"
- Hour 20, "Creating and Using Views and Synonyms"
- Hour 21, "Working with the System Catalog"
- Hour 22, "Advanced SQL Topics"
- Hour 23, "Extending SQL to the Enterprise, the Internet, and the Intranet"
- Hour 24, "Extensions to Standard SQL"
- Appendix D. <tt>CREATE TABLE</tt> Statements for Book Examples
- <tt>EMPLOYEE_TBL</tt>
- <tt>EMPLOYEE_PAY_TBL</tt>
- <tt>CUSTOMER_TBL</tt>
- <tt>ORDERS_TBL</tt>
- <tt>PRODUCTS_TBL</tt>
- Appendix E. <tt>INSERT</tt> Statements for Data in Book Examples
- <tt>INSERT</tt> Statements
- Appendix F. Glossary
- Appendix G. Bonus Exercises
Recommended Book
Recommended Book
Recommended Book
Recommended Book
INSERT Statements
EMPLOYEE_TBL
INSERT INTO EMPLOYEE_TBL VALUES
('311549902', 'STEPHENS', 'TINA', 'DAWN', 'RR 3 BOX 17A', 'GREENWOOD',
'IN', '47890', '3178784465', NULL);
INSERT INTO EMPLOYEE_TBL VALUES
('442346889', 'PLEW', 'LINDA', 'CAROL', '3301 BEACON', 'INDIANAPOLIS',
'IN', '46224', '3172978990', NULL);
INSERT INTO EMPLOYEE_TBL VALUES
('213764555', 'GLASS', 'BRANDON', 'SCOTT', '1710 MAIN ST', 'WHITELAND',
'IN', '47885', '3178984321', '3175709980');
INSERT INTO EMPLOYEE_TBL VALUES
('313782439', 'GLASS', 'JACOB', NULL, '3789 WHITE RIVER BLVD',
'INDIANAPOLIS', 'IN', '45734', '3175457676', '8887345678');
INSERT INTO EMPLOYEE_TBL VALUES
('220984332', 'WALLACE', 'MARIAH', NULL, '7889 KEYSTONE AVE',
'INDIANAPOLIS', 'IN', '46741', '3173325986', NULL);
INSERT INTO EMPLOYEE_TBL VALUES
('443679012', 'SPURGEON', 'TIFFANY', NULL, '5 GEORGE COURT',
'INDIANAPOLIS', 'IN', '46234', '3175679007', NULL);
EMPLOYEE_PAY_TBL
INSERT INTO EMPLOYEE_PAY_TBL VALUES
('311549902', 'MARKETING', '1989-05-23', NULL, '1999-05-01', '40000', NULL);
INSERT INTO EMPLOYEE_PAY_TBL VALUES
('442346889', 'TEAM LEADER', '1990-06-17', '14.75', '1999-06-01', NULL, NULL);
INSERT INTO EMPLOYEE_PAY_TBL VALUES
('213764555', 'SALES MANAGER', '1994-08-14', NULL, '1999-08-01', '30000', '2000');
INSERT INTO EMPLOYEE_PAY_TBL VALUES
('313782439', 'SALESMAN', '1997-06-28', NULL, NULL, '20000', '1000');
INSERT INTO EMPLOYEE_PAY_TBL VALUES
('220984332', 'SHIPPER', '1996-07-22', '11.00', '1999-07-01', NULL, NULL);
INSERT INTO EMPLOYEE_PAY_TBL VALUES
('443679012', 'SHIPPER', '1991-01-14', '15.00', '1999-01-01', NULL, NULL);
CUSTOMER_TBL
INSERT INTO CUSTOMER_TBL VALUES
('232', 'LESLIE GLEASON', '798 HARDAWAY DR', 'INDIANAPOLIS',
'IN', '47856', '3175457690', NULL);
INSERT INTO CUSTOMER_TBL VALUES
('109', 'NANCY BUNKER', 'APT A 4556 WATERWAY', 'BROAD RIPPLE',
'IN', '47950', '3174262323', NULL);
INSERT INTO CUSTOMER_TBL VALUES
('345', 'ANGELA DOBKO', 'RR3 BOX 76', 'LEBANON', 'IN', '49967',
'7658970090', NULL);
INSERT INTO CUSTOMER_TBL VALUES
('090', 'WENDY WOLF', '3345 GATEWAY DR', 'INDIANAPOLIS', 'IN',
'46224', '3172913421', NULL);
INSERT INTO CUSTOMER_TBL VALUES
('12', 'MARYS GIFT SHOP', '435 MAIN ST', 'DANVILLE', 'IL', '47978',
'3178567221', 3178523434');
INSERT INTO CUSTOMER_TBL VALUES
('432', 'SCOTTYS MARKET', 'RR2 BOX 173', 'BROWNSBURG', 'IN',
'45687', '3178529835', '3178529836');
INSERT INTO CUSTOMER_TBL VALUES
('333', 'JASONS AND DALLAS GOODIES', 'LAFAYETTE SQ MALL',
'INDIANAPOLIS', 'IN', '46222', '3172978886', '3172978887');
INSERT INTO CUSTOMER_TBL VALUES
('21', 'MORGANS CANDIES AND TREATS', '5657 W TENTH ST',
'INDIANAPOLIS', 'IN', '46234', 3172714398', NULL);
INSERT INTO CUSTOMER_TBL VALUES
('43', 'SCHYLERS NOVELTIES', '17 MAPLE ST', 'LEBANON', 'IN',
'48990', '3174346758', NULL);
INSERT INTO CUSTOMER_TBL VALUES
('287', 'GAVINS PLACE', '9880 ROCKVILLE RD', 'INDIANAPOLIS',
'IN', '46244', '3172719991', 3172719992');
INSERT INTO CUSTOMER_TBL VALUES
('288', 'HOLLYS GAMEARAMA', '567 US 31 SOUTH', 'WHITELAND',
'IN', '49980', '3178879023', NULL);
INSERT INTO CUSTOMER_TBL VALUES
('590', 'HEATHERS FEATHERS AND THINGS', '4090 N SHADELAND AVE',
'INDIANAPOLIS', 'IN', '43278', '3175456768', NULL);
INSERT INTO CUSTOMER_TBL VALUES
('610', 'REGANS HOBBIES INC', '451 GREEN ST', 'PLAINFIELD', 'IN',
'46818', '3178393441', 3178399090');
INSERT INTO CUSTOMER_TBL VALUES
('560', 'ANDYS CANDIES', 'RR 1 BOX 34', 'NASHVILLE', 'IN',
'48756', '8123239871', NULL);
INSERT INTO CUSTOMER_TBL VALUES
('221', 'RYANS STUFF', '2337 S SHELBY ST', 'INDIANAPOLIS', 'IN',
'47834', '3175634402', NULL);
ORDERS_TBL
INSERT INTO ORDERS_TBL VALUES
('56A901', '232', '11235', '1', '1999-10-22');
INSERT INTO ORDERS_TBL VALUES
('56A917', '12', '907', '100', '1999-09-30');
INSERT INTO ORDERS_TBL VALUES
('32A132', '43', '222', '25', '1999-10-10');
INSERT INTO ORDERS_TBL VALUES
('16C17', '090', '222', '2', '1999-10-17');
INSERT INTO ORDERS_TBL VALUES
('18D778', '287', '90', '10', '1999-10-17');
INSERT INTO ORDERS_TBL VALUES
('23E934', '432', '13', '20', '1999-10-15');
PRODUCTS_TBL
INSERT INTO PRODUCTS_TBL VALUES
('11235', 'WITCHES COSTUME', '29.99');
INSERT INTO PRODUCTS_TBL VALUES
('222', 'PLASTIC PUMPKIN 18 INCH', '7.75');
INSERT INTO PRODUCTS_TBL VALUES
('13', 'FALSE PARAFFIN TEETH', '1.10');
INSERT INTO PRODUCTS_TBL VALUES
('90', 'LIGHTED LANTERNS', '14.50');
INSERT INTO PRODUCTS_TBL VALUES
('15', 'ASSORTED COSTUMES', '10.00');
INSERT INTO PRODUCTS_TBL VALUES
('9', 'CANDY CORN', '1.35');
INSERT INTO PRODUCTS_TBL VALUES
('6', 'PUMPKIN CANDY', '1.45');
INSERT INTO PRODUCTS_TBL VALUES
('87', 'PLASTIC SPIDERS', '1.05');
INSERT INTO PRODUCTS_TBL VALUES
('119', 'ASSORTED MASKS', '4.95');