Sams Teach Yourself SQL in 24 Hours

By Ron Plew and Ryan Stephens

• 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
  • Part II: Building Your Database
  • Hour 2. Defining Data Structures
  • Hour 3. Managing Database Objects
  • Hour 4. The Normalization Process
  • Hour 5. Manipulating Data
  • Hour 6. Managing Database Transactions
  • Part III: Getting Effective Results from Queries
  • 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 Times
  • Part IV: Building Sophisticated Database Queries
  • Hour 13. Joining Tables in Queries
  • Hour 14. Using Subqueries to Define Unknown Data
  • Hour 15. Combining Multiple Queries into One
  • Part V: SQL Performance Tuning
  • Hour 16. Using Indexes to Improve Performance
  • Hour 17. Improving Database Performance
  • Part VI: Using SQL to Manage Users and Security
  • Hour 18. Managing Database Users
  • Hour 19. Managing Database Security
  • Part VII: Summarized Data Structures
  • Hour 20. Creating and Using Views and Synonyms
  • 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
  • Hour 23. Extending SQL to the Enterprise, the Internet, and the Intranet
  • Hour 24. Extensions to Standard SQL
  • Part IX: Appendixes
  • Appendix A. Common SQL Commands
  • Appendix B. Using MySQL for Exercises
  • Appendix C. Answers to Quizzes and Exercises
  • Appendix D. CREATE TABLE Statements for Book Examples
  • Appendix E. INSERT Statements for Data in Book Examples
  • Appendix F. Glossary
  • Appendix G. Bonus Exercises

Querying the System Catalog

The system catalog tables or views are queried as any other table or view in the database using SQL. A user can usually query the user-related tables, but may be denied access to various system tables that can be accessed only by privileged database user accounts, such as the database administrator.

You create a SQL query to retrieve data from the system catalog just as you create a query to access any other table in the database.

For example, the following query returns all rows of data from the Sybase table SYSTABLES:

SELECT * FROM SYSTABLES 
GO

The following section displays a few examples of querying system catalog tables and some of the information that you may stumble across.

Examples of System Catalog Queries

The following examples use Oracle's system catalog. Oracle is chosen for no particular reason other than that it is the implementation with which this book's authors are most familiar.

The following query lists all user accounts in the database:

   
   

   SELECT USERNAME

   FROM ALL_USERS;

   
USERNAME
----------------
SYS
SYSTEM
RYAN
SCOTT
DEMO
RON
USER1
USER2
8 rows selected.

The following query lists all tables owned by a user:

   
   

   SELECT TABLE_NAME

   FROM USER_TABLES;

   
TABLE_NAME
----------------
CANDY_TBL
CUSTOMER_TBL
EMPLOYEE_PAY_TBL
EMPLOYEE_TBL
PRODUCTS_TBL
ORDERS_TBL
6 rows selected.

The next query returns all the system privileges that have been granted to the database user BRANDON:

   
   

   SELECT GRANTEE, PRIVILEGE

   FROM SYS.DBA_SYS_PRIVS

   WHERE GRANTEE = 'BRANDON';

   
GRANTEE                       PRIVILEGE
---------------------- --------------------
BRANDON                ALTER ANY TABLE
BRANDON                ALTER USER
BRANDON                CREATE USER
BRANDON                DROP ANY TABLE
BRANDON                SELECT ANY TABLE
BRANDON                UNLIMITED TABLESPACE
6 rows selected.

The following is an example from MS Access:

   
   

   SELECT NAME

   FROM MSYSOBJECTS

   WHERE NAME = 'MSYSOBJECTS'

   
NAME
-----------
MSYSOBJECTS

Share This