Creating and altering Tables

--Creating and altering tables
use master
go
Create database grading

use grading

Create Table Student
(
   StudentID nchar(12) primary key,
   StudentLastName nvarchar(255),
   StudentFirstName nvarchar(255)
)

Create Table Course
(
    CourseNumber nchar(7),
 CourseName nvarchar(255) not null,
 CourseCredits int not null,
 Constraint pk_Course Primary Key (CourseNumber)
 
)


Create Table Section
(
   SectionNumber nchar(4) not null,
   CourseNumber nchar(7) Foreign Key 
   References Course(CourseNumber),
 SectionQuarter nvarchar(7) not null,
 SectionYear char(4) not null
)

Drop Table Section

alter Table Section
Add Constraint Pk_Section Primary Key(SectionNumber)

Create Table Roster
(
 RosterID int identity(1,1),
 StudentID nchar(12),
 SectionNumber nchar(4),
 Grade Decimal(3,2),
 Notes xml,
 Constraint pk_Roster primary key(RosterID),
 Constraint FK_Student Foreign key (StudentID)
  References Student(StudentID)

)

Alter Table Roster
Add constraint FK_Section Foreign Key(SectionNumber)
References Section(SectionNumber)

Alter Table Roster
Add Constraint Ck_Grade Check(Grade between 0 and 4)

Alter Table Section
Add Constraint ch_Quarter 
Check (SectionQuarter in ('Fall', 'Winter', 'Spring', 'Summer'))

Insert into Student(StudentID, StudentLastName, StudentFirstName)
Values('919-00-9876','Smith','John')

Insert into Course(CourseNumber, CourseName, CourseCredits)
Values('ITC222','SQL',5)

Insert into Section(SectionNumber, CourseNumber, 
SectionQuarter, SectionYear)
Values('3208','ITC222','Winter','2012')

--these will cause errors because of the check constraints
Insert into Section(SectionNumber, CourseNumber, 
SectionQuarter, SectionYear)
Values('3209','ITC222','Jan','2012')

Insert into Roster(StudentID, SectionNumber, Grade, Notes)
Values ('919-00-9876','3208',5,null)

Create Table Student
(
   StudentKey int identity(1,1) primary key,
   StudentID nchar(12) unique
)