遇到数据库查询问题

I'm studying for a database exam on the 6th of may and I'm having trouble wrapping my head around query from a previous years paper. I am trying to do part 'd' (4 in list).

我正在攻读5月6日的数据库考试,而我在前几年的论文中无法解决问题。我正在尝试做'd'部分(列表中的4个)。

So far I have this but dont know how to finish it

到目前为止我有这个,但不知道如何完成它

select count(*) as peopleOlderThanEmployeeNo100 
from EMPLOYEE, DEPT
where (dept.name = 'finance') and  (employee.dateOfBirth < ?);

Question 4

An SQL database has the following tables:

SQL数据库具有以下表:

[EMPLOYEE]    
  [EmpNo]       Integer  (Primary Key) 
  [Name]        Char(30) 
  [Salary]      Decimal(7,2)
  [StartDate]   Date 
  [DateOfBirth] Date 
  [Dept]        Char(12)  (Foreign key references DEPT( Name ) )

[DEPT]
  [Name]       Char(12)  (Primary Key) 
  [CostCentre] Char(4)

Write SQL statements to do the following:

编写SQL语句来执行以下操作:

1. List the names all employees in the “Finance” department.

列出“财务”部门中所有员工的姓名。

2. List the name and cost centre of all employees in “Finance” or “R&D”.

列出“财务”或“研发”中所有员工的姓名和成本中心。

3. List the number of employees in the “Production” department.

列出“生产”部门的员工人数。

4. List the number of “Finance” employees who are older than employee number 100.

列出年龄超过员工编号100的“财务”员工人数。

5. List the name, department and salary of all employees in ascending order of department and salary.

按部门和工资的升序列出所有员工的姓名,部门和工资。

6. Create an index on the EMPLOYEE table StartDate column.

在EMPLOYEE表StartDate列上创建索引。

7. List the oldest employee in the company.

列出公司中最老的员工。

8. Change the salary of employee 100 to 50,000.

将员工100的薪水更改为50,000。

1 个解决方案

#1

Use a subquery like this

使用这样的子查询

select count(*) as peopleOlderThanEmployeeNo100 
from EMPLOYEE, DEPT
where (dept.name = 'Finance') 
and  (employee.dateOfBirth < (select dateOfBirth 
                              from EMPLOYEE 
                              where EmpNo = 100));