sql - part 2 of sample paper

Answer the following questions:
(a)	Compare DDL and DML statements of SQL.
(b)	Swati needs to remove all the rows from SALES table to release the storage space. But she does not want to remove the table structure. Which statement should she use?
(c)	Meena uses a EMP table with following columns: NAME ,SAL,ID,DNAME She needs to display names of employees who have not been assigned any department or have been assigned “pathology” department. Pathology course’s names end with “Pathology”. She wrote the following query: SELECT NAME,SAL FROM EMP,COURSE WHERE DNAME = NULL OR DNAME = “%pathology”; But the query is not producing result. Identify the problem.
(d)	What is the importance of primary key in a table? Explain with example.
(e)	The Title and Price columns of table “Library” are given below: TITLE PRICE Mastering C++ 495 Guide Network 500 Mastering SQL 650 Dos GUIDE 400 Basic for beginners 399 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);
(f)	A table ACCOUNT in a database has 6 columns and 60 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 ?
(a)	Name the constraints(4) which can be added at both the levels.(table and column).
(b)	What are different types of SQL functions? Explain and give examples.
(c)	Consider the table Hospital given below.          Hospital No Name Age DEPARTMENT DateOfAdm Charges Sex 1 Sandeep 64 Surgery 23/02/97 400 F 2 Ravina 24 Orthopedic 20/01/98 200 F 3 Karan 45 Orthopedic 10/02/97 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 500 F 9 Kush 19 Cardiology 13/01/97 800 M 10 Shailya 31 Medicine 19/02/97 400 F Write commands in SQL for (i) to (xii)
	(i)    To show all information about the MALE patients of cardiology department. (ii)    To list the names of male 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 4%.   (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’ ;
(a)	Write an SQL command for creating a table Teacher whose structure is given below: FIELD NAME DATATYPE SIZE CONSTRAINT Tno Number 3 Part of Primary Key Class Varchar 5 Part of Primary Key age Number 5,2 >22 and <=40 Projno Number 6 FK –Project(pno) Address Varchar 30 Default Mumbai
(b)	In a database there are two tables ‘LOAN’ and ‘BORROWER’ as shown below:                                                       LOAN Loan_number Branch_name Amount K-70 Downtown 5000 K-230 Redwood 6000 K-260 Perryridge 3700                      BORROWER Customer_Name Loan_no Jones K-170 Smith K-230 Hayes K-155
	(i)                  Identify the foregin key column in the table BORROWER. (ii)                How many rows and columns will be there in the crossl join of these two tables?
(c)	Consider  the tables PEOPLE and PROPERTIES given below:                 PEOPLE Name Phone PID Aisha 9411223344 1 Karan 9422114455 2 Rosy 9433112244 3
	PROPERTIES PID SPID Farm_Name 1               1 Old house farm 3 2 Nanada’s farm 3 3 Will’s farm 3 4 Tall farm 4 5 The florist
	With reference to these tables, write command in SQL for (i) and (iii) and output for (iii) (i)                  Display the name and phone number of each person who has a farm. (ii)                Display the farm name of farm(s) owned by Karan.