Advanced SQLSQL المتقدمة
مقدمة الفصل
ماذا ستتعلم في هذا الفصل
- Accessing DBMS From a Programming Language
- Functions and Procedural
- Triggers
- Recursive Queries
- Advanced Aggregation Features
- OLAP
أقسام الفصل
- 1Accessing SQL from a programming language; ODBCالوصول إلى SQL من لغة برمجة وODBC
- 2JDBCJDBC
- 3Embedded SQLSQL المضمّنة
- 4Functions and proceduresالدوال والإجراءات
- 5Triggersالمشغّلات (Triggers)
- 6Recursive queriesالاستعلامات العودية
- 7Ranking and windowingالترتيب والنوافذ
- 8OLAP, cross-tabs and data cubesOLAP والجداول المتقاطعة ومكعبات البيانات
- 9Extended aggregation: CUBE and ROLLUPالتجميع الموسّع: CUBE وROLLUP
- 10OLAP operations and OLAP implementationعمليات OLAP وطرق تنفيذه
تلميح: استخدم أسهم لوحة المفاتيح للتنقل بين الشرائح.
Accessing SQL from a programming languageSQL من لغة برمجة
- Slide 4 (Accessing SQL from a Programming Language) draws the DBMS between the database and its three kinds of clients: developer, user and app.
Why a general-purpose language is needed (slide 5)
- Not all queries can be expressed in SQL — it does not provide the full expressive power of a general-purpose language.
- Non-declarative actions — printing a report, interacting with a user, sending query results to a GUI — cannot be done from within SQL.
An application program makes calls to (slide 6)
- 1
Connect with the database server
- 2
Send SQL commands to the database server
- 3
Fetch tuples of result one-by-one into program variables
Tools
- ODBC: C, C++, C#, Visual Basic
- ADO.NET (on top of ODBC)
- JDBC: Java
- Embedded SQL
ODBCODBC
Standard for an application program to communicate with a database server: open a connection with a database, send queries and updates, get back results. Applications such as GUI, spreadsheets, etc. can use ODBC.
JDBC: model and codeJDBC
A Java API for communicating with database systems supporting SQL; supports querying and updating data, retrieving query results, and metadata retrieval.
Model for communicating with the database (slide 8)
- 1
Open a connection
- 2
Create a "statement" object
- 3
Execute queries using the Statement object to send queries and fetch results
- 4
Exception mechanism to handle errors
Nulls and prepared statementsالقيم الفارغة والجمل المعدّة
JDBC
- Result fields can be read by column name, e.g. rset.getString("dept_name"), or by position.
- Dealing with null values: int a = rset.getInt("a"); if (rset.wasNull()) … "Got null value".
Always use preparedStatements while reading input from the user and adding it to a query. NEVER create a query by concatenating strings — what if name is "D'Souza"?
Metadata featuresالبيانات الوصفية
JDBC
ResultSet
- Holds the actual data (rows)
ResultSetMetaData
- Provides the structural data (metadata) about those records
- Structure, types and properties of the columns
- getColumnCount(), getColumnName(i), getColumnTypeName(i)
DatabaseMetaData (dbmd = conn.getMetaData())
- getColumns(…) — one row for each column (COLUMN_NAME, TYPE_NAME).
- getTables(…) — one row for each table (TABLE_NAME, TABLE_CAT, TABLE_TYPE).
- getPrimaryKeys("", "", tableName) — KEY_SEQ gives the position of the attribute in the primary key (needed when the key has multiple attributes).
- Arguments: null = all catalogs/schemas; "" = current catalog/schema; "%" = same meaning as the SQL like clause.
Transaction control in JDBCالتحكم في المعاملات
Each SQL statement is treated as a separate transaction: it is executed and committed automatically.
- setAutoCommit(false)
Turn off automatic commit for a connection: statements are not automatically committed — transactions are managed manually.
- conn.commit()
Save the changes to the database.
- conn.rollback()
Discard the changes made during the transaction.
- setAutoCommit(true)
Turn automatic commit back on.
Embedded SQLSQL المضمّنة
Including SQL statements directly within a program written in a high-level programming language like C, C++, COBOL, or Java.
Functions in SQLالدوال في SQL
Functions and procedures
- SQL:1999 introduced support for functions and procedures, written in SQL or in an external language (e.g., C, Java).
- SQL:1999 imperative constructs: loops, if-then-else, assignment.
Compound statement; may contain multiple SQL statements.
Indicates the variable-type that is returned (e.g., integer).
Specifies the values that are returned as the result of invoking the function.
SQL:2003 added functions that return a relation as a result: select * from table (instructor_of ('Music')).
Stored proceduresالإجراءات المخزّنة
Functions and procedures
A precompiled SQL code that can be saved and reused; can have parameters; provides code reusability, improved performance, and database security.
More than one function/procedure of the same name, as long as the number of arguments differ, or at least the types of the arguments differ.
Language constructs and external routinesالبنى اللغوية والروتينات الخارجية
Functions and procedures
Language constructs (slides 31–33)
- while … do … end while
- repeat … until … end repeat
- for r as <query> do … end for
- if-then-else
- case
Benefits
- More efficient for many operations
- More expressive power: computations that cannot be carried out in SQL
Drawbacks
- Code may need to be loaded and executed in the database system's address space
- Risk of accidental corruption of database structures
- Security risk: users access to unauthorized data
Dealing with security (slide 36)
- Sandbox techniques: a safe language like Java, which cannot be used to access/damage other parts of the database code.
- A separate process with no access to the database process' memory; parameters and results via inter-process communication.
- Both have performance overheads.
Triggersالمشغّلات
A stored procedure in a database that automatically invokes whenever a special event in the database occurs — e.g., when a row is inserted into a specified table or when specific table columns are updated.
| Trigger | Stored procedure | |
|---|---|---|
| How it runs | Automatically, when a special event occurs | Called (e.g., with call) |
| Manual invocation | Cannot be manually invoked or executed | Yes |
| Parameters | No chance to receive parameters | Can have parameters |
| Commit / rollback inside | A transaction cannot be committed or rolled back inside a trigger | لا تذكر الشرائح شيئًا هنا. |
Syntax (slides 39–40)
- create trigger
[trigger_name]
- before | after
When the trigger will be executed
- insert | update | delete
The DML operation
- on
[table_name]: the table associated with the trigger
- for each row
A row-level trigger: executed for each affected row
- BEGIN … END
Trigger body: the operation performed as the trigger is fired
A trigger example; statement-level triggers; when not to use triggersمثال ومتى نتجنبها
A single action for all rows affected by a transaction: use for each statement instead of for each row; can be more efficient when SQL statements update a large number of rows.
- 1Performance concernsComplex logic or a large number of rows.
- 2Cross-database or cross-server operationsTriggers typically operate within a single database.
- 3Infinite loop potentialA trigger on Table A updates Table B, and Table B has a trigger that updates Table A.
Recursion in SQLالعودية في SQL
Recursive queries
- SQL:1999 permits recursive view definition: convenient for hierarchical data (organizational structure, menu structure, tasks with sub-tasks).
- Recursion uses a WITH statement, called common table expression (CTE).
- It needs an initialization (base) query that seeds the recursion; when to stop is decided in the WHERE clause of the iterative query.
WITH R AS (<base query> UNION ALL <recursive query involving R>)
| Parent | Child |
|---|---|
| Alice | Carol |
| Bob | Carol |
| Carol | Dave |
| Carol | George |
| Dave | Mary |
| Eve | Mary |
| Mary | Frank |
المسألة
Find the ancestors of Frank (slide 49).
خطوات الحل
- WITH Ancestor AS ( SELECT Parent AS p FROM Parent_Child WHERE Child = 'Frank'
- UNION ALL
- SELECT Parent FROM Ancestor, Parent_Child WHERE Ancestor.p = Parent_Child.Child )
- SELECT * FROM Ancestor
النتيجة
Worked out from the table: Mary; then Dave and Eve; then Carol; then Alice and Bob — 6 ancestors. (The slide prints the query, not its result.)
Transitive closure and the fixed pointالإغلاق المتعدي والنقطة الثابتة
Recursive queries
rec_prereq finds which courses are a prerequisite, whether directly or indirectly, for a specific course (CS-347); this view is called the transitive closure of the prereq relation.
| Iteration | Tuples in c1 |
|---|---|
| 0 | لا تذكر الشرائح شيئًا هنا. |
| 1 | (CS-319) |
| 2 | (CS-319), (CS-315), (CS-101) |
| 3 | (CS-319), (CS-315), (CS-101), (CS-190) |
| 4 | (CS-319), (CS-315), (CS-101), (CS-190) |
| 5 | done |
- Recursive views make it possible to write queries, such as transitive closure, that cannot be written without recursion or iteration: a non-recursive non-iterative query performs only a fixed number of joins.
- Each iteration extends the view; the final result is called the fixed point.
- Recursive views are required to be monotonic: adding tuples to prereq never removes tuples from rec_prereq.
Ranking: rank and dense_rankالترتيب
Ranking and windowing
A window function used to assign a rank to each row within a partition of a result set, in conjunction with an order by specification: select ID, rank() over (order by GPA desc) as s_rank from student_grades — not sorted; an extra order by s_rank is needed to sort.
| ID | GPA | rank() | dense_rank() |
|---|---|---|---|
| 707 | 4 | 1 | 1 |
| 505 | 4 | 1 | 1 |
| 909 | 4 | 1 | 1 |
| 404 | 3 | 4 | 2 |
| 202 | 3 | 4 | 2 |
| 606 | 2.5 | 6 | 3 |
| 303 | 2 | 7 | 4 |
| 101 | 2 | 7 | 4 |
- Ranking may leave gaps: 3 students share the top GPA → all have rank 1, and the next rank is 4.
- dense_rank does not leave gaps: the next dense rank would be 2.
Windowingدوال النافذة
Perform calculations across a specified set of rows known as a "window". The OVER clause is key to defining this window: PARTITION BY divides the data into sets, ORDER BY orders them.
OLAP, cross-tabs and data cubesOLAP والمكعبات
Interactive analysis of data, allowing data to be summarized and viewed in different ways; multidimensional analysis on large volumes of data in a data warehouse, data lake or other repository. An OLAP database is optimised for analytical operations.
Values of one dimension attribute form the row headers, values of another the column headers; cells hold aggregates (sum) of the values that specify the cell.
A multidimensional generalization of a cross-tab; can have n dimensions; cross-tabs can be used as views on a data cube.
Cross-tabs can be represented as relations, using the value all for aggregates — the SQL standard actually uses null in place of all.
CUBE and ROLLUPCUBE و ROLLUP
group by cube(item_name, color, size)
- Union of GROUP BY on every subset of the attributes
- Eight groupings: (item_name, color, size), (item_name, color), (item_name, size), (color, size), (item_name), (color), (size), ( )
group by rollup(item_name, color, size)
- Union on every prefix of the list of attributes
- Four groupings: (item_name, color, size), (item_name, color), (item_name), ( )
- Hierarchical aggregates; also known as DRILL UP; like zooming-out
المسألة
group by rollup(item_name), rollup(color, size) — which groupings? (slide 75)
خطوات الحل
- rollup(item_name) → {(item_name), ()}
- rollup(color, size) → {(color, size), (color), ()}
- Multiple ROLLUPs in one GROUP BY: take the cross product of the sets
النتيجة
{(item_name, color, size), (item_name, color), (item_name), (color, size), (color), ( )}
ROLLUP: the inventory exampleمثال ROLLUP
Extended aggregation: CUBE and ROLLUP
- In the syntax above, ROLLUP(c1,c2) generates three grouping sets: (c1,c2), (c1) and () (slide 76).
| warehouse | product | model | quantity |
|---|---|---|---|
| San Fransisco | iPhone | 6s | 50 |
| San Fransisco | iPhone | 7 | 10 |
| San Fransisco | iPhone | X | 200 |
| San Fransisco | Samsung | Galaxy S | 200 |
| San Fransisco | Samsung | Note 8 | 100 |
| San Jose | iPhone | 6s | 100 |
| San Jose | iPhone | 7 | 50 |
| San Jose | iPhone | X | 150 |
| San Jose | Samsung | Galaxy S | 200 |
| San Jose | Samsung | Note 8 | 150 |
| warehouse | SUM(quantity) |
|---|---|
| San Fransisco | 560 |
| San Jose | 650 |
| warehouse | product | SUM(quantity) | Callout on the slide |
|---|---|---|---|
| San Fransisco | iPhone | 260 | |
| San Fransisco | Samsung | 300 | |
| San Fransisco | NULL | 560 | Total inventory in San Fransisco |
| San Jose | iPhone | 300 | |
| San Jose | Samsung | 350 | |
| San Jose | NULL | 650 | Total inventory in San Jose |
| NULL | NULL | 1210 | Total inventory in all warehouses |
OLAP operationsعمليات OLAP
| Operation | What it does (slides 72, 81, 84, 86) |
|---|---|
| Roll up (drill up) |
|
| Roll down (drill down) |
|
| Slice | Subset of the cube for a single value of one or more dimension members (e.g., temperature = cool) |
| Pivot (rotation) | Rotates the data axes in view, e.g., swapping rows and columns |
OLAP implementationتنفيذ OLAP
The earliest OLAP systems: multidimensional arrays in memory to store data cubes.
OLAP implementations using only relational database features.
Store some summaries in memory and the base data and other summaries in a relational database.
الخلاصة
أنهيت الدرس
الخطوة التالية: ثبّت المعلومات بالبطاقات، ثم اختبر نفسك بالأسئلة.
ما راجعته في هذا الدرس
- Accessing SQL from a programming language; ODBCالوصول إلى SQL من لغة برمجة وODBC
- JDBCJDBC
- Embedded SQLSQL المضمّنة
- Functions and proceduresالدوال والإجراءات
- Triggersالمشغّلات (Triggers)
- Recursive queriesالاستعلامات العودية
- Ranking and windowingالترتيب والنوافذ
- OLAP, cross-tabs and data cubesOLAP والجداول المتقاطعة ومكعبات البيانات
- Extended aggregation: CUBE and ROLLUPالتجميع الموسّع: CUBE وROLLUP
- OLAP operations and OLAP implementationعمليات OLAP وطرق تنفيذه