Relational databases are a type of database which represents data in terms of tables, columns, and rows. Having existed since the seventies, these databases remain popular today. One of the likely reasons for the continued popularity of relational databases is due to the programming language used to control many relational databases, called: SQL. 

SQL stands for structured query language and it’s typically pronounced as either: “sequel” or “ess queue ell.” You’ll commonly hear it pronounced both ways. Though, I’ll use “sequel” during this course.

In this lesson, I’ll introduce you to concepts surrounding relational databases and SQL. Some programming languages, such as Python, Java, Go, Rust, etc, are general-purpose programming languages. They’re used to solve a wide range of problems. 

SQL is a bit different. SQL is a domain-specific programming language. Meaning that it’s a language used for a particular problem domain. SQL is a domain-specific language used for creating and controlling relational databases. So before we explore SQL we should first understand relational databases a bit more.

The intent of relational databases is to ensure the integrity of the data being stored. This is a rather important aspect of relational databases. By defining data integrity rules in advance of any data storage, a relational database can ensure that data is valid before saving. 

Relational databases consist of tables, rows, and columns. A table is used to model a specific concept. Where each column describes an attribute of the concept and each row represents a single instance of the concept being modeled. The concept being modeled is commonly referred to as an entity. 

Each column has a specific data type for which the data stored in the column must match. There are a wide range of types including text, integers, floating point numbers, booleans, dates, datetimes, etc. Columns are defined by specifying names, data types, and constraints. Columns include constraints which serve as the set of rules that must be followed in order for data to be saved. 

When modeling a concept using a table, attributes are defined using columns. Each row is an instance of the concept being modeled. Simple concepts may fit well in a single table. Though, some concepts are better divided across multiple tables.

Relational databases are able to establish relationships between rows in different tables. Which allows more complex concepts to be modeled using separate tables. Database developers commonly break concepts down into granular tables of interconnected entities. 

There are even rules which are commonly followed regarding how relational data should be structured. This is referred to as database normalization. Normalization is the process of structuring a relational database according to specific rules intended to ensure data integrity and reduce data duplication. 

The exact structure of the database is referred to as a schema. The schema defines all of the required database objects such as tables and constraints. The schema is defined using SQL commands allowing any SQL developer to manage the database schema as needed.

Okay, this is a good place to transition to talking about SQL. SQL is designed to make it easier for database users to interact with relational databases. It does that through a reasonably minimalist and English language-like syntax and language standardization.

Throughout the past several decades many different vendors have created relational databases which use SQL as means of programming the database. Going forward I’ll refer to any relational database which is programmed using SQL as a: SQL database. 

One of the reasons so many databases use SQL as a means of programming is due to the standardization of the language. Not all programming languages include a set of standards; which can result in different implementations of the language which behave differently. 

SQL does include a set of industry-recognized standards. And because many vendors follow these standards, SQL skills are applicable to just about every SQL database. Though there’re often minor syntactical differences and vendors do tend to include their own language extensions; For example, the popular Postgres database includes extensions for geographic information systems (GIS) data. 

Because SQL is used to create and control relational databases it includes a wide range of commands. These commands are often categorized based on their use case. 

These include:

• Data Definition

• Data Manipulation

• Data Control

• Transaction Control

Commands inside the data definition category are used to manage the database schema. Which includes commands for creating, altering, and deleting database objects such as tables. Commands inside the data manipulation category are used to store, retrieve, and delete data. Commands inside the data control category are used to grant and revoke database object access to database users. 

Commands inside the transaction control category are used to work with transactions. Transactions allow multiple data manipulation operations to be treated as a single operation. Which means all operations must be successful in order for the transaction to be considered successful. If an operation in a transaction fails, the transaction fails and can rollback any changes.

In part 2 of this lesson, we’ll take a look at some examples of the SQL syntax. So when you’re ready to keep learning, I’ll see you in the next lesson.