Scribd
Upload
25 views

DBMS Practical 1 Syntax

The document contains the SQL commands to create three database tables (ACCOUNT, LOAN, INSTALLMENT) with sample data inserted. It then provides 23 SQL queries against these tables including selects, updates, alters, creates, drops, etc. to retrieve, modify and test the data.

Uploaded by

Temporary User
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
25 views

DBMS Practical 1 Syntax

The document contains the SQL commands to create three database tables (ACCOUNT, LOAN, INSTALLMENT) with sample data inserted. It then provides 23 SQL queries against these tables including selects, updates, alters, creates, drops, etc. to retrieve, modify and test the data.

Uploaded by

Temporary User
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

Click here

Create table ACCOUNT (acc_no varchar2(5),Name varchar2(30),City varchar2(20),Balance


Number(10,2),loan_taken varchar2(5));
Insert into ACCOUNT values ('A001','Patel Jigar','Mehsana',50000,'YES');
Insert into ACCOUNT values ('A002','Patel Ramesh','Mehsana',50000,'YES');
Insert into ACCOUNT values ('A003','Dave Hardik','Ahmedabad',75000,'NO');
Insert into ACCOUNT values ('A004','Soni Hetal','Ahmedabad',100000,'NO');
Insert into ACCOUNT values ('A005','Sony Atul','Vadodara',100000,'YES');
Select * from ACCOUNT;

Create table LOAN (loan_no varchar2(5),acc_no varchar2(5),loan_amt


Number(10,2),interest_rate Number(5,2),loan_date Date,remaining_loan Number(10,2));
Insert into LOAN values ('L001','A001',100000,7,'1-jan-04',75000);
Insert into LOAN values ('L002','A002',300000,9,'18-may-04',150000);
Insert into LOAN values ('L003','A003',500000,11,'15-june-04',300000);
Select * from LOAN;

Create table INSTALLMENT (loan_no varchar2(5),Inst_no varchar2(5),lnst_date Date,Amount


Number(10,2));
Insert into INSTALLMENT values ('L001','I001','2-Feb-04',15000);
Insert into INSTALLMENT values ('L002','I002','18-june-04',20000);
Insert into INSTALLMENT values ('L003','I003','15-july-04',20000);
Select * from INSTALLMENT;

Q-1
Select * from INSTALLMENT;
Q-2
Select Amount from INSTALLMENT;
Q-3
Select acc_no,name from ACCOUNT where acc_no='A001';
Q-4
Select * from LOAN where loan_no='L001';
Q-5
Desc LOAN
Desc ACCOUNT
Desc INSTALLMENT
Q-6
Update ACCOUNT set Name='Patel Hiren' where Name='Patel Jigar';
Select * from ACCOUNT;
Q-7
Update ACCOUNT set Name='Kothari Nehal',City='Kherva' where acc_no='A005';
Select * from ACCOUNT;
Q-8
Select * from ACCOUNT where loan_taken='YES';
Q-9
Alter table ACCOUNT add adderess varchar2(20);
Select * from ACCOUNT;
Q-10
Alter table LOAN add credit_no varchar2(4);
Select * from LOAN;
Q-11
Create table ACCOUNT_TEMP as Select acc_no,Name,Balance from ACCOUNT;
Select * from ACCOUNT_TEMP;
Q-12
Create table LOAN_TEMP as Select loan_no,Acc_no,loan_amt,loan_date from LOAN;
Select * from LOAN;
Q-13
Create table TRANS_TEMP (account_no) as select acc_no from LOAN_TEMP;
Select * from TRANS_TEMP;
Q-14
Alter table LOAN modify acc_no varchar2(7);
Desc LOAN;
Q-15
Delete from ACCOUNT where acc_no='A004';
Select * from ACCOUNT;
Q-16
Update LOAN set interest_rate= interest_rate+2;
Select * from LOAN;
Q-17
Select * from LOAN where loan_date='01-JAN-04';
Q-18
Update INSTALLMENT set Lnst_Date='03-MAR-05' where Inst_Date='2-Feb-04'; //////
Select * from INSTALLMENT;
Q-19
Select loan_amt*2 from LOAN;
Q-20
Update LOAN set loan_amt=150000 where loan_no='L001';
Select * from LOAN;
Q-21
select loan_no,amount from INSTALLMENT order by Inst_Date desc;
Q-22
Select * from ACCOUNT order by acc_no desc;
Q-23
Drop table LOAN_TEMP;

You might also like