How was SQL Defined and Conceived?

Structured Query Language (SQL) is an English-based element that provides you with the ability to ask questions of a database and get answers to those questions.

SQL was initially developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s. This version, initially called SEQUEL (Structured English Query Language), was designed to manipulate and retrieve data stored in IBM's original quasi-relational database management system, System R, which a group at IBM San Jose Research Laboratory had developed during the 1970s. The acronym SEQUEL was later changed to SQL because "SEQUEL" was a trademark of the UK-based Hawker Aircraft Company. In the late 1970s, Relational Software, Inc. (now Oracle Corporation) saw the potential of the concepts described by Codd, Chamberlin, and Boyce and developed their own SQL-based RDBMS with aspirations of selling it to the U.S. Navy, Central Intelligence Agency, and other U.S. government agencies. In June 1979, Relational Software, Inc. introduced the first commercially available implementation of SQL, Oracle V2 (Version2) for VAX computers. After testing SQL at customer test sites to determine the usefulness and practicality of the system, IBM began developing commercial products based on their System R prototype including System/38, SQL/DS, and DB2, which were commercially available in 1979, 1981, and 1983, respectively.

SQL is a bit different from developing applications that gather this information from the database by manipulating the database directly.
In those cases, the application is responsible for determining how the information is analyzed and retrieved.
With SQL, you do not have to indicate how you want information retrieved, you only indicate what criteria you want to use. This is called a declarative language.

SELECT * FROM Employees 
WHERE Salary > 50000;

While SQL isn't a direct implementation of Relational Algebra, it's heavily influenced by it. Here's how:

• Foundation: Relational Algebra provides the theoretical underpinnings for relational databases, which is the type of database SQL interacts with. It defines fundamental operations like:
  • Selection: Choosing certain rows (like WHERE in SQL)
  • Projection: Choosing certain columns (like SELECT in SQL)
  • Join: Combining data from multiple tables (various JOIN types in SQL)
  • Union, Intersection, Difference: Combining data in set-like ways
• Logical Structure: SQL borrows these core concepts from Relational Algebra to manipulate and query data. You can see the parallels in how you express logical conditions and combine data from different sources.

Key Differences:

• Procedural vs. Declarative: Relational Algebra is more procedural, specifying the order of operations. SQL is declarative, focusing on the desired outcome.
• Completeness: Relational Algebra is relationally complete, meaning it can express all possible queries on relational data. SQL, while powerful, might have some limitations in expressiveness.
• Syntax: SQL has a more user-friendly syntax designed for humans, while Relational Algebra uses a more mathematical notation.

In essence:
Think of Relational Algebra as the blueprint and SQL as the building constructed from that blueprint. SQL takes the core ideas of Relational Algebra and packages them into a more practical and user-friendly language for interacting with databases. While you don't need to master Relational Algebra to use SQL effectively, understanding its principles can give you deeper insights into how SQL works and how to write more efficient queries.
The kind of operations needed in a table-at-a-time language were introduced in the form of relational algebra. For tutorial purposes, relational algebra has the advantages that fundamental operations such as selecting rows, selecting columns and joining tables are expressed explicitly as separate statements. Relational algebra also complies with the requirements of the relational model. In practice, the table-at-atime language provided with almost all relational database management systems is the SQL (pronounced ess-cue-ell) language, which combines row, column and join operations into a single statement and which deviates in some respects from the principles of the relational model. SQL is an evolving language, both in terms of actual implementations and the standardisation process. This module is based upon the ISO SQL 2023 standard, but its aim is to convey the flavour of SQL rather than give an exhaustive description of a particular implementation or standard. Consequently, certain facilities are omitted or only touched upon lightly. In SQL, attributes are referred to as columns. Names, such as table and column names, are case insensitive, for instance roomNo, roomno, RoomNo and ROOMNO all refer to the same column name. We will use lower case letters and underscores, as in room_no, rather than capitalisation for such user-defined names. SQL keywords, such as CREATE, SELECT, DELETE, are also case insensitive. We will write these in upper case. Character literal values are case sensitive and enclosed in quote marks, so 'Smith', 'SMITH', and 'sMiTh' are three distinct values. A null is usually displayed as space(s), but we will represent it by <null> to distinguish it from a character literal of space(s). The rows in the table are in order so as to make it easier to compare the contents of the tables and follow the SQL examples.
As required by the relational model, SQL makes no assumption about the order of the rows.

SQL (Structured Query Language) is often described as a declarative language because it allows users to specify *what* they want to achieve, rather than *how* to achieve it. This is in contrast to procedural or imperative languages, where the programmer must explicitly define the steps to obtain the desired result.
Here are the characteristics of a declarative language that SQL exhibits:

1. Focus on the "What" Rather than the "How"
   • In SQL, you describe the desired outcome without specifying the algorithm or the sequence of steps the database must perform to achieve that outcome.
   • Example:
     

     SELECT name FROM employees WHERE department = 'Sales';
     

     This query specifies what data is required (names of employees in Sales) without detailing the process to retrieve it.
2. High-Level Abstraction
   • SQL abstracts the underlying operations (e.g., file reads, index usage, or memory management) and provides a higher-level interface for interacting with data.
   • Database systems handle these implementation details behind the scenes.
3. Declarative Constructs
   • SQL includes declarative constructs for data retrieval (SELECT), data definition (CREATE, ALTER, DROP), and data manipulation (INSERT, UPDATE, DELETE).
   • Example: The CREATE TABLE statement defines a table's structure without detailing the internal data storage.
4. Optimization by the Database Engine
   • Declarative languages rely on an underlying system to determine the best execution plan.
   • In SQL, the query optimizer decides how to execute a query efficiently, based on factors like available indexes, table statistics, and join strategies.
5. Set-Based Operations
   • SQL works with sets of data, allowing operations on entire tables or result sets, rather than requiring row-by-row processing (though procedural extensions like PL/SQL can be used for such cases).
   • Example:
     

     UPDATE orders SET status = 'Processed' WHERE status = 'Pending';
     

     This updates all matching rows in one operation rather than requiring a loop.

Declarative vs. Procedural SQL Extensions: While SQL itself is declarative, procedural extensions like PL/SQL (Oracle) or T-SQL (SQL Server) allow procedural logic (loops, conditionals) for more complex operations. These extensions are considered imperative in nature.
Conclusion: SQL is fundamentally a declarative language, enabling users to focus on the "what" of database interactions while the database management system takes care of the "how."
A declarative language is a language in which you simply indicate what you need and let the database engine get it for you. How the information is actually gathered is irrelevant to the user. In the next lesson, a sample SQL statement will be examined.