SQL CREATE DATABASE: Create a database

Creating a database in SQL is a straightforward process. However, the exact SQL syntax used can vary depending on the SQL database management system you are using.

Here are examples for creating a new database in MySQL, SQL Server, and PostgreSQL:

1. MySQL:

In MySQL, you use the CREATE DATABASE statement to create a new database:

CREATE DATABASE database_name;

For example, to create a new database named my_database, you would write:

CREATE DATABASE my_database;

2. SQL Server:

The syntax to create a new database in SQL Server is the same as MySQL:

CREATE DATABASE database_name;

For example, to create a new database named my_database, you would write:

CREATE DATABASE my_database;

3. PostgreSQL:

In PostgreSQL, you can also use the CREATE DATABASE statement, but the database name must be written in lowercase or enclosed in double quotes if it contains any uppercase:

CREATE DATABASE database_name;

For example, to create a new database named my_database, you would write:

CREATE DATABASE my_database;

It's important to have the necessary permissions to create a database. Typically, only superusers, the database owner, or members of the dbcreator role can create databases.

Once you've created your database, you can start creating tables and inserting data into those tables. To do so, you would use the USE statement (or CONNECT in PostgreSQL) to select the database you want to work with:

USE my_database;  -- MySQL and SQL Server

\c my_database;   -- PostgreSQL

Remember, the exact SQL commands and capabilities can differ between various database systems, so always consult the documentation for the specific DBMS you are using.

Creating a New Database in SQL:
- Description: Used to create a new database.
- Code Example:
```
CREATE DATABASE NewDatabase;
```
Specifying Database Character Set in CREATE DATABASE:
- Description: Specifies the character set for the new database.
- Code Example:
```
CREATE DATABASE NewDatabase
CHARACTER SET utf8;
```
Setting Database Collation in SQL:
- Description: Sets the collation for the new database, defining how string comparison is performed.
- Code Example:
```
CREATE DATABASE NewDatabase
COLLATE Latin1_General_CI_AS;
```
Creating a Database with Specific Options in SQL:
- Description: Allows you to set various options during database creation.
- Code Example:
```
CREATE DATABASE NewDatabase
WITH 
OWNER = 'username',
MAXSIZE = 10GB,
FILENAME = 'C:\Data\NewDatabase.mdf';
```
SQL CREATE DATABASE Example:
- Code Example:
```
CREATE DATABASE SampleDB;
```
Checking if a Database Exists Before Creation:
- Description: Ensures that a database with the same name does not exist before attempting to create.
- Code Example:
```
IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = 'NewDatabase')
CREATE DATABASE NewDatabase;
```
Granting Permissions on a New Database in SQL:
- Description: Assigns permissions to users or roles on the new database.
- Code Example:
```
USE NewDatabase;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO UserName;
```

SQL CREATE DATABASE and Filegroup Options:

Description: Allows you to specify filegroups for data and log files during database creation.

Code Example:

CREATE DATABASE NewDatabase
ON PRIMARY
(NAME = 'DataFile', FILENAME = 'C:\Data\DataFile.mdf', SIZE = 5GB),
(NAME = 'LogFile', FILENAME = 'C:\Logs\LogFile.ldf', SIZE = 1GB)