Manipulation

Relational databases are those which organize information into one or more tables. Tables are collections of data organized into rows and columns; where the column is a set of data values of a particular type and a row is a single record in the table.

SQL Clauses and Statements

A few clauses and statements touched on in this section:

CREATE TABLE

Creates a new table. For example …

CREATE TABLE demo (
  col1 INTEGER,
  col2 REAL,
  col3 TEXT
);

… creates a new table called demo with the following DB Schema:

INSERT INTO

Inserts a new row into table. For example …

INSERT INTO demo (col1, col2, col3)
VALUES (1, 'Item 1', 'Item 1 desc');

… modifies the table into:

SELECT

fetches data from a database. SELECT statements always return a new table called the ‘result set’. For example …

SELECT * FROM demo;

… returns all columns from the demo table while …

SELECT col2 FROM demo;

… returns the col2 column from the demo table.

UPDATE

edits a row in the table. This is used for changing existing records. For example …

UPDATE demo
SET col3 = 'Just gibberish'
WHERE col1 = 1;

… mutates the tables as thus:

ALTER TABLE

add new columns to the table. For example …

ALTER TABLE demo
ADD COLUMN col4 TEXT;

… add a new column (col4) to the demo table.

DELETE FROM

deletes rows from tables. For example …

DELETE FROM demo
WHERE col1 = 1;

… removes the first row.

On Constraints

Constraints which add information about how a column can be used are invoked after specifying the data type for the column:

• PRIMARY KEY
• UNIQUE
• NOT NULL
• DEFAULT