1.4: Sets in Relational Database
Databases consist of sets of data. Tables are sets of structured data. Each "row" contains entries for one or more fields; each field is a specific type of data such as a string, integer, or date. One of the uses of databases is to connect tables based on shared fields. For an example see Appendix A .
The most common method for accessing databases, especially large, enterprise databases, is Structured Query Language (SQL pronounced sequel ). Below is a very simple illustration of the format of SQL query statements.
|
|
each field from each table desired |
|
|
|
|
|
how to match rows of tables |
|
|
limits data used |
A specific example that shows the midterm grade and placement test score of all students on the roll is below.
SELECT Midterm.Section, Compare.[Student ID], Compare.[Placement Score 1], Compare.[Placement Score 2], Midterm.Score, Midterm.Letter FROM Compare INNER JOIN Midterm ON Compare.[Student ID] = Midterm.ID;
Note that the JOIN statements determine what set operation to perform on the tables. For example a left join takes all rows from the 1st table regardless of whether there is a matching row in the second table. An inner join requires that there be a matching row in both tables. An outer join uses all rows from both tables regardless of whether there is a match.
SELECT [fields] FROM Table A LEFT JOIN Table B ON A.Key=B.Key
Set Notation: \(A\)
SELECT [fields] FROM Table A INNER JOIN Table B ON A.Key=B.Key
Set Notation: \(A \cap B\)
Checkpoint \(\PageIndex{4}\)
SELECT [fields] FROM Table A RIGHT JOIN Table B ON A.Key=B.Key
Set Notation: __________
Checkpoint \(\PageIndex{5}\)
SELECT [fields] FROM Table A LEFT JOIN Table B ON A.Key=B.Key WHERE B.Key IS NULL
Set Notation: __________
Checkpoint \(\PageIndex{6}\)
SELECT [fields] FROM Table A RIGHT JOIN Table B ON A.Key=B.Key WHERE A.Key IS NULL
Set Notation: __________
Checkpoint \(\PageIndex{7}\)
SELECT [fields] FROM Table A FULL OUTER JOIN Table B ON A.Key=B.Key
Set Notation: __________
Checkpoint \(\PageIndex{8}\)
SELECT [fields] FROM Table A FULL OUTER JOIN Table B ON A.Key=B.Key WHERE A.Key IS NULL or B.Key IS NULL
Set Notation: __________