- Scoping rule in
Java:
- Scoping rules
in programming languages define
where (location in the program)
a name (= identifier) is
visible (= usable)
- { ..... }
defines a scope level
Example:
public static void main(String[] args) { ..... { ..... // Inner scope level } }
- Variables
defined in an outer scope
is visible (= usable)
in an inner scope
Variables defined in an inner scope is not visible (= not usable) in an outer scope
Example:
public static void main(String[] args) { int i = 4; { // Inner scope double x = 3.14; System.out.println("i = " + i + ", x = " + x); // We can use i } System.out.println("i = " + i + ", x = " + x); // Error - can't use x }Demo: /home/cs377001/demo/scoping/scoping.java
- Scoping rules
in programming languages define
where (location in the program)
a name (= identifier) is
visible (= usable)
- SQL
is a programming language
and its
nested query construct
also has a
scoping rule :
- Each nested query constitutes a
new inner scope:
SELECT ... // Outer scope FROM ... WHERE ...... (SELECT ... FROM ... // Inner scope WHERE ... )
- The names
of relations
(and their attribute name)
in an outer query
are
visible (= usable)
in the
inner query
Schematically:
SELECT attr. names from R1 and/or R2 /* Outer query */ FROM R1, R2 WHERE attr.names from R1 and/or R2 .... /* inner query */ ( SELECT attr. names from R1, R2, R3 and/or R4 FROM R3, R4 WHERE attr. names from R1, R2, R3 and/or R4 )The attributes of R1 and R2 are visible (accessible) in the inner query
But the attributes of R3 and R4 are not visible (accessible) in the outer query
- Each nested query constitutes a
new inner scope:
- Association rules for
attribute names:
- An attribute name specified inside an inner query is associated with the nearest relation (scope-wise) that contains the attribute name
Example:
- If relation
R3
(or
R4) contains the
attribute name
x, then:
- the attribute name x in the inner query belongs to relation R3 (or R4)
Schematically:
- If
both relations
R3 and
R4
contain the
attribute name
x, then:
- Error:
the attribute name
x is
ambiguous and
you must
qualify the name
x
See: click here
Schematically:
- Error:
the attribute name
x is
ambiguous and
you must
qualify the name
x
- If relations
R3 and
R4
does not
contain the
attribute name
x, then:
- the attribute name x in the inner query must refer to an attribute in an outer relation R1 or R2
Schematically:
Nested query:
SELECT .... /* Outer query */ FROM R1, R2 WHERE .... .... ( SELECT x /* inner query */ FROM R3, R4 WHERE x = .... )How to associate the attribute x to a relation:
- Example:
- Find fname, lname of employees who has a dependent of the opposite sex
Solution:
SELECT ssn, fname, lname FROM employee A WHERE { dependents of A where sex is opposite of A's sex } != ∅
SELECT ssn, fname, lname FROM employee A WHERE EXISTS (SELECT essn FROM dependent WHERE essn = A.ssn AND sex != A.sex )
Suppose we wrote this query: SELECT ssn, fname, lname FROM employee WHERE EXISTS (SELECT essn FROM dependent WHERE essn = ssn // essn is dependent.essn, ssn is employee.ssn AND sex != sex // Both sex attr's are: dependent.sex !!! ) The association of attributes is as follows: essn is dependent.essn because dependent is in inner scope (nearest) ssn is employee.ssn (because dependent does not have ssn) !!! sex is dependent.sex because dependent is in inner scope (nearest)
How to fix the query: SELECT ssn, fname, lname FROM employee WHERE EXISTS (SELECT essn FROM dependent WHERE essn = ssn /* (1) essn refers to dependent (2) ssn refers to employee because dependent has no "ssn" */ AND sex != employee.sex /* (1) sex (with no qualifier) refers to dependent (2) We must use employee.sex to refer to the "sex" attr in the outer relation !!! )