Modifying data

It is possible to modify an existing table's data using SQL. There are commands for inserting new data, deleting existing data and modifying existing data.

Format
Inserting a new row of data, list of values must match order of table structure.
(You must include NULL if value is blank)
INSERT INTO table VALUES(<list of values>)l

Insert multiple rows of data at once (Not available in Microsoft Access)
INSERT INTO table VALUES (<list of values>),
                                                  (<list of values>),
                                                  (<list of values>);

Delete row of data from table
DELETE FROM table WHERE <condition>

Modify existing row's value
UPDATE table SET attribute - <new value> WHERE <condition>

Modifying table structures

The ALTER statement can be used in a variety of ways to modify the table structure. It can be used to modify, add, and delete columns. Alter can also be used to change a table's name.

Format
Add an attribute
ALTER TABLE table ADD new_attribute data Type <properties>'

Delete an entire attribute(column) from table
ALTER TABLE table DROP COLUMN attribute;

Edit type and property existing attribute(Not available in Microsoft Access)
ALTER TABLE table MODIFY attribute data type <properties>;

Rename a table (Not available in Microsoft Access)
ALTER TABLE table RENAME TO <new name>;

CREATE Statement

Not only can SQL retrieve data, it can also define data structures and create objects. The create statement can be used to create tables, schemas and even users.

Data types
INTEGER- whole numbers(no decimals)
NUMERIC- decimal numbers allowed
VARCHAR(n)- text, can have a maximum length of n
CHAR(n)- text, must be of length n
DATE-date value

Properties
NOT NULL- value cannot be null
UNIQUE-value must be unique within attribute
AUTO_INCREMENT- automatically increase value for next row

Format
CREATE TABLE table(
attribute1 data type <properties>,
attribute2 data type <properties>,
attribute3 data type <properties>
PRIMARY KEY(attribute1)
);

Create table with foreign key
CREATE TABLE table(
attribute1 data type <properties>,
attribute2 data type <properties>,
attribute3 data type <properties>
PRIMARY KEY(attribute1)
FOREIGN KEY (attribute2) REFERENCES other table
);

Add composite keys instead
PRIMARY KEY(attribute1, attribute2)

SQL

SQL - Structured Query Language

A programming language designed to manage and access data in a relational DBMS. Popular SQL version or dialects: MySQL, pgSQL, Microsoft Access

DML-Data Manipulation Language

SQL commands focused on manipulating data in the database. Typical operation include retrieving deleting, inserting and updating data in the database.

SELECT-retrieves data from the database
INSERT- inserts a new tuple(row) of data
UPDATE- modifies existing data in a table
DELETE- deletes tuples (rows) from a table

DDL- Data Definition Language

SQL commands focused on defining database structures or schema in the database. Typical operations include creating schemas, modifying existing schemas, deleting tables and others.

CREATE-creates objects such as schemas, tables, or even users
ALTER- modifies an existing structure in the database
DROP- deletes an entire table including table structure
TRANCATE - deletes all data within the table.