Scribd
Upload
14 views3 pages

Chapter7 SQL Summary

Chapter 7 covers advanced SQL concepts including complex queries, triggers, views, and schema modification. It discusses techniques such as nested queries, joins, aggregate functions, and the use of the WITH clause for temporary views. The chapter emphasizes the importance of data consistency and dynamic schema evolution through assertions and triggers.

Uploaded by

abdelr7man3mad2004
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
14 views3 pages

Chapter7 SQL Summary

Chapter 7 covers advanced SQL concepts including complex queries, triggers, views, and schema modification. It discusses techniques such as nested queries, joins, aggregate functions, and the use of the WITH clause for temporary views. The chapter emphasizes the importance of data consistency and dynamic schema evolution through assertions and triggers.

Uploaded by

abdelr7man3mad2004
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

Chapter 7 - Advanced SQL Summary

Chapter 7 Summary - More SQL: Complex Queries, Triggers, Views, and Schema Modification

1. Complex Retrieval Queries:

- Includes nested queries, tuple/set comparisons, EXISTS/NOT EXISTS, and correlated subqueries.

- Joins: INNER, OUTER (LEFT, RIGHT, FULL), NATURAL.

- Use of CASE, GROUP BY, HAVING, and WITH clause for temp views.

2. NULL and 3-Valued Logic:

- NULL means unknown or not applicable.

- Comparisons involving NULL result in TRUE, FALSE, or UNKNOWN.

- Use IS NULL or IS NOT NULL for comparison.

3. Nested Queries and Set Comparisons:

- Use IN, = ANY, = ALL for set comparisons.

- Correlated nested queries refer to outer query attributes.

4. EXISTS and NOT EXISTS:

- EXISTS checks if subquery returns any tuples.

- Used for universal quantification (double negation technique).

5. Joins in SQL:

- JOIN, NATURAL JOIN, INNER JOIN, LEFT/RIGHT/FULL OUTER JOIN.

- Multi-way joins possible with nested JOIN.

Page 1
Chapter 7 - Advanced SQL Summary

6. Aggregates and Grouping:

- COUNT, SUM, MAX, MIN, AVG are aggregate functions.

- GROUP BY groups tuples, HAVING filters groups.

7. WITH Clause:

- Temporary views within a query.

- Simplifies complex queries by naming subqueries.

8. CASE Expression:

- Used to conditionally assign values.

Example:

UPDATE EMPLOYEE

SET Salary = CASE

WHEN Dno = 5 THEN Salary + 2000

WHEN Dno = 4 THEN Salary + 1500

ELSE Salary END;

9. Recursive Queries:

- Use WITH RECURSIVE to query hierarchical data.

Example:

WITH RECURSIVE SUP_EMP(SupSsn, EmpSsn) AS (

SELECT SupervisorSsn, Ssn FROM EMPLOYEE

UNION

SELECT E.Ssn, S.EmpSsn FROM EMPLOYEE E, SUP_EMP S WHERE E.SupervisorSsn = S.EmpSsn

Page 2
Chapter 7 - Advanced SQL Summary

SELECT * FROM SUP_EMP;

10. Assertions and Triggers:

- CREATE ASSERTION defines semantic constraints.

- CREATE TRIGGER defines automatic actions on events.

11. Views in SQL:

- Virtual tables created with CREATE VIEW.

- Views can be materialized or modified with strategies.

- Use WITH CHECK OPTION to enforce update conditions.

12. Schema Modification:

- DROP removes schema elements (CASCADE/RESTRICT).

- ALTER modifies table structure (add/drop column, constraints).

- Default values can be changed using ALTER TABLE.

Conclusion:

Chapter 7 expands SQL to support complex logic, ensure data consistency with triggers/assertions, and allow

dynamic schema evolution. Views enhance abstraction and security.

Page 3

You might also like