What is SQL?
SQL is an abbreviation for Structured Query Language. It was originally pronounced “sequel” but came to be defined as “ess-que-ell” in the spec due to a copyright issue. Both pronunciations, however, are still in common use.
SQL is a mechanism that allows relational database management systems (RDBMSs) to be managed and used efficiently. SQL is defined and managed as an ISO standard; however, individual RDBMS vendors implement the SQL spec as they see fit. This means that SQL statements from one database are typically not compatible with another without some modification.
RDBMSs allow for the organization of data into objects called tables. In a very rough way, one could think about database tables like different sheets in a spreadsheet. However, they have a very tightly defined definition that creates columns intended to contain specific types of data (integers, characters, dates, binary objects, sets, boolean values, etc.) and limit those in intentional ways to control data or maximize performance. Additionally, RDBMSs will support managing users and what data those users have access to, enabling a single RDBMS to isolate data for specific users in a secure way.
SQL encompasses a number of different components that allow for the proper management of an RDBMS:
- Data definition – the creation, modification, and deletion of tables, indexes, and users. This is how the database structure, or schema, is maintained.
- Data access – specifically granting or revoking permissions for specific users. Some users may be able to fully read, update, and delete data from certain tables. Other users may have read-only access. And still, other users might not be able to access the data at all.
- Data manipulation – creating, updating, or deleting records in the database.
- Data query – this is how new result sets are created and where many would argue the real power of an RDBMS can be harnessed.
If one of these tables contained a list of hundreds of thousands of users, you could ask the database to give you a subset of all the users whose last name is “Smith.” If that’s still too big of a data set, you can ask for all the users whose last name is “Smith” that became members in 2020. If that’s still not the list you need, you can “join” the table with another table that has, for example, purchases. Now you can ask for all the users whose last name is “Smith” that became members in 2020 and purchased a product within 30 days.
Essentially, you can ask the database to do the hard work of finding specific sets of data that you need to send out welcome emails, generate receipts, create reports for future sales projections, or really just about anything imaginable with the data you have collected.
Beyond these basic functions, databases typically support more advanced features that allow for implementing programming with flow control, creating “views” of larger data sets, and sometimes even specialized features like managing geospatial data.
History
In 1970, Edward F. Codd, a British computer scientist who worked for IBM, wrote an influential paper called “A Relational Model of Data for Large Shared Data Banks.” This paper inspired Donald D. Chamberlin and Raymond F. Boyce, also computer scientists working for IBM at the San Jose Research Laboratory, to develop a language that could access and manipulate data in IBM’s System R database.
SQL was created in 1974 and was initially called SEQUEL, for Structured English Query Language System. For copyright reasons, the name was later shortened to SQL, or Structured Query Language. Tests on customer data at their sites demonstrated the utility of the system, and IBM began working towards a commercial product.
Shortly thereafter, inspired by the work of Codd, Chamberlin, and Boyce, Larry Ellison founded Software Development Laboratories (SDL). The company was renamed Relational Software, Inc. (RSI) in 1979 in coordination with the release of their flagship RDBMS product, Oracle V2. RSI beat IBM to market by several months. They again rebranded to Oracle Systems Corporation in 1983, which seems to have stuck.
Finally, in 1986, ANSI and ISO standards were established to standardize SQL and guide future development. There have been ten total updates to the standard, the most recent being in 2019. SQL is now a very popular and widely used tool for managing RDBMSs. SQL and RDBMSs were, in fact, the de facto standard for managing data until NoSQL began showing up in the late ‘90s, although not really gaining traction until about 2009. SQL is still the dominant paradigm, but NoSQL is gaining ground.
Implementations
Transact-SQL (T-SQL) is the implementation that Microsoft and Sybase implemented for their databases. Microsoft provides some comprehensive documentation on their site.
PL/SQL, or Procedural Language for SQL, is the implementation that Oracle created for its RDBMS system. Oracle also provides a significant amount of documentation online.
MySQL, which is an open-source database, though owned by Oracle, and one of the most popular databases in the world. They just call their implementation “SQL,” though it is not entirely compatible with the SQL standard. It’s very well documented on their site.
PostgreSQL, or just Postgres, is another popular open-source database. This one strives for a much higher level of compliance with the SQL standard. And they, too, have a wonderful set of documentation online.
There are many more RDBMSs available, and Wikipedia maintains a substantial list. However, the above four comprise roughly 85% of the market, and other DBs have much more limited adoption.
Explore
Because each vendor uses their own proprietary implementation of SQL, you can’t really get started in SQL without first picking a vendor. PostgreSQL might be the best “generic” choice as they are very close to the pure standard and represent quite a popular database. Thousands of books have been written on the different database systems, and infinitely more articles have been published online. When searching for information online, you must ensure that you are looking up the correct database and the correct version, as different versions of the same database will evolve over time.
SQL is a very complex topic requiring some solid foundational knowledge. But, for those serious about mastering the language, there is a plethora of information available on the web.
