COURSES OFFERED:

GATE CLASS ROOM COACHING LONG TERM COACHING SHORT TERM COACHING CORRESPONDANCE COACHING CLASS ROOM TEST SERIES ONLINE TEST SERIES
IES

LONG TERM CLASS ROOM COACHING CORRESPONDANCE COACHING

PSUS

BSNL JTO MANAGEMENT TRAINEES JAO AP GENCO , AP TRANSCO ELECTRICITY BOARD IN ALL STATES DRDO BHEL TTA ECET
ALL COMPETITIVE EXAMS ETC.,

CONCESSION OFFERS:

FREE COACHING FOR CLASS TOP TWO STUDENTS. FEE CONCESSION BASED ON MERIT.

ADDRESSES OF OUR INSTITUTIONS:

HYDERABAD SAIMEDHA COACHING CENTRE G-12,SWARNA JYANTHI COMPLEX, BESIDE MYTHRIVANAM, AMEERPET. PINCODE : 500 038. CONTACT NOS : 9490272227 , 040 23731234.

BANGLORE SAIMEDHA COACHING CENTRE # 102 , CENTRAL COMPLEX, RAJIVGANDHI CIRCLE, SHESHADRI PURAM, NEAR MAGESTIC, BANGLORE. CONTACT NOS : 09035375436 , 09482072960.

TIRUPATHI SAIMEDHA COACHING CENTRE 18-1-291/1,BHAVANI NAGAR, BESIDE TTD ADMINISTRATIVE BUILDING PINCODE : 517 101. CONTACT NOS : 9989133304 , 0877 2220213.

VIJAYAWADA SAIMEDHA COACHING CENTRE RAMA KRUPA COMPLEX, FIRST FLOOR,

OPP ANDHRA BANK, RING ROAD BRANCH, BENZ CIRCLE, VIJAYAWADA. PINCODE : 520010. CONTACT NOS : 9490768873 , 0866 2493316.

DATABASES
1. Which of the following is a serious problem of file management systems (a) Difficult to update (b) Lack of data independence (c) Data redundancy (d) Program dependence (e) All of these 2. Which of the following is an advantage of the database approach (a) Elimination of data redundancy (b) Ability to associate related data (c) Increases security (d) Program/data independence (e) All of these 3. Which of the following hardware components is the most important to the operation of a database management system? (a) High resolution video display (b) Printer (c) High speed, large-capacity disk (d) Plotter 4. Database management systems are intended to (a) Eliminate data redundancy (b) Establish relationship among records in different files (c) Manage file access (d) Maintain data integrity (e) All of these 5. Which of the following is not the responsibility of the utilities component of DBMS software? (a) Creating the physical and logical designs (b) Removing flagged records for deletion (c) Creating and maintaining the data dictionary (d) Monitoring performance 6. Which of the following is a type of DBMS software? (a) Database manipulation language (b) Query language (c) Utilities (d) Report writer (e) All of these 7. A data dictionary doesnt provide information about (a) Where data is located (b) The size of the disk storageFREE demo class for GATE on 20 sep(sunday) at 10 AM,

(c) S academy, G12, swarnajayanthi complex, ameerpet. (d) contact: 9490272227, 23731234. (e) Who owns or is responsible for the data (f) How the data is used 8. Which two files are used during operation of the DBMS (a) Query languages and utilities. (b) Data manipulation language and query language (c) Data dictionary and transaction log (d) Data dictionary and query language 9. Which of the following is a not characteristic of a relational data base model? (a) Tables (b) Treelike structure (c) Complex logical relationships (d) Records. 10. Which of the following contains a complete record of all activity that affected the contents of a database during a certain period of time? (a) Report writer (b) Query language (c) Data manipulation language (d) Transaction log 11. Which of the following is a database administrators function? (a) Database design (b) Backing up the database (c) Performance monitoring (d) User co-ordination (e) All of these 12. The data base administrator is, in effect, the coordinator between them ___ and the ____ (a) DBMS, database (b) Application program, database (c) Database, users (d) Application programs, users 13. Which of the following is not true of the traditional approach to information processing? (a) These is common sharing of data among the various applications (b) It is file oriented (c) Programs are dependent on the files (d) It is flexible 14. The way a particular application views the data from the database that the application uses is a (a) Module (b) Relational model (c) Schema (d) Subschema 15. Which of the following is true of the data manipulation language (DML)? (a) It refers to data using physical addresses (b) It cant interface with high-level programming language (c) It is used to define the physical characteristics of each record (d) None of these 16. A tuple is equivalent to

(a) Record (b) Field (c) File (d) Database 17. Which of the following is not a logical data base structure? (a) Tree (b) Relational (c) Network (d) Chain 18. The logical data structure with a one-to-many relationship is a (a) Network (b) Tree (c) Chain (d) Relationship 19. The database environment has all of the following components except (a) Users (b) Separate files (c) Data model (d) DBA 20. Which two files are used during operation of the DBMS? (a) Query languages and utilities (b) Data manipulation language and query language (c) Data dictionary and transaction log (d) Data dictionary and query language 21. Generalized database management systems do not retrieve data to meet routine request (a) True (b) False (c) Cant say (d) None 22. Data items are composed of data elements (a) True (b) False (c) Cant say (d) None 23. Data items grouped together for storage purposes are called a (a) Record (b) Title list (c) List (d) String 24. One approach to standardizing storing of data (a) Mis (b) CODASYL specification (c) Structured programming (d) None 25. An access path is provided by the file key (a) True (b) False (c) Cant say (d) None 26. Embedded pointer provides (a) A secondary access path (b) A physical record key (c) An inverted index (d) All of these 27. Updating a database means (a) Revising the file structure (b) Reorganizing the database (c) Modifying or adding record occurrences (d) All of these 28. A schema describes (a) Data elements (b) Records and files (c) Record relationship (d) All of these 29. One data dictionary software package is called (a) DB / DC dictionary (b) Total (c) Access (d) All of these 30. E-R model is used in (a) Logical design (b) Conceptual design (c) Physical design (d) Both (a) & (b)

31. Cardinality ratios are used in (a) Unary relationship (c) Ternary relationship

(b) Binary relationship (d) None of these

32. A minimum cardinality of zero specifies (a) No participation (b) Partial participation (c) Total participation (d) Zero participation 33. What rule or constraint governing the department and employee relationship is expressed as one department can have a maximum of 100 employees? (a) Existence dependency (b) Overlap constraint (c) Covering constraint (d) Cardinality rule 34. Role names or role indicators are necessary for (a) Many too many relationships (b) Recursive relationships (c) Aggregation / Generalization relationships (d)None of these 35. The number of entities participating in the relationship is known as the (a) Maximum cardinality (b) Composite identifiers (c) Degree (d) None 36. A constraint showing the maximum number of entities that can occur on a side of a relationship is called the (a) Degree (b) Maximum cardinality (c) Instance (d) None 37. An M : N relationship is decomposed into (a) Two l : 1 relationship (b) A l : l relationship and a l : M relationship (c) Two l : N relationship (d) None 38. What is not true about weak entity? (a) They do not have key attributes (b) They are the examples of existence dependency (c) Every existence dependency results in a weak entity (d) Weak entity will have always discriminator attributes 39. For which one of the following entities needs mandatory participation in the relationship? (a) Strong entities that participate in 1: 1 relationship. (b) Strong entity on N side relationship in 1 : N relationship (c) Weak entity in identifying relationship (d) Strong entity in identifying relationship 40. Participation constraints cant be represented for (a) Unary relationship (b) Binary relationship (c) Ternary relationship (d) None 41. The relationship between owner entity set and the weak entity set. (a) Many to many (b) One to many (c) Many to one (d) One to one 42. A relationship among entities of the same class is called a (a) Binary relationship (b) Weak relationship

(c) Recursive relationship (d) None 43. Descriptive attributes are used to (a) Record information about participating (b) Record information about relationship (c) Record information about data (d) Record information about attributes 44. Derived attribute is attribute that represents a value that is derivable from (a) Only from a single attribute in an entity (b) It can be derived from two or more attributes in an entity. (c) It can be derived from a different entity (d) All 45. A fan trap may exit in ER model, if (a) Two or more one to one relationship fan out from same entity (b) Two or more one to many relationship fan out from same entity (c) Two or more many to one relationship fan out from same entity (d) Two or more many to many relationship fan out from same entity 46. A chasm trap may exit in ER model, if (a) One or more relationships with maximum cardinality of N (b) One or more relationships with maximum cardinality of 1 (c) One or more relationships with minimum cardinality of 1 (d) One or more relationships with maximum cardinality of 0 47. Select * from customer A, customer B where A. customer Number = B. customer Number; is an example of a (a) Many to many relationship (b) No recursive relationship (c) Recursive relationship (d) None 48. All 1 N relationships in E R model is implemented in relational model as (a) Relation corresponding to 1 side is modified to include foreign key of the relation on the N side (b) Relation corresponding to N side is modified to include foreign key of the relation on the 1 side (c) Primary keys are added on both sides (d) Foreign key are added on both sides 49. A database management system (a) Allows simultaneous access to multiple files (b) Can do more than a record management system (c) Is a collection of programs for managing data in a single file (d) Both (a) & (b) 50. In a large DBMS (a) Each user can see only a small part of the entire data store. (b) Each subschema contains every field in the logical schema.

(c) Each user can access every subschema. (d) All of these

51. A logical schema (a) Is the entire database (b) Is a standard way of organizing information into a accessible part (c) Describe how data is actually stored in disk (d) None of these 52. Subschema can be used to (a) Create very different personalized views of the same data (b) Present information in different formats (c) Hide sensitive information by omitting of fields from the subschema description (d) All of these 53. Goals for the design of the logical schema include (a) Avoiding data inconsistency (b) Being able to construct queries easily (c) Being able to access data efficiently (d) All of these 54. A top to bottom relationship among the items in a database is established by (a) Hierarchical schema (b) Network schema (c) Relational schema (d) All 55. In relational schema, each type is divided into (a) Relations (b) Domains (c) Queries (d) None 56. A Network scheme (a) Restricts the structure to a one to many relationships. (b) Permits many to many relationships (c) Stores data in tables (d) None 57. A transparent DBMS (a) Cant hide sensitive information from users (b) Keep its logical structure hidden from users (c) Keep its physical structure hidden from users (d) Both (b) & (c) 58.
X Y

The diagram represents what inference rule (a) Inclusion rule (b)Transitivity rule (c)Augmentation rule (d)Union rule

59. Decomposition rule is (a) XZ YZ XY (b) X Y , Y Z X YZ (c) X YZ XY,XZ (d) X y , WY Z WX Z (e) None 60. A relation R (ABCDEFG) with functional dependencies set F= A BC, AB DE, D EF, FA Find the no. of candidates key (a) 2 (b) 3 (c) 4 (d) 5 61. Conceder a schema R (ABCDEF) with dependence A BC, BC D, D EA, How many no. of non prime attributes are there in the above relation R. (a) 0 (b) 2 (c) 1 (d) 4 62. A relation R (ABC) having the tuples (1,2,3) (4,2,3) and (5,3,3).which of the following dependencies can you infer does not hold over the relation R. (a) A B(b) BC A(c) B C(d) AC B (e) None 63. A relation R (ABCDEFG) with functional dependencies set F A C, B D, C E, DE E A, FB Find the no. of candidate keys (a) 2 (b) 4 (c) 6 (d) 9 (e) None

64. Given the following relation instance X 1 1 4 3 Y 4 5 6 2 Z 3 3 3 2

Which of the following functional dependencies are satisfied by the instance? (a) XY Z and Z Y (b) XY Z and Z X (c) XZ X and Y Z (d) XZ Y and Y Z (e) None 65. R (ABCDEFGH) with functional dependence F A C, B D, E F, G H, CG How many no. of candidate keys are there (a) 1 (b) 2 (c) 3 (d) 4 66. What is the normal form of the above relation (a) 1NF (b) 2NF (c) 3NF (d) BCNF (e) None 67. A relation R (AB) and primary key is A and B is a foreign key refining the same relation R which of the following row sequence can be inserted into R. (b) (a1,null) (a2,a1) (a4,a3) (a3,a2) (a) (a1,a2) (a2,a3) (a3,a4) (a4,a5) (c) (a1,null) (a1,a2) (a2,a3) (a3,a2) (d) None of these 68. Suppose that we have a relation schema R (A,B,C) representing a relationship between two entity sets with keys A and respectively and suppose that R has the functional dependencies AB BA What FDs imply about the relationship.

(a) One to many (c) Many to one

(b) One to one (d) Many to many

69. Relation R (ABCD) with AB as primary key, mention functional dependency so that R in 1NF but not in 2NF. (a) AB C (b)AB D (c) A C (d) AB CD 70. A relation R (ABCDE) and the set of functional dependencies on R is AB CD, ABC E, C A. Find the no. of candidate keys. (a) 1 (b)2 (c)3 (d) 4 71. R (ABCDE) and F is AB BC D D BC DE Find the no. of redundant functional dependencies in F. (a) 1 (b)2 (c)3 (d) 4 72. Relation R (ABCD) with AB as primary key , mention functional dependency so that R in 2NF but not in 3NF (a) D C (b)AB C (c) AB D (d)A B 73. R (ABC) with functional dependency B c; if a is a candidate key for R, under what conditions R to be in BCNF? (a) B is not a key for R (b) B is a key for R (c) C is a key for R (d) BC is a key 74. R (ABCDE) with F= A DE, C D, E AB What is the normal form of this relation? (a) 1NF (b) 2NF (c) 3NF (d) BCNF 75. A relation R (ABCDE) and the dependency set F AB CD, C DE, A F. Find the normal form of this relation (a) 2NF (b)1NF (c) 3NF (d) BCNF

(e) None

(e) None

76. A relation R (ABCDE) with the set F A CE, B D, C AD, BD EF Closure of AB is (a) ABCE (b) ACED (c) ABCD (d) None 77. A relation R (ABCDE) and the dependency set F AB BC D DE Can we decompose the above relation into BCNF without violating dependency Preservation (a) Yes (b) No (c) Cant say (d) None 78. What is the normal form of the above relationship (a) 1NF (b) 2NF (c) 3NF (d) BCNF 79. A relation R is in BCNF and at least one of its key consists of a single attribute , what is the normal form of the relation (a) BCNF (b) 4NF (c) 5NF (d) Cant say (e) None 80. R (ABCDEF) with FD set F ABC DE, BC D, EF And is properly decomposed into BCNF. Then the no. of foreign keys in decomposed relations. (a) 3 (b) 2 (c) 4 (d) 1

81. A relation R

P P1

Q Q1 Q2 Q3 Q1 Q4

R R1 R2 R2 R3 R4

S S1 S1 S2 S1 S2

P2 P3

What is the normal form of this relation? (a) 1NF (b) 2NF (c) 3NF (d) None 82. If the above relation is converted to BCNF what will be the no. of rows in the resulting relation (a) 10 (b) 12 (c) 14 (d) 16 (e) None 83. The following your tuples in a relation R with three attributes ABC : (1,2,3),(4,2,3),(5,3,3,),(5,3,4).which of the following functional and multilevel dependencies can you infer does not hold over relation R. (a) A B (b) BC A (c) B C (d) B C (e) None 84. A relation R having five attributes (ABCDE), which of the following instance (a,2,3,4,5),(2,a,3,4,5),(a,2,3,6,5)(a,2,3,6,6) which of the following FDs or Multi valued dependencies cant be inferred from the above instance (a) A BC (b) BC D (c) C DE (d) CD E 85. Every binary relation is in ______normal form (a) BCNF (b)3NF (c) 4NF (d) PJNF (e) None 86. R (ABCD) is a relation. Which of the following doesnt have either lossless join or dependency preserving BCNF decompositions? (a) A B , B CD (b) AB C ,C D (c) A BC , C D (d) AB CD , C A (e) None

87. F =

X YZ, Y XZ, ZX

How many no. of minimal and canonical covers are possible respectively (a) 2,1 (b)1,2 (c)2,2 (d)1,1 88. Consider the relation R (ABCDE) and the FDs A B, C D, AE Is the decomposition R into (ABC) (BCD) (CDE) is? (i) Loss less (ii) lossy (ii) depending preserving (iv) not depending preserving (a) (i) & (iii) (b) (i) & (iv) (c) (ii) & (iii) (d) None 89. A relation R (ABCDE) with FD set F = A BC C DE DE And the decomposition p = R1 (ABCD), R2 (DE) is (i) Loss less (ii) Lossy (iii) Dependency preserving (iv) Not dependencies preserving (a) (i) & (iii) (b) (i) & (iv) (c) (ii) & (iii) (d) (ii) & (iv) 90. A relation R (ABCDEFGHIJ) with FD set AB C, A DE, B F, F GH, D IJ And decomposition of R is R1 (ABCD), R2 (DE), R3 (BF), R4 (FGH), R5 (DIJ) which of the following is true?

(i) Loss less (ii) Lossy (iii) Not dependency preserving (iv) Dependency preserving (a) (i) & (ii) (b) (i) & (iv) (c) (ii) & (iii) (d) (ii) & (iv) (e) None 91. R (ABCDE) and F = AB CDE C D, E A. And is decomposed into BCNF. Which of the following best combination can be achieved? (i) Loss less (ii) Lossy (iii) Not dependency preserving (iv) Dependency preserving (a) (i) & (ii) (b) (i) & (iv) (c) (ii) & (iii) (d) (ii) & (iv) (e) None 92. R (ABCDE) with FD set A BCD, BC D, DB (a) Is the above relation can be decomposed into 2NF without violating dependency perseveration (b) Is the above relation can be decomposed into 3NF without violating dependency perseveration (a) Yes , yes (b) Yes , no (c) No , yes (d) No , no 93. Consider the set of fractioned dependency F: PQ R, P Q, S PQ, S T. G: P QR, S PT. (a) F covers G (b)G covers f (c) F & G are equivalent (d)Cant say (e)None 94. Any binary relation is in 3NF (a) True (b) False (c) Cant say (d) None 95. Normalization of database is used to (a) Eliminate redundancy (b) Improve security (c) Improve efficiency (d) Minimize errors 96. If a relation schema is in BCNF , then it is also in (a) 1NF (b)2NF (c)3NF (d)None

97. Given functional dependencies X W, X Y, Y Z, Z PQ. Which of the following doesnt hold good? (a) X Z (b)W Z (c)X WY (d)None 98. A functional dependency of the form X Y is trivial if (a) Y X (b) Y X (c) X Y (d) X Y and Y X 99. Which normal form is considered adequate for normal relationship database design (a) 2NF (b) 3NF (c) 4NF (d) 5NF 100. Let R = (A,B,C,D,E,F) be relation schema with the following dependencies C F, E A, EC D, A B. Which of the following is a key for R? (a) CD (b) EC (c) AE (d) AC 101. Consider the schema R = (STUV) and the dependencies S T, T U, U V, V S. Let R = (R1 and R2) be a decomposition such that R1 R2 =. The decomposition is (a) Not in 2NF (b) In 2NF but not in 3NF (c) In 3NF but not in BCNF (d) Both in 2NF and 3NF 102. Any binary relation in BCNF (a) True (b) False (c) Cant say (d) None 103. Every Boyce codd normal form decomposition is (a) Dependency preserving (b) Not dependency preserving (c) Need be dependency preserving (d) None 104. E R modeling technique is a (a) Top down approach (b) Bottom up approach (c) Left right approach (d) None 105. Relations produced from an E R model will always be in

(a) First normal form (b) Second normal form (c) Third normal form (d) Fourth normal form 106. Student and courses enrolled, is an example of (a) One to one relationship (b) One to many relationship (c) Many to one relationship (d) Many to many relationship 107. Entity set transaction has the attributes transaction number, date, amount entity set account has the attributes account numbers, customer name, balance identity the discriminator of the weak entity (a) Account number (b) Transaction number (c) Account number, date (d) Date 108. Identify the primary key of the weak entity in the above problem (a) Account number (b) Account number, transaction number (c) Account number, date (d) Account number, date 109. Functional dependencies are generalization of (a) Key dependencies (b) Relation dependencies (c) Database dependencies (d) None of these 110. A primary key, if combined with a foreign key creates (a) Parent child relationship between the tables that connect them (b) Many many relationship between the tables that connect them (c) Network model between the tables connect them (d) No0ne of these 111. A relation model which allows non atomic domain is (a) Nested relation model (b) Non atomic data model (c) Hierarchical data model (d) None of these 112. Minimum balance of an account is 2000 Rs. This is (a) Integrity constraint (b) Referential constraint (c) Over defined constraint (d) Feasible constraint 113. Managers salary details are hidden from the employee this is (a) Conceptual level data hiding (b) Physical level data hiding (c) External level data hiding (d) None 114. A trigger is a (a) student that enables to start any DBMS (b) student that is executed by the user when debugging an application program. (c) Condition the system tests for the validity of the database user (d) Student that is executed automatically by the system as a side effect of a modification to the details

115. Let R(a,b,c) and S(d,e,f) be two relation in which d is the foreign key of S that refers to the primary key of R. consider the following four operations R and S (i) Insert into R (ii) Insert into S (iii) Delete from R (iv) Delete from S Which of the following is true about the referential integrity constraint above? (a) None of these can cause its violation (b)All of these can cause its violation (c) Both (i) & (iv) can cause its violation (d)Both (ii) & (iii) can cause its violation 116. Match the following A .Secondary index i .Functional dependencies B .Non procedural QL ii .B True C .Closure of a set of iii .Domain calculus attributes D .Natural join iv .relati0onal algebraic operations (a) A ii , B iii , C i , D iv (b) A iii , B ii , C iv , D i (c) A iii , B i , C ii , D iv (d) None 117. Given two union compatible relations R1 (A, B) and R2(C, D), what is the result of the operation R1A = CAB = DR2? (a) R1 R2 (b) R1 R2 (c) R1 R2 (d) R1 R2 118. Choose the correct statement (a) Network models are complicated by physical keys, but the relational model is faster because it uses logical keys (b) Network models are complicated by logical keys, but the relational model is faster because it uses physical keys (c) Network models are complicated by logical keys, but the relational model is slower because it uses physical keys (d) Network models are complicated by physical keys, but the relational model is slower because it uses logical keys 119. Consider the join of a relation S. If R has m tuples and S has n tuples , then the max and min sizes of the join respectively are (a) m + n and o (b) mn and o (c) m + n and |m - n| (d) mn and m+n 120. Embedded pointer provides (a) Secondary access path (b) Physical record key (c) Inverted index (d) All of these

121. An owner member set in the CODASYL specification may have (a) Only on owner but many owner occurrences. (b) Only one member but many member occurrences (c) More than one member but only one occurrences per member (d) All of these 122. A record in 3NF is better than a record to 1NF a relation mathematics viewpoint (a) True (b) False (c) Cant say (d) None 123. What is the serious problem(s) of the file management system (a) Data redundancy (b) Difficult to update (c) Program dependence (d) All of these 124. Which of the following is logical database structure? (a) Network (b) True (c) Chain (d) All of these 125. Which two files are used during the operation of DBMS? (a) Data dictionary and query language (b) Query language and utilities (c) DML and query language (d) None 126. A relational data base management package manages data in more than one file at once. How does it organize these files? As (a) Tables (b) Relations (c) Tuples (d) Both (a) and (b) 127. Which of the following is/are correct (a) An SQL query automatically eliminates duplicates. (b) An SQL query will not work If there are no indexes on the relations (c) SQL permits attributes name to be repeated in the same relation. (d) None of these 128. For a database relation R(a,b,c,d) , where the domains of a,b,c,d include only atomic values , only the following functional dependencies and those that can be inferred from them hold ac bd The relation is (a) In first normal form but in second normal form (b) In second normal form but not in 3NF. (c) In 3NF (d) None 129. Data security threads include (a) Privacy invasion (b) Hardware failure (c) Fraud lent manipulation of data (d) All of these 130. If a field size is too small for the longest piece of data to be entered (a) The data base program will freeze (b) The field will automatically expand (c) part of the data will be cutoff (d) None

131. Data integrity control (a) Is used to set upper and lower limits on numeric data. (b) Requires the use of passwords to prohibit unauthorized access to the file (c) Has the data dictionary to keep the data and time of lost access, last back-up, and most recent modification for all files. (d) None 132. Consider the relation

A 10 10 11 12 13 14

B b1 b2 b4 b3 b1 b3

C c1 c2 c1 c4 c1 c4

Which one of the following dependencies. May hold in the above relation (a) A B (b)B C (c)C B (d)B A (e)C A 133. Consider a relation ABCDEFG and FDs are AB BC DE AEG G Then AC
+

is

(a) ABC (b) ABCD (c) ABCDE (d) ABCDEG + for R (ABCDE) and FDs are 134. Find B A BC CD E BD EA (a) B,A (b) A,B,C (c) ABCDE (d) B,D 135. Consider a relation with R(ABCDEF) and FDs are AB C BC AD DE CF B Find

AB

(a)

ABCDEF

(b) ABCDE

(c) ABCD

(d)None

Common data for questions from 136 to 143: Employee (empID, fName, Iname, address, DOB, sex, pos, deptNo) Department (deptNo, dataName, mgrEMPID) Project (projNo, projName, deptNo) Workson (empID, projNo, hours worked) 136. List all employees in alphabetical order of surname and within surname, first name. Ans: select * from Employee ORDER by IName, fName. 137. List all the details of employees who are female select * from Employee where sex=F; Ans: 138. Last the names and addresses of all employees who are managers Ans: select fname, IName, address from Employee e, department d where e.empID = d.mgrEmpId; 139. Produce a list of names and addresses of all employees who work for the IT department. select e.IName, e.address from employee e, Department d where Ans: e.deptNo = d.deptNo and d.deptName=IT; 140. produce a complete list of all managers who are due to retire this year in alphabetical order of surname Ans: select IName from employee e, Department D where e.empID=d.mgrEmpID And date-part (year, DOB) <date-part (year, Date (2011-1-01));

141. Find out how many employees are managed by James Ans: select count (*) from employees e1, e2, Department D. Where e1.IName=James And e1.empID=d.mgrEmpID and d.deptNo=e2deptNo; 142. Produce a report of the total hours worked by each by each employee arranged in order of department number and within department, alphabetically by employee surname. Ans: select e. IName, e. fName, e.deptNo, sum (w.hours worked) from employee e, project p, workson w where e.deptno=e.IName.

143. For each project on which more than two employees worked. List the project number, name & the no. of employees who work on that project. Ans: select e.projNo, e.projName, count (*) from project p, workson w where p.projNo=w.projNo group by e.projNo, e.projName having count (*)>2; Common data for Q.144 to Q.148: (1) Vender_master(vencode, venname, venaddr1, venaddr2, venaddr3, tel_no) (2) Order_master(orderno, odate, vencode, ostatus, del_date) (3) Create table itemfile(itemcode, itemdesc, p_category, qty_hand, re_level, max_level constraint max check(max_val <500), itemrate.constraint un_item unique(itemdesc)); (4) Order_detail(orderno constraint foreign key references order_master(orderno), itemcode constraint foreign key references itemfile(itemcode), qty_ord, qty_del); are 4 tables in our database then 144. What is the result of Select distinct (a.orderno) from order_detail a where 3<= (select count (itemcode) from Order_detail where a.orderno=orderno); (a) Every orderno that has three or more items is listed. (b) Every orderno that has less than three items is listed (c) Every orderno that has three or less than three is listed (d) None 145. Select itemdesc, max_level, qty_ord, qty_deld, from itemfile, order_detail where (itemfile.max_level<order_detail.qty_ord) and (itemfile. Itemcode=order_detail. itemcode) (a) Provides max_level of the items belonging to itemfile table are lesser than qty_ord belonging to order_detail table (b) It joins the rows of itemfile table to that of order_details table provided max_level of the items belonging to itemfile are greater than that of qty_ord belonging to order_detail table (c) It displays the details where max_level less than qty ordered. (d) Both a and c 146. Select a.itemcode, a.itemdesc, a.qty_hand. B.max_level from itemfile a, itemfile b wherea.qty_hand<b.max_level and a.p_category=spares and a.itemcode=b.itemcode; this displays

(a) Only those rows from the itemfile table where qty_hand is less than max_level and belongs spares category. (b) The details from itemfile whose max_level less than qty_hand. (c) The details from itemfile where qty_hand less than max_level (d) None 147. Select a.orderno, vencode, qty_ord, qty_deld from order_master a, order_detail where a orderno=order_details. Orderno (+). (a) It retrieve rows from under order_master table which dont have any matching records in the order detail table (b) It will alsoretrieve rows from order_master table which dont have any matching records in the order detail table (c) Either (a) or (b) (d) None 148. Select * from vendor_master where vencode =(select vencode from order_master where orderno=(select orderno from order_detail where qty_ord= 100 and itemcode=i203)); (a) Retrieve the details of the vendor who has ordered qty_ord=100 of the type item i203 (b) Retrieve the details of the vendor qty_ord who has order expect the item code i203 (c) Vendor details from the vendor_master table who has ordered qty 100 (d) None 149. Table aliases are used to make multiple table queries shorter and readable (a) True (b) False (c) Cant say (d) None

Common data for Q. 150 Employee (Fname, minit, Lname, ssn, Bdata, addr, sex, salary, superssn, dno) Department (dname, dnumber, mgrssn, mgrstartdata) Dependent (Essn, dependent_name, sex, Bdaterelation) Workson (Essn, pno, hours) project (pname, pnumber, plocation, dnum) Dept_Locations (Dnumber, Dlocation) 150. Select Fname, Lname from employee where ((select pno from works_on where ssn=Essn)contains (select pnumber from project where Dnum=5)); (a) Retrieve the name of each employee who works on all the depart nos. (b) Retrieve the name of each employee who works on all projects. (c) Retrieve the name of each employee who works on all projects controlled by department number 5.

(d) None

KEY FOR DATABASES

1-e 11-e 21-b 31-b 41-b 51-c 61-c 71-a 81-d 91-e 101-d 111-a 121-a 131-c 141

2-e 12-e 22-b 32-b 42-c 52-d 62-b 72-a 82-b 92-a 102-a 112-a 122-a 132-b 142-

3-c 13-a 23-a 33-d 43-b 53-d 63-e 73-b 83-a 93-c 103-c 113-c 123-d 133-d 143-

4-e 14-d 24-c 34-d 44-d 54-a 64-c 74-b 84-c 94-a 104-a 114-c 124-d 134-d 144-a

5-a 15-d 25-a 35-c 45-b 55-b 65-a 75-a 85-d 95-a 105-c 115-d 125-a 135-b 145-d

6-e 16-a 26-a 36-b 46-d 56-b 66-a 76-e 86-d 96-c 106-d 116-a 126-d 136146-a

7-b 17-d 27-d 37-c 47-c 57-c 67-e 77-b 87-c 97-b 107-b 117-d 127-d 137147-b

8-c 18-b 28-d 38-c 48-b 58-c 68-b 78-a 88-d 98-a 108-b 118-a 128-a 138148-a

9-b 19-b 29-a 39-c 49-d 59-c 69-c 79-b 89-a 99-b 109-a 119-b 129-a 139149-a

10-d 20-c 30-b 40-c 50-a 60-a 70-b 80-b 90-b 100-b 110-a 120-a 130-c 140150-c