Database MCQ Set 1
1. A collection of data designed to be used by different people is called a/an
a) Organization
b) Database
c) Relationship
d) Schema
Answer
Answer: b [Reason:] Database is a collection of related tables.
2. Which of the following is the oldest database model?
a) Relational
b) Deductive
c) Physical
d) Network
Answer
Answer: d [Reason:] The network model is a database model conceived as a flexible way of representing objects and their relationships.
3. Which of the following schemas does define a view or views of the database for particular users?
a) Internal schema
b) Conceptual schema
c) Physical schema
d) External schema
Answer
Answer: d [Reason:] An externally-defined schema can provide access to tables that are managed on any PostgreSQL, Microsoft SQL Server, SAS, Oracle, or MySQL database.
4. Which of the following is an attribute that can uniquely identify a row in a table?
a) Secondary key
b) Candidate key
c) Foreign key
d) Alternate key
Answer
Answer: b [Reason:] A Candidate Key can be any column or a combination of columns that can qualify as unique key in database.
5. Which of the following is the process of selecting the data storage and data access characteristics of the database?
a) Logical database design
b) Physical database design
c) Testing and performance tuning
d) Evaluation and selecting
Answer
Answer: b [Reason:] The physical design of the database optimizes performance while ensuring data integrity by avoiding unnecessary data redundancies.
6. Which of the following terms does refer to the correctness and completeness of the data in a database?
a) Data security
b) Data constraint
c) Data independence
d) Data integrity
Answer
Answer: d [Reason:] ACID property is satisfied by transaction in database.
7. The relationship between DEPARTMENT and EMPLOYEE is a
a) One-to-one relationship
b) One-to-many relationship
c) Many-to-many relationship
d) Many-to-one relationship
Answer
Answer: b [Reason:] One entity department is related to several employees.
8. A table can be logically connected to another table by defining a
a) Super key
b) Candidate key
c) Primary key
d) Unique key
Answer
Answer: c [Reason:] A superkey is a combination of attributes that can be uniquely used to identify a database record.
9. If the state of the database no longer reflects a real state of the world that the database is supposed to capture, then such a state is called
a) Consistent state
b) Parallel state
c) Durable state
d) Inconsistent state
Answer
Answer: d [Reason:] SQL data consistency is that whenever a transaction is performed, it sees a consistent database.
10. Ensuring isolation property is the responsibility of the
a) Recovery-management component of the DBMS
b) Concurrency-control component of the DBMS
c) Transaction-management component of the DBMS
d) Buffer management component in DBMS
Answer
Answer: b [Reason:] Concurrency control ensures that correct results for concurrent operations are generated, while getting those results as quickly as possible.
Database MCQ Set 2
SQL data definition for part of the university database.
CREATE TABLE department (dept_name VARCHAR (20), building VARCHAR (15), budget NUMBER, PRIMARY KEY (dept_name)); CREATE TABLE course (course_id VARCHAR (7), title VARCHAR (50), dept_name VARCHAR (20), credits NUMERIC (2,0), PRIMARY KEY (course_id), FOREIGN KEY (dept_name) ____ department); CREATE TABLE instructor (ID VARCHAR (5), name VARCHAR (20) NOT NULL, dept_name VARCHAR (20), salary NUMERIC (8,2), FOREIGN KEY (dept_name) _______ department); CREATE TABLE SECTION (course_id VARCHAR (8), sec_id VARCHAR (8), semester VARCHAR (6), YEAR NUMERIC (4,0), building VARCHAR (15), room_number VARCHAR (7), time_slot id VARCHAR (4), PRIMARY KEY (course_id, sec_id, semester, YEAR), FOREIGN KEY (_______) ______ course); CREATE TABLE teaches (ID VARCHAR (5), course_id VARCHAR (8), sec_id VARCHAR (8), semester VARCHAR (6), YEAR NUMERIC (4,0), PRIMARY KEY (ID, course_id, sec_id, semester, YEAR), FOREIGN KEY (course_id, sec_id, semester, YEAR) REFERENCES SECTION, FOREIGN KEY (ID) _______ instructor);
Answer questions based on the above commands
1. Which is the main relation which is used in the university database which is referenced by all other relation of the university.
a) Teaches
b) Course
c) Department
d) Section
Answer
Answer: c [Reason:] Department is the only relation which forms the main part of the university database .
2. The department relation has the an entry budget whose type has to be replaced by
a) Varchar (20)
b) Varchar2 (20)
c) Numeric (12,2)
d) Numeric
Answer
Answer: c [Reason:] Department is the only relation which forms the main part of the university database.
3. In the course relation the title field should throw an error in case of any missing title. The command to be added in title is
a) Unique
b) Not null
c) 0
d) Null
Answer
Answer: b [Reason:] By specifying not null the value cannot be left blank.
4. In the above DDL command the foreign key entries are got by using the keyword
a) References
b) Key reference
c) Relating
d) None of the mentioned
Answer
Answer: a [Reason:] References (table_name) give the prior table name for the entry.
5. Identify the error in the section relation
a) No error
b) Year numeric (4,0)
c) Building varchar (15)
d) Sec_id varchar (8)
Answer
Answer: a [Reason:] The building and the sec_id have varchar values and year is of numeric type. So no such errors are found in the relation.
6. The following entry is given in to the instructor relation .
(100202,Drake,Biology,30000)
Identify the output of the query given
a) Row(s) inserted
b) Error in ID of insert
c) Error in Name of insert
d) Error in Salary of the insert
Answer
Answer: b [Reason:] The varchar(5) value cannot hold the entry 100202.
7. Which of the following can be used as a primary key entry of the instructor relation.
a) DEPT_NAME
b) NAME
c) ID
d) All of the mentioned
Answer
Answer: c [Reason:] The value ID can only be primary key unlike dept_name which is used as a foreign key.
8. In the section relation which of the following is used as a foreign key ?
a) Course_id
b) Course_id,sec_id
c) Room_number
d) Course_id,sec_id,room_number
Answer
Answer: a [Reason:] Course_id is the only field which is present in the course relation.
9. In order to include an attribute Name to the teaches relation which of the following command is used ?
a) Alter table teaches include Name;
b) Alter table teaches add Name;
c) Alter table teaches add Name varchar;
d) Alter table teaches add Name varchar(20);
Answer
Answer: d [Reason:] The form of the alter table command is
alter table r add AD;
where r is the name of an existing relation, A is the name of the attribute to be added, and D is the type of the added attribute.
10. To replace the relation section with some other relation the initial step to be carried out is
a) Delete section;
b) Drop section;
c) Delete from section;
d) Replace section new_table ;
Answer
Answer: b [Reason:] Droping the table drops all the references to that table .
Database MCQ Set 3
1. Which level of RAID refers to disk mirroring with block striping?
a) RAID level 1
b) RAID level 2
c) RAID level 0
d) RAID level 3
Answer
Answer: a [Reason:] RAID level 1 refers to disk mirroring with block striping.
2. Optical disk technology uses
a) Helical scanning
b) DAT
c) A laser beam
d) RAID
Answer
Answer: d [Reason:] Redundant Array of Inexpensive Disks.
3. With multiple disks, we can improve the transfer rate as well by _____ data across multiple disks.
a) Striping
b) Dividing
c) Mirroring
d) Dividing
Answer
Answer: a [Reason:] Data striping consists of splitting the bits of each byte across multiple disks; such striping is called bitlevel striping.
4. Which one of the following is a Stripping technique ?
a) Byte level stripping
b) Raid level stripping
c) Disk level stripping
d) Block level stripping
Answer
Answer: d [Reason:] Block-level striping stripes blocks across multiple disks. It treats the array of disks as a single large disk, and it gives blocks logical numbers.
5. The RAID level which mirroring is done along with stripping is
a) RAID 1+0
b) RAID 0
c) RAID 2
d) Both RAID 1+0 and RAID 0
Answer
Answer: d [Reason:] Mirroring without striping can also be used with arrays of disks, to give the appearance of a single large, reliable disk.
6. Where performance and reliability are both important, RAID level ____ is used.
a) 0
b) 1
c) 2
d) 0+1
Answer
Answer: d [Reason:] Mirroring without striping can also be used with arrays of disks, to give the appearance of a single large, reliable disk.
7. ________ partitiones data and parity among all N+1 disks, instead of storing data in N-disks and parity in one disk.
a) Block interleaved parity
b) Block interleaved distributed parity
c) Bit parity
d) Bit interleaved parity
Answer
Answer: b [Reason:] In level 5, all disks can participate in satisfying read requests, unlike RAID level 4, where the parity disk cannot participate, so level 5 increases the total number of requests that can be met in a given amount of time.
8. Hardware RAID implementations permit ___ that is, faulty disks can be removed and replaced by new ones without turning power off.
a) Scrapping
b) Swapping
c) Hot swapping
d) None of the mentioned
Answer
Answer: c [Reason:] Hot
swapping reduces the mean time to repair, since replacement of a disk does not have to wait until a time when the system can be shut down.
9. _____ is popular for applications such as storage of log files in a database system, since it offers the best write performance.
a) RAID level 1
b) RAID level 2
c) RAID level 0
d) RAID level 3
Answer
Answer: a [Reason:] RAID level 1 refers to disk mirroring with block striping.
10. ________ which increases the number of I/O operations needed to write a single logical block, pays a significant time penalty in terms of write performance.
a) RAID level 1
b) RAID level 2
c) RAID level 5
d) RAID level 3
Answer
Answer: a [Reason:] In level 5, all disks can participate in satisfying read requests, unlike RAID level 4, where the parity disk cannot participate, so level 5 increases the total number of requests that can be met in a given amount of time.
Database MCQ Set 4
1. Which schema object instructs Oracle to connect to a remotely access an object of a database?
a) Sequence
b) Remote link
c) Database link
d) Data link
Answer
Answer: d [Reason:] A database link (DBlink) is a definition of how to establish a connection from one Oracle database to another.
2. DML changes are
a) Insert
b) Update
c) Create
d) Both Insert and Update
Answer
Answer: d [Reason:] Create is a DDL operation.
3. Which of the following object types below cannot be replicated?
a) Data
b) Trigger
c) View
d) Sequence
Answer
Answer: d [Reason:] Sequence is a series of items which is like a unique index .
4. How to force a log switch?
a) By using ALTER SYSTEM LOG
b) By using ALTER SYSTEM SWITCH LOGFILE
c) By using ALTER SYSTEM SWITCH LOGS
d) By using ALTER SYS LOGFILES
Answer
Answer: b [Reason:] ALTER SYSTEM ARCHIVE LOG CURRENT is the best practice for production backup scripts with RMAN. .
5. In the following query, which expression is evaluated first?
SELECT id_number, (quantity - 100 / 0.15 - 35 * 20) FROM inventory
a) 100 / 0.15
b) quantity – 100
c) 35*20
d) 0.15-35
Answer
Answer: a [Reason:] According to the precedence of expression as in BODMAS the expression evaluated.
6. The ORDER BY clause can only be used in
a) SELECT queries
b) INSERT queries
c) GROUP BY queries
d) HAVING queries
Answer
Answer: a [Reason:] SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;.
7. Which of the following rule below are categories of index?
a) Column and Functional
b) Multiple Column and functional
c) Column, Multiple Column and functional
d) None of the mentioned
Answer
Answer: a [Reason:] The CREATE INDEX statement is used to create indexes in tables.
8. What is the purpose of SMON background process?
a) Performs crash recovery when a failed instance starts up again
b) Performs recovery when a user process fails
c) Writes redo log entries to disk
d) None of the mentioned
Answer
Answer: a [Reason:] SMON (System MONitor) is an Oracle background process created when you start a database instance .
9. Which of the following queries are legal?
a) SELECT deptno, count(deptno) FROM emp GROUP BY ename;
b) SELECT deptno, count(deptno), job FROM emp GROUP BY deptno;
c) SELECT deptno, avg(sal) FROM emp;
d) SELECT deptno, avg(sal) FROM emp GROUP BY deptno;
Answer
Answer: d [Reason:] For aggregate functions group by clause is necessary.
10. Which of the following queries displays the sum of all employee salaries for those employees not making commission, for each job, including only those sums greater than 2500?
a) select job, sum(sal) from emp where sum(sal) > 2500 and comm is null;
b) select job, sum(sal) from emp where comm is null group by job having sum(sal) > 2500;
c) select job, sum(sal) from emp where sum(sal) > 2500 and comm is null group by job;
d) select job, sum(sal) from emp group by job having sum(sal) > 2500 and comm is not null;
Answer
Answer: b [Reason:] For aggregate functions group by clause is necessary.
Database MCQ Set 5
1. The log is a sequence of ___ recording all the update activities in the database.
a) Log records
b) Records
c) Entries
d) Redo
Answer
Answer: a [Reason:] The most widely used structure for recording database modifications is the log.
2. In the _____ scheme, a transaction that wants to update the database first creates a complete copy of the database.
a) Shadow copy
b) Shadow Paging
c) Update log records
d) All of the mentioned
Answer
Answer: a [Reason:] If at any point the transaction has to be aborted, the system merely deletes the new copy. The old copy of the database has not been affected.
3. The ______ scheme uses a page table containing pointers to all pages; the page table itself and all updated pages are copied to a new location.
a) Shadow copy
b) Shadow Paging
c) Update log records
d) All of the mentioned
Answer
Answer: b [Reason:] Any page which is not updated by a transaction is not copied, but instead the new page table just stores a pointer to the original page.
4. The current copy of the database is identified by a pointer, called ______ which is stored on disk.
a) Db-pointer
b) Update log
c) Update log records
d) All of the mentioned
Answer
Answer: a [Reason:] Any page which is not updated by a transaction is not copied, but instead the new page table just stores a pointer to the original page.
5. If a transaction does not modify the database until it has committed, it is said to use the _____ technique.
a) Deferred-modification
b) Late-modification
c) Immediate-modification
d) Undo
Answer
Answer: a [Reason:] Deferred modification has the overhead that transactions need to make local copies of all updated data items; further, if a transaction reads a data item that it has updated, it must read the value from its local copy.
6. If database modifications occur while the transaction is still active, the transaction is said to use the _____technique.
a) Deferred-modification
b) Late-modification
c) Immediate-modification
d) Undo
Answer
Answer: c [Reason:] We say a transaction modifies the database if it performs an update on a disk buffer, or on the disk itself; updates to the private part of main memory do not count as database modifications.
7. ______ using a log record sets the data item specified in the log record to the old value.
a) Deferred-modification
b) Late-modification
c) Immediate-modification
d) Undo
Answer
Answer: d [Reason:] Undo brings the previous contents.
8. In the ____ phase, the system replays updates of all transactions by scanning the log forward from the last checkpoint.
a) Repeating
b) Redo
c) Replay
d) Undo
Answer
Answer: b [Reason:] Undo brings the previous contents.
9. The actions which are played in the order while recording it is called ________ history.
a) Repeating
b) Redo
c) Replay
d) Undo
Answer
Answer: a [Reason:] Undo brings the previous contents.
10. A special redo-only log record <Ti , Xj , V1> is written to the log, where V1 is the value being restored to data item Xj during the rollback. These log records are sometimes called
a) Log records
b) Records
c) Compensation log records
d) Compensation redo records
Answer
Answer: c [Reason:] Such records do not need undo information, since we never need to undo such an undo operation.
