Q:1 Create a table Deposit with fields AccNo Number (8) Primary key Name Character (25) Not Null Branch Character (20) Amount Number (8, 2) Not Null Branches can be CALICUT, COCHIN, or TRICHUR. SQL> CREATE TABLE DEPOSIT( 2 ACCNO NUMBER(8) PRIMARY KEY, 3 NAME CHARACTER(25) NOT NULL, 4 BRANCH CHARACTER(20) CHECK(BRANCH IN('CALICUT','COCHIN','TRICHUR')), 5 AMOUNT NUMBER(8,2) NOT NULL); Table created. Q:2 Show the structure of the STUDENT table. SQL> DESC DEPOSIT; Name Null? Type ----------------------------------------- -------- ---------------------------- ACCNO NOT NULL NUMBER(8) NAME NOT NULL CHAR(25) BRANCH CHAR(20) AMOUNT NOT NULL NUMBER(8,2) Q:3 Insert data in all the fields. (at least 7 records) SQL> INSERT INTO DEPOSIT VALUES(&ACCNO,'&NAME','&BRANCH',&AMOUNT); Enter value for accno: 1001 Enter value for name: SYAM KUMAR Enter value for branch: CALICUT Enter value for amount: 55000 old 1: INSERT INTO DEPOSIT VALUES(&ACCNO,'&NAME','&BRANCH',&AMOUNT) new 1: INSERT INTO DEPOSIT VALUES(1001,'SYAM KUMAR','CALICUT',55000) 1 row created. SQL> / Enter value for accno: 1002 Enter value for name: RAM KUMAR Enter value for branch: COCHIN Enter value for amount: 25000 old 1: INSERT INTO DEPOSIT VALUES(&ACCNO,'&NAME','&BRANCH',&AMOUNT) new 1: INSERT INTO DEPOSIT VALUES(1002,'RAM KUMAR','COCHIN',25000) 1 row created. SQL> / Enter value for accno: 1003 Enter value for name: KRISHNA KUMAR Enter value for branch: TRICHUR Enter value for amount: 45000 old 1: INSERT INTO DEPOSIT VALUES(&ACCNO,'&NAME','&BRANCH',&AMOUNT) new 1: INSERT INTO DEPOSIT VALUES(1003,'KRISHNA KUMAR','TRICHUR',45000) 1 row created. SQL> / Enter value for accno: 1004 Enter value for name: MOHAN KUMAR Enter value for branch: COCHIN Enter value for amount: 5000 old 1: INSERT INTO DEPOSIT VALUES(&ACCNO,'&NAME','&BRANCH',&AMOUNT) new 1: INSERT INTO DEPOSIT VALUES(1004,'MOHAN KUMAR','COCHIN',5000) 1 row created. SQL> / Enter value for accno: 1005 Enter value for name: ANIL KUMAR Enter value for branch: TRICHUR Enter value for amount: 35000 old 1: INSERT INTO DEPOSIT VALUES(&ACCNO,'&NAME','&BRANCH',&AMOUNT) new 1: INSERT INTO DEPOSIT VALUES(1005,'ANIL KUMAR','TRICHUR',35000) 1 row created. SQL> / Enter value for accno: 1006 Enter value for name: DEVA KUMAR Enter value for branch: CALICUT Enter value for amount: 60000 old 1: INSERT INTO DEPOSIT VALUES(&ACCNO,'&NAME','&BRANCH',&AMOUNT) new 1: INSERT INTO DEPOSIT VALUES(1006,'DEVA KUMAR','CALICUT',60000) 1 row created. SQL> / Enter value for accno: 1007 Enter value for name: ASHOK KUMAR Enter value for branch: CALICUT Enter value for amount: 45000 old 1: INSERT INTO DEPOSIT VALUES(&ACCNO,'&NAME','&BRANCH',&AMOUNT) new 1: INSERT INTO DEPOSIT VALUES(1007,'ASHOK KUMAR','CALICUT',45000) 1 row created. Q:4 Display the details of deposit with maximum deposit. SQL> SELECT * FROM DEPOSIT WHERE AMOUNT=(SELECT MAX(AMOUNT) FROM DEPOSIT); ACCNO NAME BRANCH AMOUNT ---------- ------------------------- -------------------- ---------- 1006 DEVA KUMAR CALICUT 60000 Q:5 Display all the records from the table DEPOSIT. SQL> SELECT * FROM DEPOSIT; ACCNO NAME AMOUNT BRANCH ---------- ------------------------- ---------- -------------------- 1001 SYAM KUMAR 55000 CALICUT 1002 RAM KUMAR 25000 COCHIN 1003 KRISHNA KUMAR 45000 TRICHUR 1004 MOHAN KUMAR 5000 COCHIN 1005 ANIL KUMAR 35000 TRICHUR 1006 DEVA KUMAR 60000 CALICUT 1007 ASHOK KUMAR 45000 CALICUT 7 rows selected. Q:6 Display the accounts branch wise in ascending order of AccNo. SQL> SELECT BRANCH, ACCNO,NAME FROM DEPOSIT ORDER BY BRANCH, ACCNO; BRANCH ACCNO NAME -------------------- ---------- ------------------------- CALICUT 1001 SYAM KUMAR CALICUT 1006 DEVA KUMAR CALICUT 1007 ASHOK KUMAR COCHIN 1002 RAM KUMAR COCHIN 1004 MOHAN KUMAR TRICHUR 1003 KRISHNA KUMAR TRICHUR 1005 ANIL KUMAR 7 rows selected. Q:7 Display the AccNo and Name of depositors in the branch CALICUT. SQL> SELECT ACCNO, NAME FROM DEPOSIT WHERE BRANCH='CALICUT'; ACCNO NAME ---------- ------------------------- 1001 SYAM KUMAR 1006 DEVA KUMAR 1007 ASHOK KUMAR Q:8 Display the AccNo of depositors who have deposited more than Rs. 10,000/- and less than Rs. 50,000/-. SQL> SELECT ACCNO, NAME FROM DEPOSIT WHERE AMOUNT BETWEEN 10000 AND 50000; ACCNO NAME ---------- ------------------------- 1002 RAM KUMAR 1003 KRISHNA KUMAR 1005 ANIL KUMAR 1007 ASHOK KUMAR Q:9 Display the names and AccNo. of depositors in CALICUT branch who have deposited more than Rs. 20,000/-. SQL> SELECT ACCNO, NAME,AMOUNT FROM DEPOSIT WHERE BRANCH='CALICUT' AND AMOUNT > 20000; ACCNO NAME AMOUNT ---------- ------------------------- ---------- 1001 SYAM KUMAR 55000 1006 DEVA KUMAR 60000 1007 ASHOK KUMAR 45000 Q:10 Display the details of the Customer who had deposited exactly Rs. 10,000/-. SQL> SELECT * FROM DEPOSIT WHERE AMOUNT=10000; no rows selected Q:11 Display the details of Customers from AccNo 1001 to 1005. SQL> SELECT * FROM DEPOSIT WHERE ACCNO BETWEEN 1001 AND 1005; ACCNO NAME AMOUNT BRANCH ---------- ------------------------- ---------- -------------------- 1001 SYAM KUMAR 55000 CALICUT 1002 RAM KUMAR 25000 COCHIN 1003 KRISHNA KUMAR 45000 TRICHUR 1004 MOHAN KUMAR 5000 COCHIN 1005 ANIL KUMAR 35000 TRICHUR Q:12 Display the name of customers with maximum deposit in each branch. SQL> SELECT NAME, BRANCH, AMOUNT FROM DEPOSIT WHERE AMOUNT IN ( 2 SELECT MAX(AMOUNT) FROM DEPOSIT GROUP BY BRANCH); NAME BRANCH AMOUNT ------------------------- -------------------- ---------- RAM KUMAR COCHIN 25000 KRISHNA KUMAR TRICHUR 45000 DEVA KUMAR CALICUT 60000 Q:13 Display the number of depositors in each branch who have more than Rs. 50,000/- as deposit. SQL> SELECT BRANCH, COUNT(*) FROM DEPOSIT WHERE AMOUNT > 50000 GROUP BY BRANCH; BRANCH COUNT(*) -------------------- ---------- CALICUT 2 Q:14 Display the total deposit of all branches. SQL> SELECT SUM(AMOUNT) FROM DEPOSIT; SUM(AMOUNT) ----------- 270000 Q:15 Update the amount of depositor with ac no 1003 as 65000. SQL> UPDATE DEPOSIT SET AMOUNT=65000 WHERE ACCNO=1003; 1 row updated. Q:16 Display the total deposit of each branches in the order of branch. SQL> SELECT BRANCH,SUM(AMOUNT) FROM DEPOSIT GROUP BY BRANCH ORDER BY BRANCH; BRANCH SUM(AMOUNT) -------------------- ----------- CALICUT 160000 COCHIN 30000 TRICHUR 80000