Chapter – 8 . STRUCTURED QUERY LANGUAGE

(b)To show all information about the teachers of history department.

Ans. select *from teacher where department=’history’;

(c) To list names of female teacher who are in math department.

Ans. select name from teacher where sex=’male’ and department=’maths’;

d) To list names of all teacher with their date of joining in ascending order.

Ans. Select Name From Teacher order by dateofjoing;

(f) To count the number of teachers with age >23.

Ans. Select count(number of teachers) from ,teacher where age>23;

(g) To insert a new row in the teacher table with the following data: 9, “raja’, 26, “computer”, {13/5/95 }, 2300, “M”.

Ans. Insert into Teacher values(9,”raja”,26,”computer”, {13/05/95},2300,”M”);

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	Date of adm	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	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