Scribd Logo
Upload

Welcome to Scribd!

  • 112 views

    Dbms Practical 2

    Uploaded by

    Safik Sherasiya
    This document provides SQL queries and solutions for practical problems involving database tables. It inserts sample data into tables for ACCOUNTS, LOANS, INSTALLMENTS, and TRANSACTIONS. It then provides queries to update, select, alter and manipulate data within the tables. The queries demonstrate common SQL operations like modifying records, joining tables, ordering results, and changing table structures. The document is intended as a guide for students learning SQL through practical examples and solutions.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Dbms Practical 2

    Uploaded by

    Safik Sherasiya
    112 views14 pages
    This document provides SQL queries and solutions for practical problems involving database tables. It inserts sample data into tables for ACCOUNTS, LOANS, INSTALLMENTS, and TRANSACTIONS. It then provides queries to update, select, alter and manipulate data within the tables. The queries demonstrate common SQL operations like modifying records, joining tables, ordering results, and changing table structures. The document is intended as a guide for students learning SQL through practical examples and solutions.

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document provides SQL queries and solutions for practical problems involving database tables. It inserts sample data into tables for ACCOUNTS, LOANS, INSTALLMENTS, and TRANSACTIONS. It then provides queries to update, select, alter and manipulate data within the tables. The queries demonstrate common SQL operations like modifying records, joining tables, ordering results, and changing table structures. The document is intended as a guide for students learning SQL through practical examples and solutions.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    112 views14 pages

    Dbms Practical 2

    Uploaded by

    Safik Sherasiya
    This document provides SQL queries and solutions for practical problems involving database tables. It inserts sample data into tables for ACCOUNTS, LOANS, INSTALLMENTS, and TRANSACTIONS. It then provides queries to update, select, alter and manipulate data within the tables. The queries demonstrate common SQL operations like modifying records, joining tables, ordering results, and changing table structures. The document is intended as a guide for students learning SQL through practical examples and solutions.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 14

    DBMS Practicals With Solution

    By Bhavik Thummar

    From GEC, Modasa 3rd Sem I.T


    Email : [email protected]
    Facebook : https://www.facebook.com/Bhavik.Thummar.97
    Twitter : https://twitter.com/ThummarBhavik
    Google Plus : https://plus.google.com/+BhavikThummar9797
    Our Partner Sites :
    To Download Games, Softwares for Pc & Android,
    GTU Ebooks, Safari Magazine

    http://softonicks.blogspot.in/

    To Download Study Materials For I.T Engineering &


    Computer Engineering

    http://bhavikthummar.weebly.com/

    Happy To Help You !


    Practical – 2
    (SQL)
    O

    Table : ACCOUNT

    1. Insert the following records


    Acc_no Name City Balance Loan_taken
    A001 Patel Jigar Mehsana 50000 YES
    A002 Patel Ramesh Mehsana 50000 Yes
    A003 Dave Hardik Ahmedabad 75000 NO
    A004 Soni Hetal Ahmedabad 100000 NO
    A005 Soni Atul Vadodara 100000 YES

    Query : (1). insert into ACCOUNT values (‘A001’,


    ’Patel Jigar’,’Mehsana’,50000,’YES’);
    (2). insert into ACCOUNT values (‘A002’,
    ’Patel Ramesh’,’Mehsana’,50000,’YES’);
    (3). insert into ACCOUNT values (‘A003’,
    ’Dave Hardik,’Ahmedabad’,75000,’NO’);
    (4). insert into ACCOUNT values (‘A004’,
    ’Soni Hetal’,’ Ahmedabad’,100000,’NO’);
    (5). insert into ACCOUNT values (‘A005’,
    ’Soni Atul’,’Vadodara’,100000,’YES’);
    2. Change the name ‘Patel Jigar’ to ‘Patel Hiren’.
    Query : update ACCOUNT set name=’Patel Hiren’
    where acc_no=’A001’;
    3. Change the name and city where account number is A005.
    (new name = ‘Kothari Nehal’ and new city = ‘Patan’).

    Query : update ACCOUNT set name=’Kothari Nehal’,


    city=’Patan’ where acc_no=’A005’;

    4. Display only those records where loan taken status is ‘YES’.

    Query : select * from ACCOUNT where loan_taken=’YES’;

    5. Add the new column (address varchar2 (20)) into table


    ACCOUNT.

    Query : alter table ACCOUNT add address varchar2(20);

    6. Create another table ACCOUNT_TEMP (acc_no, name,


    balance) from table ACCOUNT.

    Query : create table ACCOUNT_TEMP as select


    acc_no,name,balance from ACCOUNT;

    7. Rename the table ACCOUNT to ACCOUNT_MASTER.

    Query : rename ACCOUNT to ACCOUNT_MASTER;


    8. Update the column balance for all the account holders.
    (Multiply the balance by 2 for each account holders)

    Query : update ACCOUNT set balance=balance*2;

    9. Describe the structure of table ACCOUNT

    Query : desc ACCOUNT;

    10. Delete the records whose account no is A004

    Query : delete from ACCOUNT where acc_no=’A004’;


    Table : LOAN
    1. Insert the following Records

    Loan_no Acc_no Loan_amt Interest_rate Loan_date Remaining_loan


    L001 A001 100000 7 1-jan-04 75000
    L002 A002 300000 9 18-may-04 150000
    L003 A005 500000 11 15-june-04 300000

    Query : (1). insert into LOAN values (‘L001’,


    ’A001’,100000,7,’01-JAN-04’,75000);
    (2). insert into LOAN values (‘L002’,
    ’A002’,300000,9,’18-JAN-04’,150000);
    (3). insert into LOAN values (‘L003’,
    ’A003’,500000,11,’15-JUN-04’,300000);

    2. For each loan holders Add 100000 Rs. Amount into the
    column loan_amt.
    Query : update LOAN set loan_amt=loan_amt+100000;

    3. For each loan holders Increase the interest rate 2%


    Query : update LOAN set interest_rate= interest_rate+2;
    4. Create another table LOAN_TEMP (loan_no, Acc_no,
    loan_amt, loan_date) from The table LOAN.
    Query : create table LOAN_TEMP as select loan_no,Acc_no,
    loan_amt,loan_date from The table LOAN

    5. Display only those records where loan holder takena loan in


    month of January.
    Query : select * from LOAN where loan_date=’01-JAN-04’;

    6. Modify the structure of table LOAN by adding one column


    credit_no varchar2 (4).
    Query : alter table LOAN add credit_no varchar2(4);

    7. Display the Loan amount*2 of table LOAN.


    Query : select loan_amt*2 from LOAN;

    8. Display the records of table LOAN by date wise in ascending


    order.
    Query : select * from LOAN order by loan_date;
    9. Display the records of table LOAN by account number wise in
    descending Order.
    Query : select * from LOAN order by acc_no desc;

    10. Increase the size 5 to 7 of column acc_no.


    Query : alter table LOAN modify acc_no varchar2(7);
    Table : INSTALLMENT
    1. Insert the following Records
    Loan_no Inst_no Inst_Date Amount
    L001 I001 2-Feb-04 15000
    L002 I002 18-June-04 20000
    L003 I003 15-July-04 20000

    Query : (1). insert into INSTALLMENT values (‘L001’,


    ’I001’,’02-FEB-04’,15000);
    (2). insert into INSTALLMENT values (‘L002’,
    ’I002’,’18-JUN-04’,20000);
    (3). insert into INSTALLMENT values (‘L003’,
    ’I003’,’15-JUL-04’,20000);

    2. Change the Inst_Date ‘2-Feb-04’ to ’3-Mar-04’.


    Query : update INSTALLMENT set inst_date=’03-MAR-04’ where
    loan_no=’I001’;

    3. Reduce 5000 amount from all Installment holders.


    Query : update INSTALLMENT set amount=amount-5000;
    4. Add the amount 5000 where loan no is ‘L003’ and ‘L002’.
    Query : update INSTALLMENT set amount=amount+5000
    where loan_no=’L003’ or loan_no=’L002’;

    5. Change the column size of 5 to 7 where column name is


    Loan_no.
    Query : alter table INSTALLMENT modify loan_no varchar2(7);

    6. Decrease the column size 5 to 4 where column name Inst_no.


    Query : alter table INSTALLMENT modify inst_no varchar2(4);

    7. Show the structure of the Table.


    Query : desc INSTALLMENT;

    8. Change the amount 15000 to 5000 where loan number is L001


    Query : update INSTALLMENT set amount=5000
    where loan_no=’L001’;
    9. Perform delete operation. (Delete only particular one record)
    Query : delete from INSTALLMENT where loan_no=’L001’;

    Table : TRANSACTION
    1. Insert the following Records
    Acc_no Trans_Date Amt Type_of_tr Mode_of_pay
    A001 3-may-04 10000 D Cash
    A002 5-july-04 5000 W Check
    A003 12-Aug-04 25000 D Check
    A004 15-may-04 30000 D Check
    A005 22-oct-04 15000 W Cash

    Query : (1). insert into TRANSACTION values (‘A001’,


    ’03-MAY-04’,10000,’D’,’Cash’);
    (2). insert into TRANSACTION values (‘A002’,
    ’05-JUL-04’,5000,’W’,’Check’);
    (3). insert into TRANSACTION values (‘A003’,
    ’12-AUG-04’,25000,’D’,’ Check’);
    (4). insert into TRANSACTION values (‘A004’,
    ’15-MAY-04’,30000,’D’,’ Check’);
    (5). insert into TRANSACTION values (‘A005’,
    ’12-OCT-04’,15000,’W’,’Cash’);
    2. Insert any duplicate value and display all the records without
    any duplicate rows.
    Query : (1). insert into TRANSACTION values (‘A001’,
    ’09-MAY-04’,10000,’D’,’Cash’);
    (2). Select distinct* from TRANSACTION;

    3. Select all the records in descending order(account number


    wise).
    Query : Select * from TRANSACTION order by acc_no desc;

    4. Display amt, date, and type of transaction by date wise.


    Query : Select amt,trans_date,typr_of_tr from TRANSACTION
    order by trans_date;

    5. Create another table TRANSACTION_TEMP from this table.


    Query : create table TRANSACTION_TEMP as
    select TRANSACTION
    6. Create a another table TRANS_TEMP by change the column
    name acc_no to account_no.
    Query : create table trans_temp (account_no,trans_date,amt,
    type_of_tr,mode_of_pay) as select acc_no,trans_date,amt,
    type_of_tr,mode_of_pay from transaction;

    7. Delete a table TRANSACTION_TEMP.


    Query : drop table TRANSACTION;

    8. Rename the table TRANSACTION to TRANS.


    Query : rename TRANSACTION to TRANS;

    9. Only create a structure of table transaction1 from table


    transaction.
    Query : create table transaction1 as select * from transaction;

    10. Display account number where type of transaction is ‘D’.


    Query : select acc_no from TRANSACTION where
    type_of_tr=’D’;
    - Bhavik Thummar

    You might also like