Scribd Logo
Upload

Welcome to Scribd!

  • 178 views

    SQL Day1 Assignment

    Uploaded by

    Sanket Gupta
    The document describes SQL commands to create tables, insert data, update data, and define relationships between tables for a loan management system database. The tables created include customers, products, agreements, repayment schedules, transactions, checks, and payments. Primary and foreign keys are defined. Data is inserted into the tables and later updated, deleted and rolled back in a transaction.

    Copyright:

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

    SQL Day1 Assignment

    Uploaded by

    Sanket Gupta
    178 views21 pages
    The document describes SQL commands to create tables, insert data, update data, and define relationships between tables for a loan management system database. The tables created include customers, products, agreements, repayment schedules, transactions, checks, and payments. Primary and foreign keys are defined. Data is inserted into the tables and later updated, deleted and rolled back in a transaction.

    Original Description:

    SQL DAILY ASSIGNMENT

    Original Title

    SQL DAY1 ASSIGNMENT

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document describes SQL commands to create tables, insert data, update data, and define relationships between tables for a loan management system database. The tables created include customers, products, agreements, repayment schedules, transactions, checks, and payments. Primary and foreign keys are defined. Data is inserted into the tables and later updated, deleted and rolled back in a transaction.

    Copyright:

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

    SQL Day1 Assignment

    Uploaded by

    Sanket Gupta
    The document describes SQL commands to create tables, insert data, update data, and define relationships between tables for a loan management system database. The tables created include customers, products, agreements, repayment schedules, transactions, checks, and payments. Primary and foreign keys are defined. Data is inserted into the tables and later updated, deleted and rolled back in a transaction.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 21

    ASSIGNMENT DAY-1 (SQL)

    1. CREATE TABLE LMS_CUSTOMER_M (


    Customer_ID VARCHAR(20) NOT NULL,
    First_Name VARCHAR(20) NOT NULL ,
    Last_Name VARCHAR(20) ,
    Gender VARCHAR(1) NOT NULL ,
    Date_Of_Birth DATE NOT NULL ,
    Contact_Number VARCHAR(10) NOT NULL,
    Email_Address VARCHAR(40) NOT NULL UNIQUE,
    Monthly_Income NUMBER(8,2) NOT NULL ,
    Profession VARCHAR(40) ,
    Total_Monthly_Expense NUMBER(8,2) NOT NULL ,
    Designation VARCHAR(40) ,
    Company_Name VARCHAR(40));

    2. CREATE TABLE LMS_PRODUCT_CATEGORY_M (


    Category_ID VARCHAR(10) NOT NULL,
    Category_Name VARCHAR(50) NOT NULL,
    Asset_Based VARCHAR (1) NOT NULL check(asset_based='Y' or asset_based='N'),
    Description VARCHAR(150),
    SecuredLoan VARCHAR(2) NOT NULL check(SecuredLoan ='S' or SecuredLoan ='US') );
    3. CREATE TABLE LMS_Product_M ( Product_Code VARCHAR(20) NOT NULL,
    Product_Name VARCHAR(100) NOT NULL,
    Product_Description VARCHAR(200) ,
    Product_Category VARCHAR(10) NOT NULL,
    Max_Tenure Number(2),
    Min_Tenure Number(2));
    4. CREATE TABLE LMS_AGREEMENT_DTL ( Agreement_ID VARCHAR(20) NOT NULL,
    Lessee_ID VARCHAR(100) NOT NULL,
    Tenure NUMBER(2) ,
    ROI NUMBER(3,1),
    Loan_Amount NUMBER(10,2),
    Repayment_Frequency VARCHAR(2) NOT NULL check(Repayment_Frequency ='M' or
    Repayment_Frequency ='Y' or Repayment_Frequency ='Q' or Repayment_Frequency
    ='HY'), Loan_Disbursal_Date DATE, Status VARCHAR(10) NOT NULL check(Status
    ='Pending' or Status ='Approved' or Status='Rejected' or Status ='Active' or Status
    ='Closed' ) , Product_Code VARCHAR(20) NOT NULL ) ;

    5. CREATE TABLE LMS_REPAYSCH_DTL (


    Agreement_id Varchar(20) NOT NULL ,
    PropInstID Number(8) NOT NULL,
    Installment_Amount Number(8,2) NOT NULL,
    Installment_Number Number(3) NOT NULL,
    Principal_Component Number(8,2) NOT NULL,
    Interest_Component Number(8,2) NOT NULL,
    Balance_Principal_Amount Number(8,2) NOT NULL,
    Penalty_Charges Number(8,2),
    Installment_Due_Date Date NOT NULL);
    6. CREATE TABLE LMS_TXNTYPE_M (
    TXN_TYPE VARCHAR(20) NOT NULL CHECK(TXN_TYPE = 'INSTALLMENT' OR TXN_TYPE
    = 'LPP'),
    DESCRIPTION VARCHAR(100));
    7. Create Table LMS_TXN_ADVICE_M (
    TXN_ADVICE_ID NUMBER(8) NOT NULL,
    ADVICEAMT NUMBER(8,2) NOT NULL,
    ADVICEDATE DATE NOT NULL,
    CASE_ID VARCHAR(20) NOT NULL,
    TXNID NUMBER(8) NOT NULL,
    TXNTYPE VARCHAR(20) NOT NULL);

    8. CREATE TABLE LMS_CHEQUE_DTL(


    cheque_id number(8) NOT NULL check(cheque_id = 'Unique system identifier'),
    Payment_mode varchar(1) NOT NULL check(payment_mode = 'C' or payment_mode
    = 'Q' or payment_mode = 'D' or payment_mode = 'T'),
    cheque_num varchar(50) NOT NULL,
    cheque_date Date,
    cheque_amount Number(10,2),
    Drawn_On_Bank Varchar(50),
    Deposit_date Date,
    status varchar(1) check(status = 'C' or status = 'D' or status = 'X' or status = 'B’));
    9. CREATE TABLE LMS_PAYMENT_DTL (
    PAYMENT_ID NUMBER(8) NOT NULL CHECK(PAYMENT_ID = 'UNIQUE SYSTEM
    IDENTIFIER'), CHEQUE_ID NUMBER(8) NOT NULL CHECK(CHEQUE_ID = 'UNIQUE
    SYSTEM IDENTIFIER'), PAYMENT_DATE DATE NOT NULL,
    STATUS VARCHAR(1) NOT NULL CHECK(STATUS = 'C' OR STATUS = 'N'),
    TXN_ADVICE_ID NUMBER(8) NOT NULL);
    10. DESC USER_CONSTRAINTS;

    11. ALTER TABLE LMS_CUSTOMER_M


    ADD CONSTRAINTS LMS_CUSTOMER_M_PK PRIMARY KEY (CUSTOMER_ID);

    ALTER TABLE LMS_PRODUCT_CATEGORY_M


    ADD CONSTRAINTS LMS_PRODUCT_CAT_M_PK PRIMARY KEY (CATEGORY_ID);

    ALTER TABLE LMS_PRODUCT_M


    ADD CONSTRAINTS LMS_PRODUCT_M_PK PRIMARY KEY (PRODUCT_CODE);

    ALTER TABLE LMS_AGREEMENT_DTL


    ADD CONSTRAINTS LMS_AGREEMENT_DTL_PK PRIMARY KEY (AGREEMENT_ID);

    ALTER TABLE LMS_REPAYSCH_DTL


    ADD CONSTRAINTS LMS_REPAYSCH_DTL_PK PRIMARY KEY (PROPINSTID);

    ALTER TABLE LMS_TXNTYPE_M


    ADD CONSTRAINTS LMS_TXNTYPE_M_PK PRIMARY KEY (TXNTYPE);

    ALTER TABLE LMS_TXN_ADVICE_DTL


    ADD CONSTRAINTS LMS_TXN_ADVICE_DTL_PK PRIMARY KEY (TXN_ADVICE_ID);

    ALTER TABLE LMS_CHEQUE_DTL


    ADD CONSTRAINTS LMS_CHEQUE_DTL_PK PRIMARY KEY (CHEQUE_ID);
    ALTER TABLE LMS_PAYMENT_DTL
    ADD CONSTRAINTS LMS_PAYMENT_DTL_PK PRIMARY KEY (PAYMENT_ID);
    12. ALTER TABLE LMS_Product_M
    ADD CONSTRAINT LMS_Product_Cat_FK FOREIGN KEY (Product_Category)
    REFERENCES LMS_Product_Category_M (Category_ID);

    ALTER TABLE LMS_Agreement_DTL


    ADD CONSTRAINT LMS_Agreement_Dtl_FK1 FOREIGN KEY (Lessee_ID) REFERENCES
    LMS_Customer_M (Customer_ID);

    ALTER TABLE LMS_Agreement_DTL


    ADD CONSTRAINT LMS_Agreement_DTL_FK2 FOREIGN KEY (Product_code)
    REFERENCES LMS_Product_M (Product_Code);

    ALTER TABLE LMS_Repaysch_Dtl


    ADD CONSTRAINT LMS_Repaysch_Dtl_FK FOREIGN KEY (Agreement_Id) REFERENCES
    LMS_Agreement_DTL (Agreement_id);

    ALTER TABLE LMS_TXN_ADVICE_DTL


    ADD CONSTRAINT LMS_TXN_ADVICE_DTL_FK1 FOREIGN KEY (TxnType) REFERENCES
    LMS_TXNTYPE_M (Txn_Type);

    ALTER TABLE LMS_TXN_ADVICE_DTL


    ADD CONSTRAINT LMS_TXN_ADVICE_DTL_FK2 FOREIGN KEY (Case_Id) REFERENCES
    LMS_Agreement_DTL (Agreement_id);

    ALTER TABLE LMS_TXN_ADVICE_DTL


    ADD CONSTRAINT LMS_TXN_ADVICE_DTL_FK3 FOREIGN KEY (Txn_ID) REFERENCES
    LMS_REPAYSCH_DTL (PropInstID);

    ALTER TABLE LMS_Payment_DTL


    ADD CONSTRAINT LMS_Payment_Dtl_FK1 FOREIGN KEY (Cheque_ID) REFERENCES
    LMS_CHEQUE_DTL (Cheque_id);

    ALTER TABLE LMS_Payment_DTL


    ADD CONSTRAINT LMS_Payment_Dtl_FK2 FOREIGN KEY (Txn_Advice_Id)
    REFERENCES LMS_TXN_ADVICE_DTL (Txn_Advice_id);
    13. ALTER TABLE LMS_CUSTOMER_M MODIFY CUSTOMER_ID VARCHAR(20);
    ALTER TABLE LMS_CUSTOMER_M MODIFY FIRST_NAME VARCHAR(20);
    ALTER TABLE LMS_CUSTOMER_M MODIFY LAST_NAME VARCHAR(30);
    ALTER TABLE LMS_CUSTOMER_M MODIFY GENDER CHAR(1);
    ALTER TABLE LMS_CUSTOMER_M MODIFY DATE_OF_BIRTH DATE;
    ALTER TABLE LMS_CUSTOMER_M MODIFY CONTACT_NUMBER VARCHAR(10);
    ALTER TABLE LMS_CUSTOMER_M MODIFY EMAIL_ADDRESS VARCHAR(40);
    ALTER TABLE LMS_CUSTOMER_M MODIFY MONTHLY_INCOME NUMBER(8,2);
    ALTER TABLE LMS_CUSTOMER_M MODIFY DESIGNATION VARCHAR(40);
    ALTER TABLE LMS_CUSTOMER_M MODIFY COMPANY_NAME VARCHAR(40);
    14. CREATE TABLE CUSTOMER_2 AS (SELECT
    Customer_id AS ID, First_Name AS NAME,
    Email_Address AS EMAIL,
    Profession AS PROFESSION,
    Monthly_Income AS Income from
    LMS_CUSTOMER_M);

    15. DROP TABLE CUSTOMER_2;


    16. SHOW USER_RECYCLEBIN;

    17. Flashback table customer_2 to before drop;

    18. ALTER TABLE CUSTOMER_2 DROP COLUMN FIRST_NAME;

    DML

    1. INSERT INTO LMS_CUSTOMER_M VALUES('C101','Amit','Gupta','m','10-jan-


    88','9876545678','amit.gupta@gmail.com','50000','service','15000','software
    engineer','nucleus');

    2. INSERT INTO LMS_CUSTOMER_M (CUSTOMER_ID, FIRST_NAME, LAST_NAME,


    GENDER, DATE_OF_BIRTH, CONTACT_NUMBER, EMAIL_ADDRESS,
    MONTHLY_INCOME, PROFESSION, TOTAL_MONTHLY_EXPENSE, DESIGNATION,
    COMPANY_NAME)
    VALUES (‘C102’, 'REKHA','ARORA','F','08-AUG-76,’987897451’,’AREKHA@GMAIL.CO
    M’,'100000','SERVICE','40000','PROJECT_MANAGER','TCS');
    3. SELECT
    CUSTOMER_ID,FIRST_NAME,LAST_NAME,GENDER,DATE_OF_BIRTH,CONTACT_NUM
    BER,EMAIL_ADDRESS,MONTHLY_INCOME,PROFESSION,TOTAL_MONTHLY_EXPENSE,
    DESIGNATION,COMPANY_NAME FROM LMS_CUSTOMER_M;

    4. save @D:\lab_01_04.sql;
    5&6. INSERT INTO LMS_CUSTOMER_M
    (CUSTOMER_ID,FIRST_NAME,LAST_NAME,GENDER,DATE_OF_BIRTH,CONTACT_NUMBER
    ,EMAIL_ADDRESS,MONTHLY_INCOME,PROFESSION,TOTAL_MONTHLY_EXPENSE,DESIGNATION,C
    OMPANY_NAME)VALUES(&CUSTOMER_ID,&FIRST_NAME,&LAST_NAME,&GENDER,&DATE_OF_
    BIRTH,&CONTACT_NUMBER,&EMAIL_ADDRESS,&MONTHLY_INCOME,&PROFESSION,&TOTAL_M
    ONTHLY_EXPENSE,&DESIGNATION,&COMPANY_NAME);

    7. SELECT * FROM LMS_CUSTOMER_M;


    8. COMMIT;

    9. TABLE 2- INSERT INTO LMS_PRODUCT_M VALUES('P101','UNDER-CONSTRUCTION','


    ' ,'LN101','20','10');

    TABLE 3- INSERT INTO LMS_PRODUCT_CATEGORY_M VALUES ('LN101','HOME


    LOAN','Y',' ','S');

    TABLE 4 - INSERT INTO LMS_AGREEMENT_DTL


    VALUES('LN_HOME_101','C101','15','8.5','3000000','M','10-SEP-18','

    TABLE 5- INSERT INTO LMS_REPAYSCH_DTL


    VALUES('LN_HOME_101','101','1','29542.19','8292.19','21250.00','3000000.00','0','10
    -OCT-18');

    TABLE 6- INSERT INTO LMS_TXNTYPE_M VALUES('INSTALLMENT','EMI TO BE PAID


    PER MONTH FOR A PARTICULAR LOAN');

    TABLE 7- INSERT INTO LMS_TXN_ADVICE_M VALUES('1001','₹29,542.19','10-OCT-


    18','LN_HOME_101','P_H101_01','INSTALLMENT');

    TABLE 8- INSERT INTO LMS_CHEQUE_DTL VALUES('10001','Q','34567878','10-OCT-


    18','29542.19','SBI','10-OCT-18','D’);

    TABLE 9- INSERT INTO LMS_PAYMENT_DTL VALUES('100001','10001','10-OCT-


    18','C','1001');
    10. - UPDATE LMS_CUSTOMER_M SET Last_name='sharma' WHERE
    CUSTOMER_ID='C103';

    11. UPDATE LMS_CUSTOMER_M SET Profession='Service',Designation='Asst


    Manager',Company_name='Wipro' WHERE Customer_id='C104';
    12. DELETE FROM LMS_CUSTOMER_M WHERE MONTHLY_INCOME < 40000;

    13. DELETE FROM LMS_AGREEMENT_DTL WHERE EXTRACT(YEAR FROM


    LOAN_DISBURSAL_DATE)<2000;
    14. commit;

    CONTROL DATA TRANSACTION

    15. INSERT INTO LMS_CUSTOMER_M values('101','Amit','Gupta','M','10-JAN-


    88','9876545678','amit.gupta@gmail.com','50000','Service','1500','Software
    Engineer','Nucleus');

    INSERT INTO LMS_CUSTOMER_M VALUES('c104','Mayank','Mittal','M','20-


    Jan87','9876542318','mayank.mittal@gmail.com ','2 00000','self-Employe','85000','Fitness
    Trainer','');
    16. SAVEPOINT SP1;

    17. DELETE FROM LMS_CUSTOMER_M;

    18. SELECT * FROM LMS_CUSTOMER_M;

    19. ROLLBACK TO SP1;

    20. SELECT * FROM LMS_CUSTOMER_M;


    21. COMMIT;

    You might also like