2nd Dec 2020 7 minutes read SQL Technologies Are More Common Than You Think Agnieszka Kozubek-Krycuń sql learn sql Table of Contents SQL in Databases Relational Databases = SQL Databases Data Warehouses and Analytical Databases Modern Databases: Cloud Databases, Column-Store Databases, In-Memory Databases NoSQL Databases Data Processing Technologies Distributed Data Processing Technologies Full-text search engines SQL in Everyday Apps Spreadsheets Smartphone apps SQL in Web Browsers SQL in the Command Line SQL Is Everywhere Where is SQL being used today? The answers might surprise you! (Hint: SQL technology is basically everywhere.) Is it worth learning SQL? After all, the language was created in the 1970s. Is it still relevant today? The list of technologies that support SQL is very long – and it’s growing. Read on to find out what kinds of programs use SQL. SQL in Databases Relational Databases = SQL Databases It’s no surprise that SQL is used in relational databases. SQL started in the 1970s as the query language for relational databases (a brand-new technology at the time). Nowadays, SQL is the standard language for communicating with relational databases. Popular relational databases include Oracle, SQL Server, MySQL, PostgreSQL, and SQLite. Have you noticed that a lot of them have “SQL” in their name? This used to be a big selling point. Relational databases are often called “SQL databases” because of their SQL support. Relational databases are used in a lot of enterprise applications. They are a common technology that web applications use to store data. Online stores, content management platforms (e.g. WordPress, Joomla, or Drupal), Customer Relationship Management (CRM) apps, Enterprise Resource Planning (ERP) software, banking applications, and even social media sites like Facebook all use relational databases. In fact, relational databases used to be the default data storage technology for many applications. Today, they are still a very common solution, especially for small and medium-sized companies. Data Warehouses and Analytical Databases A data warehouse is a system used for reporting and data analysis. It collects data from many different sources, such as the operational databases in different company branches and departments. Storing current and historical data in one place enables analysts to create reports quickly and easily. Many data warehouse technologies, like Microsoft SQL Server Analysis Services and Oracle Exadata, are built on top of popular relational databases, so obviously they support SQL. Many other data warehouse technologies (e.g. Teradata, SAP IQ) also use SQL. Modern Databases: Cloud Databases, Column-Store Databases, In-Memory Databases Traditional relational databases all have a similar architecture: All information is stored on hard drives. Data is organized into tables and rows. B-tree or B+tree indexes are used to index data. In the 2000s, people started to notice that this architecture does not work well for extremely large-scale applications, such as Google’s search engine or big social networks. New architectural designs for databases began to be developed, including: Columnar databases organize data in columns rather than rows. In-memory databases keep their data in-memory and not on the hard drive. Cloud databases run on a Cloud computing platform rather than on local hardware. Interestingly, many of these modern databases support SQL. Cloud databases like Amazon Redshift, Google BigQuery, and Microsoft Azure SQL Database all support SQL, as do columnar databases like SAP HANA and MonetDB. The in-memory database VoltDB supports SQL, and these are just a couple of examples. Even with significant architectural changes, database creators keep adding SQL support to their databases. NoSQL Databases NoSQL databases are perhaps the most surprising type of SQL-supporting databases. The NoSQL movement started in the 2010s as a response to traditional relational databases’ incompatibility with extremely large-scale applications. “NoSQL” refers to all databases that use an architecture different from that of traditional relational databases: document databases, graph databases, key-value databases, and wide-column databases. Not all NoSQL databases support SQL, but some do. OrientDB supports SQL, while Apache Cassandra uses its own query language, CQL, which is very similar to SQL. Nowadays, people often define the acronym “NoSQL” as meaning “Not Only SQL”. They want to emphasize that the NoSQL movement is not directly against using SQL. Data Processing Technologies Distributed Data Processing Technologies With the arrival of new data-storing technologies like Cloud and NoSQL databases, new data processing technologies also came on the scene. These allowed for distributing computations on data over many different computers, which is known as distributed data processing. This technology is probably most associated with Hadoop. Originally, you had to write programs for Hadoop in Java. However, Hadoop creators soon realised that people who needed to process data were often data analysts and not necessarily programmers. The language they knew well was SQL. That’s how Hive, a SQL interface for Hadoop, came into existence. Hive became many people’s preferred way to write programs for Hadoop. Today, Hadoop is considered old-school, but there are many new distributed data processing technologies, such as Apache Spark, Apache Flink, and Apache Beam. All of them offer SQL as a programming interface – mostly because it’s so universally known. There’s also the Presto project, which is a distributed SQL query engine for Big Data. Its only goal is to give a unified SQL interface to various distributed data-storing technologies. Full-text search engines Full-text search engines are tools that know how to efficiently search text data (such as product descriptions, Wikipedia articles, blog websites, and book contents). These tools can somewhat understand our natural language. For example, they understand that if you’re looking for the text “like”, you may also be interested in the words “liked” or “liking”. They know how to sort search results by relevance, highlight matching text, and correct spelling mistakes in search terms. Major full-text search engines, such as Apache Solr - and Elasticsearch, support SQL as a query language. SQL in Everyday Apps All of the technologies listed above are used to build large-scale applications that can serve thousands or millions of users. SQL is used by developers to write and maintain these large applications and by data analysts to analyze the data coming from them. Is SQL limited to large-scale applications used by international corporations? Nope. Here are some apps that nearly everybody uses: Spreadsheets Spreadsheets organize and process data in tabular form. Common spreadsheet applications include Microsoft Excel, LibreOffice Calc, Numbers, and Google Sheets. You can use SQL in Google Sheets to get data out of worksheets. As far as I know, there is no built-in support for SQL in Excel, but there are external add-on tools that allow you to use SQL in Excel, like QuerySurge or XLTools. Similar tools for other spreadsheets also exist. Smartphone apps Smartphone apps store data on your phone, such as your settings or account data. Their technology of choice often is SQLite, a lightweight embedded relational database. A smartphone app developer uses SQL to get data to and from the database, making it visible to the app user or enabling the app to save user data to the device. SQL in Web Browsers You probably didn’t know about it, but if you’re using a Chrome, Opera, or Android web browser, your browser has a small SQL database built in. It’s called Web SQL Database and it allows website developers to store some information in the browser (for example, the look and feel of the website) and retrieve it to modify that site’s behavior. This technology is now deprecated, which means it’s not recommended for use. Still, it’s interesting to know it was possible at one time. Maybe SQL databases in web browsers will make a comeback one day? SQL in the Command Line Finally, tools like q allow the execution of SQL-like queries on CSV and TSV files using the command line. Pretty neat, isn’t it? SQL Is Everywhere As you can see, the list of technologies that use SQL is impressive. SQL has become the standard data language for all large- and small-scale data processing technologies. It’s so popular and widespread because it has a simple syntax that’s easy for non-programmers to learn. At the same time, SQL is powerful and expressive. You can ask it to do a very complex operation using a few short commands. SQL has solid mathematical foundations that make it sound, complete, and well-defined. Even though this language is over 40 years old, it has stood the test of time. And it’s not going anywhere – SQL is more relevant today than it‘s ever been. If you plan to work with data, learning SQL is a necessary part of your training. The best way to learn SQL is through practice. We recommend starting with our interactive SQL Basics course. You can then continue your SQL journey with our SQL from A to Z learning track, the most complete set of SQL courses on the Internet. Tags: sql learn sql