Overview of SQL

Learn about SQL, where it’s used, and how it works.

We'll cover the following

Introduction to SQL

A database is an organized collection of raw information or data. A relational database, or more commonly an SQL database, stores data in tables and uses relations between them to retrieve information. SQL is the standard language for querying and manipulating databases. The American National Standards Institute (ANSI) and the International Organization of Standards (ISO) have both standardized SQL.

Press + to interact

SQL is the prevalent language for querying and manipulating relational databases.

Postgres is an SQL database. SQL plays an essential role in Postgres, as it does in most relational databases. We can use SQL databases in various applications, including web applications, data warehouses, and e-commerce systems.

Writing an SQL query

SQL is a declarative language that specifies a query’s desired result without providing the steps needed to achieve that result. For example, the following SQL query would return a list of all the users from the table named Users in a database:

SELECT
  *
FROM
  Users;
A simple SQL query

This declarative nature makes SQL easy to learn and use. However, it also means there can be many different ways to write a given query.

How does SQL work?

When we write an SQL query, we tell the database what data we want and how we want it organized. The database then determines the best way to fetch that data. This process is known as query optimization.

The optimizer is a sophisticated piece of software, and it’s constantly improving. As new features are added to SQL, the optimizer improves at choosing the most efficient way to execute queries. However, occasionally, there are cases where the optimizer does not choose the best possible plan. For these cases, it’s up to the developer to write the query so that the optimizer will choose the most efficient plan.

When writing SQL queries, keep them simple. The more complex a query is, the more likely that the optimizer will not choose the most efficient plan. When in doubt, we should consult our database system’s official documentation. The documentation will often have information on how the optimizer works and how to write queries that are more likely to be optimized efficiently.

Benefits of using SQL

SQL is easy to learn and use. It’s also a very powerful language, which makes it suitable for a wide range of applications. Finally, SQL is the standard language for RDBMS, which makes it easy to interoperate with other databases.

Drawbacks of using SQL

SQL can be slow when queries are complex. In addition, SQL isn’t well suited for some applications, such as real-time data processing or machine learning. Finally, SQL isn’t a Turing-complete language, meaning it can’t be used to write programs that can solve all possible problems.

Language syntax rules

The following are some of the essential characteristics of SQL language:

  • SQL is not a case-sensitive language. We can write SQL keywords or identifiers in uppercase, lowercase, or mixed case. However, using uppercase letters for keywords is a standard coding convention.

  • SQL statements can be on one or more lines.

  • We can’t split SQL keywords across lines.

  • SQL keywords are not reserved; we can use them as identifiers for tables or columns.

  • SQL uses single-line comments that start with -- and extend to the end of the line.

  • SQL also supports multiline comments that begin with /* and end with */.