SQL Server Exercise 1

Create a database and schema that contain the following tables.

Q1: Create all the tables and add all the rows. (format for date: mm-dd-yy e.g,‘02-22-2017’)

Q2: Identify one PK from each table and add the constraints using alter.

Q3: Identify all the FK constraints (there are composite fk too), and add those constraints, such that:

a) If the Student table or ClassVenue Table or Teacher table is updated the referencing columns should also reflect the changes.

b) Students should not be deleted if the details of that order are present.

Q4: Alter table Student by adding new column “warning count” and deleting “Phone” Column.

Solution:

create database University;

create table Student(
   RollNum varchar(30) not null unique,
   Name varchar(50) not null,
   Gender varchar(10) not null,
   Phone varchar(20)
);

create table Teacher(
   ID int not null unique,
   Name varchar(50) not null,
   Designation varchar(30),
   Department varchar(30)
);

create table ClassVenue(
   ID int not null unique,
   Building varchar(10),
   RoomNum int,
   Teacher varchar(50) not null,
   TeacherID int not null unique
);

create table Attendence(
   RollNum varchar(30) not null unique,
   Date date not null,
   Status varchar(1) not null,
   ClassVenue int  not null
);

alter table Student
add constraint Student_PK primary key (RollNum);

alter table Teacher
add constraint Teacher_PK primary key (ID, Name);

alter table ClassVenue
add constraint ClassVenue_PK primary key (ID);

alter table Attendence
add constraint Attendence_PK primary key (RollNum, Date);

alter table ClassVenue
add foreign key (TeacherID, Teacher) references Teacher (ID, Name);

alter table Attendence
add constraint Attendence_FK foreign key (ClassVenue) references ClassVenue (ID);

alter table Student
add Warning_Count int;

alter table Student
drop column Phone;

select * from Student
select * from Teacher
select * from ClassVenue
select * from Attendence