anthonyog888
Answered

TABLE: ACCOUNT
\begin{tabular}{|l|l|l|}
\hline
ANO & ANAME & ADDRESS \\
\hline
101 & Nirja Singh & Bangalore \\
\hline
102 & Rohan Gupta & Chennai \\
\hline
103 & Ali Reza & Hyderabad \\
\hline
104 & Rishabh Jain & Chennai \\
\hline
105 & Simran Kaur & Chandigarh \\
\hline
\end{tabular}

TABLE: TRANSACT
\begin{tabular}{|l|l|l|l|l|}
\hline
TRNO & ANO & AMOUNT & TYPE & DOT \\
\hline
T001 & 101 & 2500 & Withdraw & [tex]$2017-12-21$[/tex] \\
\hline
T002 & 103 & 3000 & Deposit & [tex]$2017-06-01$[/tex] \\
\hline
T003 & 102 & 2000 & Withdraw & [tex]$2017-05-12$[/tex] \\
\hline
T004 & 103 & 1000 & Deposit & [tex]$2017-10-22$[/tex] \\
\hline
T005 & 102 & 12000 & Deposit & [tex]$2017-11-06$[/tex] \\
\hline
\end{tabular}

(i) Display details of all transactions of TYPE 'Withdraw' from the TRANSACT table.

(ii) Display ANO and AMOUNT of all deposits and withdrawals done in the month of 'May' 2017 from the TRANSACT table.

(iii) Display the first date of transaction (DOT) from the TRANSACT table for the account having ANO as 102.

(iv) Display ANO, ANAME, AMOUNT, and DOT of those persons from the ACCOUNT and TRANSACT tables who have done transactions less than or equal to 3000.



Answer :

GinnyAnswer
Let's address each part of the problem step-by-step:

### (i) To display details of all transactions of TYPE Withdraw from TRANSACT table

We need to filter the TRANSACT table to only include transactions where the TYPE is "Withdraw". From the table, the transactions that match this criterion are:

- T001 (ANO: 101, AMOUNT: 2500, TYPE: Withdraw, DOT: 2017-12-21)
- T003 (ANO: 102, AMOUNT: 2000, TYPE: Withdraw, DOT: 2017-05-12)

So, the result is:
[tex]\[ \begin{array}{|c|c|c|c|c|} \hline TRNO & ANO & AMOUNT & TYPE & DOT \\ \hline T001 & 101 & 2500 & Withdraw & 2017-12-21 \\ T003 & 102 & 2000 & Withdraw & 2017-05-12 \\ \hline \end{array} \][/tex]

### (ii) To display ANO and AMOUNT of all Deposit and Withdrawals done in month of 'May' 2017 from table TRANSACT

We need to filter the TRANSACT table to include transactions where the date (DOT) is in May 2017, i.e., "2017-05". From the table, the transactions that match this criterion are:

- T003 (ANO: 102, AMOUNT: 2000, TYPE: Withdraw, DOT: 2017-05-12)

So, the result is:
[tex]\[ \begin{array}{|c|c|} \hline ANO & AMOUNT \\ \hline 102 & 2000 \\ \hline \end{array} \][/tex]

### (iii) To display first date of transaction (DOT) from table TRANSACT for Account having ANO as 102

We need to find the first date of transaction for the account with ANO 102. From the TRANSACT table, the transactions for ANO 102 are:

- T003 (DOT: 2017-05-12)
- T005 (DOT: 2017-11-06)

The earliest date among these is 2017-05-12.

So, the result is:
[tex]\[ 2017-05-12 \][/tex]

### (iv) To display ANO, ANAME, AMOUNT and DOT of those persons from ACCOUNT and TRANSACT table who have done transactions less than or equal to 3000

We need to merge the TRANSACT and ACCOUNT tables, and filter the transactions for amounts less than or equal to 3000. From the TRANSACT table, the transactions that match this criterion are:

- T001 (ANO: 101, AMOUNT: 2500, DOT: 2017-12-21)
- T002 (ANO: 103, AMOUNT: 3000, DOT: 2017-06-01)
- T003 (ANO: 102, AMOUNT: 2000, DOT: 2017-05-12)
- T004 (ANO: 103, AMOUNT: 1000, DOT: 2017-10-22)

From the ACCOUNT table, we map the ANO to ANAME:
- ANO 101 = Nirja Singh
- ANO 102 = Rohan Gupta
- ANO 103 = Ali Reza

So, combining these we get:
[tex]\[ \begin{array}{|c|c|c|c|} \hline ANO & ANAME & AMOUNT & DOT \\ \hline 101 & Nirja Singh & 2500 & 2017-12-21 \\ 103 & Ali Reza & 3000 & 2017-06-01 \\ 102 & Rohan Gupta & 2000 & 2017-05-12 \\ 103 & Ali Reza & 1000 & 2017-10-22 \\ \hline \end{array} \][/tex]

These are the detailed steps and the respective solutions for each question based on the given tables.