ASSIGNMENT ON SQL QUERIES

SUBJECT: INFORMATICS PRACTICES

ASSIGNMENT-1

TOPIC: SQL QUERIES

Q1. Consider the table Hospital given below and write commands in SQL for (i) to (xii)

 Hospital

No	Name	Age	DEPARTMENT	DateOfAdm	Charges	Sex
1	Sandeep	64	Surgery	23/02/98	300	M
2	Ravina	24	Orthopedic	20/01/98	200	F
3	Karan	45	Orthopedic	10/02/98	200	M
4	Tarun	12	Surgery	01/01/98	300	F
5	Zubin	36	ENT	12/01/98	250	M
6	Ketaki	16	ENT	12/02/98	300	F
7	Ankita	29	Cardiology	20/02/98	800	F
8	Zareen	45	Gynecology	22/02/98	Null	F
9	Kush	19	Cardiology	13/01/98	800	M
10	Shailya	31	Medicine	19/02/97	400	F

(i)    To show all information about the patients of cardiology department.

(ii)    To list the names of female patients who are in orthopaedic department.

(iii)   To display Patient’s name, charges, Age for  male and female patients.

(iv)  To count the number of patients with Age > 30.

(v)    Increase the charges of male patient in ENT department by 3%.

  (vi) Add another column email_id with suitable data type.

  (vii) Delete the records of all female patients in Surgery department.

  (viii)Display a report listing name, age, charges and amount of charges including VAT as 2%  on charges name the column as total charges and keep the data in ascending order of name.

  (ix)To display the difference of highest and lowest charges of each department having maximum charges more than 300.

  (x) Find out the details of patients whose age  is same or more than that of patient whose hospital charges are maximum.

 (xi)Display the details of all the patients who are hospitalised in 1998.

 (xii)Display the charges of various departments .A charge amount should appear only once.  

Find out the  output for SQL commands (xiii) to (xvi).

  (xiii)SELECT COUNT(DISTINCT  Department) FROM HOSPITAL ;

  (xiv)SELECT MAX(Age) FROM HOSPITAL  WHERE SEX=’M’;

  (xv)SELECT AVG(Charges) FROM HOSPITAL  WHERE SEX=’F’;

  (xvi)SELECT SUM(Charges) FROM HOSPITAL  WHERE DATEOFadm < ’12/08/98’ ;

Q2.

(a)

Write an SQL command for creating a table student whose structure is given below: FIELD NAME DATATYPE SIZE CONSTRAINT Rno Number 3 Part of Primary Key Class Varchar 5 Part of Primary Key Percentage Number 5,2 >0 and <=100 Projno Number 6 FK –Project(pno) Address Varchar 30 Default Hyderabad

            

(b) The Title and Price columns of table “Library” are given below:

TITLE                      PRICE

Mastering C++     295

Guide Network   300

Mastering SQL     450

Dos GUIDE            400

Basic for beginners  299

Mastering Window  Null

Based on this information ,find the output of the following queries:

(a)    SELECT MIN(Price)from library;

(b)   SELECT COUNT(Title) from library WHERE Price < 150;

(c)    Select  AVG(price) from library WHERE title like ‘%e%’;

(d)   Select title from library where price = (select max(price) from library);  

                                                                                         

(c)    A table ACCOUNT in a database has 3 columns and 30 rows. The DBA has added 3 more columns and 50 more rows to the table. But the table has about 15 records where balance is null. What is the degree and cardinality of this table now ?