Introduction to Standard Query Language (SQL) and PostgreSQL

Joshua Lee
Jul 25, 2017 · 2 min read

Below is a breakdown SQL definitions and syntax. Also a table showing some the more important datatypes supported in PostgreSQL


Definitions:

SQL — SQL (standard query language) is a standard language for storing, manipulating and retrieving data in databases. SQL can do the following:

  • execute queries against a database
  • retrieve data from a database
  • insert records in a database
  • update records in a database
  • delete records from a database
  • create new databases
  • create new tables in a database
  • create stored procedures in a database
  • can create views in a database
  • can set permissions on tables, procedures, and views

PostgreSQL — PostgreSQL is an Relational Database Management System (RDBMS) (such as MS Access, SQL Server, MySQL). The data in an RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows (as seen in the image below).


Important SQL commands:

SELECT — extracts data from a database

SELECT column1, column2, ...
FROM table_name;

UPDATE — updates data in a database

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

DELETE — deletes data from a database

DELETE FROM table_name
WHERE condition;

INSERT INTO — inserts new data into a database

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

CREATE DATABASE — creates a new database

CREATE DATABASE databasename;

CREATE TABLE — creates a new table

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);

ALTER TABLE — modifies a table

ALTER TABLE table_name
ADD column_name datatype;

DROP TABLE — deletes a table

DROP TABLE table_name;

CREATE INDEX — creates an index (search key)

CREATE INDEX index_name
ON table_name (column1, column2, ...);

DROP INDEX — deletes an index

DROP INDEX index_name;

Important Data Types (PostgreSQL):

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade