SIGN UP MEMBER LOGIN:
   
ARTICLE

Create a database using Visual Studio 2010

Posted by Praveen Kumar Articles | SQL Server January 27, 2010
This article and snippet shows how to create a SQL Server database using SQL Script in Visual Studio 2010.
Reader Level:

School database snippet is provided by Microsoft in MSDN documentation.

The following code snippet creates a School database, tables, and inserts data into the tables. The database schema looks like following:

SchoolDB.gif

You can create a database using Visual Studio 2010.

If you create a project in Visual Studio 2010, you will notice a Data menu item at the top.

If you click on this menu item, you will see Transact-SQL Editor.

SchoolDBImg1.gif


Click on this menu item will ask you to connect to a database server. Once connected to a server, you will see an editor.

Copy and paste the following SQL script to the editor and click on the Execute SQL icon as you see in the following image.

SchoolDBImg2.gif

Once successful, you will see School database is created.

-----------------------------------------------------------------------------

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

USE [master];

GO

 

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'School')

        DROP DATABASE School;

GO

 

-- Create the School database.

CREATE DATABASE School;

GO

 

-- Specify a simple recovery model

-- to keep the log growth to a minimum.

ALTER DATABASE School

        SET RECOVERY SIMPLE;

GO

 

USE School;

GO

 

-- Create the Department table.

IF NOT EXISTS (SELECT * FROM sys.objects

               WHERE object_id = OBJECT_ID(N'[dbo].[Department]')

               AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[Department](

        [DepartmentID] [int] NOT NULL,

        [Name] [nvarchar](50) NOT NULL,

        [Budget] [money] NOT NULL,

        [StartDate] [datetime] NOT NULL,

        [Administrator] [int] NULL,

 CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED

(

        [DepartmentID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

END

GO

 

-- Create the Person table.

IF NOT EXISTS (SELECT * FROM sys.objects

               WHERE object_id = OBJECT_ID(N'[dbo].[Person]')

               AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[Person](

        [PersonID] [int] IDENTITY(1,1) NOT NULL,

        [LastName] [nvarchar](50) NOT NULL,

        [FirstName] [nvarchar](50) NOT NULL,

        [HireDate] [datetime] NULL,

        [EnrollmentDate] [datetime] NULL,

 CONSTRAINT [PK_School.Student] PRIMARY KEY CLUSTERED

(

        [PersonID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

END

GO

 

-- Create the OnsiteCourse table.

IF NOT EXISTS (SELECT * FROM sys.objects

               WHERE object_id = OBJECT_ID(N'[dbo].[OnsiteCourse]')

               AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[OnsiteCourse](

        [CourseID] [int] NOT NULL,

        [Location] [nvarchar](50) NOT NULL,

        [Days] [nvarchar](50) NOT NULL,

        [Time] [smalldatetime] NOT NULL,

 CONSTRAINT [PK_OnsiteCourse] PRIMARY KEY CLUSTERED

(

        [CourseID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

END

GO

 

-- Create the OnlineCourse table.

IF NOT EXISTS (SELECT * FROM sys.objects

               WHERE object_id = OBJECT_ID(N'[dbo].[OnlineCourse]')

               AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[OnlineCourse](

        [CourseID] [int] NOT NULL,

        [URL] [nvarchar](100) NOT NULL,

 CONSTRAINT [PK_OnlineCourse] PRIMARY KEY CLUSTERED

(

        [CourseID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

END

GO

 

--Create the CourseGrade table.

IF NOT EXISTS (SELECT * FROM sys.objects

               WHERE object_id = OBJECT_ID(N'[dbo].[CourseGrade]')

               AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[CourseGrade](

        [EnrollmentID] [int] IDENTITY(1,1) NOT NULL,

        [CourseID] [int] NOT NULL,

        [StudentID] [int] NOT NULL,

        [Grade] [decimal](3, 2) NULL,

 CONSTRAINT [PK_CourseGrade] PRIMARY KEY CLUSTERED

(

        [EnrollmentID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

END

GO

 

-- Create the CourseInstructor table.

IF NOT EXISTS (SELECT * FROM sys.objects

               WHERE object_id = OBJECT_ID(N'[dbo].[CourseInstructor]')

               AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[CourseInstructor](

        [CourseID] [int] NOT NULL,

        [PersonID] [int] NOT NULL,

 CONSTRAINT [PK_CourseInstructor] PRIMARY KEY CLUSTERED

(

        [CourseID] ASC,

        [PersonID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

END

GO

 

-- Create the Course table.

IF NOT EXISTS (SELECT * FROM sys.objects

               WHERE object_id = OBJECT_ID(N'[dbo].[Course]')

               AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[Course](

        [CourseID] [int] NOT NULL,

        [Title] [nvarchar](100) NOT NULL,

        [Credits] [int] NOT NULL,

        [DepartmentID] [int] NOT NULL,

 CONSTRAINT [PK_School.Course] PRIMARY KEY CLUSTERED

(

        [CourseID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

END

GO

 

-- Create the OfficeAssignment table.

IF NOT EXISTS (SELECT * FROM sys.objects

               WHERE object_id = OBJECT_ID(N'[dbo].[OfficeAssignment]')

               AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[OfficeAssignment](

        [InstructorID] [int] NOT NULL,

        [Location] [nvarchar](50) NOT NULL,

        [Timestamp] [timestamp] NOT NULL,

 CONSTRAINT [PK_OfficeAssignment] PRIMARY KEY CLUSTERED

(

        [InstructorID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

END

GO

 

-- Define the relationship between OnsiteCourse and Course.

IF NOT EXISTS (SELECT * FROM sys.foreign_keys

       WHERE object_id = OBJECT_ID(N'[dbo].[FK_OnsiteCourse_Course]')

       AND parent_object_id = OBJECT_ID(N'[dbo].[OnsiteCourse]'))

ALTER TABLE [dbo].[OnsiteCourse]  WITH CHECK ADD 

       CONSTRAINT [FK_OnsiteCourse_Course] FOREIGN KEY([CourseID])

REFERENCES [dbo].[Course] ([CourseID])

GO

ALTER TABLE [dbo].[OnsiteCourse] CHECK

       CONSTRAINT [FK_OnsiteCourse_Course]

GO

 

-- Define the relationship between OnlineCourse and Course.

IF NOT EXISTS (SELECT * FROM sys.foreign_keys

       WHERE object_id = OBJECT_ID(N'[dbo].[FK_OnlineCourse_Course]')

       AND parent_object_id = OBJECT_ID(N'[dbo].[OnlineCourse]'))

ALTER TABLE [dbo].[OnlineCourse]  WITH CHECK ADD 

       CONSTRAINT [FK_OnlineCourse_Course] FOREIGN KEY([CourseID])

REFERENCES [dbo].[Course] ([CourseID])

GO

ALTER TABLE [dbo].[OnlineCourse] CHECK

       CONSTRAINT [FK_OnlineCourse_Course]

GO

 

-- Define the relationship between CourseGrade and Course.

IF NOT EXISTS (SELECT * FROM sys.foreign_keys

       WHERE object_id = OBJECT_ID(N'[dbo].[FK_CourseGrade_Course]')

       AND parent_object_id = OBJECT_ID(N'[dbo].[CourseGrade]'))

ALTER TABLE [dbo].[CourseGrade]  WITH CHECK ADD 

       CONSTRAINT [FK_CourseGrade_Course] FOREIGN KEY([CourseID])

REFERENCES [dbo].[Course] ([CourseID])

GO

ALTER TABLE [dbo].[CourseGrade] CHECK

       CONSTRAINT [FK_CourseGrade_Course]

GO

 

--Define the relationship between CourseGrade and Student.

IF NOT EXISTS (SELECT * FROM sys.foreign_keys

       WHERE object_id = OBJECT_ID(N'[dbo].[FK_CourseGrade_Student]')

       AND parent_object_id = OBJECT_ID(N'[dbo].[CourseGrade]'))

ALTER TABLE [dbo].[CourseGrade]  WITH CHECK ADD 

       CONSTRAINT [FK_CourseGrade_Student] FOREIGN KEY([StudentID])

REFERENCES [dbo].[Person] ([PersonID])

GO

ALTER TABLE [dbo].[CourseGrade] CHECK

       CONSTRAINT [FK_CourseGrade_Student]

GO

 

-- Define the relationship between CourseInstructor and Course.

IF NOT EXISTS (SELECT * FROM sys.foreign_keys

   WHERE object_id = OBJECT_ID(N'[dbo].[FK_CourseInstructor_Course]')

   AND parent_object_id = OBJECT_ID(N'[dbo].[CourseInstructor]'))

ALTER TABLE [dbo].[CourseInstructor]  WITH CHECK ADD 

   CONSTRAINT [FK_CourseInstructor_Course] FOREIGN KEY([CourseID])

REFERENCES [dbo].[Course] ([CourseID])

GO

ALTER TABLE [dbo].[CourseInstructor] CHECK

   CONSTRAINT [FK_CourseInstructor_Course]

GO

 

-- Define the relationship between CourseInstructor and Person.

IF NOT EXISTS (SELECT * FROM sys.foreign_keys

   WHERE object_id = OBJECT_ID(N'[dbo].[FK_CourseInstructor_Person]')

   AND parent_object_id = OBJECT_ID(N'[dbo].[CourseInstructor]'))

ALTER TABLE [dbo].[CourseInstructor]  WITH CHECK ADD 

   CONSTRAINT [FK_CourseInstructor_Person] FOREIGN KEY([PersonID])

REFERENCES [dbo].[Person] ([PersonID])

GO

ALTER TABLE [dbo].[CourseInstructor] CHECK

   CONSTRAINT [FK_CourseInstructor_Person]

GO

 

-- Define the relationship between Course and Department.

IF NOT EXISTS (SELECT * FROM sys.foreign_keys

       WHERE object_id = OBJECT_ID(N'[dbo].[FK_Course_Department]')

       AND parent_object_id = OBJECT_ID(N'[dbo].[Course]'))

ALTER TABLE [dbo].[Course]  WITH CHECK ADD 

       CONSTRAINT [FK_Course_Department] FOREIGN KEY([DepartmentID])

REFERENCES [dbo].[Department] ([DepartmentID])

GO

ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Department]

GO

 

--Define the relationship between OfficeAssignment and Person.

IF NOT EXISTS (SELECT * FROM sys.foreign_keys

   WHERE object_id = OBJECT_ID(N'[dbo].[FK_OfficeAssignment_Person]')

   AND parent_object_id = OBJECT_ID(N'[dbo].[OfficeAssignment]'))

ALTER TABLE [dbo].[OfficeAssignment]  WITH CHECK ADD 

   CONSTRAINT [FK_OfficeAssignment_Person] FOREIGN KEY([InstructorID])

REFERENCES [dbo].[Person] ([PersonID])

GO

ALTER TABLE [dbo].[OfficeAssignment] CHECK

   CONSTRAINT [FK_OfficeAssignment_Person]

GO

 

-- Create InsertOfficeAssignment stored procedure.

IF NOT EXISTS (SELECT * FROM sys.objects

               WHERE object_id = OBJECT_ID(N'[dbo].[InsertOfficeAssignment]')

               AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[InsertOfficeAssignment]

               @InstructorID int,

               @Location nvarchar(50)

               AS

               INSERT INTO dbo.OfficeAssignment (InstructorID, Location)

               VALUES (@InstructorID, @Location);

               IF @@ROWCOUNT > 0

               BEGIN

                       SELECT [Timestamp] FROM OfficeAssignment

                               WHERE InstructorID=@InstructorID;

               END

'

END

GO

 

--Create the UpdateOfficeAssignment stored procedure.

IF NOT EXISTS (SELECT * FROM sys.objects

               WHERE object_id = OBJECT_ID(N'[dbo].[UpdateOfficeAssignment]')

               AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[UpdateOfficeAssignment]

               @InstructorID int,

               @Location nvarchar(50),

               @OrigTimestamp timestamp

               AS

               UPDATE OfficeAssignment SET Location=@Location

               WHERE InstructorID=@InstructorID AND [Timestamp]=@OrigTimestamp;

               IF @@ROWCOUNT > 0

               BEGIN

                       SELECT [Timestamp] FROM OfficeAssignment

                               WHERE InstructorID=@InstructorID;

               END

'

END

GO

 

-- Create the DeleteOfficeAssignment stored procedure.

IF NOT EXISTS (SELECT * FROM sys.objects

               WHERE object_id = OBJECT_ID(N'[dbo].[DeleteOfficeAssignment]')

               AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[DeleteOfficeAssignment]

               @InstructorID int

               AS

               DELETE FROM OfficeAssignment

               WHERE InstructorID=@InstructorID;

'

END

GO

 

-- Create the DeletePerson stored procedure.

IF NOT EXISTS (SELECT * FROM sys.objects

               WHERE object_id = OBJECT_ID(N'[dbo].[DeletePerson]')

               AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[DeletePerson]

               @PersonID int

               AS

               DELETE FROM Person WHERE PersonID = @PersonID;

'

END

GO

 

-- Create the UpdatePerson stored procedure.

IF NOT EXISTS (SELECT * FROM sys.objects

               WHERE object_id = OBJECT_ID(N'[dbo].[UpdatePerson]')

               AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[UpdatePerson]

               @PersonID int,

               @LastName nvarchar(50),

               @FirstName nvarchar(50),

               @HireDate datetime,

               @EnrollmentDate datetime

               AS

               UPDATE Person SET LastName=@LastName,

                               FirstName=@FirstName,

                               HireDate=@HireDate,

                               EnrollmentDate=@EnrollmentDate

               WHERE PersonID=@PersonID;

'

END

GO

 

-- Create the InsertPerson stored procedure.

IF NOT EXISTS (SELECT * FROM sys.objects

               WHERE object_id = OBJECT_ID(N'[dbo].[InsertPerson]')

               AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[InsertPerson]

               @LastName nvarchar(50),

               @FirstName nvarchar(50),

               @HireDate datetime,

               @EnrollmentDate datetime

               AS

               INSERT INTO dbo.Person (LastName,

                                      FirstName,

                                      HireDate,

                                      EnrollmentDate)

               VALUES (@LastName,

                       @FirstName,

                       @HireDate,

                       @EnrollmentDate);

               SELECT SCOPE_IDENTITY() as NewPersonID;

'

END

GO

 

-- Create GetStudentGrades stored procedure.

IF NOT EXISTS (SELECT * FROM sys.objects

            WHERE object_id = OBJECT_ID(N'[dbo].[GetStudentGrades]')

            AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[GetStudentGrades]

            @StudentID int

            AS

            SELECT EnrollmentID, Grade FROM dbo.CourseGrade

            WHERE StudentID = @StudentID

'

END

GO

 

-- Insert data into the Person table.

USE School

GO

SET IDENTITY_INSERT dbo.Person ON

GO

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (1, 'Abercrombie', 'Kim', '1995-03-11', null);

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (2, 'Barzdukas', 'Gytis', null, '2005-09-01');

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (3, 'Justice', 'Peggy', null, '2001-09-01');

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (4, 'Fakhouri', 'Fadi', '2002-08-06', null);

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (5, 'Harui', 'Roger', '1998-07-01', null);

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (6, 'Li', 'Yan', null, '2002-09-01');

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (7, 'Norman', 'Laura', null, '2003-09-01');

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (8, 'Olivotto', 'Nino', null, '2005-09-01');

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (9, 'Tang', 'Wayne', null, '2005-09-01');

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (10, 'Alonso', 'Meredith', null, '2002-09-01');

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (11, 'Lopez', 'Sophia', null, '2004-09-01');

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (create-a-database-using-visual-studio-2010, 'Browning', 'Meredith', null, '2000-09-01');

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (13, 'Anand', 'Arturo', null, '2003-09-01');

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (14, 'Walker', 'Alexandra', null, '2000-09-01');

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (15, 'Powell', 'Carson', null, '2004-09-01');

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (16, 'Jai', 'Damien', null, '2001-09-01');

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (17, 'Carlson', 'Robyn', null, '2005-09-01');

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (18, 'Zheng', 'Roger', '2004-02-create-a-database-using-visual-studio-2010', null);

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (19, 'Bryant', 'Carson', null, '2001-09-01');

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (20, 'Suarez', 'Robyn', null, '2004-09-01');

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (21, 'Holt', 'Roger', null, '2004-09-01');

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (22, 'Alexander', 'Carson', null, '2005-09-01');

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (23, 'Morgan', 'Isaiah', null, '2001-09-01');

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (24, 'Martin', 'Randall', null, '2005-09-01');

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (25, 'Kapoor', 'Candace', '2001-01-15', null);

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (26, 'Rogers', 'Cody', null, '2002-09-01');

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (27, 'Serrano', 'Stacy', '1999-06-01', null);

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (28, 'White', 'Anthony', null, '2001-09-01');

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (29, 'Griffin', 'Rachel', null, '2004-09-01');

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (30, 'Shan', 'Alicia', null, '2003-09-01');

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (31, 'Stewart', 'Jasmine', '1997-10-create-a-database-using-visual-studio-2010', null);

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (32, 'Xu', 'Kristen', '2001-7-23', null);

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (33, 'Gao', 'Erica', null, '2003-01-30');

INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)

VALUES (34, 'Van Houten', 'Roger', '2000-create-a-database-using-visual-studio-2010-07', null);

GO

SET IDENTITY_INSERT dbo.Person OFF

GO

 

-- Insert data into the Department table.

INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)

VALUES (1, 'Engineering', 350000.00, '2007-09-01', 2);

INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)

VALUES (2, 'English', create-a-database-using-visual-studio-20100000.00, '2007-09-01', 6);

INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)

VALUES (4, 'Economics', 200000.00, '2007-09-01', 4);

INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)

VALUES (7, 'Mathematics', 250000.00, '2007-09-01', 3);

GO

 

 

-- Insert data into the Course table.

INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)

VALUES (1050, 'Chemistry', 4, 1);

INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)

VALUES (1061, 'Physics', 4, 1);

INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)

VALUES (1045, 'Calculus', 4, 7);

INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)

VALUES (2030, 'Poetry', 2, 2);

INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)

VALUES (2021, 'Composition', 3, 2);

INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)

VALUES (2042, 'Literature', 4, 2);

INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)

VALUES (4022, 'Microeconomics', 3, 4);

INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)

VALUES (4041, 'Macroeconomics', 3, 4);

INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)

VALUES (4061, 'Quantitative', 2, 4);

INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)

VALUES (3141, 'Trigonometry', 4, 7);

GO

 

-- Insert data into the OnlineCourse table.

INSERT INTO dbo.OnlineCourse (CourseID, URL)

VALUES (2030, 'http://www.fineartschool.net/Poetry');

INSERT INTO dbo.OnlineCourse (CourseID, URL)

VALUES (2021, 'http://www.fineartschool.net/Composition');

INSERT INTO dbo.OnlineCourse (CourseID, URL)

VALUES (4041, 'http://www.fineartschool.net/Macroeconomics');

INSERT INTO dbo.OnlineCourse (CourseID, URL)

VALUES (3141, 'http://www.fineartschool.net/Trigonometry');

 

--Insert data into OnsiteCourse table.

INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])

VALUES (1050, 'create-a-database-using-visual-studio-20103 Smith', 'MTWH', '11:30');

INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])

VALUES (1061, '234 Smith', 'TWHF', '13:15');

INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])

VALUES (1045, 'create-a-database-using-visual-studio-20101 Smith','MWHF', '15:30');

INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])

VALUES (4061, '22 Williams', 'TH', '11:15');

INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])

VALUES (2042, '225 Adams', 'MTWH', '11:00');

INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])

VALUES (4022, '23 Williams', 'MWF', '9:00');

 

-- Insert data into the CourseInstructor table.

INSERT INTO dbo.CourseInstructor(CourseID, PersonID)

VALUES (1050, 1);

INSERT INTO dbo.CourseInstructor(CourseID, PersonID)

VALUES (1061, 31);

INSERT INTO dbo.CourseInstructor(CourseID, PersonID)

VALUES (1045, 5);

INSERT INTO dbo.CourseInstructor(CourseID, PersonID)

VALUES (2030, 4);

INSERT INTO dbo.CourseInstructor(CourseID, PersonID)

VALUES (2021, 27);

INSERT INTO dbo.CourseInstructor(CourseID, PersonID)

VALUES (2042, 25);

INSERT INTO dbo.CourseInstructor(CourseID, PersonID)

VALUES (4022, 18);

INSERT INTO dbo.CourseInstructor(CourseID, PersonID)

VALUES (4041, 32);

INSERT INTO dbo.CourseInstructor(CourseID, PersonID)

VALUES (4061, 34);

GO

 

--Insert data into the OfficeAssignment table.

INSERT INTO dbo.OfficeAssignment(InstructorID, Location)

VALUES (1, '17 Smith');

INSERT INTO dbo.OfficeAssignment(InstructorID, Location)

VALUES (4, '29 Adams');

INSERT INTO dbo.OfficeAssignment(InstructorID, Location)

VALUES (5, '37 Williams');

INSERT INTO dbo.OfficeAssignment(InstructorID, Location)

VALUES (18, '143 Smith');

INSERT INTO dbo.OfficeAssignment(InstructorID, Location)

VALUES (25, '57 Adams');

INSERT INTO dbo.OfficeAssignment(InstructorID, Location)

VALUES (27, '271 Williams');

INSERT INTO dbo.OfficeAssignment(InstructorID, Location)

VALUES (31, '131 Smith');

INSERT INTO dbo.OfficeAssignment(InstructorID, Location)

VALUES (32, '203 Williams');

INSERT INTO dbo.OfficeAssignment(InstructorID, Location)

VALUES (34, '213 Smith');

 

-- Insert data into the CourseGrade table.

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (2021, 2, 4);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (2030, 2, 3.5);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (2021, 3, 3);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (2030, 3, 4);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (2021, 6, 2.5);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (2042, 6, 3.5);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (2021, 7, 3.5);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (2042, 7, 4);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (2021, 8, 3);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (2042, 8, 3);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (4041, 9, 3.5);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (4041, 10, null);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (4041, 11, 2.5);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (4041, create-a-database-using-visual-studio-2010, null);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (4061, create-a-database-using-visual-studio-2010, null);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (4022, 14, 3);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (4022, 13, 4);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (4061, 13, 4);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (4041, 14, 3);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (4022, 15, 2.5);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (4022, 16, 2);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (4022, 17, null);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (4022, 19, 3.5);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (4061, 20, 4);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (4061, 21, 2);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (4022, 22, 3);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (4041, 22, 3.5);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (4061, 22, 2.5);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (4022, 23, 3);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (1045, 23, 1.5);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (1061, 24, 4);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (1061, 25, 3);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (1050, 26, 3.5);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (1061, 26, 3);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (1061, 27, 3);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (1045, 28, 2.5);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (1050, 28, 3.5);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (1061, 29, 4);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (1050, 30, 3.5);

INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)

VALUES (1061, 30, 4);

GO

 


Login to add your contents and source code to this article
This Feature is Sponsored By
post comment
COMMENT USING