create table Students (
  sid number primary key,
  name varchar(128) not null,
  gpa number
);

create table Departments (
  did number primary key, 
  name varchar(128) not null
);

create table Courses (
  cid number, 
  did number, 
  name varchar(128) unique, 
  credits number default 3,
  primary key (cid, did),
  foreign key (did) references Departments(did)
);

create table Enrollment (
  cid number,
  did number,
  sid number,
  term varchar(32),
  grade number,
  primary key (cid, did, sid),
  foreign key (cid, did) references Courses(cid, did),
  foreign key (sid) references Students(sid)
 );

insert into Students values (1, 'Jane', 4);
insert into Students values (2, 'Mike', 3.75);
insert into Students values (3, 'Ann', 3.3);
insert into Students values (4, 'Mike', 2.8);

insert into Departments values (100, 'Math');
insert into Departments values (200, 'CS');
insert into Departments values (300, 'Italian');

insert into Courses (cid, did, name) values (1001, 100, 'Calculus 1');
insert into Courses (cid, did, name) values (1001, 200, 'Introduction to Computation');
insert into Courses (cid, did, name) values (1007, 200, 'Object-Oriented Design in Java');

insert into Enrollment(cid, did, sid, term) values (1001, 100, 1, 'Fall 2009');
insert into Enrollment(cid, did, sid, term) values (1001, 100, 2, 'Fall 2009');

insert into Enrollment(cid, did, sid, term, grade) values (1007, 200, 1, 'Summer 2009', 2);
insert into Enrollment(cid, did, sid, term, grade) values (1007, 200, 2, 'Summer 2009', 3);
insert into Enrollment(cid, did, sid, term, grade) values (1007, 200, 3, 'Summer 2009', 4);
insert into Enrollment(cid, did, sid, term) values (1007, 200, 4, 'Summer 2009');

drop table Enrollment;

select * 
from Students;

select * 
from Students 
where gpa > 3.0;

select name 
from Students 
where gpa > 3.0;

select distinct name 
from Students;

delete from Students 
where name = 'Ann';

update Students 
set name = 'Mike' 
where id = 4;

select * 
from  Students, Enrollment
where Students.sid = Enrollment.sid
and   Enrollment.did = 100

select count(*)
from Students;

select min(gpa), max(gpa)
from Students;

select term, count(*), avg(grade)
from Enrollment
group by term;

select * 
from Enrollment 
where grade is null;