TUGAS_UAS ( 41808110042 )

A)
CREATE TABLE `student` (
`STUDENTID` char(5) NOT NULL default '',
`STUDENT` varchar(25) default NULL,
`BIRTHDATE` date default NULL,
PRIMARY KEY (`STUDENTID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `course` (
`COURSEID` char(6) NOT NULL default '',
`COURSE` varchar(25) default NULL,
`CREDIT` int(2) default NULL,
PRIMARY KEY (`COURSEID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



CREATE TABLE `student_course` (
`STUDENTID` char(5) NOT NULL default '',
`COURSEID` varchar(6) default NULL,
`GRADE` char(1) default NULL,
KEY `FK01` (`STUDENTID`),
KEY `FK02` (`COURSEID`),
KEY `FK03` (`GRADE`),
CONSTRAINT `FK03` FOREIGN KEY (`GRADE`) REFERENCES `grade` (`GRADE`),
CONSTRAINT `FK01` FOREIGN KEY (`STUDENTID`) REFERENCES `student` (`STUDENTID`),
CONSTRAINT `FK02` FOREIGN KEY (`COURSEID`) REFERENCES `course` (`COURSEID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT;

CREATE TABLE `grade` (
`GRADE` char(1) NOT NULL default '',
`WEIGHT` int(1) default NULL,
PRIMARY KEY (`GRADE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



B)
INSERT INTO `database_ujian_meruya`.`student` SET `STUDENTID`='61521', `STUDENT`='Jones', `BIRTHDATE`='1977-05-12;
INSERT INTO `database_ujian_meruya`.`student` SET `STUDENTID`='61300', `STUDENT`='Diana', `BIRTHDATE`='1977-08-14';
INSERT INTO `database_ujian_meruya`.`student` SET `STUDENTID`='61425', `STUDENT`='Tony', `BIRTHDATE`='1976-01-11';
INSERT INTO `database_ujian_meruya`.`student` SET `STUDENTID`='61230', `STUDENT`='Paula', `BIRTHDATE`='1977-04-06';



INSERT INTO `database_ujian_meruya`.`course` SET `COURSEID`='MAT231', `COURSE`='Math', `CREDIT`=3;
INSERT INTO `database_ujian_meruya`.`course` SET `COURSEID`='DB220', `COURSE`='DataBase', `CREDIT`=3;
INSERT INTO `database_ujian_meruya`.`course` SET `COURSEID`='ENG202', `COURSE`='English', `CREDIT`=2;
INSERT INTO `database_ujian_meruya`.`course` SET `COURSEID`='NET211', `COURSE`='Networking', `CREDIT`=3;


INSERT INTO `database_ujian_meruya`.`grade` SET `GRADE`='A', `WEIGHT`=4;
INSERT INTO `database_ujian_meruya`.`grade` SET `GRADE`='B', `WEIGHT`=3;
INSERT INTO `database_ujian_meruya`.`grade` SET `GRADE`='C', `WEIGHT`=2;
INSERT INTO `database_ujian_meruya`.`grade` SET `GRADE`='D', `WEIGHT`=1;
INSERT INTO `database_ujian_meruya`.`grade` SET `GRADE`='E', `WEIGHT`=0;



INSERT INTO `database_ujian_meruya`.`student_course` SET `STUDENTID`='61521', `COURSEID`='MAT231', `GRADE`='B';
INSERT INTO `database_ujian_meruya`.`student_course` SET `STUDENTID`='61521', `COURSEID`='DB220', `GRADE`='A';
INSERT INTO `database_ujian_meruya`.`student_course` SET `STUDENTID`='61521', `COURSEID`='NETT21', `GRADE`='B';
INSERT INTO `database_ujian_meruya`.`student_course` SET `STUDENTID`='61521', `COURSEID`='NET211', `GRADE`='B';
INSERT INTO `database_ujian_meruya`.`student_course` SET `STUDENTID`='61300', `COURSEID`='NET211', `GRADE`='A';
INSERT INTO `database_ujian_meruya`.`student_course` SET `STUDENTID`='61425', `COURSEID`='ENG202', `GRADE`='C';
INSERT INTO `database_ujian_meruya`.`student_course` SET `STUDENTID`='61425', `COURSEID`='MAT231', `GRADE`='B';
INSERT INTO `database_ujian_meruya`.`student_course` SET `STUDENTID`='61230', `COURSEID`='MAT231', `GRADE`='B';
INSERT INTO `database_ujian_meruya`.`student_course` SET `STUDENTID`='61230', `COURSEID`='ENG202', `GRADE`='C';



ALTER TABLE `database_ujian_meruya`.`student`
ADD COLUMN `Address` varchar(30) NULL DEFAULT NULL;

UPDATE `database_ujian_meruya`.`student` SET `Address`='New Jersey' WHERE `STUDENTID`='61230';
UPDATE `database_ujian_meruya`.`student` SET `Address`='Las Vegas' WHERE `STUDENTID`='61300';
UPDATE `database_ujian_meruya`.`student` SET `Address`='Chicago' WHERE `STUDENTID`='61425';
UPDATE `database_ujian_meruya`.`student` SET `Address`='Pensylvania' WHERE `STUDENTID`='61521';




SELECT
student_course.STUDENTID
FROM
student_course
INNER JOIN student ON (student_course.STUDENTID = student.STUDENTID)
WHERE
student.STUDENT = 'JONES' AND
student_course.GRADE = 'A'


SELECT
student_course.STUDENTID,
student_course.COURSEID
FROM
student_course
INNER JOIN student ON (student_course.STUDENTID = student.STUDENTID)
WHERE
YEAR(student.BIRTHDATE) = 1977



SELECT
student_course.STUDENTID,
student.STUDENT,
course.COURSE,
grade.GRADE
FROM
student_course
INNER JOIN student ON (student_course.STUDENTID = student.STUDENTID)
INNER JOIN course ON (student_course.COURSEID = course.COURSEID)
INNER JOIN grade ON (student_course.GRADE = grade.GRADE)

SELECT
student.STUDENT,
course.COURSE,
student_course.GRADE,
grade.WEIGHT
FROM
student_course
INNER JOIN student ON (student_course.STUDENTID = student.STUDENTID)
INNER JOIN course ON (student_course.COURSEID = course.COURSEID)
INNER JOIN grade ON (student_course.GRADE = grade.GRADE)


SELECT
course.COURSE,
course.CREDIT
FROM
course
WHERE
course.CREDIT = 3


SELECT
student_course.STUDENTID,
student.STUDENT,
course.COURSE
FROM
student_course
INNER JOIN student ON (student_course.STUDENTID = student.STUDENTID)
INNER JOIN course ON (student_course.COURSEID = course.COURSEID)
WHERE
course.COURSE = "Data Base"


SELECT
student.STUDENT,
student.BIRTHDATE,
student.Address
FROM
student
WHERE
student.Address = "Chicago"