Introduction to Relational Databases and SQL

What is a Database?

A database is fundamentally a collection of data stored in an organized fashion. In more strict technical terms, a database is defined as a self-describing collection of integrated records. Databases allow individuals and organizations to store, retrieve, and manage large volumes of information efficiently over time.

The Relational Database Model

The relational database model organizes data logically as two-dimensional tables. Under this model, initially outlined by E.F. Codd in 1970, users view data as a collection of tables that are related to one another through common data values .

Tables, Columns, and Rows

A relational database separates data into distinct, focused tables to minimize data redundancy.

• Tables: A table represents a single entity or subject, such as customers, orders, or employees
• Columns: Each table consists of columns, also known as attributes or fields. A column contains data of a specified type and embodies a single attribute of the table, such as a customer's phone number or an employee's hire date
• Rows: Tables are populated by rows, sometimes referred to as records or tuples. A row holds the complete data for a single occurrence of the entity, such as the complete contact information for one specific customer

Primary and Foreign Keys

To uniquely identify each row within a table, relational databases use a primary key. A primary key is a column, or a set of columns, whose values uniquely identify every row in that table. For instance, a customer table might use a unique customer ID number as its primary key.Tables of independent data are linked or related to each other using common columns known as foreign keys .

What is SQL?

SQL stands for Structured Query Language. It is the international standard language used to create, maintain, modify, and query relational databases. SQL operates differently from general-purpose procedural programming languages. SQL is a declarative, nonprocedural language. This means you use SQL to specify exactly what data you want to retrieve, rather than providing step-by-step programming instructions on how to retrieve it. The database engine determines the most efficient way to access the requested data.

Categories of SQL Statements

SQL statements fall into distinct categories based on their operational function:

• Data Definition Language (DDL): Statements used to define or modify database objects and structures, such as creating new tables. Commands include CREATE, ALTER, and DROP
• Data Manipulation Language (DML): Statements used to query and modify the actual data stored within those tables. The core DML commands are SELECT, INSERT, UPDATE, and DELETE
• Data Control Language (DCL): Statements used to control access and manage security permissions within the database system

.

Data Types

When defining a column in a table, you must assign it a specific data type. A data type acts as a rule defining what kind of data the column can hold, such as text, integers, or dates. This enforces data integrity by ensuring a user cannot insert a text string into a column strictly designed to hold numerical values.

Your First SQL Query

The SELECT statement is the most frequently used command in SQL. Its purpose is to retrieve information from one or more tables . The most basic query requires you to specify what data to retrieve and from which table.

SELECT * FROM employees;

In this syntax, the SELECT keyword is followed by an asterisk (*), which is a wildcard character instructing the database system to return all columns. The FROM keyword specifies the table source, which in this case is the employees table. The statement terminates with a semicolon, indicating the end of the SQL command.

Apply what you have learned about database structures and basic data retrieval to begin exploring your data.