Codecademy Logo

Manipulation

Print Cheatsheet

Column Constraints

Column constraints are the rules applied to the values of individual columns:

  • PRIMARY KEY constraint can be used to uniquely identify the row.
  • UNIQUE columns have a different value for every row.
  • NOT NULL columns must have a value.
  • DEFAULT assigns a default value for the column when no value is specified.

There can be only one PRIMARY KEY column per table and multiple UNIQUE columns.

CREATE TABLE student ( id INTEGER PRIMARY KEY, name TEXT UNIQUE, grade INTEGER NOT NULL, age INTEGER DEFAULT 10 );

CREATE TABLE Statment

The CREATE TABLE statement creates a new table in a database. It allows one to specify the name of the table and the name of each column in the table.

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

INSERT Statement

The INSERT INTO statement is used to add a new record (row) to a table.

It has two forms as shown:

  • Insert into columns in order.
  • Insert into columns by name.
-- Insert into columns in order: INSERT INTO table_name VALUES (value1, value2); -- Insert into columns by name: INSERT INTO table_name (column1, column2) VALUES (value1, value2);

ALTER TABLE Statement

The ALTER TABLE statement is used to modify the columns of an existing table. When combined with the ADD COLUMN clause, it is used to add a new column.

ALTER TABLE table_name ADD column_name datatype;

DELETE Statement

The DELETE statement is used to delete records (rows) in a table. The WHERE clause specifies which record or records that should be deleted. If the WHERE clause is omitted, all records will be deleted.

DELETE FROM table_name WHERE some_column = some_value;

UPDATE Statement

The UPDATE statement is used to edit records (rows) in a table. It includes a SET clause that indicates the column to edit and a WHERE clause for specifying the record(s).

UPDATE table_name SET column1 = value1, column2 = value2 WHERE some_column = some_value;