Create Tables

Here is a script for Creating tables. To look at all the data types supported by SQL Server you can go to MSDN Help Data Types

/*********************************
First we will create a database to store our new tables
*************************************/
Create database Gradebook

/*now use that database*/
Use Gradebook

/*Below are the create table commands
I have shown many alternate ways to create constraints,
both within and outside the tables */
Create table Student
(
StudentID nchar(9) Primary key,
StudentLastName nvarchar(255) not null,
StudentFirstName nvarchar(255) null
)



Create table Course
(
CourseNumber nchar(7) not null,
CourseName nvarchar(255) not null,
CourseCredits int default 5,
Constraint pk_Course primary Key (CourseNumber)

)

Create table Session
(
SessionID int identity(1,1) not null,
CourseNumber nchar(7) Foreign Key references Course(courseNumber),
SessionQuarter nchar(7) check (SessionQuarter in ('Fall', 'Winter', 'Spring', 'Summer')),
SessionYear nchar(4) not null,
Section int default 1
)

Alter table Session
Add Constraint PK_Session Primary Key(SessionID)

Create Table SessionStudent
(
SessionID int not null,
CourseNumber nchar(7) not null,
SessionStudentGrade decimal(2,1) null,
SessionStudentNotes xml

)

/*various alter table commands*/
Alter table SessionStudent
Add Constraint PK_SessionStudent primary key(SessionID, CourseNumber)

Alter Table SessionStudent
Add Constraint FK_Session foreign key (SessionID) references Session(SessionID)

Alter Table SessionStudent
Add Constraint FK_Course Foreign Key(CourseNumber) References Course(CourseNumber)

Alter table SessionStudent
Add Constraint chk_Grade Check(SessionStudentGrade between 0 and 4)


Alter table SessionStudent
Drop Constraint chkGrade

Alter table Session Student
Drop column SessionStudentNotes

Alter table SessionStudent
Add SessionStudentNotes xml