Notes

1.
a.connect to sample
create table USTStudent(
StudentId integer not null,
StudentName varchar(30),
Address vachar(30)
Subject_Id Char(10) not null,
Mark Integer
);

create table USTSubject(
Subject_Name varchar(30) not null,
Subject_CODE Char(10) not null
);

Alter table USTSubject
Add constraint Subject_Code_pk primary key(Subject_Code);
Alter table USTSubject
Add constraint StudentId_pk primary key(StudentId);
Add constraint Subject_ID_fk foreign key(StudentId) references USTSubject(Subject_Code)
Add constraint Mark_ck check(Mark between 40 to 100);
Insert into USTStudents values(
('9400074', 'JASON', '9', 'Ozie Hall', 'MA4545', '90'),
('9200020', 'TEDDY', '90', 'Second Hall', 'MA4545', '65'),
('9875620', 'LESIE', '87', 'River Road', 'CS1200', '78'),
('9200021', 'TOM' , '25', 'First Hall', 'MA4545', '75'),
('9875630', 'SUSAN', '80', 'River Dale', 'CS1200', '78')
);

Insert into USTSubjects values(
('CS1200', 'Information Systems'),
('MA4545', 'The Diversity of Life'),
('CS1300', 'Intro to Computing'),
('CH8794', 'Intro of Chemistry'),
('MA1235', 'Pure Mathematics')
);

Insert into USTStudents values(
('8745520', 'BEN', '79', 'Main Street', 'CS8794', 'Null'),
('9841220', 'WENDY', '124', 'Long Hall', 'CS1200', '85'),
('984120', 'WENDY', '124', 'Long Hall', 'CS1200', '85')
);

select * from USTStudents;
select * from USTSubject;
update USTStudents
set Subject_ID='CS1200'
where Student_ID='9400074';
Select Address from USTStudents
where Address ='%Hall';
Select Mark from USTStudents
where Mark >=65;
Select Student_ID from USTStudents
where Mark=75;
Select Student_ID,Student_Name from USTStudents
where Subject_Code='CS1200';
Select Sum(Mark) from USTStudents as TotalScores
group by Subject_ID;





A)
db2start
db2 connect to sample


B)
db2 "create table uststudent
(studentid integer not null,
studentname varchar(30),
address varchar(30),
subject_id char(10) not null,
mark integer)"
db2 "create table ustsubject
(subject_code char(10),
subject_name varchar(30),
primarykey (subject_code))"


C)
db2 alter table ustsubject
add constraint pk primary key(subject_code);
db2 alter table
add constraint pk primary key(student_id);
add constraint fk foreign key(subject_id) referrences ustsubject(subject_code);
add constraint ck check (Mark between 40 to 10);



D)
db2 "insert into uststudent
(student_id,student_name,address,subject_id,mark) values
('9400074','JASON','9, Ozie Hall','MA4545','90'),
('9200020','TEDDY','90, Second Hall','MA4545','65'),
('9875620','LESIE','87, River Road','CS1200','78'),
('9200021','TOM','25, First Hall','MA4545','75'),
('9875630','SUSAN','80, River Dale','CS1200','78')"

db2 "insert into ustsubject
(subject_code, subject_name) values
('CS1200','Information Systems'),
('MA4545','The Diversity of Life'),
('CS1300','Intro to Computing'),
('CH8794','Intro of Chemistry'),
('MA1235','Pure Mathematics')"

E)
db2 "Insert into USTStudents
student_id,student_name,address,subject_id,mark) values
('8745520', 'BEN', '79', 'Main Street', 'CS8794', 'Null'),
('9841220', 'WENDY', '124', 'Long Hall', 'CS1200', '85'),
('984120', 'WENDY', '124', 'Long Hall', 'CS1200', '85')"

F)