In a Bank’s database, there are two tables ‘Customer’ and ‘Tra

1.	In a Bank’s database, there are two tables ‘Customer’ and ‘Transaction’ as shown below.CustomerAcc_NoCust_NameCust_CityCust_PhoneOpen_Bal2101001SunitaAmbala9710557614100002201002SandhyaPatna8223545233150002301003VivekNew Delhi9972136576130002401004MeenaNew Delhi932130545310000TransactionTrans_IdAcc_NoTransaction_TypeAmountTr0012301003Credit15000Tr0022201002Credit20000Tr0032101001Debit3500Tr0042301003Credit26000Tr0052301003Credit24000With reference to the above given tables, attempt the questions given below:(i) Write a query to display customer’s name who has withdrawn the money.(ii) Write a query to display customer’s name along with their transaction details.(iii) Write a query to display customer’s name who have not done any transaction yet.(iv) How many rows and column will be there in the Cartesian product of the above given tables. Also mention the degree and cardinality of the Cartesian product of the above given tables.(v) Select Acc_No, sum(Amount) from Customer c, Transaction t where c.Acc_No=t.Acc_No group by c.Acc_No having Transaction_Type="Credit";(vi) Discuss the significance of having clause with group by statement with suitable example.
Answer» (i) Select cust_name from customer c,transaction t where c.Acc_No=t.Acc_No and Transaction_Type= "Debit"; (ii) Select cust_name, t.* from customer c, transaction t where c.Acc_No=t.Acc_No; (iii) Select cust_name from customer c,transaction t where c.Acc_No!=t.Acc_No; (iv) Cartesian Product: Number of Rows: 20 Number of Columns: 9 Degree: 9 Cardinality: 20 (v) 2301003 65000 2201002 20000 (vi) Sometimes we do not want to see the whole output produced by a statement with Group By clause. We want to see the output only for those groups which satisfy some condition. It means we want to put some condition on individual groups (and not on individual records). A condition on groups is applied by Having clause. For example consider the following query: select Acc_No, sum(Amount) from Customer c, Transaction t where c.Acc_No=t.Acc_No group by c.Acc_No having Transaction_Type="Credit"; This query will create account number wise groups and instead of displaying the total amount of all type of transactions, it will only display the total of credit transactions only.

In a Bank’s database, there are two tables ‘Customer’ and ‘Transaction’ as shown below.CustomerAcc_NoCust_NameCust_CityCust_PhoneOpen_Bal2101001SunitaAmbala9710557614100002201002SandhyaPatna8223545233150002301003VivekNew Delhi9972136576130002401004MeenaNew Delhi932130545310000TransactionTrans_IdAcc_NoTransaction_TypeAmountTr0012301003Credit15000Tr0022201002Credit20000Tr0032101001Debit3500Tr0042301003Credit26000Tr0052301003Credit24000With reference to the above given tables, attempt the questions given below:(i) Write a query to display customer’s name who has withdrawn the money.(ii) Write a query to display customer’s name along with their transaction details.(iii) Write a query to display customer’s name who have not done any transaction yet.(iv) How many rows and column will be there in the Cartesian product of the above given tables. Also mention the degree and cardinality of the Cartesian product of the above given tables.(v) Select Acc_No, sum(Amount) from Customer c, Transaction t where c.Acc_No=t.Acc_No group by c.Acc_No having Transaction_Type="Credit";(vi) Discuss the significance of having clause with group by statement with suitable example.

Answer»

(i) Select cust_name from customer c,transaction t where c.Acc_No=t.Acc_No and Transaction_Type= "Debit";

(ii) Select cust_name, t.* from customer c, transaction t where c.Acc_No=t.Acc_No;

(iii) Select cust_name from customer c,transaction t where c.Acc_No!=t.Acc_No;

(iv) Cartesian Product:

Number of Rows: 20

Number of Columns: 9

Degree: 9

Cardinality: 20

(v) 2301003 65000

2201002 20000

(vi) Sometimes we do not want to see the whole output produced by a statement with Group By clause. We want to see the output only for those groups which satisfy some condition. It means we want to put some condition on individual groups (and not on individual records). A condition on groups is applied by Having clause.

For example consider the following query:

select Acc_No, sum(Amount) from Customer c, Transaction t where c.Acc_No=t.Acc_No group by c.Acc_No having Transaction_Type="Credit";

This query will create account number wise groups and instead of displaying the total amount of all type of transactions, it will only display the total of credit transactions only.

Discussion

No Comment Found

Related InterviewSolutions

Reply to Comment