SQL Basics: Convention, Commands, Constraints, and Operators

https://www.wevolver.com/article/making-databases-faster

Style Convention

SELECT name, phone_number, department_id FROM employees;
Todo One-to-one relationship: 
CREATE TABLE employees (
employee_id int PRIMARY KEY AUTO_INCREMENT,
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)
);

Commands

// 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
INSERT INTO employees (name, age)
VALUES ("Soo Kim", 30);
INSERT INTO employees
VALUES (1, "Soo Kim", "123-456-7890", 30);

WHERE can be used with operators — AND, OR, NOT, IS NULL, IS NOT NULL, LIKE, etc.

UPDATE employees 
SET phone_number = "000-111-222" WHERE name = "Soo Kim";
DELETE FROM employees 
WHERE name = "Soo Kim" AND NOT employee_age <= 30;
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
ORDER BY age
LIMIT 1;
// 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;

Constraints

CREATE TABLE young_adult_users (
user_id int PRIMARY KEY AUTO_INCREMENT,
age int NOT NULL CHECK(age>=18 AND age<30),
has_subscribed_to_email tinyint(1) DEFAULT true
);
CREATE INDEX index_name 
ON table_name (column_name);
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)
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
WHERE age BETWEEN 20 AND 30;
SELECT * FROM burgers
WHERE price NOT BETWEEN 10 AND 20
AND burger_id NOT IN (1, 2, 3);
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
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 ...,
);
UPDATE employees_copy
SET siblings = "Hannah, Shawn"
WHERE employee_id = 1;
// assumes that no other information for siblings will be provided or needed.

--

--

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