inserting unique data from one table into another in sql

Question

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

Can you please insert your sample data and desired output? While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). All within the question, no images. — Eren Temelli
– Eren Temelli, Commented Feb 23, 2022 at 7:48
Got it, please can you post manually what is your desired output look like? — Eren Temelli
– Eren Temelli, Commented Feb 23, 2022 at 8:18

Eren Temelli · Accepted Answer · 2022-02-23 08:24:40Z

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:

Fiddle

All of these are great answers! unfortunately I can only "select" one as the "the" answer otherwise both were perfect.
Do you know how to get the other parts for the minGPA and maxGPA to work? So as not to waste another question

nbk · Accepted Answer · 2022-02-23 08:21:19Z

1

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;

answered Feb 23, 2022 at 8:21

nbk

49.6k8 gold badges32 silver badges53 bronze badges

2 Comments

this is me Over a year ago

Do you know how to get the other parts for the minGPA and maxGPA to work? So as not to waste another question

nbk Over a year ago

make the same as for Apply, onl you link Aplly tith all student and select the MIN or MAx from it

this is me · Accepted Answer · 2022-02-23 09:49:15Z

0

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;

answered Feb 23, 2022 at 9:49

this is me

475 bronze badges

Collectives™ on Stack Overflow

inserting unique data from one table into another in sql

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related