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)