SQL Statements for MySQL Database Operations
There exists a table named CUSTOMERS & DEPOSITS in the
database BANK
Write My SQL statements for the following :-
Display all databases in the computer.
SHOW DATABASES;
Display the tables in bank database.
USE BANK;
SHOW TABLES;
Display the structure of tables customers & deposits.
DESCRIBE CUSTOMERS;
DESCRIBE DEPOSITS;
Display the contents of tables CUTOMERS & DEPOSITS .
SELECT * FROM CUSTOMERS;
SELECT * FROM DEPOSITS;
Display the customer id & minimum amount maintained by the
customers from deposits table.
SELECT CUST_ID, MIN(AMOUNT)
FROM DEPOSITS
GROUP BY CUST_ID ;
Display the customer id & maximum amount maintained by the
customers from deposits table.
SELECT CUST_ID, MAX(AMOUNT)
FROM DEPOSITS
GROUP BY CUST_ID ;
Display the customer id & average amount maintained by the
customers from deposits table.
SELECT CUST_ID, AVG(AMOUNT)
FROM DEPOSITS
GROUP BY CUST_ID ;
Display the cust_id & average amount of the customers
whose amount is greater than Rs.15000 from deposits
table.
SELECT CUST_ID, AVG(AMOUNT)
FROM DEPOSITS
WHERE AMOUNT>15000
GROUP BY CUST_ID ;
Display the cust_id & average amount maintained by the
customers whose average amount is greater than Rs.20000
from deposits table.
SELECT CUST_ID, AVG(AMOUNT)
FROM DEPOSITS
GROUP BY CUST_ID
HAVING AVG(AMOUNT)>20000;
Display the cust_id & amount of customers who are maintaining
more than the average of all customers from deposits table.
SELECT CUST_ID, AMOUNT
FROM DEPOSITS
WHERE AMOUNT> ( SELECT AVG(AMOUNT)
FROM DEPOSITS) ;
Display the cust_id & amount of all customers which is same
amount as that of account no. 450 from deposits table.
SELECT CUST_ID, AMOUNT
FROM DEPOSITS
WHERE AMOUNT = ( SELECT AMOUNT
FROM DEPOSITS
WHERE AC_NO =450) ;
Display the id & amount of all customers who have
amount lesser than that of account no. 500 from
deposits table.
SELECT CUST_ID, AMOUNT
FROM DEPOSITS
WHERE AMOUNT < ( SELECT AMOUNT
FROM DEPOSITS
WHERE AC_NO =500) ;
Display the name, amount deposited for all customers from
customers table and deposits table
SELECT CUSTOMERS.CUST_NAME, DEPOSITS.AMOUNT
FROM CUSTOMERS , DEPOSITS
WHERE CUSTOMERS.CUST_ID=DEPOSITS.CUST_ID;
Display the name , amount deposited for all customers in
alphabetical order of names from customers table and
deposits table
SELECT CUSTOMERS.CUST_NAME, DEPOSITS.AMOUNT
FROM CUSTOMERS, DEPOSITS
WHERE CUSTOMERS.CUST_ID=DEPOSITS.CUST_ID
ORDER BY CUSTOMERS.CUST_NAME;
Display the gender & average amount grouped according
to gender from customers table and deposits table
SELECT CUSTOMERS.GENDER, AVG(DEPOSITS.AMOUNT)
FROM CUSTOMERS , DEPOSITS
WHERE CUSTOMERS.CUST_ID=DEPOSITS.CUST_ID
GROUP BY CUSTOMERS.GENDER;
Display the customer name & average amount maintained by the
customers from customers table and deposits table
SELECT CUSTOMERS.CUST_NAME, AVG(DEPOSITS.AMOUNT)
FROM CUSTOMERS, DEPOSITS
WHERE CUSTOMERS.CUST_ID=DEPOSITS.CUST_ID
GROUP BY CUSOMERS.CUST_ID;
Display the name and amount deposited for all customers whose
amount is more than the average amount from customers table
and deposits table .
SELECT CUSTOMERS.CUST_NAME, DEPOSITS.AMOUNT
FROM CUSTOMERS, DEPOSITS
WHERE CUSTOMERS.CUST_ID=DEPOSITS.CUST_ID
AND DEPOSITS.AMOUNT > ( SELECT AVG(AMOUNT)
FROM DEPOSITS);
Display the name , amount deposited for all customers
whose amount is less than amount of ac_no 550 from
customers table and deposits table
SELECT CUSTOMERS.CUST_NAME, DEPOSITS.AMOUNT
FROM CUSTOMERS, DEPOSITS
WHERE CUSTOMERS.CUST_ID=DEPOSITS.CUST_ID
AND DEPOSITS.AMOUNT < ( SELECT AMOUNT
FROM DEPOSITS
WHERE AC_NO= 550);
This content provides SQL statements for various operations on the CUSTOMERS and DEPOSITS tables in the BANK database, including displaying databases, tables, structures, contents, and performing queries to find minimum, maximum, and average amounts maintained by customers.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
There exists a table named CUSTOMERS & DEPOSITS in the database BANK Write My SQL statements for the following :- Display all databases in the computer. SHOW DATABASES; Display the tables in bank database. USE BANK; SHOW TABLES; Display the structure of tables customers & deposits. DESCRIBE CUSTOMERS; DESCRIBE DEPOSITS; Display the contents of tables CUTOMERS & DEPOSITS . SELECT * FROM CUSTOMERS; SELECT * FROM DEPOSITS;
Display the customer id & minimum amount maintained by the customers from deposits table. SELECT CUST_ID, MIN(AMOUNT) FROM DEPOSITS GROUP BY CUST_ID ; Display the customer id & maximum amount maintained by the customers from deposits table. SELECT CUST_ID, MAX(AMOUNT) FROM DEPOSITS GROUP BY CUST_ID ; Display the customer id & average amount maintained by the customers from deposits table. SELECT CUST_ID, AVG(AMOUNT) FROM DEPOSITS GROUP BY CUST_ID ;
Display the cust_id & average amount of the customers whose amount is greater than Rs.15000 from deposits table. SELECT CUST_ID, AVG(AMOUNT) FROM DEPOSITS WHERE AMOUNT>15000 GROUP BY CUST_ID ; Display the cust_id & average amount maintained by the customers whose average amount is greater than Rs.20000 from deposits table. SELECT CUST_ID, AVG(AMOUNT) FROM DEPOSITS GROUP BY CUST_ID HAVING AVG(AMOUNT)>20000;
Display the cust_id & amount of customers who are maintaining more than the average of all customers from deposits table. SELECT CUST_ID, AMOUNT FROM DEPOSITS WHERE AMOUNT> ( SELECT AVG(AMOUNT) FROM DEPOSITS) ; Display the cust_id & amount of all customers which is same amount as that of account no. 450 from deposits table. SELECT CUST_ID, AMOUNT FROM DEPOSITS WHERE AMOUNT = ( SELECT AMOUNT FROM DEPOSITS WHERE AC_NO =450) ;
Display the id & amount of all customers who have amount lesser than that of account no. 500 from deposits table. SELECT CUST_ID, AMOUNT FROM DEPOSITS WHERE AMOUNT < ( SELECT AMOUNT FROM DEPOSITS WHERE AC_NO =500) ; Display the name, amount deposited for all customers from customers table and deposits table SELECT CUSTOMERS.CUST_NAME, DEPOSITS.AMOUNT FROM CUSTOMERS , DEPOSITS WHERE CUSTOMERS.CUST_ID=DEPOSITS.CUST_ID;
Display the name , amount deposited for all customers in alphabetical order of names from customers table and deposits table SELECT CUSTOMERS.CUST_NAME, DEPOSITS.AMOUNT FROM CUSTOMERS, DEPOSITS WHERE CUSTOMERS.CUST_ID=DEPOSITS.CUST_ID ORDER BY CUSTOMERS.CUST_NAME; Display the gender & average amount grouped according to gender from customers table and deposits table SELECT CUSTOMERS.GENDER, AVG(DEPOSITS.AMOUNT) FROM CUSTOMERS , DEPOSITS WHERE CUSTOMERS.CUST_ID=DEPOSITS.CUST_ID GROUP BY CUSTOMERS.GENDER;
Display the customer name & average amount maintained by the customers from customers table and deposits table SELECT CUSTOMERS.CUST_NAME, AVG(DEPOSITS.AMOUNT) FROM CUSTOMERS, DEPOSITS WHERE CUSTOMERS.CUST_ID=DEPOSITS.CUST_ID GROUP BY CUSOMERS.CUST_ID; Display the name and amount deposited for all customers whose amount is more than the average amount from customers table and deposits table . SELECT CUSTOMERS.CUST_NAME, DEPOSITS.AMOUNT FROM CUSTOMERS, DEPOSITS WHERE CUSTOMERS.CUST_ID=DEPOSITS.CUST_ID AND DEPOSITS.AMOUNT > ( SELECT AVG(AMOUNT) FROM DEPOSITS);
Display the name , amount deposited for all customers whose amount is less than amount of ac_no 550 from customers table and deposits table SELECT CUSTOMERS.CUST_NAME, DEPOSITS.AMOUNT FROM CUSTOMERS, DEPOSITS WHERE CUSTOMERS.CUST_ID=DEPOSITS.CUST_ID AND DEPOSITS.AMOUNT < ( SELECT AMOUNT FROM DEPOSITS WHERE AC_NO= 550);
