ch5

The chapter covers various advanced SQL database concepts focusing on accessing SQL from a programming language, functions and procedures, triggers, recursive queries, advanced aggregation features, and Online Analytical Processing (OLAP).

A standard way to connect programs with databases using API (Application Programming Interface).
JDBC (Java Database Connectivity): Java API for querying and updating data. Supports:
- Opening connections, creating statement objects, executing queries, and handling exceptions.
ODBC (Open Database Connectivity): Standard API to connect different programming environments (C, C++, C#, etc.) to databases.
- Applications like GUIs and spreadsheets utilize ODBC.
Embedded SQL: Allows SQL statements to be integrated within host programming languages like C, C++, and Java.

Embedded SQL starts with the declaration of SQL sections:
- EXEC SQL <SQL statement>;
- Must connect to the database before executing SQL statements.
- Variables from the host language can be included, prefixed with a colon (e.g., :credit_amount).

Use DECLARE CURSOR for writing embedded SQL queries. Operations include declaring, opening, fetching tuples, and closing the cursor.
Example of using cursor to fetch data:
- EXEC SQL FETCH c INTO :si, :sn;

SQL:1999 enables creating functions that can be written in SQL or external programming languages (e.g., C, Java).
Functions can return relations and support complex data types.

Creating a simple function to count instructors in a department:
- `CREATE FUNCTION dept_count(dept_name VARCHAR(20)) RETURNS INTEGER;
Can also be written as a procedure using CREATE PROCEDURE statement.

Procedures can be invoked using the CALL statement and handle parameters like IN, OUT and INOUT in their declarations.

Automatic response to modifications in a database (INSERT, UPDATE, DELETE).
Trigger structure specifies the conditions for execution and the actions to perform.
Example: Triggers can update credit hours after a grade change for a student.

Introduced in SQL:1999, allowing the definition of recursive views that enable complex queries.
Example of retrieving course prerequisites:
- Can leverage recursive definitions for complex hierarchical queries.

Ranking can be efficiently queried using SQL built-in functions like RANK(), DENSE_RANK(), and others.
Example:
- SELECT ID, RANK() OVER (ORDER BY GPA DESC) AS s_rank FROM student_grades;

OLAP allows interactive data analysis and presents data in a multidimensional format.
- CUBE and ROLLUP operations for generating aggregations at multiple levels of granularity.
Example usage for calculating aggregate values with grouping:
- SELECT item_name, SUM(number) FROM sales GROUP BY CUBE(item_name, color);

Data Cube: Generalization of cross-tabulation allowing for multiple dimensions.
Hierarchical views let you summarize data at different levels of detail.

Advanced SQL concepts facilitate efficient data manipulation and querying in modern database systems, providing flexibility through programming integration, procedural extensions, triggers, and analytical capabilities for complex queries.