Sample Paper –Class – XII: MYSQL

 Subject – Informatics Practices

Time : 1 hours                                                                                                                                    Max Marks:25

  General Instructions

1.       Answer the questions after carefully reading the text.

Q1	Answer the following questions:
	(a)	Compare Char and Varchar datatype of SQL.	1
	(b)	Prerna needs to remove all the rows from SALE_HISTORY table to release the storage space. But she does not want to remove the table structure. Which statement should she use?	1
	(c)	Meena uses a STUDENT table with following columns: NAME ,CLASS,COURSE_ID,COURSE_NAME She needs to display names of students who have not been assigned any course or have been assigned “pathology” course. Pathology course’s names end with “Pathology”. She wrote the following query: SELECT NAME,CLASS FROM STUDENT,COURSE WHERE COURSE_NAME = NULL OR COURSE_NAME = “%pathology”; But the query is not producing result. Identify the problem.	1
	(d)	What is the importance of primary key in a table? Explain with example.	1
	(e)	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	2
		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 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 ?	1
Q2	(a)	Name the constraints(4) which can be added at both the levels.(table and column).	1
	(b)	What are different types of SQL functions? Explain and give examples.	2
	(c)	Consider the table Hospital given below.          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 Write commands in SQL for (i) to (xii)	8
		(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’ ;
Q3	(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	2
	(b)	In a database there are two tables ‘LOAN’ and ‘BORROWER’ as shown below:                                                       LOAN Loan_number Branch_name Amount L-170 Downtown 3000 L-230 Redwood 4000 L-260 Perryridge 1700                      BORROWER Customer_Name Loan_no Jones L-170 Smith L-230 Hayes L-155	2
		(i)                  Identify the primary key column in the table LOAN. (ii)                How many rows and columns will be there in the natural join of these two tables?
	(c)	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   Consider  the tables PEOPLE and PROPERTIES given below:                 PEOPLE Name Phone PID Aisha 9411223344 1 Karan 9422114455 2 Rosy 9433112244 3	3
		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 Rosy. (iii)               SELECT Name, Phone, Farm_Name FROM PEOPLE  left join PROPERTIES On PEOPLE.PID = PROPERTIES .PID;

Name	Phone	PID	sPID	Farm_name
Aisha	9411223344	1	1
Karan	9422114455	2	-	-
Rosy	9433112244	3	2
Rosy	9433112244	3	3
Rosy	9433112244	3	4
-	-	4	5