Ad Code

CS409 Final Term MCQs | 205 Important MCQs Questions with Answers | Virt...

Rana Ejaz July 06, 2025
CS409 past paper solved




Sample Paper

 FINALTERM  EXAMINATION

 

CS409 – Introduction to Database Administration

 

Question No: 1      (Marks: 01) - Please choose the correct option

 

 Which SQL statement from the following is used to create a new tablespace?

A.    CREATE TABLESPACE

B.     CREATE TEMPORARY TABLESPACE

C.     CREATE UNDO TABLESPACE

D.    Both A & B

 

Question No: 2      (Marks: 01) - Please choose the correct option

 

Views are ____ tables that don't exist physically in Oracle.

A.    Real

B.     Virtual

C.     Both A and B

D.    None of the above

Question No: 3      (Marks: 01) - Please choose the correct option

 

One or more tables are joined to create a ____.

A.    View

B.     Virtue

C.     Variable

D.    None

Question No: 4      (Marks: 01) - Please choose the correct option

 

Which of the following date function can be used to get the timestamp from database?

A.    SYSTEMTIMESTAMP

B.     SYSTIMESTAMP

C.     SYSTEMDATE

D.    SYSDATE

 

Question No: 5      (Marks: 01) - Please choose the correct option

 

________ are PL/SQL programs that are set up to execute in response to a particular event on a table in the database.

A.    Package

B.     Functions

C.     Triggers

D.    Procedure

 

Question No: 6     (Marks: 01) - Please choose the correct option

 

A program that resides inside an oracle database that manipulates data in the database before the data is used outside the database is known as __________.

A.    Triggers

B.     Function

C.     Stored procedure

D.    Package

 

 

Question No: 7     (Marks: 01) - Please choose the correct option

 

Which command is used to fetch records from database?

A.    Fetch

B.     Select

C.     Use

D.    Get

 

Question No: 8     (Marks: 01) - Please choose the correct option

 

A ________ is a group of PL/SQL statements that can be called by name.

A.    View

B.     Trigger

C.     Procedure

D.    Function

 

Question No: 9     (Marks: 01) - Please choose the correct option

 

How many CREATE TRIGGER statements are there?

A.    4

B.     5

C.     6

D.    7

Question No: 10     (Marks: 01) - Please choose the correct option

 

TRUNCATE TABLE statement is used to remove the table.

A.    TRUE

B.     FALSE

C.     Can be true or false

D.    Can not say

 

Question No: 11      (Marks: 01) - Please choose the correct option

 

Which processes is an instance made of Oracle?

A.    Memory processes

B.     Oracle background processes

C.     Data processes

D.    All of the above

 

Question No: 12     (Marks: 01) - Please choose the correct option

 

Which is the smallest unit of storage in an Oracle database?

A.    Segment

B.     Data Block

C.     Extent

D.    Data File

 

Question No: 13      (Marks: 01) - Please choose the correct option

 

How many types of procedure are there?

A.    1

B.     2

C.     3

D.    4

Question No: 14      (Marks: 01) - Please choose the correct option

 

Which of the following is/are the type of procedure?

A.    IN

B.     OUT

C.     IN OUT

D.    All of the above

Question No: 15      (Marks: 01) - Please choose the correct option

 

The value of ____ is passed to the subprogram as a default parameter.

A.    IN

B.     OUT

C.     IN OUT

D.    None

Question No: 16      (Marks: 01) - Please choose the correct option

 

What is the syntax to Drop Procedure?

A.    DELETE PROCEDURE procedure_name;

B.     DROP PROCEDURE procedure_name;

C.     RETRIEVE PROCEDURE procedure_name;

D.    END PROCEDURE procedure_name;

Question No: 17      (Marks: 01) - Please choose the correct option

 

Which of the following is not present in the syntax of Create Procedure?

A.    IS

B.     BEGIN

C.     END

D.    None

Question No: 18      (Marks: 01) - Please choose the correct option

 

Which of the following is present in Call Procedure?

A.    BEGIN

B.     END

C.     Both A and B

D.    None of the above

Question No: 19      (Marks: 01) - Please choose the correct option

 

How many types of exceptions are there?

A.    2

B.     3

C.     4

D.    5

Question No: 20      (Marks: 01) - Please choose the correct option

 

Errors that are encountered during the execution of the program are referred to as ____ in PL/SQL.

A.    FUNCTION

B.     CURSOR

C.     EXCEPTION

D.    PROCEDURE

 

1. A/an _________ derives its data from the table on which it is based. This table is known as base table.
a) Function
b) View ✓
c) Join
d) Index

2. __________ is the basic unit of data storage in an Oracle Database.
a) Table ✓
b) Function
c) Join
d) Index

3. A/an ________ is a logical representation of a table or combination of tables.
a) View ✓
b) Index
c) Function
d) Join

4. You can use views in almost the same way as __________.
a) Index
b) Table ✓
c) Function
d) Join

5. A/an ____________ is a collection of column information corresponding to a single record.
a) Row ✓
b) Attribute
c) Entity
d) Table

6. __________ statement is used to change or rebuild an existing index.
a) CHANGE INDEX
b) REBUILD INDEX
c) UPDATE INDEX
d) ALTER INDEX ✓

7. Which of the following table statement is used to delete only the table structure from the database?
a) Create
b) Delete
c) Update
d) Drop ✓

8. Making an index ____________ is an alternative method to make it unusable or dropping it.
a) MUTE
b) UNREADABLE
c) INVISIBLE ✓
d) VISIBLE

9. To alter an index, your schema must contain the index or you must have the ______________ system privilege.
a) ALTER PRIVILEGE
b) ANY INDEX ALTER
c) ALTER INDEX
d) ALTER ANY INDEX ✓

10. A/an _________________ is a schema object that contains an entry for each value that appears in the column of the table or cluster and provides direct, fast access to rows.
a) Cluster
b) Table
c) View
d) Index ✓

11. Which option is true about the following code?

sql

CopyEdit

select sum(bytes)/1024/1024 SizeMB from dba_segments where segment_name ='EMP';

a) find size of table in GB
b) find size of table in TB
c) find size of table in KB
d) find size of table in MB ✓

12. Which option is true about the following code?

sql

CopyEdit

Select Table_name From Dict Where Table_name like '%TAB%‘;

a) Displaying Sequence for Table
b) Selecting Views for Table
c) Displaying synonyms for Table
d) Displaying Views for Table ✓

13. How many types of Views to manage Tables related information in oracle database?
a) 1
b) 2 ✓
c) 3
d) 4

14. Which of the following option is not a characteristic of PL/SQL?
a) Centrally accessible from single database
b) Executed on server without any network overhead
c) Embedded structured programming
d) Performed on client Machine ✓

15. EMP.sal%TYPE is an example of __________ data type.
a) Scalar ✓
b) Complex
c) Composite
d) Subtype

16. ALTER ANY INDEX is a ________ privilege.
a) System ✓
b) Complex
c) Organization
d) Domain

17. Instead of making an index unusable or dropping it, you may make it_______.
a) Visible
b) Invisible ✓
c) Initialize
d) Rebuild

18. When the recycle bin is _____________ dropped tables and their dependent objects are placed in the recycle bin.
a) Empty
b) Enable ✓
c) Disable
d) Full

19. Which statement is used to permanently delete the table from recycle bin?
a) Purge ✓
b) Delete
c) Drop
d) Remove

20. Which command is used to restore the table from recycle bin?
a) Rename
b) Flashback ✓
c) Purge
d) Delete

1. A/an __________ provides an optional method of storing table data.
a) Data block
b) Data group
c) Cluster ✓
d) Index

2. An ________________ is a view that contains multiple tables in the top-level FROM clause of the SELECT statement, and is not restricted by the WITH READ ONLY clause.
a) Add join view
b) Edit join view
c) Updatable join view ✓
d) Update join view

3. Front end programming can be changed without affecting __________ programming.
a) SQL
b) Client
c) Server ✓
d) Server and client

4. __________ is a pointer that points to the result of a query.
a) Record
b) Indicator
c) Index
d) Cursor ✓

5. Database roles make it __________ to grant multiple privileges to many users.
a) Impossible
b) Easy ✓
c) Difficult
d) Static

6. __________ is a binary parameter file that resides on the database server machine.
a) PFILE
b) Log File
c) SPFILE ✓
d) Control File

7. The code that you write to handle exceptions is called a/an ___________.
a) Exception handling
b) Exception SQL code
c) Exception handler ✓
d) Exception code

8. To create a table in a __________, you must have either the CREATE TABLE or CREATE ANY TABLE system privilege.
a) View
b) Cluster ✓
c) Index
d) Data

9. A/an __________ derives its data from the table on which it is based. This table is known as base table.
a) Join
b) Function
c) View ✓
d) Index

10. To alter an index, your schema must contain the index or you must have the __________ system privilege.
a) ANY INDEX
b) ALTER
c) ALTER INDEX
d) ALTER ANY INDEX ✓

11. EMP.sal%TYPE is an example of __________ data type.
a) Scalar
b) Complex
c) Subtype ✓
d) Composite

12. How many types of views are utilized to control table-related information?
a) 3 ✓
b) 5
c) 2
d) 4

13. Which of the following table statements is used to delete only the table structure from the database?
a) Update
b) Drop ✓
c) Delete
d) Create

14. __________ is the basic unit of data storage in an Oracle Database.
a) Join
b) Function
c) Table ✓
d) Index

15. You can use views in almost the same way as __________.
a) Function
b) Table ✓
c) Index
d) Join

16. A/an __________ is a collection of column information corresponding to a single record.
a) Table
b) Attribute
c) Entity
d) Row ✓

17. A/an __________ is a logical representation of a table or combination of tables.
a) Join
b) View ✓
c) Index
d) Function

18. Which of the following language code is executed on server without any network overhead?
a) Python
b) PHP
c) C++
d) PL/SQL ✓

 

1. A View derives its data from the table on which it is based. This table is known as base table.

  • Function
  • View ✓
  • Join
  • Index

2. ______ is the basic unit of data storage in an Oracle Database.

  • Table ✓
  • Function
  • Join
  • Index

3. A/an ________ is a logical representation of a table or combination of tables.

  • View ✓
  • Index
  • Function
  • Join

4. You can use views in almost the same way as __________.

  • Index
  • Table ✓
  • Function
  • Join

5. A/an ____________ is a collection of column information corresponding to a single record.

  • Row ✓
  • Attribute
  • Entity
  • Table

6. The statement used to change or rebuild an existing index is:

  • CHANGE INDEX
  • REBUILD INDEX
  • UPDATE INDEX
  • ALTER INDEX ✓

7. Which of the following table statement is used to delete only the table structure from the database?

  • Create
  • Delete
  • Update
  • Drop ✓

8. Making an index ____________ is an alternative method to make it unusable or dropping it.

  • MUTE
  • UNREADABLE
  • INVISIBLE ✓
  • VISIBLE

9. To alter an index, your schema must contain the index or you must have the ______________ system privilege.

  • ALTER ANY INDEX ✓
  • ALTER PRIVILEGE
  • ANY INDEX ALTER
  • ALTER INDEX

10. A/an _________________ is a schema object that provides direct, fast access to rows.

  • Cluster
  • Table
  • View
  • Index ✓

11. Which option is true about the following code?
select sum(bytes)/1024/1024 SizeMB from dba_segments where segment_name ='EMP';

  • find size of table in GB
  • find size of table in TB
  • find size of table in KB
  • find size of table in MB ✓

12. Data dictionary view is restricted to views owned by the current user:

  • None of these
  • DBA
  • USER ✓
  • ALL

13. PL/SQL is executed on _______ without any network overhead.

  • Client Side
  • Front end
  • Back end
  • Server side ✓

14. A cursor is a _______ to this context area.

  • Variable
  • Integer
  • Pointer ✓
  • Function

15. PL/SQL is more powerful than pure__________.

  • SQL ✓
  • Database
  • Python
  • C#

16. A __________ statement allows us to execute a statement or group of statements multiple times.

  • Loop ✓
  • None of these
  • Selection
  • If else

17. In basic structure of PL/SQL, execution section starts with _____ keyword.

  • END
  • DECLARE
  • EXCEPTIONS
  • BEGIN ✓

18. In basic structure of PL/SQL, exception section starts with ______ keyword.

  • EXCEPTIONS ✓
  • DECLARE
  • BEGIN
  • END

19. Which of the following is not a syntax for while loop in PL/SQL?

  • LOOP – EXIT WHEN – END LOOP
  • WHILE – LOOP – END LOOP
  • FOR – LOOP – END LOOP
  • WHILE –END LOOP ✓

20. ______ is a block with a name in PL/SQL.

  • Control file
  • Index
  • Procedure ✓
  • Exception

1. An/a ______________ is a set of logically contiguous data blocks allocated for storing a specific type of information.
A. Control file
✓ B. Extent
C. Block
D. Data file

2. Logical minimum storage unit is called __________________.
✓ A. Data block
B. Database block
C. Logical block
D. Control block

3. An extent is always contained in one _________________.
A. Data block
B. Data segment
C. Data memory
✓ D. Data file

4. Collection of extents, may be from multiple data files is called __________________.
✓ A. Segment
B. Tablespace
C. Table
D. Data block

5. Collection of segments is called _______________.
✓ A. Tablespace
B. Data block
C. Data extent
D. Data file

6. ________ are database objects, such as tables and indexes, that consume storage space.
✓ A. Segments
B. Table space
C. Extent
D. Data block

7. A/an ______________ derives its data from the tables on which it is based.
A. Index
B. Join
✓ C. View
D. Function

8. A view that derives its data from the tables on which it is based is called __________.
A. Schema
B. Store Procedure
✓ C. Base Table
D. Object

9. A ____________ is a logical representation of one or more tables.
✓ A. View
B. Index
C. Segment
D. Tablespace

10. You can use views in almost the same way as __________.
✓ A. Table
B. Join
C. Function
D. Index

11. A/an ____________ is a collection of column information corresponding to a single record.
✓ A. Row
B. Attribute
C. Entity
D. Table

12. The ________storage parameter is essential to how the database manages free space.
A. PFREE
B. PFREEC
✓ C. PCTFREE
D. PTFREE

13. PCTFREE is important for preventing _____________ and avoiding wasted space.
A. Data Inconsistency
✓ B. Row migration
C. Data anomaly
D. Data redundancy

14. The database can reuse space within an/a ____________ block.
A. Control
B. Index
✓ C. Data
D. File

15. By default, the database allocates an initial _____________ for a data segment when the segment is created.
A. Memory space
✓ B. Extent
C. Data block
D. Data file

16. Oracle Database manages the logical storage space in the data files of a database known as ____________.
A. Control file
✓ B. Data file
C. Oracle pages
D. Block file

17. If ____________________ is not set, then the default data block size is operating system-specific.
A. DATA_BLOCK_SIZE
B. BLOCK_SIZE
✓ C. DB_BLOCK_SIZE
D. BLOCK-SIZE

18. A/an __________ is a user-created object that can be shared by multiple users to generate integers.
✓ A. Sequence
B. Extent
C. Segment
D. Table

19. Which of the following is not a technique of data integrity?
✓ A. Enforcing business rules without using code of a database application
B. Enforcing business rules with triggered stored database procedures
C. Using stored procedures to completely control access to data
D. Enforcing business rules in the code of a database application

20. A constraint specified as part of the definition of a column or attribute is called an _____ specification.
✓ A. Inline
B. Null
C. Out-of-line
D. Outline

.

21. Which of the following is not an advantage of data integrity constraints?
A. Declarative ease
B. Centralized rules
C. Flexibility when loading data
✓ D. Decentralized rules

22. ________ constraints require a database value to obey a specified condition.
A. Primary Key
✓ B. Check
C. Foreign Key
D. Unique Key

23. A _________ constraint requires that a column of a table contains no null values.
A. Unique Key
B. Secondary Key
✓ C. Not Null
D. Foreign Key

24. A table that resides in the application database is called ___________.
A. Object
✓ B. Base Table
C. Store Procedure
D. Schema

25. __________ is a logical representation of a table or combination of tables.
A. Procedures
✓ B. View
C. Constraints
D. Table

26. Tables enable your data to be broken down into smaller, more manageable pieces.
A. Clustered
✓ B. Partitioned
C. None of them
D. Centralized

27. ________ constraint forces the column to contain a value in every row.
A. Check
B. Primary key
C. Unique key
✓ D. Not Null

28. ________ are the basic unit of data storage in an Oracle Database.
✓ A. Tables
B. Store procedures
C. Constraints
D. Sequences

29. Which option is true about the following code?

sql

CopyEdit

CREATE INDEX employees_ix ON employees (last_name, job_id, salary);

A. Rebuild index
✓ B. Composite index
C. Alter index
D. None of these

30. The cluster index is an _____ index on the cluster key.
✓ A. B-tree
B. Composite
C. Rebuild
D. None of these

31. Concatenated index is an alternative name of ___________?
A. Rebuild index
✓ B. Composite index
C. Alter index
D. None of these

32. The DROP TABLE statement is used when you want to:
A. Delete the record from the table
B. Delete the column from the table
C. Delete a row from the table
✓ D. Delete the table structure along with data

33. Which command is used to restore the table from recycle bin?
A. Delete
✓ B. Flashback
C. Purge
D. Rename

34. Which system privileges are required when you want to drop a table from the database?
A. Drop Table
✓ B. Drop any table
C. Drop
D. None of these

35. Data dictionary view is restricted to indexes owned by the user.
A. ALL
B. DBA
✓ C. USER
D. None

36. Data dictionary view describes indexes on all tables in the database.
A. ALL
✓ B. DBA
C. USER
D. None

37. Data dictionary view describes indexes on all tables accessible to the user.
✓ A. ALL
B. DBA
C. USER
D. None

38. A cluster index is created on the __________ key.
✓ A. Cluster
B. Table
C. Constraint
D. Index

39. Making an index ____________ is an alternative method to make it unusable or dropping it.
A. Unreadable
B. Visible
✓ C. Invisible
D. Mute

40. A/an _________________ is a schema object that contains an entry for each value that appears in the column of the table or cluster and provides direct, fast access to rows.
✓ A. Index
B. View
C. Cluster
D. Table

.

41. Which option is true about the following code?

sql

CopyEdit

ALTER INDEX emp_name REBUILD ONLINE;

A. Create index
B. Update index
C. Delete index
✅ D. Rebuild index

42. Instead of making an index unusable or dropping it, you may make it _______.
A. Visible
✅ B. Invisible
C. Rebuild
D. Initialize

43. The statement used to remove an index from database is:
A. Remove
B. Delete
C. Flashback
✅ D. Drop

44. Which of the following is correct syntax for dropping employee name index from employee table?
A. INDEX DROP emp_ename;
B. DROP emp_ename INDEX;
✅ C. DROP INDEX emp_ename;
D. ALTER INDEX emp_ename;

45. A cluster is a schema object that contains data from one or more __________.
A. Views
B. Tablespaces
C. Sequences
✅ D. Tables

46. A cluster key is the column, or group of columns, that the clustered tables have in ________.
A. Rows
✅ B. Common
C. Separate
D. Index

47. Which command is used to drop a cluster?
A. Flashback
B. Delete
C. Remove
✅ D. Drop

48. When we want to drop a table from a cluster, which clause should we use?
A. Update Tables
✅ B. Including Tables
C. Excluding Tables
D. Remove Tables

49. The database physically stores the rows of a table in a hash cluster and retrieves them according to the result of a __________.
A. User-defined function
✅ B. Hash function
C. Sequence
D. Join

50. Storing a table in a hash cluster is an optional way to improve the _______ of data retrieval.
A. Output
✅ B. Performance
C. Behavior
D. Speed

51. Identify the condition where hashing is useful.
A. The table is not static
B. Most queries retrieve rows over a range of values
C. You cannot afford to preallocate the space
✅ D. Most queries are equality queries on the cluster key

52. To create a hash cluster, you must specify a ______ clause.
A. TABLESPACE
✅ B. HASHKEYS
C. HASHFUNCTION
D. HASHTABLE

53. Data dictionary views show information about schema objects accessible to you at different levels of privilege.
A. Sequences
B. Tablespaces
✅ C. Data Dictionary Views
D. Hash Function

54. Data dictionary view is restricted to views owned by the current user.
A. DBA
✅ B. USER
C. ALL
D. None of these

55. Data dictionary view describes all views in the database.
A. USER
B. ALL
✅ C. DBA
D. None of these

56. Data dictionary view is restricted to views accessible to the current user.
A. DBA
B. ALL
✅ C. USER
D. None of these

57. PL/SQL can execute a number of queries in ______ block using a single command.
A. One
B. Two
C. Three
✅ D. Four

58. PL/SQL is a ___________ structured programming language.
A. Inherited
B. None of these
✅ C. Embedded
D. Encapsulated

59. PL/SQL is executed on _______ without any network overhead.
A. Client side
B. Front end
✅ C. Server side
D. Back end

60. A cursor is a _______ to the context area.
A. Function
✅ B. Pointer
C. Variable
D. Integer

Q: Which of the following is correct Syntax of For LOOP?
Select the correct option

A. FOR index lower_bound .. upper_bound LOOP statements; END LOOP;
B. FOR index IN lower_bound .. upper_bound LOOP statements; LOOP;
C. FOR index IN lower_bound .. upper_bound statements; END LOOP;
✅ D. FOR index IN lower_bound .. upper_bound LOOP statements; END LOOP;

1. A/an _________ derives its data from the table on which it is based. This table is known as base table.
🔘 Function
✅ View
🔘 Join
🔘 Index

2. ________ is the basic unit of data storage in an Oracle Database.
✅ Table
🔘 Function
🔘 Join
🔘 Index

3. A/an ________ is a logical representation of a table or combination of tables.
✅ View
🔘 Index
🔘 Function
🔘 Join

4. You can use views in almost the same way as __________.
🔘 Index
✅ Table
🔘 Function
🔘 Join

5. A/an ____________ is a collection of column information corresponding to a single record.
✅ Row
🔘 Attribute
🔘 Entity
🔘 Table

6. The statement used to change or rebuild an existing index is:
🔘 CHANGE INDEX
🔘 REBUILD INDEX
🔘 UPDATE INDEX
✅ ALTER INDEX

7. Which of the following table statements is used to delete only the table structure from the database?
🔘 Create
🔘 Delete
🔘 Update
✅ Drop

8. Making an index ____________ is an alternative method to make it unusable or dropping it.
🔘 MUTE
🔘 UNREADABLE
✅ INVISIBLE
🔘 VISIBLE

9. To alter an index, your schema must contain the index or you must have the ______________ system privilege.
✅ ALTER ANY INDEX
🔘 ALTER PRIVILEGE
🔘 ANY INDEX ALTER
🔘 ALTER INDEX

10. A/an _________________ provides direct, fast access to rows and contains entries for values in a column.
🔘 Cluster
🔘 Table
🔘 View
✅ Index

11. What does the following code return?
SELECT SUM(bytes)/1024/1024 SizeMB FROM dba_segments WHERE segment_name ='EMP';
🔘 find size of table in GB
🔘 find size of table in KB
✅ find size of table in MB
🔘 find size of table in TB

12. What does the following code do?
SELECT Table_name FROM Dict WHERE Table_name LIKE '%TAB%';
🔘 Displaying Sequence for Table
🔘 Selecting Views for Table
🔘 Displaying synonyms for Table
✅ Displaying Views for Table

13. Which of the following is not a characteristic of PL/SQL?
🔘 Centrally accessible from single database
🔘 Executed on server without any network overhead
🔘 Embedded structured programming
✅ Performed on client Machine

14. EMP.sal%TYPE is an example of __________ data type.
✅ Scalar
🔘 Complex
🔘 Composite
🔘 Subtype

15. Which command is used to restore the table from recycle bin?
🔘 Rename
🔘 Purge
✅ Flashback
🔘 Delete

16. Which of the following is NOT true when you create an index in your own schema?
✅ You have CREATE ANY INDEX system privilege.
🔘 The table or cluster to be indexed is in your own schema.
🔘 You have INDEX privilege on the table to be indexed.
🔘 When a column is updated, all indexes that contain the column must be updated

17. The average number of transactions processed per minute is called:
🔘 Speed
🔘 Space Utilization
🔘 Multiplexing
✅ Transaction Throughput

18. Which of the following is NOT available while creating a sequence?
🔘 Start
🔘 Minvalue
🔘 Maxvalue
✅ End

19. Which of the following returns one value only?
🔘 PL/SQL Procedure
🔘 PL/SQL Control block
✅ PL/SQL Function
🔘 PL/SQL Block

20. A _____ is a logical representation of a table or combination of tables.
🔘 Tablespace
✅ View
🔘 Sequence
🔘 Procedures

 

21. A view derives its data from the tables on which it is based is called ___________.
🔘 Temporary Table
🔘 Permanent Table
✅ Base Table
🔘 Relational Table

22. Which of the following is not a rule name for updating a join view in Oracle?
✅ Remove
🔘 Insert
🔘 Update
🔘 Delete

23. How many rules are there in Oracle for updating a join view?
🔘 1
✅ 4
🔘 3
🔘 2

24. “Any INSERT, UPDATE, or DELETE operation on a join view can modify only one underlying base table at a time” is the description of __________.
✅ General Rule
🔘 Insert Rule
🔘 Delete Rule
🔘 Update Rule

25. Which column is used to get the next value of the sequence?
✅ NEXTVAL
🔘 NEXT
🔘 NEXTVALUE
🔘 NXTVL

26. ______ are database objects from which multiple users can generate unique integers.
✅ Sequences
🔘 Data Dictionary
🔘 Function
🔘 Synonyms

27. Which of the following is not available while creating sequence?
✅ End
🔘 Maxvalue
🔘 Minvalue
🔘 Start

28. Identify the correct statement about where CURRVAL and NEXTVAL cannot be used?
✅ The condition of a CHECK constraint
🔘 The SET clause of an UPDATE statement
🔘 The WHERE clause of a SELECT statement
🔘 A SELECT statement with a GROUP BY clause

29. To drop a sequence in another schema, you must have the ________ system privilege.
🔘 DROP CLUSTER
🔘 DROP SEQUENCE
✅ DROP ANY SEQUENCE
🔘 DROP ANY CLUSTER

30. TableName.Column%TYPE is a __________ data type.
✅ Scalar
🔘 Complex
🔘 Composite
🔘 Simple

31. In basic structure of PL/SQL, execution section starts with _____ keyword.
✅ BEGIN
🔘 END
🔘 DECLARE
🔘 EXCEPTIONS

32. In basic structure of PL/SQL, execution section ends with _____ keyword.
🔘 BEGIN
✅ END
🔘 DECLARE
🔘 EXCEPTIONS

33. In basic structure of PL/SQL, exception section starts with ______ keyword.
✅ EXCEPTIONS
🔘 DECLARE
🔘 BEGIN
🔘 END

34. A cursor is a _______ to this context area.
🔘 Variable
🔘 Integer
✅ Pointer
🔘 Function

35. PL/SQL is more powerful than pure __________.
✅ SQL
🔘 Python
🔘 Database
🔘 C#

36. Which of the following is not a syntax for while loop in PL/SQL?
🔘 LOOP – EXIT WHEN – END LOOP
🔘 WHILE – LOOP – END LOOP
🔘 FOR – LOOP – END LOOP
✅ WHILE – END LOOP

37. Which of the following is not a parameter for the PL/SQL procedure?
🔘 IN
🔘 OUT
✅ ININ
🔘 IN OUT

38. A __________ statement allows us to execute a statement or group of statements multiple times.
✅ Loop
🔘 Selection
🔘 If else
🔘 None of these

39. Which of the following program compiles and executes on SQL prompt?
✅ DECLARE BEGIN EXCEPTION END; /
🔘 DECLARE EXCEPTION END; /
🔘 DECLARE BEGIN EXCEPTION END;.
🔘 DECLARE BEGIN EXCEPTION END;

40. Which of the following data type does NOT belong to scalar data types family?
✅ Composite
🔘 Boolean
🔘 Number
🔘 Character

Question No: 21      (Marks: 01) - Please choose the correct option

 

Which of the following is/are an/the type(s) of exceptions?

A.    System-defined

B.     User-defined

C.     Both A. and B.

D.    None of the above

Question No: 22      (Marks: 01) - Please choose the correct option

 

Which command is used in order to raise an exception explicitly?

A.    RISE

B.     ROSE

C.     RAISE

D.    RINSE

Question No: 23      (Marks: 01) - Please choose the correct option

 

Oracle creates ____ when SQL statements are processed.

A.    Content Areas

B.     Context Areas

C.     Context Ids

D.    Content Ids

Question No: 24      (Marks: 01) - Please choose the correct option

 

In the contexts created by Oracle, a cursor represents a ____.

A.    Function

B.     Table

C.     Pointer

D.    None of the above

Question No: 25      (Marks: 01) - Please choose the correct option

 

 An SQL ____ refers to a program that retrieves and processes one row at a time, based on the results of the SQL statement.

A.    Cursor

B.     Function

C.     Procedure

D.    View

Question No: 26      (Marks: 01) - Please choose the correct option

 

Which of the following would cause an infinite loop to occur in a simple loop

A.    LOOP

B.     END LOOP

C.     IF – THEN

D.    EXIT

Question No: 27      (Marks: 01) - Please choose the correct option

 

Which identifier is valid?

A.    Customer_12

B.     Loop

C.     customer@orgA

D.    12customer

Question No: 28      (Marks: 01) - Please choose the correct option

 

Select the best answer below. What are the components of a package?

A.    Box, wrapping and binding

B.     Specification and content

C.     Header and body

D.    None of the given

Question No: 29      (Marks: 01) - Please choose the correct option

 

If you cannot specify a mode for a parameter, what is the default mode?

A.    OUT

B.     IN

C.     COPY

D.    DEFAULT

Question No: 30      (Marks: 01) - Please choose the correct option

 

Which of the given files are not copied in cold/offline backup.

A.    Data Files

B.     Control Files

C.     Init.ora and config.ora files

D.    Log files

Question No: 31      (Marks: 01) - Please choose the correct option

 

____________ is online backup.

A.    Hot

B.     Physical

C.     Cold

D.    Logical

Question No: 32      (Marks: 01) - Please choose the correct option

 

A management procedure called ___ control is required to control the process of concurrently performing operations on a database.

A.    Database

B.     Conspiracy

C.     Concurrency

D.    Relational

Question No: 33      (Marks: 01) - Please choose the correct option

 

It is called a schedule when it consists of a series of operations from one ___ to another.

A.    Transition

B.     Transaction

C.     Transportation

D.    Transcription

Question No: 34      (Marks: 01) - Please choose the correct option

 

In SQL, which of the following is not a data definition language command?

A.    RENAME

B.     REVOKE

C.     GRANT

D.    UPDATE

 

Question No: 35      (Marks: 01) - Please choose the correct option

 

The granting and revoking of roles by the user may cause some confusions when that user role is revoked. To overcome the above situation

A.    The privilege must be granted only by roles

B.     The privilege is granted by roles and users

C.     The user role cannot be removed once given

D.    By restricting the user access to the roles

Question No: 36      (Marks: 01) - Please choose the correct option

 

Which of the following data base objects can generate serial numbers?

A.    Synonyms

B.     Views

C.     Tables

D.    Sequences

Question No: 37      (Marks: 01) - Please choose the correct option

 

Which of the given is part of logical database?

A.    Control Files

B.     Redo Log Files

C.     Data Files

D.    Segments

Question No: 38      (Marks: 01) - Please choose the correct option

 

The size of the database block is decided at the time of ­­­­­­­­­­­­­­­_____________.

A.    Database creation

B.     Table creation

C.     Workspace creation

D.    Tablespace creation

Question No: 39      (Marks: 01) - Please choose the correct option

 

Which of the given synonym is used for Char Datatype in PL/SQL?

A.    Character

B.     Varchar

C.     Varchar2

D.    Ch

Question No: 40      (Marks: 01) - Please choose the correct option

 

A ___________of the transactions can be obtained by finding a linear order consistent with the partial order of the precedence graph.

A.    Direction graph

B.     Precedence graph

C.     Scheduling scheme

D.    Serializability order

Compiled MCQs with Correct and Incorrect Options

  1. The package specification declares the ___________ objects that are accessible from outside the package.
    • Public ✓
    • Local
    • Null
    • Private
  2. A package body can have an __________________part whose statements initialize variables or perform other one-time setups for the whole package.
    • Initialization ✓
    • Declare
    • Begin
    • Declaration
  3. The _____________ keyword specifies that you are creating a new trigger.
    • CREATE ✓
    • INITIATE
    • GENERATE
    • CAUSE
  4. A/an _____________ is a named PL/SQL block stored in the Oracle Database and executed automatically when a triggering event takes place.
    • Trigger ✓
    • Index block
    • Event
    • Control block
  5. Which of the following is correct statement to disable a trigger in PL/SQL?
    • ALTER TRIGGER trigger_name DISABLE; ✓
    • TRIGGER trigger_name DISABLE;
    • ALTER TRIGGER DISABLE;
    • TRIGGER DISABLE;
  6. An explicit cursor is an _____________ statement declared explicitly in the declaration section of the current block or a package specification.
    • SELECT ✓
    • INSERT
    • REPLY
    • REPLACE
  7. ____________ is mainly used with tables which return more than one row.
    • Cursor ✓
    • Column
    • Table
    • Index
  8. Which of the following is not an attribute of Explicit Cursor?
    • %CLOSE ✓
    • %FOUND
    • %NOTFOUND
    • %NOTFOUND (Repeated intentionally to confuse, but still valid)
  9. Which option is true about the following Code? CURSOR c_emp(p_job IN EMP.job%TYPE) IS SELECT * FROM EMP WHERE job = p_job;
    • Cursor with Parameters ✓
    • Cursor with Pointers
    • Cursor with Behavior
    • Cursor with Transition Graphs
  10. Which option is true about the following Code? CURSOR c_emp IS SELECT empno, ename, job FROM emp WHERE deptno=20;
  • Cursor Declaration ✓
  • Cursor Retrieval
  • Cursor Deletion
  • Cursor Updation
  1. The lowest address corresponds to the _______ element and the highest address to the _____ element.
  • First, last ✓
  • Last, Second Last
  • last, First
  • First, Second
  1. The key in Associative Array can be either an integer or a _________.
  • String ✓
  • Float
  • Double
  • Boolean
  1. We must specify the ________ and the type of elements stored in the varray.
  • Maximum size ✓
  • Datatype
  • Type of arguments
  • Number of arguments
  1. Dynamic SQL is a programming methodology for generating and running SQL statements at ______.
  • Run time ✓
  • Compile time
  • None of these
  • Run and compile time
  1. __________ is a programming methodology for generating and running SQL statements at run time.
  • Dynamic SQL ✓
  • Static SQL
  • Light SQL
  • Static and Dynamic SQL
  1. Which of the following statement is not true about Dynamic SQL?
  • It is useful when you know at compilation time the full text of a SQL statement. ✓
  • It is useful when writing general-purpose and flexible programs like ad hoc query systems.
  • It is useful when writing programs that must run database definition language (DDL) statements.
  • It is useful when the number or data types of its input and output variables are known.
  1. The system global area is shared by the Oracle processes, which include server processes and background processes, running on a _____ computer.
  • Single ✓
  • Various
  • None of these
  • Different
  1. Every running Oracle database is associated with at least _____ Oracle database instance.
  • One ✓
  • Two
  • Three
  • Five
  1. A Database is a set of physical files on disk created by the __________ statement.
  • CREATE DATABASE ✓
  • ADD DATABASE
  • GENERATE DATABASE
  • INSERT DATABASE
  1. We can start a database instance and mount (associate the instance with) one database, but not mount ___________ databases simultaneously with the same instance.
  • Two ✓
  • Three
  • Four
  • Five
  1. In a Single-instance configuration, a _________ relationship exists between the database and an instance.
  • one-to-one ✓
  • one-to-many
  • many-to-one
  • many-to-many
  1. In Oracle Real Application Clusters (Oracle RAC) configuration, a ________ relationship exists between the database and instances.
  • many-to-many ✓
  • one-to-one
  • one-to-many
  • many-to-one
  1. An instance begins when it is created with the ______ command and ends when it is terminated.
  • STARTUP ✓
  • START
  • BEGIN
  • CREATE
  1. The instance can mount a database only ______, close it only once, and open it only once.
  • Once ✓
  • Twice
  • None of these
  • Always
  1. The system identifier is used by default to locate the_______ .
  • None of these ✓ (Assuming correct based on context; please confirm actual option list if needed)
  • User
  • Session
  • Cache
  1. In a typical use case, which command can be used to start an instance and then mount and open the database, making it available for users?
  • STARTUP ✓
  • START
  • CREATE
  • BEGIN
  1. The data contained in the data files is accessible to _______.
  • Authorized Users ✓
  • None of these
  • Unauthorized Users
  • Authorized and Unauthorized Users
  1. A database _________ provides user access to a database.
  • Instance ✓
  • Table
  • None of these
  • Row
  1. Temporary tablespaces are created with the ________________.
  • CREATE TEMPORARY TABLESPACE ✓
  • INSERT TEMPORARY TABLESPACE
  • ADD TEMPORARY TABLESPACE
  • NEW TEMPORARY TABLESPACE
  1. ________ users do not have control over the current status of an Oracle database.
  • Normal ✓
  • Special
  • Privilege
  • All
  1. The checkpoint position acts as a ______ to the redo stream and is stored in the control file and in each data file header.
  • Pointer ✓
  • Array
  • Heap
  • Stack
  1. Which of the following is not a goal of Oracle Database when it uses checkpoints?
  • Increase the time required for recovery in case of an instance or media failure ✓
  • Ensure that all committed data is written to disk during a consistent shutdown
  • Establish data consistency
  • Ensure that dirty buffers in the buffer cache are written to disk regularly
  1. Checkpoint is a mechanism in which all the previous logs are removed from the system and are stored permanently in the ________.
  • Storage disk ✓
  • Buffer
  • Cache
  • None of these
  1. Shortest form of the Checkpoint Process is______________________.
  • CKPT ✓
  • CKPS
  • CHPT
  • CHPS
  1. Which of the following is not a checkpoint situation when it occurs?
  • ALTER DATABASE BEGIN BACKUP statement ✓
  • ALTER SYSTEM CHECKPOINT statement
  • Offline redo log switch
  • Consistent database shutdown
  1. An incremental checkpoint is a type of _______ checkpoint.
  • Online ✓
  • Offline
  • Thread
  • Subject
  1. Checkpoint command will run through ______.
  • Sys user ✓
  • DBA
  • User name
  • User
  1. At each switch of the redo log files _________ command will occur.
  • Checkpoint ✓
  • Update
  • Alter
  • System privileges
  1. Which of the following is a parameter that can be used in PL/SQL function?
  • IN ✓
  • OUT
  • IN OUT
  • ALL
  1. PL/SQL uses a _______ structured syntax.
  • Block ✓
  • Banner
  • Physical
  • Logical

Question No: 41      (Marks: 03)

 

Write the syntax to create view in PL/SQL.

Answer

 

CREATE VIEW view_name AS

SELECT column1, column2, ...

FROM table_name

WHERE condition;

 

 

Question No: 42      (Marks: 03)

 

Write the PL/SQL code to create a sequence to generate the primary key of the Employee table incremented by 2.

 

Answer

CREATE SEQUENCE employee_seq

START WITH 1

INCREMENT BY 2

NOCACHE

NOCYCLE;

 

 

Question No: 43      (Marks: 03)

 

Write the difference between PL/SQL Procedure and PL/SQL Functions.

 

Answer

·         PL/SQL Procedure:

·         A procedure is a named block of PL/SQL code that performs a specific task and can accept parameters.

·         It can return multiple values through OUT parameters.

·         PL/SQL Function:

·         A function is also a named block of PL/SQL code but is designed to return a single value.

·         It can be used in SQL statements and can only have IN parameters.

 

 

 

 

Question No: 44      (Marks: 03)

 

Write the names of any three scaler data types used in PL/SQL programs.

 

Answer

1.      NUMBER

2.      VARCHAR2

3.      DATE

 

 

 

Question No: 45      (Marks: 03)

 

Write PL/SQL statement to remove the privilege from the user Amir.

 

Answer

REVOKE privilege_name FROM Amir;

 

 

Question No: 46      (Marks: 03)

 

Write a transaction which reads two elements X and Y and perform the following operations and write the results.

X:= X+500

Y:= Y+1000

 

Answer

BEGIN

  X := X + 500;

  Y := Y + 1000;

END;

 

 

Question No: 47      (Marks: 05)

 

Write sample query for creating schema named “Product” for sample user “AE” using following attributes?

1.      Table name is “new_product”

2.      View is “new_product_view”

3.      Grants is “SELECT” object privilege on view to the sample user “HR”

 

 

Answer

CREATE TABLE new_product (

  product_id NUMBER PRIMARY KEY,

  product_name VARCHAR2(100),

  price NUMBER

);

 

CREATE VIEW new_product_view AS

SELECT product_id, product_name

FROM new_product;

 

GRANT SELECT ON new

 

Question No: 48      (Marks: 05)

 

Define deadlock in context of oracle and also describe to resolve this deadlock issue.

 

Answer

Answer:

Definition of Deadlock: A deadlock in Oracle occurs when two or more sessions are waiting for each other to release resources, creating a cycle of dependencies that prevents any of the sessions from proceeding. For example, if Session A holds a lock on Resource 1 and is waiting for Resource 2, while Session B holds a lock on Resource 2 and is waiting for Resource 1, neither session can continue, resulting in a deadlock.

Resolving Deadlock Issues:

1.      Automatic Detection and Resolution:

·         Oracle automatically detects deadlocks and resolves them by terminating one of the sessions involved in the deadlock. The terminated session receives an error message indicating that it has been rolled back due to a deadlock.

2.      Application Design:

·         To prevent deadlocks, design applications to access resources in a consistent order. For example, if multiple sessions need to access the same resources, ensure they do so in the same sequence.

3.      Lock Timeout:

·         Implement lock timeouts in your application. This means that if a session cannot acquire a lock within a specified time, it will give up and can retry later, reducing the chances of deadlocks.

4.      Monitoring:

·         Use Oracle's monitoring tools to identify and analyze deadlocks. The V$LOCK and V$SESSION views can help in diagnosing deadlocks.

 

 

Question No: 49      (Marks: 05)

 

Write the SQL Plus statements for following.

 

1.      Recreating control files.

2.      Viewing Alert Log using data dictionary

3.      To trace files for your current session.

Answer

 

STARTUP NOMOUNT;

CREATE CONTROLFILE REUSE DATABASE "your_database_name" RESETLOGS NOARCHIVELOG

MAXLOGFILES 16

MAXLOGFILESIZE 32M

MAXDATAFILES 100

MAXINSTANCES 1

MAXLOGHISTORY 292

LOGFILE

  GROUP 1 ('/path/to/log1.log', '/path/to/log2.log') SIZE 50M,

  GROUP 2 ('/path/to/log3.log', '/path/to/log4.log') SIZE 50M

DATAFILE

  '/path/to/datafile1.dbf',

  '/path/to/datafile2.dbf'

CHARACTER SET AL32UTF8;

 

 

SELECT * FROM V$DICTIONARY WHERE OBJECT_NAME = 'ALERT_LOG';

 

ALTER SESSION SET sql_trace = TRUE;

 

ALTER SESSION SET sql_trace = FALSE;

 

 

 

 

 

 

 

 

 

 

 

Question No: 50      (Marks: 05)

 

Given are the entities of School management system, you have to create ERD for this. Mention the primary key and cardinalities among all entities.

1.      Teacher

2.      Class

3.      Student

Answer

Entities and Relationships for School Management System:

1.      Teacher

·         Primary Key: Teacher_ID

·         Attributes: Name, Subject, Email, Phone

2.      Class

·         Primary Key: Class_ID

·         Attributes: Class_Name, Room_Number, Schedule

3.      Student

·         Primary Key: Student_ID

·         Attributes: Name, Age, Email, Phone

Cardinalities:

·         A Teacher can teach multiple Classes (1 to many).

·         A Class can have multiple Students enrolled (1 to many).

·         A Student can enroll in multiple Classes (many to many).

 


Tags:

Post a Comment

0 Comments