What You’ll Learn in This Hour:
▶ An introduction to and brief history of SQL
▶ An overview of the SQL standard
▶ An introduction to some vendor implementations of SQL
▶ The basic components, or sublanguages, that comprise the SQL language
SQL has many components that are simple yet offer flexibility and power for any user communicating with a relational database. This hour provides a broad overview of SQL, to cover some preliminary concepts that you need to know before you jump into hands-on exercises and coding. Be patient with this hour; the “boring” background material helps you build a solid starting foundation.
Every modern business uses and stores data. Thus, every business requires some organized method or mechanism for maintaining and retrieving that data. When data is kept within a database, this mechanism is referred to as a database management system (DBMS). Database management systems have been around for years; many of them started out as flat-file systems on a mainframe.
These days, information management is primarily achieved through a relational database management system (RDBMS), derived from the traditional DBMS. Modern businesses typically use databases combined with client/server and web technologies to successfully manage their data and stay competitive in their appropriate markets. The trend for many businesses is to move from a client/server environment to the web, which sidesteps location as a restriction when users need access to important data.
The next few sections discuss SQL and the relational database, the most common DBMS implemented today. Having a good fundamental understanding of the relational database and how to use SQL to manage data in today’s information technology world is important to your overall understanding of the SQL language.
Structured Query Language (SQL) is the standard language used to communicate with a relational database. The prototype was originally developed by IBM using Dr. E. F. Codd’s paper, A Relational Model of Data for Large Shared Data Banks, as a model. In 1979, not long after IBM’s prototype was created, the company Relational Software released the first SQL product, named ORACLE (Relational Software was later renamed Oracle Corporation). Today Oracle remains as one of the distinguished leaders in relational database technologies.
If you travel to a foreign country, you might need to know that country’s language to get around. For example, you might have trouble ordering from a menu in your native tongue if the waiter speaks only his country’s language. Similarly, you can consider a database as a foreign land in which you seek information. SQL is the language you use to express your needs to the database. Just as you would order a meal from a menu in another country, you can request specific information from within a database in the form of a query using SQL.
Note
Communicating with Databases
Think about when you access your favorite online store to order a book, an article of clothing, or just about any other product. When you point and click to navigate the product catalog, enter search criteria, and place items in your shopping cart, SQL code is often executed behind the scenes to facilitate a database connection, while telling the database what data you want to see and how you want to see it.
As previously stated, SQL is the standard language used to communicate with a relational database. SQL is a nonprocedural language. It largely defines the structures of a relational database and queries data from the relational database. However, some extensions to SQL have procedural components that are found in other programming languages, such as C++ and .NET.
This section describes the standard for SQL, examines the components of the standard, and briefly discusses why the standard is important to the many different vendor implementations of the relational database. This section also looks at the various vendor implementations of SQL.
The American National Standards Institute (ANSI) is an organization that approves certain standards in many different industries. SQL has been deemed the standard language for relational database communication. SQL-86 was originally approved in 1986, based on IBM’s implementation. In 1987, the International Standards Organization (ISO) accepted the ANSI SQL standard as the international standard. The standard was further revised in 1989 (SQL-89), 1992 (SQL-92), 1999 (SQL:1999), 2003 (SQL:2003), 2006 (SQL:2006), 2008 (SQL:2008), 2011 (SQL:2011), and, most recently, 2016 (SQL:2016). Along the way, revisions have been made to the standard as information systems, database software, and data usage have evolved.
SQL-2016, the current SQL standard, has nine interrelated documents. Other documents might be added in the near future as the standard is expanded to encompass newly emerging technology needs. The nine interrelated parts follow:
▶ Part 1, “SQL/Framework,” specifies the general requirements for conformance and defines the fundamental concepts of SQL.
▶ Part 2, “SQL/Foundation,” defines the syntax and operations of SQL.
▶ Part 3, “SQL/Call-Level Interface,” defines the interface for application programming to SQL.
▶ Part 4, “SQL/Persistent Stored Modules,” defines the control structures that then define SQL routines. Part 4 also defines the modules that contain SQL routines.
▶ Part 9, “Management of External Data (SQL/MED),” defines extensions to SQL to support the management of external data through data wrappers and data link types.
▶ Part 10, “Object Language Bindings,” defines extensions to the SQL language to support the embedding of SQL statements into programs written in Java.
▶ Part 11, “Information and Definition Schemas,” defines specifications for the Information Schema and Definition Schema, which provide structural and security information related to SQL data.
▶ Part 13, “Routines and Types Using the Java Programming Language,” defines the capability to call Java static routines and classes as SQL-invoked routines.
▶ Part 14, “XML-Related Specifications,” defines ways SQL can be used with XML.
Note
The SQL Standard and This Book
These parts to the standard will not mean much to you until you learn a lot more about SQL. This book does not delve into every specific of the standard; it teaches you the SQL language based on the standard.
Vendors of relational (SQL) database software adhere to the standard with various levels of compliance. If you work with a database implementation that does not fully comply with any given standard, workarounds normally are available, involving business logic that is incorporated into the database design. With any standard comes numerous obvious advantages as well as some disadvantages. Primarily, a standard steers vendors in the appropriate industry direction for development. For SQL, a standard provides the basic skeleton of necessary fundamentals, enabling consistency among various implementations and increasing portability (not only for database programs, but also for databases in general and individuals who manage databases).
Some people argue that a standard limits the flexibility and possible capabilities of a particular implementation. However, most vendors that comply with the standard add product-specific enhancements to standard SQL to fill in these gaps.
Considering the advantages and disadvantages, having a standard is desirable. Overall, a standard demands features that should be available in any complete SQL implementation and outlines basic concepts that not only force consistency among all competitive SQL implementations, but also increase the value of SQL programmers.
A SQL implementation is a particular vendor’s SQL product, or RDBMS. It is important to note, as this book states numerous times, that implementations of SQL vary widely. No single implementation follows the standard completely, although some implementations are mostly ANSI compliant. Also noteworthy is the fact that, in recent years, the ANSI standard has not dramatically changed the list of functionality that vendors must adhere to in order to be compliant. Thus, when new versions of RDBMS are released, they will most likely claim ANSI SQL compliance.
RDBMS market share leaders, as of publication, include the following:
▶ Oracle
▶ MySQL (owned by Oracle)
▶ Microsoft SQL
▶ PostgreSQL
▶ IBM Db2
▶ IBM Informix
▶ MariaDB
▶ SQLite
▶ Amazon RDS
Note
Free Relational Database Software
Many RDBMS vendors offer free use of their database software or trial downloads. Free versions of relational database software typically are allowed for development, personal, and educational purposes. Software vendors do this to gain exposure to their products and remain competitive in the RDBMS market. In addition, you can often find free relational databases that reside in the cloud.
The examples in this book primarily use the Oracle database. The code for creating the tables and data works seamlessly with Oracle. Nonetheless, all code is based on the SQL standard to the fullest extent possible. You can easily apply everything you learn in this book to any vendor implementation of SQL that you are using, with little or no changes to the code. The reason this book is based on the standard instead of any specific implementation of SQL is that SQL and relational databases are so widespread. Many organizations use multiple implementations of the relational database. Having a solid knowledge of standard SQL enables you to integrate various databases and data fairly easily, with few modifications.
Choosing the best implementation for you or your organization involves many factors, including the type of data, the size of the data, legacy information systems that are in place, and the cost of vendor implementations. You will find both for-profit leaders and open source products in the relational database world.
A SQL session occurs when a user interacts with a relational database through SQL commands. When a user initially connects to the database, a session is established. Within the scope of a SQL session, valid SQL commands can be entered to query the database, manipulate data in the database, and define database structures such as tables. A session is invoked either by direct connection to the database or through a front-end application. In both cases, a user normally establishes a session at a terminal or workstation that communicates through a network with the computer hosting the database.
CONNECTWhen a user connects to a database, the SQL session is initialized. The CONNECT command establishes a database connection. With the CONNECT command, you either invoke a connection or change connections to the database. For example, if you connect as USER1, you can use the CONNECT command to connect to the database as USER2. When this happens, the SQL session for USER1 is implicitly disconnected. You then normally use the following:
CONNECT user@database
When you attempt to connect to a database, you are automatically prompted for a password that is associated with your current username. The username authenticates you to the database; the password is the key that allows entrance.
DISCONNECT and EXITWhen a user disconnects from a database, the SQL session is terminated. The DISCONNECT command disconnects a user from the database. When you disconnect from the database, the software you use might still appear to communicate with the database, but you have lost your connection. When you use EXIT to leave the database, your SQL session terminates and the software that you use to access the database closes.
DISCONNECT
The following sections discuss the basic categories of commands, or sublanguages, used in SQL to perform various functions. These functions include building database objects, manipulating objects, populating database tables with data, updating existing data in tables, deleting data, performing database queries, controlling database access, and handling overall database administration.
The main categories of commands are listed here:
▶ Data Definition Language (DDL)
▶ Data Manipulation Language (DML)
▶ Data Query Language (DQL)
▶ Data Control Language (DCL)
▶ Data administration commands
▶ Transactional control commands
The Data Definition Language (DDL) is the part of SQL that enables a database user to create and restructure database objects, such as when creating or deleting a table.
Some of the most fundamental DDL commands discussed during the following hours include the following:
▶ CREATE TABLE
▶ ALTER TABLE
▶ DROP TABLE
▶ CREATE INDEX
▶ ALTER INDEX
▶ DROP INDEX
▶ CREATE VIEW
▶ DROP VIEW
These commands are discussed in detail during Hour 9, “Creating and Managing Database Objects”; Hour 20, “Creating and Using Views and Synonyms”; and Hour 22, “Using Indexes to Improve Performance.”
The Data Manipulation Language (DML) is the part of SQL used to manipulate data within objects of a relational database.
The three basic DML commands are listed here:
▶ INSERT
▶ UPDATE
▶ DELETE
These commands are discussed in detail during Hour 10, “Manipulating Data.”
Although it consists of only one command, the Data Query Language (DQL) is the most concentrated focus of SQL for modern relational database users. The base command is SELECT.
This command, accompanied by many options and clauses, composes queries against a relational database. A query is an inquiry to the database for information. A query is usually issued to the database through an application interface or a command-line prompt. You can easily create queries, whether simple or complex, vague or specific.
The SELECT command is discussed in detail during Hour 12, “Introduction to Database Queries,” through Hour 20, “Creating and Using Views and Synonyms.”
Data control commands in SQL enable you to control access to data within the database. These Data Control Language (DCL) commands are normally used to create objects related to user access and also control the distribution of privileges among users. Some data control commands follow:
▶ ALTER PASSWORD
▶ GRANT
▶ REVOKE
▶ CREATE SYNONYM
These commands often are grouped with other commands and appear in several lessons throughout this book.
Data administration commands enable the user to perform audits and analyses on operations within the database. They can also help analyze system performance. Two general data administration commands follow:
▶ START AUDIT
▶ STOP AUDIT
Do not confuse data administration with database administration: Database administration is the overall administration of a database, which envelops the use of all levels of commands. Data administration is much more specific to each SQL implementation than the core commands of the SQL language.
In addition to the previously introduced categories of commands, these commands enable the user to manage database transactions:
▶ COMMIT—Saves database transactions
▶ ROLLBACK—Undoes database transactions
▶ SAVEPOINT—Creates points within groups of transactions for a ROLLBACK
▶ SET TRANSACTION—Places a name on a transaction
Transactional commands are discussed extensively during Hour 11, “Managing Database Transactions.”
In this hour, you learned about the standard language of SQL and got a brief history of how the standard has evolved over the past several years. You took a look at database systems and current technologies, including the relational database, client/server systems, and web-based database systems, all of which are vital to your understanding of SQL. You also got an overview of the main SQL language components and learned that the relational database market has many players that produce various flavors of SQL. Despite ANSI SQL variations, most vendors comply to some extent with the current standard (SQL-2016), ensuring general consistency and forcing the development of portable SQL applications.
During this hour, you should have acquired overall background knowledge of the fundamentals of SQL and should understand the concept of a modern database. In Hour 3, “Getting to Know Your Data,” you get an introduction to the database you use throughout this book. Soon you will be performing exercises to design, build, manage, and query a relational database using SQL.
Q. Is SQL in one implementation of SQL portable to another implementation?
A. SQL code is typically portable from one implementation to another with minor changes to syntax. Also keep in mind that some implementations follow the SQL standard more closely than others, and implementations may offer features that might not exist in other implementations.
Q. If I learn SQL, can I use any implementation that uses SQL?
A. Yes, you can communicate with a database whose implementation is ANSI SQL compliant. If an implementation is not completely compliant, you most likely will pick it up quickly, with some adjustments.
The following workshop consists of a series of quiz questions and practical exercises. The quiz questions are designed to test your overall understanding of the current material. The practical exercises give you the opportunity to apply the concepts discussed during the current hour, as well as build on the knowledge you acquired in previous hours of study. Be sure to complete the quiz questions and exercises before continuing to the next hour. Refer to Appendix C, “Answers to Quizzes and Exercises,” for answers.
1. Identify the categories for the following SQL commands:
CREATE TABLE DELETE SELECT INSERT ALTER TABLE UPDATE
2. List the basic SQL statements to manipulate data.
3. List the SQL statement that is used to query a relational database.
4. Which transactional control command is used to save a transaction?
5. Which transactional control command is used to undo a transaction?