CODEWITHSUNDEEP
sqlsql-serverdatabaserdbms
Ahmed Hillal
Ahmed Hillal

Reputation: 59

Why I can't make any kind of join in this query?

When I'm trying to make any kind of join it gives me errors

How can I solve this problem

Here's my code because this problem make a hassle for me

it's annoying me because I don't know what my wrong

So, please may you guide me what I can do to make any kind of join in this query ?

Create Table Department (
dept_name varchar(100) Primary Key,
building varchar(100),
budget INT,
);


Create Table student (
ID INT Primary Key,
name varchar(100),
dept_name varchar(100),
tot_cred INT,

Foreign Key (dept_name) references Department
);

Create Table course (
course_id varchar(100) Primary Key,
title varchar(100),
dept_name varchar(100),
credits INT,
Foreign Key (dept_name) references Department
);

Create Table takes (
ID INT,
course_id varchar(100),
sec_id INT,
semester varchar(100),
year INT,
grade varchar(100),

Primary Key (ID, course_id, sec_id, semester, year)

);
 --inserting student values
Insert Into student (ID, name, dept_name, tot_cred) values (00128, 'Zhang', 'Comp.Sci', 102);
Insert Into student (ID, name, dept_name, tot_cred) values (12345, 'Shankar', 'Comp.Sci', 32);
Insert Into student (ID, name, dept_name, tot_cred) values (19991, 'Brandt', 'History', 80);
Insert Into student (ID, name, dept_name, tot_cred) values (23121, 'Chavez', 'Finance', 110);
Insert Into student (ID, name, dept_name, tot_cred) values (44553, 'Peltier', 'Physics', 56);
Insert Into student (ID, name, dept_name, tot_cred) values (45678, 'Levy', 'Physics', 46);
Insert Into student (ID, name, dept_name, tot_cred) values (54321, 'Williams', 'Comp.Sci', 54);
Insert Into student (ID, name, dept_name, tot_cred) values (55739, 'Sanchez', 'Music', 38);
Insert Into student (ID, name, dept_name, tot_cred) values (70557, 'Snow', 'Physics', 0);
Insert Into student (ID, name, dept_name, tot_cred) values (76543, 'Brown', 'Comp.Sci', 58);
Insert Into student (ID, name, dept_name, tot_cred) values (76653, 'Aoi', 'Elec.Eng', 60);
Insert Into student (ID, name, dept_name, tot_cred) values (98765, 'Bourikas', 'Elec.Eng', 98);
Insert Into student (ID, name, dept_name, tot_cred) values (98988, 'Tanaka', 'Biology', 120);

--inserting takes values
Insert Into takes (ID, course_id, sec_id, semester, year, grade) values (00128, 'CS-101', 1, 'Fall', 2009, 'A');
Insert Into takes (ID, course_id, sec_id, semester, year, grade) values (00128, 'CS-347', 1, 'Fall', 2009, 'A-');
Insert Into takes (ID, course_id, sec_id, semester, year, grade) values (12345, 'CS-101', 1, 'Fall', 2009, 'C');
Insert Into takes (ID, course_id, sec_id, semester, year, grade) values (12345, 'CS-190', 2, 'Spring',2009, 'A');
Insert Into takes (ID, course_id, sec_id, semester, year, grade) values (12345, 'CS-315', 1, 'Spring', 2010, 'A');
Insert Into takes (ID, course_id, sec_id, semester, year, grade) values (12345, 'CS-347', 1, 'Fall', 2009, 'A');
Insert Into takes (ID, course_id, sec_id, semester, year, grade) values (19991, 'HIS-351', 1, 'Spring', 2010, 'B');
Insert Into takes (ID, course_id, sec_id, semester, year, grade) values (23121, 'FIN-201', 1, 'Spring', 2010, 'C+');
Insert Into takes (ID, course_id, sec_id, semester, year, grade) values (44553, 'PHY-101', 1, 'Fall', 2009, 'B-');
Insert Into takes (ID, course_id, sec_id, semester, year, grade) values (45678, 'CS-101', 1, 'Fall', 2009, 'F');
Insert Into takes (ID, course_id, sec_id, semester, year, grade) values (45678, 'CS-101', 1, 'Spring', 2010, 'B+');
Insert Into takes (ID, course_id, sec_id, semester, year, grade) values (45678, 'CS-319', 1, 'Spring', 2010, 'B');
Insert Into takes (ID, course_id, sec_id, semester, year, grade) values (54321, 'CS-101', 1, 'Fall', 2009, 'A-');
Insert Into takes (ID, course_id, sec_id, semester, year, grade) values (54321, 'CS-190', 2, 'Spring', 2009, 'B+');
Insert Into takes (ID, course_id, sec_id, semester, year, grade) values (55739, 'MU-199', 1, 'Spring', 2010, 'A-');
Insert Into takes (ID, course_id, sec_id, semester, year, grade) values (76543, 'CS-101', 1, 'Fall', 2009, 'A');
Insert Into takes (ID, course_id, sec_id, semester, year, grade) values (76543, 'CS-319', 2, 'Spring', 2010, 'A');
Insert Into takes (ID, course_id, sec_id, semester, year, grade) values (98765, 'CS-101', 1, 'Fall', 2009, 'C-');
Insert Into takes (ID, course_id, sec_id, semester, year, grade) values (98765, 'CS-315', 1, 'Spring', 2010, 'B');
Insert Into takes (ID, course_id, sec_id, semester, year, grade) values (98988, 'BIO-101', 1, 'Summer', 2009, 'A');
Insert Into takes (ID, course_id, sec_id, semester, year, grade) values (98988, 'BIO-101', 1, 'Summer', 2010, 'null');

--inserting course values
Insert Into Course(course_id, title, dept_name, credits) values ('BIO-101', 'Intro to Biology', 'Biology', 4);
Insert Into Course(course_id, title, dept_name, credits) values ('BIO-301', 'Genetics', 'Biology', 4);
Insert Into Course(course_id, title, dept_name, credits) values ('BIO-399', 'Computational Biology', 'Biology', 3);
Insert Into Course(course_id, title, dept_name, credits) values ('CS-101', 'Intro to Computer Science', 'Comp.Sci', 4);
Insert Into Course(course_id, title, dept_name, credits) values ('CS-190', 'Game Design', 'Comp.Sci', 4);
Insert Into Course(course_id, title, dept_name, credits) values ('CS-315', 'Robotics', 'Comp.Sci', 3);
Insert Into Course(course_id, title, dept_name, credits) values ('CS-319', 'Image Processing', 'Comp.Sci', 3);
Insert Into Course(course_id, title, dept_name, credits) values ('CS-347', 'Database System Concepts', 'Comp.Sci', 3);
Insert Into Course(course_id, title, dept_name, credits) values ('EE-181', 'Intro to Digital System', 'Elec.Eng', 3);
Insert Into Course(course_id, title, dept_name, credits) values ('FIN-201', 'Investment Banking', 'Finance', 3);
Insert Into Course(course_id, title, dept_name, credits) values ('HIS-351', 'World History', 'History', 4);
Insert Into Course(course_id, title, dept_name, credits) values ('MU-199', 'Music Video Production', 'Music', 3);
Insert Into Course(course_id, title, dept_name, credits) values ('PHY-101', 'Physics Principles', 'Physics', 4);

--inserting department values

Insert Into Department(dept_name, building, budget) values ('Biology', 'Watson', 90000);
Insert Into Department(dept_name, building, budget) values ('Comp.Sci', 'Taylor', 100000);
Insert Into Department(dept_name, building, budget) values ('Elec.Eng', 'Taylor', 85000);
Insert Into Department(dept_name, building, budget) values ('Finance', 'Painter', 120000);
Insert Into Department(dept_name, building, budget) values ('History', 'Painter', 50000);
Insert Into Department(dept_name, building, budget) values ('Music', 'Packard', 80000);
Insert Into Department(dept_name, building, budget) values ('Physics', 'Watson', 70000);


Select name, dept_name

From student join Department

on student.dept_name=Department.dept_name

Upvotes: 3

Views: 225

Answers (2)

User2012384
User2012384

Reputation: 4919

Take a look at the dept_name, you have dept_name in Department and student

so, try changing your query, specify which dept_name you want:

Select a.name, b.dept_name

From student a join Department b 

on student.dept_name=Department.dept_name

By the way, I would suggest you to post your error message here so we can take a deeper look at the error.

Upvotes: 0

Ben Glasser
Ben Glasser

Reputation: 3355

when I ran your query I received this error ERROR: column reference "dept_name" is ambiguous

I'm not sure if this is your problem or just a typo, but to fix it you just need to specify which table you want to retrieve dept_name from like this: 

select name, Department.dept_name                                                                                                                                                                          
from student join Department                                                                                                                                                                                    
on student.dept_name=Department.dept_name;

which returns:

   name   | dept_name 
----------+-----------
 Zhang    | Comp.Sci
 Shankar  | Comp.Sci
 Brandt   | History
 Chavez   | Finance
 Peltier  | Physics
 Levy     | Physics
 Williams | Comp.Sci
 Sanchez  | Music
 Snow     | Physics
 Brown    | Comp.Sci
 Aoi      | Elec.Eng
 Bourikas | Elec.Eng
 Tanaka   | Biology

Upvotes: 4

Related Questions