COMPUTER SCIENCE STRUCTURED QUERY LANGUAGE

1998:

5. Write SQL commands for (b) to (g) and write the outputs for (h) on the basis of table HOSPITAL.

NO	NAME	AGE	DEPARTMENT	DATEOFADM	CHARGES	SEX
1	Arpit	62	Surgery	21/1/98	300	M
2	Zareena	22	Ent	12/12/97	250	F
3	Kareem	32	Arthopedic	19/2/98	200	M
4	Arun	12	Surgery	11/1/98	300	M
5	Zubin	30	Ent	12/1/98	250	M
6	Karin	16	Ent	24/2/98	250	F
7	Ankita	29	cardiology	22/2/98	800	F
8	Zareen	45	Gynecology	22/2/98	300	F
9	Kush	19	Cardiology	13/1/98	800	M
10	Shilpa	23	Nuclear medicine	21/2/98	400	F

(b) To select all the information of patients of all cardiology department.

Ans: Select all from Hospital where department=”Cardiology”

(c) To list the names of female patients who are in ent department.

Ans:select name from Hospital where Department=”Ent” and Sex=”F”

(d) To list names of all patients with their date of admission in ascending order.

Ans: Select name,dateofadm from Hospital dateofadm.

(e) To display patients name, charges, age, for only female patients.

Ans: Select Name,Charges,age from Hospital where sex=”F”

(f) To count the number of patients with age <30.

Ans: Select count(*) from hospitals where age<30

(g) To insert the new row in the hospital table with the following data: 11, “aftab”, 24, “surgery”, {25/2/98}, 300, “M”.

Ans: insert into Hospital values(11, “aftab”, 24, “surgery”, {25/02/98}, 300, “M”)

(h) Give the output of the following SQL statements:

(i) Select count (distinct charges)from hospital;

Ans: 5

(ii) Select min(age) from hospital where sex = “f’;

Ans: 16

(iii) Select sum(charges) from hospital where department = “ent”;

Ans: 750

(iv) Select avg(charges) from hospital where date of admission is <{12/02/98};

Ans: 380

 

CBSE Computer Science Solved Revision Tour By Mr. Ravi Kiran ( [email protected] )