learningIT's questions

这些是我创建的表和序列，并插入了它们的值：

 CREATE TABLE Ticket_Type
  (
   t_type_id  NUMBER(1) PRIMARY KEY,
   CONSTRAINT check_t_type_id CHECK(t_type_id  > 0),
   t_type VARCHAR(20) NOT NULL,
   t_type_price NUMBER(4,2) NOT NULL,
   CONSTRAINT check_t_type_price CHECK(t_type_price > 0),
   t_type_start_date DATE NOT NULL,
   t_type_end_date DATE,
   CONSTRAINT check_t_type_end_date CHECK((t_type_end_date IS NULL) OR (t_type_end_date >= t_type_start_date)),
   CONSTRAINT unique_t_type_t_type_start_date UNIQUE(t_type,t_type_start_date)
  );


 CREATE TABLE Screening
  (
   screening_id NUMBER(6) PRIMARY KEY,
   CONSTRAINT check_screening_id CHECK(screening_id > 0),
   plan_id NUMBER(4) NOT NULL,
   theatre_id NUMBER(1) NOT NULL,
   screening_date DATE DEFAULT CURRENT_DATE NOT NULL,
   screening_start_hh24 NUMBER(2) NOT NULL,
   CONSTRAINT check_start_hh24 CHECK(screening_start_hh24 BETWEEN 9 AND 22),
   screening_start_mm60 NUMBER(2) NOT NULL,
   CONSTRAINT check_start_mm60 CHECK(screening_start_mm60 BETWEEN 0 AND 59),
   CONSTRAINT unique_theatre_id_screening_date_screening_start_hh24_screening_start_mm60 UNIQUE(theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
  );


  CREATE TABLE Ticket
  (
   ticket_id NUMBER(8) PRIMARY KEY,
   CONSTRAINT check_ticket_id CHECK(ticket_id > 0),
   t_type_id NUMBER(1) NOT NULL,
   CONSTRAINT fk_t_type_id FOREIGN KEY(t_type_id) REFERENCES Ticket_Type(t_type_id),
   screening_id NUMBER(6) NOT NULL,
   CONSTRAINT fk_screening_id FOREIGN KEY(screening_id) REFERENCES Screening(screening_id),
   seat_id NUMBER(5) NOT NULL,
   ticket_date DATE DEFAULT SYSDATE NOT NULL,
   CONSTRAINT unique_screening_id_seat_id UNIQUE(screening_id,seat_id)
  );

CREATE SEQUENCE ticket_type_seq 
START WITH 1 
INCREMENT BY 1 
NOCACHE NOCYCLE;

CREATE SEQUENCE screening_seq 
START WITH 1 
INCREMENT BY 1 
NOCACHE NOCYCLE;

CREATE SEQUENCE ticket_seq 
START WITH 1 
INCREMENT BY 1 
NOCACHE NOCYCLE;

INSERT INTO ticket_type(t_type_id,t_type,t_type_price,t_type_start_date,t_type_end_date) 
VALUES (ticket_type_seq.nextval,'Adult',13.95,to_date('1/06/2017', 'DD/MM/YYYY'),to_date('30/11/2017', 'DD/MM/YYYY'));
INSERT INTO ticket_type(t_type_id,t_type,t_type_price,t_type_start_date,t_type_end_date) 
VALUES (ticket_type_seq.nextval, 'Concession',7,to_date('1/06/2017', 'DD/MM/YYYY'),to_date('30/11/2017', 'DD/MM/YYYY'));
INSERT INTO Ticket_Type (t_type_id,t_type,t_type_price,t_type_start_date)
VALUES (ticket_type_seq.NEXTVAL,'Student',7,TO_DATE('1/6/2017','DD/MM/YYYY'));

INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,1,3,TO_DATE('11/9/2017','DD/MM/YYYY'),9,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,1,3,TO_DATE('11/9/2017','DD/MM/YYYY'),11,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,1,3,TO_DATE('11/9/2017','DD/MM/YYYY'),13,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,1,2,TO_DATE('11/9/2017','DD/MM/YYYY'),13,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,1,3,TO_DATE('11/9/2017','DD/MM/YYYY'),15,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,1,3,TO_DATE('11/9/2017','DD/MM/YYYY'),17,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,2,1,TO_DATE('11/9/2017','DD/MM/YYYY'),9,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,2,1,TO_DATE('11/9/2017','DD/MM/YYYY'),12,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,2,1,TO_DATE('11/9/2017','DD/MM/YYYY'),15,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,2,2,TO_DATE('11/9/2017','DD/MM/YYYY'),9,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,2,2,TO_DATE('11/9/2017','DD/MM/YYYY'),15,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,3,4,TO_DATE('11/9/2017','DD/MM/YYYY'),9,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,3,4,TO_DATE('11/9/2017','DD/MM/YYYY'),12,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,3,4,TO_DATE('11/9/2017','DD/MM/YYYY'),15,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,3,4,TO_DATE('11/9/2017','DD/MM/YYYY'),18,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,4,1,TO_DATE('11/9/2017','DD/MM/YYYY'),18,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,4,2,TO_DATE('11/9/2017','DD/MM/YYYY'),18,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,4,3,TO_DATE('11/9/2017','DD/MM/YYYY'),19,0);
INSERT INTO Screening (screening_id,plan_id,theatre_id,screening_date,screening_start_hh24,screening_start_mm60)
VALUES (screening_seq.NEXTVAL,4,4,TO_DATE('11/9/2017','DD/MM/YYYY'),21,0);

现在这是作业中的问题：

创建一个名为 TR_valid_15_min_ticket 的触发器，它应该在 Ticket 表中插入一行之前检查 ticket_date。出票日期（ticket_date）不能大于当前日期和时间。此外，它不能大于筛选时间 + 15 分钟。否则，对于每种情况，触发器都应引发适当的错误。错误消息必须有意义。

提示：筛选时间 = 筛选日期 + (screening_start_hh24)/24 + (screening_start_mm60)/(24*60)

注：ticket_date 在 Ticket 表中，screening_date,screening_start_hh24,screening_start_mm60 在 Screening 表中

这是我尝试过的：

CREATE OR REPLACE TRIGGER TR_valid_15_min_ticket 
BEFORE INSERT
ON Ticket
FOR
EACH ROW
DECLARE
screening_time NUMBER;
v_screening_date Screening.screening_date%TYPE;
v_screening_start_hh24 Screening.screening_start_hh24%TYPE;
v_screening_start_mm60 Screening.screening_start_mm60%TYPE;
BEGIN
screening_time := v_screening_date + (v_screening_start_hh24)/24 + (v_screening_start_mm60)/(24*60);
IF (:NEW.ticket_date > SYSDATE) OR  (:NEW.ticket_date > (screening_time + 15))
THEN
RAISE_APPLICATION_ERROR(-20000,'Ticket date cannot be more than the current date nor can it have extra 15 minutes with addition to its screening time');
END IF;
END;

但是，我收到以下错误：

Error(95,1): PL/SQL: Statement ignored
Error(95,19): PLS-00382: expression is of wrong type
Error(96,1): PL/SQL: Statement ignored
Error(96,55): PLS-00306: wrong number or types of arguments in call to '>'

如果提供了正确的解决方案触发代码，那将非常有帮助。

这些是我创建并在其中插入值的表：

CREATE TABLE Customer
(Customer_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 Customer_Name VARCHAR(30) NOT NULL
)

CREATE TABLE DVD
(DVD_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 DVD_Name VARCHAR(30)
)

CREATE TABLE DVD_Purchase
(DVD_Purchase_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 DVD_No INTEGER NOT NULL,
 Customer_No INTEGER NOT NULL
 )

INSERT INTO Customer (Customer_Name)
VALUES('Daman')
INSERT INTO Customer (Customer_Name)
VALUES('Saif')
INSERT INTO Customer (Customer_Name)
VALUES('Gurung')
INSERT INTO Customer (Customer_Name)
VALUES('Upendra')
INSERT INTO Customer (Customer_Name)
VALUES('Ornob')

INSERT INTO DVD (DVD_Name)
VALUES('Bleach')
INSERT INTO DVD (DVD_Name)
VALUES('Gintama')
INSERT INTO DVD (DVD_Name)
VALUES('Tokyo Ghoul')
INSERT INTO DVD (DVD_Name)
VALUES('Death Note')
INSERT INTO DVD (DVD_Name)
VALUES('Rurouni Kenshin')


INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (4,1)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,2)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (2,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (3,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (4,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,4)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (2,4)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (5,1)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (5,2)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (5,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (5,4)

为了显示没有使用 MINUS 运算符购买任何 DVD 的客户名称，这是我尝试过的：

SELECT Customer_Name
FROM Customer
WHERE Customer_No 
IN
(SELECT Customer_No FROM Customer
 MINUS
 SELECT Customer_No FROM DVD_Purchase
)

我收到以下错误消息：

消息 156，级别 15，状态 1，第 123 行关键字“SELECT”附近的语法不正确。消息 102，级别 15，状态 1，第 124 行 ')' 附近的语法不正确。

如错误消息中所示，')' 附近的语法不正确，我无法弄清楚。

除此之外，有人可以为我提供正确的查询吗？

这些是我创建的表并相应地插入了值：

CREATE TABLE Customer
(Customer_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 Customer_Name VARCHAR(30) NOT NULL
)

CREATE TABLE DVD
(DVD_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 DVD_Name VARCHAR(30)
)

CREATE TABLE DVD_Purchase
(DVD_Purchase_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 DVD_No INTEGER NOT NULL,
 Customer_No INTEGER NOT NULL
 )

INSERT INTO Customer (Customer_Name)
VALUES('Daman')
INSERT INTO Customer (Customer_Name)
VALUES('Saif')
INSERT INTO Customer (Customer_Name)
VALUES('Gurung')
INSERT INTO Customer (Customer_Name)
VALUES('Upendra')

INSERT INTO DVD (DVD_Name)
VALUES('Bleach')
INSERT INTO DVD (DVD_Name)
VALUES('Gintama')
INSERT INTO DVD (DVD_Name)
VALUES('Tokyo Ghoul')
INSERT INTO DVD (DVD_Name)
VALUES('Death Note')

INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (4,1)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,2)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (2,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (3,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (4,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,4)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (2,4)

为了显示购买了所有 DVD 的客户的姓名，这是我尝试过的查询：

SELECT Customer_Name
FROM Customer
WHERE 
EXISTS (SELECT Customer_Name,DVD_Name
FROM Customer,DVD,DVD_Purchase
WHERE Customer.Customer_No = DVD_Purchase.Customer_No AND DVD.DVD_No = DVD_Purchase.DVD_No)

但不幸的是，执行此查询后，它会显示所有客户名称。但我想显示购买了所有 DVD 的特定客户名称。

有人可以向我提供正确的查询吗？