Database MCQ Number 00874

Database MCQ Set 1

1. What is the purpose of 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

2. How many types of indexes are there in sql server?
a) 1
b) 2
c) 3
d) 4

3. 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

4. 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

5. 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

6. 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

7. 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

8. If an index is _____ the metadata and statistics continue to exists
a) Disabling
b) Dropping
c) Altering
d) Both Disabling and Dropping

9. 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

10. 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

Database MCQ Set 2

1. Which statements are correct regarding indexes?
a) When a table is dropped, the corresponding indexes are automatically dropped
b) For each DML operation performed, the corresponding indexes are automatically updated
c) A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index
d) All of the mentioned

2. You executed the following SQL statements in the given order:

CREATE TABLE orders
(order_id NUMBER(3) PRIMARY KEY,
order_date DATE,
customer_idnumber(3));
 
INSERT INTO orders VALUES (100,'10-mar-2007,,222);
 
ALTER TABLE orders MODIFY order_date NOT NULL;
 
UPDATE orders SET customer_id=333;
 
DELETE FROM order;

The DELETE statement results in the following error:
ERROR at line 1: table or view does not exist
What would be the outcome?
a) All the statements before the DELETE statement would be rolled back
b) All the statements before the DELETE statement would be implicitly committed within the session
c) All the statements up to the ALTER TABLE statement would be committed and the outcome of UPDATE statement would be rolled back
d) All the statements up to the ALTER TABLE statement would be committed and the outcome of the UPDATE statement is retained uncommitted within the session

3. Evaluate the following statements:

CREATE TABLE digits
(id NUMBER(2),
description VARCHAR2(15));
INSERT INTO digits VALUES (1,'ONE);
UPDATE digits SET description ='TWO'WHERE id=1;
INSERT INTO digits VALUES (2 ,'TWO');
COMMIT;
DELETE FROM digits;
SELECT description FROM digits
VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE;

What would be the outcome of the above query?
a) It would not display any values
b) It would display the value TWO once
c) It would display the value TWO twice
d) It would display the values ONE, TWO, and TWO

4. A non-correlated subquery can be defined as________
a) A set of sequential queries, all of which must always return a single value
b) A set of sequential queries, all of which must return values from the same table
c) A SELECT statement that can be embedded in a clause of another SELECT statement only
d) A set of one or more sequential queries in which generally the result of the inner query is used as the search value in the outer query

5. Which statement is true regarding synonyms?
a) Synonyms can be created for tables but not views
b) Synonyms are used to reference only those tables that are owned by another user
c) A public synonym and a private synonym can exist with the same name for the same table
d) The DROP SYNONYM statement removes the synonym, and the status of the table on which the synonym has been created becomes invalid

6. SCOTT is a user in the database.

Evaluate the commands issued BY the DBA:
1 - CREATE ROLE mgr;
2 - GRANT CREATE TABLE, SELECT
ON oe. orders TO mgr;
3 - GRANT mgr, CREATE TABLE TO SCOTT;

Which statement is true regarding the execution of the above commands?
a) Statement 1 would not execute because the WITH GRANT option is missing
b) Statement 1 would not execute because the IDENTIFIED BY clause is missing
c) Statement 3 would not execute because role and system privileges cannot be granted together in a single GRANT statement
d) Statement 2 would not execute because system privileges and object privileges cannot be granted together in a single GRANT command

7. OE and SCOTT are the users in the database. The ORDERS table is owned by OE. Evaluate the statements issued by the DBA in the following sequence:

CREATE ROLE r1;
GRANT SELECT, INSERT ON oe. orders TO r1;
GRANT r1 TO scott;
GRANT SELECT ON oe. orders TO scott;
REVOKE SELECT ON oe.orders FROM scott;

What would be the outcome after executing the statements?
a) SCOTT would be able to query the OE.ORDERS table
b) SCOTT would not be able to query the OE.ORDERS table
c) The REVOKE statement would remove the SELECT privilege from SCOTT as well as from the role R1
d) The REVOKE statement would give an error because the SELECT privilege has been granted to the role R1

8. Given below are the SQL statements executed in a user session:

CREATE TABLE product
(pcode NUMBER(2),
pnameVARCHAR2(10));
INSERT INTO product VALUES(1, 'pen');
INSERT INTO product VALUES (2,'penci');
SAVEPOINT a;
UPDATE product SET pcode = 10 WHERE pcode = 1;
SAVEPOINT b;
DELETE FROM product WHERE pcode = 2;
COMMIT;
DELETE FROM product WHERE pcode=10;
ROLLBACK TO SAVEPOINT a;

Which statement describes the consequences?
a) No SQL statement would be rolled back
b) Both the DELETE statements would be rolled back
c) Only the second DELETE statement would be rolled back
d) Both the DELETE statements and the UPDATE statement would be rolled back

9. Evaluate the following command:

CREATE TABLE employees (employee_id NUMBER(2) PRIMARY KEY, last_name VARCHAR2(25) NOT
NULL, department_id NUMBER(2), job_id VARCHAR2(8), salary NUMBER(10,2));
You issue the following command TO CREATE a VIEW that displays the IDs AND LAST names OF the sales staff IN the organization:
CREATE OR REPLACE VIEW sales_staff_vu AS SELECT employee_id, last_name job_id FROM employees
WHERE job_id LIKE 'SA_%' WITH CHECK OPTION;

Which statements are true regarding the above view?
a) It allows you to insert details of all new staff into the EMPLOYEES table
b) It allows you to delete the details of the existing sales staff from the EMPLOYEES table
c) It allows you to update the job ids of the existing sales staff to any other job id in the EMPLOYEES table
d) It allows you to insert the IDs, last

CREATE VIEW view_name AS
SELECT column_name(s)
FROM TABLE_NAME
WHERE condition.

10. EMPDET is an external table containing the columns EMPNO and ENAME. Which command would work in relation to the EMPDET table?

a) UPDATE empdet
SET ename = 'Amit'
WHERE empno = 1234;
b) DELETE FROM empdet
WHERE ename LIKE 'J%';
c) CREATE VIEW empvu
AS
SELECT* FROM empdept;
d) CREATE INDEX empdet_idx
ON empdet(empno);

Database MCQ Set 3

1. To include integrity constraint in a existing relation use :
a) Create table
b) Modify table
c) Alter table
d) Drop table

2. Which of the following is not a integrity constraint ?
a) Not null
b) Positive
c) Unique
d) Check ‘predicate’

3.

CREATE TABLE Employee(Emp_id NUMERIC NOT NULL, Name VARCHAR(20) , dept_name VARCHAR(20), Salary NUMERIC UNIQUE(Emp_id,Name));
INSERT INTO Employee VALUES(1002, Ross, CSE, 10000)
INSERT INTO Employee VALUES(1006,Ted,Finance, );
INSERT INTO Employee VALUES(1002,Rita,Sales,20000);

What will be the result of the query?
a) All statements executed
b) Error in create statement
c) Error in insert into Employee values(1006,Ted,Finance, );
d) Error in insert into Employee values(1008,Ross,Sales,20000);

4.

CREATE TABLE Manager(ID NUMERIC,Name VARCHAR(20),budget NUMERIC,Details VARCHAR(30));

Inorder to ensure that the value of budget is non-negative which of the following should be used?
a) Check(budget>0)
b) Check(budget<0)
c) Alter(budget>0)
d) Alter(budget<0)

5. Foreign key is the one in which the ________ of one relation is referenced in another relation.
a) Foreign key
b) Primary key
c) References
d) Check constraint

6.

CREATE TABLE course
( . . .
FOREIGN KEY (dept name) REFERENCES department
. . . );

Which of the following is used to delete the entries in the referenced table when the tuple is deleted in course table?
a) Delete
b) Delete cascade
c) Set null
d) All of the mentioned

7. Domain constraints, functional dependency and referential integrity are special forms of ___
a) Foreign key
b) Primary key
c) Assertion
d) Referential constraint

8. Which of the following is the right syntax for assertion?
a) Create assertion ‘assertion-name’ check ‘predicate’;
b) Create assertion check ‘predicate’ ‘assertion-name’;
c) Create assertions ‘predicates’;
d) All of the mentioned

9. Data integrity constraints are used to:
a) Control who is allowed access to the data
b) Ensure that duplicate records are not entered into the table
c) Improve the quality of data entered for a specific property (i.e., table column)
d) Prevent users from changing the values stored in the table

10. Which of the following can be addressed by enforcing a referential integrity constraint?
a) All phone numbers must include the area code
b) Certain fields are required (such as the email address, or phone number) before the record is accepted
c) Information on the customer must be known before anything can be sold to that customer
d) When entering an order quantity, the user must input a number and not some text (i.e., 12 rather than ‘a dozen’)

Database MCQ Set 4

1. The____condition allows a general predicate over the relations being joined.
a) On
b) Using
c) Set
d) Where

2. 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

3.

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

4. 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

5. How many tables may be included with a join?
a) One
b) Two
c) Three
d) All of the mentioned

6. Which are the join types in join condition:
a) Cross join
b) Natural join
c) Join with USING clause
d) All of the mentioned

7. How many join types in join condition:
a) 2
b) 3
c) 4
d) 5

8. 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

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

10. 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

Database MCQ Set 5

1. 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

2. A condition is referred to as ____
a) Join in SQL
b) Join condition
c) Join in SQL & Condition
d) None of the mentioned

3. 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

4. How many join types in join condition:
a) 2
b) 3
c) 4
d) 5

5. Which are the join types in join condition:
a) Cross join
b) Natural join
c) Join with USING clause
d) All of the mentioned

6. 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

7. Which is a join condition contains an equality operator:
a) Equijoins
b) Cartesian
c) Both Equijoins and Cartesian
d) None of the mentioned

8. 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

9. Which operation are allowed in a join view:
a) UPDATE
b) INSERT
c) DELETE
d) All of the mentioned

10. 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

DistPub Team

Distance Publisher (DistPub.com) provide project writing help from year 2007 and provide writing and editing help to hundreds student every year.

• Website
• Twitter
• Facebook