Qnsans Rdbms Eltp Db2

RDBMS Concepts Full Marks : 30 Time : 45 mins. 1. State True or False 8 x1 =8 a) DB2 is a relational database model b) The PRIMARY KEY constraint is a must for all tables c) Normalization helps in reducing the Redundancy in a database d) COMMIT indicates the end of a transaction e) A JOIN fetches data from two or more tables that are related by some condition f) ER-Diagram falls under the category of iconic model. g) SQL is a language used for programming in RDBMS. h) The GRANT command is an example for DML statement 2. Fill in the blanks: 7x1=7 a) Removal of transitive dependency is a feature in _________ NF. b) Collection of eligible values for a column is called _______________. c) The entities in the ER-Diagram are transformed into ______________ in database implementation. d) In ACID property, I stand for ____________________. e) ______________________ Rule is the first rule of Codd. f) In client-server architecture, the database appears at _____________ end. g) A logical unit of work involving a set of database operation is called ________________. 3. Write SQL statements to accomplish the following tasks: 5x3=15 a) List last name, salary, bonus, and commission for all employees with a salary greater than $22,000 and a bonus of $400, or for all employees with a bonus of $500 and a commission lower than $1,900. The list should be ordered by last name. Ans –

SELECT LASTNAME, SALARY, BONUS, COMM FROM EMPLOYEE WHERE SALARY > 22000 AND BONUS = 400 OR BONUS = 500 AND COMM < 1900 ORDER BY LASTNAME

b) Produce a report that lists employees' last names, first names, and department names. Sequence the report on first name within last name, within department name. Ans-

SELECT E.LASTNAME, E.FIRSTNME, D.DEPTNAME FROM EMPLOYEE E, DEPARTMENT D WHERE E.WORKDEPT = D.DEPTNO ORDER BY D.DEPTNAME, E.LASTNAME, E.FIRSTNME c) Produce a list of all employees in department D11 that have an income (sum of salary, commission, and bonus) that is greater than their salary increased by 10 percent. Name the generated column INCOME. List department number, last name, and income. Sort the result in descending order by income. For this problem assume that all employees have non-null salaries, commissions, and bonuses. Ans-

SELECT WORKDEPT, LASTNAME, SALARY+COMM+BONUS AS INCOME FROM EMPLOYEE WHERE SALARY+COMM+BONUS > 1.1*SALARY AND WORKDEPT = 'D11' ORDER BY INCOME DESC