📘 Page 1: Chapter Outline – Intermediate SQL

This chapter expands upon basic SQL concepts and introduces advanced features that are
essential for real-world database applications. The topics include:

●​ Join Expressions: Techniques for combining data from multiple tables.

●​ Views: Virtual tables that simplify access and enhance security.
●​ Transactions: Ensuring data consistency and reliability.
●​ Integrity Constraints: Rules that enforce data validity.
●​ SQL Data Types and Schemas: Structuring and typing data.
●​ Index Definition: Enhancing query performance.
●​ Authorization: Controlling access to data.

Each topic builds upon foundational SQL knowledge and introduces mechanisms for managing
complex data interactions, enforcing business rules, and optimizing performance.

📘 Page 2: Joined Relations – Concept and Utility

Join operations are fundamental in relational databases. They allow combining rows from two or
more tables based on related columns. The result is a new relation that merges relevant data.
Joins are typically used in the FROM clause of SQL queries and are essential for querying
normalized databases.

There are three primary types of joins:

●​ Natural Join: Automatically matches columns with the same name and eliminates
duplicates.
●​ Inner Join: Returns only matching rows based on a specified condition.
●​ Outer Join: Includes unmatched rows from one or both tables, using NULL for missing
values.

Understanding joins is crucial for writing efficient queries that span multiple tables and for
reconstructing meaningful relationships from normalized data.

📘 Page 3: Schema of University Database

This schema models a university's operations and includes entities such as:

●​ Classroom: Physical locations with capacity.

●​ Department: Academic units with budgets.
●​ Course: Offerings with titles and credits.
●​ Instructor: Faculty members with salaries.
●​ Section: Specific offerings of courses.
●​ Teaches: Mapping instructors to sections.
●​ Student: Learners with total credits.
●​ Takes: Records of student enrollments.
●​ Advisor: Student-instructor relationships.
●​ Time Slot: Scheduling details.
●​ Prereq: Course prerequisites.

This schema is used throughout the chapter to demonstrate SQL operations and relational
concepts. It reflects a normalized structure with foreign keys linking related entities.

📘 Page 4–5: Natural Join in SQL

A natural join merges tables based on all common attributes. It simplifies queries by
automatically detecting matching columns and removing duplicates. For example:

SELECT name, course_id FROM student NATURAL JOIN takes;

This query retrieves student names and the courses they are enrolled in. Natural joins are
concise but must be used carefully to avoid unintended matches on unrelated columns with the
same name.

Multiple natural joins can be chained together in the FROM clause, allowing complex queries
across several tables. However, clarity and correctness must be maintained, especially when
attribute names overlap.

📘 Page 6–8: Student and Takes Relations – Natural Join

Output
These pages show the result of a natural join between student and takes. The output
includes:

●​ Student ID, name, department, total credits

 ●​ Course ID, section ID, semester, year, grade

This merged view provides a comprehensive picture of student enrollments and performance. It
demonstrates how natural joins can reconstruct meaningful relationships from normalized data.
The example also highlights the importance of consistent attribute naming and schema design.

📘 Page 9–10: Schema Diagram and Schema Recap

The schema diagram visually represents relationships among university entities. It shows how
tables are connected via foreign keys and shared attributes. This aids in understanding join
paths and query construction.

The schema recap lists all relations and their attributes, reinforcing the structure and enabling
students to visualize how data flows across the system. This is especially useful for designing
queries and understanding normalization.

📘 Page 11: Natural Join – Pitfalls and Correct Usage

Natural joins can lead to incorrect results if unrelated attributes share the same name. For
example, joining student, takes, and course using natural joins may unintentionally match
dept_name across all three tables, excluding valid records.

Correct usage involves specifying join conditions explicitly when necessary:

SELECT name, title FROM student NATURAL JOIN takes, course WHERE takes.course_id =
course.course_id;

This ensures accurate results and avoids unintended filtering. Understanding these nuances is
critical for writing reliable SQL queries.

📘 Page 12–18: Outer Joins – Concepts and Examples

Outer joins extend the join operation to include unmatched rows:

●​ Left Outer Join: Includes all rows from the left table.
●​ Right Outer Join: Includes all rows from the right table.
●​ Full Outer Join: Includes all rows from both tables.
These joins use NULL to fill in missing values and preserve data that would otherwise be lost in
an inner join. Examples using course and prereq illustrate how outer joins retain course
information even when prerequisites are missing.

Understanding outer joins is essential for data completeness and reporting, especially when
dealing with optional relationships.

📘 Page 19–22: Join Types and Conditions

Join operations are defined by:

●​ Join Condition: Specifies how tuples match.

●​ Join Type: Determines how unmatched tuples are handled.

SQL supports various syntaxes:

●​ NATURAL JOIN
●​ JOIN ON <predicate>
●​ JOIN USING (columns)

Examples show how different join types affect query results. Choosing the right join type and
condition is crucial for accurate data retrieval and analysis.

📘 Page 23–28: Views – Definition and Usage

A view is a virtual table created from a query. It allows users to access specific data without
exposing the entire schema. Views enhance security, simplify queries, and support abstraction.

Creating a view:

CREATE VIEW faculty AS SELECT ID, name, dept_name FROM instructor;

Using a view:

SELECT name FROM faculty WHERE dept_name = 'Biology';

Views can be defined using other views, creating dependency chains. View expansion replaces
view names with their defining queries, enabling query execution. Views are powerful tools for
modular design and access control.

📘 Page 29–31: Materialized Views and Updates

Materialized views are physically stored versions of views. They improve performance but
require maintenance when underlying data changes.

Updating data through views can be complex. SQL allows updates only on simple views with
one base table and no aggregates. Complex views may reject updates or produce unintended
results.

Understanding view update rules is essential for designing maintainable and secure database
applications.

📘 Page 32–34: View Update Challenges

Some updates cannot be translated uniquely due to ambiguity or missing data. For example,
inserting into a view that joins instructor and department may fail if the department is not
clearly identified.

SQL implementations vary in their support for view updates. Generally, updates are allowed only
when they can be mapped unambiguously to base tables. This reinforces the importance of
careful view design.

📘 Page 35–39: Transactions – ACID Properties

A transaction is a sequence of operations that form a logical unit of work. SQL transactions
begin implicitly and end with:

●​ COMMIT: Makes changes permanent.

●​ ROLLBACK: Undoes changes.

ACID properties ensure reliability:

●​ Atomicity: All or nothing.

 ●​ Consistency: Maintains valid state.
●​ Isolation: Prevents interference.
●​ Durability: Changes persist after failure.

Examples like money transfers illustrate how transactions maintain data integrity even in the
face of errors or concurrent access.

📘 Page 40–41: Integrity – Concept and Types

Integrity ensures data remains accurate and consistent. Types include:

●​ Entity Integrity: Unique identifiers (primary keys).

●​ Referential Integrity: Valid foreign key references.
●​ Domain Integrity: Valid attribute values.
●​ User-Defined Integrity: Custom business rules.

Constraints enforce these rules and prevent invalid data entry. They are essential for
maintaining trustworthiness and correctness in databases.

📘 Page 42–47: Integrity Constraints – SQL Syntax

Constraints can be added using CREATE TABLE or ALTER TABLE. Types include:

●​ NOT NULL: Prevents missing values.

●​ UNIQUE: Ensures distinct values.
●​ CHECK: Validates conditions.
●​ PRIMARY KEY: Uniquely identifies tuples.
●​ FOREIGN KEY: Enforces referential integrity.

Examples show how to define constraints and ensure data validity. These mechanisms are
foundational for robust database design.

📘 Page 48–53: Referential Integrity and Complex Checks

Referential integrity ensures that foreign keys reference existing values. SQL allows cascading
actions:
 ●​ ON DELETE CASCADE
●​ ON UPDATE CASCADE
●​ SET NULL
●​ SET DEFAULT

Complex checks can include subqueries, validating relationships across tables. These
constraints enforce business logic and prevent orphaned records.

📘 Page 54–56: Assertions – Global Constraints

Assertions are global constraints that apply to the entire database. They are defined using:

CREATE ASSERTION <name> CHECK (<predicate>);

Examples include:

●​ Ensuring total credits match completed courses.

●​ Preventing instructors from teaching in multiple rooms at the same time.

Assertions provide powerful validation but are not widely supported due to performance
concerns.

📘 Page 57: Built-in Data Types in SQL

SQL provides a variety of built-in data types to handle different kinds of information. These types
are essential for defining the structure of tables and ensuring that data is stored in a consistent
and meaningful way.

●​ DATE: Stores calendar dates in the format YYYY-MM-DD. Example: date

'2005-07-27'.
●​ TIME: Stores time of day, including hours, minutes, and seconds. It can also include
fractional seconds. Example: time '09:00:30.75'.
●​ TIMESTAMP: Combines date and time into a single value. Useful for logging events.
Example: timestamp '2005-07-27 09:00:30.75'.
●​ INTERVAL: Represents a duration of time. Example: interval '1' day.
These types allow for precise handling of temporal data, which is critical in applications like
scheduling, logging, and financial transactions. SQL also supports arithmetic operations on
these types, such as subtracting two timestamps to get an interval.

📘 Page 58: Default Values in SQL

Default values simplify data entry and ensure consistency. When a value is not explicitly
provided during insertion, the default is automatically used.

Example:

CREATE TABLE student (

ID VARCHAR(5),

name VARCHAR(20) NOT NULL,

dept_name VARCHAR(20),

tot_cred NUMERIC(3,0) DEFAULT 0,

PRIMARY KEY (ID)

);

Here, tot_cred will default to 0 if not specified. This is particularly useful for attributes like
counters, flags, or status indicators. Defaults reduce errors and streamline data entry processes.

📘 Page 59: Large-Object Types (LOBs)

SQL supports large-object types for storing multimedia and other large data items:

●​ BLOB (Binary Large Object): Stores binary data such as images, videos, or audio files.
The database does not interpret the content.
●​ CLOB (Character Large Object): Stores large text data such as documents or reviews.

Examples:

●​ book_review CLOB(10KB)
 ●​ image BLOB(10MB)
●​ movie BLOB(2GB)

When queried, these objects return pointers rather than the actual data, allowing efficient
handling of large content. LOBs are essential for applications involving digital media, document
management, and archival systems.

📘 Page 60: User-Defined Types

SQL allows users to define custom data types using the CREATE TYPE construct. This
enhances modularity and reusability.

Example:

CREATE TYPE Dollars AS NUMERIC(12,2);

CREATE TABLE department (

dept_name VARCHAR(20),

building VARCHAR(15),

budget Dollars

);

User-defined types encapsulate domain-specific constraints and formatting, making schemas

more expressive and easier to maintain. They are particularly useful in large systems with
recurring data patterns.

📘 Page 61: Domains in SQL

Domains are similar to user-defined types but include constraints directly. They are created
using the CREATE DOMAIN statement.

Example:

CREATE DOMAIN degree_level VARCHAR(10)

 CONSTRAINT degree_level_test CHECK (VALUE IN ('Bachelors', 'Masters', 'Doctorate'));

Domains promote consistency across tables and simplify schema definitions. They are ideal for
enforcing business rules and standardizing attribute values.

📘 Page 62–63: Index Creation in SQL

Indexes are data structures that improve query performance by allowing fast access to rows
based on column values. Without indexes, the database must scan every row, which is
inefficient for large tables.

Creating an index:

CREATE INDEX studentID_index ON student(ID);

This index speeds up queries like:

SELECT * FROM student WHERE ID = '12345';

Indexes are especially useful for columns used in WHERE, JOIN, and ORDER BY clauses. They
can be dropped using:

DROP INDEX studentID_index;

Understanding indexing is crucial for optimizing database performance.

📘 Page 64–65: Authorization – Basic Privileges

SQL provides fine-grained control over who can access or modify data. Privileges include:

●​ SELECT: Read data.

●​ INSERT: Add new rows.
●​ UPDATE: Modify existing rows.
 ●​ DELETE: Remove rows.

These privileges can be granted using:

GRANT SELECT ON department TO Amita, Satoshi;

Revoking privileges:

REVOKE SELECT ON department FROM Amita, Satoshi;

Authorization ensures data security and integrity by restricting access based on user roles and
responsibilities.

📘 Page 66–69: Advanced Authorization and Revocation

SQL supports advanced authorization features:

●​ GRANT OPTION: Allows users to pass privileges to others.

●​ CASCADE: Automatically revokes dependent privileges.
●​ RESTRICT: Prevents revocation if privileges are in use.

Example:

GRANT SELECT ON department TO Amit WITH GRANT OPTION;

REVOKE SELECT ON department FROM Amit CASCADE;

These mechanisms support complex access control scenarios, such as hierarchical roles and
delegated authority. They are vital for enterprise-level database management.

📘 Page 70–71: Roles in SQL

Roles simplify authorization by grouping privileges and assigning them to users collectively.

Creating a role:
CREATE ROLE instructor;

GRANT instructor TO Amit;

Privileges can be granted to roles:

GRANT SELECT ON takes TO instructor;

Roles can be nested:

CREATE ROLE teaching_assistant;

GRANT teaching_assistant TO instructor;

This structure supports scalable and maintainable access control, especially in large
organizations.

📘 Page 72–73: Authorization on Views and References

Authorization on views is independent of base tables. A user may have access to a view but not
the underlying tables. This allows for secure data abstraction.

Example:

CREATE VIEW geo_instructor AS SELECT * FROM instructor WHERE dept_name = 'Geology';

GRANT SELECT ON geo_instructor TO geo_staff;

SQL also supports REFERENCES privilege for creating foreign keys:

GRANT REFERENCES(dept_name) ON department TO Mariano;

These features enhance security and enforce data integrity across relational structures.
📘 Page 74: Transfer of Privileges
Privileges can be transferred with the GRANT OPTION. Revocation can be controlled using
CASCADE or RESTRICT.

Example:

GRANT SELECT ON department TO Amit WITH GRANT OPTION;

REVOKE SELECT ON department FROM Amit RESTRICT;

This ensures that privilege management aligns with organizational policies and prevents
unintended access loss.