Structured Query Language (SQL)

SQL provides a variety of built-in data types to store different kinds of values. Each data type has its own characteristics and constraints. The most commonly used types include:

• char(n): A fixed-length character string with a specified length n. If the stored string is shorter than n, extra spaces are appended to fill the length. Full form: character(n).

• varchar(n): A variable-length character string with a maximum length n. Only the actual string is stored, without extra spaces. Full form: character varying(n).

• int / integer: Stores integer numbers. The exact range is machine-dependent.

• smallint: Stores smaller integer numbers, a subset of int.

• numeric(p,d): A fixed-point number with total precision p (number of digits) and scale d (number of digits to the right of the decimal point). Example: numeric(3,1) allows storing 44.5 exactly but not 444.5 or 0.32.

• real: Single-precision floating-point number with machine-dependent precision.

• double precision: Double-precision floating-point number with higher precision than real.

• float(n): Floating-point number with at least n digits of precision.

• nvarchar(n): Variable-length Unicode string supporting multilingual characters.

Null values: All SQL data types can include the special NULL value, which represents missing or unknown data. Null values indicate that a value may exist but is currently unknown, or that it may not exist at all. Certain columns can be restricted to disallow NULL.

An SQL relation (table) is defined using the CREATE TABLE command.

A relation consists of:

• A name (relation name)

• A set of attributes (columns), each with a name and domain (data type)

• Optional integrity constraints (primary key, foreign key, NOT NULL, unique, etc.)

General Syntax:

CREATE TABLE relation_name (
    attribute1 datatype [constraint],
    attribute2 datatype [constraint],
    ...,
    [table-level constraints]
);

Notes:

• Semicolon at the end of a SQL statement is optional in many implementations.

• Newly created tables are initially empty; data is inserted later.

The INSERT statement is used to add tuples (rows) into a relation (table).

There are two common forms:

Form 1 — Insert with attribute list:

INSERT INTO table_name (A1, A2, ..., An)
VALUES (v1, v2, ..., vn);

Form 2 — Insert without attribute list (full tuple order):

INSERT INTO table_name
VALUES (v1, v2, ..., vn);

Key Ideas:

• Values must match the attribute data types.

• Order must match attribute order if column list is omitted.

• String values are written in quotes.

• Numeric values are written without quotes.

• Missing attributes get NULL (if allowed).

When foreign key constraints exist, tables must be populated in dependency order.

Rule: Insert into parent tables first, then child tables.

If table B has a foreign key referencing table A:

• Insert into table A first.

• Insert into table B next.

Otherwise, the DBMS rejects the insert due to referential integrity violation.

Typical Order Example:

1. department (parent)

2. course (references department)

3. section (references course)

4. enrollment (references section)

When a row in a parent table is deleted, the DBMS must ensure that no invalid references remain in related tables.

If another table contains a Foreign Key referencing the deleted row, the database must decide what to do.

When a referenced row is deleted, the DBMS can apply one of the following rules:

• RESTRICT (Default)

  Deletion is rejected if related rows exist.

  Fails if instructors belong to CS.

• CASCADE

  Deletes the referencing rows automatically.

  Deleting department → deletes its instructors.

• SET NULL

  Foreign key values in referencing table become NULL.

  Instructor still exists but department becomes unknown.

CREATE TABLE instructor (
    ID char(5) PRIMARY KEY,
    name varchar(20),
    dept_name varchar(20),
    FOREIGN KEY (dept_name)
        REFERENCES department(dept_name)
        ON DELETE CASCADE
        ON UPDATE SET NULL
);

Meaning

• ON DELETE CASCADE → deleting a department deletes its instructors.

• ON UPDATE SET NULL → if department name changes, instructor.dept_name becomes NULL.

• If no rule is specified → RESTRICT (default) prevents deletion/update.

The UPDATE command is used to modify existing values in a table.

Syntax

UPDATE table_name
SET column1 = value1,
    column2 = value2
WHERE condition;

Important Points

• Updates one or more columns in existing rows.

• WHERE clause specifies which rows to update.

• If WHERE is omitted, all rows in the table are updated.

• Multiple columns can be updated in a single statement.

• Can use expressions or values from other columns.

The ALTER TABLE command is used to modify the structure of an existing table. It can add new columns, modify column definitions, or remove columns.

The DROP command is used to permanently remove a database object such as a table. Both the table structure and all stored data are deleted.

The TRUNCATE command removes all rows from a table quickly. The table structure remains unchanged.

The asterisk symbol (*) in the SELECT clause denotes all attributes.

Two important forms exist:

1. SELECT *

SELECT *
FROM table_list;

Returns all attributes of the result relation formed by the FROM clause.

2. table_name.*

SELECT table_name.*
FROM table_list;

Returns all attributes of only that specific table.

Properties:

• Column order follows schema definition order.

• Includes all columns — even if later added.

• Common in quick inspection queries.

• Not recommended in production views (schema-change risk).

SQL provides the AS clause to rename attributes (columns) and relations (tables) within the result of a query. This is called the rename operation.

Renaming is required because:

• Different relations may contain attributes with the same name.

• Arithmetic expressions in SELECT do not have default column names.

• Derived columns need meaningful labels.

• Long table names should be shortened for readability.

• Self-joins require multiple logical copies of the same table.

Renaming does not change the base table — it only affects the query result.

Concept of Relational Operators

Relational operators (also known as Comparison Operators) are used in SQL to compare two expressions. When a comparison is made, the result is a Boolean value: either TRUE, FALSE, or UNKNOWN (in the case of NULL values). These operators are the backbone of data filtering in the WHERE clause.

The Standard Relational Operators are:

• = : Equal to

• <> or != : Not equal to

• > : Greater than

• < : Less than

• >= : Greater than or equal to

• <= : Less than or equal to

SQL supports arithmetic operations in the SELECT clause:

• + addition

• - subtraction

• * multiplication

• / division

These operations can be applied to numeric attributes or constants.

SELECT salary * 1.1 AS new_salary, salary + 500 AS incremented_salary
FROM employee;

Explanation: The query shows salary after 10% increase and after adding 500.

SQL represents strings using single quotes.

Examples of string literals:

• 'Computer'

• 'Database Systems'

• 'CS101'

If a single quote appears inside a string, it must be written twice.

Example:

• Text: It's correct

• SQL string: 'It”s correct'

String comparison using equality (=) is defined as case sensitive in the SQL standard. Thus:

• 'data' = 'DATA' → false (standard behavior)

However, some systems (like MySQL default collations) may treat string comparison as case-insensitive unless configured otherwise.

SQL supports pattern matching using the LIKE operator.

Special pattern characters:

• % → matches any substring (zero or more characters)

• _ → matches exactly one character

Pattern matching is case sensitive in standard SQL, but may be case-insensitive in some systems.

Forms:

column LIKE pattern
column NOT LIKE pattern

Examples of patterns:

• 'Data

• '

• '___' → exactly 3 characters

• '__

In SQL aggregation, NULL values are treated specially.

Rule: All aggregate functions ignore NULL values in their input, except COUNT(*).

This prevents unknown values from corrupting aggregate results.

GROUP BY is used to divide tuples into groups based on one or more attributes, and then apply aggregate functions on each group separately.

Instead of one aggregate result for the whole table, we get one aggregate result per group.

Typical use: summaries per department, per course, per year, etc.

SELECT group_column, AGG_FUNC(column)
FROM table
WHERE row_condition
GROUP BY group_column;

Each distinct value of group_column forms one group.

HAVING filters groups after aggregation.

WHERE filters rows before grouping.

Key difference:

• WHERE → cannot use aggregates

• HAVING → can use aggregates

• Selecting non-grouped, non-aggregated column

• Using aggregate in WHERE

• Forgetting that WHERE runs before grouping

• Confusing HAVING with WHERE

Rule: Row filter → WHERE
Group filter → HAVING

When grouping:

• All NULL values form one group

• Aggregates inside the group still ignore NULL inputs

• Unlike comparisons, GROUP BY treats NULL = NULL for grouping purposes.

So:

GROUP BY dept

All rows with dept = NULL go into one group.

Many practical SQL queries need data from more than one relation. Such queries are called multi-relation queries. SQL allows combining multiple tables using the FROM clause and specifying matching conditions in the WHERE clause (or using JOIN syntax).

Core idea:

• Each relation listed in the FROM clause participates in query evaluation.

• SQL conceptually forms a Cartesian product of those relations.

• The WHERE clause filters only meaningful tuple combinations.

• The SELECT clause finally projects required attributes.

Typical business questions answered using multi-relation queries:

• Get employee names with department buildings.

• List students with course titles.

• Show orders with customer details.

• Find instructors with courses taught.

If multiple tables are listed in FROM without a matching condition, SQL generates a Cartesian product:

• Every tuple of table A pairs with every tuple of table B.

• Result size = $|A| \times |B|$

• Usually very large and rarely meaningful by itself.

• Cartesian Product Without WHERE Clause is called CROSS JOIN

• Join predicates in WHERE restrict combinations.

When a table is renamed in a query using AS, the new name is called a:

• correlation name

• table alias

• tuple variable

All mean the same concept: a temporary query-level name for a relation.

The INNER JOIN is the most fundamental join type in SQL. It selects records that have matching values in both tables being joined.

Key Characteristics:

• It returns only the intersection of two tables.

• If a row in Table A does not have a corresponding match in Table B, it will not appear in the result.

• It is used to combine related data stored in separate tables.

Venn Diagram Representation:

A NATURAL JOIN is a specialized type of join that automatically joins two tables based on all columns that have the same name and data type in both tables.

Key Characteristics:

• Implicit Join Condition: You do not use the ON or USING clause; SQL identifies matching columns automatically.

• Column Deduplication: Unlike a standard Join, the Natural Join automatically removes duplicate columns from the output.

• Risk Factor: If tables share common names like ID or Created_At that aren't intended for joining, it can lead to empty or incorrect results.

Venn Diagram Representation: Like an Inner Join, it represents the intersection, but specifically restricted by shared column names.

The LEFT JOIN (or LEFT OUTER JOIN) returns all records from the left table (Table A), and the matched records from the right table (Table B).

Key Characteristics:

• Persistence: Every row from the left table is guaranteed to appear at least once in the result.

• Handling Mismatches: If there is no match in the right table for a specific row, the result will contain NULL values for all columns of the right table.

• Directional: The order in which you list the tables matters significantly.

Venn Diagram Representation: The result is the entirety of Table A, including the overlapping section with Table B.

The RIGHT JOIN (or RIGHT OUTER JOIN) is the mirror image of the Left Join. It returns all records from the right table (Table B), and the matched records from the left table (Table A).

Key Characteristics:

• Persistence: Every row from the right table is guaranteed to appear in the result set.

• Handling Mismatches: If there is no matching row in the left table for a specific row in the right table, the result will contain NULL values for the columns belonging to the left table.

• Logic: It is essentially a Left Join with the table order reversed.

Venn Diagram Representation: The result covers the entirety of Table B, including the overlapping section where records match Table A.

The FULL JOIN returns all records when there is a match in either left (Table A) or right (Table B) table records. It essentially combines the results of both a Left and a Right Join.

Key Characteristics:

• Complete Visibility: It retains every row from both tables.

• NULL Padding: For rows where there is no match, NULL values are placed in the columns of the table that lacks the matching record.

• Compatibility Note: Some databases (like MySQL) do not support FULL JOIN directly and require a UNION of a Left and Right Join.

Venn Diagram Representation: The result is the union of both sets, covering all areas of both circles.

The UNION operator combines the results of two SELECT queries and returns tuples that appear in either result.

Syntax:

SELECT column_list
FROM table1
WHERE condition
UNION
SELECT column_list
FROM table2
WHERE condition;

Rules:

• Both queries must return the same number of columns.

• Corresponding columns must have compatible domains.

• Column names in output come from the first query.

• Duplicates are automatically removed.

• Parentheses around subqueries are optional (DB dependent).

The INTERSECT operator returns only those tuples that appear in both query results.

Syntax:

SELECT column_list
FROM table1
WHERE condition
INTERSECT
SELECT column_list
FROM table2
WHERE condition;

Rules:

• Both queries must return same number of columns.

• Corresponding columns must have compatible domains.

• Result column names come from first query.

• Duplicates are automatically removed.

• Output = set intersection of the two results.

The EXCEPT operator returns all tuples that appear in the first query result but not in the second.

It performs set difference.

Syntax:

SELECT column_list
FROM table1
WHERE condition
EXCEPT
SELECT column_list
FROM table2
WHERE condition;

Rules:

• Both queries must return same number of columns.

• Corresponding columns must have compatible domains.

• Result column names come from first query.

• Duplicate tuples are removed before difference.

• Output = tuples in query1 − tuples in query2.

Checks whether a value belongs to the result set of a subquery.

Syntax:

value IN (subquery)

True if value matches any element returned by subquery.

Used when subquery returns numeric or comparable values.

Form:

value > ANY (subquery)
value > ALL (subquery)

If subquery returns empty set:

• x $>$ ALL(empty) → TRUE

• x $>$ ANY(empty) → FALSE

Checks whether subquery returns at least one row.

EXISTS (subquery)

Returns TRUE if subquery result is non-empty.