4> CREATE TABLE Students(
5> StudentID int Primary Key,
6> FirstName nvarchar(30),
7> LastName nvarchar(50),
8> FullName AS (FirstName + ' ' + LastName)
9> )
10> GO
1>
2> CREATE TABLE Classes(
3> ClassID int Primary Key,
4> ClassTitle varchar(50)
5> )
6> GO
1>
2> CREATE TABLE ClassGrades(
3> ClassID int,
4> StudentID int,
5> GradeLetter varchar(2),
6> Constraint PK_ClassGrades
7> PRIMARY KEY(ClassID, StudentID),
8> Constraint FK_Classes_ClassID
9> FOREIGN KEY(ClassID)
10> REFERENCES Classes(ClassID) ON UPDATE CASCADE,
11> Constraint FK_Students_StudentID
12> FOREIGN KEY(StudentID)
13> REFERENCES Students(StudentID) ON UPDATE CASCADE
14> )
15> GO
1>
2> --Insert classes rows
3> INSERT Classes VALUES(1,'SQL')
4> INSERT Classes VALUES(999,'Java')
5> GO
(1 rows affected)
(1 rows affected)
1>
2> --Insert Students rows
3> INSERT Students VALUES(1, 'Poor', 'DBA')
4> INSERT Students VALUES(2, 'Better', 'DBA')
5> GO
(1 rows affected)
(1 rows affected)
1>
2> --Insert ClassGrades rows
3> INSERT ClassGrades VALUES(1, 1, 'C+')
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> --Show table values after initial population
3> SELECT S.FullName, C.ClassTitle, CG.GradeLetter
4> FROM Classes C, ClassGrades CG, Students S
5> WHERE C.ClassID = CG.ClassID AND
6> S.StudentID = CG.StudentID
7> GO
FullName ClassTitle GradeLetter
--------------------------------------------------------------------------------- -------------------------------------------------- -----------
Poor DBA SQL C+
Better DBA SQL A+
Better DBA Java A
(3 rows affected)
1>
2> drop table ClassGrades;
3> drop table Classes;
4> drop table Students;
5> GO