Question

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.

13 Sep 2024
Answer :
Word Count : 675

(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

IGNOU NEWS
Assignment Submission Last Date Extended Till 30 June 2026 Click Here★★★IGNOU June 2026 TEE Date Sheet Released Click Here★★★
Top
📞
Call Support Instant phone assistance Consider the following relational database:Customer (custId, custN
→
🟢
WhatsApp Chat Fast live messaging
→
Email Us Business enquiries & support
→