Skip to content

SQL Relational Databases wouldn’t be the same without it.

The Structured Query Language. SQL (Structured Query Language) is a standardized programming language used to manage and manipulate relational databases. It is the primary language for interacting with data stored in relational database management systems (RDBMS).

I first used SQL back in 1987, loaded on to an IBM 370 Mainframe Computer which was a similar size to my garden shed but less powerful than my modern PC by some considerable amount.

How is it used for

SQL is used to Select from or Retrieve, Update, Delete and Create tables or a Relational Database. There are two types of Database, a Relational Database and a Non-Relational Database. A Database holds Tables, organised like Spreadsheets, each table within the database has a link to another table, an index and a key. Non-Relational databases include JSON, XML, and others. We would use NoSQL to work with Non-Relational databases.

The Development of SQL

SQL (Structured Query Language) was developed in the early 1970s at IBM as part of a research project called System R, which aimed to demonstrate the practicality of relational database management systems (RDBMS). Here’s a detailed timeline:

To work with a Relational Database we would need to install a DBMS, a dashboard where create the Db, it’s tables, and manipulate the data.

Key Milestones in SQL Development

  1. 1970 – Theoretical Foundation:
    • Dr. Edgar F. Codd, a researcher at IBM, published his seminal paper titled “A Relational Model of Data for Large Shared Data Banks” in the journal Communications of the ACM.
    • He introduced the concept of the relational model for data storage and querying, which forms the foundation of SQL.
  2. 1973-1974 – Development of SEQUEL:
    • IBM began developing a query language for their experimental relational database system, System R.
    • The language was initially called SEQUEL (Structured English Query Language) to make database interaction more accessible.
    • SEQUEL was designed to retrieve and manipulate data in a user-friendly, declarative way.
  3. 1976 – Renaming to SQL:
    • SEQUEL was renamed SQL due to a trademark conflict with an existing UK-based company.
  4. Late 1970s – System R and Early Prototypes:
    • IBM demonstrated the effectiveness of SQL with System R.
  5. 1980s – Standardization and Adoption:
    • In 1979, Relational Software, Inc. (later renamed Oracle) introduced the first commercially available RDBMS, which used SQL.
    • SQL gained widespread adoption and became the de facto standard for querying relational databases.
  6. Modern Era – Ongoing Evolution:
    • SQL has been continuously enhanced to include support for new features such as complex queries, advanced data types, and procedural extensions (e.g., PL/SQL, T-SQL).
    • It remains a core component of modern databases like MySQL, PostgreSQL, Microsoft SQL Server, and Oracle Database.

Why SQL Was Revolutionary

  1. User-Friendly:
    • SQL’s declarative syntax allowed users to focus on what data to retrieve, not how to retrieve it, making it accessible to non-programmers.
  2. Based on Relational Theory:
    • The relational model, combined with SQL’s ease of use, proved more effective than hierarchical or network-based database systems.
  3. Scalable and Flexible:
    • SQL’s ability to work with relational databases enabled it to scale to large data systems while maintaining simplicity.

SQL’s development marked a turning point in database technology, and it continues to be a cornerstone of data management today.

I’m working with WordPress and PHP.

PHP (short for “Hypertext Preprocessor”) is a popular, open-source scripting language widely used for web development. It is executed on the server side and is designed to create dynamic and interactive web pages.

Key Features of PHP

  1. Server-Side Execution:
    • PHP scripts are executed on the web server, and the resulting HTML is sent to the client’s browser.
    • Example: Fetching data from a database and displaying it dynamically.
  2. Embedded in HTML:
    • PHP code can be seamlessly embedded within HTML, making it easy to mix programming logic with web page design.
    • Example: <html> <body> <h1>Welcome, <?php echo "User"; ?>!</h1> </body> </html>
  3. Database Integration:
    • PHP supports integration with various databases like MySQL, PostgreSQL, SQLite, and more, making it ideal for building data-driven applications.
  4. Cross-Platform:
    • Runs on various operating systems (Linux, Windows, macOS) and web servers (Apache, Nginx, IIS).
  5. Extensive Libraries:
    • Built-in functions and libraries for tasks like handling file uploads, session management, and data encryption.
  6. Free and Open Source:
    • Developed by a global community, PHP is free to use and widely supported.

Common Uses of PHP

  1. Dynamic Website Development:
    • PHP is used to create websites where content changes based on user interactions, like blogs, forums, and social media platforms.
  2. Content Management Systems (CMS):
    • Platforms like WordPress, Drupal, and Joomla are built on PHP.
  3. E-commerce:
    • PHP powers online stores like Magento and WooCommerce.
  4. RESTful APIs:
    • PHP is used to create backend APIs that power mobile apps and web services.
  5. Web Applications:
    • Popular frameworks like Laravel, Symfony, and CodeIgniter are based on PHP, simplifying large-scale application development.

Back to SQL

Now you know how SQL came about and where it’s used. Now for some examples of it’s use.

I will only show some basic examples since SQL is possibly one of the most written about subject on the Web along with HTML, CSS, JavaScript and PHP.

Certainly! Here are some simple examples of SQL queries for common tasks:

1. Creating a Table

To create a table to store information about users:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100),
    age INT
);

2. Inserting Data

To add some data to the users table:

INSERT INTO users (name, email, age) 
VALUES ('Alice', 'alice@example.com', 25),
       ('Bob', 'bob@example.com', 30),
       ('Charlie', 'charlie@example.com', 35);

3. Retrieving Data

To fetch all records from the users table:

SELECT * FROM users;

To fetch only names and emails of users:

SELECT name, email FROM users;

4. Filtering Data

To find users who are older than 30:

SELECT * FROM users WHERE age > 30;

To find a user named “Alice”:

SELECT * FROM users WHERE name = 'Alice';

5. Updating Data

To update Bob’s age to 32:

UPDATE users 
SET age = 32 
WHERE name = 'Bob';

6. Deleting Data

To remove a user with the name “Charlie”:

DELETE FROM users 
WHERE name = 'Charlie';

7. Sorting Data

To list users sorted by age in ascending order:

SELECT * FROM users 
ORDER BY age ASC;

To sort in descending order:

SELECT * FROM users 
ORDER BY age DESC;

8. Counting Records

To count the total number of users:

SELECT COUNT(*) AS total_users 
FROM users;

9. Using Aggregate Functions

To find the average age of all users:

SELECT AVG(age) AS average_age 
FROM users;

10. Joining Tables

If there is another table called orders:

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    product_name VARCHAR(100),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

To list all users and their orders:

SELECT users.name, orders.product_name
FROM users
JOIN orders ON users.id = orders.user_id;

These examples cover fundamental operations in SQL. There is so much more to using SQL including working with Views and PL/SQL.

I will come back with more content for this page in due course.