CS457 Syllabus & Progress

Intro to tuple condition

Tuple condition

Recall: this is the basic SELECT syntax:

select attribute-list from relation-list where condition

The condition in the where clause is called:

tuple condition

because:

The where clause's condition is applied to the set of tuples in this cartesian product:

select attribute-list from relation-list

Example 1:

Consider the SQL query:

select fname, lname from employee where dno = 5

The FROM clause of the query:

select ssn, fname, lname, salary, dno from employee

form the cartesian product consisting of all employee tuples: (showing only a subset of all attributes)

+-----------+--------+---------+----------+-----+ | ssn | fname | lname | salary | dno | +-----------+--------+---------+----------+-----+ | 123456789 | John | Smith | 30000.00 | 5 | | 333445555 | Frankl | Wong | 40000.00 | 5 | | 999887777 | Alicia | Zelaya | 25000.00 | 4 | | 987654321 | Jennif | Wallace | 43000.00 | 4 | | 666884444 | Ramesh | Narayan | 38000.00 | 5 | | 453453453 | Joyce | English | 25000.00 | 5 | | 987987987 | Ahmad | Jabbar | 25000.00 | 4 | | 888665555 | James | Borg | 55000.00 | 1 | +-----------+--------+---------+----------+-----+

The tuple condition "dno=5" will select the following set of tuple (from the cartesian product):

+-----------+--------+---------+----------+-----+ | ssn | fname | lname | salary | dno | +-----------+--------+---------+----------+-----+ | 123456789 | John | Smith | 30000.00 | 5 | | 333445555 | Frankl | Wong | 40000.00 | 5 | | 666884444 | Ramesh | Narayan | 38000.00 | 5 | | 453453453 | Joyce | English | 25000.00 | 5 | +-----------+--------+---------+----------+-----+

Example 2:

Consider the SQL query:

select fname, lname from employee, department where dnumber = dno and dno = 5

The FROM clause of the query:

select fname, lname, dno, dnumber, dname, mgrssn from employee, department

form the following cartesian product: (with some attributes not shown)

+--------+---------+-----+---------+----------------+-----------+ | fname | lname | dno | dnumber | dname | mgrssn | +--------+---------+-----+---------+----------------+-----------+ | John | Smith | 5 | 5 | Research | 333445555 | | John | Smith | 5 | 4 | Administration | 987654321 | | John | Smith | 5 | 1 | Headquarters | 888665555 | | Frankl | Wong | 5 | 5 | Research | 333445555 | | Frankl | Wong | 5 | 4 | Administration | 987654321 | | Frankl | Wong | 5 | 1 | Headquarters | 888665555 | | Alicia | Zelaya | 4 | 5 | Research | 333445555 | | Alicia | Zelaya | 4 | 4 | Administration | 987654321 | | Alicia | Zelaya | 4 | 1 | Headquarters | 888665555 | | Jennif | Wallace | 4 | 5 | Research | 333445555 | | Jennif | Wallace | 4 | 4 | Administration | 987654321 | | Jennif | Wallace | 4 | 1 | Headquarters | 888665555 | | Ramesh | Narayan | 5 | 5 | Research | 333445555 | | Ramesh | Narayan | 5 | 4 | Administration | 987654321 | | Ramesh | Narayan | 5 | 1 | Headquarters | 888665555 | | Joyce | English | 5 | 5 | Research | 333445555 | | Joyce | English | 5 | 4 | Administration | 987654321 | | Joyce | English | 5 | 1 | Headquarters | 888665555 | | Ahmad | Jabbar | 4 | 5 | Research | 333445555 | | Ahmad | Jabbar | 4 | 4 | Administration | 987654321 | | Ahmad | Jabbar | 4 | 1 | Headquarters | 888665555 | | James | Borg | 1 | 5 | Research | 333445555 | | James | Borg | 1 | 4 | Administration | 987654321 | | James | Borg | 1 | 1 | Headquarters | 888665555 | +--------+---------+-----+---------+----------------+-----------+

The tuple condition "dnumber=dno and dno=5" will select the following set of tuple (from the cartesian product):

+--------+---------+-----+---------+----------+-----------+ | fname | lname | dno | dnumber | dname | mgrssn | +--------+---------+-----+---------+----------+-----------+ | John | Smith | 5 | 5 | Research | 333445555 | | Frankl | Wong | 5 | 5 | Research | 333445555 | | Ramesh | Narayan | 5 | 5 | Research | 333445555 | | Joyce | English | 5 | 5 | Research | 333445555 | +--------+---------+-----+---------+----------+-----------+

(Because these tuples in the cartesian product satisfy the stated tuple condition)