SQL: Data Manipulation Language

Data Manipulation Language (DML) is used to retrieve and modify data values.

Run all statements listed below from withing the psql console.

INSERT:

INSERT INTO table_name (column_1, column_2)
VALUES (value_1, value_2);

NOTE: You do not need to declare the column values if you are providing a value for each column.

UPDATE VALUES IN ALL COLUMNS:
UPDATE table_name SET column_name = value;

UPDATE SPECIFIC VALUES (WHERE CLAUSE):

UPDATE table_name SET column_name = value
WHERE column_name = value;

UPDATE (WITH LIKE):

UPDATE table_name SET column_name = value
WHERE expression LIKE ‘%ing’;

DELETE ALL RECORDS:
DELETE FROM database_name;

DELETE A SPECIFIC VALUE:

DELETE FROM table_name
WHERE column_name = value;

NOTE: Must delete dependent data first unless column was setup with ON DELETE CASCADE

SELECT EVERYTHING:
SELECT * FROM table_name;

SELECT WITH ORDER BY CLAUSE:
SELECT * FROM table_name ORDER BY (column) DESC;

NOTE: ASC is the default

SELECT WITH DISTINCT:
SELECT DISTINCT column FROM table;

SELECT WITH INNER OUTER JOIN:

SELECT table1.*, table2.* FROM table1 
INNER JOIN table2 ON column1 = table2.column2;

SELECT WITH MULTIPLE INNER OUTER JOINS:

SELECT table1.column, table3.column AS alias
FROM table1
INNER JOIN table2 ON condition
INNER JOIN table3 ON condition
WHERE condition;

SELECT WITH LEFT OUTER JOIN:

SELECT table1.*, table2.* FROM table1 
LEFT JOIN table2 ON table1.column1 = table2.column2;

SELECT WITH MULTIPLE RIGHT OUTER JOINS:

SELECT table1.column1, table3.column3 FROM table1 
RIGHT JOIN table2 ON (table2.column2 = table1.column1)
RIGHT JOIN table3 ON (table3.id = table2.column2);

SELECT WITH FULL OUTER JOIN:

SELECT columns
FROM table1
FULL OUTER JOIN table2 ON condition
FULL OUTER JOIN table3 ON condition
WHERE column_x condition OR column_y condition;

NOTE: This returns null values where there is no match between conditions.

FORMATTING QUERY OUTPUT AND USING ALIAS:

SELECT CONCAT(column1, ‘string’, column2) 
AS output_table_name FROM table;
SELECT alias.column_name FROM table_name AS alias;

SELECT WITH GROUP BY:

SELECT column, other_table.column AGG_FUNC AS alias 
FROM table
JOIN other_table
ON table.column = column
GROUP BY column;

NOTE: You do not need other_table if all columns are unique.

Aggregate Functions:
COUNT()
ROUND()
SUM()
RANDOM()
AVG()

Other Functions:
TRIM(), RTRIM(), LTRIM()
LENGTH()
CONCAT()
MAX(), MIN()
NOW()
ABS()

SUBQUERY IN FROM CLAUSE:
SELECT column_name FROM (subquery) AS alias;

SUBQUERY IN WHERE CLAUSE:

SELECT column FROM table
WHERE column IN (subquery)

Other Keywords:

NOT IN
EXISTS
SOME/ANY
ALL

SUBQUERY IN SELECT CLAUSE:
SELECT column, (subquery) AS alias FROM table_name

NOTE: This subquery return 1 value.

Show your support

Clapping shows how much you appreciated Jocie Moore’s story.