Share
Notifications

View all notifications

Write Sql Queries for (I) to (Iv) and Find Outputs for Sql Queries (V) to (Viii) Which Are Based on the Table. to Display Details of All Transaction Of Type Depos1t From Table Transact. and to Display All Ano, Aname and Dot of Those Persons from Tables Account and Transact Who Have Done Transactions Less than Or Equal to 3000? Select Count(*), Sum(Amount) from Transact Where Dot <= '2017-06-01' - Computer Science (Python)

Login
Create free account


      Forgot password?
ConceptStructured Query Language Advantages of Using SQL

Question

Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii) which are based on the table.

TABLE: Account

ANO ANAME ADDRESS
101 Nirja Singh Bangalore
102 Rohan Gupta Chennai
103 Ali Reza Hyderabad
104 Rishabh Jain Chennai
105 Simran Kaur Chandigarh

TABLE: TRANSACT

TRNO ANO AMOUNT TYPE DOT
T001  101 2500 Withdraw 2017-12-21
T002 103 3000 Deposit 2017-06-01
T003  102 2000 Withdraw 2017-0S-12
T004  103 1000 Deposit 2017-10-22
T005  101 12000 Deposit 2017-11-06

1) To display details of all transaction of TYPE Depos1t from Table TRANSACT.

2) To display the ANO and AMOUNT of all Depos1ts and Withdrawals done in the month of October 2017 from table TRANSACT.

3) To display the last date of the transaction (DOT) from the table TRANSACT for the Accounts having ANO as 103

4) To display all ANO, ANAME and DOT of those persons from tables ACCOUNT and TRANSACT who have done transactions less than or equal to 3000?

5) SELECT ANO, ANAME FROM ACCOUNT WHERE ADDRESS NOT IN ('CHENNAI', 'BANGALORE');

6) SELECT DISTINCT ANO FROM TRANSACT

7) SELECT ANO, COUNT(*), MIN(AMOUNT) FROM TRANSACT GROUP BY AND HAVING COUNT(*) > 1;

8) SELECT COUNT(*), SUM(AMOUNT) FROM TRANSACT WHERE DOT <= '2017-06-01'

Solution

1) SELECT * FROM TRANSACT WHERE TYPE = ‘Deposit’

2) SELECT ANO, AMOUNT FROM TRANSACT WHERE MONTH(DOT) = 10;

3) SELECT DOT FROM TRANSACT WHERE ANO = 103  ORDER BY DOT DESC LIMIT = 1

4) Select A.ANAME, T.ANO, T.DOT FROM ACCOUNT as A

JOIN TRANSACT as T ON A.ANO = T.ANO WHERE T.AMOUNT < = 3000;

5) 

ANO ANAME
103 Ali Reza
105 Simran Kaur

6) 

ANO
101
103
102

7) 

ANO COUNT MIN(AMOUNT)
101 2 2500
103 2 1000

8) 

Count Sum(AMOUNT)
2 5000
  Is there an error in this question or solution?

APPEARS IN

Video TutorialsVIEW ALL [1]

Solution Write Sql Queries for (I) to (Iv) and Find Outputs for Sql Queries (V) to (Viii) Which Are Based on the Table. to Display Details of All Transaction Of Type Depos1t From Table Transact. and to Display All Ano, Aname and Dot of Those Persons from Tables Account and Transact Who Have Done Transactions Less than Or Equal to 3000? Select Count(*), Sum(Amount) from Transact Where Dot <= '2017-06-01' Concept: Structured Query Language Advantages of Using SQL.
S
View in app×