SQL (Structured Query Language) is a standard language that we use for communicate with databases.

SELECT Statement

• Intent Select data from a database.
• Syntax

  sql
  SELECT column1, column2, ...
  FROM table_name;

• Example

  sql
  SELECT first_name, last_name
  FROM students;

INSERT Statement

• Intent Insert new data into a table.
• Syntax

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

• Example

  sql
  INSERT INTO students (first_name, last_name, department)
  VALUES ('Kimkorng', 'Mao', 'Computer Science');

UPDATE Statement

• Intent Update existing data within a table.
• Syntax

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

• Example

  sql
  UPDATE students
  SET department = 'Computer Science'
  WHERE student_id = 5;

DELETE Statement

• Intent Delete data from a table.
• Syntax

  sql
  DELETE FROM table_name
  WHERE condition;

• Example

  sql
  DELETE FROM students
  WHERE last_name = 'Mao';

WHERE Clause

• Intent For filtering records (Mostly used with UPDATE and DELETE).
• Syntax

  sql
  SELECT column1, column2, ...
  FROM table_name
  WHERE condition;

• Example

  sql
  SELECT * FROM students
  WHERE department = 'Computer Science';

Advanced SQL Concepts

JOIN Operations

• Intent Combine rows from two or more tables, based on a related column between them.
• Types
  • INNER JOIN Returns records that have matching values in both tables.
  • LEFT (OUTER) JOIN Returns all records from the left table (table1), and the matching records from the right table (table2).
  • RIGHT (OUTER) JOIN Returns all records from the right table and matched records from the left table.
  • FULL (OUTER) JOIN Returns all records when there is a match in either left or right table.
• Syntax

  sql
  SELECT table1.column1, table2.column2, ...
  FROM table1
  JOIN table2
  ON table1.common_column = table2.common_column;

• Example

  sql
  SELECT students.first_name, departments.department_name
  FROM students
  INNER JOIN departments
  ON students.department_id = departments.department_id;

GROUP BY Clause

• Intent Group rows that have the same values into summary rows.
• Syntax

  sql
  SELECT column1, COUNT(*)
  FROM table_name
  GROUP BY column1;

• Example

  sql
  SELECT department, COUNT(*)
  FROM students
  GROUP BY department;

ORDER BY Clause

• Intent Sort the result set.
• Syntax

  sql
  SELECT column1, column2, ...
  FROM table_name
  ORDER BY column1 [ASC|DESC];

• Example

  sql
  SELECT first_name, last_name
  FROM students
  ORDER BY last_name ASC;

HAVING Clause

• Intent Filter groups based on a specified condition (used with GROUP BY).
• Syntax

  sql
  SELECT column1, COUNT(*)
  FROM table_name
  GROUP BY column1
  HAVING COUNT(*) > value;

• Example

  sql
  SELECT department, COUNT(*)
  FROM students
  GROUP BY department
  HAVING COUNT(*) > 10;

Data Types

Common SQL Data Types

• Numeric
  • INT: Integer
  • FLOAT: Floating-point number
  • DECIMAL: Fixed-point number
• String
  • VARCHAR(size): Variable-length string
  • CHAR(size): Fixed-length string
• Date/Time
  • DATE: Date value
  • TIME: Time value
  • DATETIME: Date and time value

Creating a Table

• Intent Create a new table in a database.
• Syntax

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

• Example

  sql
  CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    department_id INT,
    salary DECIMAL(10, 2) CHECK (salary > 0),
    hire_date DATE
  );

Key Constraints

PRIMARY KEY

• Intent Ensures that each row/record in a table is unique.
• Syntax

  sql
  CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype,
    ...
  );

FOREIGN KEY

• Intent Used to link two tables together.
• Syntax

  sql
  CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    FOREIGN KEY (column1) REFERENCES another_table(column)
  );

UNIQUE

• Intent Ensures all values in a column are unique.
• Syntax

  sql
  CREATE TABLE table_name (
    column1 datatype UNIQUE,
    column2 datatype,
    ...
  );

NOT NULL

• Intent Ensures that a column cannot have a NULL value.
• Syntax

  sql
  CREATE TABLE table_name (
    column1 datatype NOT NULL,
    column2 datatype,
    ...
  );

CHECK

• Intent Ensures that the values in a column meet a specific condition.
• Syntax

  sql
  CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    CHECK (condition)
  );