Assignment2

1.sql

create table Student (
    stuID   number not null,
    stuName varchar2(10),
    house   number,
    gpa     number,
    primary key (stuID),
    check ( gpa between 0.0 and 4.0 )
);

create table Degree
(
    dept    char(4) not null,
    lvl     char(2) not null,
    advisor varchar2(10),
    hours   number,
    primary key (dept, lvl),
    check ( hours >= 0 ),
    check ( lvl in ('BS', 'MS', 'PhD') )
);

create table Major (
    stuID number not null,
    dept char(4) not null,
    lvl char(2) not null,
    credits number,
    primary key (stuID, dept, lvl),
    check ( credits >= 0 ),
    foreign key (stuID) references Student,
    foreign key (dept, lvl) references Degree
);

2.sql

insert into Student (stuID, stuName, house, gpa) values (1000, 'January', '1', 3.5);
insert into Student (stuID, stuName, house, gpa) values (1001, 'February', '1', 2.4);
insert into Student (stuID, stuName, house, gpa) values (1002, 'March', '2', 1.75);
insert into Student (stuID, stuName, house, gpa) values (1003, 'April', '2', 4.0);
insert into Student (stuID, stuName, house, gpa) values (1004, 'May', '2', 3.75);
insert into Student (stuID, stuName, house, gpa) values (1005, 'June', '3', 2.9);
insert into Student (stuID, stuName, house, gpa) values (1006, 'July', '3', 3.5);
insert into Student (stuID, stuName, house, gpa) values (1007, 'August', '3', 4.0);
insert into Student (stuID, stuName, house, gpa) values (1008, 'September', '4', 3.4);
insert into Student (stuID, stuName, house, gpa) values (1009, 'October', '4', 2.8);
insert into Student (stuID, stuName, house, gpa) values (1010, 'November', '4', 3.8);
insert into Student (stuID, stuName, house, gpa) values (1011, 'December', '1', 4.0);

insert into Degree (dept, lvl, advisor, hours) values ('COSC', 'BS', 'Knight', 120);
insert into Degree (dept, lvl, advisor, hours) values ('COSC', 'MS', 'Knight', 40);
insert into Degree (dept, lvl, advisor, hours) values ('COSC', 'PhD', 'Dragon', 35);
insert into Degree (dept, lvl, advisor, hours) values ('MATH', 'BS', 'Princess', 126);
insert into Degree (dept, lvl, advisor, hours) values ('MATH', 'MS', 'Princess', 33);
insert into Degree (dept, lvl, advisor, hours) values ('MATH', 'PhD', 'Queen', 20);
insert into Degree (dept, lvl, advisor, hours) values ('PHYS', 'BS', 'King', 117);
insert into Degree (dept, lvl, advisor, hours) values ('PHYS', 'MS', 'Prince', 65);
insert into Degree (dept, lvl, advisor, hours) values ('PHYS', 'PhD', 'Peasant', 43);
insert into Degree (dept, lvl, advisor, hours) values ('BSKT', 'PhD', 'Beggar', 175);

insert into Major (stuID, dept, lvl, credits) values (1000, 'COSC', 'BS', 95);
insert into Major (stuID, dept, lvl, credits) values (1000, 'BSKT', 'PhD', 10);
insert into Major (stuID, dept, lvl, credits) values (1001, 'COSC', 'MS', 34);
insert into Major (stuID, dept, lvl, credits) values (1001, 'COSC', 'PhD', 2);
insert into Major (stuID, dept, lvl, credits) values (1002, 'BSKT', 'PhD', 180);
insert into Major (stuID, dept, lvl, credits) values (1003, 'COSC', 'BS', 20);
insert into Major (stuID, dept, lvl, credits) values (1003, 'MATH', 'BS', 35);
insert into Major (stuID, dept, lvl, credits) values (1003, 'PHYS', 'PhD', 40);
insert into Major (stuID, dept, lvl, credits) values (1004, 'PHYS', 'BS', 60);
insert into Major (stuID, dept, lvl, credits) values (1004, 'BSKT', 'PhD', 160);
insert into Major (stuID, dept, lvl, credits) values (1005, 'PHYS', 'BS', 190);
insert into Major (stuID, dept, lvl, credits) values (1005, 'COSC', 'BS', 3);
insert into Major (stuID, dept, lvl, credits) values (1006, 'BSKT', 'PhD', 175);
insert into Major (stuID, dept, lvl, credits) values (1006, 'COSC', 'MS', 20);
insert into Major (stuID, dept, lvl, credits) values (1007, 'COSC', 'MS', 5);
insert into Major (stuID, dept, lvl, credits) values (1007, 'PHYS', 'PhD', 3);
insert into Major (stuID, dept, lvl, credits) values (1008, 'PHYS', 'MS', 30);
insert into Major (stuID, dept, lvl, credits) values (1008, 'BSKT', 'PhD', 53);
insert into Major (stuID, dept, lvl, credits) values (1009, 'BSKT', 'PhD', 140);
insert into Major (stuID, dept, lvl, credits) values (1010, 'BSKT', 'PhD', 40);