what is the query in oracle sql to find product orders before a certain date?

how do i find all the customers who have made orders before 2017. List must incld the customer ID, customer name, and ordered by their ID values in desc

CREATE TABLE employees
      (
        employee_id NUMBER 
                    GENERATED BY DEFAULT AS IDENTITY START WITH 108 
                    PRIMARY KEY,
        first_name VARCHAR( 255 ) NOT NULL,
        last_name  VARCHAR( 255 ) NOT NULL,
        email      VARCHAR( 255 ) NOT NULL,
        phone      VARCHAR( 50 ) NOT NULL ,
        hire_date  DATE NOT NULL          ,
        manager_id NUMBER( 12, 0 )        , -- fk
        job_title  VARCHAR( 255 ) NOT NULL,
        CONSTRAINT fk_employees_manager 
            FOREIGN KEY( manager_id )
            REFERENCES employees( employee_id )
            ON DELETE CASCADE
      );
    -- product category
    CREATE TABLE product_categories
      (
        category_id NUMBER 
                    GENERATED BY DEFAULT AS IDENTITY START WITH 6 
                    PRIMARY KEY,
        category_name VARCHAR2( 255 ) NOT NULL
      );


    -- products table
    CREATE TABLE products
      (
        product_id NUMBER 
                   GENERATED BY DEFAULT AS IDENTITY START WITH 289 
                   PRIMARY KEY,
        product_name  VARCHAR2( 255 ) NOT NULL,
        description   VARCHAR2( 2000 )        ,
        standard_cost NUMBER( 9, 2 )          ,
        list_price    NUMBER( 9, 2 )          ,
        category_id   NUMBER NOT NULL         ,
        CONSTRAINT fk_products_categories 
          FOREIGN KEY( category_id )
          REFERENCES product_categories( category_id ) 
          ON DELETE CASCADE




     CREATE TABLE customers
          (
            customer_id NUMBER 
                        GENERATED BY DEFAULT AS IDENTITY START WITH 320 
                        PRIMARY KEY,
            name         VARCHAR2( 255 ) NOT NULL,
            address      VARCHAR2( 255 )         ,
            website      VARCHAR2( 255 )         ,
            credit_limit NUMBER( 8, 2 )
          );



        CREATE TABLE orders
          (
            order_id NUMBER 
                     GENERATED BY DEFAULT AS IDENTITY START WITH 106 
                     PRIMARY KEY,
            customer_id NUMBER( 6, 0 ) NOT NULL, -- fk
            status      VARCHAR( 20 ) NOT NULL ,
            salesman_id NUMBER( 6, 0 )         , -- fk
            order_date  DATE NOT NULL          ,
            CONSTRAINT fk_orders_customers 
              FOREIGN KEY( customer_id )
              REFERENCES customers( customer_id )
              ON DELETE CASCADE,
            CONSTRAINT fk_orders_employees 
              FOREIGN KEY( salesman_id )
              REFERENCES employees( employee_id ) 
              ON DELETE SET NULL
          );


        CREATE TABLE order_items
          (
            order_id   NUMBER( 12, 0 )                                , -- fk
            item_id    NUMBER( 12, 0 )                                ,
            product_id NUMBER( 12, 0 ) NOT NULL                       , -- fk
            quantity   NUMBER( 8, 2 ) NOT NULL                        ,
            unit_price NUMBER( 8, 2 ) NOT NULL                        ,
            CONSTRAINT pk_order_items 
              PRIMARY KEY( order_id, item_id ),
            CONSTRAINT fk_order_items_products 
              FOREIGN KEY( product_id )
              REFERENCES products( product_id ) 
              ON DELETE CASCADE,
            CONSTRAINT fk_order_items_orders 
              FOREIGN KEY( order_id )
              REFERENCES orders( order_id ) 
              ON DELETE CASCADE
          );

Answer

You can use exists and a correlated subquery to filter customers who ordered before 2017. We can get that information by looking at the orders table only:

select c.customer_id, c.name
from customers c
where exists (
    select 1 
    from orders o 
    where o.customer_id = c.customer_id and o.order_date < date '2017-01-01'
)
order by c.customer_id desc