Introduction to SQL language: Role, Features and Benefits

The SQL language and relational database systems based on it are one of the most important foundation technologies in the computer industry. Over the last two decades, SQL has grown from its first commercial use into a computer product and services market segment worth tens of billions of dollars per year, and SQL stands today as the standard computer database language. Literally hundreds of database products now support SQL, running on computer systems from mainframes to personal computers and even handheld devices. An official international SQL standard has been adopted and expanded twice. Virtually every major enterprise software product relies on SQL for its data management, and SQL is at the core of the database products from Microsoft, Oracle, and IBM, the three largest software companies in the world. SQL is also at the heart of open-source database products that are helping to fuel the popularity of Linux and the open-source movement. From its obscure beginnings as an IBM research project, SQL has leaped to prominence as both an important computer technology and a powerful market force.

What, exactly, is SQL? Why is it important? What can it do, and how does it work? If SQL is really a standard, why are there so many different versions and dialects? How do popular SQL products like SQL Server, Oracle, Informix, Sybase, and DB2 compare? How does SQL relate to Microsoft standards, such as ODBC and COM? How does JDBC link SQL to the world of Java and object technology? What role does it play in the emerging “web services” architecture, and the competing web services architectures from the Microsoft and Java-based camps? Does SQL really scale from mainframes to handheld devices? Has it really delivered the performance needed for high-volume transaction processing? How will SQL impact the way you use computers, and how can you get the most out of this important data management tool?

1. The SQL Language

SQL is a tool for organizing, managing, and retrieving data stored by a computer database. The acronym SQL is an abbreviation for Structured Query Language. For historical reasons, SQL is usually pronounced “sequel,” but the alternate pronunciation “S.Q.L.” is also used. As the name implies, SQL is a computer language that you use to interact with a database. In fact, SQL works with one specific type of database, called a relational database.

Figure 1-1 shows how SQL works. The computer system in the figure has a database that stores important information. If the computer system is in a business, the database might store inventory, production, sales, or payroll data. On a personal computer, the database might store data about the checks you have written, lists of people and their phone numbers, or data extracted from a larger computer system. The computer program that controls the database is called a database management system, or DBMS.

When you need to retrieve data from a database, you use the SQL language to make the request. The DBMS processes the SQL request, retrieves the requested data, and returns it to you. This process of requesting data from a database and receiving back the results is called a database query—hence the name Structured Query Language.

The name Structured Query Language is actually somewhat of a misnomer. First of all, SQL is far more than a query tool, although that was its original purpose, and retrieving data is still one of its most important functions. SQL is used to control all of the functions that a DBMS provides for its users, including:

  • Data definition. SQL lets a user define the structure and organization of the stored data and relationships among the stored data items.
  • Data retrieval. SQL allows a user or an application program to retrieve stored data from the database and use it.
  • Data manipulation. SQL allows a user or an application program to update the database by adding new data, removing old data, and modifying previously stored data.
  • Access control. SQL can be used to restrict a user’s ability to retrieve, add, and modify data, protecting stored data against unauthorized access.
  • Data sharing. SQL is used to coordinate data sharing by concurrent users, ensuring that they do not interfere with one another.
  • Data integrity. SQL defines integrity constraints in the database, protecting it from corruption due to inconsistent updates or system failures.

SQL is thus a comprehensive language for controlling and interacting with a database management system.

Second, SQL is not really a complete computer language like COBOL, C, C++, or Java. SQL contains no IF statement for testing conditions, and no GOTO, DO, or FOR statements for program flow control. Instead, SQL is a database sublanguage, consisting of about 40 statements specialized for database management tasks. These SQL statements can be embedded into another language, such as COBOL or C, to extend that language for use in database access. Alternatively, they can be explicitly sent to a database management system for processing, via a call-level interface from a language such as C, C++, or Java, or via messages sent over a computer network.

Finally, SQL is not a particularly structured language, especially when compared to highly structured languages such as C, Pascal, or Java. Instead, SQL statements resemble English sentences, complete with “noise words” that don’t add to the meaning of the statement but make it read more naturally. There are quite a few inconsistencies in the SQL language, and there are also some special rules to prevent you from constructing SQL statements that look perfectly legal, but don’t make sense.

Despite the inaccuracy of its name, SQL has emerged as the standard language for using relational databases. SQL is both a powerful language and one that is relatively easy to learn. The quick tour of SQL in Chapter 2 will give you a good overview of the language and its capabilities.

2. The Role of SQL

SQL is not itself a database management system, nor is it a stand-alone product. You cannot go into a computer store and “buy SQL.” Instead, SQL is an integral part of a database management system, a language and a tool for communicating with the DBMS. Figure 1-2 shows some of the components of a typical DBMS, and how SQL acts as the glue that links them together.

The database engine is the heart of the DBMS, responsible for actually structuring, storing, and retrieving the data in the database. It accepts SQL requests from other DBMS components—such as a forms facility, report writer, or interactive query facility—from user-written application programs, and even from other computer systems. As the figure shows, SQL plays many different roles:

  • SQL is an interactive query language. Users type SQL commands into an interactive SQL program to retrieve data and display it on the screen, providing a convenient, easy-to-use tool for ad hoc database queries.
  • SQL is a database programming language. Programmers embed SQL commands into their application programs to access the data in a database. Both user- written programs and database utility programs (such as report writers and data entry tools) use this technique for database access.
  • SQL is a database administration language. The database administrator responsible for managing a minicomputer or mainframe database uses SQL to define the database structure and control access to the stored data.
  • SQL is a client/server language. Personal computer programs use SQL to communicate over a network with database servers that store shared data. This client/server architecture has become very popular for enterprise-class applications.
  • SQL is an Internet data access language. Internet web servers that interact with corporate data and Internet applications servers all use SQL as a standard language for accessing corporate databases.
  • SQL is a distributed database language. Distributed database management systems use SQL to help distribute data across many connected computer systems. The DBMS software on each system uses SQL to communicate with the other systems, sending requests for data access.
  • SQL is a database gateway language. In a computer network with a mix of different DBMS products, SQL is often used in a gateway that allows one brand of DBMS to communicate with another brand.

SQL has thus emerged as a useful, powerful tool for linking people, computer programs, and computer systems to the data stored in a relational database.

3. SQL Features and Benefits

SQL is both an easy-to-understand language and a comprehensive tool for managing data. Here are some of the major features of SQL and the market forces that have made it successful:

  • Vendor independence
  • Portability across computer systems
  • SQL standards
  • IBM endorsement and commitment (DB2)
  • Microsoft commitment (SQL Server, ODBC, and ADO)
  • Relational foundation
  • High -level, English-like structure
  • Interactive, ad hoc queries
  • Programmatic database access
  • Multiple views of data
  • Complete database language
  • Dynamic data definition
  • Client/server architecture
  • Enterprise application support
  • Extensibility and object technology
  • Internet database access
  • Java integration (JDBC)
  • Industry infrastructure

These are the reasons why SQL has emerged as the standard tool for managing data on personal computers, minicomputers, and mainframes. They are described in the sections that follow.

3.1. Vendor Independence

SQL is offered by all of the leading DBMS vendors, and no new database product over the last decade has been highly successful without SQL support. A SQL-based database and the programs that use it can be moved from one DBMS to another vendor’s DBMS with minimal conversion effort and little retraining of personnel. Database tools, such as query tools, report writers, and application generators, work with many different brands of SQL databases. The vendor independence thus provided by SQL was one of the most important reasons for its early popularity and remains an important feature today.

3.2. Portability Across Computer Systems

SQL-based database products run on computer systems ranging from mainframes and midrange systems to personal computers, workstations, a wide range of specialized server computers, and even handheld devices. They operate on stand-alone computer systems, in departmental local area networks, and in enterprisewide or Internetwide networks. SQL-based applications that begin on single-user or departmental server systems can be moved to larger server systems as they grow. Data from corporate SQL-based databases can be extracted and downloaded into departmental or personal databases. Finally, economical personal computers can be used to prototype a SQL-based database application before moving it to an expensive multiuser system.

3.3. SQL Standards

An official standard for SQL was initially published by the American National Standards Institute (ANSI) and the International Standards Organization (ISO) in 1986, and was expanded in 1989 and again in 1992 and 1999. SQL is also a U.S. Federal Information Processing Standard (FIPS), making it a key requirement for large government computer contracts. Over the years, other international, government, and vendor groups have pioneered the standardization of new SQL capabilities, such as call-level interfaces or object-based extensions. Many of these new initiatives have been incorporated into the ANSI/ISO standard over time. The evolving standards serve as an official stamp of approval for SQL and have speeded its market acceptance.

3.4. IBM Endorsement and Commitment (DB2)

SQL was originally invented by IBM researchers and has since become a strategic product for IBM based on its flagship DB2 database. SQL support is available on all major IBM product families, from personal computers through midrange systems (AS/400 and UNIX-based servers) to IBM mainframes. IBM’s initial work provided a clear signal of IBM’s direction for other database and system vendors to follow early in the development of SQL and relational databases. Later, IBM’s commitment and broad support speeded the market acceptance of SQL. IBM’s SQL reach today extends well beyond its own computer systems business. SQL-based products that IBM has developed or acquired now run across a broad range of hardware, in many cases from competing computer vendors such as Sun or Hewlett-Packard.

3.5. Microsoft Commitment (SQL Server, ODBC, and ADO)

Microsoft has long considered database access a key part of its Windows personal computer software architecture. Both desktop and server versions of Windows provide standardized relational database access through Open Database Connectivity (ODBC), a SQL-based call-level API. Leading Windows software applications (spreadsheets, word processors, databases, etc.) from Microsoft and other vendors support ODBC,
and all leading SQL databases provide ODBC access. Microsoft has enhanced ODBC support with higher-level, more object-oriented database access layers as part of its Object Linking and Embedding technology (OLE DB), and more recently as part of Active/X (Active/X Data Objects, or ADO). When Microsoft began its effort in the late 1980s to make Windows a viable server operating system, it introduced SQL Server as its own SQL-based offering. SQL Server continues today as a flagship Microsoft product, and a key component of its .NET architecture for web services.

3.6. Relational Foundation

SQL is a language for relational databases, and it has become popular along with the relational database model. The tabular, row/column structure of a relational database is intuitive to users, keeping the SQL language simple and easy to understand. The relational model also has a strong theoretical foundation that has guided the evolution and implementation of relational databases. Riding a wave of acceptance brought about by the success of the relational model, SQL has become the database language for relational databases.

3.7. High-Level, English-Like Structure

SQL statements look like simple English sentences, making SQL easy to learn and understand. This is in part because SQL statements describe the data to be retrieved, rather than specifying how to find the data. Tables and columns in a SQL database can have long, descriptive names. As a result, most SQL statements “say what they mean” and can be read as clear, natural sentences.

3.8. Interactive, Ad Hoc Queries

SQL is an interactive query language that gives users ad hoc access to stored data. Using SQL interactively, a user can get answers even to complex questions in minutes or seconds, in sharp contrast to the days or weeks it would take for a programmer to write a custom report program. Because of SQL’s ad hoc query power, data is more accessible and can be used to help an organization make better, more informed decisions. SQL’s ad hoc query capability was an important advantage over nonrelational databases early in its evolution and more recently has continued as a key advantage over pure object-based databases.

3.9. Programmatic Database Access

SQL is also a database language used by programmers to write applications that access a database. The same SQL statements are used for both interactive and programmatic access, so the database access parts of a program can be tested first with interactive SQL and then embedded into the program. In contrast, traditional databases provided one set of tools for programmatic access and a separate query facility for ad hoc requests, without any synergy between the two modes of access.

3.10. Multiple Views of Data

Using SQL, the creator of a database can give different users of the database different views of its structure and contents. For example, the database can be constructed so that each user sees data only for his or her department or sales region. In addition, data from several different parts of the database can be combined and presented to the user as a simple row/column table. SQL views can thus be used to enhance the security of a database and tailor it to the particular needs of individual users.

3.11. Complete Database Language

SQL was first developed as an ad hoc query language, but its powers now go far beyond data retrieval. SQL provides a complete, consistent language for creating a database, managing its security, updating its contents, retrieving data, and sharing data among many concurrent users. SQL concepts that are learned in one part of the language can be applied to other SQL commands, making users more productive.

3.12. Dynamic Data Definition

Using SQL, the structure of a database can be changed and expanded dynamically, even while users are accessing database contents. This is a major advance over static data definition languages, which prevented access to the database while its structure was being changed. SQL thus provides maximum flexibility, allowing a database to adapt to changing requirements while online applications continue uninterrupted.

3.13. Client/Server Architecture

SQL is a natural vehicle for implementing applications using a distributed, client/ server architecture. In this role, SQL serves as the link between “front-end” computer systems optimized for user interaction and “back-end” systems specialized for database management, allowing each system to do what it does best. SQL also allows personal computers to function as front-ends to network servers or to larger minicomputer and mainframe databases, providing access to corporate data from personal computer applications.

3.14. Enterprise Application Support

The largest enterprise applications that support the daily operation of large companies and organizations all use SQL-based databases to store and organize their data. Data about business transactions (orders, sales amounts, customers, inventory levels, payment amounts, etc.) tends to have a structured, records-and-fields format, which converts easily into the row/column format of SQL. By constructing their applications to use enterprise-class SQL databases, major application vendors eliminate the need to develop their own data management software and can benefit from existing tools and programming skills. Because every major enterprise application requires a SQL-based database for its operation, new sales of enterprise applications automatically generate “drag-along” demand for new copies of database software.

3.15. Extensibility and Object Technology

The major challenge to SQL’s continued dominance as a database standard has come from the emergence of object-based programming, and the introduction of object-based databases as an extension of the broad market trend toward object-based technology. SQL-based database vendors have responded to this challenge by slowly expanding and enhancing SQL to include object features. These “object/relational” databases, which continue to be based on SQL, have emerged as a more popular alternative to “pure object” databases and have perpetuated SQL’s dominance through the last decade. The newest wave of object technology, embodied in the XML standard and web services architectures, has once again created a crop of “XML databases” and alternative query languages to challenge SQL. Previous history tends to suggest that XML-based extensions to SQL and the relational model will once again meet this challenge and insure SQL’s continuing importance.

3.16. Internet Database Access

With the exploding popularity of the Internet and the World Wide Web, and their standards-based foundation, SQL found a new role in the late 1990s as an Internet data access standard. Early in the development of the Web, developers needed a way to retrieve and present database information on web pages and used SQL as a common language for database gateways. More recently, the emergence of three-tiered Internet architectures with distinct thin client, application server and database server layers, has established SQL as the standard link between the application and database tiers. In the future, the role of SQL in the Internet will extend beyond web site architectures to include data management for collaborating applications and distributed objects in a web services architecture.

3.17. Java Integration (JDBC)

A major area of SQL development over the last five to ten years has been the integration of SQL with Java. Seeing the need to link the Java language to existing relational databases, Sun Microsystems (the creator of Java) introduced Java Database Connectivity (JDBC), a standard API that allows Java programs to use SQL for database access. JDBC received a further boost when it was adopted as the data access standard within the Java2 Enterprise Edition (J2EE) specification, which defines the operating environment provided by all of the leading Internet application servers. In addition to its role as a programming language from which databases are used, many of the leading database vendors have also announced or implemented Java support within their database systems, allowing Java to be used as a language for stored
procedures and business logic within the database itself. This trend toward integration between Java and SQL will insure the continued importance of SQL in the new era of Java-based programming.

3.18. Industry Infrastructure

Perhaps the most important factor contributing to the growing importance of SQL is the emergence of an entire computer industry infrastructure based on SQL. SQL-based relational database systems are an important part of this infrastructure. Enterprise applications that use SQL and require a SQL-based database are another important part, as are reporting tools, data-entry tools, design tools, programming tools, and a host of other tools that simplify the use of SQL. A large pool of experienced SQL programmers is a critical part of the infrastructure. Another important part is the training and support services that surround SQL and help to create and perpetuate SQL expertise. An entire subindustry has emerged around SQL consulting, optimization, and performance-tuning. All parts of this infrastructure tend to reinforce one another and contribute to the ongoing success of the other parts. Simply stated, to solve data management problems, the easiest, lowest-risk, lowest-cost solution is almost always a solution based on SQL.

Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.

Leave a Reply

Your email address will not be published. Required fields are marked *