Question

Using MySQL...

1. Join the Students, Enrollment, and Classes table to display the UniqueCode and the students’ classes (34 rows)
2. Alter the Enrollment table to create column FullName
3. Update the Enrollment table (using a subquery) to fill in the FullName selecting the students from the Student table (34 rows changed)
4. Using Union (WHERE conditions limited to 1 condition each), display the students names whose HeightInches is greater than 65 or less than 72 (13 rows)
5. Using “Intersection”, display the students names whose HeightInches is greater than 65 and whose ClassID is less than 6 (7 rows)

CREATE DATABASE school;
Use school;

drop table if exists Schools;
drop table if exists Departments;
drop table if exists Classes;
drop table if exists Students;

CREATE TABLE Schools (
   SchoolID int PRIMARY KEY,
SchoolName varchar(255)
);

CREATE TABLE Departments (
   DepartmentID int PRIMARY KEY,
SchoolID int,
DepartmentName varchar(255)
);

CREATE TABLE Classes (
   ClassID int PRIMARY KEY,
DepartmentID int,
ClassTitle varchar(255)
);

CREATE TABLE Enrollment (
   CIN int,
ClassID int
);

CREATE TABLE Students (
   SSN int PRIMARY KEY,
CIN int,
FirstName varchar(255),
LastName varchar(255),
Age int,BirthMonth varchar(255),
HeightInches int,
Address varchar(255),
City varchar(255),
PhoneNumber varchar(255),
Email varchar(255),
EyeColor varchar(50),
HairColor varchar(50),
Gender varchar(1),
FavColor varchar(255),
FavNumber int
);

INSERT INTO Students VALUES(1, 1, "David", "San", 22, "March", 72, "1234", "Flowerville", "231-246-4361", "[email protected]", "BRN", "BLK", "M", "Blue", 7);
INSERT INTO Students VALUES(2, 5, "Randy", "Bernard", 21, "February", 69, "7123", "Rossette Park", "634-124-7452", "[email protected]", "GRN", "BRN", "M", "Green", 12);
INSERT INTO Students VALUES(3, 6, "Andy", "Lam", 24, "December", 59, "9072", "Jefferson", "124-564-6354", "[email protected]", "BRN", "BLK", "M", "Grey", 32);
INSERT INTO Students VALUES(4, 8, "Pranil", "Watakana", 23, "February", 63, "2146", "Rossette Park", "543-325-3521", "[email protected]", "BRN", "BLK", "M", "Grey", 3);
INSERT INTO Students VALUES(5, 11, "Jeff", "Bezos", 22, "April", 65, "6312", "Grey Valley", "351-532-6439", "[email protected]", "BRN", "BRN", "M", "Yellow", 0);
INSERT INTO Students VALUES(6, 13, "Makoto", "Amagi", 21, "September", 64, "39857","Flowerville", "314-352-5321", "[email protected]", "BRN", "BRN", "F", "Black", 3);
INSERT INTO Students VALUES(7, 2, "Jeff", "Gonzales", 20, "October", 68, "4361", "Flowerville", "231-342-5467", "[email protected]", "BRN", "BLK", "M", "Blue", 21);
INSERT INTO Students VALUES(8, 3, "Anna", "Grayson", 21, "January", 62, "6543", "Rossette Park", "634-423-5763", "[email protected]", "BRN", "BRN", "F", "Green", 12);
INSERT INTO Students VALUES(9, 4, "Kathryn", "Moloney", 24, "May", 68, "5437", "Jefferson", "124-684-4131", "[email protected]", "GRN", "BLK", "F", "Grey",3);
INSERT INTO Students VALUES(10, 7, "Makoto", "Yuki", 19, "April", 61, "75632", "Rossette Park", "543-354-6421", "[email protected]", "BRN", "BRN", "F", "Grey", 7);
INSERT INTO Students VALUES(11, 9, "Pierce", "Santos", 21, "January", 74, "3543", "Grey Valley", "351-542-7541", "[email protected]", "BRN", "BLK", "M", "Yellow", 10);
INSERT INTO Students VALUES(12, 10, "Soliel", "Estrada", 20, "June", 66, "3754", "Flowerville", "314-325-6543", "[email protected]", "BRN", "BLU", "F", "Black", 5);
INSERT INTO Students VALUES(13, 12, "Andy", "Chen", 22, "September", 69, "3865", "Flowerville", "314-231-4233", "[email protected]", "BRN", "BLK", "M", "Black",3);

INSERT INTO Enrollment VALUES(1, 2);
INSERT INTO Enrollment VALUES(2, 2);
INSERT INTO Enrollment VALUES(2, 3);
INSERT INTO Enrollment VALUES(2, 5);
INSERT INTO Enrollment VALUES(3, 2);
INSERT INTO Enrollment VALUES(3, 3);
INSERT INTO Enrollment VALUES(3, 6);
INSERT INTO Enrollment VALUES(4, 2);
INSERT INTO Enrollment VALUES(4, 15);
INSERT INTO Enrollment VALUES(4, 8);
INSERT INTO Enrollment VALUES(5, 2);
INSERT INTO Enrollment VALUES(5, 3);
INSERT INTO Enrollment VALUES(5, 6);
INSERT INTO Enrollment VALUES(6, 2);
INSERT INTO Enrollment VALUES(6, 5);
INSERT INTO Enrollment VALUES(6, 8);
INSERT INTO Enrollment VALUES(7, 2);
INSERT INTO Enrollment VALUES(7, 3);
INSERT INTO Enrollment VALUES(8, 2);
INSERT INTO Enrollment VALUES(8, 13);
INSERT INTO Enrollment VALUES(8, 8);
INSERT INTO Enrollment VALUES(9, 2);
INSERT INTO Enrollment VALUES(9, 5);
INSERT INTO Enrollment VALUES(10, 2);
INSERT INTO Enrollment VALUES(10, 3);
INSERT INTO Enrollment VALUES(10, 14);
INSERT INTO Enrollment VALUES(11, 2);
INSERT INTO Enrollment VALUES(11, 15);
INSERT INTO Enrollment VALUES(12, 2);
INSERT INTO Enrollment VALUES(12, 3);
INSERT INTO Enrollment VALUES(12, 6);
INSERT INTO Enrollment VALUES(13, 2);
INSERT INTO Enrollment VALUES(13, 5);
INSERT INTO Enrollment VALUES(13, 13);
INSERT INTO Enrollment VALUES(14, 2);
INSERT INTO Enrollment VALUES(14, 14);
INSERT INTO Enrollment VALUES(15, 2);
INSERT INTO Enrollment VALUES(15, 5);

INSERT INTO Classes VALUES(1, 1, "CS1010");
INSERT INTO Classes VALUES(2, 1, "CS1222");
INSERT INTO Classes VALUES(3, 1, "CS2011");
INSERT INTO Classes VALUES(4, 1, "CS2012");
INSERT INTO Classes VALUES(5, 3, "ENGR1540");
INSERT INTO Classes VALUES(6, 2, "EE1200");
INSERT INTO Classes VALUES(7, 2, "EE2100");
INSERT INTO Classes VALUES(8, 3, "CE1200");
INSERT INTO Classes VALUES(9, 3, "CE2100");
INSERT INTO Classes VALUES(10, 4, "ME1200");
INSERT INTO Classes VALUES(11, 4, "ME2100");
INSERT INTO Classes VALUES(12, 5, "CS101");
INSERT INTO Classes VALUES(13, 5, "CS102");
INSERT INTO Classes VALUES(14, 5, "CS201");
INSERT INTO Classes VALUES(15, 6, "TECH1010");
INSERT INTO Classes VALUES(16, 6, "TECH2010");

INSERT INTO Departments VALUES(1, 1, "Computer Science");
INSERT INTO Departments VALUES(2, 1, "Electrical Engineering");
INSERT INTO Departments VALUES(3, 1, "Civil Engineering");
INSERT INTO Departments VALUES(4, 1, "Mechanical Engineering");
INSERT INTO Departments VALUES(5, 2, "Computer Engineering");
INSERT INTO Departments VALUES(6, 3, "Technology");

INSERT INTO Schools VALUES(1, "Cal State LA");
INSERT INTO Schools VALUES(2, "ELAC");
INSERT INTO Schools VALUES(3, "PCC");

SELECT * FROM Students;

Answer 1

1. Select SSN, Students.CIN, Classes.ClassID from Students inner join Enrollment on Students.CIN = Enrollment.CIN inner join Classes on Enrollment.ClassID = Classes.ClassID;
2. Alter Table Enrollment Add Column FullName varchar(540);
3. Update Enrollment Set FullName = ( Select Concat(FirstName, ' ', LastName) from Students) where Enrollment.CIN = Students.CIN;
4. Select FirstName, LastName from Students where HeightInches > 65 UNION Select FirstName, LastName from Students where HeightInches < 72;
5. Select FirstName, LastName from Students where HeightInches > 65 INTERSECT Select FirstName, LastName from Students inner join Enrollment on Students.CIN = Enrollment.CIN where ClassID < 6;

Do ask if any doubt.