Mega Code Archive
An one-to-many relation
<![CDATA[
3> CREATE TABLE Classes(
4> ClassID int PRIMARY KEY,
5> ClassTitle varchar(50)
6> )
7> GO
1>
2> CREATE TABLE ClassGrades(
3> ClassID int REFERENCES Classes(ClassID)
4> ON UPDATE CASCADE,
5> StudentID int,
6> GradeLetter varchar(2),
7> Constraint PK_ClassGrades
8> PRIMARY KEY(ClassID, StudentID)
9> )
10>
11> INSERT Classes VALUES(1,'SQL')
12> INSERT Classes VALUES(999,'Java')
13> GO
(1 rows affected)
(1 rows affected)
1>
2> INSERT ClassGrades VALUES(1, 1, 'C+')
3>
4> INSERT ClassGrades VALUES(1, 2, 'A+')
5> INSERT ClassGrades VALUES(999, 2, 'A')
6> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> SELECT CG.StudentID, C.ClassTitle, CG.GradeLetter
4> FROM Classes C, ClassGrades CG
5> WHERE C.ClassID = CG.ClassID
6> GO
StudentID ClassTitle GradeLetter
----------- -------------------------------------------------- -----------
1 SQL C+
2 SQL A+
2 Java A
(3 rows affected)
1>
2> UPDATE Classes
3> SET ClassTitle = 'The Life of Jesus Christ'
4> WHERE ClassID = 999
5> GO
(1 rows affected)
1>
2> SELECT CG.StudentID, C.ClassTitle, CG.GradeLetter
3> FROM Classes C, ClassGrades CG
4> WHERE C.ClassID = CG.ClassID
5> GO
StudentID ClassTitle GradeLetter
----------- -------------------------------------------------- -----------
1 SQL C+
2 SQL A+
2 The Life of Jesus Christ A
(3 rows affected)
1>
2>
3> drop table ClassGrades;
4> GO
1>
2> drop table Classes;
3> GO
]]>