Unit 5: Introduction to SQL

INT322 — Computing System And Technologies 10 min read

I. Foundations of SQL

SQL—Structured Query Language—is the standard language for defining, retrieving, modifying, and controlling data in relational database management systems (developed at IBM in the 1970s; standardized by ANSI in 1986). It operates mainly on sets of rows rather than processing one record at a time.

A. Introduction to SQL

SQL provides declarative statements through which users specify the required result rather than the step-by-step procedure for producing it.

Relational structure: Data is organized into tables called relations.
- A row or tuple represents one record.
- A column or attribute represents one property.
- A schema defines table names, columns, data types, keys, and constraints.
Declarative nature: A query such as SELECT name FROM Student states what data is required; the DBMS chooses an execution strategy.
Core operations: SQL supports schema definition, data manipulation, access control, transaction management, filtering, grouping, sorting, and joining.
Common data types: Frequently used types include INTEGER, DECIMAL(p,s), CHAR(n), VARCHAR(n), DATE, and BOOLEAN; exact availability varies among database systems.
Basic query form:

SQL

SELECT column_list
FROM table_name
WHERE condition;

SQL conventions: Keywords are normally case-insensitive, string values use single quotation marks, and statements conventionally end with a semicolon.
NULL value: NULL means missing, unknown, or inapplicable data; it is tested with IS NULL, not = NULL.

II. SQL Command Categories — Managing Structure, Data, Access, and Transactions

SQL commands are classified by purpose: DDL defines database objects, DML works with stored rows, DCL manages privileges, and TCL controls transactions.

A. DDL commands

Data Definition Language commands create and alter the logical structure of a database.

CREATE: Creates an object such as a database, table, view, or index.

SQL

CREATE TABLE Student (
    student_id INTEGER PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    marks DECIMAL(5,2)
);

ALTER: Changes the definition of an existing object; for example, ALTER TABLE Student ADD email VARCHAR(100);.
DROP: Removes an object and its definition, as in DROP TABLE Student;; dependent data may also be lost.
TRUNCATE: Removes all rows efficiently while retaining the table structure: TRUNCATE TABLE Student;.
RENAME: Changes an object's name where supported, for example ALTER TABLE Student RENAME TO Learner;.
Transaction behavior: Some DBMSs perform implicit commits around DDL statements, so rollback behavior is product-dependent.

B. DML commands

Data Manipulation Language commands retrieve and change the rows stored in tables.

SELECT: Retrieves selected data: SELECT name, marks FROM Student WHERE marks >= 60;.
INSERT: Adds rows while matching values to columns.

SQL

INSERT INTO Student (student_id, name, marks)
VALUES (101, 'Asha', 84.50);

UPDATE: Modifies rows satisfying a condition: UPDATE Student SET marks = 86 WHERE student_id = 101;.
DELETE: Removes selected rows: DELETE FROM Student WHERE student_id = 101;.
Safety of conditions: Omitting WHERE from UPDATE or DELETE affects every row in the table.
Constraint enforcement: DML operations must satisfy applicable primary-key, foreign-key, uniqueness, and check constraints.

C. DCL commands

Data Control Language commands regulate authorization and database access.

GRANT: Assigns privileges to a user or role: GRANT SELECT, INSERT ON Student TO teacher_role;.
REVOKE: Withdraws previously assigned privileges: REVOKE INSERT ON Student FROM teacher_role;.
Privilege scope: Permissions may apply to database objects or actions such as SELECT, UPDATE, EXECUTE, and schema creation.
Role-based control: Privileges can be granted to roles and roles assigned to users, simplifying administration.
Security principle: The principle of least privilege gives each user only the permissions needed for assigned work.

D. TCL commands

Transaction Control Language commands manage a transaction—a logical unit containing one or more database operations.

COMMIT: Permanently accepts all changes in the current transaction.
ROLLBACK: Cancels uncommitted changes and restores the earlier consistent state.
SAVEPOINT: Marks an intermediate point to which part of a transaction can be rolled back.
Example sequence:

SQL

START TRANSACTION;
UPDATE Account SET balance = balance - 500 WHERE account_id = 1;
SAVEPOINT debit_complete;
UPDATE Account SET balance = balance + 500 WHERE account_id = 2;
COMMIT;

Atomicity: A correct transfer commits both updates or rolls both back; this prevents money from being removed without being credited.
Dialect variation: Transaction-start syntax and automatic-commit settings vary across DBMS products.

III. Relational Identification and Integrity

Keys identify records, establish relationships, and prevent inconsistent or duplicate data.

A. Keys and their types

A key is one column or a combination of columns used to identify rows or connect related tables.

Super key: Any attribute set that uniquely identifies a row; {student_id} and {student_id, name} may both be super keys.
Candidate key: A minimal super key, meaning no included column can be removed without losing uniqueness.
Primary key: The chosen candidate key; it must be unique and cannot contain NULL.
Alternate key: A candidate key not selected as the primary key, such as a unique email address when student_id is primary.
Composite key: A key containing multiple columns; (student_id, course_id) can identify one enrollment.
Foreign key: A column set referring to a candidate—usually primary—key in another or the same table.

SQL

CREATE TABLE Enrollment (
    student_id INTEGER,
    course_id INTEGER,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES Student(student_id)
);

Unique key: A UNIQUE constraint prevents duplicate values; treatment of NULL can differ by DBMS.
Natural and surrogate keys:
1. Natural key: Has business meaning, such as a registration number.
2. Surrogate key: Is artificially generated, such as an identity integer.
Referential integrity: A foreign-key value must match a referenced key value or be NULL when nullability permits it.

IV. Aggregate Data Analysis

Aggregate functions calculate one result from a set of rows and are frequently combined with grouping.

A. Aggregate functions: MIN, MAX, SUM, AVG and COUNT

Aggregate functions summarize values selected from one or more records.

MIN(expression): Returns the smallest non-NULL value, such as MIN(marks).
MAX(expression): Returns the largest non-NULL value, such as MAX(marks).
SUM(expression): Adds non-NULL numeric values; SUM(salary) gives total salary.
AVG(expression): Computes the arithmetic mean of non-NULL numeric values: total of known values divided by their count.
COUNT(*): Counts rows, including rows containing NULL values.
COUNT(column): Counts only rows in which that column is not NULL.
DISTINCT option: COUNT(DISTINCT department_id) counts different non-NULL department identifiers.
Combined example:

SQL

SELECT
    MIN(marks) AS lowest,
    MAX(marks) AS highest,
    SUM(marks) AS total,
    AVG(marks) AS average,
    COUNT(*) AS students
FROM Student;

Empty input: COUNT returns zero for an empty set, while the other listed aggregates generally return NULL.

V. SQL Joins — Combining Related Rows

A join combines rows from two table references according to a matching rule; aliases make repeated or lengthy table names easier to distinguish.

A. Self join

A self join joins a table to another reference to itself, usually to represent hierarchical or same-table relationships.

Aliases: Separate aliases are essential because both references have identical column names.
Hierarchy example:

SQL

SELECT e.name AS employee, m.name AS manager
FROM Employee AS e
LEFT JOIN Employee AS m
    ON e.manager_id = m.employee_id;

Interpretation: e represents an employee and m represents that employee's manager.
Join choice: LEFT JOIN retains top-level employees whose manager_id is NULL.

B. Equi join

An equi join matches rows using the equality operator between related columns.

Condition: Its defining predicate has the form table1.column = table2.column.
Example: Student.department_id = Department.department_id connects each student to the corresponding department.
Result columns: Both matching columns may appear unless the query selects only one or uses USING where supported.
Relationship to inner joins: An equi join is commonly an inner join, but equality can also be used as the condition of an outer join.

C. Inner join

An inner join returns only row combinations satisfying its join condition.

Syntax:

SQL

SELECT s.name, d.department_name
FROM Student AS s
INNER JOIN Department AS d
    ON s.department_id = d.department_id;

Excluded rows: Students without a matching department and departments without matching students do not appear.
Conditions: Inner joins may use equality or non-equality predicates; therefore, not every inner join is necessarily an equi join.

D. Outer join

An outer join preserves unmatched rows from one or both participating table references.

LEFT OUTER JOIN: Keeps every row from the left table and fills unmatched right-side columns with NULL.
RIGHT OUTER JOIN: Keeps every row from the right table and supplies NULL for unmatched left-side columns.
FULL OUTER JOIN: Keeps matched rows plus unmatched rows from both sides; it is not supported directly by every DBMS.
Filtering caution: A WHERE condition on a nullable outer-side column can unintentionally remove unmatched rows and make the result behave like an inner join.

E. Cross join

A cross join produces the Cartesian product of two tables without requiring a matching condition.

Row count: If table A has (m) rows and table B has (n) rows, the result has (m \times n) rows.
Example:

SQL

SELECT c.colour_name, s.size_name
FROM Colour AS c
CROSS JOIN Size AS s;

Application: Three colours combined with four sizes produce (3 \times 4 = 12) possible variants.
Limitation: Large inputs can generate enormous results, so cross joins must be used deliberately.

VI. Grouping, Filtering, and Ordering Query Results

SQL clauses transform a result in a logical sequence: rows are filtered, grouped, filtered by group, projected, and finally sorted.

A. GROUP BY clause

The GROUP BY clause partitions rows sharing specified values so that aggregates can be calculated for each group.

Grouping rule: Selected columns that are not aggregated generally must appear in GROUP BY.
Example:

SQL

SELECT department_id, AVG(marks) AS average_marks
FROM Student
GROUP BY department_id;

Result meaning: The query returns one row per department identifier rather than one row per student.
Multiple columns: GROUP BY department_id, course_id creates one group for each distinct department-course combination.

B. HAVING clause

The HAVING clause filters completed groups, normally by applying a condition to an aggregate result.

Group filter:

SQL

SELECT department_id, COUNT(*) AS student_count
FROM Student
GROUP BY department_id
HAVING COUNT(*) >= 10;

Contrast with WHERE:
1. WHERE: Filters individual rows before grouping and cannot normally contain aggregate conditions.
2. HAVING: Filters groups after aggregates have been calculated.
Efficiency: Conditions applying to individual rows should ordinarily be placed in WHERE so fewer rows enter the grouping stage.

C. ORDER BY clause

The ORDER BY clause sorts the final query result by one or more expressions.

Direction: ASC means ascending and is generally the default; DESC means descending.
Multiple criteria:

SQL

SELECT name, marks
FROM Student
ORDER BY marks DESC, name ASC;

Interpretation: Students are ordered from highest to lowest marks; equal marks are then ordered alphabetically by name.
Aliases and positions: Many DBMSs permit output aliases in ORDER BY; positional ordering exists but is less maintainable.
NULL placement: The default position of NULL values varies by DBMS, while some systems support explicit NULLS FIRST or NULLS LAST.