- 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
Various Common Character Functions
Character functions are used mainly to compare, join, search, and extract a segment of a string or a value in a column. There are several character functions available to the SQL programmer.
The following sections illustrate the application of ANSI concepts in some of the leading implementations of SQL, such as in Oracle, Sybase, SQLBase, Informix, and SQL Server.
Concatenation
Concatenation, along with most other functions, is represented slightly differently among various implementations. The following examples show the use of concatenation in Oracle and SQL Server.
|
In Oracle |
|
SELECT 'JOHN' || 'SON' returns JOHNSON |
|
In SQL Server |
|
SELECT 'JOHN' + 'SON' returns JOHNSON |
|
In MySQL |
|
SELECT CONCAT('JOHN' , 'SON') returns JOHNSON |
The syntax for Oracle is
COLUMN_NAME || [ '' || ] COLUMN_NAME [ COLUMN_NAME ]
The syntax for SQL Server is
COLUMN_NAME + [ '' + ] COLUMN_NAME [ COLUMN_NAME ]
The syntax for MySQL is
CONCAT(COLUMN_NAME , [ '' , ] COLUMN_NAME [ COLUMN_NAME ]
|
Example |
Meaning |
|
SELECT CITY + STATE FROM EMPLOYEE_TBL; |
This SQL Server statement concatenates the values for city and state into one value. |
|
SELECT CITY ||', '|| STATE FROM EMPLOYEE_TBL; |
This Oracle statement concatenates the values for city and state into one value, placing a comma between the values for city and state. |
|
SELECT CITY + '' + STATE FROM EMPLOYEE_TBL; |
This SQL Server statement concatenates the values for city and state into one value, placing a space between the two original values. |
Example:
SELECT LAST_NAME || ', ' || FIRST_NAME NAME FROM EMPLOYEE_TBL;
NAME ----------------- STEPHENS, TINA PLEW, LINDA GLASS, BRANDON GLASS, JACOB WALLACE, MARIAH SPURGEON, TIFFANY 6 rows selected.
TRANSLATE
The TRANSLATE function searches a string of characters and checks for a specific character, makes note of the position found, searches the replacement string at the same position, and then replaces that character with the new value. The syntax is
|
Example |
Meaning |
|
SELECT TRANSLATE (CITY,'IND','ABC' FROM EMPLOYEE_TBL);B, and D with C. |
This SQL statement substitutes every occurrence of I in the string with A, replaces all occurrences with C. |
The following example illustrates the use of TRANSLATE with real data:
Notice in this example that all occurrences of I were replaced with A, N with B, and D with C. In the city INDIANAPOLIS, IND was replaced with ABC, but in GREENWOOD, D was replaced with C. Also notice how the value WHITELAND was translated.
REPLACE
The REPLACE function is used to replace every occurrence of a character(s) with a specified character(s). The use of this function is similar to the TRANSLATE function; only one specific character or string is replaced within another string. The syntax is
|
Example |
Meaning |
|
SELECT REPLACE(FIRST__'T', 'B') FROM EMPLOYEE_TBL |
This statement returns all NAME, the first names and changes any occurrence of T to a B. |
UPPER
Most implementations have a way to control the case of data by using functions. The UPPER function is used to convert lowercase letters to uppercase letters for a specific string.
The syntax is as follows:
|
Example |
Meaning |
|
SELECT UPPER(LAST_NAME)FROM EMPLOYEE_TBL;LAST_NAME |
This SQL statement converts all characters in the column to uppercase. |
LOWER
The converse of the UPPER function, the LOWER function is used to convert uppercase letters to lowercase letters for a specific string.
The syntax is as follows:
|
Example |
Meaning |
|
SELECT LOWER(LAST_NAME)FROM EMPLOYEE_TBL;LAST_NAME |
This SQL statement converts all characters in the column to lowercase. |
SUBSTR
Taking an expression's substring is common in most implementations of SQL, but the function name may differ, as shown in the following Oracle and SQL Server examples.
The syntax for Oracle is
The syntax for SQL Server is
SUBSTRING(COLUMN NAME, STARTING POSITION, LENGTH)
The only difference between the two implementations is the spelling of the function name.
|
Example |
Meaning |
|
SELECT SUBSTRING(EMP_ID,1,3) FROM EMPLOYEE_TBL |
This SQL statement returns the first three characters of EMP_ID. |
|
SELECT SUBSTRING(EMP_ID,4,2) FROM EMPLOYEE_TBL |
This SQL statement returns the fourth and fifth characters of EMP_ID. |
|
SELECT SUBSTRING(EMP_ID,6,4) FROM EMPLOYEE_TBL |
This SQL statement returns the sixth through the ninth characters of EMP_ID. |
The following is an example that is compatible with Microsoft SQL Server and MySQL:
INSTR
The INSTR function is a variation of the POSITION function; it is used to search a string of characters for a specific set of characters and report the position of those characters. The syntax is as follows:
|
Example |
Meaning |
|
SELECT INSTR(STATE,'I',1,1) FROM EMPLOYEE_TBL; |
This SQL statement returns the position of the first occurrence of the letter I for each state in EMPLOYEE_TBL. |
Notice that if the searched character A was not found in a string, the value 0 was returned for the position.
LTRIM
The LTRIM function is another way of clipping part of a string. This function and SUBSTRING are in the same family. LTRIM is used to trim characters from the left of a string. The syntax is
|
Example |
Meaning |
|
SELECT LTRIM(FIRST_NAME,'LES') FROM CUSTOMER_TBL WHERE FIRST_NAME ='LESLIE'; |
This SQL statement trims the characters LES from the left of all names that are LESLIE. |
The S in SHIPPER was trimmed off, even though SHIPPER does not contain the string SALES. The first four characters of SALES were ignored. The searched characters must appear in the same order of the search string and must be on the far left of the string. In other words, LTRIM will trim off all characters to the left of the last occurrence in the search string.
RTRIM
Like LTRIM, the RTRIM function is used to trim characters from the right of a string. The syntax is
|
Example |
Meaning |
|
SELECT RTRIM(FIRST_NAME, 'ON') FROM EMPLOYEE_TBL WHERE FIRST_NAME = 'BRANDON'; |
This SQL statement returns the first name BRANDON and trims the ON, leaving BRAND as a result. |
The string ER was trimmed from the right of all applicable strings.
DECODE
The DECODE function is not ANSI—at least not at the time of this writing—but its use is shown here because of its great power. This function is used in SQLBase, Oracle, and possibly other implementations. DECODE is used to search a string for a value or string, and if the string is found, an alternative string is displayed as part of the query results.
The syntax is
|
Example |
Meaning |
|
SELECT DECODE(LAST_NAME,'SMITH', 'JONES','OTHER') FROM EMPLOYEE_TBL; |
This query searches the value of all last names in EMPLOYEE_TBL; if the value SMITH is found, JONES is displayed in its place.Any other names are displayed as OTHER, which is called the default value. |
In the following example, DECODE is used on the values for CITY in EMPLOYEE_TBL:
The output shows the value INDIANAPOLIS displayed as INDY, GREENWOOD displayed as GREEN, and all other cities displayed as OTHER.