PL/SQL and SQL*Plus: SQL*Plus is a command-line tool used to interact with Oracle, Oracle SQL, System Process, Pros

stem Process, Pros & Cons : Oracle is an RDBMS

Oracle databases. You can write and run SQL and PL/SQL commands in it. It just sends developed by Oracle Corp. It stores and manages large amounts of data and is known
your code to the database and shows you the result. It doesn't process or validate logic for performance, security, and scalability. Oracle SQL is the query language used in
—it only executes what you write. PL/SQL is Oracle’s procedural extension of SQL. It Oracle DB. It supports standard SQL operations with Oracle-specific features like
allows writing blocks of code that include conditions (IF), loops (FOR, WHILE), DUAL, ROWNUM, and advanced functions. Oracle’s system runs on processes like: User
variables, error handling, and reusable components like procedures and functions. It's Process (user interaction), Server Process (executes SQL), Background processes like
mainly used when you want to automate tasks, apply logic, or build database programs. DBWR, LGWR, SMON, and PMON for managing storage, logs, recovery, etc. Advantages:
SQL*Plus runs PL/SQL. Think of SQL*Plus as the editor, and PL/SQL as the actual Fast, secure, scalable, supports PL/SQL, handles large data. Disadvantages: Costly,
program being written and run through it. resource-heavy, complex for new users.

DBMS/RDBMS/OODBMS : A Database Management System or DBMS is a software system that enables users to store, modify, and retrieve data efficiently. It acts as a middle
layer between the user and the physical storage of data. Although DBMS supports operations like insert, update, delete, and select, it stores data in traditional formats like files or
hierarchical structures without enforcing strict relationships between different sets of data. This can lead to data redundancy and inconsistency in large systems. On the other
hand, RDBMS, or Relational Database Management System, organizes data in tabular format using rows and columns. It ensures relationships between tables using primary keys
and foreign keys and supports constraints to maintain data integrity. RDBMS provides powerful features such as transactions, concurrency control, normalization, and SQL
querying, which make it more suitable for large applications. In contrast, OODBMS, or Object-Oriented Database Management System, follows the object-oriented paradigm. It
stores data in the form of objects that include both data and the functions that operate on them. This system supports encapsulation, inheritance, and polymorphism. OODBMS is
particularly effective in applications where complex data types are involved, and it allows direct integration with object-oriented programming languages. The main difference
among the three lies in how data is stored and interacted with. DBMS lacks relational structure and advanced features, RDBMS adds relational integrity and SQL support, while
OODBMS focuses on objects and object-based logic, aligning closely with modern application code.
DATA TYPES, OPERATOINS, CRUD : Oracle SQL is a language used to interact with Oracle databases. It is used to perform operations like creating, modifying, deleting, and
reading data. Oracle SQL supports various data types such as character types like CHAR and VARCHAR2, numeric types like NUMBER, and date/time types like DATE and
TIMESTAMP. It also includes large object types like CLOB and BLOB for storing large text or binary data. CRUD operations in SQL refer to Create, Read, Update, and Delete.
These are done using commands like INSERT, SELECT, UPDATE, and DELETE respectively, and are part of the Data Manipulation Language. Oracle SQL is divided into four
categories. DML is used to work with data, including insert, update, and delete commands. DDL is used to define or alter the structure of tables and other database objects, using
commands like CREATE, DROP, and ALTER. DCL handles permissions and access with GRANT and REVOKE. TCL deals with transactions and uses commands like COMMIT
and ROLLBACK to manage changes in the database. Together, these components form the foundation of how Oracle SQL is used to interact with and manage data effectively.
A PROCEDURE in PL/SQL is a stored subprogram that performs a specific task and is saved in the database for repeated use. It can take input and output parameters but does
not return a value directly. Procedures are used for operations like inserting data, updating values, or executing complex tasks, and they improve code reusability, performance,
and security. All the SQL logic is written once and reused many times, and if needed, we can group related procedures using packages which help in organizing the code. Unlike
functions, procedures do not return a value and cannot be used inside SELECT queries. Functions are used when we need to return a result, while procedures are meant to perform
actions. Both help in modularizing the code, but procedures are more flexible for task-oriented operations.
A CURSOR in PL/SQL is a pointer that allows row-by-row processing of query results. It is needed when a SELECT query returns multiple rows, and we want to handle each
row individually inside PL/SQL. There are two types of cursors: implicit and explicit. An implicit cursor is automatically created by Oracle when DML or single-row SELECT
queries are executed. You can use attributes like SQL%ROWCOUNT or SQL%FOUND to get information about the execution. For multi-row SELECT queries, we need explicit
cursors, which are declared manually and then opened, fetched, and closed using proper commands. Explicit cursors give more control over the row processing. Cursor attributes
like %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN help in controlling and checking the status of cursor operations. Without cursors, it would be difficult to manage
queries that return more than one row, and that’s why they are essential in PL/SQL programming.
Transaction in PL/SQL is a logical unit of work consisting of one or more SQL Group Functions like SUM(), AVG(), COUNT(), etc., work on multiple rows and
operations. Oracle uses COMMIT to save the changes permanently, and ROLLBACK to return a single value. Used with GROUP BY for reports.
undo them. If there is an error or failure before COMMIT, Oracle automatically rolls Subqueries are queries within queries, used for conditions or values in the main query.
back the transaction. SAVEPOINT allows partial rollbacks to a specific point. A Can be single-row, multi-row, or correlated.
transaction may fail due to system crashes, user interruption, constraint violations, or Indexes speed up SELECT queries but slow down INSERT/UPDATE. Create using
deadlocks. In PL/SQL, %TYPE and %ROWTYPE are used to declare variables and CREATE INDEX.
records based on existing table columns and rows. %TYPE assigns the exact datatype of Views are virtual tables created from SELECT. Simplify queries, provide security, and
a column to a variable, while %ROWTYPE creates a record with the structure of a avoid repetition.
whole row. They provide advantages like automatic datatype updates, consistency with Sequences generate unique numbers (e.g., for primary keys). Use NEXTVAL to get the
table schema, and easier maintenance compared to using raw datatypes next number.
A trigger in Oracle is a stored PL/SQL block that runs automatically when a specific Set operators in SQL are used to combine the result of two or more SELECT queries
event like INSERT, UPDATE, or DELETE happens on a table or view. It helps in tasks into a single result set. The main set operators are UNION, UNION ALL,
like enforcing rules, maintaining logs, or auditing data without manual effort. Triggers INTERSECT, and MINUS. All queries combined using set operators must have the
same number of columns with the same or compatible data types. UNION returns
can run BEFORE or AFTER the event and can be defined at the statement or row level.
distinct records from both queries, while UNION ALL includes all records including
Row-level triggers use special variables like :NEW and :OLD to refer to data before and duplicates. INTERSECT returns only the common rows present in both queries.
after the change. INSTEAD OF triggers are used with views to allow DML actions. The MINUS (specific to Oracle) returns the rows from the first query that do not appear in
syntax begins with CREATE OR REPLACE TRIGGER, followed by the timing, event, the second. These operators follow standard set theory logic and are useful when
and table. The logic is written inside a BEGIN-END block. working with multiple data sources or comparing datasets. Set operators process each
row as a whole and automatically eliminate duplicates where required.
Data constraints are rules applied to table columns in Oracle to ensure the accuracy and A join is used to combine rows from two or more tables based on a related column
reliability of the data stored. They automatically restrict the type of data that can be between them. It allows fetching meaningful data spread across different tables. The
inserted, updated, or deleted. Common types include NOT NULL (prevents empty most common is the INNER JOIN, which returns only matching rows from both
values), UNIQUE (ensures all values in a column are different), PRIMARY KEY tables. LEFT OUTER JOIN returns all rows from the left table and matching rows
(uniquely identifies each row and can't be null), FOREIGN KEY (ensures referential from the right. RIGHT OUTER JOIN does the opposite. FULL OUTER JOIN
integrity by linking to another table), and CHECK (validates values based on a returns all rows when there is a match in either table. CROSS JOIN gives the
condition). Constraints can be defined during table creation or later using ALTER. Cartesian product of the two tables, combining every row from the first with every
Oracle enforces these constraints automatically, preventing invalid data from entering row from the second. Joins are essential for relational data management and efficient
the database and maintaining overall consistency. querying.
In Oracle Forms or database applications, input items are fields where the user can LOV, or List of Values, is a feature in Oracle Forms used to present a predefined list of
enter or modify data, such as text boxes or editable drop-downs. These items are used valid values to the user for selection. It helps ensure data consistency by allowing users
to collect input from the user and are usually linked to database columns for data to choose only from allowed entries instead of typing manually. LOVs are often linked
manipulation. Non-input items, on the other hand, are used only to display information to a database query or static list and can be attached to input fields. When a user clicks
without allowing user edits. They include display fields, labels, images, or calculated or presses a specific key (like F9), the LOV appears, and the selected value is returned
results. The main difference is that input items accept data entry, while non-input items to the form field. This reduces user errors and improves efficiency in data entry tasks.
are read-only and used for output or interface purposes.
A lock in Oracle is a mechanism used to control access to data in a multi-user A database model defines the logical structure and organization of data, and determines
environment. It ensures that data remains consistent and prevents problems like lost how data is stored, connected, and accessed in a database system. The most common
updates or dirty reads when multiple users try to access or modify the same data at the models include the hierarchical model, which arranges data in a tree-like structure with
same time. There are two main types of locks: implicit and explicit. Implicit locks are parent-child relationships; the network model, which allows many-to-many
automatically placed by Oracle when a user performs operations like insert, update, or relationships using a graph structure; and the relational model, which stores data in
delete. Explicit locks are manually applied using the LOCK TABLE command. Oracle tables with rows and columns and uses keys to maintain relationships. The object-
also uses row-level and table-level locks. Row-level locks restrict access to specific oriented model integrates object programming features like inheritance and
rows, while table-level locks apply to the whole table. Proper use of locking helps encapsulation. Among these, the relational model is most widely used due to its
maintain data integrity and avoids conflicts in transactions. simplicity, flexibility, and strong theoretical foundation.
1. Database and Tablespace: 2. Error and Exception:
A database is a structured collection of data stored and accessed electronically. It An error in PL/SQL refers to a fault that disrupts the program’s flow, usually occurring
contains all the data files, control files, redo logs, and the overall structure needed to due to invalid operations like dividing by zero or referencing invalid data. Errors may
manage and store information. A tablespace, on the other hand, is a logical storage unit be syntax-related or runtime issues. Exceptions are PL/SQL constructs used to handle
within a database. It acts like a container to group related logical structures like tables errors gracefully. They are triggered when an error occurs, allowing the program to
and indexes. While the database is the full system, tablespaces divide storage within it continue or exit cleanly. Exceptions can be predefined by Oracle or user-defined for
to make data management more efficient and organized. custom scenarios, enabling better control over error responses.
3. Sequence and Synonym: 4. WHERE and HAVING Clause:
A sequence in Oracle is a database object that automatically generates numeric values, The WHERE clause filters rows before any groupings are made; it is used to restrict
often used to create unique identifiers for primary keys. It ensures that numbers are records based on conditions applied directly to columns. It applies to individual rows
generated in a specific order and can be incremented or cycled. A synonym is an alias and works before aggregation. The HAVING clause, however, is used to filter the
for another database object like a table or view. It simplifies access by allowing users to results after grouping has been done using GROUP BY. It applies to aggregated data,
refer to objects using shorter or more convenient names, especially when the original allowing conditions on functions like COUNT, SUM, or AVG to limit grouped results.
object belongs to another schema or has a long name. Both are used for filtering but at different stages of query execution.
Concurrency in databases refers to the simultaneous access of the same data by multiple users or transactions without affecting data consistency. It ensures that operations are
executed as if they were run one after another, even when they occur at the same time. To maintain data integrity, locking techniques are used. Shared locks allow multiple users to
read data but not write, while exclusive locks prevent other users from reading or writing the locked data. Row-level locking locks only the affected row, ensuring minimal
blocking, whereas table-level locking locks the entire table, which is simpler but less efficient. Optimistic locking assumes minimal conflicts and checks for changes before
commit, while pessimistic locking assumes conflicts are likely and locks early. Proper concurrency control prevents problems like lost updates, dirty reads, and phantom reads.