drop database if exists university;
create database if not exists university;
use university;

Drop table if exists registration;
Create table registration(sid char(3), cid char(7),grade char(1));
Insert into registration values('s1','isys263','A');
Insert into registration values('s1','acct101', 'B');
Insert into registration values('s2','isys363','A');
Insert into registration values('s2','isys464','C');
Insert into registration values('s2','fin350','A');
Insert into registration values('s3','isys263','B');
Insert into registration values('s4','art100','B');
Insert into registration values('s5','sci101','A');
Insert into registration values('s7','sci101','B');

Drop table if exists account;
Create table account(sid char(3),balance decimal (6,2));
Insert into account values('s1',2500.0);
Insert into account values('s2',500.0);
Insert into account values('s3',3000.0);
Insert into account values('s9',0.0);
Insert into account values('s4',500.0);
Insert into account values('s5',500.0);
Insert into account values('s7',500.0);

Drop table if exists student;
Create table student (sid char(3), sname varchar(20), sex char(1), gpa decimal(3,2), major char(10), fid char(3));
Insert into student values('s1', 'peter','m',3.2,'bus','f2');
Insert into student values('s9', 'pan','m',2.0,'bus','f2');
Insert into student values('s2', 'paul','m',2.2,'bus','f2');
Insert into student values('s5', 'mary','f',1.5,'sci','f3');
Insert into student values('s7', 'mia','f',1.5,'sci','f3');
Insert into student values('s3', 'nancy','f',3.5,'bus','f1');
Insert into student values('s4', 'linda','f',2.5,'art','f1');

Drop table if exists faculty;
Create table faculty(fid char(3), fname varchar(20), phone char(4));
Insert into faculty values('f1','chao','1111');
Insert into faculty values('f2','smith','2222');
Insert into faculty values('f3','boxer','3333');

Drop table if exists course;
Create table course(cid char(7), cname varchar(15), units decimal(2,1));
Insert into course values('isys263','is intro',3.0);
Insert into course values('isys363','mis',3.0);
Insert into course values('isys464','database',3.0);
Insert into course values('acct101','accounting',3.0);
Insert into course values('fin350','finance',3.0);
Insert into course values('sci101','science',3.0);
Insert into course values('art100','art intro',3.0);