Scribd
Upload
1 views

Practical assignment 2 cs class 12

Uploaded by

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

Practical assignment 2 cs class 12

Uploaded by

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

Practical assignment 2

-- Table: Loan_Accounts
-- | AccNo | Cust_Name | Loan_Amount | Instalments | Int_Rate | Start_Date | Interest |
-- |-------|------------|-------------|-------------|----------|------------|----------|
-- | 1 | R. K. Gupta| 300000 | 36 | 12.00 | 19-07-2009 | Null |
-- | 2 | S. P. Sharma| 500000 | 48 | 10.00 | 22-03-2008 | Null |
-- | 3 | K. P. Jain | 300000 | 36 | NULL | 08-03-2007 | Null |
-- | 4 | M. P. Yadav| 800000 | 60 | 10.00 | 06-12-2008 | Null |
-- | 5 | S. P. Sinha| 200000 | 36 | 12.50 | 03-01-2010 | Null |
-- | 6 | P. Sharma | 700000 | 60 | 12.50 | 05-06-2008 | Null |
-- | 7 | K. S. Dhall| 500000 | 48 | NULL | 05-03-2008 | Null |

-- (i) Insert rows in the above table Loan_Accounts.


-- Use both implicit and explicit methods while inserting NULL values

-- Implicit method (NULL values are omitted)


INSERT INTO Loan_Accounts (AccNo, Cust_Name, Loan_Amount, Instalments, Int_Rate,
Start_Date)
VALUES (8, 'A. B. Singh', 400000, 36, 11.00, '15-04-2011');

-- Explicit method (NULL values are explicitly mentioned)


INSERT INTO Loan_Accounts (AccNo, Cust_Name, Loan_Amount, Instalments, Int_Rate,
Start_Date, Interest)
VALUES (9, 'C. D. Verma', 600000, 48, NULL, '20-09-2010', NULL);

-- Output:
-- Query OK, 1 row affected (0.01 sec) [for each insert]

-- (ii) Put the interest rate (Int_rate) as 11.50% on loan amount for the loans for which interest
rate is missing
UPDATE Loan_Accounts
SET Int_Rate = 11.50
WHERE Int_Rate IS NULL;

-- Output:
-- Query OK, 2 rows affected (0.01 sec)

-- (iii) Increase the interest rate by 0.5% for the loans for which the loan amount is more than
400000
UPDATE Loan_Accounts
SET Int_Rate = Int_Rate + 0.5
WHERE Loan_Amount > 400000;

-- Output:
-- Query OK, 4 rows affected (0.01 sec)

-- (iv) For each loan replace interest with (Loan_Amount * Int_Rate * Instalments) / (12 * 100)
UPDATE Loan_Accounts
SET Interest = (Loan_Amount * Int_Rate * Instalments) / (12 * 100);

-- Output:
-- Query OK, 9 rows affected (0.01 sec)

-- (v) Remove the records of all the loans whose start date is before 2007
DELETE FROM Loan_Accounts
WHERE Start_Date < '2007-01-01';

-- Output:
-- Query OK, 0 rows affected (0.01 sec)

-- (vi) Remove the loan record of 'K.P. Jain'


DELETE FROM Loan_Accounts
WHERE Cust_Name = 'K. P. Jain';

-- Output:
-- Query OK, 1 row affected (0.01 sec)

-- (vii) Remove all records


DELETE FROM Loan_Accounts;

-- Output:
-- Query OK, 8 rows affected (0.01 sec)

You might also like