Question

Using the Class database:

1. Using a union, display the date and student id those students from the absence table with the date and category of any grade event. Place in order by date. Insert your snip here.

2. List the scores for student 17 for every List the student id, event id, score, and category for student id 17. Insert your snip here.

3. For every student in the student table, display the student id as ‘ID’, student name as ‘Name’, and the total number of tests and quizzes each student has taken as ‘Number Taken’. Insert your snip here.

4. Who missed a test or quiz? Display the student id as ‘ID’, student name as ‘Name’, the category of the test or quizzed missed as ‘Category’, and the date as ‘Date’. You will use 3 tables here. Insert your snip here.

5. Display the average score for each test event. Do not explicitly test for individual event ids. Let MySQL do the work for you. Display the event id as ‘Event’ and the average score for that event as ‘Average Score’. Insert your snip here.

***********************************************************************************************************************************************

***********************************************************************************************************************************************

***********************************************************************************************************************************************
#---Create and open the database

drop database if exists Class;

CREATE DATABASE Class;

#-- Using the database

USE Class;

# create student table

DROP TABLE IF EXISTS student;

CREATE TABLE student

(
  
name VARCHAR(20) NOT NULL,
  
gender ENUM('F','M') NOT NULL,
  
student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  
PRIMARY KEY (student_id)

);

# create grade event table

DROP TABLE IF EXISTS grade_event;

CREATE TABLE grade_event

(
  
date DATE NOT NULL,
  
category ENUM('T','Q') NOT NULL,
  
event_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  
PRIMARY KEY (event_id)

);

# create score table

# The PRIMARY KEY comprises two columns to prevent any combination
# of event_id/student_id from appearing more than once.

DROP TABLE IF EXISTS score;

CREATE TABLE score

(
  
student_id INT UNSIGNED NOT NULL,
  
event_id INT UNSIGNED NOT NULL,
  
score INT NOT NULL,
  
PRIMARY KEY (event_id, student_id),
  
INDEX (student_id),
  
FOREIGN KEY (event_id) REFERENCES grade_event (event_id),
  
FOREIGN KEY (student_id) REFERENCES student (student_id)

);

# create absence table

DROP TABLE IF EXISTS absence;

CREATE TABLE absence

(
  
student_id INT UNSIGNED NOT NULL,
  
date DATE NOT NULL,
  
PRIMARY KEY (student_id, date),
  
FOREIGN KEY (student_id) REFERENCES student (student_id)

);

#--Populate the student table

INSERT INTO student VALUES ('Megan','F',NULL);

INSERT INTO student VALUES ('Joseph','M',NULL);

INSERT INTO student VALUES ('Kyle','M',NULL);

INSERT INTO student VALUES ('Katie','F',NULL);

INSERT INTO student VALUES ('Abby','F',NULL);

INSERT INTO student VALUES ('Nathan','M',NULL);

INSERT INTO student VALUES ('Liesl','F',NULL);

INSERT INTO student VALUES ('Ian','M',NULL);

INSERT INTO student VALUES ('Colin','M',NULL);

INSERT INTO student VALUES ('Peter','M',NULL);

INSERT INTO student VALUES ('Michael','M',NULL);

INSERT INTO student VALUES ('Thomas','M',NULL);

INSERT INTO student VALUES ('Devri','F',NULL);

INSERT INTO student VALUES ('Ben','M',NULL);

INSERT INTO student VALUES ('Aubrey','F',NULL);

INSERT INTO student VALUES ('Rebecca','F',NULL);

INSERT INTO student VALUES ('Will','M',NULL);

INSERT INTO student VALUES ('Max','M',NULL);

INSERT INTO student VALUES ('Rianne','F',NULL);

INSERT INTO student VALUES ('Avery','F',NULL);

INSERT INTO student VALUES ('Lauren','F',NULL);

INSERT INTO student VALUES ('Becca','F',NULL);

INSERT INTO student VALUES ('Gregory','M',NULL);

INSERT INTO student VALUES ('Sarah','F',NULL);

INSERT INTO student VALUES ('Robbie','M',NULL);

INSERT INTO student VALUES ('Keaton','M',NULL);

INSERT INTO student VALUES ('Carter','M',NULL);

INSERT INTO student VALUES ('Teddy','M',NULL);

INSERT INTO student VALUES ('Gabrielle','F',NULL);

INSERT INTO student VALUES ('Grace','F',NULL);

INSERT INTO student VALUES ('Emily','F',NULL);

#--Populate grade event table

INSERT INTO grade_event VALUES('2015-09-03', 'Q', NULL);
INSERT INTO grade_event VALUES('
2015-09-06', 'Q', NULL);
INSERT INTO grade_event VALUES('
2015-09-09', 'T', NULL);
INSERT INTO grade_event VALUES('
2015-09-16', 'Q', NULL);
INSERT INTO grade_event VALUES(
'2015-09-23', 'Q', NULL);
INSERT INTO grade_event VALUES('
2015-10-01', 'T', NULL);

#--Populate the score table

INSERT INTO score VALUES (1,1,20);

INSERT INTO score VALUES (3,1,20);

INSERT INTO score VALUES (4,1,18);

INSERT INTO score VALUES (5,1,13);

INSERT INTO score VALUES (6,1,18);

INSERT INTO score VALUES (7,1,14);

INSERT INTO score VALUES (8,1,14);

INSERT INTO score VALUES (9,1,11);

INSERT INTO score VALUES (10,1,19);

INSERT INTO score VALUES (11,1,18);

INSERT INTO score VALUES (12,1,19);

INSERT INTO score VALUES (14,1,11);

INSERT INTO score VALUES (15,1,20);

INSERT INTO score VALUES (16,1,18);

INSERT INTO score VALUES (17,1,9);

INSERT INTO score VALUES (18,1,20);

INSERT INTO score VALUES (19,1,9);

INSERT INTO score VALUES (20,1,9);

INSERT INTO score VALUES (21,1,13);

INSERT INTO score VALUES (22,1,13);

INSERT INTO score VALUES (23,1,16);

INSERT INTO score VALUES (24,1,11);

INSERT INTO score VALUES (25,1,19);

INSERT INTO score VALUES (26,1,10);

INSERT INTO score VALUES (27,1,15);

INSERT INTO score VALUES (28,1,15);

INSERT INTO score VALUES (29,1,19);

INSERT INTO score VALUES (30,1,17);

INSERT INTO score VALUES (31,1,11);

INSERT INTO score VALUES (1,2,17);

INSERT INTO score VALUES (2,2,8);

INSERT INTO score VALUES (3,2,13);

INSERT INTO score VALUES (4,2,13);

INSERT INTO score VALUES (5,2,17);

INSERT INTO score VALUES (6,2,13);

INSERT INTO score VALUES (7,2,17);

INSERT INTO score VALUES (8,2,8);

INSERT INTO score VALUES (9,2,19);

INSERT INTO score VALUES (10,2,18);

INSERT INTO score VALUES (11,2,15);

INSERT INTO score VALUES (12,2,19);

INSERT INTO score VALUES (13,2,18);

INSERT INTO score VALUES (14,2,18);

INSERT INTO score VALUES (15,2,16);

INSERT INTO score VALUES (16,2,9);

INSERT INTO score VALUES (17,2,13);

INSERT INTO score VALUES (18,2,9);

INSERT INTO score VALUES (19,2,11);

INSERT INTO score VALUES (21,2,12);

INSERT INTO score VALUES (22,2,10);

INSERT INTO score VALUES (23,2,17);
INSERT INTO score VALUES (24,2,19);

INSERT INTO score VALUES (25,2,10);

INSERT INTO score VALUES (26,2,18);

INSERT INTO score VALUES (27,2,8);

INSERT INTO score VALUES (28,2,13);

INSERT INTO score VALUES (29,2,16);

INSERT INTO score VALUES (30,2,12);

INSERT INTO score VALUES (31,2,19);

INSERT INTO score VALUES (1,3,88);

INSERT INTO score VALUES (2,3,84);

INSERT INTO score VALUES (3,3,69);

INSERT INTO score VALUES (4,3,71);

INSERT INTO score VALUES (5,3,97);

INSERT INTO score VALUES (6,3,83);

INSERT INTO score VALUES (7,3,88);
INSERT INTO score VALUES (8,3,75);

INSERT INTO score VALUES (9,3,83);

INSERT INTO score VALUES (10,3,72);
INSERT INTO score VALUES (11,3,74);

INSERT INTO score VALUES (12,3,77);

INSERT INTO score VALUES (13,3,67);
INSERT INTO score VALUES (14,3,68);

INSERT INTO score VALUES (15,3,75);

INSERT INTO score VALUES (16,3,60);

INSERT INTO score VALUES (17,3,79);

INSERT INTO score VALUES (18,3,96);

INSERT INTO score VALUES (19,3,79);

INSERT INTO score VALUES (20,3,76);

INSERT INTO score VALUES (21,3,91);

INSERT INTO score VALUES (22,3,81);

INSERT INTO score VALUES (23,3,81);

INSERT INTO score VALUES (24,3,62);

INSERT INTO score VALUES (25,3,79);

INSERT INTO score VALUES (26,3,86);

INSERT INTO score VALUES (27,3,90);

INSERT INTO score VALUES (28,3,68);

INSERT INTO score VALUES (29,3,66);

INSERT INTO score VALUES (30,3,79);

INSERT INTO score VALUES (31,3,81);

INSERT INTO score VALUES (2,4,7);

INSERT INTO score VALUES (3,4,17);

INSERT INTO score VALUES (4,4,16);

INSERT INTO score VALUES (5,4,20);

INSERT INTO score VALUES (6,4,9);

INSERT INTO score VALUES (7,4,19);

INSERT INTO score VALUES (8,4,12);

INSERT INTO score VALUES (9,4,17);

INSERT INTO score VALUES (10,4,12);

INSERT INTO score VALUES (11,4,16);

INSERT INTO score VALUES (12,4,13);

INSERT INTO score VALUES (13,4,8);

INSERT INTO score VALUES (14,4,11);

INSERT INTO score VALUES (15,4,9);

INSERT INTO score VALUES (16,4,20);

INSERT INTO score VALUES (18,4,11);

INSERT INTO score VALUES (19,4,15);

INSERT INTO score VALUES (20,4,17);

INSERT INTO score VALUES (21,4,13);

INSERT INTO score VALUES (22,4,20);

INSERT INTO score VALUES (23,4,13);

INSERT INTO score VALUES (24,4,12);
INSERT INTO score VALUES (25,4,10);

INSERT INTO score VALUES (26,4,15);

INSERT INTO score VALUES (28,4,17);

INSERT INTO score VALUES (30,4,11);

INSERT INTO score VALUES (31,4,19);

INSERT INTO score VALUES (1,5,15);

INSERT INTO score VALUES (2,5,12);

INSERT INTO score VALUES (3,5,11);

INSERT INTO score VALUES (5,5,13);

INSERT INTO score VALUES (6,5,18);

INSERT INTO score VALUES (7,5,14);

INSERT INTO score VALUES (8,5,18);

INSERT INTO score VALUES (9,5,13);

INSERT INTO score VALUES (10,5,14);

INSERT INTO score VALUES (11,5,18);

INSERT INTO score VALUES (12,5,8);

INSERT INTO score VALUES (13,5,8);

INSERT INTO score VALUES (14,5,16);

INSERT INTO score VALUES (15,5,13);

INSERT INTO score VALUES (16,5,15);

INSERT INTO score VALUES (17,5,11);

INSERT INTO score VALUES (18,5,18);

INSERT INTO score VALUES (19,5,18);
INSERT INTO score VALUES (20,5,14);

INSERT INTO score VALUES (21,5,17);

INSERT INTO score VALUES (22,5,17);

INSERT INTO score VALUES (23,5,15);

INSERT INTO score VALUES (25,5,14);

INSERT INTO score VALUES (26,5,8);

INSERT INTO score VALUES (28,5,20);

INSERT INTO score VALUES (29,5,16);

INSERT INTO score VALUES (31,5,9);

INSERT INTO score VALUES (1,6,100);

INSERT INTO score VALUES (2,6,91);

INSERT INTO score VALUES (3,6,94);

INSERT INTO score VALUES (4,6,74);

INSERT INTO score VALUES (5,6,97);

INSERT INTO score VALUES (6,6,89);

INSERT INTO score VALUES (7,6,76);

INSERT INTO score VALUES (8,6,65);

INSERT INTO score VALUES (9,6,73);

INSERT INTO score VALUES (10,6,63);

INSERT INTO score VALUES (11,6,98);

INSERT INTO score VALUES (12,6,75);

INSERT INTO score VALUES (14,6,77);

INSERT INTO score VALUES (15,6,62);

INSERT INTO score VALUES (16,6,98);

INSERT INTO score VALUES (17,6,94);

INSERT INTO score VALUES (18,6,94);

INSERT INTO score VALUES (19,6,74);
INSERT INTO score VALUES (20,6,62);

INSERT INTO score VALUES (21,6,73);

INSERT INTO score VALUES (22,6,95);

INSERT INTO score VALUES (24,6,68);

INSERT INTO score VALUES (25,6,85);

INSERT INTO score VALUES (26,6,91);
INSERT INTO score VALUES (27,6,70);

INSERT INTO score VALUES (28,6,77);

INSERT INTO score VALUES (29,6,66);

INSERT INTO score VALUES (30,6,68);

INSERT INTO score VALUES (31,6,76);

#--Populate the absence table

INSERT INTO `absence` VALUES (3,'2015-09-03');

INSERT INTO `absence` VALUES (5,'2015-09-03');

INSERT INTO `absence` VALUES (10,'2015-09-06');

INSERT INTO `absence` VALUES (10,'2015-09-09');

INSERT INTO `absence` VALUES (17,'2015-09-07');

INSERT INTO `absence` VALUES (20,'2015-09-07');

Answer 1

1.
SELECT date, student_id FROM absence UNION SELECT date, category FROM grade_event

2.
SELECT student_id,score.event_id,score,category FROM score,grade_event WHERE score.event_id = grade_event.event_id AND student_id=17

3.
SELECT student.student_id AS `ID`, name AS `Name` , COUNT(*) AS `Number Taken` FROM student, score WHERE student.student_id = score.student_id GROUP BY student.student_id ORDER BY student.student_id

4.
SELECT student.student_id AS `ID`, name AS `Name` , category AS `Category`, grade_event.date as `Date` FROM student,grade_event, absence WHERE student.student_id = absence.student_ID AND grade_event.date = absence.date;

5.
SELECT event_id AS `Event`, AVG(score) AS `Average Score` FROM score GROUP BY event_id;