MCQ King

Oracle – 48 – Tables and Views

Start MCQ

In Oracle SQL, tables and views are fundamental database objects used to store and retrieve data. They serve as containers for organizing and managing structured information. Here’s a brief description of tables and views in Oracle:

1. Tables:

  • Purpose: Tables are the core storage structures in a relational database. They store data in rows and columns, providing a structured and organized way to store information.
  • Attributes:
    • Rows: Each row in a table represents a single record or data entry.
    • Columns: Columns define the data elements or fields within each record, specifying the data type and constraints for each field.
  • Usage:
    • Tables are used to store, manage, and retrieve data in a database.
    • They can represent real-world entities, such as customers, products, orders, and employees, and are used to create the schema for a database.
  • Operations:
    • Data Insertion: New data records are added to tables using the INSERT statement.
    • Data Retrieval: Data is retrieved from tables using the SELECT statement.
    • Data Modification: Existing data is updated or deleted using the UPDATE and DELETE statements.
    • Schema Management: Tables are created, altered, or dropped to define and maintain the database structure.

2. Views:

  • Purpose: Views are virtual or logical representations of data from one or more tables. They do not store data themselves but provide a dynamic way to access and manipulate data.
  • Attributes:
    • Virtual: Views do not contain actual data; they are created based on the data in one or more underlying tables.
    • Customized: Views allow you to define custom subsets of data, specific column selections, or join multiple tables to present a consolidated view.
  • Usage:
    • Views are used for simplifying complex queries by creating a more straightforward and tailored interface to access data.
    • They can provide security by restricting access to specific columns or rows.
    • Views are helpful for abstraction, allowing applications to interact with a simplified data structure.
  • Operations:
    • Data Retrieval: Views are queried like tables using the SELECT statement.
    • Data Manipulation: Depending on the view’s definition, some views allow data modification operations (INSERT, UPDATE, DELETE).

3. Differences:

  • Tables store data physically, while views provide a logical representation.
  • Tables can hold a large volume of data, while views are lightweight and do not store data directly.
  • Data in tables can be modified directly, while data modification through views may be limited, depending on the view’s definition.

4. Use Cases:

  • Tables are used for data storage and manipulation.
  • Views are used for simplifying complex queries, enforcing security, and abstracting data for application use.

5. Examples:

  • Creating a table: CREATE TABLE employees ( employee_id NUMBER(5) PRIMARY KEY, first_name VARCHAR2(50), last_name VARCHAR2(50), salary NUMBER(10, 2) );
  • Creating a view: CREATE VIEW high_salary_employees AS SELECT first_name, last_name FROM employees WHERE salary > 50000;

Tables and views are foundational database objects in Oracle SQL. Tables store the actual data, while views provide a flexible and controlled way to access and manipulate that data. Both are integral to building efficient and organized database systems and are used extensively in database design and application development.