Summary: In this tutorial, you’ll understand the databases and SQL, the standard language for interacting with the databases.
Introduction to databases #
A database is a structured collection of data stored on a computer. It is a powerful tool for storing and organizing data efficiently.
Today, you can find databases everywhere, from your smartphone to computer to the online services you use.
Here are some main features of databases:
- Efficient data storage: Databases offer a central location to store structured data such as sales, inventory, and finance.
- Fast data retrieval: Databases allow you to access information quickly, much faster than reading data stored on spreadsheets and other flat files.
- Efficient data management: Databases allow you to quickly insert, update, and delete data.
- Data Sharing: Databases enable multiple users and applications to access the central information simultaneously.
- Data analysis: Databases provide the structure that you can use to analyze the data and gain valuable insights.
There are various types of databases; each has its specific use cases.
Here are two common database types:
- Relational database management systems (RDBMS): This is the most common database type, organizing data into tables with rows and columns. The popular RDBMS are PostgreSQL, MySQL, MariaDB, Oracle Database, SQL Server, and IBM Db2.
- Document databases (or NoSQL databases): These types of database stores data as documents. The popular document databases are MongoDB, Databricks, and Amazon DynamoDB.
To interact with data in RDMBS, you need to use Structrured Query Language or SQL, which is the central topic of this site.
Overview of SQL #
SQL stands for the structured query language. SQL is pronounced as /ˈɛs kjuː ˈɛl/ or /ˈsiːkwəl/.
SQL is a standard language for interacting with RDBMS. It allows you to:
- Create and maintain database structures such as tables.
- Insert, update, and delete data.
- Retrieve data from tables.
SQL consists of three main parts:
- Data definition language (DDL) deals with the database structure creation and modification. For example, CREATE TABLE, ALTER TABLE, and DROP TABLE statements.
- Data manipulation language (DML) provides statements to query data, such as the SELECT statement, and modify the data like INSERT, UPDATE, and DELETE statements.
- Data control language (DCL) includes statements, working with user authorization and security, such as GRANT and REVOKE statements.
Key SQL concepts #
The core of an RDBMS is tables. Tables allow you to organize data in rows and columns:
- Columns: represent specific fields
- Rows: stores individual records.
For example, an employees table has the employe_id, first_name, last_name, email, phone_number, … columns. It contains many rows, each representing an employee record.
SQL Standards #
SQL was one of the first commercial database languages since 1970. Since then, different database vendors implemented SQL in their products with some variations.
The American Standards Institute (ANSI) published the first SQL standards in 1986 to bring greater vendor conformity. ANSI updated the SQL standard in 1992, SQL92, and SQL2, and again in 1999 as SQL99 and SQL3. Every time, ANSI added new features and commands into the SQL language.
Today, ANSI and the International Standards Organization maintain SQL Standards as ISO/IEC 9075 standard. The latest release standard is SQL:2023.
The SQL Standards formalize SQL syntax structures and behaviors across database products. It becomes even more critical to open-source databases such as MySQL and PostgreSQL, where the RDBMS are developed mainly by the communities rather than big corporations.
SQL Dialects #
SQL dialects are like English spoken by various English-speaking countries such as the UK, the US, and India.
SQL dialects exist for the following reasons:
- Innovation: RDMBS vendors and communities continuously add new features to optimize their database products. SQL dialects allow them to provide unique features not part of SQL standards.
- Performance: Dialects include statements that work well with a particular database, resulting in faster statement execution.
Here are some popular SQL dialects:
- MySQL has its own procedural language since version 5.
- PL/SQL stands for procedural language/SQL. It is developed by Oracle for the Oracle Database.
- Transact-SQL or T-SQL is developed by Microsoft for Microsoft SQL Server.
- PL/pgSQL stands for Procedural Language/PostgreSQL that consists of SQL dialects and extensions implemented in PostgreSQL.
In each tutorial, we’ll explain the SQL syntax structures and behaviors that are valid across the databases. We’ll also discuss the exceptions that may exist in specific databases.
