Sql missing right parenthesis ошибка

I have been looking at this code for the past two days now and I can not seem to get it to work. It keeps giving me

ORA-00907: missing right parenthesis.

I know that this is a topic that comes up a lot but for some reason none of the examples I have seen has helped me. Can someone please tell me why I got this error and how do I fix it? I am pretty sure that it has nothing to do with my parenthesis, maybe it’s my CONSTRAINTS?

DROP TABLE T_customers CASCADE CONSTRAINTS;
DROP TABLE dvd_collection CASCADE CONSTRAINTS;
DROP TABLE vhs_collection CASCADE CONSTRAINTS;

CREATE TABLE T_customers   (


                           customer_id         VARCHAR2 (8) PRIMARY KEY,
                           last_name           VARCHAR2 (30) NOT NULL,
                           first_name          VARCHAR2 (20) NOT NULL,
                           street             VARCHAR2 (30) NOT NULL,
                           city               VARCHAR2 (30) NOT NULL,
                           state                 CHAR (2) NOT NULL,
                                    CHECK (state IN ('GA','DC','VA','NY')),
                           zip_code           CHAR (5)
                                    CHECK (TO_NUMBER(zip_code)
                              BETWEEN 10000 AND 27999),
                           home_phone         VARCHAR2 (12) UNIQUE,
                           work_phone         VARCHAR2 (12) UNIQUE,
                           email                 VARCHAR2 (95) NOT NULL);




CREATE TABLE historys_T    (

          history_record       VARCHAR2 (8),
          customer_id       VARCHAR2 (8), 
          CONSTRAINT historys_T_FK FOREIGN KEY (customer_id) REFERENCES T_customer
                                       ON DELETE CASCADE,
                           order_id           VARCHAR2 (10) NOT NULL,
                                 CONSTRAINT fk_order_id_orders  
                                       REFERENCES orders
                                       ON DELETE CASCADE);


CREATE TABLE orders     (

                           order_id           VARCHAR2 (10) PRIMARY KEY,
                           m_p_unique_id       VARCHAR2 (10),
                                    CONSTRAINT orders_FK FOREIGN KEY (m_p_unique_id) REFERENCES library (m_p_unique_id)
                           order_date          DATE DEFAULT);



CREATE TABLE library_T     (

                           m_p_unique_id       VARCHAR2 (10)  PRIMARY KEY,
                           movie_title         VARCHAR2 (80)  NOT NULL,
                           serial_number       VARCHAR2 (10)  NOT NULL,
                           movie_id_number   VARCHAR2 (10)  NOT NULL,
                           movie_cast        VARCHAR2 (100) NOT NULL,
                           movie_format    CHAR (3) NOT NULL, 
                                  CONSTRAINT library_FK REFERENCES formats (movie_format));

CREATE TABLE formats_T     (

                           movie_format      CHAR (3) PRIMARY KEY,
                           movie_title       VARCHAR2 (80) NOT NULL,
                           m_p_unique_id     VARCHAR2 (10) NOT NULL,
                                 CONSTRAINT format_FK  REFERENCES library (m_p_unique_id));



CREATE TABLE dvd_collection (      


                           m_p_unique_id       VARCHAR2 (10) NOT NULL,
                           serial_number       VARCHAR2 (10) NOT NULL,
                           movie_id_number  VARCHAR2 (10) NOT NULL,
                           movie_title         VARCHAR2 (80) NOT NULL,
                           movie_cast          VARCHAR2 (100) NOT NULL,
                           movie_format     VARCHAR2 (80) NOT NULL,
                           movie_rating    VARCHAR2 (6) NOT NULL,
                           movie_distributer    VARCHAR2 (30) NOT NULL,
                           movie_price         NUMBER (3,2) NOT NULL,
                           movie_length     NUMBER (3) NOT NULL,
                           movie_award         VARCHAR2 (175) NOT NULL,
                           movie_release       DATE); 


CREATE TABLE vhs_collection            
(

                           m_p_unique_id       VARCHAR2 (10)NOT NULL,
                           serial_number       VARCHAR2 (10) NOT NULL,
                           movie_id_number  VARCHAR2 (10) NOT NULL,
                           movie_title         VARCHAR2 (80) NOT NULL,
                           movie_cast        VARCHAR2 (100) NOT NULL,
                           movie_format    VARCHAR2 (80) NOT NULL,
                           movie_rating    VARCHAR2 (6) NOT NULL,
                           movie_distributer    VARCHAR2 (30) NOT NULL,
                           movie_price         NUMBER (3,2) NOT NULL,
                           movie_length     NUMBER (3) NOT NULL,
                           movie_award         VARCHAR2 (175) NOT NULL,
                           movie_release        DATE);

Here are the results I get when I run the code:

Table dropped.

Table dropped.

Table dropped.

Table created.

                                       ON DELETE CASCADE)
                                       *

ERROR at line 10:
ORA-00907: missing right parenthesis

                           order_date          DATE DEFAULT)
                           *

ERROR at line 6:
ORA-00907: missing right parenthesis

                                  CONSTRAINT library_FK REFERENCES formats (movie_format))
                                                                           *

ERROR at line 9:
ORA-00907: missing right parenthesis

                                 CONSTRAINT format_FK  REFERENCES library (m_p_unique_id))
                                                                          *

ERROR at line 6:
ORA-00907: missing right parenthesis
Table created.

Table created.               

При использовании коррелированных подзапросов, т.е. подзапросов, результаты которых используются для каждой строки основного запроса — «a correlated subquery is evaluated once for each row», могут встречаются ошибки, неточно объясняющие, что делается неправильно

ora-904 «%s: invalid identifier»

SQL> select (select count(*) from
(select * from scott.emp where ename = dual.dummy)) from dual;
select (select count(*) from
(select * from scott.emp where ename = dual.dummy)) from dual
                                                        *
ERROR at line 1:
ORA-00904: "DUAL"."DUMMY": invalid identifier

ora-907 «missing right parenthesis»

SQL> select
(select empno from scott.emp where ename = dual.dummy order by 1)
from dual;
select
(select empno from scott.emp where ename = dual.dummy order by 1)
from dual
 *
ERROR at line 1:
ORA-00907: missing right parenthesis

Первая ошибка ora-904 в случае correlated subquery при отсутствии синтаксических ошибок сообщает об использовании более, чем одного уровня глубины вложенности (N-th level sub-query):

SQL> select (select count(*) from
(select * from scott.emp where ename = dual.dummy)) from dual;

select (select count(*) from
(select * from scott.emp where ename = dual.dummy)) from dual
                                       *
ERROR at line 1:
ORA-00904: "DUAL"."DUMMY": invalid identifier

что не поддерживается Oracle (до версии 11.1.0.7 включительно) и стандартом SQL: ANSI SQL has table references (correlation names) scoped to just one level deep. Это пишет вице-президент Oracle T.Kyte, отвечая на соответствующий вопрос «Is there some sort of nesting limit for correlated subqueries?», т.е. информация официальная, из первых рук, пример оттуда же. В документации Oracle, начиная с 10.1, пишется иначе:

Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement any number of levels above the subquery

— но, это, видимо, долгосрочные планы Oracle.

ora-907

SQL> select
(select empno from scott.emp where ename = dual.dummy order by 1)
from dual;

select
(select empno from scott.emp where ename = dual.dummy order by 1)
from dual
 *
ERROR at line 1:
ORA-00907: missing right parenthesis

тут проблема старая и известная: запрет на использование ORDER BY в подзапросах, описанная в документации Oracle 7 (в документации следующих версий уже не упоминается — видимо, тоже есть планы по исправлению):

The ORDER BY clause cannot appear in subqueries within other statements.

По вышеупомянутой ссылке на сайте asktom.oracle.com можно найти пример замены конструкции с ORDER BY типа:

(SELECT column_1 FROM
  (SELECT column_1 FROM table_1 WHERE ... ORDER BY ...) Q_1
 WHERE rownum = 1)

на аналитическую функцию типа :

(SELECT max(column_1) keep (dense_rank first ORDER BY ...)
 FROM table_1 WHERE ...)

для одновременного понижения уровня вложенности подзапроса (correlated query level deep) до 1 и устранения проблемы с ORDER BY в подзапросе — конструкция dense_rank first ORDER BY допускается Oracle.