SQL syntax

Included here are some notes I jotted down while watching this SQL tutorial from freeCodeCamp. Kudos to freeCodeCamp, which offers the best free resources to self-taught developers. - Haowen Jiang

• SQL syntax
  • Comments
  • Data types
  • Creating tables
  • Inserting data
  • Updating and deleting data
  • Basic queries
  • Built-in functions
    • The schema for the company database
    • COUNT()
    • AVG()
    • SUM()
  • Wildcards
  • Union
  • Join
    • Inner join
    • Left join
    • Right join
  • Nested queries
  • Trigger
  • Entity relationship diagrams
  • Postscript

Comments

-- This is a comment in SQL!

Data types

-- integers
INT
-- 5 digits in total, 2 digits after the decimal point
DECIMAL(5, 2)
-- max string len = 3
VARCHAR(3) 
-- binary data
BLOB 
-- 2022-06-06 
DATE 
-- 2022-06-06 22:22:22
TIMESTAMP 

Creating tables

• Create a table

CREATE TABLE student (
    student_id INT AUTO_INCREMENT,
    name VARCHAR(20),
    major VARCHAR(20) DEFAULT 'undecided',
    PRIMARY KEY(student_id)
);

• Describe a table

DESCRIBE student;

• Delete a table

DROP TABLE student;

• Create a column in a table

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

• Delete a column from a table

ALTER TABLE student DROP COLUMN gpa;

Inserting data

• Insert a row

INSERT INTO student VALUES(
    1,
    'Jack',
    'Biology'
);

Updating and deleting data

• Update all instances of one value with another under a condition

UPDATE student 
SET major = 'Bio'
WHERE major = 'Biology';

• Update all instances of two values with one under a condition

UPDATE student 
SET major = 'Biochemistry'
WHERE major = 'Bio' OR major = 'Chemistry';

• Update multiple columns of a row under a condition

UPDATE student
SET name = 'Tom', major = 'undecided'
WHERE student_id = 1;

• Delete a specific row

DELETE FROM student
WHERE student_id = 5;

Basic queries

• Select all columns

SELECT * FROM student;

• Select specific columns

SELECT name, major FROM student;

• Select specific columns (another way)

SELECT student.name, student.major 
FROM student;

• Order by a column in ascending order (by default)

SELECT name, major 
FROM student
ORDER BY name;

• Order by a column in descending order

SELECT name, major 
FROM student
ORDER BY name DESC;

• Order by multiple columns

SELECT * 
FROM student
ORDER BY name, student_id;

• Limit the results by a number

SELECT * 
FROM student
LIMIT 5;

• Filter the results by a comparison operator

SELECT * 
FROM student
-- = equal to
-- <> NOT equal to
WHERE major = 'Biology';

• Filter the results by comparing against a list

SELECT * 
FROM student
WHERE name IN ('Kate', 'Mike');

• Give an alias to a column

SELECT first_name AS forename
FROM employee

• Select distinct values in a column

SELECT DISTINCT last_name
FROM employee

• Summary formula

SELECT column1 as alias, DISTINCT column2
FROM any_table
WHERE codition1='text' AND condition2>5
ORDER BY column3 ASC, column4 DESC
LIMIT 5;

Built-in functions

The schema for the company database

COUNT()

• Count the number of rows in a column

SELECT COUNT(emp_id)
FROM employee;

• Count the number of female employees born after 1970

SELECT COUNT(emp_id)
WHERE sex = 'F' AND birth_date > '1970-01-01'
FROM employee;

• Count the number of male and female employees

SELECT sex, COUNT(sex)
FROM employee
GROUP BY sex;

AVG()

• Find the average salary of male employees

SELECT AVE(salary)
FROM employee
WHERE sex = 'M';

SUM()

• Find the total salary of employees

SELECT SUM(salary)
FROM employee;

• Find the total sales of each employee

SELECT emp_id, SUM(total_sales)
-- The works_with table has three columns:
-- emp_id, client_id, and total_sales.
FROM works_with
GROUP BY emp_id;

Wildcards

• %: for any number of characters, equivalent to * in Regex

• _: for a single character, equivalent to . in Regex

• Find any employee whose last name ends with son

SELECT emp_id, first_name, last_name
FROM employee
WHERE last_name LIKE '%son';

• Find any employee born in September

SELECT emp_id, first_name, last_name
FROM employee
WHERE birth_date LIKE '____-09%';

Union

• Two constraints with UNION:

  • The number of columns has to match.
  • The data types have to match.

• Find a list of all client names and supplier names

SELECT client_name AS client_supplier
FROM client
UNION
SELECT supplier_name
FROM supplier;

Join

Inner join

• Find all branches and the names of their managers, and include only employees that manage a branch

SELECT employee.emp_id, employee.first_name, branch.branch_name
FROM employee
JOIN branch
ON employee.emp_id = branch.mgr_id;

Left join

• Find all branches and the names of their managers, and include all employees

SELECT employee.emp_id, employee.first_name, branch.branch_name
FROM employee
LEFT JOIN branch
ON employee.emp_id = branch.mgr_id;

Right join

• Find all branches and the names of their managers, and include all branches

SELECT employee.emp_id, employee.first_name, branch.branch_name
FROM employee
RIGHT JOIN branch
ON employee.emp_id = branch.mgr_id;

Nested queries

• Find names of all employees who have sold over 30K to a single client

-- Start from the inner querry
-- Then embed it to IN() in the outer query
SELECT employee.first_name, employee.last_name
FROM employee
WHERE employee.emp_id IN (
    SELECT works_with.emp_id
    FROM works_with
    WHERE works_with.total_sales > 3000
)

Trigger

• Create a trigger in the SQL terminal

# change the default delimiter from ; to $$
DELIMITER $$

# create a trigger 
CREATE 
    TRIGGER my_trigger BEFORE INSERT
    ON employee
    FOR EACH ROW BEGIN
        INSERT INTO trigger_test VALUES('added new employee')
    END$$
    
# change the delimiter back to the default
DELIMITER ;

• What the trigger above does is that whenever a new row is inserted into the employee table, a fixed message will be inserted into the trigger_test table.

• Another trigger, but with more conditions

# change the default delimiter from ; to $$
DELIMITER $$

# create a trigger 
CREATE 
    TRIGGER my_trigger2 BEFORE INSERT
    ON employee
    FOR EACH ROW BEGIN
        IF NEW.sex 'M' THEN
            INSERT INTO trigger_test VALUES('added male employee');
        ELSEIF NEW.sex 'F' THEN
            INSERT INTO trigger_test VALUES('added female employee');
        ELSE
            INSERT INTO trigger_test VALUES('added other employee');
        END IF;
    END$$
    
# change the delimiter back to the default
DELIMITER ;

• What the trigger above does is that whenever a new row is inserted into the employee table, a different message will be inserted into the trigger_test table depending on the sex of the newly inserted employee.

• Delete a trigger in the SQL terminal

DROP TRIGGER my_trigger

Entity relationship diagrams

• Entity: e.g. student
  • Weak entity: an entity that cannot be uniquely identified by its attributes alone, e.g. exam, which is not independent from class
• Attribute: features about an entity, e.g. student_id, gpa
  • Composite attribute: attribute that consists of multiple attributes, e.g. name consisting of last_name and first_name
  • Multi-valued attribute: attribute that has multiple values, e.g. clubs, whose values might be guitar, dancing, etc.
  • Derived attribute: attribute that can be derived from other attributes, e.g. has_honors, which can be derived from gpa
• Relationship: a relationship between two entities, eg. student holds the relationship takes with class.
  • Partial participation: e.g. only some students take a specific class
  • Total participation: e.g. all classes are taken by at least a student
  • Relationship attribute: attribute about a relationship, e.g. grade in the takes relationship between student annd class
  • Relationship cardinality:
    • one-to-one: 1:1
    • one-to-many: 1:N
    • many-to-one: N:1
    • many-to-many: N:M
  • Identifying relationship: a relationship that serves to uniquely identify a weak entity, e.g. has between class, a normal entity, and exam, a weak entity

Postscript

Not until I finished writing the notes above did I find this page, where Mike Dane, the instructor, already created a nice-looking page for all the SQL syntax covered in this tutorial.