Notes

--RUIZ, Jose Ricardo IV, R.
--3CSD



--a.)
connect to Sample;


--b.)

CREATE TABLE USTStudent (StudentID INTEGER not null, StudentName VARCHAR(30), Adress

VARCHAR(30), Subject_ID CHAR(10) not null, Mark INTEGER);

CREATE TABLE USTSubject (Subject_CODE CHAR(10), Subject_Name Varchar(30));

--c.)

alter table USTSubject add primary key(Subjecd_CODE);
alter table USTStudent add primary key(StudentID);
alter table USTStudent add foreign key(Subject_ID);

--d.)

INSERT INTO USTStudent (Student_ID,StudentName,Adress,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);

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.)
INSERT INTO USTStudent (Student_ID,StudentName,Adress,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.)
--already populated

--g.)
--already populated

--h.)
update USTStudent set Subject_ID ='CS1200' where Student_ID = 9400074;

--i.)
select Address from USTStudent where Address like %hall%;

--j.)
select Student_ID from USTStudent where Mark>=65;

--k.)
select Student_ID from USTStudent where Subject_ID='MA4545' AND Mark=75;

--l.)
select Student_ID, Student_Name from USTStudent where Subject_ID='CS1200';

--m.)
select sum(Mark) from USTStudent group by Subject_ID;

--n.)

select Student_ID, Subject_ID , Subject_Name from USTStudent,USTSubject where

Subject_CODE = Subject_ID;

--o.)
delete USTSTudent where Subject_ID='MA4545';

--p.)
select * from USTStudent,USTSubject where Subject_CODE = Subject_ID;

--q.)
update USTStudent set Mark=90 where Student_ID=9875630;

--r.)
CREATE TABLE StudGrades
(StudNumber INTEGER,Mark INTEGER);

--s.)
insert into StudGrades(StudNumber, Mark);
select Student_ID, Mark from USTStudent;