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 ( mrkdata@yahoo.com )
