Scribd Logo
Upload

Welcome to Scribd!

  • 4 views

    Lab Assignment-5

    Uploaded by

    heathcliff5792

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Lab Assignment-5

    Uploaded by

    heathcliff5792
    4 views3 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    4 views3 pages

    Lab Assignment-5

    Uploaded by

    heathcliff5792

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 3

    Laboratory Assignments

    Subject: Introduction to Databases


    Subject code: CSE 3151

    Assignment 5: Subqueries and Joins

    Objective of this Assignment:

     To write SQL query using concept of sub query and join.

    A) Write the expression for the following set of queries in SQL, based on the set of schemas
    of Assignment (4), using concept of sub query.

    1. Find out the name, phone_no and cust_no of customer having Account_no “A0004”.
    2. Find out the name of the customer who has not taken any loan.
    3. Find out the branch_city where “ASLESHA TIWARI” has taken a loan.
    4. Find out the installment details of customer named “ANKITA SINGH”.
    5. Find out the branch name and branch city, in which “ABHIJIT MISHRA” has an account.
    6. Find out the account_no that has greater balance than some accounts of type FD.
    (Use >some clause)
    7. Find out the account_no that has grater balance than all accounts of type FD. (Use >all
    clause)
    8. Display the details of the branch in which some loans are taken. (Use exist clause)
    9. Display the details of the loan for which no instalments are paid. (Use not exist clause)
    10. Increase all accounts with balance over 80000 by 6%, and all other accounts receive 5%.
    (Use case statement)

    B) Write the expression for the following set of queries in SQL, based on the set of schemasof
    Assignment (4), using concept of join.
    1. Find out the Loan_nos where the loans are taken from any branch with branch_city
    =MUMBAI.
    2. Find the Type of the accounts available in any branch with branch_city =DELHI.
    3. Find out the Name and Ph_no of customers who have account balance more than 100000.
    4. Find out Installment_no and Installment amount of customer with Name= RAJ ANAND
    SINGH.
    5. Find out the Name of the customers who do not have account of Type=SB.
    6. Find out the Name of the customers who have paid installments of Amount 50000 against
    his/her loan.
    7. Find out the Ph_no of customers having account at branch with Branch_name equal to
    SALTLAKE.
    8. Find out the Branch_name and Branch_city where customer with Name=ABHIJIT
    MISHRA has his account.
    9. Find out the Types of account and the account Balance of customer with
    Name=’SWAROOP RAY’
    10. Find all branch codes where the total balance is greater than the average of the total balance
    at all departments. (Use with clause).

    You might also like