我有一个关于DB的作业。我的查询有些问题。我是SQL新手。这是我的桌子:
CREATE TABLE Employee (
employee_name varchar(20) NOT NULL PRIMARY KEY,
street varchar(30) NOT NULL,
city varchar(20) NOT NULL
)
CREATE TABLE Works
(
employee_name varchar(20) NOT NULL,
department_name varchar(20) NOT NULL,
title varchar(10) NOT NULL,
salary int NOT NULL,
PRIMARY KEY (employee_name),
FOREIGN KEY (employee_name) REFERENCES Employee,
FOREIGN KEY (department_name) REFERENCES Department
)
CREATE TABLE Department
(
department_name varchar(20),
cityvarchar(20),
PRIMARY KEY (department_name)
)
CREATE table Manages(
employee_name varchar(20) not null,
manager_name varchar (20) not null,
PRIMARY KEY(employee_name),
FOREIGN KEY(employee_name) REFERENCES Employee
)
查找所在部门所在城市的员工姓名。这是我的代码:
SELECT E.employee_name
FROM Employee E
JOIN Department D
ON E.city = D.city
WHERE E.city <> 'Roma';
查找与他们的经理居住在同一城市和同一条街上的雇员姓名。这是我的代码:
SELECT E.employee_name
FROM Employee E
INNER JOIN Department D
ON E.city = D.city
INNER JOIN Manages M
ON E.employee_name = M.employee_name
WHERE city='Roma' AND street='Main street';
我究竟做错了什么?先感谢您!:)
以下是您完成第一个任务的方式(居住在所在部门以外城市的员工):
SELECT E.employee_name
FROM Employee E JOIN Works W
ON E.employee_name = W.employee_name
JOIN Department D
ON W.departent_name = D.department_name
WHERE D.city <> E.city;
为了确定给定员工所在的部门,您需要加入Works
表格。
我必须承认,将员工数据垂直划分为Employee
和的Works
表有些奇怪-没有真正的理由将这两个表分开(这是1-1的关系)。为此,也Manages
不必是单独的表。
希望这可以使您在第二条查询的正确路径上。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句