12B-QUESTION PAPER- MONTHLY-TEST, INFORMATICS PRACTICES (MYSQL-UNIT)

KENDRIYA VIDYALAYA OCF AVADI, CHENNAI-54

MONTHLY-TEST,  INFORMATICS PRACTICES (MYSQL-UNIT)

Time allowed: 90Minutes                                             Max. Marks: 35

I - a) Which MySql command is used to make permanent changes on the databases.   1

b) When to use “AS”  operator in MySql statements, give an example                    1

c) What is the difference between the Alter and Update Command?                        2

d) Can a table have multiple Foreign keys                                                                1

e) What is the purpose of SAVEPOINT in SQL                                                       1

f) Mr. Amit want to UNION two tables but he couldn’t  get result, what might be the problem?                                                                                                                                  2

g) Differentiate HAVING and WHERE clause with an example                               2

II  a) Miss Nisha created a table JOURNEY with 3 rows and 5 columns. She added one more row to it and deleted 1 column. What is it cardinality and Degree of the table JOURNEY?                                                                                                             1

b) Explain referential integrity with reference to DBMS.                                          1

c) Consider the table STUDENT and SQL statements                                              2

Admno	RollNo	Class	Student name	Fathers Name
4001	1	XII	Ram	Raj
4002	1	XI	Ravi	Rao
4003	2	XI	Rakesh	Ramana
4004	2	XII	Rajini	Raghav

         Identify Candidate keys and  Primary Keys for the above table

d) Consider the following table Employ, Write SQL Commands for i to iv and output for v and vi   6

No.	Name	Age	Department	Date ofJoin	Salary	Sex
1	Jugal	33	Computer	10/01/97	12000	M
2	Sharmila	31	History	23/03/98	20000	F
3	Sandeep	32	Maths	12/12/96	20000	M
4	Sangeeta	35	History	01/07/99	40000	F
5	Rakesh	42	Maths	05/09/97	25000	M
6	Shyam	50	History	27/06/98	30000	M
7	Shiv Om	44	Computer	25/02/97	21000	M
8	Shalaka	33	Maths	31/07/97	20000	F

i)  Display details of all female history teachers

ii) Count the number of teachers whose name have “h” as second letter

iii) Display Name, age, Salary in the descending order of age and ascending order of salary

iv) Increase the salary of all Computer teachers  above 40 years  by Rs 1000

v) Select Name, Department from Employ where year(DateofJoin) in {96,98} and sex=’F’;

vi) Select Department, Sum(Salary) from Employ Group by Department having count(Department)<3;

III a) Write SQL to Create the following table BANK with specified constraints     2

Column Name	Datatype	Constraint
BankCode	Character of 10 size	Primary Key
Branch	Character of 30 size
City	Character of 30 size	City can have only four values “Hyderabad”,”Delhi”
Budget	Numeric(7,2)

b) Can we change the column name using Alter table command? Change the data type of Budget column of above mentioned BANK Table to Numeric(12,2) and add  default value to 10000000.                                                                                                      2

d) Consider the tables HANDSET AND CUSTOMER

HandSet

SetCode	SetName	TouchScreen	PhoneCost
N1	Talk 2G	N	5000
N2	Talk 3G	Y	8000
B1	SamWaad	N	14000

Customer

CustNo	SetNo	CustAddress
1	N2	Delhi
2	B1	Mumbai
3	N2	Mumbai
4	N1	Kolkata
5	B1	Delhi

i)                   Display the CustNo, Custaddress and corresponding Setname                  2

ii)                Display the customer details for each customer who uses a Talk handset  2

iii)              Count the number of customers in Mumbai who purchased Touchscreen set          2

III. a) Explain the following terms:                                                                          -5                                                                             i) RDBMS                     ii) DROP TABLE                        iii) ROLLBACK