MySQL

Creating tables in MySQL is a fundamental task when working with databases. TheCREATE TABLE statement allows you to define the structure of a table, specifying the columns, their data types, and any constraints. This is essential for organizing and storing data efficiently. Below, you'll find a step-by-step guide to creating tables in MySQL, including code samples for a company database with tables for company, employees,departments, andbranches.

Steps to Create Tables in MySQL

1. Define Table Structure
   • Determine the columns needed and their data types.
   • Decide on primary keys and any constraints.
2. Write the CREATE TABLE Statement
   • Use theCREATE TABLEcommand to define each table.
   • Include column names, data types, and constraints.
3. Execute the Statement
• Run the SQL command in your MySQL client to create the table.

Code Samples

1. Creating the companyTable

   CREATE TABLE company (
   company_id INT AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(100) NOT NULL,
   founded DATE
   );
   

   • company_id: Unique identifier for each company (auto-incremented).
   • name: Name of the company (required field).
   • founded: Date when the company was founded.
2. Creating the employeesTable

   CREATE TABLE employees (
   employee_id INT AUTO_INCREMENT PRIMARY KEY,
   first_name VARCHAR(50) NOT  NULL,
   last_name VARCHAR(50) NOT  NULL,
   email VARCHAR(100) UNIQUE NOT  NULL,
   hire_date DATE,
   department_id INT,
   branch_id INT,
   FOREIGN KEY (department_id) REFERENCES departments(department_id),
   FOREIGN KEY (branch_id) REFERENCES branches(branch_id)
   );
   

   • employee_id: Unique identifier for each employee (auto-incremented).
   • first_nameand last_name: Employee's names (required fields).
   • email: Email address (must be unique and required).
   • hire_date: Date the employee was hired.
   • department_id and branch_id: Foreign keys linking to departmentsand branches.
3. Creating the departmentsTable

   CREATE TABLE departments (
   deapartment_id INT AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(100) NOT  NULL,
   location VARCHAR(100)
   );
   

   • department_id: Unique identifier for each department (auto-incremented).
   • name: Name of the department (required field).
   • location: Location of the department.
4. Creating the branchesTable

   CREATE TABLE branches (
   branch_id INT AUTO_INCREMENT PRIMARY KEY,
   address VARCHAR(255) NOT  NULL,
   city VARCHAR(100),
   state VARCHAR(100),
   postal_code VARCHAR(20)
   );
   

   • branch_id: Unique identifier for each branch (auto-incremented).
   • address: Address of the branch (required field).
   • city,state, andpostal_code: Additional address details.

To gain complete access, login with gmail or outlook, no need of signup, click here