程序代写代做代考 Java database SQL Slide 1

Slide 1

SQL

Past Muddiest Points

RA => SQL Natural Join

SQL: Natural Join

SQL: Division

SQL

SQL

Natural Join

SQL

How many attributes / columns are there?

SQL

Natural Join

S1-S2: name, dept, rank, sal S2-S1: cid, yearA: pid

SQL

SQL

Past Muddiest Points

RA => SQL Natural Join

SQL: Join Condition, Inner Join, Natural Join

SQL: Division

SQL

Past Muddiest Points
Phone codes

select *
from City, Area
Where City.areaCode = Area.areaCode

areaCode cityCode cityName areaCode areaName

40 01792 Swansea 40 United Kingdom

86 10 Beijing 86 China

1 416 Toronto 1 North America

1 212 NewYork 1 North America

Explicit Join Condition

areaCode cityCode cityName

40 01792 Swansea

86 10 Beijing

1 416 Toronto

1 212 NewYork

areaCode areaName

40 United Kingdom

852 Hong Kong

1 North America

86 China

City Area

SQL

Past Muddiest Points
Phone codes

select *
from City inner join Area
on City.areaCode = Area.areaCode

areaCode cityCode cityName areaCode areaName

40 01792 Swansea 40 United Kingdom

86 10 Beijing 86 China

1 416 Toronto 1 North America

1 212 NewYork 1 North America

areaCode cityCode cityName

40 01792 Swansea

86 10 Beijing

1 416 Toronto

1 212 NewYork

areaCode areaName

40 United Kingdom

852 Hong Kong

1 North America

86 China

City Area

SQL

Past Muddiest Points
Phone codes

select *
from City natural join Area

areaCode cityCode cityName areaName

40 01792 Swansea United Kingdom

86 10 Beijing China

1 416 Toronto North America

1 212 NewYork North America

areaCode cityCode cityName

40 01792 Swansea

86 10 Beijing

1 416 Toronto

1 212 NewYork

areaCode areaName

40 United Kingdom

852 Hong Kong

1 North America

86 China

City Area

SQL

Past Muddiest Points
One day, a guy added a column

areaCode cityCode cityName lastupdate

40 01792 Swansea 1990

86 10 Beijing 1988

1 416 Toronto 1975

1 212 NewYork 1971

areaCode areaName lastupdate

40 United Kingdom 1980

852 Hong Kong 1978

1 North America 1979

86 China 1987

City Area

select *
from City, Area
Where City.areaCode = Area.areaCode

select *
from City inner join Area
on City.areaCode = Area.areaCode

areaCode cityCode cityName lastupdate areaCode areaName lastupdate

40 01792 Swansea 1990 40 United Kingdom 1980

86 10 Beijing 1988 86 China 1987

1 416 Toronto 1975 1 North America 1979

1 212 NewYork 1971 1 North America 1979

SQL

Past Muddiest Points
One day, a guy added a column

areaCode cityCode cityName lastupdate

40 01792 Swansea 1990

86 10 Beijing 1988

1 416 Toronto 1975

1 212 NewYork 1971

areaCode areaName lastupdate

40 United Kingdom 1980

852 Hong Kong 1978

1 North America 1979

86 China 1987

City Area

areaCode cityCode cityName areaName lastupdate

empty

select *
from City natural join Area

Natural Join breaks the whole system!

SQL

Past Muddiest Points
One day, the guy change the schema (by mistake)

select *
from City natural join Area

areaCode areaName

40 United Kingdom

852 Hong Kong

1 North America

86 China

City Area

select *
from City, Area
where City.areaCode = Area.areaCode

select *
from City inner join Area
on City.areaCode = Area.areaCode

Cartesian Product!SQL Warning:
No areaCode

It breaks the system!
But no warning.

cityCode cityName

01792 Swansea

10 Beijing

416 Toronto

212 NewYork

SQL

Past Muddiest Points
Lesson learnt

Database related codes may not update for years.
If something wrong, you need some warnings reminders.

Old School Wisdom
Use join with explicit join condition in SQL
Never use Natural Join Keyword in SQL

Natural join in Relational Algebra is fine.
Part of the theory, designed for easy manipulation.
You should use the concept in Relational Algebra and SQL too.

But practically, treat Natural Join keyword in SQL
as if it never exists, avoids it likes a plague.

In CW2 & Exam, if you use natural join keyword in SQL, no marks.
Learn the correct way of translating concept of natural join from RA to SQL.

http://www.dba-oracle.com/oracle_news/2004_2_19_rittman.htm

Unnatural outcome of natural joins


http://www.postgresqltutorial.com/postgresql-natural-join
https://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax

aka, compile-time warning in
Java / C / C++. Spot mistakes early.

http://www.dba-oracle.com/oracle_news/2004_2_19_rittman.htm

Unnatural outcome of natural joins


http://www.postgresqltutorial.com/postgresql-natural-join
https://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax

SQL

Past Muddiest Points

RA => SQL Natural Join

SQL: Natural Join

SQL: Division

SQL

Division

RA: Division was a bit confusing and to
understand

Results:
p1
p4

SQL

Division

Which operations go first?

SQL

SQL and Division

SQL

SQL and Division

SQL

SQL and Division

SQL

SQL and Division

SQL

SQL and Natural Join

SQL

SQL and Natural Join

SQL

SQL and Division