Assignment in SQL for use of set operator

Assignment:-               

Assign. Name:- In SQL Use of SET Operator.

CLASS:-T.Y.B.Sc(Computer)          

*************************************************************

Use of SET Operator:
- UNION
- INTERSECT
- IN
- NOT IN

SQL>select * from borrower;

 CUST_NAME                 LOAN_NO
---------------------    -------------
Samir                            1
Mahesh                         2
Smita                            3
Vrushali                         4
Devendra                       5
Vaibhav                         6

UNION:-
SQL> (select cust_name from borrower) UNION
     (select cust_name from depositer);
 CUST_NAME
-----------------------
 Devendra
 Mahesh
 Samir
 Smita
 Vaibhav
 Vrushali
  6 rows selected.
SQL> (Select cust_name from borrower) UNION ALL
     (Select cust_name from depositer);

    CUST_NAME
---------------------------------
    Samir
    Mahesh
    Smita
    Vrushali
    Devendra
    Vaibhav
    Samir
    Mahesh
    Samita
    Vrushali
    Devendra   

    

11 row selected.

INTERSECT:-

SQL> (Select cust_name from borrower) INTERSECT
     (Select cust_name from depositer);

    CUST_NAME
  ----------------------------
    Devendra
    Mahesh
    Samir
    Smita
    Vrushali

 IN:-

SQL> Select DISTINCT cust_name from borrower WHERE
     cust_name IN (Select cust_name from depositer);

    CUST_NAME
       ------------------------
    Devendra
    Mahesh
    Samir
    Smita
    Vrushali

 NOT IN:-

SQL> Select DISTINCT cust_name from borrower WHERE
     cust_name NOT IN (Select cust_name from depositer);

    CUST_NAME
    ----------------------
    Vaibhav
   

Assignment for SQL Queries Operations TYBSc(computer science)

Assignment :-               
Assign. Name:-SQL Queries Operations
CLASS:-T.Y.B.Sc. (Computer)      

*************************************************************
            SQL Queries on Single Table

SQL Simple Queries:-
            -Select Clause
            -From  Clause
            -Where  Clause

LOAN_NO            BNAME            AMMOUNT
--------------        ------------        ----------------
1            M G ROAD            10000
2            J M ROAD            15000
3            F C ROAD            12000
4            G P ROAD            25000
5            M G ROAD            18000
6            J M ROAD            5000
7            G P ROAD            20000

Example1:-  Find the distinct name of branches in the loan.

SQL>select distinct Bname from loan;
       BNAME
-----------------------
    F C ROAD
     G P ROAD
    J M ROAD
     M G ROAD

Example2:-Find the loan number of those 10000<=loan amount<=25000.

SQL>select loan_no from loan where amount>=10000 AND amount<=25000;

     LOAN_NO
--------------------------
     1
     2
     3
     4
     5
     7

 Example3:- Find the loan number,branch name amount of those loans amount between 10000 and 20000.

SQL> select * from loan where amount BETWEEN 10000 AND 20000;
    LOAN_NO        BNAME              AMOUNT       
-----------------         --------------        -----------------
  1                    M G ROAD            10000
  2                   J M ROAD            15000
  3                   F C ROAD            12000
  5                 M G ROAD            18000
  7                 G P ROAD            20000
 
Example4:- Find all the attribute in loan where interest on the amount is calculated as 3% of the amount.

SQL> select loan_no,Bname,Amount,Amount*0.03 from loan;

LOAN_NO          BNAME              AMOUNT             AMOUNT*0.03
-----------------      --------------        -----------------       ----------------------
  1                 M G ROAD            10000                    300
  2                 J M ROAD            15000                    450
  3                 F C ROAD            12000                    360
  4                G P ROAD            25000                    750
  5                M G ROAD            18000                    540
  6               J M ROAD            5000                    150
  7                G P ROAD            20000                    600

Example5:- Find all the attribute in loam where branch name equal to J M Road.

SQL>select loan_no,Bname,amount from loan where Banme='J M Road';

LOAN_NO       BNAME                    AMMOUNT
---------------     --------------        ------------------
 2                 J M ROAD                15000
 6                 J M ROAD                5000

Example6:- Find all the attribute in loan where adds (using + operator ) 500 in amount.

SQL> select loan_no,Bname,amount,amount+500 from loan;

LOAN_NO         BNAME              AMOUNT             AMOUNT+500
-----------------        --------------        -----------------       ----------------------
  1                    M G ROAD            10000                10500
  2                     J M ROAD            15000                15500
  3                     F C ROAD            12000                12500
  4                    G P ROAD            25000                25500
  5                     M G ROAD            18000                18500
  6                    J M ROAD            5000                5500
  7                    G P ROAD            20000                20500