Basic SQL queries in a nutshell

Constraints:

• Key constraints :

— Restrict 1–1

— 1-many

— many-many.

• Participation Constraints

— Total : Every instance of an entity is participating the relationship.

— Partial : The participation of an entity in the relationship is not total.

CRUD:

CREATE:

CREATE DATABASE database_name;
CREATE SCHEMA schema_name;
CREATE TABLE table_name
(attribute_name data_type attribute_constriant,
table_constriant);

— DATATYPE

• Boolean Type — boolean
• Character Types — char(n), varchar(n), text
• Numeric Types — integer, serial, real
• Date/Time Types — timestamp , date, time, interval

NULL:

• IS NULL/ IS NOT NULL
• >/</AND/OR -> NULL
• COUNT/SUM/MIN/MAX/AVG -> ignore

— CONSTRIANTS

• CHECK (CONDITIONS)
• NOT NULL, NULL
• DEFAULT

— INTEGRITY CONSTRIANTS

• UNIQUE KEYS (can be NULL, can have many)
• PRIMARY KEYS (cannot be NULL, only one)
• FOREIGN KEYS

ACTION: CASCADE/ NO ACTION/ RESTRICT/ SET NULL/ SET DEFAULT

FOREIGN KEY (attr_name)
REFERENCES table [(attr_name)]
ON [DELETE/ UPDATE] ACTION

INSERT:

INSERT INTO table_name (attr_name) VALUES (value), (value);

COPY:

COPY table_name (attr_name)
FROM ‘file_path’
DELIMITER ‘SYMBOL’
CSV HEADER;

READ:

SELECT (DISTINCT) attr_name
FROM table_name
WHERE constraint
GROUP BY attr_name
HAVING constraint
ORDER BY attr_name
LIMIT number;

UPDATE:

UPDATE table_name
SET attr_name = value
WHERE constraint;
ALTER TABLE table_name ACTION;

ACTIONS:

ADD COLUMN attr_name data_type constraint;
DROP COLUMN attr_name;
RENAME COLUMN attr_name TO new_attr_name;
ALTER COLUMN attr_name SET NOT NULL/ DROP NOT NULL;
RENAME TO new_table_name;

DELETE:

DELETE FROM table_name WHERE constraints;
DROP TABLE IF EXISTS table_name CASCADE/ RESTRICT;

FUNCTIONS:

DATA TYPE CONVERSION

• CAST:

CAST (expression AS datatype);
expression::datatype;

• TO_ FUNCTION

TO_CHAR(timestamp);
TO_DATE(text);
TO_TIMESTAMP(text);
TO_CHAR(numerical_type);
TO_NUMBER(text);

STRING FUNCTIONS:

LENGTH(string)
||
LOWER(string)
UPPER(string)
TRIM(string)
REPLACE(string, from, to)
LEFT(string, number) -> return n first chars from left
RIGHT(string, number)
LIKE pattern. — %, _
SIMILAR TO regex

MATH FUNCTIONS:

ABS(x)
ROUND(number, number)
CELL(number)
FLOOR(number)
POWER(a, 2) = a^2
LOG(2, a) = log2(a)
GREATEST()
LEAST()

TIME FUNCTIONS:

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
EXTRACT(field FROM source) //field: century, decade, dow, doy, year, month, day, hour, minute, second, etc.
DATE_TRUNC(filed, source)

COMBINATION

UNION [ALL]
INTERSECT [ALL]
EXCEPT [ALL]
//ALL keeps duplicates