Scribd Logo
Upload

Welcome to Scribd!

  • 0 views

    bhikas dbms 3 (1)

    Uploaded by

    rajeshkr10de
    data

    Copyright:

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

    bhikas dbms 3 (1)

    Uploaded by

    rajeshkr10de
    0 views3 pages
    data

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    data

    Copyright:

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

    bhikas dbms 3 (1)

    Uploaded by

    rajeshkr10de
    data

    Copyright:

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

    NAME : SAURABH KAPASIYA

    EXPEIMENT NO.-3 ROLL NUM : 2202161630086

    OBJECTIVE:- account =acc_n0.,branch name,balance


    branch=branch name,branch ciity,assets
    customer=customername,cust_street,cust_city,,,,
    depositer=cust_name,acc_no
    loan=loan_no.,branch name,amount
    borrower=cust_name,loan no
    1. find customer name with their account no. and loan no.
    2. find customer name having both account and loan
    3. find cust_name who have account but no loan 4. ind
    name sof customer who have more than one account.

    SQL QUERY:-
    CREATE TABLE Account (
    AccNo INT PRIMARY KEY,
    BranchName VARCHAR(50),
    Balance DECIMAL(10, 2)
    );
    CREATE TABLE Branch (
    BranchName VARCHAR(50) PRIMARY KEY,
    BranchCity VARCHAR(50),
    Assets DECIMAL(15, 2)
    );
    CREATE TABLE Customer (
    CustName VARCHAR(100) PRIMARY KEY,
    CustStreet VARCHAR(100),
    CustCity VARCHAR(50)
    );
    CREATE TABLE Depositer (
    CustName VARCHAR(100),
    AccNo INT,
    PRIMARY KEY (CustName, AccNo),
    FOREIGN KEY (CustName) REFERENCES Customer(CustName),
    FOREIGN KEY (AccNo) REFERENCES Account(AccNo)
    );
    CREATE TABLE Loan (
    LoanNo INT PRIMARY KEY,
    BranchName VARCHAR(50),
    Amount DECIMAL(10, 2),
    FOREIGN KEY (BranchName) REFERENCES Branch(BranchName)
    );
    CREATE TABLE Borrower (
    CustName VARCHAR(100),
    LoanNo INT,
    PRIMARY KEY (CustName, LoanNo),
    FOREIGN KEY (CustName) REFERENCES Customer(CustName),
    FOREIGN KEY (LoanNo) REFERENCES Loan(LoanNo)
    );
    INSERT INTO Branch (BranchName, BranchCity, Assets)
    VALUES
    ('SBI Connaught Place', 'Delhi', 100000000),
    ('HDFC MG Road', 'Bangalore', 75000000);

    INSERT INTO Account (AccNo, BranchName, Balance)


    VALUES
    (101, 'SBI Connaught Place', 50000),
    (102, 'SBI Connaught Place', 200000),
    (103, 'HDFC MG Road', 10000);

    INSERT INTO Customer (CustName, CustStreet, CustCity)


    VALUES
    ('Amit Sharma', '123 Lodhi Road', 'Delhi'),
    ('Priya Verma', '45 Indiranagar', 'Bangalore'),
    ('Rajesh Kumar', '12 Dadar East', 'Mumbai');

    INSERT INTO Depositer (CustName, AccNo)


    VALUES
    ('Amit Sharma', 101),
    ('Amit Sharma', 102),
    ('Priya Verma', 103);

    INSERT INTO Loan (LoanNo, BranchName, Amount)


    VALUES
    (201, 'SBI Connaught Place', 500000),
    (202, 'HDFC MG Road', 300000);

    INSERT INTO Borrower (CustName, LoanNo)


    VALUES
    ('Amit Sharma', 201),
    ('Rajesh Kumar', 202);

    1. SELECT
    C.CustName,
    D.AccNo,
    B.LoanNo
    FROM
    Customer C
    LEFT JOIN
    Depositer D ON C.CustName = D.CustName
    LEFT JOIN
    Borrower B ON C.CustName = B.CustName;
    2. SELECT
    C.CustName
    FROM
    Customer C
    INNER JOIN
    Depositer D ON C.CustName = D.CustName
    INNER JOIN
    Borrower B ON C.CustName = B.CustName;
    3. SELECT
    C.CustName
    FROM
    Customer C
    INNER JOIN
    Depositer D ON C.CustName = D.CustName
    WHERE
    C.CustName NOT IN (SELECT CustName FROM Borrower);

    4. SELECT
    D.CustName
    FROM
    Depositer D
    GROUP BY
    D.CustName
    HAVING
    COUNT(D.AccNo) > 1;

    You might also like