Create tables

Use Community_Assist
Go
Select * from Person

Create database TestTables
use testTables
/*
this is 
a multine
comment
*/
Create table Student
(
 StudentKey int identity(1,1) primary key,
 StudentLastName nvarchar(255) not null,
 StudentFirstName nvarchar(255) null,
 StudentEmail nvarchar(255) not null
)

--this is a comment
Create table Course
(
    CourseKey int identity(1,1) primary key,
 CourseName nvarchar(255) not null,
 CourseCredits int default 5 -- default value

)

Create table Section
(
  SectionKey int identity(1,1),
  CourseKey int not null foreign key references Course(CourseKey),
  SectionYear int not null,
  SectionQuarter nvarchar(6),
  Constraint constraint_quarter check (SectionQuarter in ('Fall', 'winter', 'Spring', 'Summer')),
  Constraint PK_SectionKey primary key (SectionKey)
)

Create table Roster
(
    Rosterkey int identity(1,1) not null,
 SectionKey int not null,
 StudentKey int not null,
 RosterGrade decimal(2,1) null
   
)

Alter table Roster
Add Constraint PK_Roster primary key (RosterKey)

Alter table Roster
Add Constraint FK_Section Foreign Key (SectionKey)
References Section(SectionKey)

Alter table Roster
Add Constraint FK_Student Foreign Key (StudentKey)
References Student(StudentKey)

Alter Table Roster
Add Constraint ck_Grade Check (RosterGrade between 0 and 4)

Alter table Student
Add Constraint unique_email unique(StudentEmail)

Alter table Student
Add StudentId nvarchar(9)

Alter Table Student
Drop column StudentID