Ad Code

CS409P Final Term MCQs | Past Paper cs409p 400 MCQs

Rana Ejaz July 07, 2025

 

Past Paper cs409p


Useful for Midterm 400 MCQs

With Correct Answers – CS409P

 

CS409P MCQs – Topics 1 to 25

🧾 Topic 1: Data, Information & Database

  1. Which of the following is an example of data?
    A) 250 PKR profit report
    B) "Pass" result status
    C) 42, 56, 78
    D) 15% increase in sales
    ✔ Correct Answer: C
  2. Information is defined as:
    A) Unprocessed facts
    B) Processed data with meaning
    C) Binary code
    D) Raw text
    ✔ Correct Answer: B

🧾 Topic 2–5: DBMS & Its Components

  1. Which of the following is NOT an example of a DBMS?
    A) Oracle
    B) MS Excel
    C) IBM DB2
    D) MySQL
    ✔ Correct Answer: B
  2. Which DBMS language is used to create and modify the structure of database objects?
    A) DML
    B) DCL
    C) DDL
    D) XML
    ✔ Correct Answer: C

🧾 Topic 6: DBMS Architecture (2-tier and 3-tier)

  1. Which architecture allows a client to directly interact with the database server?
    A) 3-tier
    B) Teleprocessing
    C) 2-tier
    D) Distributed
    ✔ Correct Answer: C
  2. In 3-tier architecture, database connections are managed through:
    A) MS Access
    B) UI Layer
    C) JDBC / ODBC via Application Layer
    D) Direct user request
    ✔ Correct Answer: C

🧾 Topic 7: Redo Logs

  1. What is the purpose of a Redo Log in DBMS?
    A) Backup only
    B) Reversing transactions
    C) Recovering committed transactions after crash
    D) Security verification
    ✔ Correct Answer: C

🧾 Topic 8–10: Keys – Super, Candidate, Composite

  1. An attribute that uniquely identifies each row in a table is called:
    A) Foreign Key
    B) Composite Key
    C) Primary Key
    D) Surrogate Key
    ✔ Correct Answer: C
  2. Which of the following is a Composite Key?
    A) (Card#, TransNo)
    B) Email
    C) Name
    D) CNIC#
    ✔ Correct Answer: A
  3. A candidate key is:
    A) A key not used in any relation
    B) A column that refers another table
    C) Any attribute that can act as a Primary Key
    D) Automatically generated key
    ✔ Correct Answer: C
  4. A Super Key is:
    A) Primary Key with extra columns
    B) Always equal to Foreign Key
    C) Always NULL
    D) Only used in NoSQL
    ✔ Correct Answer: A

🧾 Topic 11–12: Primary Keys

  1. Which of the following cannot be used as a Primary Key?
    A) Unique ID
    B) CNIC
    C) NULL value
    D) Roll Number
    ✔ Correct Answer: C
  2. What is a good quality of a Primary Key?
    A) Long and text-based
    B) Nullable
    C) Short, fixed-length, never changes
    D) Derived from multiple tables
    ✔ Correct Answer: C

🧾 Topic 13–16: Surrogate Keys

  1. A surrogate key is typically:
    A) User-chosen key
    B) Meaningful value
    C) System-generated, meaningless to user
    D) Composite of two columns
    ✔ Correct Answer: C
  2. Which of the following is an example of a surrogate key?
    A) Card#
    B) CNIC#
    C) Invoice# (Auto-generated)
    D) Mobile number
    ✔ Correct Answer: C

🧾 Topic 17–22: Foreign Keys

  1. Which key is used to create a relationship between two tables?
    A) Primary Key
    B) Composite Key
    C) Foreign Key
    D) Super Key
    ✔ Correct Answer: C
  2. A foreign key must reference a:
    A) Derived column
    B) Unique column
    C) Primary Key of another table
    D) Null field
    ✔ Correct Answer: C
  3. Which of the following is true about Foreign Keys?
    A) Always unique
    B) Always NULL
    C) Can be part of composite key
    D) Not linked to any table
    ✔ Correct Answer: C

🧾 Topic 23–25: Composite Key Examples

  1. Which combination would be a composite key in ATMTransaction table?
    A) Card#, Amount
    B) Card#, TransNo
    C) TransType
    D) Machine#
    ✔ Correct Answer: B
  2. Can a composite key contain a foreign key?
    A) No
    B) Yes
    C) Only if numeric
    D) Only in 2-tier DBMS
    ✔ Correct Answer: B

CS409P – MCQs from Topic 76 & 77

(With Correct Answers – For Midterm/Final Practice)

🧾 Topic 76: Readonly & Online Status of Tablespaces

  1. What is the correct SQL command to make a tablespace read-only?
    A)     ALTER TABLESPACE ts1 WRITE ONLY;
    B)     ALTER TABLESPACE ts1 READONLY;
    C)     ALTER TABLESPACE ts1 READ ONLY;
    D)     MODIFY TABLESPACE ts1 READ ONLY;
    ✔ Correct Answer: C
  1. Which parameter delays access to read-only files until first read?
    A)     DELAYED_FILE_ACCESS
    B)     READ_ONLY_OPEN_DELAYED
    C)     LAZY_FILE_ACCESS
    D)     DELAYED_READ_FILES
    ✔ Correct Answer: B
  1. What happens when READ_ONLY_OPEN_DELAYED is set to TRUE?
    A) Read-only files open faster and show full size immediately
    B) They are never accessed unless restored manually
    C) They are accessed only upon first data read
    D) They are permanently locked
    ✔ Correct Answer: C
  1. Which of the following views shows read-only files with "DELAYED OPEN"?
    A)     V$DATAFILE_HEADER
    B)     V$RECOVER_FILE
    C)     V$TABLESPACE
    D)     V$TEMPFILE
    ✔ Correct Answer: B
  1. Which operation is not affected by READ_ONLY_OPEN_DELAYED=TRUE?
    A)     ALTER TABLESPACE ... ONLINE
    B)     V$RECOVER_FILE
    C)     ALTER DATABASE OPEN RESETLOGS
    D)     V$DATAFILE
    ✔ Correct Answer: C
  1. Before taking a tablespace read-only, what must you do?
    A) Drop all objects in it
    B) Make it offline
    C) Copy data files to WORM device
    D) Encrypt the tablespace
    ✔ Correct Answer: C
  1. Which clause is used to rename data files?
    A)     ALTER FILE NAME
    B)     MODIFY DATAFILE
    C)     RENAME DATAFILE
    D)     CHANGE FILE TO
    ✔ Correct Answer: C

🧾 Topic 77: Altering & Maintaining Tablespaces

  1. Which of the following operations can be performed using ALTER TABLESPACE on a locally managed tablespace?
    A) Converting to dictionary-managed
    B) Changing to temporary
    C) Adding data file
    D) Shrinking a segment
    ✔ Correct Answer: C
  1. Which clause resizes a bigfile tablespace?
    A)     AUTOEXTEND
    B)     EXPAND
    C)     ALTER SIZE
    D)     RESIZE
    ✔ Correct Answer: D
  1. What error occurs if you use ADD DATAFILE in a bigfile tablespace?
    A) File not found
    B) Syntax error
    C) Error — not allowed in bigfile tablespace
    D) Permission denied
    ✔ Correct Answer: C
  1. Which command takes a temp file offline in a temporary tablespace?
    A)     ALTER TABLESPACE temp OFFLINE;
    B)     ALTER TABLESPACE tempfile OFFLINE;
    C)     ALTER DATABASE TEMPFILE OFFLINE;
    D) Both B and C
    ✔ Correct Answer: D
  1. Which of the following cannot be altered in a locally managed tablespace?
    A) Adding data file
    B) Changing to temporary
    C) Renaming data file
    D) Changing autoextension
    ✔ Correct Answer: B

CS409P – MCQs from Topics 78 to 80

(With Correct Answers – Best for Practice & Midterm)

🧾 Topic 78: Renaming & Dropping Tablespaces

  1. Which SQL clause is used to rename a tablespace?
    A)     ALTER NAME
    B)     RENAME TABLESPACE
    C)     ALTER TABLESPACE ... RENAME TO
    D)     MODIFY TABLESPACE
    ✔ Correct Answer: C
  1. Which tablespaces cannot be renamed?
    A) Temporary Tablespace
    B) SYSTEM and SYSAUX
    C) Bigfile Tablespace
    D) Read-only Tablespace
    ✔ Correct Answer: B
  1. What must be done before dropping a tablespace?
    A) Make it read-only
    B) Shrink all files
    C) Take it offline
    D) Backup the entire database
    ✔ Correct Answer: C
  1. Which command drops a tablespace along with its data files?
    A)     DROP TABLESPACE ts1 INCLUDING SEGMENTS;
    B)     DROP TABLESPACE ts1 DELETE FILES;
    C)     DROP TABLESPACE ts1 INCLUDING CONTENTS AND DATAFILES;
    D)     DROP DATABASE ts1 WITH FILES;
    ✔ Correct Answer: C
  1. Which error appears if a table is created in a dropped tablespace?
    A) ORA-00001
    B) ORA-00959
    C) ORA-01500
    D) ORA-99999
    ✔ Correct Answer: B

🧾 Topic 79: Managing SYSAUX Tablespace

  1. What is the purpose of the SYSAUX tablespace?
    A) To store user tables
    B) To replace SYSTEM tablespace
    C) To reduce the load on SYSTEM tablespace
    D) For backup operations only
    ✔ Correct Answer: C
  1. Which view is used to monitor SYSAUX tablespace components?
    A)     V$TABLESPACE_INFO
    B)     DBA_SYSAUX_MONITOR
    C)     SYSAUX_STATS
    D)     V$SYSAUX_OCCUPANTS
    ✔ Correct Answer: D
  1. If SYSAUX tablespace becomes unavailable, what happens?
    A) Database shuts down
    B) Core functionality remains, but features may fail
    C) No effect
    D) SYSTEM tablespace stops working
    ✔ Correct Answer: B
  1. SYSAUX tablespace stores metadata of which Oracle component?
    A) Backup Logs
    B) Oracle Enterprise Manager Repository
    C) User Schema
    D) Flashback Data
    ✔ Correct Answer: B

🧾 Topic 80: Repairing Tablespaces

  1. Which package is used for repairing locally managed tablespaces?
    A)     DBMS_REPAIR_ADMIN
    B)     DBMS_DATAFIX
    C)     DBMS_SPACE_ADMIN
    D)     DBA_FIX_TOOL
    ✔ Correct Answer: C
  1. The DBMS_SPACE_ADMIN package is mainly used for:
    A) Monitoring disk usage
    B) Import/export
    C) Diagnosing & repairing LMT issues
    D) Enabling redo logging
    ✔ Correct Answer: C
  1. Before repairing, what should be identified first?
    A) Operating system
    B) Instance name
    C) Tablespace type and problem scenario
    D) User role
    ✔ Correct Answer: C

 

CS409P – 50 Important Mixed MCQs (Topics 1–80)

Best for Midterm & Final Exam Preparation

1.      Which of the following is NOT a type of SQL command?
A) Data Definition Language
B) Data Creation Language
C) Data Control Language
D) Data Manipulation Language
✔ Correct Answer: B

2.      Which of the following is NOT an example of DBMS?
A) Oracle
B) IBM DB2
C) MS Excel
D) Ingress
✔ Correct Answer: C

3.      What is a Redo Log used for?
A) To reverse changes in a transaction
B) To store user session info
C) To recover committed changes after crash
D) To store backups
✔ Correct Answer: C

4.      Which of the following represents a weak entity in an ER diagram?
A) Oval
B) Double Oval
C) Double Rectangle
D) Diamond
✔ Correct Answer: C

5.      A composite key consists of:
A) A primary key with NULLs
B) Multiple foreign keys
C) Two or more attributes that uniquely identify a row
D) A derived key
✔ Correct Answer: C

6.      Which of the following is a multi-valued attribute?
A) CNIC
B) Email
C) Color
D) Salary
✔ Correct Answer: C

7.      Which attribute type contains sub-parts?
A) Multi-valued
B) Derived
C) Composite
D) Single
✔ Correct Answer: C

8.      Primary keys must be:
A) Unique and Nullable
B) Unique and Not Null
C) Composite and Derived
D) Auto-generated only
✔ Correct Answer: B

9.      An attribute that holds more than one value is called:
A) Complex Attribute
B) Multi value attribute
C) Redundant attribute
D) Derived attribute
✔ Correct Answer: B

10.  Which SQL command is used to change database structures?
A) DML
B) DCL
C) DDL
D) DQL
✔ Correct Answer: C

11.  Which symbol represents a relationship in ER diagram?
A) Square
B) Oval
C) Diamond
D) Dotted Line
✔ Correct Answer: C

12.  Which key is automatically generated by the system and has no business meaning?
A) Composite Key
B) Candidate Key
C) Surrogate Key
D) Foreign Key
✔ Correct Answer: C

13.  What does ODBC stand for?
A) Oracle Data Base Connectivity
B) Open Data Binary Code
C) Open Database Connectivity
D) Oracle Direct Backup Control
✔ Correct Answer: C

14.  Which architecture is best for thousands of users with high security?
A) 1-tier
B) 2-tier
C) 3-tier
D) Peer-to-peer
✔ Correct Answer: C

15.  What is the role of a Database Administrator (DBA)?
A) Writing user reports
B) Designing UI
C) Managing all DB activities
D) Creating graphics
✔ Correct Answer: C

16.  In a relational database, a foreign key:
A) Must be NULL
B) Must reference a primary key in another table
C) Is always composite
D) Can never be indexed
✔ Correct Answer: B

17.  Which key uniquely identifies a record but is not selected as the primary key?
A) Surrogate Key
B) Candidate Key
C) Foreign Key
D) Redundant Key
✔ Correct Answer: B

18.  Which SQL clause renames a tablespace?
A) RENAME TABLESPACE
B) ALTER TABLESPACE ... RENAME TO
C) MODIFY TABLESPACE NAME
D) TABLESPACE RENAME AS
✔ Correct Answer: B

19.  Which command makes a tablespace read-only?
A) MODIFY TABLESPACE ts1 READONLY;
B) ALTER TABLESPACE ts1 READ ONLY;
C) READONLY TABLESPACE ts1;
D) SET ts1 TO READONLY;
✔ Correct Answer: B

20.  Which parameter delays access to read-only files?
A) READ_DELAYED
B) FILE_DELAY
C) READ_ONLY_OPEN_DELAYED
D) OPEN_DELAY
✔ Correct Answer: C

21.  Which clause resizes a bigfile tablespace?
A) CHANGE SIZE
B) ALTER FILE SIZE
C) RESIZE
D) MODIFY SIZE
✔ Correct Answer: C

22.  Which system view shows SYSAUX tablespace occupants?
A) DBA_SYSAUX_STATS
B) V$SYSAUX_OCCUPANTS
C) SYSAUX_USERS
D) DB_SYSAUX_INFO
✔ Correct Answer: B

23.  Which SQL command drops a tablespace with its data files?
A) DROP TABLESPACE ts1 DELETE FILES;
B) DROP TABLESPACE ts1 INCLUDING CONTENTS AND DATAFILES;
C) DELETE TABLESPACE ts1;
D) DROP ts1 CASCADE FILES;
✔ Correct Answer: B

24.  Which package is used to repair locally managed tablespaces?
A) DBMS_REPAIR
B) DBMS_DATA_FIX
C) DBMS_SPACE_ADMIN
D) DBMS_DIAG
✔ Correct Answer: C

25.  Which key is used to connect two tables together?
A) Primary Key
B) Super Key
C) Composite Key
D) Foreign Key
✔ Correct Answer: D

📘 CS409P – MCQs 26 to 50 (Mixed, Topics 1–80)

Continuation from previous list

26.  What is a surrogate key typically used for?
A) To connect tables
B) To hold business data
C) As a system-generated unique identifier
D) To store composite attributes
✔ Correct Answer: C

27.  Which of the following can exist in a table multiple times?
A) Primary Key
B) Candidate Key
C) Foreign Key
D) Super Key
✔ Correct Answer: C

28.  Which key may include additional, unnecessary attributes but still uniquely identifies records?
A) Candidate Key
B) Surrogate Key
C) Super Key
D) Composite Key
✔ Correct Answer: C

29.  Which attribute type is calculated using other attributes?
A) Composite
B) Derived
C) Foreign
D) Surrogate
✔ Correct Answer: B

30.  Which symbol is used to represent a simple attribute in an ERD?
A) Double oval
B) Oval
C) Diamond
D) Rectangle
✔ Correct Answer: B

31.  A primary key cannot contain:
A) Numbers
B) Characters
C) NULL values
D) Unique data
✔ Correct Answer: C

32.  Which of the following is a correct composite key in ATMTransaction table?
A) TransNo, Amount
B) Card#, Amount
C) Card#, TransNo
D) Machine#
✔ Correct Answer: C

33.  Which type of architecture is more secure and scalable?
A) File server
B) 2-tier
C) 3-tier
D) Local DB
✔ Correct Answer: C

34.  Which layer in 3-tier architecture processes business logic?
A) Client layer
B) Application layer
C) Database layer
D) Middleware
✔ Correct Answer: B

35.  Which tool helps Java applications connect to databases?
A) API
B) JDBC
C) ORM
D) HTML
✔ Correct Answer: B

36.  Which operation can be performed using ALTER TABLESPACE on temporary tablespaces?
A) Add temp file
B) Rename database
C) Create user
D) Drop schema
✔ Correct Answer: A

37.  What happens when a tablespace is dropped?
A) It is backed up
B) It's moved to recycle bin
C) Associated files are deleted if specified
D) Data is archived automatically
✔ Correct Answer: C

38.  What does the V$SYSAUX_OCCUPANTS view show?
A) User roles
B) Tablespace size only
C) Info about components using SYSAUX
D) System passwords
✔ Correct Answer: C

39.  Which of the following is a derived attribute example?
A) Age (calculated from DOB)
B) CNIC
C) Email
D) Phone Number
✔ Correct Answer: A

40.  The ALTER DATABASE TEMPFILE ... ONLINE command is used to:
A) Drop a temp file
B) Encrypt a temp file
C) Reactivate a temp file
D) Create a new user
✔ Correct Answer: C

41.  Which of the following is not a valid ERD component?
A) Entity
B) Attribute
C) Foreign Key
D) Function
✔ Correct Answer: D

42.  Which tablespace is automatically created with SYSTEM and stores performance data?
A) USERS
B) TEMP
C) SYSAUX
D) ADMIN
✔ Correct Answer: C

43.  A unique key can:
A) Have duplicate values
B) Allow NULL once
C) Never be indexed
D) Always be a primary key
✔ Correct Answer: B

44.  Which of the following identifies a specific record and never allows NULLs?
A) Unique Key
B) Candidate Key
C) Primary Key
D) Composite Key
✔ Correct Answer: C

45.  Which ERD symbol is used to show multivalued attributes?
A) Oval
B) Rectangle
C) Double Oval
D) Dashed Line
✔ Correct Answer: C

46.  Which view shows read-only files as "DELAYED OPEN"?
A) V$BACKUP
B) V$RECOVER_FILE
C) V$CONTROL_FILE
D) V$USER_TABLES
✔ Correct Answer: B

47.  Which key ensures referential integrity in database?
A) Primary Key
B) Super Key
C) Foreign Key
D) Composite Key
✔ Correct Answer: C

48.  Which key type is used to ensure uniqueness but not necessarily for relationship?
A) Candidate Key
B) Foreign Key
C) Derived Key
D) Surrogate Key
✔ Correct Answer: A

49.  Which layer in 2-tier architecture connects directly to the database?
A) Middleware
B) Client application
C) Frontend Web Server
D) Authentication server
✔ Correct Answer: B

50.  Which command shrinks a temporary tablespace in Oracle?
A) SHRINK TEMPFILE
B) RESIZE TABLESPACE
C) SHRINK SPACE
D) DROP TEMP
✔ Correct Answer: C

📘 CS409P – MCQs 51 to 100 (Topics 1–80 Mixed)

Midterm + Final Exam Ready – With Correct Answers

51.  Which of the following SQL types is responsible for controlling access to data?
A) DDL
B) DML
C) DCL
D) TCL
✔ Correct Answer: C

52.  Which SQL type includes commands like COMMIT and ROLLBACK?
A) DDL
B) DML
C) DCL
D) TCL
✔ Correct Answer: D

53.  Which of the following statements is used to provide privileges to a user?
A) GRANT
B) ALLOW
C) PERMIT
D) ENABLE
✔ Correct Answer: A

54.  Which one is not a valid SQL data type?
A) VARCHAR
B) NUMBER
C) DECIMAL
D) TEXTBOOK
✔ Correct Answer: D

55.  Which of the following stores the metadata in a DBMS?
A) Table
B) View
C) Catalog
D) Index
✔ Correct Answer: C

56.  What does ERD stand for?
A) Entity Reference Diagram
B) Entity Relationship Diagram
C) External Resource Diagram
D) Extended Relational Design
✔ Correct Answer: B

57.  A derived attribute in an ERD is represented by:
A) Solid oval
B) Dashed oval
C) Rectangle
D) Diamond
✔ Correct Answer: B

58.  Which of the following is an example of a composite attribute?
A) Phone Number
B) Email
C) Address
D) Age
✔ Correct Answer: C

59.  Which of the following is used to uniquely identify a row in a table?
A) Foreign key
B) Primary key
C) Composite attribute
D) Relationship
✔ Correct Answer: B

60.  Which command is used to permanently save changes in SQL?
A) SAVE
B) STORE
C) COMMIT
D) EXECUTE
✔ Correct Answer: C

61.  In ERD, a double oval represents:
A) Simple attribute
B) Composite attribute
C) Multivalued attribute
D) Derived attribute
✔ Correct Answer: C

62.  Which constraint is used to ensure data integrity between tables?
A) NOT NULL
B) UNIQUE
C) FOREIGN KEY
D) CHECK
✔ Correct Answer: C

63.  Which language is used for data retrieval?
A) DDL
B) DML
C) DQL
D) DCL
✔ Correct Answer: C

64.  In DBMS, normalization is done to:
A) Increase redundancy
B) Speed up queries
C) Remove redundancy
D) Create indexes
✔ Correct Answer: C

65.  Which of the following normal forms removes partial dependency?
A) 1NF
B) 2NF
C) 3NF
D) BCNF
✔ Correct Answer: B

66.  What does BCNF stand for?
A) Basic Composite Normal Form
B) Boyce-Codd Normal Form
C) Big Column Normal Form
D) Binary Code Normal Form
✔ Correct Answer: B

67.  Which SQL command is used to remove a table?
A) DELETE TABLE
B) REMOVE
C) DROP
D) ERASE
✔ Correct Answer: C

68.  Which clause is used to filter rows in SQL SELECT?
A) HAVING
B) ORDER
C) GROUP BY
D) WHERE
✔ Correct Answer: D

69.  Which clause is used to filter groups?
A) WHERE
B) ORDER BY
C) HAVING
D) LIKE
✔ Correct Answer: C

70.  Which symbol is used to represent entity in ERD?
A) Oval
B) Rectangle
C) Diamond
D) Double Line
✔ Correct Answer: B

71.  Which join returns matching rows from both tables?
A) LEFT JOIN
B) RIGHT JOIN
C) INNER JOIN
D) OUTER JOIN
✔ Correct Answer: C

72.  Which SQL function returns the number of rows?
A) COUNT(*)
B) SUM()
C) AVG()
D) MAX()
✔ Correct Answer: A

73.  Which key can have NULL values?
A) Primary Key
B) Foreign Key
C) Candidate Key
D) Surrogate Key
✔ Correct Answer: B

74.  Which of these is a system-generated key?
A) Candidate Key
B) Surrogate Key
C) Composite Key
D) Foreign Key
✔ Correct Answer: B

75.  Which tool allows Java apps to connect to DBMS?
A) JCONNECT
B) JDBC
C) JSCRIPT
D) JDBA
✔ Correct Answer: B

76.  Which is the most secure and scalable architecture?
A) 1-tier
B) 2-tier
C) 3-tier
D) 4-tier
✔ Correct Answer: C

77.  What happens if you drop a tablespace with active segments?
A) It gets archived
B) It gives an error
C) It deletes all backups
D) It renames it
✔ Correct Answer: B

78.  Which package is used to repair tablespaces?
A) DBMS_FIX
B) DBMS_TABLE_ADMIN
C) DBMS_SPACE_ADMIN
D) DBMS_DATA_FIX
✔ Correct Answer: C

79.  Which tablespace stores performance & metadata info?
A) SYSTEM
B) USER
C) SYSAUX
D) TEMP
✔ Correct Answer: C

80.  Which clause is used to drop temp file?
A) DELETE TEMP
B) DROP INCLUDING DATAFILES
C) REMOVE TEMP FILE
D) ERASE TEMP
✔ Correct Answer: B

 

 

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