1. 创建Employee 表,包含所有员工信息,每个员工有其对应的 Id, salary 和 department Id。
CREATE TABLE employee(
employee_id CHAR(4) NOT NULL,
emplyee_name VARCHAR(100) NOT NULL,
salary INTEGER NOT NULL,
department_id CHAR(4) NOT NULL,
PRIMARY KEY (employee_id));
INSERT INTO employee VALUES ('0001', 'Joe', 70000, '0001');
INSERT INTO employee VALUES ('0002', 'Henry', 80000, '0002');
INSERT INTO employee VALUES ('0003', 'Sam', 60000, '0002');
INSERT INTO employee VALUES ('0004', 'Max', 90000, '0001');
SELECT * FROM employee;
创建Department 表,包含公司所有部门的信息。
CREATE TABLE department( department_id CHAR(4) NOT NULL, department_name VARCHAR(100) NOT NULL, PRIMARY KEY (department_id) );
INSERT INTO department VALUES ('0001', 'IT');
INSERT INTO department VALUES ('0002', 'Sales');
SELECT * FROM department;
2. 不会做
3. 查询分数排名。如果两个分数相同,则两个分数排名(Rank)相同。请注意,平分后的下一个名次应该是下一个连续的整数值。换句话说,名次之间不应该有“间隔”。
SELECT Score,
DENSE_RANK() OVER (ORDER BY score DESC) as Rank
FROM Score;
4.