SQL

Question 1

UPDATE statement

Answer 1

UPDATE {table} SET {field}={value} WHERE {field}={value}

Question 2

Make new table

Answer 2

CREATE TABLE {table} (
{col1 def},
{col2 def}
);

Question 3

New table (foreign key)

Answer 3

CONSTRAINT {colname} FOREIGN KEY {local_fk_colname} REFERENCES {table}({column}),

Question 4

New table (primary key; auto; not null) [sqlite]

Answer 4

{colname} INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,

Question 5

New table (primary key; auto; not null) [postgres]

Answer 5

{colname} SERIAL PRIMARY KEY NOT NULL,

Question 6

New table (column definition)

Answer 6

{colname} {type} [NOT NULL],

Question 7

remove table (test for existence first)

Answer 7

DROP TABLE IF EXISTS {table};

Question 8

New index

Answer 8

CREATE INDEX {idx_name} ON {table}({column});

Question 9

INSERT statement

Answer 9

INSERT INTO {table} ({col1}, {col2}) VALUES ({val1}, {val2});

Question 10

COUNT statement

Answer 10

SELECT COUNT({col})
FROM {table}
WHERE {condition};

Question 11

SELECT statement

Answer 11

SELECT {col1}, {col2}
FROM {table};

Question 12

INNER JOIN

Answer 12

SELECT {table1}.{col1}, {table2}.{col2}
FROM {table1}
INNER JOIN {table2} ON {table1}.{col}={table2}.{col};

Question 13

UNION statement

Answer 13

SELECT {cols} FROM {table1}
UNION
SELECT {cols} FROM {table2};

(cols must be same number and type)

Question 14

DELETE statement

Answer 14

DELETE FROM {table} WHERE {condition};

Question 15

SUM statement

Answer 15

SELECT SUM({col})
FROM {table}
WHERE {condition};

Question 16

AVG statement

Answer 16

SELECT SUM({col})
FROM {table}
WHERE {condition};

Question 17

sqlite list tables

Answer 17

.tables

Question 18

sqlite show DB schema

Answer 18

.schema {table}

Question 19

postgres choose database

Answer 19

bash# pgsql {db_name}

pgsql> \c {db_name}

Question 20

postgres list tables

Answer 20

pgsql> \dt

Question 21

postgres show table schema

Answer 21

psql> \d+ {table}

Question 22

Search for string in text field

Answer 22

SELECT * FROM {table}
WHERE {col} LIKE ‘%word1%’

Question 23

postgres list databases

Answer 23

pgsql> \l

Question 24

subquery syntax

Answer 24

SELECT
first_name,
salary
FROM
employees
WHERE
salary = (
SELECT
MAX(salary)
FROM
employees
);

Question 25

Used with aggregate functions (e.g. COUNT, SUM) to combine results set

Answer 25

GROUP BY

Question 26

Get records that fulfill a specified condition

Answer 26

WHERE

Question 27

Get records that fulfill a specified condition (for aggregates)

Answer 27

HAVING

Question 28

Aggregate Functions

Answer 28

MIN, MAX, COUNT, SUM, AVG

Question 29

Create a view

Answer 29

CREATE VIEW {view_name} AS SELECT {cols} FROM {table} WHERE {condition};

Question 30

Add column to table

Answer 30

ALTER TABLE {table} ADD {col_name} {type};

Question 31

Remove column from table

Answer 31

ALTER TABLE {table} DROP COLUMN {col_name};

Question 32

Rename a column

Answer 32

ALTER TABLE {table} RENAME COLUMN {old_name} TO {new_name};

Question 33

Change column data type [postgres]

Answer 33

ALTER TABLE {table} ALTER COLUMN {col} TYPE {data_type};

Question 34

Define a join table (many-to-many)

Answer 34

CREATE TABLE a_b ( a_id INT, b_id INT, PRIMARY KEY (a_id, b_id), FOREIGN KEY (a_id) REFERENCES A(a_id), FOREIGN KEY (b_id) REFERENCES B(b_id) );