SQL syntax for creating database and table

1. Creating a Database

To create a new database, you can use the CREATE DATABASE statement.

CREATE DATABASE database_name;

• database_name: The name of the database you want to create.

Example:

CREATE DATABASE CompanyDB;

2. Using a Database

Before creating tables or performing any operations on a database, you need to select the database you want to work with. This is done using the USE statement.

USE database_name;

Example:

USE CompanyDB;

3. Creating a Table

To create a new table in a database, use the CREATE TABLE statement. You must define the columns, their data types, and any constraints (like PRIMARY KEY, NOT NULL, etc.).

CREATE TABLE table_name (
   column1 datatype constraints,
   column2 datatype constraints,
   column3 datatype constraints,
   ...
);

• table_name: The name of the table you want to create.
• column1, column2, ...: The names of the columns in the table.
• datatype: The data type of the column (e.g., INT, VARCHAR, DATE).
• constraints: Constraints like PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, etc.

Example:

CREATE TABLE Employees (
   EmployeeID INT PRIMARY KEY,
   FirstName VARCHAR(50) NOT NULL,
   LastName VARCHAR(50) NOT NULL,
   HireDate DATE,
   DepartmentID INT
);

In this example:

• EmployeeID: Integer column, set as the primary key (unique identifier).
• FirstName and LastName: String columns (maximum of 50 characters), set as NOT NULL (cannot be empty).
• HireDate: Date column for storing the date the employee was hired.
• DepartmentID: Integer column to associate employees with a department.