Ebony Maw's questions

我不允许删除表格或删除列。任务是可能通过使用 ALTER 语句向列添加约束，以便它不允许任何负数。这可能吗？

我尝试了以下方法：

SQL> alter table SPEND_PLAN_DETAIL_VALUE modify (SP_DETAIL_VALUE_ID CHECK (SP_DETAIL_VALUE_ID > 0));

但这不起作用。

我有以下 SQL 视图创建脚本。我只想把它变成一个查询。我试图用 SELECT 语句替换初始行，但这没有用。有人可以告诉我我做错了什么吗？

CREATE OR REPLACE FORCE VIEW "RMS"."VW_CR_ADDITIONAL_INFO" ("COMMIT_REG_ID", "FUND_CERTIFIED", "FUND_CERTIFIED_DATE", "CANCELED", "APPROVED", "AMOUNT", "LAST_STATE_ID") AS 
  WITH FUND_CERTIFIED AS (
  SELECT REQUEST_ID, MIN(TIME_STAMP) AS FUND_CERTIFIED_DATE
  FROM REQFLOW_STATE
  WHERE STATUS_ID = 6
  GROUP BY REQUEST_ID
),
APPROVED AS (
  SELECT DISTINCT REQUEST_ID
  FROM REQFLOW_STATE
  WHERE STATUS_ID NOT IN ( 5, 8 )
),
CANCELED AS (
  SELECT DISTINCT REQUEST_ID
  FROM REQFLOW_STATE
  WHERE STATUS_ID = 4
),
LAST_STATE AS (
  SELECT MAX(ID) AS LAST_STATE_ID, REQUEST_ID
  FROM REQFLOW_STATE
  WHERE STATUS_ID <> 8
  GROUP BY REQUEST_ID
)
SELECT CR.COMMIT_REG_ID,
NVL2( FC.FUND_CERTIFIED_DATE, 'Y', 'N' ) AS FUND_CERTIFIED,
FC.FUND_CERTIFIED_DATE,
NVL2( C.REQUEST_ID, 'Y', 'N' ) AS CANCELED,
NVL2( A.REQUEST_ID, 'Y', 'N' ) AS APPROVED,
SUM( NVL( CRD.AMOUNT, 0 ) ) AS AMOUNT,
LS.LAST_STATE_ID
FROM COMMITMENT_REGISTER CR
LEFT OUTER JOIN FUND_CERTIFIED FC ON ( FC.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN APPROVED A ON ( A.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN CANCELED C ON ( C.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN LAST_STATE LS ON ( LS.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN COMMITMENT_REGISTER_DETAIL CRD ON ( CRD.COMMIT_REG_ID = CR.COMMIT_REG_ID )
GROUP BY CR.COMMIT_REG_ID,
NVL2( FC.FUND_CERTIFIED_DATE, 'Y', 'N' ),
FC.FUND_CERTIFIED_DATE,
NVL2( C.REQUEST_ID, 'Y', 'N' ),
NVL2( A.REQUEST_ID, 'Y', 'N' ),
LS.LAST_STATE_ID;

让我们考虑第一个查询：

SELECT r.cnum as cnum, c.name as name, r.assets as assets
FROM research..complex c, research..retirement_simple_ira r
where r.date = '9/30/2016'
and c.cnum = r.cnum
ORDER BY cnum

这给了我们一个结果集，如下所示：

这是我的第二个查询：

SELECT cf.cnum, SUM(rs.dh_simple + rs.ha_simple) as sums
FROM retirement_survey as rs
INNER JOIN currentfunds as cf
ON rs.fundid = cf.fundid
WHERE rs.date  = '9/30/2016'
GROUP BY cf.cnum
HAVING SUM(rs.dh_simple + rs.ha_simple) > 0

我尝试合并这两个查询，以便得到一个列出 4 列的结果集：

数字 | 姓名 | 资产 | 总和

这是我的尝试：

SELECT cf.cnum as cnum, c.name as name, r.assets as assets, SUM(rs.dh_simple + rs.ha_simple) as sums
FROM research..complex c, research..retirement_simple_ira r, retirement_survey as rs
INNER JOIN currentfunds as cf
ON rs.fundid = cf.fundid
WHERE rs.date = '9/30/2016'
HAVING SUM(rs.dh_simple + rs.ha_simple) > 0

那就是我收到错误的时候。我做错了什么？

select LTRIM(RTRIM(convert(char(10), max(date), 101))) as str 
from   inventory 
where  date < '" + date + "'"

我碰巧在我的 Java 代码中经常看到这种情况。有人可以用简单的英语重新表述上面的查询吗？谢谢你。

在下面的代码中，我为表格创建的第一列courses是一个名为 的列department_code。然后我定义fk_courses_department_code并指定department_code在这个表中将是一个department_code从departments表中引用的外键。这条线的相关性或重要性是CONSTRAINT fk_courses_department_code什么？就我而言，据了解这department_code是该表中的外键。那么究竟有什么作用fk_courses_department_code呢？它甚至作为一个真正的专栏存在吗？它只是一个别名department_code吗？究竟是什么？

CREATE TABLE courses
(
  department_code    VARCHAR2(2)   NOT NULL,
  course_number      NUMBER(3,0)   NOT NULL,
  course_title       VARCHAR2(64)  NOT NULL,
  course_description VARCHAR2(512) NOT NULL,
  credits            NUMBER(3,1)   NOT NULL,
  CONSTRAINT pk_courses
    PRIMARY KEY (department_code, course_number),
  CONSTRAINT fk_courses_department_code
      FOREIGN KEY (department_code)
      REFERENCES departments (department_code),
);