Help with Queries.

DROP DATABASE IF EXISTS HAPPY_LEARNING;
CREATE DATABASE HAPPY_LEARNING;
SHOW DATABASES;
USE HAPPY_LEARNING;

CREATE TABLE IF NOT EXISTS `HAPPY_LEARNING`.`CLASS` (
`CODE` CHAR(2) NOT NULL,
`LEVEL` VARCHAR(10) NOT NULL,
`COLOR` VARCHAR(10) NOT NULL,
`ROOM` INT NOT NULL,
`PHONE` CHAR(12) NOT NULL,
`CAPACITY` INT NOT NULL,
`LOCATION` VARCHAR(100) NULL,
PRIMARY KEY (`CODE`),
UNIQUE INDEX `ROOM_UNIQUE` (`ROOM` ASC) VISIBLE,
UNIQUE INDEX `PHONE_UNIQUE` (`PHONE` ASC) VISIBLE)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `HAPPY_LEARNING`.`STUDENT` (
`STUDENT_ID` INT NOT NULL,
`FIRST_NAME` VARCHAR(30) NOT NULL,
`MIDDLE_NAME` VARCHAR(30) NULL,
`LAST_NAME` VARCHAR(30) NOT NULL,
`NICKNAME` VARCHAR(15) NULL,
`DATE_OF_BIRTH` DATE NOT NULL,
`STREET` VARCHAR(50) NOT NULL,
`CITY` VARCHAR(25) NOT NULL,
`ZIPCODE` VARCHAR(10) NOT NULL,
`STATE` CHAR(2) NOT NULL,
`CLASS_CODE` CHAR(2) NOT NULL,
PRIMARY KEY (`STUDENT_ID`),
INDEX `fk_STUDENT_CLASS_idx` (`CLASS_CODE` ASC) VISIBLE,
CONSTRAINT `fk_STUDENT_CLASS`
FOREIGN KEY (`CLASS_CODE`)
REFERENCES `HAPPY_LEARNING`.`CLASS` (`CODE`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `HAPPY_LEARNING`.`TEACHER` (
`TEACHER_ID` INT NOT NULL,
`FIRST_NAME` VARCHAR(45) NOT NULL,
`LAST_NAME` VARCHAR(45) NOT NULL,
`HOME_NUM` CHAR(12) NULL,
`WORK_EMAIL` VARCHAR(25) NOT NULL,
`PERSONAL_EMAIL` VARCHAR(25) NULL,
`COLLEGE` VARCHAR(45) NOT NULL,
`MAJOR` VARCHAR(45) NOT NULL,
`CLASS_CODE` CHAR(2) NOT NULL,
`DEGREE` VARCHAR(65) NOT NULL,
PRIMARY KEY (`TEACHER_ID`),
INDEX `fk_TEACHER_CLASS1_idx` (`CLASS_CODE` ASC) VISIBLE,
UNIQUE INDEX `WORK_EMAIL_UNIQUE` (`WORK_EMAIL` ASC) VISIBLE,
CONSTRAINT `fk_TEACHER_CLASS1`
FOREIGN KEY (`CLASS_CODE`)
REFERENCES `HAPPY_LEARNING`.`CLASS` (`CODE`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `HAPPY_LEARNING`.`PARENT` (
`PARENT_ID` INT NOT NULL,
`FIRST_NAME` VARCHAR(30) NOT NULL,
`LAST_NAME` VARCHAR(30) NOT NULL,
`HOME_NUM` CHAR(12) NULL,
`MOBILE_NUM` CHAR(12) NOT NULL,
`WORK_NUM` CHAR(12) NULL,
`EMAIL` VARCHAR(25) NULL,
`RELATION` VARCHAR(45) NOT NULL,
PRIMARY KEY (`PARENT_ID`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `HAPPY_LEARNING`.`ASSIGNMENT` (
`PARENT_PARENT_ID` INT NOT NULL,
`STUDENT_STUDENT_ID` INT NOT NULL,
PRIMARY KEY (`PARENT_PARENT_ID`, `STUDENT_STUDENT_ID`),
INDEX `fk_PARENT_has_STUDENT_STUDENT1_idx` (`STUDENT_STUDENT_ID` ASC) VISIBLE,
INDEX `fk_PARENT_has_STUDENT_PARENT1_idx` (`PARENT_PARENT_ID` ASC) VISIBLE,
CONSTRAINT `fk_PARENT_has_STUDENT_PARENT1`
FOREIGN KEY (`PARENT_PARENT_ID`)
REFERENCES `HAPPY_LEARNING`.`PARENT` (`PARENT_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_PARENT_has_STUDENT_STUDENT1`
FOREIGN KEY (`STUDENT_STUDENT_ID`)
REFERENCES `HAPPY_LEARNING`.`STUDENT` (`STUDENT_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

#SHOW TABLE STRUCTURE
SHOW TABLES;
DESCRIBE CLASS;
DESCRIBE STUDENT;
DESCRIBE TEACHER;
DESCRIBE PARENT;
DESCRIBE ASSIGNMENT;

INSERT INTO CLASS VALUES('GT', 'Turtles', 'Green', '25', '111-999-0000', '27', 'North West'),
('GF', 'Foxes', 'Green', '26', '222-000-1111', '25', 'West'),
('GM', 'Monkeys', 'Green', '27', '333-000-2222', '31', 'South East'),
('RF', 'Foxes', 'Red', '28', '444-555-6600', '29', 'East'),
('RT', 'Turtles', 'Red', '29', '999-999-9999', '27', 'South West'),
('RM', 'Monkeys', 'Red', '30', '000-111-2222', '26', 'South'),
('BM', 'Monkeys', 'Blue', '31', '555-888-2222', '30', 'North'),
('BF', 'Foxes', 'Blue', '32', '777-777-7777', '28', 'North East'),
('BT', 'Turtles', 'Blue', '33', '888-999-8888', '26', 'Mid West');

INSERT INTO STUDENT VALUES('1', 'John', 'Michael', 'Doe', NULL, '2016-03-25', 'King', 'Colombia', '20101', 'SC', 'RT'),
('2', 'Jane', NULL, 'Smith', NULL, '2016-04-25', 'Main', 'Charleston', '20101', 'SC', 'GT'),
('3', 'John', NULL, 'Williams', 'JP', '2016-05-25', 'Dave', 'Colmbia', '20101', 'SC', 'GT'),
('4', 'Katie', 'Kasie', 'Maroney', 'Kat', '2016-06-25', 'Davis', 'River', '21102', 'SC', 'GM'),
('5', 'Cameron', 'Roderick', 'Johnson', 'Cam', '2016-07-25', 'Amanda', 'Middleburg', '21102', 'SC', 'BT'),
('6', 'Kevin','Michael', 'Carlson', NULL, '2016-08-25', 'Walney', 'Middleburg', '21100', 'SC', 'BM'),
('7', 'Christine', 'Alexa', 'Doe', NULL, '2016-09-25', 'King', 'Colombia', '20101', 'SC', 'GM'),
('8', 'Justin', 'Robert', 'Fields', NULL, '2016-10-25', 'Queen', 'Charleston', '21110', 'SC', 'RM'),
('9', 'Charles', 'Jack', 'Donovan', 'Charlie', '2016-11-25', 'Rainwater', 'McLean', '29900', 'VA', 'GF'),
('10', 'Alexander', 'Alfred', 'Doe', NULL, '2016-12-25', 'Hogan', 'Falls', '20105', 'SC', 'BF'),
('11', 'Samantha', NULL, 'Ackerman', 'Sam', '2016-03-25', 'Kline', 'River', '20101', 'SC', 'RF'),
('12', 'Elizabeth', 'Katherine', 'Lemon', 'Liz', '2016-01-25', 'Link', 'Halifax', '20101', 'SC', 'BT');

INSERT INTO TEACHER VALUES('100', 'Matthew', 'Johson', NULL, 'MJ@mial.com', NULL, 'South Carolina University', 'Math', 'GT', 'BA MATH'),
('101', 'Mary', 'Doe', NULL, 'MD@mial.com', NULL, 'North Carolina University', 'English', 'GF', 'BA ENGLISH'),
('102', 'John', 'Smith', NULL, 'JS@mial.com', NULL, 'Penn State University ', 'History', 'GM', 'BA HISTORY'),
('103', 'Amanda', 'Grey', NULL, 'AG@mial.com', NULL, 'Duke University', 'Biology', 'RF', 'BS BIOLOGY'),
('104', 'Shane', 'Austin', NULL, 'SA@mial.com', NULL, 'Virginia University', 'Chemistry', 'RT', 'MS CHEMISTRY'),
('105', 'Dorris', 'Johson', NULL, 'DJ@mial.com', NULL, 'John Hopkins University', 'Psychology', 'RM', 'PhD PSYCHOLOGY (Child Psychology)'),
('106', 'Stephanie', 'Miller', NULL, 'SM@mial.com', NULL, 'Jame Hawkins University', 'Sociology', 'BM', 'BA SOCIOLOGY'),
('107', 'William', 'Meadow', NULL, 'WM@mial.com', NULL, 'Radford University', 'Math', 'BF', 'BA MATH'),
('108', 'Katie', 'Nolan', NULL, 'KN@mial.com', NULL, 'Rutgers University', 'Community Health', 'BT', 'BS Community Health');

INSERT INTO PARENT VALUES('200', 'Michael', 'Doe', NULL, '899-999-0000', NULL, 'MDOE@mial.com', 'Father'),
('201', 'Kelly', 'Smith', NULL, '999-999-1000', NULL, 'KS@mial.com', 'Mother'),
('202', 'Daryl', 'Smith', NULL, '799-999-2000', NULL, 'DS@mial.com', 'Father'),
('203', 'Justin', 'Williams', NULL, '123-456-7890', NULL, 'JW@mial.com', 'Father'),
('204', 'Kevin', 'Maroney', NULL, '234-567-8901', NULL, 'KME@mial.com', 'Father'),
('205', 'Mary', 'Johson', NULL, '345-999-5000', NULL, 'MJO@mial.com', 'Mother'),
('206', 'Michael', 'Johnson', NULL, '688-999-6000', NULL, 'MMMJ@mial.com', 'Father'),
('207', 'Hope', 'Carlson', NULL, '999-999-7000', NULL, 'HC@mial.com', 'Mother'),
('208', 'Rachel', 'Doe', NULL, '449-999-8000', NULL, 'RDOE@mial.com', 'Mother'),
('209', 'Brianna', 'Fields', NULL, '199-999-9000', NULL, 'BF@mial.com', 'Mother'),
('210', 'Leslie', 'Donovan', NULL, '299-999-1100', NULL, 'LD@mial.com', 'Mother'),
('211', 'Alfred', 'Doe', NULL, '399-999-1200', NULL, 'ADOE@mial.com', 'Father'),
('212', 'Don', 'Lemon', NULL, '699-999-1300', NULL, 'DL@mial.com', 'Father'),
('213', 'Michelle', 'Lemon', NULL, '099-999-1400', NULL, 'ML@mial.com', 'Mother'),
('214','Joseph', 'Ackerman', NULL, '222-333-0000', NULL, 'JA@mial.com', 'Father');

INSERT INTO ASSIGNMENT VALUES('200','1'),('200','7'), ('201','2'), ('202','2'), ('203','3'),('204', '4'),
('205','5'),('206','5'),('207','6'),('208','1'),('208','7'), ('209','8'), ('210', '9'),('211','10'),('212','12'),
('213', '12'), ('214', '11');

SELECT * FROM CLASS;
SELECT * FROM STUDENT;
SELECT * FROM TEACHER;
SELECT * FROM PARENT;
SELECT * FROM ASSIGNMENT;

Query Questions (please help , you help is greatly appreciated)

• 1) List all students in alphabetical order, with the level and color of the class and the name of the teacher.
• 2) For each class level and color show all the student enrolled in alphabetical order per each class (i.e. you start with blue foxes and show all the students enrolled alphabetically, then you go to green foxes, and so on).
• 3) For each parent in alphabetical order (show name and email), list all the students enrolled (show name and class level and color)
• 4) For each teacher (show name in alphabetical order) list all the students (show name in alphabetical order) and their parents (show names in alphabetical order)

Answer rating: 100% (QA)

1 List all students in alphabetical order with the level and color of the class and the name of the ... View the full answer