This is a learning note related to relational database.

Intro

  1. Relational Database & Non-Relational database (e.g. json)
    • Relational Databases use SQL and store data in tables with rows and columns
  2. SQL (Structured query language)
  3. DBMS (Database Management System)
    • it is a software application that allows users to perform C.R.U.D (Create, Read, Update, Delate) operations and other administrative tasks.

SQL

  1. Capital or not

    It is not necessary. But capital cords can help us to distinguish the reserved word with what we have written.

  2. Semicolon

    All statements need to be end with semicolon ( ; ).

  3. Datatype

    • **INT: ** whole numbers
    • **DECIMAL(M,N): **decimal numbers. M is the number of total digits; N is the number of decimal digits.
    • **VARCHAR(N): **string. N suggests the number of characters
    • **BLOB: **binary large object, stores large data
    • **DATE: **‘YYYY-MM-DD’
    • **TIMESTAMP: **‘YYYY-MM-DD HH:MM:SS’

  4. Create table

    • **Primary Key: ** two ways to denote the primary key

      CREATE TABLE student(
    student_id INT,
    name VARCHAR(20),
    major VARCHAR(20),
    PRIMARY KEY(student_id) 
);

      CREATE TABLE student(
    student_id INT PRIMARY KEY,
    name VARCHAR(20),
    major VARCHAR(20)
);

    • **Constraints: **

      • NOT NULL (the field cannot be NULL);
      • UNIQUE (each value in the same attribute should be different);
      • DEFAULT (set a defsult value);
      • AUTO_INCREMENT (increase the number automatically)
      CREATE TABLE student(
    student_id INT AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    major VARCHAR(20) UNIQUE,
  	gpa DECIMAL(3,2) DEFAULT 0.00,
    PRIMARY KEY(student_id) 
);

    • **FOREIGN KEY: **

      • **ON DELETE SET ** NULL: when the foreign key is deleted, the related field will be set to null
      CREATE TABLE branch (
  branch_id INT PRIMARY KEY,
  branch_name VARCHAR(40),
  mgr_id INT,
  mgr_start_date DATE,
  FOREIGN KEY(mgr_id) REFERENCES employee(emp_id) ON DELETE SET NULL
);
      • ON DELETE CASCADE: when the foreign key is deleted, the whole row will be deleted

  5. **Describe table **

    DESCRIBE student;

  6. Delete table

    DROP TABLE student;

  7. Modify table

    • Add column in table

      ALTER TABLE student ADD gpa DECIMAL(3,2);

    • Drop column in table

      ALTER TABLE student DROP COLUMN gpa;

  8. Insert information in table

    INSERT INTO student VALUES(1, 'Jack', 'Biology');  	-- with all things
INSERT INTO student(student_id, major) VALUES(3, 'Computer Science'); -- no name
INSERT INTO student(student_id, name) VALUES(4, 'Claire'); -- no major

  9. Update information in table

    • Comparision operations
      • $=$ (equals)
      • $<>$ (not equals)
      • $>$; $>=$
      • $<$; $<=$
    • Where is optional.
    UPDATE student 
SET name = 'Someone', major = 'Biochemistry'
WHERE major = 'Biology' OR major = 'Chemistry';

  10. **Delete information in table **

    DELETE FROM student
WHERE name = 'Someone' AND student_id = 1;

  11. Query information from table

    • **DESC: ** set to descending order, default is ascending order .
    • **ASC: ** set to ascenging order
    • In SQL, the correct order of the clauses should be: SELECT, FROM, WHERE, ORDER BY, and LIMIT.
    • AS: rename
    • **DISTINCT: **return distinct values
    SELECT student_id AS id, name, major 
FROM student
WHERE student_id <> 1 AND name IN ('Maria', 'Jack')
ORDER BY major, student_id DESC
LIMIT 2; -- limite the number of rows returned, in this case, we can only get 2 rows back

  12. Functions

    • **COUNT: ** count the number of subjects
    • **AVG: **compute average value
    • **SUM: ** compute the sum
    • **GROUP BY: **groups rows that have the same values into summary rows
    SELECT COUNT(emp_id), AVG(salary), SUM(salary)
FROM employee;
    
SELECT COUNT(sex), sex
GROUP BY sex;

  13. Widdcards

    • %: represents zero or more characters
    • _: represents a single character
    • [any_characters]: represents any single character within the brackets
    • [^any_characters]: epresents any character not in the brackets
    • [character1-character2]: represents any single character within the specified range
    SELECT *
FROM client
WHERE client_name LIKE '%LLC'

  14. Unions

    • Every SELECT statement within UNION must have the same number of columns
    • The columns must also have same data types
    • The columns in every SELECT statement must also be in the same order
    SELECT client_name, client.branch_id
FROM client
UNION
SELECT supplier_name, branch_supplier.branch_id
FROM branch_supplier

  15. Joins

    • JOIN: combine rows from two or more tables, based on a related column between them.
    SELECT employee.emp_id, employee.first_name, branch.branch_name
FROM employee
JOIN branch
ON employee.emp_id = branch.mgr_id;
    • INNER JOIN: ** same with **JOIN
    • **LEFT JOIN: **returns all records from the left table (employee), and the matching records from the right table (branch).
    • RIGHT JOIN: **do the opposite from **LEFT JOIN
    • FULL (OUTER) JOIN: **combined **LEFT JOIN and RIGHT JOIN

  16. Triggers

    One useful way to keep track of the actions in database, see the vedio triggers for details

Difinations

  1. Surrogate key

    a type of primary key, doesn’t mean anything in the real world, just a key assigned to a value

  2. Natural key

    a type of primary key, but has a mapping in the real world not just in the database.

  3. Foreign key

    stores the primary key of a row in another database table, help defines the relationships between different tables.

  4. Composite key

    refers to two or more columns that act as primary key

Reference

SQL Tutorial

SQL Exercises