由于某些原因在SQL开发中缺少逗号错误.以前工作得很好

Question

我让它去做别的事情，它以前工作得很好。现在它甚至无法运行，因为缺少逗号错误，即使逗号已经在那里了

CREATE TABLE employees
(
EMPLOYEE_ID  NUMBER(6) PRIMARY KEY NOT NULL,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR( 25) NOT NULL,
EMAIL VARCHAR(25) NOT NULL,
PHONE_NUMBER VARCHAR(20),
HIRE_DATE DATE NOT NULL,
JOB_ID VARCHAR2(10) NOT NULL,
SALARY NUMBER(8,2),
COMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(6),
DEPARTMENT_ID NUMBER(4)
);
CREATE TABLE job_history
(
EMPLOYEE_ID NUMBER(6) PRIMARY KEY NOT NULL,
START_DATE DATE NOT NULL,
END_DATE DATE NOT NULL,
JOB_ID VARCHAR2(10) NOT NULL,
DEPARTMENT_ID NUMBER(4)
);
--Question 1
INSERT INTO employees (EMPLOYEE_ID, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, DEPARTMENT_ID)  
VALUES(176, 'Taylor', '[e-mail]', '1999-AUG-31, 'SA_REP', 80);
INSERT INTO employees (EMPLOYEE_ID, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, DEPARTMENT_ID)  
VALUES(200, 'Whalen', '[e-mail]', DATE '1993-06-18', 'AD_ASST', 200);
SELECT LAST_NAME, DEPARTMENT_ID, END_DATE, JOB_ID, EMPLOYEE_ID 
FROM employees;
NATURAL JOIN job_history;

Answer 1

不是少了逗号，而是第一个插入语句中的整个日期值都错了

This:'1999-AUG-31是nothing，甚至不是字符串(缺少尾单引号)。但是，它也不是一个有效的日期值。将它与包含日期文字的第二个语句进行比较:DATE '1999-08-31'(是的，这也是你在第一个语句中应该做的)。

固定时:

SQL> INSERT INTO employees (EMPLOYEE_ID, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, DEPARTMENT_ID)
2  VALUES(176, 'Taylor', '[e-mail]', DATE '1999-08-31', 'SA_REP', 80);
-----------------
1 row created.                                      this was wrong
SQL> INSERT INTO employees (EMPLOYEE_ID, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, DEPARTMENT_ID)
2  VALUES(200, 'Whalen', '[e-mail]', DATE '1993-06-18', 'AD_ASST', 200);
1 row created.
SQL>

---

作为你的自然连接问题:自然连接隐式地连接两个表中的公共列(它们具有相同的名称)。

这意味着你的查询:

SQL> SELECT LAST_NAME, DEPARTMENT_ID, END_DATE, JOB_ID, EMPLOYEE_ID  FROM employees NATURAL JOIN job_history;
LAST_NAME                 DEPARTMENT_ID END_DATE   JOB_ID     EMPLOYEE_ID
------------------------- ------------- ---------- ---------- -----------
Taylor                               80 06-12-0031 SA_REP             176

返回与内部连接相同的结果，连接公共列:

SQL> select e.last_name, e.department_id, h.end_Date, e.job_id, e.employee_id
2  from employees e join job_history h on h.employee_id   = e.employee_id
3                                     and h.job_id        = e.job_id
4                                     and h.department_id = e.department_id;
LAST_NAME                 DEPARTMENT_ID END_DATE   JOB_ID     EMPLOYEE_ID
------------------------- ------------- ---------- ---------- -----------
Taylor                               80 06-12-0031 SA_REP             176
SQL>

如果你希望查询返回"more"行，它们应该在公共列中有匹配的数据。例如，虽然EMPLOYEE_ID值匹配，但job_history表中没有AD_ASST的作业值，department_id表中也没有200的作业值。

---

需要返回两个员工的数据:删除员工200并重新输入修改后的职位和部门数据;那么你的自然连接工作。

SQL> delete from job_history where employee_id = 200;
1 row deleted.
SQL> INSERT INTO job_history (EMPLOYEE_ID, START_DATE, END_DATE, JOB_ID, DEPARTMENT_ID)
2  VALUES(200, '2001-JAN-14', '2001-JUN-17', 'AD_ASST', 200);
1 row created.
SQL> SELECT LAST_NAME, DEPARTMENT_ID, END_DATE, JOB_ID, EMPLOYEE_ID  FROM employees NATURAL JOIN job_history;
LAST_NAME                 DEPARTMENT_ID END_DATE    JOB_ID     EMPLOYEE_ID
------------------------- ------------- ----------- ---------- -----------
Taylor                               80 0031-DEC-06 SA_REP             176
Whalen                              200 2001-JUN-17 AD_ASST            200