all customers who have placed at least one order from us in the last month : Query IN « Select Query « Oracle PL / SQL

Home
Oracle PL / SQL
1.Aggregate Functions
2.Analytical Functions
3.Char Functions
4.Constraints
5.Conversion Functions
6.Cursor
7.Data Type
8.Date Timezone
9.Hierarchical Query
10.Index
11.Insert Delete Update
12.Large Objects
13.Numeric Math Functions
14.Object Oriented Database
15.PL SQL
16.Regular Expressions
17.Report Column Page
18.Result Set
19.Select Query
20.Sequence
21.SQL Plus
22.Stored Procedure Function
23.Subquery
24.System Packages
25.System Tables Views
26.Table
27.Table Joins
28.Trigger
29.User Previliege
30.View
31.XML
Oracle PL / SQL » Select Query » Query IN 
all customers who have placed at least one order from us in the last month
     
SQL>
SQL>
SQL>
SQL> create table ord(
  2           order_no               integer          primary key
  3          ,cust_no                integer
  4          ,order_date             date not null
  5          ,total_order_price      number(7,2)
  6          ,deliver_date           date
  7          ,deliver_time           varchar2(7)
  8          ,payment_method         varchar2(2)
  9          ,emp_no                 number(3,0)
 10          ,deliver_name           varchar2(35)
 11          ,gift_message           varchar2(100)
 12  );

Table created.

SQL>
SQL>
SQL> insert into ord(order_no,cust_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
  2           values(1,1,'14-Feb-2002', 23.00'14-Feb-2002', '12 noon', 'CA',1, null, 'Gift for wife');

row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(2,1,'14-Feb-2003', 510.98'14-feb-2003', '5 pm', 'NY',7'Rose Ted', 'Happy Valentines Day to Mother');

row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(32,'14-Feb-2004', 315.99'14-feb-2004', '3 pm', 'VS',2'Ani Forest', 'Happy Valentines Day to Father');

row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(42,'14-Feb-1999', 191.95'14-feb-1999', '2 pm', 'NJ',2'O. John', 'Happy Valentines Day');

row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message    )
  2           values(56,'4-mar-2002', 101.95'5-mar-2002', '2:30 pm', 'MO'   2'Cora', 'Happy Birthday from John');

row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(69,'7-apr-2003', 221.95'7-apr-2003', '3 pm', 'MA', 2'Sake Keith', 'Happy Birthday from Joe' );

row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(79,'20-jun-2004', 315.95'21-jun-2004', '12 noon', 'BC', 2'Jessica Li', 'Happy Birthday from Jessica');

row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values (812'31-dec-1999', 135.95'1-jan-2000', '12 noon', 'DI',      3'Larry', 'Happy New Year from Lawrence');

row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values (912'26-dec-2003', 715.95'2-jan-2004', '12 noon', 'SK',7'Did', 'Happy Birthday from Nancy' );

row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(104, sysdate-1119.95, sysdate+2'6:30 pm', 'VG',2'P. Jing', 'Happy Valentines Day to Jason');

row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(112, sysdate, 310.00, sysdate+2'3:30 pm', 'DC',2'C. Late', 'Happy Birthday Day to Jack');

row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
  2           values(127, sysdate-3121.95, sysdate-2'1:30 pm', 'AC',2'W. Last', 'Happy Birthday Day to You');

row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
  2           values(137, sysdate, 211.95, sysdate-4'4:30 pm', 'CA',2'J. Bond', 'Thanks for hard working');

row created.

SQL>
SQL>
SQL> create table customer(
  2           cust_no                   integer                 primary key
  3          ,lastname                  varchar2(20)    not null
  4          ,firstname                 varchar2(15)    not null
  5          ,midinit                   varchar2(1)
  6          ,street                    varchar2(30)
  7          ,city                      varchar2(20)
  8          ,state                             varchar2(2)
  9          ,zip                               varchar2(5)
 10          ,zip_4                             varchar2(4)
 11          ,area_code                         varchar2(3)
 12          ,phone                             varchar2(8)
 13          ,company_name                      varchar2(50)
 14  );

Table created.

SQL>
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(1'Allen', 'Joe','J','10 Ave','London','CA','11111','1111','111', '111-1111','Big Company');

row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(2,'Ward','Sue','W','20 Ave','New York','NY','44444','4444','444', '436-4444','B Company');

row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(3,'Jason','Pure','J','50 St','Longli','CA','55555','5555','555', '234-4444','C Company');

row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(4,'Bird','Jill', null,'30 St','Pais','NY','22222','2222','222', '634-7733','D Company');

row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(5,'Hill','Carl','H','19 Drive','A Town','CA','66666','6566','666', '243-4243','E Company');

row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(6,'Peter','Yari','P','38 Ave','Small City','NY','77777','7777','777', '454-5443','F Inc');

row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(7,'Joe','Paula','J','78 St. Apt 3A','Queen City','NY','32322','2323','888', '664-4333','E Inc');

row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(8,'Chili','Steve','C','38 Ave Apt 62','Mili','CA','88888','8888','787', '456-4566','G Inc');

row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(9,'Mona','Joe','M','930 Ave933','Kansas City','MO','12345','1234','412', '456-4563','H Inc');

row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(10,'Hack','Kisi','H','Kings Rd','Bellmore','NY','54321','3898','516', '767-5677','I Inc');

row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(11,'Bill','Jose','B','12 Giant Rd.','Newton','NJ','23454','1234','958', '123-7367','J Associates');

row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(12,'Taker','Lawrence','T','Sask Rd.','Camp','NJ','19191','3298','928', '123-7384','K Company');

row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(13,'Richer','Doris','R','213 Easy Street','WarPease','RI','34343','2112','501', '123-7384','L Inc');

row created.

SQL> insert into customercust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(14,'Pete','Doris','P','Ave','New York','NY','45454','4222','112', '123-1234','M Company');

row created.

SQL>
SQL> select from customer;
   CUST_NO LASTNAME             FIRSTNAME       M STREET                         CITY                 ST ZIP   ZIP_ ARE PHONE
---------- -------------------- --------------- - ------------------------------ -------------------- -- ----- ---- --- --------
COMPANY_NAME
--------------------------------------------------
         Allen                Joe             J 10 Ave                         London               CA 11111 1111 111 111-1111
Big Company

         Ward                 Sue             W 20 Ave                         New York             NY 44444 4444 444 436-4444
B Company

         Jason                Pure            J 50 St                          Longli               CA 55555 5555 555 234-4444
C Company

         Bird                 Jill              30 St                          Pais                 NY 22222 2222 222 634-7733
D Company

         Hill                 Carl            H 19 Drive                       A Town               CA 66666 6566 666 243-4243
E Company

         Peter                Yari            P 38 Ave                         Small City           NY 77777 7777 777 454-5443
F Inc

         Joe                  Paula           J 78 St. Apt 3A                  Queen City           NY 32322 2323 888 664-4333
E Inc

         Chili                Steve           C 38 Ave Apt 62                  Mili                 CA 88888 8888 787 456-4566
G Inc

         Mona                 Joe             M 930 Ave933                     Kansas City          MO 12345 1234 412 456-4563
H Inc

        10 Hack                 Kisi            H Kings Rd                       Bellmore             NY 54321 3898 516 767-5677
I Inc

        11 Bill                 Jose            B 12 Giant Rd.                   Newton               NJ 23454 1234 958 123-7367
J Associates

        12 Taker                Lawrence        T Sask Rd.                     Camp                 NJ 19191 3298 928 123-7384
K Company

        13 Richer               Doris           R 213 Easy Street                WarPease             RI 34343 2112 501 123-7384
L Inc

        14 Pete                 Doris           P Ave                          New York             NY 45454 4222 112 123-1234
M Company


14 rows selected.

SQL>
SQL> select firstname || ' ' || lastname as "Name",
  2    '(' || area_code || ')' || phone as "Telephone"
  3    from customer
  4   where cust_no in
  5     (select cust_no from ord
  6       where order_date > add_months(sysdate, -1));
Name                 Telephone
-------------------- -------------
Jill Bird            (222)634-7733
Sue Ward             (444)436-4444
Paula Joe            (888)664-4333

rows selected.

SQL>
SQL> drop table customer;

Table dropped.

SQL>
SQL> drop table ord;

Table dropped.

SQL>
SQL> --

   
    
    
    
  
Related examples in the same category
1.Query with an IN operator
2.Use in operator with number type values
3.Use in operator with 'and' in where clause
4.Use in operator with table join
5.Using the IN operator: compare with OR
6.NOT IN processes a null in a list of values
7.Use in operator with varchar2 type value
8.not in (1,2,3,NULL)
9.not in vs not exists
10.Value pair and in operator
11.All attendees whose id is not in the list
12.Use in operator to match three employee numbers
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.