改进书面sql查询 [英] improving the written sql query

问题描述

我想为下面定义的问题编写一个SQL查询，我的答案如下，但是我不确定答案，有人可以帮助我吗？答案是正确的，如果不是，我该如何改善？

I want to write a SQL query for the problem as defined below, my answer is as below, but I am not sure about the answer, can anyone help me? Is the answer correct, or if not, how can I improve it?

让我们考虑以下有关医生和科室的关系模式：

Let us consider the following relational schema about physicians and departments:

• 医生（医生编号，姓名，姓氏，专业，性别，出生日期，部门）；

• PHYSICIAN (PhysicianId, Name, Surname, Specialization, Gender, BirthDate, Department);

通过代码唯一地标识每个医师，并以名称，姓氏，专业（我们假设要为每个医师准确记录一个专业），性别，出生日期和相关部门（每个医师被分配给

Let every physician be univocally identified by a code and characterized by a name, a surname, a specialization (we assume to record exactly one specialization for each physician), a gender, a birth date, and the relative department (each physician is assigned to one and only one department).

部门（名称，建筑物，楼层，主管）

DEPARTMENT (Name, Building, Floor, Chief)

让每个部门都可以通过名称唯一标识，并以其位置（建筑物和楼层）和主管为特征。

Let every department be univocally identified by a name and characterized by its location (building and floor) and a chief.

让我们假设医师最多可以是一个部门（他/她所属的部门）的负责人。我们不排除两个不同部门位于同一建筑物同一层的可能性。

Let us assume that a physician can be the chief of at most one department (the department he/she belongs to). We do not exclude the possibility for two distinct departments to be located at the same floor of the same building.

预先定义主键，其他候选键（如果有）和外键（如果有的话）。然后，编写一个SQL查询来计算以下数据（仅在绝对必要时才使用聚合函数）：

Define preliminarily primary keys, other candidate keys (if any), and foreign keys (if any). Then, formulate an SQL query to compute the following data (exploiting aggregate functions only if they are strictly necessary):

• 由男性和女性医师组成的部门，他的医生都出生于1955年之后，也就是说，他们都到了64岁。

我的回答如下：

create view table X as {
select d.Name
from department d inner join PHYSICIAN p on 
d.department=f.name
where gender="Female" and gender="Male and birthdate>1955

select *
from X

推荐答案

您的查询不会返回任何记录，因为'gender'字段不能同时等于Female和Male。
我的建议是：

Your query will not return any records because 'gender' field cannot be equal to Female and Male at the same time. My suggestion is something like:

select distinct d.*
  from departments d
  inner join physicians m on d.name = m.department and m.gender = 'Male'
  inner join physicians f on d.name = f.department and f.gender = 'Female'
  left join physicians o on d.name = o.department and p.birthdate < '1955-01-01'
  where o.id is null

这个想法是找到至少一个男性和女性各部门。之后，为每个部门找到一个老人，并过滤出所有拥有该老人的部门。

The idea is to find at least one male and one female for each department. After that find one old person for each department and filter out all departments that have such an old person.

这篇关于改进书面sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！