SQL Basics: Convention, Commands, Constraints, and Operators


Table of Index

  • Style convention
    - Snake-case (lowercase_underscore) for column and table names
    - Capitalization for keywords and reserved words
    - Prefix Ambiguous Terms
    - Singular/Plural table names and singular column names
    - Foreign Key
  • Commands
    - DDL
    (Data Definition Language): create, drop, alter, rename, truncate
    - DML (Data Manipulation Language): insert into, update, delete from, select from
    Functions — count, min, max, sum, avg, concat(SELECT)
    Keywords — distinct, order by, limit, as (SELECT)
    Operators — and, or, not, is null, is not null (used with WHERE clause)
    - DQL (Data Query Language): select
    - DCL (Data Control Language):
    - TCL (Transaction Control Language):
  • Constraints — not null, unique, primary key, foreign key, check, default, create index
  • Others — like, in, between, wildcards
  • Join
  • How to deal with lists?

Style Convention

Snake-case (lowercase_underscore) for column and table names

SELECT name, phone_number, department_id FROM employees;

Capitalization for keywords and reserved words & lowercase for data types

Prefix Ambiguous Terms & Do not Use Unconventional Abbreviation

Singular/Plural table names and singular column names

Foreign Key

Todo One-to-one relationship: 
CREATE TABLE employees (
name varchar(50) NOT NULL,
phone_number varchar(25),
age int NOT NULL
CREATE TABLE marketing_team_employees (
employee_id int PRIMARY KEY,
job_position varchar(50) NOT NULL,
FOREIGN KEY (employee_id) REFERENCES employees(employee_id)


DDL (Data Definition Language)

// Copy another table 
CREATE TABLE employees_copy AS
SELECT name, phone_number
FROM employees;
CREATE TABLE employees_copy AS SELCT * FROM employees;ALTER TABLE employees
ADD department varchar(50); // add new columm
ALTER TABLE employees
MODIFY COLUMN department int; // modify data type
ALTER TABLE employees
RENAME COLUMN department TO department_id; // modify column name
ALTER TABLE employees
DROP COLUMN department_id; // remove column
DROP TABLE employees; // remove table completely

DML (Data Manipulation Language)

  • INSERT INTO <table_name> (column list) VALUES (values);
    if adding values for all columns: INSERT INTO <table_name> VALUES;
INSERT INTO employees (name, age)
VALUES ("Soo Kim", 30);
INSERT INTO employees
VALUES (1, "Soo Kim", "123-456-7890", 30);
  • UPDATE <table_name> SET column_name = value WHERE condition;
UPDATE employees 
SET phone_number = "000-111-222" WHERE name = "Soo Kim";
  • DELETE FROM <table_name> (WHERE condition);
DELETE FROM employees 
WHERE name = "Soo Kim" AND NOT employee_age <= 30;
  • SELECT * FROM <table_name>; // all columns
    (technically DQL but when you add FROM or WHERE, it becomes part of DML)
SELECT * FROM employees
WHERE department_id = 1;
SELECT DISTINCT department_id FROM employees; // returns only different values of department_idSELECT COUNT(employee_id) AS "Marketing Team Member Count" FROM employees
WHERE department_id = 1;
// returns the number of rows (COUNT)
// return column name: Marketing Team Member Count (AS) = also known as aliases
SELECT COUNT(DISTINCT department_id) FROM employees;
// returns the number of departments
SELECT name AS youngest_employee FROM employees
// sort in ascending order
// return only the first record
SELECT * FROM employees
ORDER BY age DESC, name;
// sorted by age descending order, and then name in ascending order
SELECT MIN(age) FROM employees // vs MAX(...)
WHERE department_id = 1;
// returns smallest value of age
SELECT AVG(age) FROM employees AS average_age;
// returns average age
SELECT SUM(order_quantity) FROM orders; // returns sum of ordersSELECT customer_id, name, CONCAT(address, ", ", city, " ", country, " ", postal_code) AS address
FROM customers; //
SELECT employees.employee_id, bonuses.bonus_amount, employees.bank_name, employees.bank_account FROM employees, bonuses
WHERE employees.employee_id = bonuses.employee_id
ORDER BY employees.employee_id;

DQL (Data Query Language)

DCL (Data Control Language)

TCL (Transaction Control Language)

  • ROLLBACK: Rollbacks a transaction in case of any error occurs.
  • SAVEPOINT: Sets a savepoint within a transaction.
  • SET TRANSACTION: Specify characteristics for the transaction.
  • COMMIT: Commits a transaction


  • UNIQUE — all values in a column are different
  • PRIMARY KEY — unique key that identifies each row (NOT NULL + UNIQUE)
  • FOREIGN KEY — prevents actions that would destroy links between tables; refers to the primary key of another table
    - table with foreign key = child table
    - table with primary key = referenced / parent table
  • CHECK — limit the values in a column
  • DEFAULT — a default value for a column if no value is specified
CREATE TABLE young_adult_users (
age int NOT NULL CHECK(age>=18 AND age<30),
has_subscribed_to_email tinyint(1) DEFAULT true
  • CREATE INDEX — to create and retrieve data from db very quickly; only create indexes on columns that will be frequently searched, because updating tables with indexes takes longer than updating those without
CREATE INDEX index_name 
ON table_name (column_name);

Wildcards, Other Operators (Like, In, Between), and Aliases

  • % : 0+ characters
SELECT * FROM employees 
WHERE name LIKE "s%"; // start with s (eg. Soo)
WHERE name LIKE "%s"; // end with s (eg. Lewis)
WHERE name LIKE "%oo%"; // have "oo" in any position (eg. Soo)
WHERE name LIKE "d%l"; // start with d and end with l (eg. Daniel)
  • _ : single character
SELECT * FROM employees 
WHERE name LIKE "_e%"; // have e in 2nd position (eg. Lewis)
WHERE name LIKE "da__%"; // start with da, and are at least 3 characters in length (eg. Daniel)
SELECT * FROM employees
WHERE employee_id IN (1, 2, 3);
SELECT * FROM employees
WHERE department_id IN (SELECT department_id FROM employees WHERE name = 'Soo Kim'); // select all employees that have the same department_id as employee with name "Soo Kim"
SELECT * FROM employees
SELECT * FROM burgers
AND burger_id NOT IN (1, 2, 3);


  • (INNER) JOIN — returns all records that have matching values
SELECT * FROM employees
INNER JOIN bonuses WHERE employees.employee_id = bonuses.employee_id
ORDER BY employees.employee_id;
// returns all column & its values from both employees and bonuses if employee_id matches in both tables
// records from employees and then bonuses
  • LEFT (OUTER) JOIN — returns all records from left table, and matched records from right table; if no records exist with the same matching value on the right table, it’ll show up as NULL
  • 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

How to Deal With Lists?

  • junction table: have separate tables identifying each entity, and have a junction table contains primary keys of the tables you want to relate. That way, you can find and analyze information related to each primary key.
CREATE TABLE students (
student_id ...,
name ...,
birthday ...,
CREATE TABLE classes (
class_id ...,
name ...,
professor_id ...,
professor_name ...,
semester ...,
academic_year ...,
CREATE TABLE professors (
professor_id ...,
name ...,
birthday ...,
CREATE TABLE students_classes (
id ...,
student_id ...
class_id ...
SELECT COUNT(*) FROM students_classes
WHERE student_id = (SELECT student_id FROM students WHERE name = "Soo Kim");
// count how many classes student "Soo Kim" is taking
SELECT COUNT(*) FROM students_clases
WHERE class_id = (SELECT class_id FROM classes WHERE academic_year = 2022 AND semester = "FALL" AND name = "French 101");
// count how many students are class "French 101" in 2022 Fall semester
CREATE TABLE professors_classes (
id ... ,
professor_id ...,
class_id ...,
  • separator: this is the most primitive yet easy way. Use a separator (like comma, semi-colon, hyphen, underscore etc) between the values. However, I would only use this method for values will not need information regarding the values, and 1:M relationships.
UPDATE employees_copy
SET siblings = "Hannah, Shawn"
WHERE employee_id = 1;
// assumes that no other information for siblings will be provided or needed.



Flutter & Node.js Full-Stack Developer

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store