Question

DROP DATABASE class;CREATE DATABASE class;Use class;drop table if exists Class;drop table if exists Student;CREATE TABLE Class (CIN int PRIMARY KEY, FirstName varchar(255), LastName varchar(255), Gender varchar(1), EyeColor varchar(50), HairColor varchar(50), HeightInches int,CurrentGrade varchar(1));CREATE TABLE Student (SSN int PRIMARY KEY,FirstName varchar(255),LastName varchar(255), Age int,BirthMonth varchar(255),HeightInches int,Address varchar(255),City varchar(255),PhoneNumber varchar(12),Email varchar(255),FavColor varchar(255),FavNumber int);INSERT INTO Class VALUES(1, "David", "San", "M", "BRN", "BLK", 72, "-");INSERT INTO Class VALUES(2, "Jeff", "Gonzales", "M", "BRN", "BLK", 68, "B");INSERT INTO Class VALUES(3, "Anna", "Grayson", "F", "BRN", "BRN", 62, "A");INSERT INTO Class VALUES(4, "Kathryn", "Moloney", "F", "GRN", "BLK", 68, "B");INSERT INTO Class VALUES(5, "Randy", "Bernard", "M", "GRN", "BRN", 69, "A");INSERT INTO Class VALUES(6, "Andy", "Lam", "M", "BRN", "BLK", 59, "C");INSERT INTO Class VALUES(7, "Makoto", "Yuki", "F", "BRN", "BRN", 61, "A");INSERT INTO Class VALUES(8, "Pranil", "Watakana", "M", "BRN", "BLK", 63, "D");INSERT INTO Class VALUES(9, "Pierce", "Santos", "M", "BRN", "BLK", 74, "B");INSERT INTO Class VALUES(10, "Soliel", "Estrada", "F", "BRN", "BLU", 66, "B");INSERT INTO Class VALUES(11, "Jeff", "Bezos", "M", "BRN", "BRN", 65, "B");INSERT INTO Class VALUES(12, "Andy", "Chen", "M", "BRN", "BLK", 69, "A");INSERT INTO Class VALUES(13, "Makoto", "Amagi", "F", "BRN", "BRN", 64, "C");INSERT INTO Student VALUES(1, "David", "San", 22, "March", 72, "1234", "Flowerville", "231-246-4361", "[email protected]", "Blue", 7);INSERT INTO Student VALUES(2, "Randy", "Bernard", 21, "February", 69, "7123", "Rossette Park", "634-124-7452", "[email protected]", "Green", 12);INSERT INTO Student VALUES(3, "Andy", "Lam", 24, "December", 59, "9072", "Jefferson", "124-564-6354", "[email protected]", "Grey", 32);INSERT INTO Student VALUES(4, "Pranil", "Watakana", 23, "February", 63, "2146", "Rossette Park", "543-325-3521", "[email protected]", "Grey", 3);INSERT INTO Student VALUES(5, "Jeff", "Bezos", 22, "April", 65, "6312", "Grey Valley", "351-532-6439", "[email protected]", "Yellow", 0);INSERT INTO Student VALUES(6, "Makoto", "Amagi", 21, "September", 64, "39857", "Flowerville", "314-352-5321", "[email protected]", "Black", 3);

INSERT INTO Student VALUES(7, "Jeff", "Gonzales", 20, "October", 68, "4361", "Flowerville", "231-342-5467", "[email protected]", "Blue", 21);INSERT INTO Student VALUES(8, "Anna", "Grayson", 21, "January", 62, "6543", "Rossette Park", "634-423-5763", "[email protected]", "Green", 12);INSERT INTO Student VALUES(9, "Kathryn", "Moloney", 24, "May", 68, "5437", "Jefferson", "124-684-4131", "[email protected]", "Grey", 3);INSERT INTO Student VALUES(10, "Makoto", "Yuki", 19, "April", 61, "75632", "Rossette Park", "543-354-6421", "[email protected]", "Grey", 7);INSERT INTO Student VALUES(11, "Pierce", "Santos", 21, "January", 74, "3543", "GreyValley", "351-542-7541", "[email protected]", "Yellow", 10);INSERT INTO Student VALUES(12, "Soliel", "Estrada", 20, "June", 66, "3754", "Flowerville", "314-325-6543", "[email protected]", "Black", 5);INSERT INTO Student VALUES(13, "Andy", "Chen", 22, "September", 69, "3865", "Flowerville", "314-231-4233", "[email protected]", "Black", 3);

-----------------------------------------------------------------------------------------------------------------------

1. Assign grades for students who lived in Rossette Park to be a B
2. Add another table for students who had an altered grade (in this case students who lived in Rossette Park)
3. Add the CIN, FirstName, LastName, Gender, Address, City, and Email of the students who had an altered grade to the table
4. Remove students from the student table who live in Rossette Park
5. Join all 3 tables and display the Names, Gender, and City of the students (Going to need to use ON)

Use
SET SQL_SAFE_UPDATES = 0;
To disable Safe Mode if prompted.

Not sure where to begin, as my instructor taught the theory but never any live coding session.

Answer 1

//creating the database and using it

DROP DATABASE class;
CREATE DATABASE class;
Use class;

//creating the tables

drop table if exists Class;
drop table if exists Student;

CREATE TABLE Class(
 CIN int PRIMARY KEY,
 FirstName varchar(255),
 LastName varchar(255),
 Gender varchar(1),
 EyeColor varchar(50),
 HairColor varchar(50),
 HeightInches int,
 CurrentGrade varchar(1));

CREATE TABLE Student(
 SSN int PRIMARY KEY,
 FirstName varchar(255),
 LastName varchar(255),
 Age int,
 BirthMonth varchar(255),
 HeightInches int,
 Address varchar(255),
 City varchar(255),
 PhoneNumber varchar(12),
 Email varchar(255),
 FavColor varchar(255),
 FavNumber int);

//populating the tables

/*inserting in the Class table*/

INSERT INTO Class VALUES(1, "David", "San", "M", "BRN", "BLK", 72, "-");
INSERT INTO Class VALUES(2, "Jeff", "Gonzales", "M", "BRN", "BLK", 68, "B");
INSERT INTO Class VALUES(3, "Anna", "Grayson", "F", "BRN", "BRN", 62, "A");
INSERT INTO Class VALUES(4, "Kathryn", "Moloney", "F", "GRN", "BLK", 68, "B");
INSERT INTO Class VALUES(5, "Randy", "Bernard", "M", "GRN", "BRN", 69, "A");
INSERT INTO Class VALUES(6, "Andy", "Lam", "M", "BRN", "BLK", 59, "C");
INSERT INTO Class VALUES(7, "Makoto", "Yuki", "F", "BRN", "BRN", 61, "A");
INSERT INTO Class VALUES(8, "Pranil", "Watakana", "M", "BRN", "BLK", 63, "D");
INSERT INTO Class VALUES(9, "Pierce", "Santos", "M", "BRN", "BLK", 74, "B");
INSERT INTO Class VALUES(10, "Soliel", "Estrada", "F", "BRN", "BLU", 66, "B");
INSERT INTO Class VALUES(11, "Jeff", "Bezos", "M", "BRN", "BRN", 65, "B");
INSERT INTO Class VALUES(12, "Andy", "Chen", "M", "BRN", "BLK", 69, "A");
INSERT INTO Class VALUES(13, "Makoto", "Amagi", "F", "BRN", "BRN", 64, "C");

/*inserting in the Student table*/

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

//SQL queries

/*Assign grades for students who lived in Rossette Park to be a B*/

UPDATE Class, Student SET Class.CurrentGrade = 'B' WHERE Student.FirstName = Class.FirstName AND Student.LastName = Class.LastName AND Student.City = 'Rossette Park';

/*Add another table for students who had an altered grade (in this case students who lived in Rossette Park)*/

/*Add the CIN, FirstName, LastName, Gender, Address, City, and Email of the students who had an altered grade to the table*/

CREATE TABLE altered_grade_table AS
    SELECT Student.Age, Class.CIN, Student.FirstName, Student.LastName, Class.Gender, Student.Address, Student.City, Student.Email
    FROM Class, Student
    WHERE Student.FirstName = Class.FirstName and Student.LastName = Class.LastName and Student.City = 'Rossette Park';

/*Remove students from the student table who live in Rossette Park*/

DELETE FROM Student WHERE Student.City = 'Rossette Park';

/*Join all 3 tables and display the Names, Gender, and City of the students (Going to need to use ON)*/

SELECT altered_grade_table.FirstName, altered_grade_table.LastName, altered_grade_table.Gender, altered_grade_table.City FROM Class JOIN altered_grade_table ON Class.CIN = altered_grade_table.CIN JOIN Student ON altered_grade_table.SSN = Student.SSN;

/*This last code will give zero records as we have already deleted the common records from Student table so it won't be able to show the records*/