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                                   

MONTHLY TEST, 12B-2015 INFORMATICS PRACTICES(MYSQL- PART) ANSWER KEY

MONTHLY TEST, 12B-2015 INFORMATICS PRACTICES(MYSQL- PART)

ANSWER KEY

 I-  a)  COMMIT command.  ( 1mark)                                   

    b) AS is used to give alias name to columns in SELECT clause and to tables in FROM Clause. ( ½ mark)

    Ex: SELECT ename, Salary, Salary*.10 AS IncTax FROM Employ AS E; ( ½ mark for example )

c)Alter is used to do changes(Add/Drop/Modify) on the columns of the tables where as update is to do changes on the rows of the table.

Alter is a DDL Command and update is a DML Command (1mark for each difference 1 X 1 =2M )

d) Yes, A table in a Relational Database can have Multiple Foreign Keys referencing different parent tables.

  ( 1 mark for yes )

e) The SAVEPOINT statement defines a marker in a transaction. These markers are useful in rolling back a transaction till the marker.  ( 1 mark  for correct answer)

f)    The Degree/ no of columns of two tables may not be of same degree or the tables are not of same type. (2 mark for correct reason)

g)  WHERE is used to put a condition on individual row of a table whereas HAVING is used to put condition on individual group formed by GROUP BY clause in a SELECT statement. 

( 1 mark for Difference)

SELECT Department, Count(*)

FROM Employ
WHERE salary>50000

Group BY Department

Having count(Department)>3;

(1 mark for example)

II-  a) Degree =4  Cardinality =4

( 1 mark for correct answer)

b) The property of a relational database which ensures that no entry in a foreign key column of a table can be made unless it matches a primary key value in the corresponding related table is called Referential Integrity.
( 1 mark for correct answer)

c)  Candidate Keys: Admno, Rollno+Class      ( 1 mark )

     Primary Key: Admno (1 mark)

d) i)SELECT * FROM Employ WHERE Department=’History’ and Sex=’F’;
    (1 mark for correct query ½ mark for partially correct)

ii) SELECT Count(Name) FROM Employ
WHERE Name LIKE “_h%”;

(1 mark for correct query ½ mark for partially correct)

  iii) SELECT Name,Age,Salary FROM Employ ORDER BY age DESC, Salary ;

(1 mark for correct query ½ mark for partially correct)

  iv) UPDATE Employ SET Salary=Salary+1000 WHERE  department=’Computer’  and age>40;

(1 mark for correct query ½ mark for partially correct)

       v) Name          Department

    Sharmila         History        (1 mark for correct output)

vi)  Computer  33000             (1 mark for correct output)

6 a) Create Table Bank( BankCode VarChar(30) PRIMARY KEY,Branch VarChar(30),
City VarChar(30) ,Budget Numeric(7,2),CHECK  City in (‘Hyderabad’, ‘Delhi’));
(1 mark for declaration of attributes with correct data types)
(1 mark for constraints)

b)No we cannot change column name with Alter Table. ( 1 mark)
Alter Table BANK MODIFY Budget Numeric(12,2) default 10000000;
(1 mark)

c)

i) SELECT CustNo, CustAddress, SetName FROM Handset, Customer
WHERE Handset.SetCode=Customer.SetNo;

(2 marks for correct query 1 mark for partially correct)

ii) SELECT Customer.* FROM Handset, Customer
WHERE Handset.SetCode=Customer.SetNo AND SetName like ’Talk%’;

(2 marks for correct query 1 mark for partially correct)

iii) SELECT Count(*) FROM Handset, Customer
WHERE Handset.SetCode=Customer.SetNo
AND CustAdress=’Mumbai’ AND TouchScreen=’Y’;

(2 marks for correct query 1 mark for partially correct)