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 1 year 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,...

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

--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...

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...

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...

Use MYSQL to create the set of database tables of the relational database model and complete...

Use MYSQL to create the set of database tables of the relational database model and complete the associated queries given. Procedure: 1) Write all the SQL statements, necessary to create all tables and relationships, with Primary & Foreign keys. 2) Execute each statement in the correct order to create the relational database in MYSQL. 3)Insert some data into each table. 4) Use all your SQL create and Insert statements (from MS Word) to execute in the MYSQL WorkBench 5) Write...

java Objective: Create a class. Create objects. Use methods of a class. Create a class BankAccount...

java Objective: Create a class. Create objects. Use methods of a class. Create a class BankAccount to represent a bank account according to the following requirements: A bank account has three attributes: accountnumber, balance and customer name. Add a constructor without parameters. In the initialization of the attributes, set the number and the balance to zero and the customer name to an empty string. Add a constructor with three parameters to initialize all the attributes by specific values. Add a...

Write create table statements to create tables for the Exoproduct employees computers database depicted by the...

Write create table statements to create tables for the Exoproduct employees computers database depicted by the relational schema created in a mini case MC5 in chapter 3. Use insert into statements to insert no fewer than 2 and no more than 10 records per table.

Question