22319 Database Management System Mcq pdf [ Interactive SQL & Advance SQL: SQL Performance Tuning ]

22319 mcq

Subject	subject details
Program:	Diploma in computer engineering
Program Code	CO
Scheme	I
Semester	3
Course:	Database Management System
Course Code	22319

 Unit 03 – Interactive SQL & Advance SQL: SQL Performance Tuning        Marks:-14 
"3.1 In-built functions: string, arithmetic
3.2 Date and time, Aggregate functions
3.3 Queries using Group by, having and order by clause, Joins-inner and outer join, sub queries
3.4 Views: concept of view, the create view command, updating views ,Views and joins, sub queries, dropping views,
3.5 Sequences: creating sequences, altering sequences, Dropping sequences
3.6 Indexes: index types, creating of an index: simple unique
3.7 Composite index, dropping indexes
3.8 Synonyms: creating Synonyms, Dropping synonyms" 

1. Which of the following creates a virtual relation for storing the query? 
a) Function 
b) View 
c) Procedure 
d) None of the mentioned 
Answer: b 
"Explanation: Any such relation that is not part of the logical model, but is made visible to a user as a virtual relation, is called a view." 

2. Which of the following is the syntax for views where v is view name? 
a) Create view v as “query name”; 
b) Create “query expression” as view; 
c) Create view v as “query expression”; 
d) Create view “query expression”; 
Answer: c 
Explanation: <query expression> is any legal query expression. The view name is represented by v. 

3.SELECT course_id FROM physics_fall_2009 WHERE building= ’Watson’; 
Here the tuples are selected from the view. Which one denotes the view. 
a) Course_id 
b) Watson 
c) Building 
d) physics_fall_2009 
Answer: c 
Explanation: View names may appear in a query any place where a relation name may appear. 

4. Materialized views make sure that 
a) View definition is kept stable 
b) View definition is kept up-to-date 
c) View definition is verified for error 
d) View is deleted after specified time 
Answer: b Explanation: None. 

5. Updating the value of the view 
a) Will affect the relation from which it is defined 
b) Will not change the view definition 
c) Will not affect the relation from which it is defined 
d) Cannot determine 

Answer: a Explanation: None. 

"6. SQL view is said to be updatable (that is, inserts, updates or deletes can be applied on the view)" 
if which of the following conditions are satisfied by the query defining the view? 
a) The from clause has only one database relation 
b) The query does not have a group by or having clause 
"c) The select clause contains only attribute names of the relation and does not have any expressions, aggregates, or distinct specification" 
d) All of the mentioned 
Answer: d 
Explanation: All of the conditions must be satisfied to update the view in sql. 

7. Which of the following is used at the end of the view to reject the tuples which do not satisfy the 
condition in where clause? 
a) With 
b) Check 
c) With check 
d) All of the mentioned 
Answer: c 
"Explanation: Views can be defined with a with check option clause at the end of the view definition; then, if a tuple inserted into the view does not satisfy the view’s where clause condition, the insertion is rejected by the database system." 

8. Consider the two relations instructor and department 
Instructor: 
ID       Name        Dept_name       Salary 
1001  Ted             Finance          10000 
1002  Bob             Music              20000 
1003 Ron            Physics             50000 

Department: 
Dept_name   Building    Budget 
Biology          Watson     40000 
Chemistry      Painter     30000 
Music            Taylor         50000 

Which of the following is used to create view for these relations together? 
"a) CREATE VIEW instructor_info AS SELECT ID, name, building" 
"FROM instructor, department WHERE instructor.dept name= department.dept name;" 
"b) CREATE VIEW instructor_info SELECT ID, name, building FROM instructor, department;" 
"c) CREATE VIEW instructor_info AS SELECT ID, name, building FROM instructor;" 
"d) CREATE VIEW instructor_info AS SELECT ID, name, building FROM department;" 
Answer: a Explanation: None. 

9. For the view Create view instructor_info as 
"If we insert tuple into the view as insert into instructor info values (’69987’, ’White’, ’Taylor’);" 
What will be the values of the other attributes in instructor and department relations? 
a) Default value 
b) Null 
c) Error statement 
d) 0 
Answer: b 
Explanation: The values take null if there is no constraint in the attribute else it is an Erroneous statement. 

10. CREATE VIEW faculty AS SELECT ID, name, dept name FROM instructor;
SELECT ID, name, dept name FROM instructor; F
a) Instructor 
b) Select 
c) View …as 
d) None of the mentioned 
Answer: d 
Explanation: Syntax is – create view v as <query expression>;. 

"11. In ordered indices the file containing the records is sequentially ordered, a is an" 
index whose search key also defines the sequential order of the file. 
a) Clustered index 
b) Structured index 
c) Unstructured index 
d) Nonclustered index 
Answer: a 
"Explanation: Clustering index are also called primary indices; the term primary index may appear to denote an index on a primary key, but such indices can in fact be built on any search key." 

12. Indices whose search key specifies an order different from the sequential order of the file are 
called indices. 
a) Nonclustered 
b) Secondary 
c) All of the mentioned 
d) None of the mentioned 
Answer: c 
Explanation: Nonclustering index is also called secondary indices. 

13. An consists of a search-key value and pointers to one or more records with that 
value as their search-key value. 
a) Index entry 
b) Index hash 
c) Index cluster 
d) Index map 
Answer: a 
Explanation: The pointer to a record consists of the identifier of a disk block and an offset within the disk block to identify the record within the block. 

"14. In a clustering index, the index record contains the search-key value and a pointer to" 
the first data record with that search-key value and the rest of the records will be in the sequential pointers. 
a) Dense 
b) Sparse 
c) Straight 
d) Continuous 
Answer: a 
"Explanation: In a dense nonclustering index, the index must store a list of pointers to all records with the same search-key value." 

"15. In a index, an index entry appears for only some of the search-key values." 
a) Dense 
b) Sparse 
c) Straight 
d) Continuous 
Answer: a 
"Explanation: Sparse indices can be used only if the relation is stored in sorted order of the search key, that is if the index is a clustering index." 

"16. Incase the indices values are larger, index is created for these values of the index. This is called" 
a) Pointed index 
b) Sequential index 
c) Multilevel index 
d) Multiple index 
Answer: c 
Explanation: Indices with two or more levels are called multilevel indices. 

17. A search key containing more than one attribute is referred to as a search key. 
a) Simple 
b) Composite 
c) Compound 
d) Secondary 
Answer: b 
"Explanation: The structure of the index is the same as that of any other index, the only difference being that the search key is not a single attribute, but rather is a list of attributes." 

18. In B+ tree the node which points to another node is called 
a) Leaf node 
b) External node 
c) Final node 
d) Internal node 
Answer: d 
Explanation: Nonleaf nodes are also referred to as internal nodes. 

19. Insertion of a large number of entries at a time into an index is referred to as of the 
index. 
a) Loading 
b) Bulk insertion 
c) Bulk loading 
d) Increase insertion 
Answer: c 
Explanation: Bulk loading is used to improve efficiency and scalability. 

"20. While inserting the record into the index, if the search-key value does not appear in the index." 
a) The system adds a pointer to the new record in the index entry 
b) The system places the record being inserted after the other records with the same search-key values 
c) The system inserts an index entry with the search-key value in the index at the appropriate position 
d) None of the mentioned 
Answer: c 
"Explanation: If the index entry stores pointers to all records with the same search key value, the system adds a pointer to the new record in the index entry." 

21. What is the purpose of the index in sql server? 
a) To enhance the query performance 
b) To provide an index to a record 
c) To perform fast searches 
d) All of the mentioned 
Answer: d 
Explanation: A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes. 

22. How many types of indexes are there in sql server? 
a) 1 
b) 2 
c) 3 
d) 4 
Answer: b 
Explanation: They are clustered index and non-clustered index. 

23. How non- clustered index point to the data? 
a) It never points to anything 
b) It points to a data row 
c) It is used for pointing data rows containing key values 
d) None of the mentioned 
Answer: c 
Explanation: Non clustered indexes have a structure separate from the data rows. A non-clustered index contains the non-clustered index key values and each key value entry has a pointer to the data row that contains the key value. 

24. Which one is true about clustered index? 
a) Clustered index is not associated with table 
b) Clustered index is built by default on unique key columns 
c) Clustered index is not built on unique key columns 
d) None of the mentioned 
Answer: b 
Explanation: Non clustered indexes have a structure separate from the data rows. A non-clustered index contains the non-clustered index key values and each key value entry has a pointer to the data row that contains the key value. 

25. What is true about indexes? 
a) Indexes enhance the performance even if the table is updated frequently 
b) It makes harder for sql server engines to work to work on index which have large keys 
c) It doesn’t make harder for sql server engines to work to work on index which have large keys 
d) None of the mentioned 
Answer: b 
Explanation: Indexes tend to improve the performance. 

26. Does index take space in the disk? 
a) It stores memory as and when required 
"b) Yes, Indexes are stored on disk" 
c) Indexes are never stored on disk 
d) Indexes take no space 
Answer: b 
Explanation: Indexes take memory slots which are located on the disk. 
27. What are composite indexes? 
a) Are those which are composed by database for its internal use 
b) A composite index is a combination of index on 2 or more columns 
c) Composite index can never be created 
d) None of the mentioned 
Answer: b 
Explanation: A composite index is an index on two or more columns of a table. 

28. If an index is the metadata and statistics continue to exists 
a) Disabling 
b) Dropping 
c) Altering 
d) Both Disabling and Dropping 
Answer: a 
Explanation: A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes. 

"29. In index instead of storing all the columns for a record together, each column" 
is stored separately with all other rows in an index. 
a) Clustered 
b) Column store 
c) Non clustered 
d) Row store 
Answer: b 
Explanation: A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes. 
30. A index is the one which satisfies all the columns requested in the query 
without performing further lookup into the clustered index. 
a) Clustered 
b) Non Clustered 
c) Covering 
d) B-Tree 
Answer: c 
Explanation: A covered query is a query where all the columns in the query’s result set are pulled from non-clustered indexes. 

31. The condition allows a general predicate over the relations being joined. 
a) On 
b) Using 
c) Set 
d) where 
answer: a 
Explanation: On gives the condition for the join expression. 

32. Which of the join operations do not preserve non matched tuples? 
a) Left outer join 
b) Right outer join 
c) Inner join 
d) Natural join 
Answer: c 
Explanation: INNER JOIN: Returns all rows when there is at least one match in BOTH tables.

33.SELECT * FROM student JOIN takes USING (ID); 
The above query is equivalent to 
a) SELECT * FROM student INNER JOIN takes USING (ID); 
b) SELECT * FROM student OUTER JOIN takes USING (ID); 
c) SELECT * FROM student LEFT OUTER JOIN takes USING (ID); 
d) None of the mentioned 
Answer: a 
Explanation: Join can be replaced by inner join. 

34. What type of join is needed when you wish to include rows that do not have matching values? 
a) Equi-join 
b) Natural join 
c) Outer join 
d) All of the mentioned 
Answer: c 
Explanation: An outer join does not require each record in the two joined tables to have a matching record. 

35. How many tables may be included with a join? 
a) One 
b) Two 
c) Three 
d) All of the mentioned 
Answer: d 
Explanation: Join can combine multiple tables. 

36. Which are the join types in join condition: 
a) Cross join 
b) Natural join 
c) Join with USING clause 
d) All of the mentioned 
Answer: d 
Explanation: There are totally four join types in SQL. 

37. How many join types in join condition: 
a) 2 
b) 3 
c) 4 
d) 5 
Answer: d 
"Explanation: Types are inner join, left outer join, right outer join, full join, cross join." 

38. Which join refers to join records from the right table that have no matching key in the left table 
are include in the result set: 
a) Left outer join 
b) Right outer join 
c) Full outer join 
d) Half outer join 
Answer: b 
Explanation: RIGHT OUTER JOIN: Return all rows from the right table and the matched rows from the left table. 

39. The operation which is not considered a basic operation of relational algebra is 
a) Join 
b) Selection 
c) Union 
d) Cross product

 Answer: a Explanation: None. 

"40. In SQL the statement select * from R, S is equivalent to" 
a) Select * from R natural join S 
b) Select * from R cross join S 
c) Select * from R union join S 
d) Select * from R inner join S 
Answer: b Explanation: None. 
41. A_ is a query that retrieves rows from more than one table or view: 
a) Start 
b) End 
c) Join 
d) All of the mentioned 
Answer: c 
Explanation: An SQL join clause combines records from two or more tables in a database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining fields from two tables by using values common to each. 

42. A condition is referred to as                      
a) Join in SQL 
b) Join condition 
c) Join in SQL & Condition 
d) None of the mentioned 
Answer: b 
Explanation: An SQL join clause combines records from two or more tables in a database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining fields from two tables by using values common to each. 

43. Which oracle is the join condition is specified using the WHERE clause: 
a) Oracle 9i 
b) Oracle 8i 
c) Pre-oracle 9i 
d) Pre-oracle 8i 
Answer: c 
Explanation: Oracle 9i is a version of the Oracle Database. The i stands for “Internet” to indicate that 9i is “Internet ready”. 

44. How many join types in join condition: 
a) 2 
b) 3 
c) 4 
d) 5 
Answer: d 
"Explanation: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, EQUIJOIN." 

45. Which are the join types in join condition: 
a) Cross join 
b) Natural join 
c) Join with USING clause 
d) All of the mentioned 
Answer: d 
"Explanation: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, EQUIJOIN are the types of joins." 

46. Which product is returned in a join query have no join condition: 
a) Equijoins 
b) Cartesian 
c) Both Equijoins and Cartesian 
d) None of the mentioned 
Answer: b 
Explanation: A Cartesian coordinate system is a coordinate system that specifies each point uniquely in a plane by a pair of numerical coordinates. 

47. Which is a join condition contains an equality operator: 
a) Equijoins 
b) Cartesian 
c) Both Equijoins and Cartesian 
d) None of the mentioned 
Answer: a 
"Explanation: An equi-join is a specific type of comparator-based join, that uses only equality comparisons in the join-predicate." 

48. Which join refers to join records from the write table that have no matching key in the left table 
are include in the result set: 
a) Left outer join 
b) Right outer join 
c) Full outer join 
d) Half outer join 
Answer: b 
Explanation: A right outer join will return all the rows that an inner join returns plus one row for each of the other rows in the second table that did not have a match in the first table. It is the same as a left outer join with the tables specified in the opposite order. 

49. Which operation are allowed in a join view: 
a) UPDATE 
b) INSERT 
c) DELETE 
d) All of the mentioned 
Answer: d 
Explanation: The DELETE statement is used to delete rows in a table. The UPDATE statement is used to update existing records in a table. The INSERT INTO statement is used to insert new records in a table. 

50. Which view that contains more than one table in the top-level FROM clause of the SELECT 
statement: 
a) Join view 
b) Datable join view 
c) Updatable join view 
d) All of the mentioned 
Answer: c 
Explanation: The DELETE statement is used to delete rows in a table. The UPDATE statement is used to update existing records in a table. The INSERT INTO statement is used to insert new records in a table. 

22319 Database Management System Mcq pdf [ Interactive SQL & Advance SQL: SQL Performance Tuning ] pdf download msbte ischeme 3rd sem