DROP DATABASE class;CREATE DATABASE class;Use class;drop table if exists Class;drop table if exists Student;CREATE TABLE Class...

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);

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

Assign grades for students who lived in Rossette Park to be a B
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
Remove students from the student table who live in Rossette Park
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.

Expert Solution

//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*/

venereology answered 2 years ago

Information on the following Database: create database Sales_Co use Sales_Co create table Vendor (v_code integer, v_name...

Information on the following Database: create database Sales_Co use Sales_Co create table Vendor (v_code integer, v_name varchar(35) not null, v_contact varchar(15) not null, v_areacode char(3) not null, v_phone char(8) not null, v_state char(2) not null, v_order char(1) not null, primary key (v_code)); create table product (p_code varchar(10) constraint product_p_code_pk primary key, p_descript varchar(35) not null, p_indate datetime not null, p_qoh integer not null, p_min integer not null, p_price numeric (8,2) not null, p_discount numeric (4,2) not null, v_code integer, constraint...

Using SQL create a new database called school_app. Create a student table with fields id (auto...

Using SQL create a new database called school_app. Create a student table with fields id (auto increment), first_name, last_name. Create a course table with fields id (auto increment), course code (such as ITC or MTH), and course number (such as 100 or 295). Note that the relationship between student and course is many-to-many (n:m). Create a join table called student_course that implements the n:m relationship with fields id (auto increment), student_id, course_id, and grade (which has values 0, 1, 2,...

--Creating Package Table DROP TABLE Package_Dimensions; DROP TABLE Package_Statuses; DROP TABLE PACKAGES; DROP TABLE Status_Codes; DROP...

--Creating Package Table DROP TABLE Package_Dimensions; DROP TABLE Package_Statuses; DROP TABLE PACKAGES; DROP TABLE Status_Codes; DROP TABLE Exception_Codes; DROP TABLE Allowable_Limits; DROP TABLE Limit_Codes; CREATE TABLE Packages (Package_Id int PRIMARY KEY,Package_Number int); --Creating Package Dimensions Table: CREATE TABLE Package_Dimensions (Dimension_Id int Primary Key, Package_Id dec (5,2),Height dec (5,2),Width dec (5,2),Weight dec (5,2),Length dec (5,2)); --Creating Package Statuses Table CREATE TABLE Package_Statuses (Package_Status_Id int Primary Key, Status_Id int, Package_Id int, Status_Code int, Status_Date date, Exception_Code int); --Creating Status Codes and Exception...

USE SQL CREATE TABLE IF NOT EXISTS football_games ( visitor_name VARCHAR(30), /* Name of the visiting...

USE SQL CREATE TABLE IF NOT EXISTS football_games ( visitor_name VARCHAR(30), /* Name of the visiting team */ home_score SMALLINT NOT NULL, /* Final score of the game for the Buffs */ visitor_score SMALLINT NOT NULL,/* Final score of the game for the visiting team */ game_date DATE NOT NULL, /* Date of the game */ players INT[] NOT NULL, /* This array consists of the football player ids (basically a foreign key to the football_player.id) */ PRIMARY KEY(visitor_name, game_date)...

In this create a code that will drop a student by ID number //////////////////////////////////////////////////////////////////////// import java.util.Scanner;...

In this create a code that will drop a student by ID number //////////////////////////////////////////////////////////////////////// import java.util.Scanner; public class COurseCom666 { private String courseName; private String [] students = new String[1]; private int numberOfStudents; public COurseCom666(String courseName) { this.courseName = courseName; } public String[] getStudents() { return students; } public int getNumberOfStudents() { return numberOfStudents; } public String getCourseName() { return courseName; } public void...

Part 2: Use MySQL Workbench to add a table to your database on the class server...

Part 2: Use MySQL Workbench to add a table to your database on the class server and name the table “Person”. Include these fields in the Person table: Field Name Description Data Type Sample Value LoginID User’s login name varchar(10) Bob FirstName User’s first name varchar(50) Bob LastName User’s last name varchar(50) Barker picUrl Filename of the user’s picture varchar(50) bob.gif Bio User’s biography varchar(255) Bob is the best! LoginID should be the Primary Key of the table. Add at...

Language C++ Student-Report Card Generator: create a class called student and class called course. student class...

Language C++ Student-Report Card Generator: create a class called student and class called course. student class this class should contain the following private data types: - name (string) - id number (string) - email (s) - phone number (string) - number of courses (int) - a dynamic array of course objects. the user will specify how many courses are there in the array. the following are public members of the student class: - default constructor (in this constructor, prompt the...

Database questions: USE THE FOLLOWING SQL CODE TO SOLVE NEXT QUESTIONS: CREATE TABLE ROBOT ( Serial_no...

Database questions: USE THE FOLLOWING SQL CODE TO SOLVE NEXT QUESTIONS: CREATE TABLE ROBOT ( Serial_no INT NOT NULL, Model VARCHAR(20) NOT NULL, Manufacturer VARCHAR(20) NOT NULL, Price INT NOT NULL, PRIMARY KEY (Serial_no) ); INSERT INTO ROBOT VALUES (1, 'Scara','Epson', 23200); INSERT INTO ROBOT VALUES (2, 'ASSISTA','Mitsubishi', 17500); INSERT INTO ROBOT VALUES (3, 'Lego Mindstorm','NXT', 650); INSERT INTO ROBOT VALUES (4, 'Yumi','ABB', 40000); INSERT INTO ROBOT VALUES (5, 'Pepper','Foxconn', 1600); INSERT INTO ROBOT VALUES (6, 'Humanoid','Honda', 30000); SELECT *...

Create a class called Student which stores • the name of the student • the grade...

Create a class called Student which stores • the name of the student • the grade of the student • Write a main method that asks the user for the name of the input file and the name of the output file. Main should open the input file for reading . It should read in the first and last name of each student into the Student’s name field. It should read the grade into the grade field. • Calculate the...

In this assignment you are going to create a Student class to demonstrate a Student object....

In this assignment you are going to create a Student class to demonstrate a Student object. Then create a StudentTest class to place the main method in, which demonstrates the Student object. Student class should have the following instance variables: firstName, middleName, lastName (String) id (int) grade (int) Student class should have the following methods: Set and Get methods for each variable Constructor that sets all variables using Set methods that you defined. Design the application in main that you...

Question