I am attempting to insert data from one table into another in SQL. my solution is as follows:
INSERT INTO CollegeStats (cName, appCount, minGPA, maxGPA)
SELECT cName, (SELECT COUNT(*) FROM apply WHERE cName = cName ), 1, 1
FROM College;
please dis-regard the bad formatting. in any case this part where i have the select count(*) is retrieving the entire list of all applications for each college. when i use that same select line elsewhere but i do something like: SELECT COUNT(*) FROM apply WHERE cName = 'U of O it retrieves precisely the amount of applications from that table for university of Oregon. My trouble is how do i utilizing this same code enforce the select to actually select the distinct values?
MRE:
CREATE TABLE IF NOT EXISTS College
(
State CHAR(2) NOT NULL,
cName VARCHAR(20) NOT NULL,
enrollment INT NOT NULL,
PRIMARY KEY (cName)
);
CREATE TABLE IF NOT EXISTS Major
(
major VARCHAR(30) NOT NULL,
PRIMARY KEY (major)
);
CREATE TABLE IF NOT EXISTS Student
(
sID INT NOT NULL,
sName VARCHAR(30) NOT NULL,
GPA FLOAT NOT NULL,
sizeHS INT NOT NULL,
PRIMARY KEY (sID)
);
CREATE TABLE IF NOT EXISTS MinimumGPA
(
minGPA FLOAT NOT NULL,
cName VARCHAR(20) NOT NULL,
major VARCHAR(30) NOT NULL,
PRIMARY KEY (cName, major),
FOREIGN KEY (cName) REFERENCES College(cName),
FOREIGN KEY (major) REFERENCES Major(major)
);
CREATE TABLE IF NOT EXISTS APPLY
(
decision SET('Y', 'N') NOT NULL,
sID INT NOT NULL,
cName VARCHAR(20) NOT NULL,
major VARCHAR(30) NOT NULL,
PRIMARY KEY (sID, cName, major),
FOREIGN KEY (sID) REFERENCES Student(sID),
FOREIGN KEY (cName, major) REFERENCES MinimumGPA(cName, major)
);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (123, 'Amy', 3.90, 1000);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (234, 'Bob', 3.60, 1500);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (345, 'Craig', 3.50, 500);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (456, 'Doris', 3.90, 1000);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (543, 'Craig', 3.40, 2000);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (567, 'Edward', 2.90, 2000);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (654, 'Amy', 3.90, 1000);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (678, 'Fay', 3.80, 200);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (765, 'Jay', 2.90, 1500);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (789, 'Gary', 3.40, 800);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (876, 'Irene', 3.90, 400);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (987, 'Helen', 4.00, 800);
INSERT INTO College (cName, State, enrollment) VALUES ('Cornell', 'NY', 21000);
INSERT INTO College (cName, State, enrollment) VALUES ('MIT', 'MA', 10000);
INSERT INTO College (cName, State, enrollment) VALUES ('WSU', 'WA', 28000);
INSERT INTO College (cName, State, enrollment) VALUES ('U of O', 'OR', 25000);
INSERT INTO Major (major) VALUES ('CS');
INSERT INTO Major (major) VALUES ('EE');
INSERT INTO Major (major) VALUES ('history');
INSERT INTO Major (major) VALUES ('biology');
INSERT INTO Major (major) VALUES ('bioengineering');
INSERT INTO Major (major) VALUES ('psychology');
INSERT INTO Major (major) VALUES ('marine biology');
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('WSU', 'CS', 3.75);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('WSU', 'EE', 3.5);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('WSU', 'history', 2.8);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('U of O', 'CS', 3.6);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('U of O', 'biology', 3.75);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('Cornell','bioengineering', 3.8);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('Cornell','CS', 3.4);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('Cornell','EE', 3.6);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('Cornell','history', 3.6);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('Cornell','psychology', 2.8);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('MIT', 'biology', 3.5);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('MIT', 'bioengineering', 3.5);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('MIT', 'CS', 3.9);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('MIT', 'marine biology', 3.5);
INSERT INTO Apply (sID, cName, major, decision) VALUES (123, 'Cornell', 'EE', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (123, 'WSU', 'CS', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (123, 'WSU', 'EE', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (123, 'U of O', 'CS', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (123, 'MIT', 'CS', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (234, 'U of O', 'biology', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (345, 'Cornell', 'bioengineering', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (345, 'Cornell', 'CS', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (345, 'Cornell', 'EE', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (345, 'MIT', 'bioengineering', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (543, 'MIT', 'CS', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (678, 'Cornell', 'history', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (678, 'Cornell', 'psychology', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (678, 'WSU', 'history', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (765, 'WSU', 'history', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (876, 'MIT', 'biology', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (876, 'MIT', 'marine biology', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (876, 'WSU', 'CS', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (987, 'WSU', 'CS', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (987, 'U of O', 'CS', 'Y');
CREATE TABLE IF NOT EXISTS CollegeStats
(
cName VARCHAR(20) NOT NULL,
appCount INT NOT NULL,
minGPA dec(3, 2) NOT NULL,
maxGPA dec(3, 2) NOT NULL,
PRIMARY KEY (cName)
);
INSERT INTO CollegeStats (cName, appCount, minGPA, maxGPA)
SELECT cName, (SELECT COUNT(*) FROM apply WHERE cName ), 1, 1
FROM College;
select * from collegestats;
Once again my desired output is for each of the rows in CollegeStats to have the correct apply minGPA and maxGPA in their corresponding rows. once again up above is my attempt but like i said it clearly isn't working as it produces "30" for each row which is the total number of applications. I need the correct number of applications IE 6 for WSU etc
i am running whatever the latest version of sql is available from the mysql website and all of this i am trying to accomplish in mysql workbench
CodePudding user response：
You should use belowed query. I tried with your sample data and it just works fine.
CREATE TABLE IF NOT EXISTS College
(
State CHAR(2) NOT NULL,
cName VARCHAR(20) NOT NULL,
enrollment INT NOT NULL,
PRIMARY KEY (cName)
);
CREATE TABLE IF NOT EXISTS Major
(
major VARCHAR(30) NOT NULL,
PRIMARY KEY (major)
);
CREATE TABLE IF NOT EXISTS Student
(
sID INT NOT NULL,
sName VARCHAR(30) NOT NULL,
GPA FLOAT NOT NULL,
sizeHS INT NOT NULL,
PRIMARY KEY (sID)
);
CREATE TABLE IF NOT EXISTS MinimumGPA
(
minGPA FLOAT NOT NULL,
cName VARCHAR(20) NOT NULL,
major VARCHAR(30) NOT NULL,
PRIMARY KEY (cName, major),
FOREIGN KEY (cName) REFERENCES College(cName),
FOREIGN KEY (major) REFERENCES Major(major)
);
CREATE TABLE IF NOT EXISTS APPLY
(
decision SET('Y', 'N') NOT NULL,
sID INT NOT NULL,
cName VARCHAR(20) NOT NULL,
major VARCHAR(30) NOT NULL,
PRIMARY KEY (sID, cName, major),
FOREIGN KEY (sID) REFERENCES Student(sID),
FOREIGN KEY (cName, major) REFERENCES MinimumGPA(cName, major)
);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (123, 'Amy', 3.90, 1000);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (234, 'Bob', 3.60, 1500);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (345, 'Craig', 3.50, 500);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (456, 'Doris', 3.90, 1000);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (543, 'Craig', 3.40, 2000);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (567, 'Edward', 2.90, 2000);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (654, 'Amy', 3.90, 1000);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (678, 'Fay', 3.80, 200);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (765, 'Jay', 2.90, 1500);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (789, 'Gary', 3.40, 800);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (876, 'Irene', 3.90, 400);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (987, 'Helen', 4.00, 800);
INSERT INTO College (cName, State, enrollment) VALUES ('Cornell', 'NY', 21000);
INSERT INTO College (cName, State, enrollment) VALUES ('MIT', 'MA', 10000);
INSERT INTO College (cName, State, enrollment) VALUES ('WSU', 'WA', 28000);
INSERT INTO College (cName, State, enrollment) VALUES ('U of O', 'OR', 25000);
INSERT INTO Major (major) VALUES ('CS');
INSERT INTO Major (major) VALUES ('EE');
INSERT INTO Major (major) VALUES ('history');
INSERT INTO Major (major) VALUES ('biology');
INSERT INTO Major (major) VALUES ('bioengineering');
INSERT INTO Major (major) VALUES ('psychology');
INSERT INTO Major (major) VALUES ('marine biology');
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('WSU', 'CS', 3.75);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('WSU', 'EE', 3.5);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('WSU', 'history', 2.8);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('U of O', 'CS', 3.6);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('U of O', 'biology', 3.75);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('Cornell','bioengineering', 3.8);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('Cornell','CS', 3.4);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('Cornell','EE', 3.6);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('Cornell','history', 3.6);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('Cornell','psychology', 2.8);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('MIT', 'biology', 3.5);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('MIT', 'bioengineering', 3.5);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('MIT', 'CS', 3.9);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('MIT', 'marine biology', 3.5);
INSERT INTO Apply (sID, cName, major, decision) VALUES (123, 'Cornell', 'EE', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (123, 'WSU', 'CS', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (123, 'WSU', 'EE', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (123, 'U of O', 'CS', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (123, 'MIT', 'CS', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (234, 'U of O', 'biology', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (345, 'Cornell', 'bioengineering', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (345, 'Cornell', 'CS', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (345, 'Cornell', 'EE', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (345, 'MIT', 'bioengineering', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (543, 'MIT', 'CS', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (678, 'Cornell', 'history', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (678, 'Cornell', 'psychology', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (678, 'WSU', 'history', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (765, 'WSU', 'history', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (876, 'MIT', 'biology', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (876, 'MIT', 'marine biology', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (876, 'WSU', 'CS', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (987, 'WSU', 'CS', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (987, 'U of O', 'CS', 'Y');
CREATE TABLE IF NOT EXISTS CollegeStats
(
cName VARCHAR(20) NOT NULL,
appCount INT NOT NULL,
minGPA dec(3, 2) NOT NULL,
maxGPA dec(3, 2) NOT NULL,
PRIMARY KEY (cName)
);
INSERT INTO CollegeStats (cName, appCount, minGPA, maxGPA)
SELECT cName as cNameX, (SELECT COUNT(*) FROM apply WHERE cName = cNameX ), 1, 1
FROM College;
select * from CollegeStats;
Here's output:
CodePudding user response：
You need to specify the colage with a prefix, so that mysql knows whch colage name it should use
INSERT INTO CollegeStats (cName, appCount, minGPA, maxGPA)
SELECT c.cName, (SELECT COUNT(*) FROM apply WHERE cName = c.cName ), 1, 1
FROM College c;
CodePudding user response：
figured out the whole solution:
INSERT INTO CollegeStats (cName, appCount, minGPA, maxGPA)
SELECT cName as cNameX,
(SELECT COUNT(*) FROM apply WHERE cName = cNameX),
(SELECT MIN(GPA) FROM Student LEFT JOIN Apply ON Student.sID = Apply.sID WHERE cName = cNameX),
(SELECT MAX(GPA) FROM Student LEFT JOIN Apply ON Student.sID = Apply.sID WHERE cName = cNameX)
FROM College;