Consider the following relational database:

Customer (custId, custName, custAddress, custPhone)
Account (AccountNumber, custId, TypeOfAccount, Balance)
Transaction (DateTimeofTransaction, AccountNumber, DebitORCredit, Amount)

The underlined attribute(s) in the relation forms the primary key. In relation Customer, the custID is
the unique identifier of a customer. The purposes of other attributes in Customer relation are self
explanatory. You may define the domain of different attributes. The TypeOfAccount attribute can
take the value (“Saving”, “Current”, “Salary”, “Other”). Please note that at a specific time of a date
only one transaction can be performed from an account. Please also note that the Account relation
has foreign key custID and Transaction relation has foreign key AccountNumber. Write and run the
following SQL queries on the database:
(i) Create the tables with the primary and foreign key constraints.
(ii) Insert at least 5 records in the first 2 tables and 20 records in the 3rd table.
(iii) List “Saving” Account details showing the AccountNumber, custName, custPhone,
Balance of all the accounts. These records should be shown in the order of custName.
(iv) Find all the transactions made by customer whose custID is “240002”.
(v) Find the list of those customers who have more than one account.
(vi) Find the total of Debit transactions made for Account Number “A0054”
(vii) Find the total of balance of all the accounts of a customer.
(viii) Find the list of customers whose name start with an alphabet “B”
(ix) List the pair of customers who share the same phone number.
(x) Find the list of the customers, who have not made any credit transaction since 1st January,
2023.

(i) Creating Tables with Primary and Foreign Key Constraints:

```sql
CREATE TABLE Customer (
    custId INT PRIMARY KEY,
    custName VARCHAR(100),
    custAddress VARCHAR(255),
    custPhone VARCHAR(15)
);

CREATE TABLE Account (
    AccountNumber VARCHAR(10) PRIMARY KEY,
    custId INT,
    TypeOfAccount ENUM('Saving', 'Current', 'Salary', 'Other'),
    Balance DECIMAL(10, 2),
    FOREIGN KEY (custId) REFERENCES Customer(custId)
);

CREATE TABLE Transaction (
    DateTimeofTransaction DATETIME,
    AccountNumber VARCHAR(10),
    DebitORCredit ENUM('Debit', 'Credit'),
    Amount DECIMAL(10, 2),
    FOREIGN KEY (AccountNumber) REFERENCES ________ __________ __________ ____ _______ _______ ______ __________.
____ _____ _______ ____ ____ _________ _______ _____.
________ _____ ________ ______ _______ _________.
__________ _______ ____ _______ _____ _______ ____ _________ ______ ________ _________.
___ __________ _____ ___ ______ ____ _________ ________ ______.
___ __________ ___ ____ _________ ______ _______ _________.
__________ __________ ________ ____ ________ _______ ____ __________ _____ ___ _____.
______ __________ _______ _________ ________.
_________ _______ _________ ________ ________ ______ _______ ___.
___ ____ _________ _______ ____ _________ ____ _____.
______ __________ __________ ___ __________ _______ ______ __________ _________ _____ _________.
_____ _____ _______ _________ _____.
____ ______ _______ _________ ____ ____.
___ _______ __________ _______ _________ ___ ________ _____ __________ _______.
_____ _______ ____ ____ ____ ________ ________ ______ ____ _________ _________.
_________ ________ _________ ____ ___ ___ __________ ___ ___ _______ ________.
_________ ________ ___ ________ _________.
_________ ______ __________ ____ ____.
___ _________ ____ _______ ________ _______ __________.
___ ________ __________ _________ _____ ___ _________.
____ __________ ______ _________ _____.
________ __________ ______ _____ ____ ______ ______ ____ ___.
__________ ____ _______ ___ __________ ______ ______ ________ _______.
_______ ________ __________ ________ ________ __________ ______.
____ ____ _____ _______ ______ ______ ______.
____ __________ _____ ___ ______ _________ _________ _____ ________.
_____ _________ _______ ________ ___ _________ _____ __________ _______ _________.
_______ _______ ____ __________ _________ _________ ____ _______ ______ ______.
________ ______ _______ _______ ______ _______ _________ ______ ____ _________ ______.
_________ ____ ________ _________ __________ ____ _________.
_____ ________ ______ __________ ____ ___.
_________ _________ ____ ____ _______ ____ _______ __________ ____ _______ __________ ___.
_______ _______ ________ _____ ________.
________ ________ ______ _______ _______.
_______ ________ _________ ________ __________ _________ _______.
________ ________ ___ ________ ________.
___ ______ ___ ____ ___ _________.
____ _________ ____ __________ _____ ________ __________ _______ _________ _________.
________ _________ __________ ___ ______ _______.
______ _______ ___ ___ ____ _______ _____ _____ ___ __________ _______.
____ ___ _____ _____ _________ ______ _________ ________ _________ ________.
___ ______ ___ _________ ________ ___ _____ _______ __________ ___.
________ _________ ______ ________ ________ _____ ______ _________ ________ _______.
_________ ______ ___ _____ ______.
________ ________ __________ ____ _______ ___ ___ ____ ___.
_________ __________ _____ _______ ___ _______ ___.
_______ _________ __________ ______ _______ _______ ________ _________ _______ ______ _________.
_____ _____ _________ __________ __________ ________.
________ ______ ____ ____ __________ ________.
________ __________ ____ _____ _____ _____ _______ ___ __________ ____ _____.
____ __________ __________ __________ _______ ___ __________ _________ ____ __________ ______.
____ _________ ____ ________ ___ _________ ___.
__________ ____ _________ ________ _____ ________ ______ ____ _____ ________ __________.
________ _________ _______ ________ ________.
____ ____ ___ _________ _______ _________.
________ _______ ______ ____ __________ ____ _______ _________ ________ ________ _____.
________ ____ _______ _____ ________.
_______ _________ __________ _________ ________.
______ ___ ______ ___ ________ ______ _________ _______ __________ __________ _____.
______ ______ _______ _______ _____.
____ __________ ____ ____ _____ _______ _______.
________ ________ ______ ______ __________ ______ __________ _________ _____ _________ ______ ____.
________ __________ _________ ________ __________ ___ _____ ________.
_______ ______ _________ ____ ____.
__________ ______ _______ _________ _______ ___ __________ _____ __________ ______ ___ __________.
_______ ______ ____ ____ ________ ________ ________ ___ _______ ____ ______.
______ _________ _________ _____ ________ _____ __________ __________ ___ ________ _________.
_____ _______ ___ __________ _______.
________ ________ ____ ______ _____ ________ ____ ________ _______ ___ ______.
______ ____.
Get Full Answer on WhatsApp