SQL Syntax

SQL (Structured Query Language) is used to manage and manipulate relational databases. Below are some of the key SQL syntax components:

1. Data Definition Language (DDL)

• CREATE TABLE: Creates a new table in the database.

  CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    ...
  );
  

• ALTER TABLE: Modifies an existing table.

  ALTER TABLE table_name
  ADD column_name datatype constraint;
  
  ALTER TABLE table_name
  DROP COLUMN column_name;
  
  ALTER TABLE table_name
  MODIFY column_name new_datatype;
  

• DROP TABLE: Deletes a table and all of its data.

  DROP TABLE table_name;
  

2. Data Manipulation Language (DML)

• INSERT INTO: Inserts new data into a table.

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

• UPDATE: Updates existing data within a table.

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

• DELETE: Deletes data from a table.

  DELETE FROM table_name
  WHERE condition;
  

3. Data Query Language (DQL)

• SELECT: Retrieves data from the database.

  SELECT column1, column2, ...
  FROM table_name
  WHERE condition
  GROUP BY column
  HAVING condition
  ORDER BY column
  LIMIT number;
  

  Examples:

  • Basic Select:

    SELECT * FROM employees;
    

  • Select with conditions:

    SELECT name, salary FROM employees WHERE department = 'Sales';
    

4. Data Control Language (DCL)

• GRANT: Gives user access privileges to the database.

  GRANT privilege_name ON object TO user;
  

• REVOKE: Withdraws user access privileges given by the GRANT command.

  REVOKE privilege_name ON object FROM user;
  

5. Transaction Control Language (TCL)

• COMMIT: Saves the transaction permanently in the database.

  COMMIT;
  

• ROLLBACK: Undoes transactions that have not been saved to the database.

  ROLLBACK;
  

• SAVEPOINT: Sets a point within a transaction to which a rollback can be performed.

  SAVEPOINT savepoint_name;
  

• SET TRANSACTION: Sets the characteristics of the current transaction.

  SET TRANSACTION [read write | read only];
  

6. Joins

• INNER JOIN: Selects records that have matching values in both tables.

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

• LEFT JOIN (or LEFT OUTER JOIN): Selects all records from the left table and the matched records from the right table.

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

• RIGHT JOIN (or RIGHT OUTER JOIN): Selects all records from the right table and the matched records from the left table.

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

• FULL JOIN (or FULL OUTER JOIN): Selects all records when there is a match in either left or right table.

  SELECT columns
  FROM table1
  FULL JOIN table2
  ON table1.column = table2.column;
  

Example Database Table Creation

Let's say we want to create a database table for storing information about employees:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE NOT NULL,
    job_id VARCHAR(10) NOT NULL,
    salary DECIMAL(10, 2),
    department_id INT
);

With these basic SQL commands and syntax, you can effectively manage and manipulate data within a relational database. If you need further details or specific examples, feel free to ask!