· 8 years ago · May 07, 2018, 09:28 AM
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 School;
11GO
12
13-- Create the School database.
14CREATE DATABASE School;
15GO
16
17-- Specify a simple recovery model
18-- to keep the log growth to a minimum.
19ALTER DATABASE School
20 SET RECOVERY SIMPLE;
21GO
22
23USE 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_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] [nvarchar](50) 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_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-- Create InsertOfficeAssignment stored procedure.
264IF NOT EXISTS (SELECT * FROM sys.objects
265 WHERE object_id = OBJECT_ID(N'[dbo].[InsertOfficeAssignment]')
266 AND type in (N'P', N'PC'))
267BEGIN
268EXEC dbo.sp_executesql @statement = N'
269CREATE PROCEDURE [dbo].[InsertOfficeAssignment]
270 @InstructorID int,
271 @Location nvarchar(50)
272 AS
273 INSERT INTO dbo.OfficeAssignment (InstructorID, Location)
274 VALUES (@InstructorID, @Location);
275 IF @@ROWCOUNT > 0
276 BEGIN
277 SELECT [Timestamp] FROM OfficeAssignment
278 WHERE InstructorID=@InstructorID;
279 END
280'
281END
282GO
283
284--Create the UpdateOfficeAssignment stored procedure.
285IF NOT EXISTS (SELECT * FROM sys.objects
286 WHERE object_id = OBJECT_ID(N'[dbo].[UpdateOfficeAssignment]')
287 AND type in (N'P', N'PC'))
288BEGIN
289EXEC dbo.sp_executesql @statement = N'
290CREATE PROCEDURE [dbo].[UpdateOfficeAssignment]
291 @InstructorID int,
292 @Location nvarchar(50),
293 @OrigTimestamp timestamp
294 AS
295 UPDATE OfficeAssignment SET Location=@Location
296 WHERE InstructorID=@InstructorID AND [Timestamp]=@OrigTimestamp;
297 IF @@ROWCOUNT > 0
298 BEGIN
299 SELECT [Timestamp] FROM OfficeAssignment
300 WHERE InstructorID=@InstructorID;
301 END
302'
303END
304GO
305
306-- Create the DeleteOfficeAssignment stored procedure.
307IF NOT EXISTS (SELECT * FROM sys.objects
308 WHERE object_id = OBJECT_ID(N'[dbo].[DeleteOfficeAssignment]')
309 AND type in (N'P', N'PC'))
310BEGIN
311EXEC dbo.sp_executesql @statement = N'
312CREATE PROCEDURE [dbo].[DeleteOfficeAssignment]
313 @InstructorID int
314 AS
315 DELETE FROM OfficeAssignment
316 WHERE InstructorID=@InstructorID;
317'
318END
319GO
320
321-- Create the DeletePerson stored procedure.
322IF NOT EXISTS (SELECT * FROM sys.objects
323 WHERE object_id = OBJECT_ID(N'[dbo].[DeletePerson]')
324 AND type in (N'P', N'PC'))
325BEGIN
326EXEC dbo.sp_executesql @statement = N'
327CREATE PROCEDURE [dbo].[DeletePerson]
328 @PersonID int
329 AS
330 DELETE FROM Person WHERE PersonID = @PersonID;
331'
332END
333GO
334
335-- Create the UpdatePerson stored procedure.
336IF NOT EXISTS (SELECT * FROM sys.objects
337 WHERE object_id = OBJECT_ID(N'[dbo].[UpdatePerson]')
338 AND type in (N'P', N'PC'))
339BEGIN
340EXEC dbo.sp_executesql @statement = N'
341CREATE PROCEDURE [dbo].[UpdatePerson]
342 @PersonID int,
343 @LastName nvarchar(50),
344 @FirstName nvarchar(50),
345 @HireDate datetime,
346 @EnrollmentDate datetime
347 AS
348 UPDATE Person SET LastName=@LastName,
349 FirstName=@FirstName,
350 HireDate=@HireDate,
351 EnrollmentDate=@EnrollmentDate
352 WHERE PersonID=@PersonID;
353'
354END
355GO
356
357-- Create the InsertPerson stored procedure.
358IF NOT EXISTS (SELECT * FROM sys.objects
359 WHERE object_id = OBJECT_ID(N'[dbo].[InsertPerson]')
360 AND type in (N'P', N'PC'))
361BEGIN
362EXEC dbo.sp_executesql @statement = N'
363CREATE PROCEDURE [dbo].[InsertPerson]
364 @LastName nvarchar(50),
365 @FirstName nvarchar(50),
366 @HireDate datetime,
367 @EnrollmentDate datetime
368 AS
369 INSERT INTO dbo.Person (LastName,
370 FirstName,
371 HireDate,
372 EnrollmentDate)
373 VALUES (@LastName,
374 @FirstName,
375 @HireDate,
376 @EnrollmentDate);
377 SELECT SCOPE_IDENTITY() as NewPersonID;
378'
379END
380GO
381
382-- Create GetStudentGrades stored procedure.
383IF NOT EXISTS (SELECT * FROM sys.objects
384 WHERE object_id = OBJECT_ID(N'[dbo].[GetStudentGrades]')
385 AND type in (N'P', N'PC'))
386BEGIN
387EXEC dbo.sp_executesql @statement = N'
388CREATE PROCEDURE [dbo].[GetStudentGrades]
389 @StudentID int
390 AS
391 SELECT EnrollmentID, Grade, CourseID, StudentID FROM dbo.StudentGrade
392 WHERE StudentID = @StudentID
393'
394END
395GO
396
397-- Create GetDepartmentName stored procedure.
398IF NOT EXISTS (SELECT * FROM sys.objects
399 WHERE object_id = OBJECT_ID(N'[dbo].[GetDepartmentName]')
400 AND type in (N'P', N'PC'))
401BEGIN
402EXEC dbo.sp_executesql @statement = N'
403CREATE PROCEDURE [dbo].[GetDepartmentName]
404 @ID int,
405 @Name nvarchar(50) OUTPUT
406 AS
407 SELECT @Name = Name FROM Department
408 WHERE DepartmentID = @ID
409'
410END
411GO
412
413-- Insert data into the Person table.
414USE School
415GO
416SET IDENTITY_INSERT dbo.Person ON
417GO
418INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
419VALUES (1, 'Abercrombie', 'Kim', '1995-03-11', null);
420INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
421VALUES (2, 'Barzdukas', 'Gytis', null, '2005-09-01');
422INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
423VALUES (3, 'Justice', 'Peggy', null, '2001-09-01');
424INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
425VALUES (4, 'Fakhouri', 'Fadi', '2002-08-06', null);
426INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
427VALUES (5, 'Harui', 'Roger', '1998-07-01', null);
428INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
429VALUES (6, 'Li', 'Yan', null, '2002-09-01');
430INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
431VALUES (7, 'Norman', 'Laura', null, '2003-09-01');
432INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
433VALUES (8, 'Olivotto', 'Nino', null, '2005-09-01');
434INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
435VALUES (9, 'Tang', 'Wayne', null, '2005-09-01');
436INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
437VALUES (10, 'Alonso', 'Meredith', null, '2002-09-01');
438INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
439VALUES (11, 'Lopez', 'Sophia', null, '2004-09-01');
440INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
441VALUES (12, 'Browning', 'Meredith', null, '2000-09-01');
442INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
443VALUES (13, 'Anand', 'Arturo', null, '2003-09-01');
444INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
445VALUES (14, 'Walker', 'Alexandra', null, '2000-09-01');
446INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
447VALUES (15, 'Powell', 'Carson', null, '2004-09-01');
448INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
449VALUES (16, 'Jai', 'Damien', null, '2001-09-01');
450INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
451VALUES (17, 'Carlson', 'Robyn', null, '2005-09-01');
452INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
453VALUES (18, 'Zheng', 'Roger', '2004-02-12', null);
454INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
455VALUES (19, 'Bryant', 'Carson', null, '2001-09-01');
456INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
457VALUES (20, 'Suarez', 'Robyn', null, '2004-09-01');
458INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
459VALUES (21, 'Holt', 'Roger', null, '2004-09-01');
460INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
461VALUES (22, 'Alexander', 'Carson', null, '2005-09-01');
462INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
463VALUES (23, 'Morgan', 'Isaiah', null, '2001-09-01');
464INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
465VALUES (24, 'Martin', 'Randall', null, '2005-09-01');
466INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
467VALUES (25, 'Kapoor', 'Candace', '2001-01-15', null);
468INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
469VALUES (26, 'Rogers', 'Cody', null, '2002-09-01');
470INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
471VALUES (27, 'Serrano', 'Stacy', '1999-06-01', null);
472INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
473VALUES (28, 'White', 'Anthony', null, '2001-09-01');
474INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
475VALUES (29, 'Griffin', 'Rachel', null, '2004-09-01');
476INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
477VALUES (30, 'Shan', 'Alicia', null, '2003-09-01');
478INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
479VALUES (31, 'Stewart', 'Jasmine', '1997-10-12', null);
480INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
481VALUES (32, 'Xu', 'Kristen', '2001-7-23', null);
482INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
483VALUES (33, 'Gao', 'Erica', null, '2003-01-30');
484INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
485VALUES (34, 'Van Houten', 'Roger', '2000-12-07', null);
486GO
487SET IDENTITY_INSERT dbo.Person OFF
488GO
489
490-- Insert data into the Department table.
491INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)
492VALUES (1, 'Engineering', 350000.00, '2007-09-01', 2);
493INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)
494VALUES (2, 'English', 120000.00, '2007-09-01', 6);
495INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)
496VALUES (4, 'Economics', 200000.00, '2007-09-01', 4);
497INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)
498VALUES (7, 'Mathematics', 250000.00, '2007-09-01', 3);
499GO
500
501
502-- Insert data into the Course table.
503INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
504VALUES (1050, 'Chemistry', 4, 1);
505INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
506VALUES (1061, 'Physics', 4, 1);
507INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
508VALUES (1045, 'Calculus', 4, 7);
509INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
510VALUES (2030, 'Poetry', 2, 2);
511INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
512VALUES (2021, 'Composition', 3, 2);
513INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
514VALUES (2042, 'Literature', 4, 2);
515INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
516VALUES (4022, 'Microeconomics', 3, 4);
517INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
518VALUES (4041, 'Macroeconomics', 3, 4);
519INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
520VALUES (4061, 'Quantitative', 2, 4);
521INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
522VALUES (3141, 'Trigonometry', 4, 7);
523GO
524
525-- Insert data into the OnlineCourse table.
526INSERT INTO dbo.OnlineCourse (CourseID, URL)
527VALUES (2030, 'http://www.fineartschool.net/Poetry');
528INSERT INTO dbo.OnlineCourse (CourseID, URL)
529VALUES (2021, 'http://www.fineartschool.net/Composition');
530INSERT INTO dbo.OnlineCourse (CourseID, URL)
531VALUES (4041, 'http://www.fineartschool.net/Macroeconomics');
532INSERT INTO dbo.OnlineCourse (CourseID, URL)
533VALUES (3141, 'http://www.fineartschool.net/Trigonometry');
534
535--Insert data into OnsiteCourse table.
536INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
537VALUES (1050, '123 Smith', 'MTWH', '11:30');
538INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
539VALUES (1061, '234 Smith', 'TWHF', '13:15');
540INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
541VALUES (1045, '121 Smith','MWHF', '15:30');
542INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
543VALUES (4061, '22 Williams', 'TH', '11:15');
544INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
545VALUES (2042, '225 Adams', 'MTWH', '11:00');
546INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
547VALUES (4022, '23 Williams', 'MWF', '9:00');
548
549-- Insert data into the CourseInstructor table.
550INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
551VALUES (1050, 1);
552INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
553VALUES (1061, 31);
554INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
555VALUES (1045, 5);
556INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
557VALUES (2030, 4);
558INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
559VALUES (2021, 27);
560INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
561VALUES (2042, 25);
562INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
563VALUES (4022, 18);
564INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
565VALUES (4041, 32);
566INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
567VALUES (4061, 34);
568GO
569
570--Insert data into the OfficeAssignment table.
571INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
572VALUES (1, '17 Smith');
573INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
574VALUES (4, '29 Adams');
575INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
576VALUES (5, '37 Williams');
577INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
578VALUES (18, '143 Smith');
579INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
580VALUES (25, '57 Adams');
581INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
582VALUES (27, '271 Williams');
583INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
584VALUES (31, '131 Smith');
585INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
586VALUES (32, '203 Williams');
587INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
588VALUES (34, '213 Smith');
589
590-- Insert data into the StudentGrade table.
591INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
592VALUES (2021, 2, 4);
593INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
594VALUES (2030, 2, 3.5);
595INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
596VALUES (2021, 3, 3);
597INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
598VALUES (2030, 3, 4);
599INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
600VALUES (2021, 6, 2.5);
601INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
602VALUES (2042, 6, 3.5);
603INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
604VALUES (2021, 7, 3.5);
605INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
606VALUES (2042, 7, 4);
607INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
608VALUES (2021, 8, 3);
609INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
610VALUES (2042, 8, 3);
611INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
612VALUES (4041, 9, 3.5);
613INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
614VALUES (4041, 10, null);
615INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
616VALUES (4041, 11, 2.5);
617INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
618VALUES (4041, 12, null);
619INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
620VALUES (4061, 12, null);
621INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
622VALUES (4022, 14, 3);
623INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
624VALUES (4022, 13, 4);
625INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
626VALUES (4061, 13, 4);
627INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
628VALUES (4041, 14, 3);
629INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
630VALUES (4022, 15, 2.5);
631INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
632VALUES (4022, 16, 2);
633INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
634VALUES (4022, 17, null);
635INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
636VALUES (4022, 19, 3.5);
637INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
638VALUES (4061, 20, 4);
639INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
640VALUES (4061, 21, 2);
641INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
642VALUES (4022, 22, 3);
643INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
644VALUES (4041, 22, 3.5);
645INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
646VALUES (4061, 22, 2.5);
647INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
648VALUES (4022, 23, 3);
649INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
650VALUES (1045, 23, 1.5);
651INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
652VALUES (1061, 24, 4);
653INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
654VALUES (1061, 25, 3);
655INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
656VALUES (1050, 26, 3.5);
657INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
658VALUES (1061, 26, 3);
659INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
660VALUES (1061, 27, 3);
661INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
662VALUES (1045, 28, 2.5);
663INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
664VALUES (1050, 28, 3.5);
665INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
666VALUES (1061, 29, 4);
667INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
668VALUES (1050, 30, 3.5);
669INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
670VALUES (1061, 30, 4);
671GO