Difference between revisions of "DDL Extended Student Records Tutorial"

From SQLZOO
Jump to: navigation, search
(Created page with "This tutorial builds on the Student Record System <pre> CREATE TABLE student( matric_no CHAR(8) PRIMARY KEY, first_name VARCHAR(50) NOT NULL, las...")
 
Line 1: Line 1:
 
This tutorial builds on the [[DDL_Student_Records|Student Record System]]
 
This tutorial builds on the [[DDL_Student_Records|Student Record System]]
 +
<div class=hint title='mksrs.sql'>
 
<pre>
 
<pre>
 +
DROP TABLE registration;
 +
DROP TABLE `module`;
 +
DROP TABLE student;
 
CREATE TABLE student(
 
CREATE TABLE student(
 
   matric_no CHAR(8) PRIMARY KEY,
 
   matric_no CHAR(8) PRIMARY KEY,
Line 38: Line 42:
 
INSERT INTO registration VALUES ('40001012','HUF07101',20);
 
INSERT INTO registration VALUES ('40001012','HUF07101',20);
 
</pre>
 
</pre>
 +
</div>

Revision as of 21:37, 20 October 2016

This tutorial builds on the Student Record System

DROP TABLE registration;
DROP TABLE `module`;
DROP TABLE student;
CREATE TABLE student(
  matric_no CHAR(8) PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  date_of_birth DATE
);
INSERT INTO student VALUES ('40001010','Daniel','Radcliffe','1989-07-23');
INSERT INTO student VALUES ('40001011','Emma','Watson','1990-04-15');
INSERT INTO student VALUES ('40001012','Rupert','Grint','1988-10-24');

CREATE TABLE `module`(
  module_code CHAR(8) PRIMARY KEY,
  module_title VARCHAR(50) NOT NULL,
  level INT NOT NULL,
  credits INT NOT NULL DEFAULT 20
);
INSERT INTO `module` VALUES ('HUF07101','Herbology');
INSERT INTO `module` VALUES ('SLY07102','Defence Against the Dark Arts');
INSERT INTO `module` VALUES ('HUF08102','History of Magic');

CREATE TABLE registration(
  matric_no CHAR(8),
  module_code CHAR(8),
  module_title VARCHAR(50) NOT NULL,
  result DECIMAL(4,1),
  PRIMARY KEY (matric_no,module_code),
  FOREIGN KEY (matric_no) REFERENCES student(matric_no),
  FOREIGN KEY (module|_code) REFERENCES `module`(module_code)
);
INSERT INTO registration VALUES ('40001010','SLY07102',90);
INSERT INTO registration VALUES ('40001010','HUF07101',40);
INSERT INTO registration VALUES ('40001010','HUF08102',null);
INSERT INTO registration VALUES ('40001011','SLY07102',99);
INSERT INTO registration VALUES ('40001011','HUF08102',null);
INSERT INTO registration VALUES ('40001012','SLY07102',20);
INSERT INTO registration VALUES ('40001012','HUF07101',20);