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