· 8 years ago · May 18, 2018, 05:16 PM
1SET ANSI_NULLS ON
2GO
3SET QUOTED_IDENTIFIER ON
4GO
5
6USE [master];
7GO
8
9IF EXISTS (SELECT * FROM sys.databases WHERE name = 'DN_DL_School')
10 DROP DATABASE DN_DL_School;
11GO
12
13-- Create the School database.
14CREATE DATABASE DN_DL_School;
15GO
16
17-- Specify a simple recovery model
18-- to keep the log growth to a minimum.
19ALTER DATABASE DN_DL_School
20 SET RECOVERY SIMPLE;
21GO
22
23USE DN_DL_School;
24GO
25
26-- Create the Department table.
27IF NOT EXISTS (SELECT * FROM sys.objects
28 WHERE object_id = OBJECT_ID(N'[dbo].[Department]')
29 AND type in (N'U'))
30BEGIN
31CREATE TABLE [dbo].[Department](
32 [DepartmentID] [int] NOT NULL,
33 [Name] [nvarchar](50) NOT NULL,
34 [Budget] [money] NOT NULL,
35 [StartDate] [datetime] NOT NULL,
36 [Administrator] [int] NULL,
37 CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED
38(
39 [DepartmentID] ASC
40)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
41) ON [PRIMARY]
42END
43GO
44
45-- Create the Person table.
46IF NOT EXISTS (SELECT * FROM sys.objects
47 WHERE object_id = OBJECT_ID(N'[dbo].[Person]')
48 AND type in (N'U'))
49BEGIN
50CREATE TABLE [dbo].[Person](
51 [PersonID] [int] IDENTITY(1,1) NOT NULL,
52 [LastName] [nvarchar](50) NOT NULL,
53 [FirstName] [nvarchar](50) NOT NULL,
54 [HireDate] [datetime] NULL,
55 [EnrollmentDate] [datetime] NULL,
56 CONSTRAINT [PK_DN_DL_School.Student] PRIMARY KEY CLUSTERED
57(
58 [PersonID] ASC
59)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
60) ON [PRIMARY]
61END
62GO
63
64-- Create the OnsiteCourse table.
65IF NOT EXISTS (SELECT * FROM sys.objects
66 WHERE object_id = OBJECT_ID(N'[dbo].[OnsiteCourse]')
67 AND type in (N'U'))
68BEGIN
69CREATE TABLE [dbo].[OnsiteCourse](
70 [CourseID] [int] NOT NULL,
71 [Location] [nvarchar](50) NOT NULL,
72 [Days] [int] NOT NULL,
73 [Time] [smalldatetime] NOT NULL,
74 CONSTRAINT [PK_OnsiteCourse] PRIMARY KEY CLUSTERED
75(
76 [CourseID] ASC
77)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
78) ON [PRIMARY]
79END
80GO
81
82-- Create the OnlineCourse table.
83IF NOT EXISTS (SELECT * FROM sys.objects
84 WHERE object_id = OBJECT_ID(N'[dbo].[OnlineCourse]')
85 AND type in (N'U'))
86BEGIN
87CREATE TABLE [dbo].[OnlineCourse](
88 [CourseID] [int] NOT NULL,
89 [URL] [nvarchar](100) NOT NULL,
90 CONSTRAINT [PK_OnlineCourse] PRIMARY KEY CLUSTERED
91(
92 [CourseID] ASC
93)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
94) ON [PRIMARY]
95END
96GO
97
98--Create the StudentGrade table.
99IF NOT EXISTS (SELECT * FROM sys.objects
100 WHERE object_id = OBJECT_ID(N'[dbo].[StudentGrade]')
101 AND type in (N'U'))
102BEGIN
103CREATE TABLE [dbo].[StudentGrade](
104 [EnrollmentID] [int] IDENTITY(1,1) NOT NULL,
105 [CourseID] [int] NOT NULL,
106 [StudentID] [int] NOT NULL,
107 [Grade] [decimal](3, 2) NULL,
108 CONSTRAINT [PK_StudentGrade] PRIMARY KEY CLUSTERED
109(
110 [EnrollmentID] ASC
111)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
112) ON [PRIMARY]
113END
114GO
115
116-- Create the CourseInstructor table.
117IF NOT EXISTS (SELECT * FROM sys.objects
118 WHERE object_id = OBJECT_ID(N'[dbo].[CourseInstructor]')
119 AND type in (N'U'))
120BEGIN
121CREATE TABLE [dbo].[CourseInstructor](
122 [CourseID] [int] NOT NULL,
123 [PersonID] [int] NOT NULL,
124 CONSTRAINT [PK_CourseInstructor] PRIMARY KEY CLUSTERED
125(
126 [CourseID] ASC,
127 [PersonID] ASC
128)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
129) ON [PRIMARY]
130END
131GO
132
133-- Create the Course table.
134IF NOT EXISTS (SELECT * FROM sys.objects
135 WHERE object_id = OBJECT_ID(N'[dbo].[Course]')
136 AND type in (N'U'))
137BEGIN
138CREATE TABLE [dbo].[Course](
139 [CourseID] [int] NOT NULL,
140 [Title] [nvarchar](100) NOT NULL,
141 [Credits] [int] NOT NULL,
142 [DepartmentID] [int] NOT NULL,
143 CONSTRAINT [PK_DN_DL_School.Course] PRIMARY KEY CLUSTERED
144(
145 [CourseID] ASC
146)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
147) ON [PRIMARY]
148END
149GO
150
151-- Create the OfficeAssignment table.
152IF NOT EXISTS (SELECT * FROM sys.objects
153 WHERE object_id = OBJECT_ID(N'[dbo].[OfficeAssignment]')
154 AND type in (N'U'))
155BEGIN
156CREATE TABLE [dbo].[OfficeAssignment](
157 [InstructorID] [int] NOT NULL,
158 [Location] [nvarchar](50) NOT NULL,
159 [Timestamp] [timestamp] NOT NULL,
160 CONSTRAINT [PK_OfficeAssignment] PRIMARY KEY CLUSTERED
161(
162 [InstructorID] ASC
163)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
164) ON [PRIMARY]
165END
166GO
167
168-- Define the relationship between OnsiteCourse and Course.
169IF NOT EXISTS (SELECT * FROM sys.foreign_keys
170 WHERE object_id = OBJECT_ID(N'[dbo].[FK_OnsiteCourse_Course]')
171 AND parent_object_id = OBJECT_ID(N'[dbo].[OnsiteCourse]'))
172ALTER TABLE [dbo].[OnsiteCourse] WITH CHECK ADD
173 CONSTRAINT [FK_OnsiteCourse_Course] FOREIGN KEY([CourseID])
174REFERENCES [dbo].[Course] ([CourseID])
175GO
176ALTER TABLE [dbo].[OnsiteCourse] CHECK
177 CONSTRAINT [FK_OnsiteCourse_Course]
178GO
179
180-- Define the relationship between OnlineCourse and Course.
181IF NOT EXISTS (SELECT * FROM sys.foreign_keys
182 WHERE object_id = OBJECT_ID(N'[dbo].[FK_OnlineCourse_Course]')
183 AND parent_object_id = OBJECT_ID(N'[dbo].[OnlineCourse]'))
184ALTER TABLE [dbo].[OnlineCourse] WITH CHECK ADD
185 CONSTRAINT [FK_OnlineCourse_Course] FOREIGN KEY([CourseID])
186REFERENCES [dbo].[Course] ([CourseID])
187GO
188ALTER TABLE [dbo].[OnlineCourse] CHECK
189 CONSTRAINT [FK_OnlineCourse_Course]
190GO
191
192-- Define the relationship between StudentGrade and Course.
193IF NOT EXISTS (SELECT * FROM sys.foreign_keys
194 WHERE object_id = OBJECT_ID(N'[dbo].[FK_StudentGrade_Course]')
195 AND parent_object_id = OBJECT_ID(N'[dbo].[StudentGrade]'))
196ALTER TABLE [dbo].[StudentGrade] WITH CHECK ADD
197 CONSTRAINT [FK_StudentGrade_Course] FOREIGN KEY([CourseID])
198REFERENCES [dbo].[Course] ([CourseID])
199GO
200ALTER TABLE [dbo].[StudentGrade] CHECK
201 CONSTRAINT [FK_StudentGrade_Course]
202GO
203
204--Define the relationship between StudentGrade and Student.
205IF NOT EXISTS (SELECT * FROM sys.foreign_keys
206 WHERE object_id = OBJECT_ID(N'[dbo].[FK_StudentGrade_Student]')
207 AND parent_object_id = OBJECT_ID(N'[dbo].[StudentGrade]'))
208ALTER TABLE [dbo].[StudentGrade] WITH CHECK ADD
209 CONSTRAINT [FK_StudentGrade_Student] FOREIGN KEY([StudentID])
210REFERENCES [dbo].[Person] ([PersonID])
211GO
212ALTER TABLE [dbo].[StudentGrade] CHECK
213 CONSTRAINT [FK_StudentGrade_Student]
214GO
215
216-- Define the relationship between CourseInstructor and Course.
217IF NOT EXISTS (SELECT * FROM sys.foreign_keys
218 WHERE object_id = OBJECT_ID(N'[dbo].[FK_CourseInstructor_Course]')
219 AND parent_object_id = OBJECT_ID(N'[dbo].[CourseInstructor]'))
220ALTER TABLE [dbo].[CourseInstructor] WITH CHECK ADD
221 CONSTRAINT [FK_CourseInstructor_Course] FOREIGN KEY([CourseID])
222REFERENCES [dbo].[Course] ([CourseID])
223GO
224ALTER TABLE [dbo].[CourseInstructor] CHECK
225 CONSTRAINT [FK_CourseInstructor_Course]
226GO
227
228-- Define the relationship between CourseInstructor and Person.
229IF NOT EXISTS (SELECT * FROM sys.foreign_keys
230 WHERE object_id = OBJECT_ID(N'[dbo].[FK_CourseInstructor_Person]')
231 AND parent_object_id = OBJECT_ID(N'[dbo].[CourseInstructor]'))
232ALTER TABLE [dbo].[CourseInstructor] WITH CHECK ADD
233 CONSTRAINT [FK_CourseInstructor_Person] FOREIGN KEY([PersonID])
234REFERENCES [dbo].[Person] ([PersonID])
235GO
236ALTER TABLE [dbo].[CourseInstructor] CHECK
237 CONSTRAINT [FK_CourseInstructor_Person]
238GO
239
240-- Define the relationship between Course and Department.
241IF NOT EXISTS (SELECT * FROM sys.foreign_keys
242 WHERE object_id = OBJECT_ID(N'[dbo].[FK_Course_Department]')
243 AND parent_object_id = OBJECT_ID(N'[dbo].[Course]'))
244ALTER TABLE [dbo].[Course] WITH CHECK ADD
245 CONSTRAINT [FK_Course_Department] FOREIGN KEY([DepartmentID])
246REFERENCES [dbo].[Department] ([DepartmentID])
247GO
248ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Department]
249GO
250
251--Define the relationship between OfficeAssignment and Person.
252IF NOT EXISTS (SELECT * FROM sys.foreign_keys
253 WHERE object_id = OBJECT_ID(N'[dbo].[FK_OfficeAssignment_Person]')
254 AND parent_object_id = OBJECT_ID(N'[dbo].[OfficeAssignment]'))
255ALTER TABLE [dbo].[OfficeAssignment] WITH CHECK ADD
256 CONSTRAINT [FK_OfficeAssignment_Person] FOREIGN KEY([InstructorID])
257REFERENCES [dbo].[Person] ([PersonID])
258GO
259ALTER TABLE [dbo].[OfficeAssignment] CHECK
260 CONSTRAINT [FK_OfficeAssignment_Person]
261GO
262
263
264
265-- Insert data into the Person table.
266USE DN_DL_School
267GO
268SET IDENTITY_INSERT dbo.Person ON
269GO
270INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
271VALUES (1, 'Abercrombie', 'Kim', '1995-03-11', null);
272INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
273VALUES (2, 'Barzdukas', 'Gytis', null, '2005-09-01');
274INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
275VALUES (3, 'Justice', 'Peggy', null, '2001-09-01');
276INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
277VALUES (4, 'Fakhouri', 'Fadi', '2002-08-06', null);
278INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
279VALUES (5, 'Harui', 'Roger', '1998-07-01', null);
280INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
281VALUES (6, 'Li', 'Yan', null, '2002-09-01');
282INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
283VALUES (7, 'Norman', 'Laura', null, '2003-09-01');
284INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
285VALUES (8, 'Olivotto', 'Nino', null, '2005-09-01');
286INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
287VALUES (9, 'Tang', 'Wayne', null, '2005-09-01');
288INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
289VALUES (10, 'Alonso', 'Meredith', null, '2002-09-01');
290INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
291VALUES (11, 'Lopez', 'Sophia', null, '2004-09-01');
292INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
293VALUES (12, 'Browning', 'Meredith', null, '2000-09-01');
294INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
295VALUES (13, 'Anand', 'Arturo', null, '2003-09-01');
296INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
297VALUES (14, 'Walker', 'Alexandra', null, '2000-09-01');
298INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
299VALUES (15, 'Powell', 'Carson', null, '2004-09-01');
300INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
301VALUES (16, 'Jai', 'Damien', null, '2001-09-01');
302INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
303VALUES (17, 'Carlson', 'Robyn', null, '2005-09-01');
304INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
305VALUES (18, 'Zheng', 'Roger', '2004-02-12', null);
306INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
307VALUES (19, 'Bryant', 'Carson', null, '2001-09-01');
308INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
309VALUES (20, 'Suarez', 'Robyn', null, '2004-09-01');
310INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
311VALUES (21, 'Holt', 'Roger', null, '2004-09-01');
312INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
313VALUES (22, 'Alexander', 'Carson', null, '2005-09-01');
314INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
315VALUES (23, 'Morgan', 'Isaiah', null, '2001-09-01');
316INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
317VALUES (24, 'Martin', 'Randall', null, '2005-09-01');
318INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
319VALUES (25, 'Kapoor', 'Candace', '2001-01-15', null);
320INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
321VALUES (26, 'Rogers', 'Cody', null, '2002-09-01');
322INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
323VALUES (27, 'Serrano', 'Stacy', '1999-06-01', null);
324INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
325VALUES (28, 'White', 'Anthony', null, '2001-09-01');
326INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
327VALUES (29, 'Griffin', 'Rachel', null, '2004-09-01');
328INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
329VALUES (30, 'Shan', 'Alicia', null, '2003-09-01');
330INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
331VALUES (31, 'Stewart', 'Jasmine', '1997-10-12', null);
332INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
333VALUES (32, 'Xu', 'Kristen', '2001-7-23', null);
334INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
335VALUES (33, 'Gao', 'Erica', null, '2003-01-30');
336INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
337VALUES (34, 'Van Houten', 'Roger', '2000-12-07', null);
338GO
339SET IDENTITY_INSERT dbo.Person OFF
340GO
341
342-- Insert data into the Department table.
343INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)
344VALUES (1, 'Engineering', 350000.00, '2007-09-01', 2);
345INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)
346VALUES (2, 'English', 120000.00, '2007-09-01', 6);
347INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)
348VALUES (4, 'Economics', 200000.00, '2007-09-01', 4);
349INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)
350VALUES (7, 'Mathematics', 250000.00, '2007-09-01', 3);
351GO
352
353
354-- Insert data into the Course table.
355INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
356VALUES (1050, 'Chemistry', 4, 1);
357INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
358VALUES (1061, 'Physics', 4, 1);
359INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
360VALUES (1045, 'Calculus', 4, 7);
361INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
362VALUES (2030, 'Poetry', 2, 2);
363INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
364VALUES (2021, 'Composition', 3, 2);
365INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
366VALUES (2042, 'Literature', 4, 2);
367INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
368VALUES (4022, 'Microeconomics', 3, 4);
369INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
370VALUES (4041, 'Macroeconomics', 3, 4);
371INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
372VALUES (4061, 'Quantitative', 2, 4);
373INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
374VALUES (3141, 'Trigonometry', 4, 7);
375GO
376
377-- Insert data into the OnlineCourse table.
378INSERT INTO dbo.OnlineCourse (CourseID, URL)
379VALUES (2030, 'http://www.fineartschool.net/Poetry');
380INSERT INTO dbo.OnlineCourse (CourseID, URL)
381VALUES (2021, 'http://www.fineartschool.net/Composition');
382INSERT INTO dbo.OnlineCourse (CourseID, URL)
383VALUES (4041, 'http://www.fineartschool.net/Macroeconomics');
384INSERT INTO dbo.OnlineCourse (CourseID, URL)
385VALUES (3141, 'http://www.fineartschool.net/Trigonometry');
386
387--Insert data into OnsiteCourse table.
388INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
389VALUES (1050, '123 Smith', '4', '2018-05-11 11:30');
390INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
391VALUES (1061, '234 Smith', '11', '2018-12-20 13:15');
392INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
393VALUES (1045, '121 Smith','2', '2018-02-19 15:30');
394INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
395VALUES (4061, '22 Williams', '3', '2018-11-11 11:15');
396INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
397VALUES (2042, '225 Adams', '7', '2018-06-18 11:00');
398INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
399VALUES (4022, '23 Williams', '7', '2018-05-21 9:00');
400
401-- Insert data into the CourseInstructor table.
402INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
403VALUES (1050, 1);
404INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
405VALUES (1061, 31);
406INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
407VALUES (1045, 5);
408INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
409VALUES (2030, 4);
410INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
411VALUES (2021, 27);
412INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
413VALUES (2042, 25);
414INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
415VALUES (4022, 18);
416INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
417VALUES (4041, 32);
418INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
419VALUES (4061, 34);
420GO
421
422--Insert data into the OfficeAssignment table.
423INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
424VALUES (1, '17 Smith');
425INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
426VALUES (4, '29 Adams');
427INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
428VALUES (5, '37 Williams');
429INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
430VALUES (18, '143 Smith');
431INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
432VALUES (25, '57 Adams');
433INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
434VALUES (27, '271 Williams');
435INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
436VALUES (31, '131 Smith');
437INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
438VALUES (32, '203 Williams');
439INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
440VALUES (34, '213 Smith');
441
442-- Insert data into the StudentGrade table.
443INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
444VALUES (2021, 2, 4);
445INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
446VALUES (2030, 2, 3.5);
447INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
448VALUES (2021, 3, 3);
449INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
450VALUES (2030, 3, 4);
451INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
452VALUES (2021, 6, 2.5);
453INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
454VALUES (2042, 6, 3.5);
455INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
456VALUES (2021, 7, 3.5);
457INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
458VALUES (2042, 7, 4);
459INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
460VALUES (2021, 8, 3);
461INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
462VALUES (2042, 8, 3);
463INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
464VALUES (4041, 9, 3.5);
465INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
466VALUES (4041, 10, null);
467INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
468VALUES (4041, 11, 2.5);
469INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
470VALUES (4041, 12, null);
471INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
472VALUES (4061, 12, null);
473INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
474VALUES (4022, 14, 3);
475INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
476VALUES (4022, 13, 4);
477INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
478VALUES (4061, 13, 4);
479INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
480VALUES (4041, 14, 3);
481INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
482VALUES (4022, 15, 2.5);
483INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
484VALUES (4022, 16, 2);
485INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
486VALUES (4022, 17, null);
487INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
488VALUES (4022, 19, 3.5);
489INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
490VALUES (4061, 20, 4);
491INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
492VALUES (4061, 21, 2);
493INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
494VALUES (4022, 22, 3);
495INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
496VALUES (4041, 22, 3.5);
497INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
498VALUES (4061, 22, 2.5);
499INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
500VALUES (4022, 23, 3);
501INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
502VALUES (1045, 23, 1.5);
503INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
504VALUES (1061, 24, 4);
505INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
506VALUES (1061, 25, 3);
507INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
508VALUES (1050, 26, 3.5);
509INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
510VALUES (1061, 26, 3);
511INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
512VALUES (1061, 27, 3);
513INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
514VALUES (1045, 28, 2.5);
515INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
516VALUES (1050, 28, 3.5);
517INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
518VALUES (1061, 29, 4);
519INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
520VALUES (1050, 30, 3.5);
521INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
522VALUES (1061, 30, 4);
523GO