PRACTICAL-1

Table - Loan_Accounts

1.Create the database Loans.

mysql> create database Loans;

2.Use database Loans.

mysql> Use Loans;

3.Create the table Loan_Accounts and insert tuples in it.

create table Loan_Accounts(AccNo int primary key,Cust_Name varchar(15),Loan_Amount int,installment
varchar(3), Int_Rate numeric(4,2),Start_Date date);

mysql> insert into Loan_Accounts values(1,'R.K.Gupta',300000,'36',12.00,'2009-07-19');

Query OK, 1 row affected (0.03 sec)
mysql> insert into Loan_Accounts values(2,'S.P.Sharma',500000,'48',10.00,'2008-03-22');
Query OK, 1 row affected (0.01 sec)
mysql> insert into Loan_Accounts values(3,'K.P.Jain',300000,'36',null,'2007-03-08');
Query OK, 1 row affected (0.01 sec)
mysql> insert into Loan_Accounts values(4,'M.P.Yadav',800000,'60',10.00,'2008-12-06');
Query OK, 1 row affected (0.00 sec)
mysql> insert into Loan_Accounts values(5,'S.P.Sinha',200000,'36',12.50,'2010-01-03'); Query
OK, 1 row affected (0.00 sec)
mysql> insert into Loan_Accounts values(6,'P.Sharma',700000,'60',12.50,'2008-06-05'); Query
OK, 1 row affected (0.00 sec)
mysql> insert into Loan_Accounts values(7,'K.S.Dhall',500000,'48',null,'2008-03-05');
Query OK, 1 row affected (0.00 sec)

4.Display the details of all the loan.

5. Display the AccNo, Cust_Name, and Loan_Amount of all the loans.
mysql> select AccNo, Cust_Name , Loan_Amount from Loan_Accounts;

6.Display the details of all the loans with less than 40 installments.
mysql> select * from Loan_Accounts where installment<'40';

7.Display the AccNo and Loan_Amount of all the loans started before 01-04-
2009.
mysql> select AccNo,Loan_Amount from Loan_Accounts where Start_date<'2009-04-01';
8. Display the Int_Rate of all the loans started after 01-04-2009. mysql> select Int_Rate
from Loan_Accounts where Start_date>'2009-04-01';

9. Display the details of all the loans whose rate of interest is NULL.
mysql> select * from Loan_Accounts where Int_Rate is null;

10. Display the details of all the loans whose rate of interest is not NULL.
mysql> select * from Loan_Accounts where Int_Rate is not null;

11.Display the amounts of various loans from the table Loan_Accounts. A loan amount
should appear only once.
mysql> select distinct Loan_Amount from Loan_Accounts;
12.Display the number of installments of various loans from the table Loan_Accounts.
An installment should appear only once.
mysql> select distinct installment from Loan_Accounts;

13.Display the details of all the loans started after 31-12-2008 for which the number of
installments are more than 36.
mysql> select * from Loan_Accounts where Start_Date>'2008-12-31' and installment>'36';

14.Display the Cust_Name and Loan_Amount for all the loans which do not have
number of installments 36.
mysql> select Cust_Name, Loan_Amount from Loan_Accounts where installment<> 36 ;

15.Display the Cust_Name and Loan_Amount for all the loans for which the loan
amount is less than 500000 or int_rate is more than 12.
mysql> select Cust_Name,Loan_Amount from Loan_Accounts where Loan_Amount<500000 or
Int_Rate>12.00;
16.Display the details of all the loans which started in the year 2009.
mysql> select * from Loan_Accounts where Start_Date>'2008-12-31' and Start_Date<'2010-01-01';

17.Display the details of all the loans whose Loan_Amount is in the range 400000 to
500000.
mysql> select * from loan_accounts where Loan_Amount between 400000 and 500000 ;

18.Display the details of all the loans whose rate of interest is in the range 11% to 12 %.
mysql> select * from loan_accounts where Int_Rate between 11.00 and 12.00 ;

19.Display the Cust_Name and Loan_Amount for all the loans for which the number of
instalments are 24, 36, or 48. (Using IN operator) mysql> Select Cust_Name,
Loan_Amount from Loan_Accounts where installment in (24, 36, 48) ;

20. Write an alternate way of writing the same query as in Q.19 without using IN.
mysql> select Cust_Name, Loan_Amount from Loan_Accounts where installment=24 or installment=
36 or installment=48;

21.Display the details of all the loans whose Loan_Amount is in the range 400000
to500000. (Using BETWEEN operator) mysql> select * from Loan_Accounts where
Loan_Amount between 400000 and 500000 ;

22. Display the details of all the loans whose rate of interest is in the range 11% to
12%.(Using BETWEEN operator) mysql> select * from Loan_Accounts where Int_Rate between
11.00 and 12.00 ;

23. Write an alternate way of writing the same query as in Q.22 without using
BETWEEN
mysql> Select * from Loan_Accounts where Int_Rate>=11.00 and Int_Rate<=12.00;

24. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which
theCust_Name ends with 'Sharma'.
mysql> Select AccNo, Cust_Name, Loan_Amount from Loan_Accounts where Cust_Name like
'%Sharma';

25.Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which
theCust_Name ends with 'a'.
mysql> Select AccNo, Cust_Name, Loan_Amount from Loan_Accounts where Cust_Name like '%a';

26.Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which
theCust_Name contains 'a’. mysql> Select AccNo, Cust_Name, Loan_Amount from
Loan_Accounts where Cust_Name like '%a%';

27.Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which
theCust_Name does not contain 'P'. mysql> Select AccNo, Cust_Name, Loan_Amount from
Loan_Accounts where Cust_Name not like '%P%';
28.. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which
theCust_Name contains 'a' as the second last character. mysql> Select AccNo,
Cust_Name, Loan_Amount from Loan_Accounts where Cust_Name like
'%a_';

29. Display the details of all the loans in the ascending order of their Loan_Amount

mysql> select * from Loan_Accounts order by Loan_Amount asc;

30. Display the details of all the loans in the deending order of their Start_Date.

mysql>select * from Loan_Accounts order by Start_Date desc;

31. 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.

mysql> select * from Loan_Accounts order by Loan_Amount asc,Start_Date desc;

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

update Loan_Accounts set Int_Rate =11.50 where Int_Rate=null;

33. Increase the interest rate by 0.5% for all the loans for which the loan amount is more
than 400,000. mysql> Update Loan_Accounts set Int_Rate=Int_Rate + 0.5 where
Loan_Amount > 400000 ;

34.For each Loan replace the Interest with ( Loan_amount*Int_Rate*Installment

)/12*100.

mysql> update Loan_Accounts set Int_Rate= ( Loan_Amount*Int_Rate*installment)/(12*100);

35.Change No of installments to 70 and Int_Rate as 12 for all loans more than 800000.

mysql> update Loan_Accounts set Int_Rate=12 , installment=70 where Loan_Amount>800000;

36. Delete the records of all the loans whose start date is before 2007.

mysql> delete from Loan_Accounts where Start_Date < ‘2007-01-01’;

37. Delete the records of all the loans of 'K.P. Jain'

mysql> Delete from Loan_Accounts where Cust_Name='K.P.Jain';

38.Assign Primary Key to column AccNo. mysql>

Alter table Loan_Accounts add primary key(AccNo);

39.Add another column Category of type CHAR(1) in the Loan table.

mysql> Alter table Loan_Accounts add CATEGORY char(1);

40.Rename the column Instalments to Instl. mysql>

Alter table Loan_Accounts change Installment Instl;

41.Delete the column interest. mysql> Alter

table Loan_Accounts drop interest;

42.Delete all records from table Loan without deleting the table.

mysql> Delete from Loan_Accounts;

43.Delete all records from table Loan along with the table as well.

mysql> Drop table Loan_Accounts;

44.Delete all records from table Loan without deleting the table such that memory space
is released.
mysql> Truncate table Loan_Accounts;
 PRACTICAL QUESTION 2
Table -Hospital
1. Create the table Hospital with the following constraints - no primary key,name not null ,age ,
dateofadm ,charges, sex , department not null.

create table Hospital(sno int primary key,Name varchar(15) not null,age int,dateofadm
date,charges int ,Sex char(1), department varchar(10) not null);

2. Insert the records.

insert into Hospital values(1,'Arpit',62,'1998-01-21',300,'M','Cardiology');
insert into Hospital values(2,'Zarina',22,'1997-12-12' , 250 ,'F', 'ENT'); insert
into Hospital values(3,'Kareem',32,'1998-02-19', 200 ,'M' ,'ENT'); insert into
Hospital values(4, 'Arun',62, '1998-01-11' ,300, 'M', 'Eyes'); insert into
Hospital values(5,'Zubin',30, '1998-01-12' ,250, 'M' , 'Cardiology'); insert into
Hospital values(6,'Ketika',16, '1998-02-24' ,250, 'F' , 'ENT'); insert into
Hospital values(7,'Ankita',29,'1998-02-20' ,800,'F' ,'ENT'); insert into
Hospital values(8,'Zareen',45,'1998-02-22',300,'F' ,'Cardiology'); insert into
Hospital values(9,'Kush',19,'1998-01-13',800,'M','ENT'); insert into Hospital
values(10,'Shilpa',23,'1998-02-21',400,'F','ENT');

Display the records

3. To Show all information about the patients of cardiology department.
mysql> select * from hospital where Department=’Cardiology’

4. To list the names of female patients who are in ENT or Surgery department.
mysql> select name from hospital where Department in (‘ENT’,‘Surgery’) and Sex = ‘F’;

5.To list names of all patients with their date of admission in descending order.
mysql> select name from hospital order by dateofadm desc;
6.To display patients name, charges, age of patients whose name start with K. mysql>
select name, charges , age from hospital where name like ‘K%’;

7. To count the number of patients with age greater than 30. mysql> Select count(*) from
hospitals where age>’30’;

8. To display all the different departments. mysql> select distinct departments from
Hospital;
9. To display min and max age of patients as per gender.
mysql> select sex, min(age) , max(age) from Hospital group by sex;

10. To display department wise sum and average charges. mysql> select department,
sum(charges) , avg(charges) from Hospital group by department;

11. To display the number of patients in each department with more than 1 patient. mysql>
select department, count(*) from Hospital group by department having count(*)>1;

12. To change the age of patient Kush to 22. mysql> Update Hospital set age = ‘22’ where
name = ‘Kush’;

13. To increase the charges of male patients of cardiology department by 10 %. mysql>

Update Hospital set charges = charges + (0.1*charges) where Sex=‘M’,department =
‘Cardiology’;

14. .To remove all records of female patients who paid charges between 300 and 400
mysql> Delete Hospital where sex =‘F’ , charges between 300 and 400 ;
15. To add new column - DocName varchar(20).
mysql> Alter table Hospital add DocName varchar(20);

16. To rename the column Sex as Gender

mysql> Alter table Loan_Accounts change Instalments Instl;

17. To display the structure of table Hospital.

mysql> Describe Hospital;

18. To display the first 4 letter of patients’ name. mysql> Select substring( name , 1 ,
4) from hospital;
19. To display the details of patients admitted in 1997.
mysql> Select * from Hospital where dateofadm > ‘1996-12-31’ and dateofadm <
‘1998-01-01’;

20.To display all names and their length for age<25.

mysql> Select name , length(name) as lengthname from hospital where age < ‘25’;
 PRACTICAL QUESTION - 3
TABLE - STORE AND SUPPLIER

create table store(ItemNo int,Item varchar(20),Scode int ,Qty int ,Rate int ,LastBuy date
);
create table suppliers(Scode int ,Sname varchar(20)); insert into store
values(2005 ,'Sharpener Classic',23,60,8,'2009-06-30'); insert into store
values(2003 ,'Ball Pen 0.25 ',22,50,25,'2010-02-01'); insert into store
values(2002,'Gel Pen Premium',21,150,12,'2010-02-24'); insert into store
values(2006,'Gel Pen Classic',21,250,20,'2009-03-11 '); insert into store
values(2004,'Eraser Big',22,110 ,8,'2009-12-02'); insert into store
values(2009,'Ball Pen 0.5',21,180,18,'2009-11-03') ; insert into suppliers
values(21,'Premium Stationery'); insert into suppliers values(23,'Soft
Plastics'); insert into suppliers values(22,'Tetra Supply'); select * from
store; select * from suppliers;

1.To display details of all the items in the STORE table in ascending order of LastBuy.
Ans SELECT * FROM STORE ORDER BY LastBuy ASC;
2.To display ItemNo and Item name of those items from STORE table whose Rate is
more than 15 Rupees.
Ans SELECT ItemNo, Item FROM STORE WHERE Rate > 15 ;

3.To display the details of those items whose supplier code is 22 or quantity in Store is
more than 110 from the table Store.
Ans SELECT * FROM STORE WHERE Scode = ’22’ OR Qty >’110 ’;

4.To display the minimum Rate of items for each supplier individually as per Scode from
the table STORE.
Ans SELECT Sname, MIN(Rate) FROM STORE, SUPPLIERS WHERE STORE. Scode
= SUPPLIERS.Scode GROUP BY Sname;
5.SELECTCOUNT(DISTINCTScode) FROM STORE;

6.SELECTRate* Qty FROM STORE WHEREItemNo=2004;

7.SELECT Item, Sname FROM STORE S, Suppliers P WHERE S.Scode=PScode AND

ItemNo=2006;

8.SELECT MAX(LastBuy) FROM STORE;