CREATE TABLE l7_roster (
  student_id TEXT,
  first_name TEXT,
  last_name TEXT
);
INSERT INTO l7_roster VALUES
('1', 'Stephanie', 'Peters'),
('2', 'Cassandra', 'Boyd'),
('3', 'Andrea', 'Mueller'),
('4', 'Jonathan', 'Li'),
('5', 'Jennifer', 'Harvey'),
('6', 'James', 'Tran'),
('7', 'Patrick', 'Thomas'),
('8', 'Rachel', 'Velasquez'),
('9', 'Aaron', 'Romero'),
('10', 'Jennifer', 'Phillips'),
('11', 'Michelle', 'Duarte'),
('12', 'Michelle', 'Tyler'),
('13', 'Carla', 'Wheeler'),
('14', 'Andrew', 'Harper'),
('15', 'Kyle', 'Herman'),
('16', 'Amy', 'Griffin'),
('17', 'Susan', 'Mckinney'),
('18', 'Shelley', 'Booth'),
('19', 'Jennifer', 'Watson'),
('20', 'Edward', 'Bryant'),
('21', 'Elizabeth', 'Henderson'),
('22', 'Charles', 'Bishop'),
('23', 'Amanda', 'Mathis'),
('24', 'Shannon', 'Skinner'),
('25', 'Alexis', 'Moreno'),
('26', 'Laura', 'Hernandez'),
('27', 'Matthew', 'Moreno'),
('28', 'Jared', 'Williams'),
('29', 'Rebecca', 'James'),
('30', 'Jose', 'Marquez'),
('31', 'Randy', 'Caldwell'),
('32', 'Lisa', 'Dorsey'),
('33', 'Jeffrey', 'Horton'),
('34', 'Aaron', 'Walsh'),
('35', 'Tiffany', 'Davis')
;
CREATE TABLE l7_hw_info (
  hw_id TEXT,
  name TEXT,
  total_points INT,
  due_date DATE
);
INSERT INTO l7_hw_info VALUES
('1', 'Reading 1', 15, '2021-09-15'),
('2', 'Reading 2', 16, '2021-09-22'),
('3', 'Reading 3', 34, '2021-09-26'),
('4', 'Problem Set 1', 34, '2021-10-03'),
('5', 'Homework 1', 42, '2021-10-13'),
('6', 'Homework 2', 33, '2021-10-26'),
('7', 'Problem Set 2', 49, '2021-11-08'),
('8', 'Problem Set 3', 49, '2021-11-09'),
('9', 'Project 1', 21, '2021-11-12'),
('10', 'Problem Set 4', 21, '2021-11-18')
;
CREATE TABLE l7_submissions (
  student_id TEXT,
  hw_id TEXT,
  submission_date DATE,
  earned_points INT
);
INSERT INTO l7_submissions VALUES
('1', '1', '2021-09-12', 9),
('1', '2', '2021-09-21', 14),
('1', '3', '2021-09-25', 23),
('1', '4', '2021-10-01', 11),
('1', '5', '2021-10-12', 41),
('1', '6', '2021-10-25', 32),
('1', '8', '2021-11-06', 15),
('1', '9', '2021-11-09', 13),
('1', '10', '2021-11-17', 12),
('2', '1', '2021-09-14', 0),
('2', '3', '2021-09-23', 27),
('2', '4', '2021-09-30', 0),
('2', '6', '2021-10-25', 12),
('2', '7', '2021-11-06', 8),
('2', '8', '2021-11-06', 5),
('2', '9', '2021-11-11', 8),
('2', '10', '2021-11-22', 0),
('3', '1', '2021-09-14', 11),
('3', '2', '2021-09-21', 16),
('3', '3', '2021-09-25', 33),
('3', '4', '2021-10-02', 28),
('3', '5', '2021-10-10', 30),
('3', '6', '2021-10-24', 3),
('3', '7', '2021-11-11', 30),
('3', '8', '2021-11-15', 33),
('3', '9', '2021-11-18', 19),
('3', '10', '2021-11-16', 17),
('4', '1', '2021-09-12', 8),
('4', '2', '2021-09-22', 11),
('4', '4', '2021-09-30', 23),
('4', '5', '2021-10-17', 40),
('4', '7', '2021-11-07', 21),
('4', '8', '2021-11-08', 30),
('4', '9', '2021-11-09', 8),
('4', '10', '2021-11-24', 0),
('5', '1', '2021-09-12', 5),
('5', '2', '2021-09-27', 12),
('5', '3', '2021-09-24', 23),
('5', '4', '2021-10-02', 19),
('5', '5', '2021-10-11', 23),
('5', '6', '2021-10-25', 3),
('5', '7', '2021-11-07', 20),
('5', '8', '2021-11-06', 20),
('5', '9', '2021-11-10', 18),
('5', '10', '2021-11-16', 7),
('7', '1', '2021-09-21', 7),
('7', '3', '2021-09-24', 12),
('7', '4', '2021-10-01', 23),
('7', '5', '2021-10-11', 15),
('7', '6', '2021-10-24', 16),
('7', '8', '2021-11-06', 0),
('7', '10', '2021-11-16', 10),
('8', '1', '2021-09-19', 0),
('8', '2', '2021-09-20', 0),
('8', '5', '2021-10-10', 31),
('8', '6', '2021-10-25', 7),
('8', '7', '2021-11-06', 43),
('8', '8', '2021-11-08', 36),
('8', '9', '2021-11-11', 18),
('8', '10', '2021-11-15', 13),
('9', '1', '2021-09-14', 3),
('9', '2', '2021-09-21', 10),
('9', '3', '2021-09-25', 26),
('9', '4', '2021-09-30', 32),
('9', '5', '2021-10-12', 7),
('9', '6', '2021-10-23', 23),
('9', '7', '2021-11-10', 2),
('9', '8', '2021-11-08', 14),
('9', '9', '2021-11-10', 16),
('10', '2', '2021-09-21', 4),
('10', '3', '2021-09-24', 19),
('10', '4', '2021-10-08', 17),
('10', '5', '2021-10-12', 2),
('10', '6', '2021-10-23', 32),
('10', '7', '2021-11-05', 19),
('10', '8', '2021-11-08', 43),
('10', '9', '2021-11-09', 4),
('10', '10', '2021-11-16', 1),
('11', '2', '2021-09-21', 8),
('11', '3', '2021-09-25', 33),
('11', '4', '2021-10-01', 27),
('11', '5', '2021-10-10', 1),
('11', '6', '2021-10-25', 8),
('11', '7', '2021-11-11', 48),
('11', '10', '2021-11-15', 5),
('12', '1', '2021-09-14', 6),
('12', '2', '2021-09-22', 2),
('12', '3', '2021-09-23', 5),
('12', '4', '2021-09-30', 0),
('12', '5', '2021-10-12', 13),
('12', '6', '2021-10-25', 12),
('12', '7', '2021-11-05', 47),
('12', '8', '2021-11-07', 11),
('12', '9', '2021-11-10', 13),
('12', '10', '2021-11-15', 1),
('13', '1', '2021-09-12', 7),
('13', '2', '2021-09-20', 2),
('13', '4', '2021-10-02', 28),
('13', '5', '2021-10-11', 4),
('13', '6', '2021-10-24', 5),
('13', '7', '2021-11-07', 19),
('13', '8', '2021-11-07', 33),
('13', '9', '2021-11-09', 9),
('13', '10', '2021-11-16', 13),
('14', '1', '2021-09-13', 6),
('14', '2', '2021-09-20', 16),
('14', '3', '2021-09-23', 4),
('14', '4', '2021-10-01', 25),
('14', '5', '2021-10-10', 38),
('14', '6', '2021-10-25', 2),
('14', '8', '2021-11-08', 39),
('14', '9', '2021-11-11', 19),
('14', '10', '2021-11-15', 3),
('15', '1', '2021-09-13', 12),
('15', '3', '2021-09-25', 12),
('15', '4', '2021-10-01', 33),
('15', '6', '2021-10-23', 32),
('15', '7', '2021-11-07', 10),
('15', '8', '2021-11-07', 44),
('15', '9', '2021-11-10', 1),
('15', '10', '2021-11-17', 10),
('16', '1', '2021-09-13', 2),
('16', '2', '2021-09-20', 12),
('16', '3', '2021-09-25', 27),
('16', '4', '2021-10-02', 11),
('16', '6', '2021-10-30', 0),
('16', '7', '2021-11-13', 7),
('16', '8', '2021-11-08', 14),
('16', '9', '2021-11-10', 13),
('16', '10', '2021-11-15', 1),
('17', '1', '2021-09-14', 5),
('17', '2', '2021-09-21', 4),
('17', '3', '2021-09-23', 30),
('17', '4', '2021-10-02', 29),
('17', '6', '2021-10-24', 26),
('17', '7', '2021-11-05', 23),
('17', '8', '2021-11-07', 13),
('17', '9', '2021-11-11', 3),
('17', '10', '2021-11-15', 11),
('18', '1', '2021-09-12', 4),
('18', '2', '2021-09-21', 2),
('18', '3', '2021-09-23', 20),
('18', '4', '2021-09-30', 15),
('18', '5', '2021-10-12', 3),
('18', '8', '2021-11-12', 17),
('18', '9', '2021-11-11', 14),
('18', '10', '2021-11-15', 19),
('19', '1', '2021-09-12', 12),
('19', '2', '2021-09-20', 5),
('19', '3', '2021-09-24', 25),
('19', '4', '2021-09-30', 22),
('19', '5', '2021-10-15', 8),
('19', '6', '2021-10-23', 28),
('19', '7', '2021-11-06', 1),
('19', '9', '2021-11-09', 16),
('19', '10', '2021-11-16', 10),
('20', '1', '2021-09-14', 3),
('20', '2', '2021-09-21', 1),
('20', '3', '2021-09-25', 5),
('20', '4', '2021-10-02', 4),
('20', '5', '2021-10-12', 20),
('20', '6', '2021-10-28', 9),
('20', '7', '2021-11-10', 19),
('20', '8', '2021-11-07', 24),
('20', '9', '2021-11-09', 13),
('20', '10', '2021-11-23', 15),
('21', '1', '2021-09-19', 8),
('21', '2', '2021-09-21', 4),
('21', '3', '2021-09-23', 14),
('21', '4', '2021-10-04', 10),
('21', '5', '2021-10-11', 26),
('21', '6', '2021-10-30', 24),
('21', '7', '2021-11-07', 26),
('21', '8', '2021-11-06', 14),
('21', '9', '2021-11-09', 10),
('21', '10', '2021-11-17', 16),
('22', '2', '2021-09-20', 7),
('22', '3', '2021-09-24', 10),
('22', '4', '2021-09-30', 21),
('22', '6', '2021-10-26', 20),
('22', '7', '2021-11-07', 31),
('22', '8', '2021-11-07', 28),
('22', '9', '2021-11-10', 0),
('22', '10', '2021-11-16', 21),
('23', '1', '2021-09-12', 3),
('23', '2', '2021-09-19', 8),
('23', '3', '2021-09-25', 31),
('23', '4', '2021-10-01', 25),
('23', '5', '2021-10-12', 18),
('23', '6', '2021-10-24', 19),
('23', '7', '2021-11-13', 27),
('23', '8', '2021-11-07', 16),
('23', '9', '2021-11-11', 13),
('23', '10', '2021-11-16', 9),
('24', '1', '2021-09-19', 3),
('24', '2', '2021-09-19', 6),
('24', '3', '2021-09-25', 5),
('24', '4', '2021-10-02', 4),
('24', '6', '2021-10-30', 4),
('24', '7', '2021-11-06', 27),
('24', '8', '2021-11-07', 31),
('24', '9', '2021-11-17', 11),
('25', '1', '2021-09-12', 3),
('25', '2', '2021-09-20', 5),
('25', '3', '2021-09-25', 23),
('25', '4', '2021-10-02', 23),
('25', '5', '2021-10-11', 26),
('25', '6', '2021-10-25', 29),
('25', '7', '2021-11-06', 28),
('25', '8', '2021-11-06', 20),
('25', '9', '2021-11-10', 3),
('25', '10', '2021-11-17', 1),
('26', '1', '2021-09-13', 5),
('26', '2', '2021-09-24', 2),
('26', '3', '2021-09-24', 15),
('26', '4', '2021-10-01', 3),
('26', '5', '2021-10-11', 2),
('26', '6', '2021-10-24', 26),
('26', '7', '2021-11-07', 2),
('26', '8', '2021-11-07', 21),
('26', '9', '2021-11-11', 12),
('26', '10', '2021-11-16', 2),
('27', '1', '2021-09-13', 7),
('27', '2', '2021-09-21', 2),
('27', '3', '2021-09-23', 28),
('27', '4', '2021-10-07', 0),
('27', '5', '2021-10-12', 3),
('27', '6', '2021-10-24', 8),
('27', '8', '2021-11-13', 18),
('27', '9', '2021-11-11', 7),
('27', '10', '2021-11-23', 8),
('28', '1', '2021-09-13', 6),
('28', '2', '2021-09-19', 15),
('28', '3', '2021-10-01', 11),
('28', '4', '2021-10-08', 20),
('28', '5', '2021-10-10', 23),
('28', '6', '2021-10-28', 26),
('28', '7', '2021-11-05', 49),
('28', '8', '2021-11-08', 22),
('28', '9', '2021-11-09', 0),
('28', '10', '2021-11-15', 0),
('29', '1', '2021-09-12', 11),
('29', '2', '2021-09-21', 14),
('29', '3', '2021-10-02', 3),
('29', '5', '2021-10-10', 14),
('29', '6', '2021-10-23', 27),
('29', '7', '2021-11-06', 15),
('29', '8', '2021-11-06', 11),
('29', '10', '2021-11-15', 20),
('30', '1', '2021-09-14', 10),
('30', '2', '2021-09-20', 13),
('30', '3', '2021-09-25', 24),
('30', '4', '2021-10-01', 11),
('30', '5', '2021-10-11', 2),
('30', '8', '2021-11-08', 0),
('30', '9', '2021-11-12', 21),
('30', '10', '2021-11-15', 14),
('31', '1', '2021-09-13', 9),
('31', '3', '2021-09-23', 28),
('31', '4', '2021-10-02', 1),
('31', '5', '2021-10-11', 32),
('31', '7', '2021-11-05', 48),
('31', '8', '2021-11-08', 6),
('31', '9', '2021-11-10', 9),
('31', '10', '2021-11-15', 16),
('32', '1', '2021-09-21', 14),
('32', '2', '2021-09-25', 11),
('32', '4', '2021-10-01', 14),
('32', '5', '2021-10-10', 6),
('32', '7', '2021-11-11', 46),
('32', '8', '2021-11-08', 7),
('32', '9', '2021-11-11', 1),
('32', '10', '2021-11-17', 8),
('33', '2', '2021-09-27', 8),
('33', '3', '2021-09-23', 5),
('33', '4', '2021-10-02', 29),
('33', '5', '2021-10-11', 34),
('33', '6', '2021-10-24', 9),
('33', '8', '2021-11-12', 24),
('33', '9', '2021-11-10', 3),
('33', '10', '2021-11-17', 10),
('34', '1', '2021-09-14', 15),
('34', '2', '2021-09-19', 6),
('34', '3', '2021-09-25', 7),
('34', '5', '2021-10-11', 5),
('34', '6', '2021-10-23', 0),
('34', '7', '2021-11-05', 35),
('34', '8', '2021-11-07', 27),
('34', '9', '2021-11-09', 20),
('34', '10', '2021-11-17', 13),
('35', '1', '2021-09-20', 13),
('35', '2', '2021-09-21', 11),
('35', '3', '2021-09-25', 2),
('35', '4', '2021-10-01', 3),
('35', '5', '2021-10-12', 27),
('35', '6', '2021-10-23', 10),
('35', '7', '2021-11-07', 37),
('35', '8', '2021-11-07', 41),
('35', '9', '2021-11-09', 8),
('35', '10', '2021-11-15', 12)
;
