sideboob, MySQL - rextester

sideboob

DROP TABLE students14;

CREATE TABLE IF NOT EXISTS students14(
 student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 first_name VARCHAR(20) NOT NULL,
 middle_name VARCHAR(20),
 last_name VARCHAR(40) NOT NULL,
 email VARCHAR(60) NOT NULL,
 PASSWORD CHAR(40) NOT NULL,
 reg_date DATETIME NOT NULL,
 PRIMARY KEY (student_id),
 UNIQUE (email));

DESC students14;

INSERT INTO students14(first_name, last_name, email, password, reg_date)
VALUES ("George", "Blackwell", "fgvvddddtgvjhukhfgv@ujhnhgchmnhgv.com","uyjh", "2016/11/08"),
("Edwin", "Steenbergen", "rijhgg@wedtyhrt.com","95fv4c", "2016/07/09"),
("Julien", "Mercer", "qwerty@uiop.com", "fg  jh df uyjh", "2016/11/10"),
("Alex", "Seaanemone", "ilovecheesecake@chocolatecake.com", "passqword", "2016/07/06"),
("Petr", "Richards", "uyjhrtfgguyjh@edtfygio.com", "lcvjhsvdjfjheydufjk", "2016/01/01");

SELECT * 
FROM students14;

DROP TABLE subjects14;

CREATE TABLE IF NOT EXISTS subjects14(
subject_id int unsigned NOT null AUTO_INCREMENT,
subject_name varchar(30) NOT null, 
level_of_entry varchar(20) NOT null, 
exam_board varchar(20) NOT null,
PRIMARY KEY (subject_id),
UNIQUE (subject_name));

DESC subjects14;

INSERT INTO subjects14 (subject_name, level_of_entry, exam_board)
VALUES ("Chemistry", "AS", "OCR"),
("Biology", "GCSE", "AQA"),
("Physics", "A", "AQA"),
("Music", "GCSE", "Edexcel"),
("Art", "GCSE", "AQA"),
("English", "A", "OCR"),
("Maths", "A", "Edexcel"),
("Computing", "GCSE", "PIXL"),
("Geography", "AS", "OCR"),
("Drama", "GCSE", "PIXL");
  
 
SELECT *
FROM subjects14;

SELECT exam_board, COUNT(*)
FROM subjects14
GROUP BY exam_board;

CREATE TABLE IF NOT EXISTS entries14(
entries_id int unsigned NOT null AUTO_INCREMENT,
student_id int unsigned NOT null, 
subjects_id int unsigned NOT null, 
exam_date datetime NOT null,
PRIMARY KEY (entries_id),
FOREIGN KEY (subjects_id) REFERENCES subjects14(subjects_id),
FOREIGN KEY (student_id) REFERENCES students14(students_id));
    
INSERT INTO entries14 (students_id, subjects_id, exam_date)
VALUES ("1", "1", "2017/07/01"),
 ("1", "2", "2017/07/02"),
 ("1", "4", "2017/07/04"),
 ("1", "5", "2017/07/03"),
 ("1", "6", "2017/07/09"),
 ("1", "7", "2017/07/012"),
 ("2", "1", "2017/07/01"),
 ("2", "2", "2017/07/02"),
 ("2", "4", "2017/07/04"),
 ("2", "5", "2017/07/03"),
 ("2", "6", "2017/07/09"),
 ("2", "7", "2017/07/12"),
 ("3", "1", "2017/07/01"),
 ("3", "2", "2017/07/02"),
 ("3", "4", "2017/07/04"),
 ("3", "5", "2017/07/03"),
 ("3", "6", "2017/07/09"),
 ("3", "7", "2017/07/12"),
 ("4", "1", "2017/07/01"),
 ("4", "2", "2017/07/02"),
 ("4", "4", "2017/07/04"),
 ("4", "5", "2017/07/03"),
 ("4", "10", "2017/07/11"),
 ("4", "7", "2017/07/12"),
 ("5", "1", "2017/07/01"),
 ("5", "2", "2017/07/02"),
 ("5", "4", "2017/07/04"),
 ("5", "5", "2017/07/03"),
 ("5", "6", "2017/07/09"),
 ("5", "7", "2017/07/12");

SELECT *
FROM entries14;


run \| edit \| history \| help	0