Kimkorng

SQL (Structured Query Language)

20 OCT, 2022

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)
);
SHARE