Managing Schema Objects in SQL

Managing Schema Objects in SQL

Tables

Definition and Structure

Tables are the fundamental objects in a relational database. They store data in rows and columns.

Example of creating a table: 

CREATE TABLE employees (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(50) NOT NULL,
    position VARCHAR2(50),
    hire_date DATE
);

 Columns:

• id: Primary key, unique for each employee.
• name: Employee’s name (cannot be null).
• position: Position of the employee.
• hire_date: Date when the employee was hired.

Data Types

Columns in a table have defined data types, such as VARCHAR2, NUMBER, DATE, etc. Each data type has its own characteristics regarding size, format, and validity.

Common Data Types:

• VARCHAR2(n): Variable-length character string.
• NUMBER(p, s): Number with precision p and scale s.
• DATE: Date and time.

Data and Operations

Tables store data as rows, and common operations include inserting, updating, deleting, and selecting data.

Examples of operations: 

-- Insert a row
INSERT INTO employees (id, name, position, hire_date)
VALUES (1, 'John Doe', 'Developer', SYSDATE);
-- Select data
SELECT * FROM employees;
-- Update a row
UPDATE employees SET position = 'Senior Developer' WHERE id = 1;
-- Delete a row
DELETE FROM employees WHERE id = 1;

Constraints

Definition

Constraints are rules applied to columns and tables to ensure data integrity.

Types of Constraints:

• PRIMARY KEY: Ensures uniqueness of values in a column or a group of columns.
• FOREIGN KEY: Ensures that values in a column correspond to values in another table.
• UNIQUE: Ensures all values in a column are unique.
• NOT NULL: Ensures values in a column cannot be null.
• CHECK: Ensures values in a column meet a specific condition.

Example of a table with constraints: 

CREATE TABLE departments (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(100) UNIQUE NOT NULL,
    budget NUMBER CHECK (budget > 0)
);

Views

Definition and Use

A view is a virtual table based on the result of a SQL query. It doesn’t physically store data but provides a simplified and secure way to present data.

Creating a View: 

CREATE VIEW view_employees AS
SELECT id, name, position
FROM employees;

Using Views: Views can simplify complex queries and provide security by restricting access to specific columns.

Example query on a view: 

SELECT * FROM view_employees WHERE position = 'Developer';

Materialized Views

Unlike simple views, materialized views store the result of the query, which can improve performance for large-scale reads.

Creating a Materialized View: 

CREATE MATERIALIZED VIEW mv_employees
AS SELECT id, name, position FROM employees;

Indexes

Definition and Function

Indexes are data structures that improve the speed of data retrieval operations by providing quick access to rows in a table.

Types of Indexes:

• B-tree Index: The most common type of index, used for efficient searches.
• Bitmap Index: Efficient for columns with a limited number of distinct values.
• Composite Index: An index on multiple columns.

Example of creating an index: 

CREATE INDEX idx_name ON employees(name);

Invisible Indexes: Invisible indexes are not used by the database engine for queries until they are made visible. This can be useful for testing index performance impacts.

Example of creating an invisible index: 

CREATE INDEX idx_position INVISIBLE ON employees(position);

Maintaining Indexes

Indexes need to be maintained and rebuilt to reflect changes in the data.

Example of rebuilding an index: 

ALTER INDEX idx_name REBUILD;

Sequences

Definition and Use

Sequences are objects used to generate unique numeric values, often used for primary keys.

Creating a Sequence: 

CREATE SEQUENCE seq_employee_id
START WITH 1
INCREMENT BY 1
NOCACHE;

Using a Sequence in Data Insertion: 

INSERT INTO employees (id, name, position, hire_date)
VALUES (seq_employee_id.NEXTVAL, 'Marie Curie', 'Scientist', SYSDATE);

Managing Sequences

Sequences can be adjusted to change their behavior, such as the increment value or starting value.

Example of modifying a sequence: 

ALTER SEQUENCE seq_employee_id
INCREMENT BY 10;

Resetting a Sequence: 

ALTER SEQUENCE seq_employee_id RESTART START WITH 1;