DBMS Fair Record Set - I

SET-I  Create the table student with fields  SID Sname, DOB, Physic, Chems, Maths.

CREATE TABLE student(

sid NUMBER(3),

sname VARCHAR2(60),

dob DATE,

physics NUMBER(5,2),

chems NUMBER(5,2),

maths NUMBER(5,2),

PRIMARY KEY (sid)

);

---

Add few rows into the table

INSERT INTO student

VALUES(301,'Abraham Mathew','23-June-1996',75,87,37);

INSERT INTO student

VALUES(302,'Adharsh Sunny','29-April-1995',78,82,90);

INSERT INTO student

VALUES(303,'Anson Mathew','27-February-1997',65,31,88);

INSERT INTO student

VALUES(304,'Arun Joji','17-March-1994',38,55,60);

INSERT INTO student

VALUES(305,'Christin Chacko','03-May-1994',82,88,97);

INSERT INTO student

VALUES(306,'Dimal Mathew','21-December-1994',91,96,99);

INSERT INTO student

VALUES(307,'Jeethu Mathew','19-April-1993',77,83,40);

INSERT INTO student

VALUES(308,'Manual Cherian','30-April-1994',32,56,69);

INSERT INTO student

VALUES(309,Nirmal Thomas,'22-July-1996',41,55,87);

INSERT INTO student

VALUES(310,'Visakh Harikumar','14-August-1994',91,82,29);

---

--> Display name and dob of student who is youngest.

SELECT sname,dob

FROM student

WHERE dob = (SELECT MAX(dob) FROM student);

---

--> Display details of students who have passed in maths and either in physics or chemistry.

SELECT * FROM student

WHERE maths >= 40 AND (physics >=40 OR chems >=40);

---

--> Add 2 more columns total and average.

ALTER TABLE student 

ADD (total NUMBER(5,2),average NUMBER(5,2));

---

--> Display name of student who scored highest score in maths.

SELECT sname

FROM student

WHERE maths = (SELECT MAX(maths) FROM student);

---

--> update column total with total marks.

UPDATE student

SET total=maths+physics+chems;

---

--> Display details of students in order of merit.

SELECT * FROM student ORDER BY total DESC;

---

--> Rename  the column name average with avg_mark.

ALTER TABLE student RENAME COLUMN average TO avg_mark;

---

--> Rename the table with std.

ALTER TABLE student RENAME TO std;

---

--> Find out overall average of class.

UPDATE std SET avg_mark = (maths+physics+chems)/3;

SELECT AVG(avg_mark) "Average class mark" FROM std;

---

--> Display details of students whose average is greater than overall average.

SELECT * FROM std

WHERE avg_mark > (SELECT AVG(avg_mark) FROM std);

---

SET-II  Create the Table loan_accounts with the structure given below

CREATE TABLE loan_accounts(

 accno CHAR(4),

 cust_name VARCHAR2(15),

 loan_amount NUMBER(9,2),

 installments NUMBER(3),

 int_rate NUMBER(5,2),

 start_date DATE,

 interest NUMBER(9,2),

 PRIMARY KEY(accno)

 );

---

Insert the following records into the table.

INSERT INTO loan_accounts

VALUES('1001','R.K Gupta',300000.00,36,12.00,to_date('19-July-2009'),NULL);

INSERT INTO loan_accounts

VALUES('1002','S.P Sharma',500000.00,48,10.00,to_date('22-March-2008'),NULL);

INSERT INTO loan_accounts

VALUES('1003','K.P Jain',300000.00,36,NULL,to_date('03-August-2007'),NULL);

INSERT INTO loan_accounts

VALUES('1004','M.P Yadav',800000.00,60,10.00,to_date('12-June-2008'),NULL);

INSERT INTO loan_accounts

VALUES('1005','S.P Sinha',200000.00,36,12.50,to_date('01-March-2010'),NULL);

INSERT INTO loan_accounts

VALUES('1006','P Sharma',700000.00,60,12.50,to_date('06-May-2008'),NULL);

INSERT INTO loan_accounts

VALUES('1007','K.S Dhall',500000.00,48,NULL,to_date('03-May-2008'),NULL);

---

--> Put the interest rate 11.50% for all the loans for which the interest rate is NULL.

UPDATE loan_accounts SET int_rate= 11.50 WHERE int_rate IS NULL;

---

--> Increase the interest rate by 0.5% for all the Loans for which the Loan amount is more than 400000.

UPDATE loan_accounts SET int_rate=0.50+int_rate WHERE loan_amount > 400000;

---

--> Display the Accno, Cust_name and Loan_amount of all the loans.

SELECT accno "Account Number",cust_name "Customer Name",loan_amount "Loan Amount" FROM loan_accounts;

---

--> Display the Accno and Loan_amount of all the loans started before 01-04-2009.

SELECT accno,loan_amount FROM loan_accounts WHERE start_date < TO_DATE('01-April-2009');

---

--> Display the Accno, Cust_name and Loan_Amount of all the Loans for which the Cust_name ends with ‘Sharma’.

SELECT accno,cust_name,loan_amount FROM loan_accounts WHERE cust_name LIKE '%Sharma';

---

--> Dsiplay the Accno, Cust_name and Loan_Amount for all the Loans for which the Cust_name does not contain ‘P’.

SELECT * FROM loan_accounts WHERE NOT ( cust_name LIKE '%P%' OR cust_name LIKE '%p%');

---

--> Display the structure of table.

DESC loan_accounts;

---

--> Display the details of all the Loans in the ascending order of their Loan_amount and within Loan_amount in the descending order of their Start_date.

SELECT * FROM loan_accounts ORDER BY loan_amount,start_date DESC;

---

--> Display the details of all the Loans whose rate of interest is NULL.

SELECT * FROM loan_accounts WHERE int_rate is NULL;

NO Output

---

--> Display the amounts of various loans from the table Loan_Accounts. A Loan_Amount should appear only once.

SELECT UNIQUE loan_amount FROM loan_accounts;

---

--> Display the details of all the Loans whose Loan amount is in the Range 400000 to 500000.

SELECT * FROM loan_accounts WHERE loan_amount BETWEEN 400000 AND 500000;

---

--> Delete the records of loan whose name start with K.

DELETE FROM loan_accounts WHERE cust_name LIKE 'K%';

---

--> Display the Customer_name and Loan_amount of all the Loans for which the number of installments are 26, 36 and 48.

SELECT cust_name,loan_amount FROM loan_accounts WHERE installments in (26,36,48);

---

--> Display the customer name, loan_amount and interest rate. If interest rate is NULL, display it as “No Interest”.

SELECT cust_name,

loan_amount,

decode(int_rate,NULL,'no interest',int_rate) "int_rate"

FROM loan_accounts;

---

--> Delete all records whose start date is before 2008.

DELETE FROM loan_accounts WHERE start_date < to_date('01-January-2008');

---

Click to download the pdf