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

--

--

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