为什么我不能在此查询中进行任何类型的加入？

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

2 个解决方案

#1

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

当我运行您的查询时,我收到此错误错误:列引用“dept_name”是不明确的

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:

我不确定这是你的问题还是只是一个错字,但要修复它你只需要指定你要从哪个表中检索dept_name,如下所示:

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

2 个解决方案

#1

更多相关文章

随机推荐