SQL & QBE

There are three typical operations that are done in querying databases (relational algebra): (1) Project—select columns [SELECT in SQL] (2) Restrict—select rows [WHERE] (3) Join—select columns and merge on rows that meet conditions [FROM] & [WHERE]
Relational algebra is not used in current systems. It is a conceptual/theoretical way to manipulate RDBs.

Structured Query Language (SQL) is a widely used language that retrieves and updates data in tables and views (manipulate RDBs). QBE is a user interface that simplifies SQL procedures. Other than some minor syntax differences, SQL is standardized. It is very powerful—i.e. you can do almost anything with data tables that you want. It is also simple to use. SQL is set based—returns a subset of tables referenced.

Action queries enable user to change, insert, create, and delete data sets (tables).
Selection queries retrieve and display data.
Parameter queries prompt for input information.
Dynasets are temporary tables that Access uses to store data resulting from a query.

Tables must be related if used in a query.

Natural join (equijoin or inner join)—most common kind of join. Two tables are joined on the common (join) column. The WHERE (=) statement specifies the join column(s) in which the rows have to match.
Outer join (full)—all rows from both tables are included in output table (left and right outer joins would include all rows in one table but only the matches from the other).

I find the join statement hard to work with (remember), so I use WHERE with = or ne to do the same thing. Different ways to specify “not equal to” (, !=, ne) depends on the system used.

Example of a “join” in SAS:
CREATE TABLE xxx AS
SELECT A.cnum, A.ipodt, B.year, B.fyr
FROM netipo AS A, icadata AS B
WHERE A.cnum = B.cnum;

“left join” in SAS:
CREATE TABLE xxx AS
SELECT A.cnum,