Category: Database Management System Objective Questions

Database Multiple Choice Questions on “Join Operations”.

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

Answer: c
Clarification: 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.

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

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

Answer: c
Clarification: Oracle 9i is a version of the Oracle Database. The i stands for “Internet” to indicate that 9i is “Internet ready”.

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

Answer: d
Clarification: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, EQUIJOIN.

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

Answer: d
Clarification: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, EQUIJOIN are the types of joins.

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

Answer: b
Clarification: A Cartesian coordinate system is a coordinate system that specifies each point uniquely in a plane by a pair of numerical coordinates.

7. 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
Clarification: An equi-join is a specific type of comparator-based join, that uses only equality comparisons in the join-predicate.

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

Answer: b
Clarification: 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.

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

Answer: d
Clarification: 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.

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

Answer: c
Clarification: 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.

Database Multiple Choice Questions on “Deadlocks”.

1. A system is in a ______ state if there exists a set of transactions such that every transaction in the set is waiting for another transaction in the set.
a) Idle
b) Waiting
c) Deadlock
d) Ready

Answer: c
Clarification: When one data item is waiting for another data item in a transaction then system is in deadlock.

2. The deadlock state can be changed back to stable state by using _____________ statement.
a) Commit
b) Rollback
c) Savepoint
d) Deadlock

Answer: b
Clarification: Rollback is used to rollback to the point before lock is obtained.

3. What are the ways of dealing with deadlock?
a) Deadlock prevention
b) Deadlock recovery
c) Deadlock detection
d) All of the mentioned

Answer: d
Clarification: Deadlock prevention is also called as deadlock recovery. Prevention is commonly used if the probability that the system would enter a deadlock state is relatively high; otherwise, detection and recovery are more efficient.

4. When transaction Ti requests a data item currently held by Tj, Ti is allowed to wait only if it has a timestamp smaller than that of Tj (that is, Ti is older than Tj). Otherwise, Ti is rolled back (dies). This is
a) Wait-die
b) Wait-wound
c) Wound-wait
d) Wait

Answer: a
Clarification: The wait–die scheme is a non-preemptive technique.

5. When transaction Ti requests a data item currently held by Tj, Ti is allowed to wait only if it has a timestamp larger than that of Tj (that is, Ti is younger than Tj ). Otherwise, Tj is rolled back (Tj is wounded by Ti). This is
a) Wait-die
b) Wait-wound
c) Wound-wait
d) Wait

Answer: c
Clarification: The wound–wait scheme is a preemptive technique. It is a counterpart to the wait–die scheme.

6. The situation where the lock waits only for a specified amount of time for another lock to be released is
a) Lock timeout
b) Wait-wound
c) Timeout
d) Wait

Answer: a
Clarification: The timeout scheme is particularly easy to implement, and works well if transactions are short and if longwaits are likely to be due to deadlocks.

7. The deadlock in a set of a transaction can be determined by
a) Read-only graph
b) Wait graph
c) Wait-for graph
d) All of the mentioned

Answer: a
Clarification: Each transaction involved in the cycle is said to be deadlocked.

8. A deadlock exists in the system if and only if the wait-for graph contains a ___________
a) Cycle
b) Direction
c) Bi-direction
d) Rotation

Answer: a
Clarification: Each transaction involved in the cycle is said to be deadlocked.

9. Selecting the victim to be rollbacked to the previous state is determined by the minimum cost. The factors determining cost of rollback is
a) How long the transaction has computed, and how much longer the transaction will compute before it completes its designated task
b) How many data items the transaction has used
c) How many more data items the transaction needs for it to complete
d) All of the mentioned

Answer: d
Clarification: We should roll back those transactions that will incur the minimum cost.

10. __________ rollback requires the system to maintain additional information about the state of all the running transactions.
a) Total
b) Partial
c) Time
d) Commit

Answer: b
Clarification: In total rollback abort the transaction and then restart it.

Database Multiple Choice Questions on “SQL Data Types and Schemas”.

1. Dates must be specified in the format
a) mm/dd/yy
b) yyyy/mm/dd
c) dd/mm/yy
d) yy/dd/mm

Answer: b
Clarification: yyyy/mm/dd is the default format in sql.

2. A ________ on an attribute of a relation is a data structure that allows the database system to find those tuples in the relation that have a specified value for that attribute efficiently, without scanning through all the tuples of the relation.
a) Index
b) Reference
c) Assertion
d) Timestamp

Answer: a
Clarification: Index is the reference to the tuples in a relation.

3.

Create index studentID_index on student(ID);

Here which one denotes the relation for which index is created?
a) StudentID_index
b) ID
c) StudentID
d) Student

Answer: d
Clarification: The statement creates an index named studentID index on the attribute ID of the relation student.

4. Which of the following is used to store movie and image files?
a) Clob
b) Blob
c) Binary
d) Image

Answer: b
Clarification: SQL therefore provides large-object data types for character data (clob) and binary data (blob). The letters “lob” in these data types stand for “Large OBject”.

5. The user defined data type can be created using
a) Create datatype
b) Create data
c) Create definetype
d) Create type

Answer: d
Clarification: The create type clause can be used to define new types.Syntax : create type Dollars as numeric(12,2) final; .

6. Values of one type can be converted to another domain using which of the following?
a) Cast
b) Drop type
c) Alter type
d) Convert

Answer: a
Clarification: Example of cast :cast (department.budget to numeric(12,2)). SQL provides drop type and alter type clauses to drop or modify types that have been created earlier.

7.

CREATE DOMAIN YearlySalary NUMERIC(8,2)
CONSTRAINT salary VALUE test __________;

In order to ensure that an instructor’s salary domain allows only values greater than a specified value use:
a) Value>=30000.00
b) Not null;
c) Check(value >= 29000.00);
d) Check(value)

Answer: c
Clarification: Check(value ‘condition’) is the syntax.

8. Which of the following closely resembles Create view?
a) Create table . . .like
b) Create table . . . as
c) With data
d) Create view as

Answer: b
Clarification: The ‘create table . . . as’ statement closely resembles the create view statement and both are defined by using queries. The main difference is that the contents of the table are set when the table is created, whereas the contents of a view always reflect the current query result.

9. In contemporary databases, the top level of the hierarchy consists of ______ each of which can contain _____
a) Catalogs, schemas
b) Schemas, catalogs
c) Environment, schemas
d) Schemas, Environment

Answer: a
Clarification: None.

10. Which of the following statements creates a new table temp instructor that has the same schema as an instructor.
a) create table temp_instructor;
b) Create table temp_instructor like instructor;
c) Create Table as temp_instructor;
d) Create table like temp_instructor;

Answer: b

Database Questions & Answers on “Querying database part-1 DML”

Answer the questions based on the above relations

1. Which of the following command is used to display the departments of the instructor relation?
a) Select * from instructor where Dept_name = Finance;
b) Select * from instructor ;
c) Select dept_name from instructor;
d) Select dept_name for instructor where Name=Jackson;

Answer: c
Clarification: Only one field is necessary for the query and where clause is not needed for the selection.

2. How can we select the elements which have common Dept_name in both the relation ?
a) Select * from instructor i , course c where i.Dept_name=c.Dept_name;
b) Select Dept name from instructor ,Course ;
c) Select * from instructor i , course c ;
d) Select Dept_name from instructor where Dept_name = NULL;

Answer: a
Clarification: Here only the common elements are displayed .

3. Select distinct Dept_name from instructor ;
How many row(s) are displayed ?
a) 4
b) 3
c) 5
d) Error

Answer: a
Clarification: Distinct keyword eliminates the the common Dept_name .

4. Suppose the Authority want to include a new instructor for the title Neuroscience what command should be inserted ?
a) Insert into instructor values(12111,Emma,NeuroScience,200000);
b) Insert into course values(12111,Introduction,NeuroScience,2);
c)

Insert into instructor values(12111,Emma,Biology,200000);
   Insert into course values(BIO-112,Introduction to Neuro Science,NeuroScience,2);

d) Insert into course values(12111,Emma,NeuroScience,200000);

Answer: c
Clarification: The values have to be inserted into both the relations to be intact .

5. If a person all the people in Music department gets fired which of the following has to be performed on the instructor relation ?
a) Delete Dept_name=Music in instructor;
b) Delete from instructor where Dept_name=Music;
c) Remove Dept_name= Music
d) All of the mentioned

Answer: b
Clarification: Delete from table_name where condition .

6.

SELECT DISTINCT T.name
FROM instructor AS T, instructor AS S
WHERE T.salary > S.salary AND S.dept name = ’Comp.Sci.’;

What will be displayed as the value of name for the above query?
a) Hayley
b) Jackson
c) Hayley and Crick
d) Crick

Answer: d
Clarification: Only the greatest salary in Comp.Sci dept is selected for the query.

7.

SELECT Name
FROM instructor
WHERE salary > SOME (SELECT salary FROM instructor WHERE dept_name = 'Comp.Sci.');

How many rows are selected ?
a) 3
b) 4
c) 2
d) 1

Answer: d
Clarification: This displays the names of instructors with salary greater than that of some (at least one) instructor in the Biology department .

8. How will you select the Names whose first letter is E ?
a)

   SELECT Name
   FROM instructor
   WHERE Name LIKE ’A%;

b)

   SELECT Name
   FROM course
   WHERE Name LIKE ’A%;

c)

   SELECT Dept_name
   FROM instructor
   WHERE Name LIKE ’A%;

d)

   SELECT Name
   FROM instructor
   WHERE Dept_name LIKE ’A%;

View Answer

Answer: a
Clarification: % is used to indicate that some characters may appear .

9. Which function is used to find the count of distinct departments?
a) Dist
b) Distinct
c) Count
d) Count,Dist

Answer: a
Clarification: Count (distinct ID) is the correct usage.

10. Which function is used to identify the title with Least scope?
a) Min(Credits)
b) Max(Credits)
c) Min(title)
d) Min(Salary)

Answer: a
Clarification: Max is used to find the highest element and Min is used to find the lowest element.

Database Multiple Choice Questions on “Magnetic Disk and Flash Storage”.

1. In magnetic disk ________ stores information on a sector magnetically as reversals of the direction of magnetization of the magnetic material.
a) Read–write head
b) Read-assemble head
c) Head–disk assemblies
d) Disk arm

Answer: d
Clarification: Each side of a platter of a disk has a read–write head that moves across the platter to access different tracks.

2. A __________ is the smallest unit of information that can be read from or written to the disk.
a) Track
b) Spindle
c) Sector
d) Platter

Answer: c
Clarification: The disk surface is logically divided into tracks, which are subdivided into sectors.

3. The disk platters mounted on a spindle and the heads mounted on a disk arm are together known as ___________
a) Read-disk assemblies
b) Head–disk assemblies
c) Head-write assemblies
d) Read-read assemblies

Answer: b
Clarification: Each side of a platter of a disk has a read–write head that moves across the platter to access different tracks.

4. The disk controller uses ________ at each sector to ensure that the data is not corrupted on data retrieval.
a) Checksum
b) Unit drive
c) Read disk
d) Readsum

Answer: a
Clarification: A disk controller interfaces between the computer system and the actual hardware of the disk drive.

5. _________ is the time from when a read or write request is issued to when data transfer begins.
a) Access time
b) Average seek time
c) Seek time
d) Rotational latency time

Answer: a
Clarification: To access (that is, to read or write) data on a given sector of a disk, the arm first must move so that it is positioned over the correct track, and then must wait for the sector to appear under it as the disk rotates.

6. The time for repositioning the arm is called the ________ and it increases with the distance that the arm must move.
a) Access time
b) Average seek time
c) Seek time
d) Rotational latency time

Answer: c
Clarification: Typical seek times range from 2 to 30 milliseconds, depending on how far the track is from the initial arm position.

7. _________ is around one-half of the maximum seek time.
a) Access time
b) Average seek time
c) Seek time
d) Rotational latency time

Answer: b
Clarification: Average seek times currently range between 4 and 10 milliseconds, depending on the disk model.

8. Once the head has reached the desired track, the time spent waiting for the sector to be accessed to appear under the head is called the _______________
a) Access time
b) Average seek time
c) Seek time
d) Rotational latency time

Answer: d
Clarification: Rotational speeds of disks today range from 5400 rotations per minute (90 rotations per second) up to 15,000 rotations per minute (250 rotations per second), or, equivalently, 4 milliseconds to 11.1 milliseconds per rotation.

9. In Flash memory, the erase operation can be performed on a number of pages, called an _______ at once, and takes about 1 to 2 milliseconds.
a) Delete block
b) Erase block
c) Flash block
d) Read block

Answer: b
Clarification: The size of an erase block (often referred to as just “block” in flash literature) is usually significantly larger than the block size of the storage system.

10. Hybrid disk drives are hard-disk systems that combine magnetic storage with a smaller amount of flash memory, which is used as a cache for frequently accessed data.
a) Hybrid drivers
b) Disk drivers
c) Hybrid disk drivers
d) All of the mentioned

Answer: b
Clarification: Frequently accessed data that are rarely updated are ideal for caching in flash memory.

Database Multiple Choice Questions on “Evaluation of Expressions”.

1. Pictorial representation of an expression is called
a) Expression tree
b) Operator tree
c) Expression flow
d) Expression chart

Answer: b
Clarification: The operator tree has a tree like format where the evaluation starts from root of the tree.

2. The results of each intermediate operation are created and then are used for evaluation of the next-level operations. This is called
a) Materialized evaluation
b) Expression evaluation
c) Tree evaluation
d) Tree materialization

Answer: a
Clarification: The cost of a materialized evaluation is not simply the sum of the costs of the operations involved.

3. ______________ allows the algorithm to execute more quickly by performing CPU activity in parallel with I/O activity.
a) Buffering
b) Double buffering
c) Multiple buffering
d) Double reading

Answer: a
Clarification: Double buffering using two buffers, with one continuing execution of the algorithm while the other is being written out.

4. Pipelines can be executed in
a) 4
b) 3
c) 2
d) 5

Answer: c
Clarification: Demand driven and producer driven pipelines are the two ways.

5. In a _________ the system makes repeated requests for tuples from the operation at the top of the pipeline.
a) Demand-driven pipeline
b) Producer-driven pipeline
c) Demand pipeline
d) All of the mentioned

Answer: a
Clarification: Each time that an operation receives a request for tuples, it computes the next tuple (or tuples) to be returned, and then returns that tuple.

6. In a _____________ operations do not wait for requests to produce tuples, but instead generate the tuples eagerly.
a) Demand-driven pipeline
b) Producer-driven pipeline
c) Demand pipeline
d) All of the mentioned

Answer: b
Clarification: Each operation in a producer-driven pipeline is modeled as a separate process or thread within the system that takes a stream of tuples from its pipelined inputs and generates a stream of tuples for its output.

7. Each operation in a demand-driven pipeline can be implemented as an ____ that provides the following functions: open(), next(), and close().
a) Demand
b) Pipeline
c) Iterator
d) All of the mentioned

Answer: c
Clarification: After a call to open(), each call to next() returns the next output tuple of the operation.

8. The iterator maintains the __________ of its execution in between calls so that successive next() requests receive successive result tuples.
a) State
b) Transition
c) Rate
d) Block

Answer: a
Clarification: The function close() tells an iterator that no more tuples are required.

9. Tuples are generated ___________ in producer-driven pipelining, they are generated ________ on demand, in demand-driven pipelining.
a) Lazily, Eagerly
b) Eagerly, Lazily
c) Slowly, Eagerly
d) Eagerly, Slowly

Answer: b
Clarification: Producer-driven pipelining is very useful in parallel processing systems.

10. When two inputs that we desire to pipeline into the join are not already sorted it is the _____________ technique.
a) Hash join
b) Buffer join
c) double-pipelined hash join
d) double-pipelined join

Answer: d
Clarification: When hash indices are used on tuples, the resultant algorithm is called the double-pipelined hash-join technique.