Data Definition Language

DATABASE

CREATE DATABASE company;
-- This command creates a new database named "company."

USE company;
-- This command selects the database named "company" for further operations.

ALTER DATABASE database_name
SET CHARACTER SET utf8;
-- This command changes the character set of the specified database to UTF-8.

DROP DATABASE company;
-- This command deletes the database named "company" and all its associated data.

TABLE

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  department VARCHAR(50),
  salary DECIMAL(10, 2)
);
-- This command creates a table named "employees" with columns for employee ID, first name, last name, department, and salary. The employee_id column is set as the primary key.

-- PRIMARY KEY: Uniquely Identifies Each Record in a Table
CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50)
);
-- employee_id is designated as the primary key, ensuring that each employee record has a unique identifier.
 
 
-- FOREIGN KEY: Establishes a Relationship Between Two Tables
  -- On DELETE CASCADE: Automatically deletes child records
  -- On DELETE SET NULL: Foreign key values in child records are set to NULL
CREATE TABLE departments (
  department_id INT PRIMARY KEY,
  department_name VARCHAR(50)
);
 
CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  department_id INT,
  FOREIGN KEY (department_id) REFERENCES departments(department_id) -- ON DELETE option
);
-- department_id column in the employees table is a foreign key that references the department_id column in the departments table, establishing a relationship between the two tables.
 
 
-- UNIQUE: Ensures That All Values in a Column Are Unique
CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  email VARCHAR(100) UNIQUE
);
-- email column must contain unique values for each employee.
 
 
-- NOT NULL: Ensures That a Column Does Not Contain NULL Values
CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL
);
-- first_name and last_name columns must have values and cannot be NULL.
 
 
-- CHECK: Specifies a Condition That Must Be Met for a Column's Value
CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  age INT CHECK (age >= 18)
);
-- age column must have a value of 18 or greater due to the CHECK constraint.

-- Add a New Column
ALTER TABLE employees
ADD COLUMN middle_name VARCHAR(50);
-- This command adds a new column named "middle_name" to the "employees" table.
 
 
-- Rename a Column
ALTER TABLE employees
RENAME COLUMN middle_name TO middle_initial;
-- This command renames the "middle_name" column to "middle_initial".
 
 
-- Modify a Column's Data Type
ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(12, 2);
-- This command changes the data type of the "salary" column to DECIMAL with a precision of 12 and scale of 2.
 
 
-- Drop a Column
ALTER TABLE employees
DROP COLUMN middle_initial;
-- This command drops the "middle_initial" column from the "employees" table.
 
 
-- Add a Constraint
ALTER TABLE employees
ADD CONSTRAINT chk_age CHECK (age >= 18);
-- This command adds a CHECK constraint to ensure that the age is at least 18.
 

DROP TABLE employees;
-- This command deletes the entire "employees" table along with all its data.

VIEW

CREATE VIEW high_paid_employees AS
SELECT *
FROM employees
WHERE salary > 60000;
-- This query creates a views named high_paid_employees that contains all employees with a salary greater than 60000.

DROP VIEW IF EXISTS high_paid_employees;
-- This query drops the high_paid_employees view if it exists.

INDEX

CREATE INDEX idx_department ON employees (department);
-- This query creates an index named idx_department on the department column of the employees table.

DROP INDEX IF EXISTS idx_department;
-- This query drops the idx_department index if it exists.