A small experience in using SQL to calculate exam results

 

The problem is described as follows:

Sqlserver has three tables:

First: List of persons in the organization who need to take the test (Table ). This is really bad. It only has a name and no question mark.

Second, you need to take the examination list, examination number, and subject. (Table B)

There are some useless items in this table, including the transferred persons. Some of them take one and some take two.

The third one is to store the list of persons who have already taken the exam and the exam number. This table not only contains the list of persons who have taken the exam in our unit, but also the persons who have taken the exam in other units (Table C)

My task is to find out the list and subjects of those who did not take the test from these three tables.

There are several hundred people. Use my eyes to get tired of me...

My practices:

Select B. examination no., A. Name, B. subject from a join B on A. Name = B. Name

Except

Select C. examination no., C. Name, C. subject from C

 

The first select is to find out the correspondence between people and subjects in our unit.

The second select is to find out the student ID and subject of the person who has already taken the test.

The result is to use the distinct T statement to find the persons and subjects that exist in the first select result and that do not exist in the second select result.

I have been busy for more than an hour. Because you do not know the limit t statement .......

I hope it will be helpful to you. For detailed usage of the T statement, refer to sqlserver books online