w3resource logo


sql statement to create database

SQL CREATE / ALTER / DROP SCHEMA

Secondary Nav

Database Schema

A schema is a logical database object holder. A database schema of a database system is its structure described in a formal language supported by the database management system. The formal definition of a database schema is a set of formulas (sentences) called integrity constraints imposed on a database. These integrity constraints ensure compatibility between parts of the schema. All constraints are expressible in the same language.
Creating schemas can be useful when objects have circular references, that is when we need to create two tables each with a foreign key referencing the other table. Different implementations treat schemas in slightly different ways.

Syntax :

CREATE SCHEMA [schema_name] [AUTHORIZATION owner_name]
[DEFAULT CHARACTER SET char_set_name]
[PATH schema_name[, ...]]
[ ANSI CREATE statements [...] ]
[ ANSI GRANT statements [...] ];

Parameter :

Name Description
schema_name

The name of a schema to be created. If this is omitted, the user_name is used as the schema name.

AUTHORIZATION owner_name Identifies the user who is the owner of the schema. If not mentioned the current user is set as the owner.
DEFAULT CHARACTER SET char_set_name Specify the default character set, used for all objects created in the schema.
PATH schema_name[, ...] An optional file path and file name.
ANSI CREATE statements [...] Contains one or more CREATE statements.
ANSI GRANT statements [...] Contains one or more GRANT statements.

Examples :

Example-1: As a user with authority, create a schema called STUDENT with the user STUDENT as the owner.

CREATE SCHEMA STUDENT AUTHORIZATION STUDENT

Example-2: Create a schema that has an student details table. Give authority on the table to user DAVID.

 CREATE SCHEMA INVENTRY

     CREATE TABLE PART (IDNO  SMALLINT NOT NULL,
                        SNAME    VARCHAR(40),
                        CLASS INTEGER)

     GRANT ALL ON PART TO DAVID

Create schema in MySQL [5.7]

In MySQL, CREATE SCHEMA is a synonym for CREATE DATABASE.

Syntax :

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name
  

Create schema in PostgreSQL 9.3.13

CREATE SCHEMA enters a new schema into the current database. The schema name must be distinct from the name of any existing schema in the current database.

Syntax :

CREATE SCHEMA schema_name [ AUTHORIZATION user_name ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION user_name [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION user_name ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION user_name
		   

Create schema in Oracle 11g

Use the CREATE SCHEMA statement to create multiple tables and views and perform multiple grants in your own schema in a single transaction.
To execute a CREATE SCHEMA statement, Oracle Database executes each included statement. If all statements execute successfully, then the database commits the transaction. If any statement results in an error, then the database rolls back all the statements.

The CREATE SCHEMA statement can include CREATE TABLE, CREATE VIEW, and GRANT statements. To issue a CREATE SCHEMA statement, you must have the privileges necessary to issue the included statements.

Syntax :

CREATE SCHEMA AUTHORIZATION schema
   { create_table_statement
   | create_view_statement
   | grant_statement
   }...
; 

Create schema in SQL Server 2014

Creates a schema in the current database. The CREATE SCHEMA transaction can also create tables and views within the new schema, and set GRANT, DENY, or REVOKE permissions on those objects.

Syntax :

The following statement creates a database and fully specifies each argument :

CREATE SCHEMA schema_name_clause [  [ ...n ] ]

 ::=
    {
    schema_name
    | AUTHORIZATION owner_name
    | schema_name AUTHORIZATION owner_name
    }

 ::= 
    { 
        table_definition | view_definition | grant_statement | 
        revoke_statement | deny_statement 
    }

Alter Schema

The ALTER SCHEMA statement is used to rename a schema or to specify a new owner, the new owner must be a pre-existing user on the database

Syntax :

ALTER SCHEMA schema_name [RENAME TO new_schema_name] [OWNER TO new_user_name]
 

Parameter :

Name Description
schema_name The name of an existing schema.
new_schema_name The new name of the schema.
new_owner The new owner of the schema.

Alter Schema in MySQL [5.7]

In MySQL, CREATE SCHEMA is a synonym for CREATE DATABASE.

Syntax :

ALTER {DATABASE | SCHEMA} [db_name]
    alter_specification ...
ALTER {DATABASE | SCHEMA} db_name
    UPGRADE DATA DIRECTORY NAME

alter_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

In MySQL, ALTER SCHEMA is a synonym for ALTER DATABASE. ALTER DATABASE enables you to change the overall characteristics of a database. These characteristics are stored in the db.opt file in the database directory. To use ALTER DATABASE, you need the ALTER privilege on the database.

Alter schema in PostgreSQL 9.3.13

Description ALTER SCHEMA changes the definition of a schema. The user must own the schema to use ALTER SCHEMA. To rename a schema you must also have the CREATE privilege for the database. To alter the owner, you must also be a direct or indirect member of the new owning role, and you must have the CREATE privilege for the database.

Syntax :

ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO new_owner

Alter schema in SQL Server 2014

ALTER SCHEMA can only be used to move securable between schemas in the same database. Users and schemas are completely separate.

Syntax :

ALTER SCHEMA schema_name 
   TRANSFER [  :: ] securable_name 
[;]

 ::=
    {
    Object | Type | XML Schema Collection
    }

Drop Schema

Destroy a schema.

Syntax :

DROP SCHEMA <schema name>

Drop Schema in MySQL [5.7]

DROP DATABASE drops all tables in the database and deletes the database. DROP SCHEMA is a synonym for DROP DATABASE.

Syntax :

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

Drop schema in PostgreSQL 9.3.13

DROP SCHEMA removes schemas from the database. A schema can only be dropped by its owner or a superuser.

Syntax :

DROP SCHEMA [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

Drop schema in SQL Server 2014

Conditionally drops the schema only if it already exists.

Syntax :

DROP SCHEMA  [ IF EXISTS ] schema_name


Join our Question Answer community to learn and share your programming knowledge.

Help the community:

Python: Fizzbuzz

C++: Decimal to binary conversion

JavaScript: Need Help in JavaScript

Python: Help me with this program