SQL Reference - SCHEMA

SCHEMA

A SCHEMA in SQL is a namespace that logically groups and organizes various database objects, such as tables, views, indexes, and procedures. It serves as a container, allowing the management of objects in a streamlined manner. The schema is used to control the authorized access of database objects to different users. Objects within a schema have unique names but can share names with objects in other schemas. The schema provides a level of data abstraction and security helping to manage the database effectively.

Example

CREATE SCHEMA sales;

Output

Query OK, 1 row affected (0.00 sec)

Explanation

This is a simple example of an SQL statement to create a new schema in MySQL. A schema, in SQL terms, is a collection of database objects, including tables, views, indexes, and procedures, associated with a database. In this example, a new schema called ‘sales’ is created.

Example

CREATE SCHEMA Accounting;

Output

CREATE SCHEMA

Explanation

In the given example, a new schema called ‘Accounting’ is created in the PostgreSQL database. The command CREATE SCHEMA is used to create a new schema. The name of the new schema is specified immediately after the command.

Example

CREATE SCHEMA Test_Schema;
GO

CREATE TABLE Test_Schema.Employees
(
    ID int IDENTITY(1,1) PRIMARY KEY,
    Name nvarchar(50),
    Title nvarchar(50)
);

Output

Command(s) completed successfully.

Explanation

In the example code, a new schema named Test_Schema is created, after which a new table named Employees is created within this schema. The table contains three columns: ID, Name, and Title. The ID column is set to auto increment and is also designated as the primary key for the table.

Example

CREATE SCHEMA AUTHORIZATION HR
    CREATE TABLE TAB_EMPLOYEE
    ( EMPLOYEE_ID NUMBER(6) NOT NULL,
      FIRST_NAME VARCHAR2(20),
      LAST_NAME VARCHAR2(25),
      EMAIL VARCHAR2(25),
      PHONE_NUMBER VARCHAR2(20),
      HIRE_DATE DATE,
      JOB_ID VARCHAR2(10),
      SALARY NUMBER(8,2),
      MANAGER_ID NUMBER(6),
      DEPARTMENT_ID NUMBER(4) )
      CREATE VIEW EMP_VIEW AS SELECT FIRST_NAME, LAST_NAME FROM TAB_EMPLOYEE
      GRANT SELECT ON EMP_VIEW TO HR

Output

This script doesn’t generate any visible output.

Explanation

The example code demonstrates the creation of a schema named HR along with a table TAB_EMPLOYEE and a view EMP_VIEW within the schema. EMP_VIEW is designed to display only FIRST_NAME and LAST_NAME from the TAB_EMPLOYEE table. At the end, the SELECT privilege on the view EMP_VIEW is granted to the HR user.