S2 - SQL Queries (in MySQL)

Problem Statement: Consider following Relation Account (Acc_no, branch_name,balance) Branch(branch_name,branch_city,assets) Customer(cust_name,cust_street,cust_city) Depositor(cust_name,acc_no) Loan(loan_no,branch_name,amount) Borrower(cust_name,loan_no) Create above tables with appropriate constraints like primary key, foreign key, not null etc.

Find all customers who have both account and loan at bank.
Find all customers who have an account or loan or both at bank.
Find all customers who have account but no loan at the bank.
Find average account balance at ‘Wadia College’ branch.
Find no. of depositors at each branch

Creating the database

CREATE DATABASE Bank2;
USE Bank2;

Creating tables:

CREATE TABLE Account (
  acc_no INT,
  branch_name VARCHAR(255),
  balance INT,
  PRIMARY KEY (acc_no)
);

CREATE TABLE Branch (
  branch_name VARCHAR(255),
  branch_city VARCHAR(255),
  assets INT,
  PRIMARY KEY (branch_name)
);

CREATE TABLE Customer (
  cust_name VARCHAR(255),
  cust_street VARCHAR(255),
  cust_city VARCHAR(255),
  PRIMARY KEY (cust_name)
);

CREATE TABLE Depositor (
  cust_name VARCHAR(255),
  acc_no INT
);

CREATE TABLE Loan (
  loan_no INT,
  branch_name VARCHAR(255),
  amount INT,
  PRIMARY KEY (loan_no)
);

CREATE TABLE Borrower (
  cust_name VARCHAR(255),
  loan_no INT
);

Declaring foreign keys

ALTER TABLE Account ADD FOREIGN KEY (branch_name) REFERENCES Branch (branch_name);
ALTER TABLE Depositor ADD FOREIGN KEY (cust_name) REFERENCES Customer (cust_name);
ALTER TABLE Depositor ADD FOREIGN KEY (acc_no) REFERENCES Account (acc_no);
ALTER TABLE Loan ADD FOREIGN KEY (branch_name) REFERENCES Branch (branch_name);
ALTER TABLE Borrower ADD FOREIGN KEY (cust_name) REFERENCES Customer (cust_name);
ALTER TABLE Borrower ADD FOREIGN KEY (loan_no) REFERENCES Loan (loan_no);

Inserting data

INSERT INTO Branch VALUES
('Wadia College', 'Pune', 50000),
('PES', 'Pune', 65000),
('Lohegaon', 'Pune', 350000),
('Viman Nagar', 'Pune', 850000);

INSERT INTO Customer VALUES
('Kalas', 'Street 12', 'Pune'),
('Himanshu', 'Street 15', 'Pune'),
('Mehul', 'Street 29', 'Pune'),
('Macho', 'Street 59', 'Mumbai'),
('Gundeti', 'Street 40', 'Mumbai'),
('Salvi', 'Street 8', 'Pune');

INSERT INTO Account VALUES
(101, 'Lohegaon', 5500),
(102, 'PES', 4324),
(103, 'PES', 5467),
(104, 'Viman Nagar', 5433),
(105, 'Wadia College', 6462);

INSERT INTO Depositor VALUES
('Kalas', 101),
('Macho', 104),
('Gundeti', 105),
('Salvi', 105);

INSERT INTO Loan VALUES
(201, 'Wadia College', 18000),
(202, 'PES', 8500),
(203, 'PES', 15000),
(204, 'Wadia College', 5322);

INSERT INTO Borrower VALUES
('Macho', 201),
('Mehul', 202),
('Himanshu', 203),
('Salvi', 204);

Queries

Find all customers who have both account and loan at bank.

SELECT cust_name FROM Depositor INTERSECT SELECT cust_name FROM Borrower;

Find all customers who have an account or loan or both at bank.

SELECT cust_name FROM Depositor UNION SELECT cust_name FROM Borrower;

Find all customers who have account but no loan at the bank.

SELECT cust_name FROM Depositor WHERE cust_name NOT IN (SELECT cust_name FROM Borrower);

Find average account balance at ‘Wadia College’ branch.

SELECT AVG(balance) FROM Account WHERE branch_name = 'Wadia College';

Find no. of depositors at each branch

SELECT Account.branch_name, COUNT(*) AS total FROM Account INNER JOIN Depositor ON Account.acc_no = Depositor.acc_no GROUP BY branch_name;

3.5 KiB Raw Permalink Blame History Unescape Escape