Structured Query Language SQL and Database Applications

Overview of SQL and Database Applications

  • Structured Query Language (SQL):

    • Pronunciation: Commonly pronounced as "sequel", though some professionals prefer pronouncing each letter individually as "S-Q-L".
    • Definition & Function: SQL represents the standardized instruction format used to convey commands to a relational database system.
    • Execution Distinction: SQL statements represent instructions sent to a database, which is distinct from the database engine executing those instructions internally.
    • Analogy: SQL operates similarly to Minecraft console commands, remaining largely independent of the underlying execution software and hardware environment.
    • Educational Resources: A key introductory resource for learning SQL syntax and examples is www.w3schools.com.

  • Database Application System Architecture:

    • In addition to SQL command interfaces, full database applications are typically built using systems and programming languages such as:
    • C++: A platform-dependent language.
    • Java: A platform-independent language.
    • PHP: A server-side scripting language.

Programming Languages in Database Applications: C++ vs. Java

  • C++ in Database Applications:

    • Language Type: High-level programming language equipped with low-level capabilities.
    • Low-Level Capabilities: Programming statements operate close to the CPU's native instruction set.
    • High-Level Capabilities: CPU instructions are grouped or abstracted to make code more human-readable and easier to program.
    • Platform Dependency: The inclusion of low-level capabilities makes C++ platform-dependent.
    • C++ can reserve specific memory address blocks and directly access any memory location.
    • Because different computer systems feature distinct memory configurations, C++ code must be explicitly written and compiled for a specific hardware target.
    • Performance Advantage:
    • C++ applications execute faster because they are heavily optimized for target hardware configurations.
    • Performance gains stem from better utilization of specific machine capabilities (e.g., expanding and addressing larger RAM spaces directly).
    • For structured data, directly accessing specific memory addresses within RAM substantially increases execution speed.
    • Consequence: Most commercial high-performance database management systems are engineered in C++.

  • Java in Database Applications:

    • Language Type: High-level programming language lacking low-level capabilities.
    • Memory Management & Virtual Machine Mapping:
    • During software installation, local machine hardware is mapped relative to standard Java environment elements.
    • System memory allocation is fully abstracted and managed by Java; programmers cannot directly access raw memory addresses.
    • Platform Independence:
    • Human programmers write generic source code.
    • The source code is translated to execute according to the precise configuration of the host machine.
    • Design Advantage: Java applications are simpler to design because developers write generic, portable code rather than hardware-specific optimizations.

  • Java Use Cases and Alternative Languages:

    • Primary Java Use Cases:
    • Server-side applications operating within an nn-tier architecture across diverse hardware platforms.
    • Applications responsible for processing and delivering data to downstream systems or end users.
    • High-security applications: C++ carries security vulnerabilities arising from direct memory access, whereas Java's memory isolation prevents these access risks.
    • Other Relevant Languages in Data Ecosystems:
    • Python & R: Used primarily for data analysis and statistical computing.
    • C#: Used for server applications, particularly within Microsoft Windows infrastructure.
    • PHP: Used for server-side web scripting.

Webpage Access to Back-End Databases via PHP

  • PHP Characteristics:

    • Full Name: PHP stands for PHP: Hypertext Preprocessor (a recursive acronym).
    • Execution Context: PHP is embedded directly inside webpage files as server-side script code.

  • Step-by-Step Procedure for PHP Database Queries:

    1. Web Server Storage: A web server hosts a repository of webpage files containing embedded PHP script code intended to make database calls.
    2. User Request: A client/user issues a HTTP request to the web server for a specific webpage.
    3. Script Execution & Query Execution: The web server executes the embedded PHP script, connects to the back-end database, executes the SQL query, and parses the returned query results into standard HTML code.
    4. Dynamic Page Construction: The web server constructs a updated webpage by replacing the original PHP script block with the newly generated HTML content.
    5. Response Delivery: The web server sends the fully rendered HTML webpage back to the client browser.

Early History and Evolution of SQL

  • The Relational Database Model (c. 19701970):

    • Introduced around 19701970 as a superior alternative to existing network and hierarchical database architecture models.
    • Structural Differences: Network database models featured less structural constraint, whereas hierarchical database models enforced rigid, trees-like structural constraints.
    • Reference resource: https://www.dataversity.net/brief-history-data-modeling/

  • Standardization and Industry Convergence:

    • Commercial relational database applications emerged within a few years of the model's introduction, requiring a standardized command language.
    • While multiple vendor-specific query languages initially competed, they rapidly converged into an industry standard known as SQL by 19861986.
    • SQL was universally adopted by major enterprise vendors (e.g., IBM and Oracle), as there was little immediate requirement for vendor specialization at that stage.

  • Modern SQL Implementations & Dialects:

    • SQL underwent continuous revisions over following decades, regularly integrating new syntax, functions, and relational features.
    • Contemporary Status: Although formal standard specifications exist (ANSI/ISO SQL), SQL is no longer a strictly monolithic industry standard across vendors.
    • Dialects feature differing implementations and interpretations of advanced features.
    • Vendors maintain varying degrees of backward and current version compatibility.
    • Vendors routinely introduce proprietary extension features or omit specialized standard features.
    • Despite vendor-specific variations, basic core SQL syntax remains uniform across almost all database platforms.

SQL Syntax Rules and Language Groups

  • SQL Syntax Principles:

    • Commands require specific, ordered parameter structures that sequentially supply necessary execution details.
    • Sequential Construction Examples:
    • Creating a Table: Requires specifying the table name first, followed by an ordered list of fields along with their data formats.
    • Updating a Record: Requires naming the target table first, specifying the target field values to set, and defining row matching criteria.
    • Running a Query: Requires specifying columns to display, naming the target table(s), and providing filter criteria.
    • Strictness: While ordering within specific sub-clauses offers minor flexibility, SQL syntax is strictly enforced to guarantee execution precision and security.

  • The Four Core SQL Instruction Groups:

    • Data Definition Language (DDL): Instructions utilized for defining, altering, and deleting database structures like tables, views, and columns.
    • Data Manipulation Language (DML): Instructions utilized for modifying, inserting, and removing data rows stored inside existing tables.
    • Data Query Language (DQL): Instructions utilized for querying and retrieving records from the database.
    • Data Control Language (DCL): Instructions utilized for managing user access permissions, privileges, and security roles.

  • Secondary SQL Instruction Groups:

    • Transaction Control Language (TCL): Used for managing database transactions (e.g., COMMIT, ROLLBACK).
    • Session Control, System Control, & Embedded SQL Statements.

Data Definition Language (DDL)

  • Core DDL Statements:

    • CREATE TABLE: Defines table structures using the general format CREATE TABLE {<list of fields, keys, formats>}.
    • ALTER TABLE: Modifies existing column definitions in a table when appended with ADD or DROP clauses.
    • TRUNCATE TABLE: Removes all data records stored inside a table while leaving the structural table schema intact.
    • DROP TABLE: Permanently deletes an entire table along with its underlying structure from the database.

  • DDL Operational Workflow:

    • Relational Databases: Developers first model the relational design, identifying all necessary tables, fields, keys, and formats, and then issue DDL commands to construct the database. Once deployed online, the relational schema rarely changes.
    • Non-Relational (NoSQL) Databases:
    • Key-Value Stores: Data is stored as binary or text blobs, with structure determined dynamically by external application logic rather than internal database schemas.
    • Document Stores: Record structures are generated dynamically as loose collections of similar items.

  • DDL Implementation across Applications:

    • Microsoft Access: DDL operations are usually managed graphically through creation wizards and visual table design interfaces; however, custom DDL SQL statements can be executed directly within Query SQL View.
    • MySQL: Open-source database management system where tables are defined using back-end administrative tools or executed dynamically via user web scripts assigned appropriate execution permissions.

Data Manipulation Language (DML) and Filter Clauses

  • Core DML Statements:

    • INSERT INTO: Adds new data records to a target table using INSERT INTO {table, field values}.
    • UPDATE: Modifies existing field values within table records using UPDATE {table} SET {column1 = value1, column2 = value2, ...} WHERE {matching conditions}.
    • DELETE FROM: Deletes records matching specific parameters using DELETE FROM {table} WHERE {matching conditions}.

  • The WHERE Clause:

    • WHERE functions as a filtering clause, not an independent statement, and cannot exist outside of a parent query or manipulation command.
    • Supported Matching Criteria:
    • Equalities: value = ???
    • Comparisons: value < ???
    • Pattern Matching: LIKE (e.g., matching string prefixes or substrings).
    • Set Membership: IN {some set of options}.

Data Query Language (DQL)

  • Core Query Constructs:

    • DQL focuses on reading database data and ordering the output result set.
    • The standard query relies on the SELECT … FROM … WHERE sequence:
    • SELECT: Specifies the list of columns to retrieve and display.
    • FROM: Specifies the source table(s) to search.
    • WHERE: Filters which rows meet output evaluation criteria.

  • Advanced DQL Clauses:

    • ORDER BY: Sorts the returned result set by specified columns in ascending or descending order.
    • GROUP BY: Aggregates records into group rows based on matching values in specified columns.
    • HAVING: Sets conditional criteria to filter aggregated groups generated by GROUP BY.

  • Annotated DQL Code Walkthrough:

    • SQL Query: `SELECT firstName, lastName FROM Users WHERE id =