1. Database and Table Creation

• Create a Database

  sqlCopy code
  CREATE DATABASE database_name;
  
  

• Drop a Database

  sqlCopy code
  DROP DATABASE database_name;
  
  

• Create a Table

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

  Example:

  sqlCopy code
  CREATE TABLE employees (
      employee_id INT AUTO_INCREMENT PRIMARY KEY,
      first_name VARCHAR(50),
      last_name VARCHAR(50),
      hire_date DATE
  );
  
  

• Drop a Table

  sqlCopy code
  DROP TABLE table_name;
  
  

2. Basic Data Manipulation

• Insert Data

  sqlCopy code
  INSERT INTO table_name (column1, column2, ...)
  VALUES (value1, value2, ...);
  
  

  Example:

  sqlCopy code
  INSERT INTO employees (first_name, last_name, hire_date)
  VALUES ('John', 'Doe', '2024-08-28');
  
  

• Update Data

  sqlCopy code
  UPDATE table_name
  SET column1 = value1, column2 = value2, ...
  WHERE condition;
  
  

  Example:

  sqlCopy code
  UPDATE employees
  SET last_name = 'Smith'
  WHERE employee_id = 1;
  
  

• Delete Data

  sqlCopy code
  DELETE FROM table_name
  WHERE condition;
  
  

  Example:

  sqlCopy code
  DELETE FROM employees
  WHERE employee_id = 1;
  
  

3. Basic Query Operations

• Select Data

  sqlCopy code
  SELECT column1, column2, ...
  FROM table_name
  WHERE condition
  ORDER BY column1 [ASC|DESC]
  LIMIT number;
  
  

  Example:

  sqlCopy code
  SELECT first_name, last_name
  FROM employees
  WHERE hire_date > '2024-01-01'
  ORDER BY hire_date DESC
  LIMIT 10;
  
  

• Select All Columns

  sqlCopy code
  SELECT * FROM table_name;
  
  

4. Filtering and Sorting

• Filtering with WHERE

  sqlCopy code
  SELECT column1, column2
  FROM table_name
  WHERE condition;
  
  

  Example:

  sqlCopy code
  SELECT * FROM employees
  WHERE hire_date BETWEEN '2024-01-01' AND '2024-12-31';
  
  

• Sorting with ORDER BY

  sqlCopy code
  SELECT column1, column2
  FROM table_name
  ORDER BY column1 [ASC|DESC];
  
  

  Example:

  sqlCopy code
  SELECT * FROM employees
  ORDER BY hire_date DESC;
  
  

5. Joins and Relationships

• INNER JOIN: Retrieves rows with matching values in both tables.

  sqlCopy code
  SELECT columns
  FROM table1
  INNER JOIN table2 ON table1.column = table2.column;
  
  

  Example:

  sqlCopy code
  SELECT employees.first_name, departments.department_name
  FROM employees
  INNER JOIN departments ON employees.department_id = departments.department_id;
  
  

• LEFT JOIN: Retrieves all rows from the left table and matching rows from the right table.

  sqlCopy code
  SELECT columns
  FROM table1
  LEFT JOIN table2 ON table1.column = table2.column;
  
  

  Example:

  sqlCopy code
  SELECT employees.first_name, departments.department_name
  FROM employees
  LEFT JOIN departments ON employees.department_id = departments.department_id;
  
  

• RIGHT JOIN: Retrieves all rows from the right table and matching rows from the left table.

  sqlCopy code
  SELECT columns
  FROM table1
  RIGHT JOIN table2 ON table1.column = table2.column;
  
  

  Example:

  sqlCopy code
  SELECT employees.first_name, departments.department_name
  FROM employees
  RIGHT JOIN departments ON employees.department_id = departments.department_id;