SQL CREATE / ALTER DATABASE
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
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: Create/Alter/Drop Schema
Next: Create Table
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join