SQL CREATE / ALTER DATABASE

Last update on March 28 2024 09:32:02 (UTC/GMT +8 hours)

Create and alter Database

In SQL, the CREATE DATABASE statement is used to create a database though the ANSI standard does not contain a CREATE DATABASE statement. The database names are case sensitive in Unix but this restriction does not apply in Windows. This is also true for table names. The best practice is to use same letter case while creating a database as well as tables.
All most all database platform support CREATE DATABASE statement with variations.

Syntax:

CREATE DATABASE [database_name];

Parameter:

Name	Description
database_name	Name of the database. The maximum length of the database name depends on upon the vendor of the database.

Example:

CREATE DATABASE test;

Note : A database which has just been created is not current database. The user must have to instruct to make it a current database. A database needs to be created only once but a user must have to select it for each time for working with that database.

Create database in MySQL [5.6]

CREATE DATABASE creates a database with the given name. To use this statement, you need the CREATE privilege for the database.

Syntax:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...
  create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

Explanation:

In MySQL, the CREATE DATABASE or CREATE SCHEMA statement is used to create a new database or schema respectively.

CREATE DATABASE or CREATE SCHEMA: This part of the syntax specifies whether you want to create a database or a schema. In MySQL, databases and schemas are often used interchangeably.
[IF NOT EXISTS]: This clause is optional. If specified, it ensures that the statement does not produce an error if a database or schema with the same name already exists. If the database or schema does not exist, it will be created. If it does exist, no action will be taken.
db_name: This is the name of the database or schema that you want to create. It should be a valid identifier and must not contain any spaces or special characters.
[create_specification] ...: This section is optional and allows you to specify additional specifications for the database or schema being created. The specifications include:

[DEFAULT] CHARACTER SET [=] charset_name: This clause specifies the default character set to be used for storing character data in the database or schema. The charset_name parameter specifies the name of the character set. Character sets define the encoding of characters used in the database.
[DEFAULT] COLLATE [=] collation_name: This clause specifies the default collation to be used for comparing and sorting character data in the database or schema. The collation_name parameter specifies the name of the collation. Collations define the rules for comparing and sorting characters based on their encoding and language-specific rules.

Create database in PostgreSQL [9.2.3]

CREATE DATABASE creates a new PostgreSQL database. To create a database, you must be a super user or have the special CREATEDB privilege.

Syntax:

CREATE DATABASE name
  [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ CONNECTION LIMIT [=] connlimit ] ]

Explanation:

CREATE DATABASE name: Basic command to create a new database with the specified name.
[ WITH ]: Optional clause indicating the beginning of additional options for the new database.
[ OWNER [=] user_name ]: Specifies the user who will be the owner of the new database. If not specified, the user executing the command becomes the owner.
[ TEMPLATE [=] template ]: Specifies the template database to use when creating the new database. If not specified, it defaults to the template database named "template1".
[ ENCODING [=] encoding ]: Specifies the character encoding to be used for the new database. UTF-8 is a common encoding.
[ LC_COLLATE [=] lc_collate ]: Specifies the collation order for strings in the new database. Collation defines the sort order of strings.
[ LC_CTYPE [=] lc_ctype ]: Specifies the character classification for the new database, defining how characters are classified (e.g., as letters, digits, etc.).
[ TABLESPACE [=] tablespace_name ]: Allows specifying a different tablespace where the database objects will be stored. By default, it uses the default tablespace.
[ CONNECTION LIMIT [=] connlimit ]: Sets the maximum number of concurrent connections allowed to the new database. If not specified, there is no connection limit.

Create database in Oracle 11g

Use the CREATE DATABASE statement to create a database, making it available for general use.

Syntax:

CREATE DATABASE [ database ]
  { USER SYS IDENTIFIED BY password
  | USER SYSTEM IDENTIFIED BY password
  | CONTROLFILE REUSE
  | MAXDATAFILES integer
  | MAXINSTANCES integer
  | CHARACTER SET charset
  | NATIONAL CHARACTER SET charset
  | SET DEFAULT
      { BIGFILE | SMALLFILE } TABLESPACE
  | database_logging_clauses
  | tablespace_clauses
  | set_time_zone_clause
  }... ;

Explanation:

CREATE DATABASE [ database ]: Basic command to create a new database with an optional name.
{ USER SYS IDENTIFIED BY password | USER SYSTEM IDENTIFIED BY password }: Specifies the creation of either a SYS or SYSTEM user with the provided password.
| CONTROLFILE REUSE: Specifies that the control file should be reused if it already exists.
| MAXDATAFILES integer: Sets the maximum number of data files allowed in the database.
| MAXINSTANCES integer: Sets the maximum number of instances allowed for the database.
| CHARACTER SET charset: Sets the character set for the database.
| NATIONAL CHARACTER SET charset: Sets the national character set for the database.
| SET DEFAULT { BIGFILE | SMALLFILE } TABLESPACE: Specifies whether to use bigfile or smallfile tablespaces as the default tablespace for the database.
| database_logging_clauses: Additional clauses related to database logging configuration.
| tablespace_clauses: Additional clauses related to tablespace configuration.
| set_time_zone_clause: Allows setting the default time zone for the database.
...: Indicates that more clauses can be included as needed.
;: Terminates the CREATE DATABASE statement.

Example:

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



CREATE DATABASE sample
   CONTROLFILE REUSE -- Reuse existing control files if they exist
   
   LOGFILE
      GROUP 1 ('diskx:log1.log', 'disky:log1.log') SIZE 50K, -- Define log file group 1 with two members on different disks
      GROUP 2 ('diskx:log2.log', 'disky:log2.log') SIZE 50K -- Define log file group 2 with two members on different disks
   MAXLOGFILES 5 -- Set the maximum number of log files allowed for the database
   MAXLOGHISTORY 100 -- Set the maximum number of log history entries to retain
   MAXDATAFILES 10 -- Set the maximum number of data files allowed for the database
   MAXINSTANCES 2 -- Set the maximum number of instances allowed for the database
   ARCHIVELOG -- Enable archiving of filled redo log files
   
   CHARACTER SET AL32UTF8 -- Set the character set for the database to AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16 -- Set the national character set for the database to AL16UTF16
   
   DATAFILE  
      'disk1:df1.dbf' AUTOEXTEND ON, -- Create data file df1.dbf on disk1 with autoextend enabled
      'disk2:df2.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED -- Create data file df2.dbf on disk2 with autoextend enabled, incrementing by 10MB each time, with no maximum size limit

   DEFAULT TEMPORARY TABLESPACE temp_ts -- Set the default temporary tablespace to temp_ts
   UNDO TABLESPACE undo_ts -- Set the undo tablespace to undo_ts 
   
   SET TIME_ZONE = '+02:00'; -- Set the default time zone for the database to GMT+2

Explanation:

CONTROLFILE REUSE: This statement indicates that if there are existing control files, they should be reused.
LOGFILE: This section defines the redo log files for the database.

GROUP 1, GROUP 2: These lines define two groups of redo log files, each with two members located on different disks.
SIZE 50K: Specifies the size of each redo log file member as 50 KB.

MAXLOGFILES 5: Sets the maximum number of redo log files allowed for the database to 5.
MAXLOGHISTORY 100: Sets the maximum number of log history entries to retain to 100.
MAXDATAFILES 10: Sets the maximum number of data files allowed for the database to 10.
MAXINSTANCES 2: Sets the maximum number of instances allowed for the database to 2.
ARCHIVELOG: Enables archiving of filled redo log files, which is essential for database recovery and backup.
CHARACTER SET AL32UTF8: Sets the character set for the database to AL32UTF8, which supports Unicode characters and is commonly used for internationalization.
NATIONAL CHARACTER SET AL16UTF16: Sets the national character set for the database to AL16UTF16, another Unicode-based character set with support for a wider range of characters.
DATAFILE: This section defines the data files for the database.

'disk1:df1.dbf' AUTOEXTEND ON: Creates a data file named "df1.dbf" on "disk1" with autoextend enabled.
'disk2:df2.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED: Creates a data file named "df2.dbf" on "disk2" with autoextend enabled, incrementing by 10 MB each time, with no maximum size limit.

DEFAULT TEMPORARY TABLESPACE temp_ts: Sets the default temporary tablespace to "temp_ts".
UNDO TABLESPACE undo_ts: Sets the undo tablespace to "undo_ts", which is used to manage undo information for transactions.
SET TIME_ZONE = '+02:00': Sets the default time zone for the database to GMT+2.

Create database in SQL Server 2012

Creates a new database and the files used to store the database, a database snapshot or attaches a database from the detached files of a previously created database.

Syntax:

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

CREATE DATABASE database_name 
[ CONTAINMENT = { NONE | PARTIAL } ]
[ ON 
      [ PRIMARY ] <filespec> [ ,...n ] 
      [ , <filegroup> [ ,...n ] ] 
      [ LOG ON <filespec> [ ,...n ] ] 
] 
[ COLLATE collation_name ]
[ WITH  <option> [,...n ] ]
[;]
<option> ::=
{
      FILESTREAM ( <filestream_option> [,...n ] )
    | DEFAULT_FULLTEXT_LANGUAGE = { lcid | language_name | language_alias }
    | DEFAULT_LANGUAGE = { lcid | language_name | language_alias }
    | NESTED_TRIGGERS = { OFF | ON }
    | TRANSFORM_NOISE_WORDS = { OFF | ON}
    | TWO_DIGIT_YEAR_CUTOFF = <two_digit_year_cutoff> 
    | DB_CHAINING { OFF | ON }
    | TRUSTWORTHY { OFF | ON }
}
<filestream_option> ::=
{
      NON_TRANSACTED_ACCESS = { OFF | READ_ONLY | FULL }
    | DIRECTORY_NAME = 'directory_name' 
}
To attach a database
CREATE DATABASE database_name 
    ON <filespec> [ ,...n ] 
    FOR { { ATTACH [ WITH <attach_database_option> [ , ...n ] ] }
        | ATTACH_REBUILD_LOG }
[;]
<filespec> ::= 
{
(
    NAME = logical_file_name ,
    FILENAME = { 'os_file_name' | 'filestream_path' } 
    [ , SIZE = size [ KB | MB | GB | TB ] ] 
    [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ] 
    [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ] ]
)
}
<filegroup> ::= 
{
FILEGROUP filegroup_name [ CONTAINS FILESTREAM ] [ DEFAULT ]
    <filespec> [ ,...n ]
}
<attach_database_option> ::=
{
      <service_broker_option>
    | RESTRICTED_USER
    | FILESTREAM ( DIRECTORY_NAME = { 'directory_name' | NULL } )
}
<service_broker_option> ::=
{
    ENABLE_BROKER
  | NEW_BROKER
  | ERROR_BROKER_CONVERSATIONS
}
Create a database snapshot
CREATE DATABASE database_snapshot_name 
    ON 
    (
        NAME = logical_file_name,
        FILENAME = 'os_file_name' 
    ) [ ,...n ] 
    AS SNAPSHOT OF source_database_name
[;]

Explanation:

Creating a Database:

CREATE DATABASE database_name: This is the basic command to create a new database with the specified name.
CONTAINMENT = { NONE | PARTIAL }: Specifies whether the database is contained or partially contained. A contained database includes all database settings, metadata, and data needed to define the database and the database users.
ON: Specifies where to store the database files.
<filespec>: Defines the file specifications for the database, including the name, filename, size, maximum size, and file growth.
<filegroup>: Groups multiple data files together logically within a database. You can specify different properties for each filegroup.
LOG ON <filespec>: Specifies the file specifications for the transaction log files.
COLLATE collation_name: Specifies the default collation for the database, which determines how string data is sorted and compared.
WITH <option> [,...n ]: Specifies additional options for the database creation, such as FILESTREAM configuration, default languages, triggers settings, year cutoff, database chaining, and trustworthiness.

Attaching a Database:

ATTACH: Specifies that you are attaching an existing database to the server.
ATTACH_REBUILD_LOG: Specifies that the transaction log for the attached database should be rebuilt.
<attach_database_option>: Specifies additional options for attaching the database, such as service broker settings, restricted user, and FILESTREAM configuration.

Creating a Database Snapshot:

CREATE DATABASE database_snapshot_name: Creates a new database snapshot with the specified name.
AS SNAPSHOT OF source_database_name: Specifies the source database from which the snapshot will be created.
ON: Specifies where to store the database snapshot files.
NAME = logical_file_name: Specifies the logical name for the database snapshot file.
FILENAME = 'os_file_name': Specifies the physical path for the database snapshot file on the operating system.

SQL ALTER DATABASE

The ALTER DATABASE statement is used to modify, maintain, or recover an existing database.

Alter database in Oracle 11g

In earlier versions of Oracle Database, you could use the ALTER DATABASE for two conversion operations :
- The RESET COMPATIBILITY clause lets you reset the database to an earlier version at the next instance startup.
- The CONVERT clause lets you upgrade an Oracle7 data dictionary to an Oracle8i or Oracle9i data dictionary.
These clauses are no longer supported.

Alter database in SQL Server 2012

In SQL 2012 the alter command modifies a database or the file and filegroups which are associated with the database. You can add or remove files from as database, changes the attributes of a database or its files and filegroups, changes the database collation, and sets database options.

Syntax:

ALTER DATABASE { database_name  | CURRENT }
	  {      
	      MODIFY NAME = new_database_name
		| COLLATE collation_name    
		| <file_and_filegroup_options>    
		| <set_database_options>  
	  }  
	  [;]
	  
	  <file_and_filegroup_options >::= 
	  <add_or_modify_files>::=    
	  <filespec>::=     
	  <add_or_modify_filegroups>::=    
	  <filegroup_updatability_option>::=     
	  <set_database_options>::=    
	  <optionspec>::=     
	  <auto_option> ::=     
	  <change_tracking_option> ::=    
	  <cursor_option> ::=     
	  <database_mirroring_option> ::=     
	  <date_correlation_optimization_option> ::=    
	  <db_encryption_option> ::=    
	  <db_state_option> ::=    
	  <db_update_option> ::=    
	  <db_user_access_option> ::=    
	  <external_access_option> ::=    
	  <FILESTREAM_options> ::=    
	  <HADR_options> ::=      
	  <parameterization_option> ::=    
	  <recovery_option> ::=     
	  <service_broker_option> ::=    
	  <snapshot_option> ::=    
	  <sql_option> ::=     
	  <termination> ::=

Explanation:

The provided syntax is for the ALTER DATABASE statement in SQL Server 2012, which allows you to modify various aspects of a database.

ALTER DATABASE { database_name | CURRENT }: Specifies the name of the database to be altered. You can either provide the name of the specific database or use the keyword CURRENT to refer to the current database context.
{ }: Indicates a set of options that can be used to modify the database.
MODIFY NAME = new_database_name: Changes the name of the database to the specified new name.
COLLATE collation_name: Changes the default collation of the database to the specified collation.
<file_and_filegroup_options >: Refers to a set of options related to modifying files and filegroups within the database.
<set_database_options>: Refers to a set of options related to modifying various database settings.
<optionspec>, <auto_option>, <change_tracking_option>, <cursor_option>, <database_mirroring_option>, <date_correlation_optimization_option>, <db_encryption_option>, <db_state_option>, <db_update_option>, <db_user_access_option>, <external_access_option>, <FILESTREAM_options>, <HADR_options>, <parameterization_option>, <recovery_option>, <service_broker_option>, <snapshot_option>, <sql_option>: These are specific options that can be included in the <set_database_options> to alter various database settings like auto options, change tracking, cursor behavior, database mirroring, encryption, state, update options, user access, external access, FILESTREAM options, HADR (High Availability Disaster Recovery) options, parameterization, recovery, service broker settings, snapshot isolation, and SQL settings.
<termination>: Indicates the end of the ALTER DATABASE statement.

Alter database in PostgreSQL [9.2]

In PostgreSQL ALTER DATABASE change a database.

Syntax:

ALTER DATABASE name [ [ WITH ] option [ ... ] ]
where option can be:
    CONNECTION LIMIT connlimit
ALTER DATABASE name RENAME TO new_name
ALTER DATABASE name OWNER TO new_owner
ALTER DATABASE name SET TABLESPACE new_tablespace
ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name SET configuration_parameter FROM CURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL

Explanation:

ALTER DATABASE name [ [ WITH ] option [ ... ] ]: This is the basic syntax for altering a database. Replace "name" with the name of the database you want to modify. The WITH keyword is optional and can be followed by one or more options to specify the modifications to be made.

CONNECTION LIMIT connlimit: Sets the maximum number of concurrent connections allowed to the database.

ALTER DATABASE name RENAME TO new_name: Renames the specified database to a new name (new_name).
ALTER DATABASE name OWNER TO new_owner: Changes the owner of the specified database to a new owner (new_owner).
ALTER DATABASE name SET TABLESPACE new_tablespace: Moves the database to a new tablespace (new_tablespace). This option is used to relocate the entire database to a different storage area.
ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }: Sets the value of a specific configuration parameter for the database to the specified value (value). You can also set it to the default value.
ALTER DATABASE name SET configuration_parameter FROM CURRENT: Sets the value of a specific configuration parameter for the database to the same value as the current session. This is useful for inheriting settings from the current session.
ALTER DATABASE name RESET configuration_parameter: Resets the value of a specific configuration parameter for the database to its default value.
ALTER DATABASE name RESET ALL: Resets all configuration parameters for the database to their default values.

Alter database in MySQL [5.6]

In MySQL 5.6 ALTER DATABASE change the overall characteristics of a database.

Syntax:

ALTER {DATABASE | SCHEMA} [db_name]
    alter_specification ...
ALTER {DATABASE | SCHEMA} db_name
    UPGRADE DATA DIRECTORY NAME
alter_specification:
    [DEFAULT] CHARACTER SET [=] char set_name
  | [DEFAULT] COLLATE [=] collation _name

Explanation:

The provided syntax is for the ALTER DATABASE or ALTER SCHEMA statement in MySQL, which is used to modify characteristics of a database or schema.

ALTER {DATABASE | SCHEMA} [db_name]: Specifies that you want to alter a database or schema. You can optionally provide the name of the database or schema to be altered.
alter_specification ...: Specifies the alterations to be made to the database or schema.
ALTER {DATABASE | SCHEMA} db_name: Specifies the database or schema to be altered. This form of the statement is used when you want to alter the entire database or schema.
UPGRADE DATA DIRECTORY NAME: Specifies that you want to upgrade the data directory name for the database. This operation can be used to rename the directory where the database's data files are stored. This is primarily used in conjunction with storage engine changes or when moving the database to a different location.
[DEFAULT] CHARACTER SET [=] char set_name: Specifies the default character set to be used for the database or schema. This option allows you to change the default character set for all tables and columns within the database or schema.
[DEFAULT] COLLATE [=] collation_name: Specifies the default collation to be used for the database or schema. This option allows you to change the default collation for all tables and columns within the database or schema.

Reference: Oracle Database SQL Language Reference, MySQL Documentation, PostgreSQL Documentation, SQL Server 2012

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: Create/Alter/Drop Schema
Next: Create Table