PostgreSQL

Table Management (SQL Commands)

CREATE, INSERT, UPDATE, DELETE, TRUNCATE, DROP

• • Create a new table (in current database):

CREATE TABLE products (

id SERIAL NOT NULL PRIMARY KEY,

item TEXT NOT NULL,

quantity INT,

weight FLOAT

);

• • Insert new row(s) into table:

INSERT INTO products(item, quantity, weight)

VALUES('apple', 25, 0.3),

('orange', 12, 0.5),

('lemon', 30, 0.2);

• • Update row(s) in table:

UPDATE products SET weight = 0.4

WHERE item = 'apple';

• • Delete row(s) in table:

DELETE FROM products WHERE id = 1;

• • Remove all rows from table:

TRUNCATE TABLE products;

• • Delete entire table from database:

DROP TABLE products;

Common Queries (SQL Commands)

SELECT, WHERE, ORDER BY, LIMIT

The basic SELECT command is formatted like this:

SELECT column, another-column, third-column

FROM tableName

WHERE some-column = value;

Examples:

• • Show all rows/columns in table:

SELECT * FROM products;

• • Show specific attributes (columns):

SELECT id, item FROM products;

• • Count rows in table:

SELECT count(*) FROM products;

• • Sort rows by column (ascending):

SELECT * FROM products ORDER BY weight ASC;

• • Sort rows by column (descending):

SELECT * FROM products ORDER BY weight DESC;

• • Limit results (number of rows):

SELECT * FROM products LIMIT 2;

• • Filter results using predicates (WHERE clause):

SELECT * FROM products WHERE weight > 0.25;