Unknown SQL syntax

Question

I'm following an SQL course and I'm having troubles understanding an example given my our professor, there's usually a lot of mistakes in our sheets that we have to correct but here I think it might just come from my obvious ignorance of the subject.

So the database contains three tables organized like this:

Student (StudentNumber, Name, Year)
Course (Code, Name, Hours)
Results (StudentNumbber, Code, Grade)

We're asked to give the student numbers that follow the "M11104" coded course with one query to the database servor. Here is the solution given:

Select S. *
FROM Student S, Results R
WHERE Code = 'M1105'
AND S.StudentNumber = R.StudentNumber;

I just don't get how is this supposed to work, first of all the S and R are no real attributes to the database given, and the SELECT S.* doesn't seem to mean anything there.

Off-topic, but note that these days, you should really use ANSI join syntax; i.e. whenever you're joining tables, you should do so with an explicit `JOIN` clause. See e.g. http://stackoverflow.com/q/1599050/240733 for an example. — stakx - no longer contributing, Nov 13 '16 at 16:06

score 2 · Accepted Answer · answered Nov 13 '16 at 16:03

2

In your example S and R are simply defined as aliases of Student and Results tables. Using S.* is exactly the same as saying Student.*, or ALL Columns of the Student Table.

answered Nov 13 '16 at 16:03

gmiley

6,531
1
13
25

score 1 · Answer 2 · edited Nov 13 '16 at 16:09

1

This is called an "alias".

When you have a statement of the form:

SELECT … FROM [tableName] 'anyCharacter';

the character in the end of the statement became an alias to the table and you can use the alias instead of the table name for example:

SELECT st.Name FROM Student st;
--     ^^                   ^^
-- 'st' is an alias for the 'Student' table

edited Nov 13 '16 at 16:09

stakx - no longer contributing

83,039
20
168
268

answered Nov 13 '16 at 16:06

Developer

460
4
17

score 1 · Answer 3 · answered Nov 13 '16 at 16:07

S and R are aliases. S is an alias for the Student table or view, and R is an alias for the Results table or view. Aliases are used to avoid typing the full name of a table every time it is referred to in a query. It becomes more clear when we use the optional AS keyword, like this:

SELECT
    S.StudentNumber,
    S.Name,
    S.[Year]
FROM Students AS S
INNER JOIN Results AS R ON
  R.StudentNumber=S.StudentNumber
WHERE
  R.Code='M1105'

The comma syntax is discouraged, use JOIN instead. If you use an alias for any table, then you should also qualify all references to columns with that alias. Failing to do so can lead to nasty runtime errors when schemas change (in your example, if a Code column was added to the second table too, an ambiguous column exception would suddenly occur). The square brackets are used to quote object names that are reserved words (parts of the syntax or built-in functions, like YEAR).

Unknown SQL syntax

3 Answers3