SQL CREATE / ALTER DATABASE

Last update on April 07 2022 12:39:22 (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

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 ] ]

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
  }... ;

Example:

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

CREATE DATABASE sample
   CONTROLFILE REUSE 
   LOGFILE
      GROUP 1 ('diskx:log1.log', 'disky:log1.log') SIZE 50K, 
      GROUP 2 ('diskx:log2.log', 'disky:log2.log') SIZE 50K 
   MAXLOGFILES 5 
   MAXLOGHISTORY 100 
   MAXDATAFILES 10 
   MAXINSTANCES 2 
   ARCHIVELOG 
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   DATAFILE  
      'disk1:df1.dbf' AUTOEXTEND ON,
      'disk2:df2.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE temp_ts
   UNDO TABLESPACE undo_ts 
   SET TIME_ZONE = '+02:00';

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
[;]

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> ::=

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

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

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

Practice SQL Exercises