TECHONESPOT
All Tech at one spot
Considered the following table Hospital
– > How to create a Database.
Solution :-
Create database hospital;
-> How to show Databases.
Solution :-
show databases;
->How to select database.
Solution :-
use hospital;
-> How to Create table for Hospital.
Solution :-
create table hospital(
PNO int primary key not null,
name varchar(100) not null,
age int,
department varchar(100),
DOA date,
charges int,
gender varchar(1));
->How to see the structure of the database.
Solution :-
desc hospital;
-> How to insert data into table Hospital.
Solution :-
insert into hospital values(1,”Mayank”,65,”Surgery”,’2018-02-23′,600,’M’);
insert into hospital values(2,”Babita”,24,”ENT”,’2019-01-01′,400,’F’);
insert into hospital values(3,”Kashish”,45,”Orthopaedic”,’2018-12-19′,400,’M’);
insert into hospital values(4,”Tarun”,12,”Surgery”,’2018-10-01′,600,’M’);
insert into hospital values(5,”Manisha”,36,”ENT”,’2018-02-24′,400,’F’);
insert into hospital values(6,”Imran”,16,”ENT”,’2018-02-24′,400,’M’);
insert into hospital (PNO,name,Department,DOA,charges,gender)values(7,”Ankita”,”Cardiology”,’2018-08-20′,800,’F’);
insert into hospital values(8,”Zoya”,45,”Gynecology”,’2019-01-13′,500,’F’);
insert into hospital values(9,”Kush”,19,”Cardiology”,’2019-01-13′,800,’M’);
insert into hospital values(10,”Shalini”,65,”Surgery”,’2018-02-23′,300,’F’);
->To Show all the information about the patient of the cardiology department.
Solution :-
Select * from hospital where department = ‘Cardiology’;
-> To list the names of female patients who are either in the orthopedic or surgery department.
Solution :-
Select name from hospital where gender =’F’ and (Department=’Orthopaedic’ or Department=’surgery’);
-> To list the names of all the patients with their DOA in ascending order.
Solution :-
select name, DOA from hospital order by DOA;
-> To display the patient name , charges, the age for female patient only.
Solution :-
Select name ,charges, age from hospital where gender=’F’;
-> To count the number of patients with age > 30.
Solution :-
Select count(age) from hospital where age >30;
-> To display various departments.
Solution :-
Select distinct (department) from hospital;
-> To display the number of patients in each department.
Solution :-
Select department,count(department) from hospital group by department;
-> To display the detials of all the patients whose name starts with the alphabet ‘Z’.
Solution :-
Select * from hospital where name like ‘Z%’;
-> To change the age of the patient kush to 20.
Solution :-
update hospital set age = 20 where name =’Kush’;
-> To increase the charges of all the patients by 5%.
Solution :-
update hospital set charges = charges + (charges * 5)/100;
->To remove the record of the patient whose name is Tarun.
Solution :-
Delete from hospital where name=’Tarun’;
-> To add another column DocName(Doctor Name) of the type varchar in the above table.
Solution :-
Alter table hospital add DocName varchar(20);
-> To display patient detail whose age is missing(null).
Solution :-
Select * from hospital where age is Null;
->To decrease the charges by 5% of all the patients admitted to the ENT department.
Solution :-
update hospital set charges = charges-(charges*5)/100 where department =’ENT’;
-> To insert a new row in the hospital table with the following data : 11,’Karan’,37,’ENT’,’2018-02-25′,300,’M’.
Solution :-
insert into hospital values(11,’Karan’,37,’ENT’,’2018-02-25′,300,’M’,’abc’);
-> To set charges to NULL for all the patients in the surgery department.
Solution :-
update hospital set charges = NULL where department =’Surgery’;
-> To display pateient details who are giving charges in the range 300 and 400.
Solution :-
Select * from hospital where charges between 300 and 400;
-> To display the details of that patient whose name second character contains ‘a’
Solution :-
Select * from hospital where name like ‘_a%’;
-> To display total charges of ENT Department.
Solution :-
Select sum(charges)from hospital where Department=’ENT’;
-> To Display details of the patients who admitted in the year 2019.
Solution :-
Select * from hospital where year (DOA)= 2019;
-> To display the structure of the table hospital.
Solution :-
desc hospital;
