CS479/579 - Web Programming II

Displaying exercises/e10/files/newdb.sql

drop table if exists `students`;
drop table if exists `tests`;
drop table if exists `grades`;

create table `students` (
  `sid`		int		not null auto_increment,
  `name`	varchar(32)	not null default '',
  primary key (`sid`)
);

create table `tests` (
  `tid`		int		not null auto_increment,
  `name`	varchar(32)	not null default '',
  `points`	float		not null default 0.0,
  primary key (`tid`)
);

create table `grades` (
  `sid`		int		not null default 0,
  `tid`		int		not null default 0,
  `points`	float		default null,
  index	`sid` (`sid`),
  index `tid` (`tid`)
);

insert into students (name) values
( 'Alice' ), ( 'Bob' ), ( 'Carol' ), ( 'David' ), ( 'Frank' ),
( 'George' ), ( 'Harry' ), ( 'Irene' ), ( 'Jane' ), ( 'Kate' );

insert into tests (name, points) values
 ( 'Quiz 1', 10 ), ( 'Quiz 2', 10 ), ( 'Exam 1', 100 );

insert into `grades` values
 (1,1,NULL),(2,1,NULL),(3,1,NULL),(4,1,NULL),(5,1,NULL),
 (6,1,NULL),(7,1,NULL),(8,1,NULL),(9,1,NULL),(10,1,NULL),
 (1,2,NULL),(2,2,NULL),(3,2,50),(4,2,NULL),(5,2,NULL),
 (6,2,NULL),(7,2,NULL),(8,2,NULL),(9,2,NULL),(10,2,NULL),
 (1,3,NULL),(2,3,NULL),(3,3,NULL),(4,3,NULL),(5,3,NULL),
 (6,3,NULL),(7,3,NULL),(8,3,NULL),(9,3,NULL),(10,3,NULL);