Structured Query Language (SQL) is a fundamental skill for computer science professionals in Kenya who work with databases, enabling them to manage and manipulate data efficiently. As organizations increasingly rely on digital data for decision-making, proficiency in SQL is essential for designing, querying, and maintaining databases that support business operations. This chapter explores SQL’s definition, its defining characteristics, and the core components that form the basis of database interactions in professional environments such as banks, universities, and government agencies.

3.1 SQL

SQL is the standard language used to communicate with relational database management systems (RDBMS). It allows users to perform a variety of operations including data retrieval, insertion, update, and deletion. Understanding SQL is crucial for computer science professionals in Kenya, especially when working with systems in sectors like finance, healthcare, and public administration, where accurate data handling is critical.

3.1.1 Definition

SQL stands for Structured Query Language, a specialized programming language designed for managing relational databases. It provides a standardized way to create, read, update, and delete data stored in tables, which are organized into rows and columns. Unlike general-purpose programming languages, SQL focuses exclusively on data manipulation and querying within database systems.

SQL emerged in the 1970s as a result of research on relational database models, and it has since become the universal language supported by most commercial and open-source database systems. In Kenya, institutions such as the Kenya Revenue Authority (KRA) and commercial banks rely heavily on SQL to manage large volumes of transactional data securely and efficiently.

Characteristics of SQL

• Declarative Language: SQL is declarative, meaning users specify what data they want without detailing how to retrieve it. This simplifies database operations, allowing professionals to focus on data requirements rather than procedural details.

• Non-Procedural Nature: Unlike procedural languages, SQL commands do not require specifying control flow. This facilitates quicker learning and application for database tasks such as querying customer records in a SACCO.

• Portability: SQL commands are largely portable across different database systems, meaning that skills learned using MySQL or PostgreSQL can be applied in Microsoft SQL Server or Oracle environments with minimal adjustments.

• Set-Based Operations: SQL works on sets of data rather than individual records, enabling efficient processing of large datasets, such as generating reports from a county government’s population database.

• Extensibility: SQL supports extensions and procedural elements (like PL/SQL in Oracle), allowing developers to write complex logic while still leveraging standard querying capabilities.

3.1.2 Characteristics

SQL’s defining features enable it to serve as the backbone of data management in professional Kenyan contexts. These characteristics ensure that SQL remains versatile, powerful, and accessible to database administrators and developers.

Declarative Syntax

SQL focuses on what data is needed rather than how to obtain it, which means users write queries specifying the desired outcome. This contrasts with imperative programming languages and reduces the complexity of database interactions.

• This approach allows professionals in financial institutions like Equity Bank to write queries extracting customer transaction histories without managing underlying data retrieval processes.

• Declarative syntax improves productivity since less code is required to perform complex data operations.

Data Manipulation and Definition

SQL encompasses both Data Manipulation Language (DML) and Data Definition Language (DDL) components, allowing users to handle data content and database schema respectively.

• DML commands such as SELECT, INSERT, UPDATE, and DELETE enable modification and retrieval of data records in tables.

• DDL commands like CREATE, ALTER, and DROP allow database designers at universities to define or modify table structures and relationships.

Transaction Control

SQL supports transaction management to ensure data integrity during multi-step operations, essential in environments where consistency is critical.

• Commands such as BEGIN TRANSACTION, COMMIT, and ROLLBACK help maintain accurate records in scenarios like booking hotel reservations or processing loan applications at SACCOs.

• This capability ensures that either all changes are applied successfully or none, protecting against partial updates that could corrupt data.

Data Integrity Enforcement

SQL supports constraints such as primary keys, foreign keys, unique constraints, and checks to enforce data validity.

• For example, a county government database might use foreign key constraints to ensure that employee records always reference valid department entries.

• These features prevent erroneous data entry and maintain relational consistency across tables.

Security and Access Control

SQL includes commands to define user roles, privileges, and permissions, enabling fine-grained control over who can access or modify data.

• In banking systems, this ensures that tellers can only access customer account information relevant to their duties, while auditors have read-only access for compliance checks.

• SQL’s security features are fundamental for protecting sensitive data in compliance with Kenya’s Data Protection Act.

3.1.3 Components

SQL’s functionality is organized into distinct components that collectively manage data definition, manipulation, control, and querying. Mastery of these components enables computer science professionals to design robust database applications.

Data Definition Language (DDL)

DDL commands define and modify database schema objects such as tables, indexes, and views.

• CREATE: Defines new tables or database objects. For instance, a developer at a retail business might create a table to store product inventory details.

• ALTER: Modifies existing table structures, such as adding new columns or changing data types, which is common during system upgrades.

• DROP: Deletes tables or objects that are no longer needed, helping maintain database hygiene.

• TRUNCATE: Removes all rows from a table quickly without logging individual row deletions, useful for clearing temporary data in test environments.

• COMMENT: Adds descriptive text to database objects, aiding documentation and maintenance.

Data Manipulation Language (DML)

DML commands handle the management of data within tables.

• SELECT: Retrieves data from one or more tables, supporting filtering, sorting, and aggregation. For example, a university registrar uses SELECT queries to generate student grade reports.

• INSERT: Adds new rows of data into tables, such as entering new customer records in a SACCO database.

• UPDATE: Modifies existing data, like changing the status of loan applications after approval.

• DELETE: Removes records, for instance deleting outdated inventory entries in a supermarket database.

• MERGE: Combines data from source tables into a target table, used in data warehousing scenarios.

Data Control Language (DCL)

DCL commands regulate access to data and database operations.

• GRANT: Provides specific privileges to users or roles, such as allowing a bank clerk to update customer profiles.

• REVOKE: Removes previously granted permissions, essential for maintaining security when users change roles.

• DENY: Explicitly denies permissions, overriding granted privileges in some database systems.

• These commands help institutions like county governments comply with data access policies.

Transaction Control Language (TCL)

TCL commands manage database transactions to ensure data consistency and integrity.

• BEGIN TRANSACTION: Marks the start of a transaction block where multiple operations are treated as a single unit.

• COMMIT: Saves all changes made during the transaction permanently to the database.

• ROLLBACK: Reverts all changes made during the transaction if errors occur.

• SAVEPOINT: Creates intermediate points within a transaction for partial rollbacks.

• These commands are vital in financial applications such as loan disbursement systems to prevent data corruption.

Query Components and Clauses

SQL queries are constructed using various clauses that specify data retrieval conditions and manipulations.

• FROM: Specifies the tables to query from.

• WHERE: Filters rows based on conditions.

• GROUP BY: Aggregates data by one or more columns.

• HAVING: Filters groups based on aggregate conditions.

• ORDER BY: Sorts the result set.

• For example, a data analyst at a county health department might write a query using these clauses to analyze disease incidence by region and month.

Chapter Summary

This chapter introduced Structured Query Language, commonly known as SQL, defining it as the standard language used for managing and manipulating relational databases. The discussion covered its key characteristics and essential components that enable users to define, manipulate, and control data effectively. Data definition queries were explored in detail, focusing on the CREATE, DROP, and ALTER statements, which are fundamental for creating tables, removing them, and modifying their structure. The chapter provided a demonstration of the CREATE TABLE statement and explained various table constraints such as PRIMARY KEY, FOREIGN KEY, NOT NULL, CHECK, UNIQUE, and DEFAULT, which enforce data integrity. Editing table schemas using the ALTER statement was examined through examples of adding, dropping, and modifying attributes. The DROP TABLE statement was also covered as a means to delete entire tables from the database. Data manipulation query statements including INSERT, SELECT, UPDATE, and DELETE were explained with practical detail on how to retrieve, insert, update, and delete records. Finally, the concept of SQL joins was introduced, defining what joins are and describing their types, followed by exercises on creating and querying databases from validated ER models and constructing simple join queries.