SQL was developed by Donald Chamberlain and Raymond Boyce in the early 1970s as a language to retrieve data from IBM's early relational database management systems. It was accepted as a standard by the American National Standards Institute (ANSI) in 1986. SQL is generally referred to as a fourth-generation language (4GL), in contrast with third-generation languages (3GLs) such as C, Java, and Python. As a 4GL, the syntax of SQL is designed to be even closer to human language than 3GLs, making it relatively natural to learn. Some do not refer to SQL as a programming language at all, but rather a data sub-language.
SQL - a language for relational databases
Before we look at what SQL (pronounced either 'S-Q-L' or 'sequel') is, it is important to define what it is not. First, SQL is not a product of Oracle or any other companies such as Oracle, what comprises the accepted standard for SQL. The current revision is SQL:2008.
Second, while the ANSI standard forms the basis for the various implementations of SQL used in different database management systems, this does not mean that the SQL syntax and functionality in all database products is the same; in fact, it is often quite different. For instance, the SQL language permits the concatenation of two column values into one; for example, the values hello and there concatenated would be hellothere. Oracle and Microsoft SQL Server both use symbols to denote concatenation, but they are different symbols. Oracle uses the double-pipe symbol, '||', and SQL Server uses a plus sign, '+'. MySQL, on the other hand, uses a keyword, CONCAT. Additionally, RDBMS software manufacturers often add functionalities to their own SQL implementations. In Oracle version 10g, a new type of syntax was included to join data from two or more tables that differs significantly from the ANSI standard. Oracle does, however, still support the ANSI standard as well.
Tip - SQL in the real world
Although the SQL implementations of the major RDBMS products differ, they all conform to the basic ANSI standard. That means if you learn how SQL is used in one database product, such as Oracle, much of your acquired knowledge should transfer easily to other database products.
Last, SQL should not be confused with any particular database product, such as Microsoft SQL Server or MySQL. Microsoft SQL Server is sometimes referred to by some as SQL; a confusing distinction.
What SQL does provide for developers and database administrators is a simple but rich set of commands that can be used to do the following:
- Retrieve data
- Insert, modify, and delete data
- Create, modify, and delete database objects
- Give or remove privileges to a user
- Control transactions
One of the interesting things about SQL is its dataset-oriented nature. When programmers use third-generation languages such as C++, working with the kinds of datasets we use in SQL is often a cumbersome task involving the explicit construction of variable arrays for memory management. One of the benefits of SQL is that it is already designed to work with arrays of data, so the memory management portion occurs implicitly. It is worth noting, however, that third-generation languages can do many things that SQL cannot. For instance, by itself, SQL cannot be used to create standalone programs such as video games and cell phone applications. However, SQL is an extremely effective tool when used for the purpose for which it was designed—namely, the retrieval and manipulation of relational data.
Tip - SQL in the real world
Standard programming constructs such as flow control, recursion, and conditional statements are absent from SQL. However, Oracle has created PL/SQL, a third-generation overlay language that adds these and other basic programmatic constructs to the SQL language. Because of the strength of the SQL language in manipulating data, PL/SQL is often the choice of developers when programming the portions of their applications that interact with Oracle databases.
The goal of my articles is to teach you the syntax and techniques to use the SQL language to make data do whatever you want it to do. However, before we can learn more about the SQL language, we are going to need to choose a tool that can interact with the database and process our SQL.
Commonly-used SQL tools
Because SQL is the primary interface into relational databases, there are many SQL manipulation tools from which to choose. There are benefits and drawbacks to each, but the choice of tool to use is generally about your comfort level with the tool and its feature set. Some tools are free, some are open source, and some require paid licenses; however, each tool uses the same syntax for SQL when it connects to an Oracle database. Following are some commonly-used SQL tools:
Tip - SQL in the real world
While your choice of SQL tool is an important one, in the industry it is one that is sometimes dictated by the toolset standards of your employer. It's important that you don't completely dedicate yourself to one tool. If you become an expert at one and then transfer to a different employer whose standards don't allow for the use of your tool, you may find yourself with an initial learning curve.
SQL*Plus
SQL*Plus is the de facto standard of SQL tools to connect to an Oracle database. Since Oracle's early versions, it has been included as a part of any Oracle RDBMS installation. SQL*Plus is a command-line tool and is launched on all Oracle platforms using the command, sqlplus. This command-line tool has been a staple of Oracle database administrators for many years. It has a powerful, interactive command interface that can be used to issue SQL statements, create database objects, launch scripts, and startup databases. However, compared with some of the newer tools, it has a significant learning curve. Its use of line numbering and mandatory semicolons for execution is often confusing to beginners. Oracle has also released a GUI version of SQL*Plus for use on Windows systems. Its rules for use, however, are still generally the same as the command-line interface, and its confinement to the Windows platform limits its use. As of Oracle version 11g, the GUI version of SQL*Plus is no longer included with a standard Oracle on Windows installation. Whatever your choice of SQL tool, it is very difficult for a database administrator to completely avoid using SQL*Plus. The following is a screenshot of the command-line SQL*Plus tool:
TOAD
The Tool for Oracle Application Developers (TOAD) is a full-featured development and administration tool for Oracle as well as other relational database systems, including Microsoft SQL Server, Sybase, and IBM's DB2. Originally created by Jim McDaniel for his own use, he later released it as freeware for the Oracle community at large. Eventually, Quest Software acquired the rights for TOAD and began distributing a licensed version, while greatly expanding on the original functionality. TOAD is immensely popular among both DBAs and developers due to its large feature set. For DBAs, it is a complete administration tool, allowing the user to control every major aspect of the database, including storage manipulation, object creation, and security control. For developers, TOAD offers a robust coding interface, including advanced debugging facilities.
TOAD is available for download in both freeware and trial licensed versions. A screenshot is shown as follows:
DBArtisan
DBArtisan (now called DBArtisan XE), by Embarcadero Technologies, is another complete suite of database management tools that operates across multiple platforms. DBArtisan is only available as a licensed product, but has extensive administration capabilities, including the ability to do advanced capacity and performance management, all packaged in an attractive and user-friendly GUI frontend. A trial version is available for download from Embarcadero's website.
SQL Worksheet (Enterprise Manager)
The SQL Worksheet is not a separate tool in itself; rather, it is a component of the larger Enterprise Manager product. Enterprise Manager is Oracle's flagship, web-based administration suite, comprised of two main components—Database Control and Grid Control. Database Control operates from a single server as a Java process and allows the DBA to manage every aspect of a single database, including storage, object manipulation, security, and performance. Grid Control operates with the same GUI interface, but requires the installation of the Enterprise Manager product on a centralized server. From this central instance of Grid Control, a DBA can manage all the databases to which Grid Control connects, providing the DBA with a web-based interface to the entire environment. SQL Worksheet, a link within Database/ Grid Control, provides a basic SQL interface to a database.. The license for both Grid Control and Database Control is included in the license for the Enterprise edition of Oracle, although many of the performance tuning and configuration management features must be separately purchased. A screenshot of SQL Worksheet is shown as follows:
PL/SQL Developer
PL/SQL Developer is a full-featured SQL development tool from Allround Automations. Along with many of the other features common to SQL development tools, such as saved connections, data exporting, and table comparisons, PL/SQL Developer places a strong focus on the coding environment. It offers an extensive code editor with an integrated debugger, syntax highlighting, and a code hierarchy that is especially beneficial when working with the PL/SQL language. It also includes a code beautifier that formats your code using user-defined rules. PL/SQL Developer can be purchased from Allround Automations or downloaded from their website as a fully-functional, 30-day trial version.
Oracle SQL Developer
Oracle SQL Developer, originally called Raptor, is a GUI database interface that takes a somewhat different approach from its competitors. While many of the major licensable GUI administration products have continued to expand their product offering through more and more add-on components, SQL Developer is a much more dedicated tool. It's a streamlined SQL interface to the Oracle database. You can create and manipulate database objects in the GUI interface as well as write and execute SQL statements from a command line. Administration-oriented activities such as storage control are left to Enterprise Manager. SQL Developer aims to be a strong SQL and PL/SQL editor with some GUI functionalities. SQL Developer has gained in popularity in recent years, in large part to several benefits that are listed as follows:
- It is completely free with no mandatory licensable components, although third-party add-os are available for purchase.
- It is a true cross-platform client-side tool written primarily in Java. While a majority of the commonly-used SQL tools are available only on the Windows platform, SQL Developer runs on Windows, Linux, and even the Mac.
- In many Oracle shops, DBAs have been uncomfortable with the idea of giving developers a tool that can be used to cause massive damage to the database. Because Oracle has separated out most of the administration functions from SQL Developer, it is more of a true development tool.
- SQL Developer supports read-only connections to many popular databases, including SQL Server, Sybase, MySQL, Microsoft Access, DB2, and Teradata.
Because it is written in Java, it allows for the creation and addition of third-party extensions. If you want a capability that SQL Developer does not have, you can write your own! - It is provided by Oracle and is now included with any installation of Oracle database. It has essentially replaced SQL*Plus as Oracle's default SQL interface, although SQL*Plus is still available from the command line.
For these reasons, the tool we use in my articles for the purposes of demonstration will be SQL Developer. Instructions for downloading the tool are in the foreword. But, before we look at SQL Developer, let's find out a little about the data we'll be using and look at the Companylink database.
