SQL

Basics

Command	Description
SELECT	retrieves data from one or more tables
FROM	specifies the table or tables from which to retrieve data
WHERE	filters the results based on specified criteria
GROUP BY	groups the results by one or more columns
HAVING	filters the results of a GROUP BY clause based on specified criteria
ORDER BY	sorts the results by one or more columns

Operators

Operator	Description
=	tests for equality between two values
<> or !=	tests for inequality between two values
<	tests if one value is less than another
>	tests if one value is greater than another
<=	tests if one value is less than or equal to another
>=	tests if one value is greater than or equal to another
BETWEEN	tests if a value is between two other values
LIKE	tests if a value matches a pattern using wildcards
IN	tests if a value matches any value in a list of values
NOT	negates a condition

Functions

Function	Description
COUNT()	counts the number of rows or non-null values in a column
SUM()	calculates the sum of values in a column
AVG()	calculates the average of values in a column
MAX()	retrieves the maximum value in a column
MIN()	retrieves the minimum value in a column
CONCAT()	concatenates two or more strings
UPPER()	converts a string to uppercase
LOWER()	converts a string to lowercase
SUBSTRING()	retrieves a portion of a string

Joins

Join	Description
INNER JOIN	retrieves rows from both tables where the join condition is true
LEFT JOIN	retrieves all rows from the left table and matching rows from the right table
RIGHT JOIN	retrieves all rows from the right table and matching rows from the left table
FULL OUTER JOIN	retrieves all rows from both tables

Subqueries

Subquery	Description
SELECT	can be used to retrieve data from a subquery as a table
WHERE	can be used to filter results based on a subquery
IN	can be used to test if a value matches any value in a subquery
EXISTS	can be used to test if a subquery returns any rows

Examples

Creating a table

CREATE TABLE students (
  id INT PRIMARY KEY,
  name TEXT NOT NULL,
  age INT,
  gender CHAR(1)
);

Inserting data into a table

INSERT INTO students (id, name, age, gender)
VALUES (1, 'John Doe', 21, 'M'),
       (2, 'Jane Doe', 19, 'F'),
       (3, 'Bob Smith', 22, 'M');

Updating data in a table

UPDATE students
SET age = 23
WHERE name = 'John Doe';

Deleting data from a table

DELETE FROM students
WHERE id = 3;

Selecting data from a table

SELECT * FROM students;

Selecting specific columns

SELECT name, age FROM students;

Filtering data with WHERE

SELECT * FROM students
WHERE gender = 'M';

Sorting data with ORDER BY

SELECT * FROM students
ORDER BY age DESC;

Aggregating data with GROUP BY

SELECT gender, AVG(age) as avg_age
FROM students
GROUP BY gender;

Joining tables

CREATE TABLE grades (
  id INT,
  subject TEXT,
  grade INT
);

INSERT INTO grades (id, subject, grade)
VALUES (1, 'Math', 85),
       (1, 'Science', 92),
       (2, 'Math', 90),
       (2, 'Science', 88);

SELECT students.name, grades.subject, grades.grade
FROM students
JOIN grades
ON students.id = grades.id;

Using subqueries

SELECT name, age
FROM students
WHERE id IN (
  SELECT id
  FROM grades
  WHERE subject = 'Math' AND grade > 80
);

Using LIKE to search for patterns

SELECT name
FROM students
WHERE name LIKE '%Doe%';