SUB QUERIES IN SQL

SQL> create table customer(cid int,cname varchar2(20),age int,sex varchar2(10),dob varchar2(20),address varchar2(20),phno int,email varchar2(20),anualincome varchar2(10),primary key(cid));

Table created.

SQL>  insert into customer values('&cid','&cname','&age','&sex','&dob','&address','&phno','&email','

&annualincome');

Enter value for cid: 1

Enter value for cname: john

Enter value for age: 58

Enter value for sex: male

Enter value for dob: 5/7/55

Enter value for address: german

Enter value for phno: 8548547895

Enter value for email: john2@gmail.com

Enter value for annualincome: 15000

1 row created.

SQL> select * from customer;

CID       CNAME      AGE    SEX       DOB    ADDRESS     PHNO           EMAIL      ANUALINCOME

---------- -------------------- ----------       ---------- -------------------- -------------------- ---------- -----------------    

 1             john            58    male       5/7/55   german      8548547895  john2@gmail.com    15000

2             mac            47     male       1/9/65   japan         8547694858   mac33@gmail.com   14000

3             sam            21     male       1/7/91   greek         7894562158   sam2@yahoo.com     10000

SQL>select * from customer cid in (select cid from customer where annualincome>10000);

CID       CNAME      AGE    SEX       DOB    ADDRESS     PHNO           EMAIL      ANUALINCOME

---------- -------------------- ----------       ---------- -------------------- -------------------- ---------- -----------------      

1             john            58    male       5/7/55   german      8548547895  john2@gmail.com    15000

2             mac            47     male       1/9/65   japan         8547694858   mac33@gmail.com   14000

SQL>select * from customer cid not in (select cid from customer where anualincome>10000);

CID       CNAME      AGE    SEX       DOB    ADDRESS     PHNO           EMAIL      ANNUALINCOME

---------- -------------------- ----------       ---------- -------------------- -------------------- ---------- -----------------      

3             sam            21     male       1/7/91   greek         789456215   sam2@yahoo.com     10000