Sql design patterns
Download
1 / 32

SQL Design Patterns - PowerPoint PPT Presentation


  • 205 Views
  • Uploaded on

SQL Design Patterns. Advanced SQL programming idioms. Genesis. C++ world Advanced C++ Programming Styles and Idioms, by James O. Coplien Design Patterns: Elements of Reusable Object-Oriented Software by Erich Gamma et al SQL SQL for Smarties by Joe Celko SQL Cookbook by Anthony Molinaro

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SQL Design Patterns' - kiona


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Sql design patterns l.jpg

SQL Design Patterns

Advanced SQL programming idioms


Genesis l.jpg
Genesis

  • C++ world

    Advanced C++ Programming Styles and Idioms, by James O. Coplien

    Design Patterns: Elements of Reusable Object-Oriented Software by Erich Gamma et al

  • SQL

    SQL for Smarties by Joe Celko

    SQL Cookbook by Anthony Molinaro

    The Art of SQL by Stephane Faroult, Peter Robson


What is a sql pattern l.jpg
What is a SQL Pattern?

  • A common design vocabulary

  • A documentation and learning aid

  • An adjunct to existing design methods

  • A target for refactoring

  • Large range of granularity -- from very general design principles to language-specific idioms


List of patterns l.jpg
List of Patterns

  • Counting

  • Conditional summation

  • Integer generator

  • String/Collection decomposition

  • List Aggregate

  • Enumerating pairs

  • Enumerating sets

  • Interval coalesce


Slide5 l.jpg

  • Discrete interval sampling

  • User-defined aggregate

  • Pivot

  • Symmetric difference

  • Histogram

  • Skyline query

  • Relational division

  • Outer union

  • Complex constraint

  • Nested intervals

  • Transitive closure

  • Hierarchical total


Symmetric difference l.jpg
Symmetric Difference

  • A = B ?

  • Isn’t it Equality operator ?


Venn diagram l.jpg
Venn diagram

B\A

A∩B

A\B

(A \ B) ∪ (B \ A)

(A ∪ B) \ (A ∩ B)


Sql query l.jpg
SQL Query

(

select * from A

minus

select * from B

) union all (

select * from B

minus

select * from A

)


Slide9 l.jpg
Test

create table A asselect obj# id, name from sys.obj$where rownum < 100000;

create table B asselect obj# id, name from sys.obj$where rownum < 100010;



Anti join transformation l.jpg
Anti Join Transformation

convert_set_to_join = true:

select * from A

where (col1,col2,…) not in (select col1,col2,… from B)

union all

select * from B

where (col1,col2,…) not in (select col1,col2,… from A)



Optimization continued l.jpg
Optimization continued

  • CREATE INDEX A_id_name ON A(id, name); CREATE INDEX B_id_name ON B(id, name);

  • _hash_join_enabled = false_optimizer_sortmerge_join_enabled = false

    or

    /*+ use_nl(@"SEL$74086987" A) use_nl(@"SET$D8486D66" B)*/


Symmetric difference via aggregation l.jpg
Symmetric Difference via Aggregation

select * from (select id, name, sum(casewhen src=1 then 1 else 0 end) cnt1, sum(case when src=2 then 1 else 0 end) cnt2 from ( select id, name, 1 src from A union allselect id, name, 2 src from B ) group by id, name)where cnt1 <> cnt2



Equality checking via aggregation l.jpg

||

||

||

orahash

orahash

orahash

592731

267629

334382

Equality checking via Aggregation

  • 1.Is there any difference? (Boolean).

  • 2. What are the rows that one table contains, and the other doesn't?

||

orahash

512259

+

+

+

=

1523431


Relational division l.jpg

Name

Language

Language

Steve

SQL

SQL

Name

Java

Pete

Java

Steve

Kate

SQL

Pete

Kate

Steve

Java

Pete

SQL

Kate

Java

Relational Division

ApplicantSkills

JobApplicants

JobRequirements

x

=


Dividend divisor and quotient l.jpg

Name

Name

Language

Language

Steve

Kate

SQL

SQL

Java

Pete

Java

Kate

SQL

ApplicantSkills

Kate

Java

JobRequirements

/

?

=

Dividend, Divisor and Quotient

Remainder


Is it a common pattern l.jpg
Is it a common Pattern?

  • Not a basic operator in RA or SQL

  • Informally:

    “Find job applicants who meet all job requirements”

    compare with:

    “Find job applicants who meet at least one job requirement”


Set union query l.jpg

ID

ELEMENT

1

1

1

3

1

5

2

3

2

4

2

5

3

5

3

6

Set Union Query

Given a set of sets,

e.g {{1,3,5},{3,4,5},{5,6}}

Find their union:

SELECT DISTINCTelement

FROM Sets

Sets


Set intersection l.jpg

ID

ELEMENT

1

1

1

3

1

5

2

3

2

4

2

5

3

5

3

6

Set Intersection

Given a set of sets,

e.g {{1,3,5},{3,4,5},{5,6}}

Find their intersection?

Sets


It s relational division query l.jpg

ID

ELEMENT

ID

ELEMENT

1

5

1

1

1

2

3

3

1

5

2

3

2

4

2

5

3

5

3

6

It’s Relational Division Query!

“Find Elements which belong to all sets”

compare with:

“Find Elements who belong to at least one set”

/

=


Implementation 1 l.jpg

Name

Language

Steve

SQL

Pete

Java

Kate

SQL

Steve

Java

Pete

SQL

Kate

Java

Implementation (1)

πName(ApplicantSkills)x JobRequirements


Implementation 2 l.jpg
Implementation (2)

Applicants who are not qualified:

πName (

πName(ApplicantSkills)x JobRequirements

- ApplicantSkills

)


Implementation 3 l.jpg
Implementation (3)

Final Query:

πName (ApplicantSkills)-

πName ( ApplicantSkills -

πName(ApplicantSkills)x JobRequirements

)


Implementation in sql 1 l.jpg
Implementation in SQL (1)

select distinct Name from ApplicantSkills

minus

select Name from (

select Name, Language from (

select Name from ApplicantSkills

), (

select Language from JobRequirements

)

minus

select Name, Language from ApplicantSkills

)


Implementation in sql 2 l.jpg
Implementation in SQL (2)

selectdistinct Name from ApplicantSkills i

where not exists (

select * from JobRequirements ii

where not exists (

select * from ApplicantSkills iii

where iii.Language = ii.Language

and iii.Name = i.Name

)

)


Implementation in sql 3 l.jpg
Implementation in SQL (3)

“Name the applicants such that for all job requirements there exists a corresponding entry in the applicant skills” 

“Name the applicants such that there is no job requirement such that there doesn’t exists a corresponding entry in the applicant skills”

“Name the applicants for which the set of all job skills is a subset of their skills”


Implementation in sql 4 l.jpg
Implementation in SQL (4)

select distinct Name from ApplicantSkills i

where

(select Language from JobRequirements ii

where ii.Name = i.Name)

in

(select Language from ApplicantSkills)


Implementation in sql 5 l.jpg
Implementation in SQL (5)

A ⊆ BA \ B = ∅

selectdistinct Name from ApplicantSkills i

where not exists (

select Language from ApplicantSkills

minus

select Language from JobRequirements ii

where ii.Name = i.Name

)


Implementation in sql 6 l.jpg
Implementation in SQL (6)

select Name from ApplicantSkills s, JobRequirements r

where s.Language = r.Language

group by Name

having count(*) = (select count(*) from JobRequirements)



ad