Advanced SQL - PowerPoint PPT Presentation

Loading...

PPT – Advanced SQL PowerPoint presentation | free to download - id: 413f57-NWU4Z



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Advanced SQL

Description:

Chapter 8 Advanced SQL Database Systems: Design, Implementation, and Management, Seventh Edition, Rob and Coronel In this chapter, you will learn: About the ... – PowerPoint PPT presentation

Number of Views:449
Avg rating:3.0/5.0
Slides: 35
Provided by: CourseTe
Learn more at: http://www.cs.science.cmu.ac.th
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Advanced SQL


1
Chapter 8
Advanced SQL Database Systems Design,
Implementation, and Management, Seventh Edition,
Rob and Coronel
2
In this chapter, you will learn
  • About the relational set operators UNION, UNION
    ALL, INTERSECT, and MINUS
  • How to use the advanced SQL JOIN operator syntax
  • About the different types of subqueries and
    correlated queries
  • How to use SQL functions to manipulate dates,
    strings, and other data

3
In this chapter, you will learn (continued)
  • How to create and use updatable views
  • How to create and use triggers and stored
    procedures
  • How to create embedded SQL

4
Relational Set Operators
  • UNION
  • INTERSECT
  • MINUS
  • Work properly if relations are union-compatible
  • Names of relation attributes must be the same and
    their data types must be identical

5
UNION
  • Example query
  • SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL,
    CUS_AREACODE, CUS_PHONEFROM
    CUSTOMERUNIONSELECT CUS_LNAME, CUS_FNAME,
    CUS_INITIAL, CUS_AREACODE, CUS_PHONEFROM
    CUSTOMER_2

6
UNION (continued)
7
UNION ALL
  • Example query
  • SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL,
    CUS_AREACODE, CUS_PHONEFROM CUSTOMERUNION
    ALLSELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL,
    CUS_AREACODE, CUS_PHONEFROM CUSTOMER_2

8
UNION ALL (continued)
9
INTERSECT
10
MINUS
11
Syntax Alternatives
12
Syntax Alternatives (continued)
13
SQL Join Operators
14
Cross Join
  • Syntax
  • SELECT column-list FROM table1 CROSS JOIN table2

15
Natural Join
16
JOIN USING Clause
17
JOIN ON Clause
18
Outer Joins
  • Returns not only matching rows, but also rows
    with unmatched attribute values for one table or
    both tables to be joined
  • Three types
  • Left
  • Right
  • Full

19
Outer Joins (continued)
20
Outer Joins (continued)
21
Outer Joins (continued)
22
Subqueries and Correlated Queries
23
WHERE Subqueries
24
IN Subqueries
25
HAVING Subqueries
26
Multirow Subquery Operators ANY and ALL
27
FROM Subqueries
28
Attribute List Subqueries
29
Attribute List Subqueries (continued)
30
Correlated Subqueries
31
Correlated Subqueries (continued)
32
Date and Time Functions
33
Date and Time Functions (continued)
34
Date and Time Functions (continued)
About PowerShow.com