Database Normalization
Database normalization is useful because it minimizes duplicate data in any single table, and allows for data in the database to grow independently of each other. As a trade-off, queries get slightly more complex since they have to be able to find data from different parts of the database, and performance issues can arise when working with many large tables.
Database Schema
In SQL, the database schema is what describes the structure of each table, and the datatypes that each column of the table can contain.
Inserting Rows
To insert values for all columns
INSERT INTO tableA
VALUES (value_or_expr, another_value_or_expr, ...),
(value_or_expr2, another_value_or_expr2, ...),
...;
To insert values for specified columns
INSERT INTO tableA
(column, column2, ...)
VALUES (value_or_expr, another_value_or_expr, ...),
(value_or_expr2, another_value_or_expr2, ...),
...;
Updating Rows
UPDATE tableA
SET column1 = value_or_expr,
column2 = another_value_or_expr,
…
WHERE condition;
Deleting Rows
DELETE FROM tableA
WHERE condition;
Creating Tables
CREATE TABLE IF NOT EXISTS tableA (
column DataType TableConstraint DEFAULT default_value,
column2 DataType TableConstraint DEFAULT default_value,
...
);
Example of Creating a Courses Table
CREATE TABLE courses (
id INTEGER PRIMARY KEY,
courseName TEXT,
teacher TEXT,
year INTEGER
);
Updating Tables
Add a new column
ALTER TABLE tableA
ADD column DataType OptionalTableConstraint DEFAULT default_value;
Remove column
ALTER TABLE tableA
DROP column_to_be_deleted;
Rename the table
ALTER TABLE tableA
RENAME TO new_table_name;
Dropping Tables
DROP TABLE IF EXISTS mytable;